11 Replies Latest reply on Jun 19, 2015 5:44 PM by Ted Hopton

    Calculating Registered Users with Jive DES?

    Ted Hopton

      I'm making progress using Jive DES, leveraging Dirk McNealy's nifty Simple Script for Jive Data Export Service, but a significant obstacle has been calculating the number of Registered Users at any given point in time. I want to create Member Adoption and Participation Ladder(s) at the weekly level using Jive DES data.


      While I can count the number of unique users who were active, participating or contributing in a certain time period, I have not figured out how to calculate the number of Registered Users on a specific date, and I need this to calculate the inactive users, a key part of the Participation Ladder. Registered Users should be anyone who has logged into the site at least once -- not in a specific time period, but ever at all from when their account was created.


      I have an existing data set from January-May 2015 that I have already created containing the data I needed to measure Active, Participating and Contributing activity, so I've looked at that to try to figure out how to calculate Registered Users. I'm afraid I have a lot of questions (highlighted in bold) and I'd be grateful for help in understanding this better.


      What Data to Count Registered Users?

      Step 1 obviously will be to pull data not just from the time period I'm reporting on, but all time since the site was launched. But what data do I select? The activity.actor.status field contains a value of Registered, so that appears to be what I want. I could count the unique usernames that appear as Registered in the activity.actor.status field, for starters.


      One problem is that the amount of data I'd pull in would be huge, so I need a way to narrow the query parameters. If I look at the data I currently have and select activity.actionObject.objectType = user I see 8631 Registered, 146 Null, 1 Invited and 3 None. How are users that are not Registered are doing anything in our system? Are the Null users External Contributors? Has the Invited user logged in but not completed the process? What does None mean?


      In looking for a way to decrease the number of records returned, if I look at this by activityType, I see results for Create, Endorse, Login, Tag, Update and View.

      DES activity type results.png

      I would have thought that if I selected Login as the activityType then I would capture every Registered user. But there are only 7942 records that way and 8631 when I don't break it down by activityType. Which way is going to give me the accurate count of Registered users?


      De-Activated Accounts

      I can't simply count the number of unique usernames since the launch of our site that have Registered as a value for activity.actor.status, because that won't account for accounts that have been de-activated. How can I remove the de-activated users from the totals for any given date?


      CMR Does Not Seem to Match DES

      Because I could not figure out how to calculate Registered Users from Jive DES data, I tried using CMR data, instead. I pulled the Registered Users on specific dates (e.g., Monday of the week I want to report on), thinking I could then do calculations with the Jive DES data on active, participating and contributing counts in Excel to complete my Participation Ladder. While this seemed to work at a high level, when I drilled down by business unit it failed. I found many weeks where the number of Active users I calculated with Jive DES was higher than the number of Registered Users reported in CMR. After checking and re-checking the criteria I used to calculate Active users in DES, I still can't explain why the numbers are out of whack. Are the data pulling from the same source? Why would the Active Users be higher than the Registered Users?


      Sorry this is so long and full of questions. If we could document how to do this accurately, I think it would be of value for a number of Jive customers who want to tap into the Jive DES. I'd be grateful for help in getting this figured out.


      cc: Udit Shah Claire Flanagan

        • Re: Calculating Registered Users with Jive DES?

          Udit Shah Something we can put on our agenda for Monday?

          • Re: Calculating Registered Users with Jive DES?

            These are a lot of good questions, and most of them questions that I have as well. I will investigate and answer as much as I can.


            Here's something that might help to begin with:

            For all of these calculations, CMR looks for where a user is visible and enabled. So if you add to all your queries where visible=true and enabled=true, I hope you'll find the numbers to be closer.

              • Re: Calculating Registered Users with Jive DES?

                Ted Hopton, would it be possible for you to share if the numbers that you pulled from DES are higher then CMR or lower. This will help determine if you are missing filters or if there is something else.

                  • Re: Calculating Registered Users with Jive DES?
                    Ted Hopton

                    Sure, Udit, let me give you more detail. Thanks, in advance, for any help you can provide.


                    Here's what I have for a business unit's results in 2015.

                    cmr des discrepancy.png

                    Light blue columns, Total Users and Registered, are from CMR for the day beginning the week shown (Mondays), which I pulled last week. One reason I was interested in Tracy Maurer's thread, CMR data is based on only users logged in during past 30 days, is that I noticed discrepancies between the results shown in CMR last week for January data and the results I logged in my spreadsheet for January when I looked at CMR in February (numbers were lower last week).


                    The yellow columns, Active, Participating and Contributing, were calculated in Tableau from Jive DES data. The orange cells show the weeks where numbers are impossibly out of whack. It should not be possible to have more Active users than Registered users. I also looked at daily results within those weeks, to make sure we had not had a sudden spike in new Registered users after Monday, for example, and saw nothing out of the ordinary expected small daily variations.


                    Let me give you the specific criteria I used, in case that helps. The initial queries of Jive DES used Dirk's script with this command:

                    ./jive-data-export-service-simple-query 2015-05-24 2015-05-30 'filter=match(activityType,View,Comment,CommentUpdate,CreateOutcome,Endorse,Like,Rate,Resolved,Share,Vote,Create)&not(name(ACTIVITY_CREATE_USERRELATIONSHIP))' > user-adoption-week-2015-05-24.csv

                    I pulled one week at a time and loaded the CSV files into a Filemaker Pro database. Then I exported the Filemaker database to a CSV file and connected Tableau to it.


                    I also have been downloading CMR's Profile Completion table more or less monthly for a long time, so I loaded one from January into a Filemaker Pro database, then updated that database with later copies of the same table I had for each month, adding only new records (based on username) but also updating existing records (so someone's business unit changed, for example, the change would be captured).


                    I repeated the export to CSV process, connected to the file with Tableau, and then joined the two files in Tableau on username so that I have profile field data associated with the DES data (e.g., business unit).


                    In Tableau, I created a Unique Members measure, using COUNTD([activity.actor.username]).

                    To count Active members I selected ActivityType = View.

                    To count Participating members I selected all of the following ActivityType values: Comment, CommentUpdate, CreateOutcome, Endorse, Like, Rate, Resolved, Share, Vote.

                    To count Contributing members I selected ActivityType = Create and excluded the following Name values: Activity_Create_User, Activity_Create_Userrelationship, Activity_Create_Userrelationshiplist.


                    I am aware that my selections do not match up perfectly with CMR definitions (as I understand them), but when I tried selecting and deselecting specific activities and types, the differences did not seem to make a big difference and I'd rather include the items that I think make the most sense. For the major issue I have -- number of Active from Jive DES exceeds number of Registered in CMR -- my selection of ActivityType = View should match the criteria that CMR uses, and at any rate it still should not be possible for the unique number of active users to exceed the number of registered users.

                  • Re: Calculating Registered Users with Jive DES?
                    Ted Hopton

                    Ah, Enabled is what I want, Dirk. Perhaps that's not included in the dataset I have pulled -- what field is it in?

                      • Re: Calculating Registered Users with Jive DES?

                        Each user should have an enabled field, ie. activity.actor.enabled


                        edit: same with visible activity.actor.visible


                        edit2: also for the case where the user is the action object activity.actionObject.enabled and activity.actionObject.visible

                          • Re: Calculating Registered Users with Jive DES?
                            Ted Hopton

                            I see, Dirk. So does that mean I would need to pull a query for each date where I need the number of registered users, specifying activity.actor.enabled?


                            Wondering how that would work... does every user have such a field with a value in it for every day since the account was created? I would have guessed it's a flag that is either on or off, not something associated with a date. So, if you had an account for 3 years but were deactivated yesterday, if I run this query for a day one year ago, I would expect your record to be excluded, since you are no longer enabled. Just guessing though -- how does it actually work?

                              • Re: Calculating Registered Users with Jive DES?

                                Here's one of the ways cloud analytics is going to differ from cmr. Cloud analytics saves the context at the time of the event. If a user is disabled today, that doesn't mean they were disabled when they took actions yesterday. So the user taking the action in the analytics event is enabled. The idea is to show you exactly what happened at the time of an event.

                                But in CMR, I believe that user will be taken out of yesterday's active as well, in some cases. This mix and match of historical data against current data is one of the things that makes the database driven CMR charts so confusing and we're hoping to fix with cloud analytics.


                                tl;dr in the events you get from DES, the user information is the information from the time the event happened, not the time you run the query.

                                  • Re: Calculating Registered Users with Jive DES?
                                    Ted Hopton

                                    Thanks for that important clarification, Dirk. I didn't realize that and I see why it's essential to understand. Good!


                                    However, I'm still confused about how to apply this to calculating Registered Users. An example:

                                    • John Smith logs in for the first and only time ever on April 1, 2011. He never does anything again in the site, but he should still count as a registered user until December 31, 2013 when his account was de-activated.
                                      • If I count the unique usernames with Registered in activity.actor.status field, with visible and enabled = true, from launch of the community (prior to April 1 2011) through December 30, 2013, John Smith will correctly be included as one of the Registered.
                                      • If I do the same thing but pull data through Jan 1, 2014 (say that's the date I want to report about), will John Smith be counted as Registered?
                                        • His one login will be found in that set of data, and it will show that he was enabled and visible at that time, right? So that would meet my criteria and he would be counted.
                                        • How will there ever be a record that returns a not-enabled status? The user can't take any action after being de-activated, so what record would exist in Jive DES?
                                        • If there is a record showing his not-enabled status, how do I create a calculation that reflects that, instead of the record on April 1, 2011 that shows him as enabled?
                                      • Re: Calculating Registered Users with Jive DES?

                                        Building the state of objects at a given time is difficult with analytics; you have to rebuild the object's state from all it's past events. Any time a change is made to a object, it generates a modify event so we can track the changes.


                                        In your jive instance, a registered user is defined as a user who is visible and enabled and has logged in at least once.

                                        In the event model, you need to find the events related to those things and build the state of the object.

                                        A registered user at time x is defined as:

                                        A user who has been created prior to time x, has logged in once prior to time x

                                        AND (

                                        (Was created visible and has not been modified AND

                                        Was created enabled and has not been modified)


                                        (the most recent modify user event prior to time x is visible and enabled)


                                        I know this is complicated. And it's made worse by the fact that our modify events contain the data for the object after the modification, but do not contain what changed in the modification. The good news is I am working right now to fix this and to make an object state api that would make our system do these computations for you and tell you the state of an object or set of objects at a given time.