1 Reply Latest reply on Jul 23, 2015 1:27 PM by Caton Guilbault

    Database vs CMR Activity Count

    erinclements

      I am trying to get a report of all the top viewed content in our community. Unfortunately, I can not go through CMR, because the Top Content does not include System Blogs, and one of our most widely read blogs is a System Blog.  I have been going through the Analytics Database running a query that will get me all of the views for a specific month.  The query I am using is:

       

      SELECT

      jivedw_activity_agg_month.month_id, jivedw_activity_agg_month.user_id, jivedw_user.email, jivedw_object.object_type, jivedw_object.name, jivedw_container.container_type, jivedw_container.container_id, jivedw_container.name, jivedw_activity_agg_month.activity_count

      FROM

      jivedw_activity_agg_month

      LEFT JOIN jivedw_user ON jivedw_user.user_id = jivedw_activity_agg_month.user_id

      LEFT JOIN jivedw_object ON  jivedw_object.dw_object_id = jivedw_activity_agg_month.direct_dw_object_id

      LEFT JOIN jivedw_container ON jivedw_container.dw_container_id = jivedw_activity_agg_month.dw_container_id

      LEFT JOIN jivedw_month ON jivedw_activity_agg_month.month_id=jivedw_month.month_id

      WHERE jivedw_month.month_of_year = '4' AND jivedw_month.year = '2015' AND jivedw_activity_agg_month.activity_type = 10

       

      However, when I look at a piece of content that is included in CMR, the activity counts are much larger by running the query than in CMR for that specific month.  Is there a way to know which one is more reliable?  Is there another way I should be going about this?  Thanks so much!