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.
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?
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.