2 Replies Latest reply on Feb 18, 2015 9:03 AM by RyanSe

    Active users' data for a month




      I am new to jive analytics and is trying to generate a report with details of users who accessed our site in the month of January (active users). I would like to confirm if the modification timestamp (modification_ts) from jivedw_user table is the value to be fetched for this.


      Here's the basic query I am using:

      SELECT jivedw_user.user_id,jivedw_user.username,jivedw_user.firstname,jivedw_user.lastname,jivedw_user.modification_ts FROM jivedw_user

      WHERE (jivedw_user.modification_ts>='2015-01-01') AND (jivedw_user.modification_ts<='2015-01-31')

      Also, if there is a better way of doing this, please let me know.


      The numbers displayed by CMR is lower than the count I got from running this query and I am guessing that CMR is only showing the number of active users at the point of checking.




        • Re: Active users' data for a month

          Hi Vishnu ,


          The modification timestamp (modification_ts) from jivedw_user table is the timestamp when the user profile is modified.You can use "jivedw_activity_fact" table to find out the users active / activity happened during that time with the help of activity timestamp.

          Not sure if you can exactly match that data with CMR active users, as community manager reports gives you active users on the basis of this  "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." at a given point .


          Hope this helps.




          • Re: Active users' data for a month

            SELECT DISTINCT jivedw_user.user_id,jivedw_user.username,jivedw_user.firstname,jivedw_user.lastname,jivedw_user.modification_ts

            FROM jivedw_activity_fact,jivedw_user

            WHERE jivedw_activity_fact.user_id=jivedw_user.user_id AND (jivedw_activity_fact.activity_ts>='2015-01-01') AND (jivedw_activity_fact.activity_ts<'2015-02-01')


            Give that one a shot. As Charu Gulati mentions the data lives in jivedw_activity_fact (also could use various agg tables too but I prefer activity_fact).


            The trouble with using the last modified date is that if someone used it in February but not in January the last modified would show they accessed it in Feb...but does that mean they used it in January too?