18 Replies Latest reply on Nov 19, 2014 5:57 AM by josh.richau

    CMR User Reporting Definition Specifics

    annetown

      Our analyst, Chelsea, posted this in our specific, private group, but I wanted to get some answers here. Can anyone help her out? little_chelsea

       

       

      Hello,

       

      As apart of my reporting that I provide, I use the community manager reports that are included in the community. However, when I try and replicate the numbers that are calculated in these dashboards I am never successful. I have read the documentation provided on how an active, participating and contributing person is determined, but what I really need is the exact databases, schema, tables and columns that are used to filter the data down. I also would like to know the criteria Jive uses to disable users. I have found that there is an active column in the database, but to me it doesn't seem logical. I would appreciate a quick response to these issues.

       

      I have included the definitions that I would like the detailed instructions for:

       

        • Re: CMR User Reporting Definition Specifics
          Caton Guilbault

          Hi Anne & Chelsea,

           

          Great questions, the following should get you started. I think we got this directly from one of the Jivers back when CMR launched. I haven't tried to reverse engineer the tables from activty_fact data but I bet it would be possible.

           

          CMR Tables:

          jivedw_adoption_glb_summary
          jivedw_adoption_grp_summary
          jivedw_adoption_spc_summary
          jivedw_adoption_u_glb_summary
          jivedw_adoption_u_grp_summary
          jivedw_adoption_u_spc_summary

           

          ivedw_adoption_u_*_summary tables are Container level

          jivedw_adoption_*_summary tables are User & container level

           

          Version 7 Schema: SchemaSpy - dba8c1cmrana.public

           

           

          Query info

          Most of the SQL behind the CMR charts is dynamically generated and not trivial to parse. 

           

          User Adoption chart:

          select day_ts, active, participant, contributor
          from jivedw_adoption_u_grp_summary
          where day_ts >= '2013-05-29' and day_ts <= '2013-06-29' and container_id = 1296
          order by day_ts;
          

           

          The jivedw_adoption_u_*_summary tables will hold the active, participating, and contributing user counts.  The 'glb' table would be used for site-wide statistics, 'grp' and 'spc' for group and space statistics where the container_id is specified (see query above).  Then, you can put whatever time frame for which you would like to see counts.  Be sure to put upper and lower bounds or you will get a much larger result set than desired, and also be sure not to forget the 'group by' as this is what actually gives you the totals.

          1 person found this helpful
            • Re: CMR User Reporting Definition Specifics
              little_chelsea

              Hello Caton,

               

              I appreciate your quick response in helping us get a map to where we can get this information. It will help in the future determine the status of our site.

               

              I do have one last question. When I look at the numbers provided, I notice that they don't seem to add up. Please see the below screen shot:

               

              Should registered, active, participating, and contributing users add up to total users? Right now the math doesn't seem to fit. When I read the definitions provided by the community manager reports, it seems logical to me that these should add up to total users.

               

              example:

               

              If a user is a Participating user are they also counted in the active and contributing numbers?

               

              Thanks so much for your help!

                • Re: CMR User Reporting Definition Specifics
                  yanhong
                  You might read definition of these first. The latter one is the subset.


                    • Re: CMR User Reporting Definition Specifics
                      little_chelsea

                      Hello Yanhong Huang,

                       

                      I have read the definitions, however, I am still wondering if someone is considered Participating are they counted in the active user count? If they have commented on a post wouldn't that mean they have viewed it also?

                       

                      thanks!

                        • Re: CMR User Reporting Definition Specifics
                          Caton Guilbault

                          Hey Chelsea,

                           

                          The active, participating, and contributing counts are independent of each other and will not add up to total. The same user can be counted in all 3 sections for a specific day based on the activity they preformed during the prior 30 days. 

                           

                          Total is all users that have ever tried to activate that are not deactivated/disabled I believe.  The deactivated/disabled flag is in the jiveuser table userenabled column. How the process of registering and enable/disabling is based on moderation and user settings unique to each community, your admin would hopefully have all the details.

                           

                          Registered are all users that completed registration process and viewed at least 1 thing in the reporting time frame.

                            • Re: CMR User Reporting Definition Specifics
                              annetown

                              Hey Caton Guilbault - Jumping in here. I don't believe the deactivated/disabled are considered a part of the total users as little_chelsea has found discrepancies in this number. She pulls around 65,000 and our site says around 45,000 total users. Any idea or someone we can point to for help with the discrepancy?

                                • Re: CMR User Reporting Definition Specifics
                                  Caton Guilbault

                                  Hey Anne,

                                   

                                  That is my understanding as well, "Total is all users that have ever tried to activate that are not deactivated/disabled".

                                   

                                  I've also seen some discrepancies between total rows in jiveuser and what CMR reports for Total. Our public instance (support.t-mobile.com) shows 219K for Total in CMR but we have over 800K rows in jiveuser and 218K of those showing as enabled. Where is Chelsea pulling your totals from?

                                   

                                  My assumption is CMR is taking the users status into account but I can't confirm that or get full understanding of what all the status's are in jiveuser

                                   

                                  What I think I know for jiveuser Status:

                                  • none(0)
                                  • awaiting_moderation(1)
                                  • approved(2)
                                  • rejected(3)
                                  • awaiting_validation(4)
                                  • validated(5)
                                  • registered(6)
                                  • 7=invited?

                                   

                                   

                                  To help understand the difference for your instance I would recommend filing a case in your private support group. Hopefully josh.richau will see all of this activity and weigh in with his brilliance when he gets a chance.

                                  1 person found this helpful
                                    • Re: CMR User Reporting Definition Specifics
                                      annetown

                                      That is super helpful. I think this may help little_chelsea with her definitions.

                                      • Re: CMR User Reporting Definition Specifics

                                        Looks like Claire offered the answers needed - I did want to point out one other fact that can sometimes be confusing - the user counts when you do any kind of filtering are using the live core DB to get user status which means that the data is what is represented right now - not historical.

                                         

                                        This would come into play if you filter things like the user adoption chart. So that means in general the high level numbers are good - but if you do any filtering the closer you are to the present the more accurate - as you go back in time currently deactivated users get filtered out even if they would have been active during the day you may be looking at.

                                         

                                        Other than that Claire cleared up the other definitions - for active, participating and contributing we make it so if you are contributing you are also participating and active and if you are participating you are also active...

                                         

                                        Caton is also correct about the tables we use but I encourage you to report off of the jivedw_activity_agg_{day,week,month} style tables since we purposefully do not document the tables CMR uses so that we can alter them from version to version as we see fit. The other tables we do our best to keep stable so you can rely on them through an upgrade...

                                      • Re: CMR User Reporting Definition Specifics
                                        cflanagan17

                                        Hi annetown - hope you've recovered from last week! I'm just starting!

                                         

                                        I saw this discussion and wanted to point you to a page I put up for the Advanced Measurement workshop and session I ran last week Participation Ladder: Calculating Discrete Participation Tiers. This is in a project for JW14 attendees, and I don't see that little_chelsea has access, feel free to share with her. This document explains how CMR works and then how to look at "discrete" tiers so you can benchmark your ladder with the industry. (Yes, Jive counts a contributing user as also an 1) active and 2) participating user because they are also all those things.)

                                         

                                        Total users does not include deactivated users.

                                        See Community Manager Reports for these definitions

                                        • Total Users     All system users who have not been deactivated (formerly called disabled).
                                        • Registered Users     All system users who have logged in at least once.
                                        • Active Users     Users who have viewed at least one document, discussion, blog post, status update, poll, video, idea, group overview page, space overview page, or project overview page in the previous 30 days.
                                        • Participating Users     Users who have commented on, replied to, liked, rated/voted, edited, or created at least one document, discussion, blog post, status update, poll, video, idea, group, space, or project in the previous 30 days. It also counts any users who have joined a group in the previous 30 days.
                                        • Contributing Users     Users who have created at least one document, discussion, blog post, status update, poll, video, idea, group, space, or project in the previous 30 days. You need to actually create something and not just respond to existing content to be considered a contributing user. For example, you can start a discussion, ask a question, or post a document in order to be considered a contributing user.

                                         

                                        If you are seeing a discrepancy it is definitely something to discuss with support and with your technical admins to ensure your SSO integration is working correctly.

                              • Re: CMR User Reporting Definition Specifics
                                little_chelsea

                                Also Caton,

                                 

                                How does Jive determine who is deactivated? How is this process currently being done?

                                  • Re: CMR User Reporting Definition Specifics
                                    cflanagan17

                                    little_chelsea

                                    Do you not have a single sign on process? Typically determining "deactivated" users is a business process rule that companies define and configure SSO integration to honor (if you have a community tied to an SSO system). If you are managing an external community which does not use SSO integration (some external communities still do integrate with an identity management system) then someone is actively reviewing the membership and determining who to deactivate for whatever reason (1. let users self-determine 2. deactivate accounts that haven't contributed over x time or 3. deactivate accounts when email bounce testing have validated the email address is no longer correct, etc.).

                                     

                                    Once a user is deactivated their profile no longer shows up in the Jive interface (not available in the user directory). However, their content remains in the community and a member can hover on the profile card of a deactivated user to know who contributed the content and get a visual indication that the member is no longer an active member.

                                • Re: CMR User Reporting Definition Specifics
                                  katiebroberts

                                  Where does Jive "count” Banned users?

                                   

                                  (Also wondering about deactivated users)