5 Replies Latest reply on Oct 26, 2015 1:35 PM by erinclements

    Is there a query that will tell me the number of likes, comments and views on ALL content?

    erinclements

      Is there something that can tell me how many likes, comments and views all content has?  I found a fantastic query from Caton Guilbault that can tell me this for just documents within spaces, but some of our most popular content is housed in system blogs, which are not picked up by the Content Leaderboard in CMR.  I was using a query that would just give me the total number of views, but my higher ups would like to see "engagements" on each of these pieces of content as well, and I'm stuck!  Thanks!!

       

      *EDIT TO ADD MY SOLUTION*

      WOW that took about three straight days of messing around, but I think I finally got it!  What I ended up doing was running two different queries from two different databases and combining the data through a formula in Excel.

       

      This is more for me than anything, in case I need to go back and do this again, ha!  So forgive my excessive detailed-ness!

       

      First I took the following columns from the "jivebrowsecnt" table in the System Database: (Browse Table)

      SELECT 
        jivebrowsecnt.browseid, 
        jivebrowsecnt.objecttype, 
        jivebrowsecnt.objectid, 
        jivebrowsecnt.authorid, 
        jivebrowsecnt.subject, 
        jivebrowsecnt.status, 
        jivebrowsecnt.containertype, 
        jivebrowsecnt.containerid, 
        jivebrowsecnt.creationdate, 
        jivebrowsecnt.modificationdate, 
        jivebrowsecnt.lastactivitydate
      FROM 
        public.jivebrowsecnt;
      

      This gave me the object ID and name as well as a bit of extra information.

       

      Then, I took the following columns from the "jivedw_activity_summary" table in the Analytics Database, and added in a date parameter b/c I was asked for a specific time frame: (Activity Summary Table)

      SELECT 
      jivedw_activity_summary.day_ts, 
      jivedw_activity_summary.content_id, 
      jivedw_activity_summary.content_type, 
      jivedw_activity_summary.response_count, 
      jivedw_activity_summary.view_count, 
      jivedw_activity_summary.like_count
      FROM 
      public.jivedw_activity_summary jivedw_activity_summary
      WHERE 
      (jivedw_activity_summary.day_ts>{ts '2015-07-01 00:00:00'})
      

       

      From there, I combined the data in the Activity Summary sheet in a pivot table because the activities were returned with each day for each item on a separate line. 

       

      Then, I used a Match/Index formula to match the Object ID and Content Type to the Subject to get the title of the content.

       

      May be a little backwards, but after spot checking quite a few, everything seems to be checking out!