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:
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
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!
I would recommend filing a support case for this so Jive can inspect. I would venture a guess that the query is including guests (userid= -1) and CMR is only counting enabled users.