3 Replies Latest reply on May 14, 2014 3:11 PM by mnevill

    CMR content leaderboard SQL query?

    mnevill

      Does anyone have a query handy that would mimic what you get with the CMR content leaderboard?  I am mostly interested in views (not likes, follows, etc) and need all the different content (documents, questions, discussions, ideas, status updates, polls, etc) that was looked at over a date range with the totals summed up for each piece of content by title.  Also, it can't be capped at the top 1000 pieces of content.  The views need to be total views to include non-logged in users too. 

       

      I replied to this thread but didn't seem to get a response: In V6 can I extract a report showing number of contents and views of content by content type

       

      CC: Jive Analytics

       

      Thanks,

      Matt

        • Re: CMR content leaderboard SQL query?
          Caton Guilbault

          Hey Matt,

           

          We use the following query for docs but it could be modified to include other pieces of content.

           

          select doc.internaldocid,doc.documentid, person.username as "Original Author", community.name as "Space Name",to_timestamp(doc.creationdate/1000) as "creationdate",to_timestamp(docversion.creationdate/1000) as "VersionCreatedate", to_timestamp(docversion.modificationdate/1000) as "Modificationdate",docversion.userid as "Last Modified UserID", jcount.viewcount,docversion.title

          from jivedocument doc

          left join jiveuser person on person.userid = doc.userid

          left join jiveobjecttype objtype on objtype.typeid = doc.containertype

          left join jivecommunity community on community.communityid = doc.containerid

          left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid

          left join jiveviewcount jcount on jcount.objectid = doc.internaldocid

          where docversion.state = 'published' and objtype.code = 'community' and jcount.objecttype = '102'

          order by jcount.viewcount desc

           

          This a query we use for Threads:

           

          select msg.threadid, question.threadid as "QuestionID",msg.userid, community.name as "Space",to_timestamp(msg.creationdate/1000) as "creationdate",latest.messagecount, jcount.viewcount, question.resolutionstate,

              date_part('year',to_timestamp(msg.creationdate/1000)) as "Year",

              date_part('month',to_timestamp(msg.creationdate/1000)) as "Month",

              date_part('day',to_timestamp(msg.creationdate/1000)) as "Day",

              date_part('week',to_timestamp(msg.creationdate/1000)) as "Week",

              msg.subject

          from jivemessage msg

          left join jivethreadlatest latest on latest.threadid = msg.threadid

          left join jiveviewcount jcount on jcount.objectid = msg.threadid

          left join jivecommunity community on community.communityid = msg.containerid

          left join jivequestion question on question.threadid = msg.threadid

          where     msg.parentmessageid is null and

              jcount.objecttype = '1' and

              msg.containertype = '14'AND

              to_timestamp(msg.creationdate/1000) >= '9/1/2012 00:0:0'

          order by jcount.viewcount desc

            • Re: CMR content leaderboard SQL query?
              mnevill

              The query below is what I ended up using.  I haven't had a lot of time to tweak it, so you have to know the right month id to input vs being able to specify a date range.  Also, this does not distinguish a Question from a Discussion, so both are classified as a Thread.  Once I get this output, I relabel the object_type into something readable (1 = Thread, 102 = Document, etc), then I make a pivot table to get the total view count for each piece of content, and finally use VLOOKUPs to get the space id, space name, and content type all in one table.  This replicates the CMR report for everything I use except as I mentioned earlier both Discussions and Questions are now Threads.  I also mix in our company division based on space id from another VLOOKUP to help us track how much content from each division is consumed.  From there I can do another pivot table to lay everything out very nicely in a summary of each content type and the number of views for each by division.

               

              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 = '2014' AND jivedw_activity_agg_month.activity_type = 10  AND jivedw_object.object_type != '14' AND jivedw_object.object_type != '3'

               

              NOTE: replace the value for month_of_year and year appropriately

               

              If anyone figures out how to accurately break Threads up in to Questions and Discussions please comment with an updated query.

               

              Thanks,
              Matt

            • Re: CMR content leaderboard SQL query?
              mnevill

              I just updated this query with a way to insert the month and year directly without having to correlate it to month_id.  Thanks Caton Guilbault for the tip!