0 Replies Latest reply on Aug 21, 2013 11:20 AM by eg129212

    How can I see the status point detail behind the User Leaderboard report?

    eg129212

      I'm trying to create a query which shows the detailed break-down of how each user earned points in a certain social group.  This would be similar to the User Leaderboard in CMR, but instead of showing the total points per person, it would list each action that caused the user to earn points, and how many points he earned for that action.  I started out with this query:

       

      select p.*, o.dw_object_id, o.name
      from jivedw_STATUSLEVELPNT p, jivedw_object o
      where
      p.object_type = o.object_type and p.object_id = o.object_id
      and p.container_id = 1322 and p.container_type = 700
      and p.user_id = 2113
      order by p.creation_ts
       

       

      The results look like this

      Capture4.JPG

      The first row shows that the user earned 5 points for creating a document named "My document that people actually liked!".

      The remaining rows show that 12 different people liked the document (2 points each).  Notice that the object name for these rows is cryptically "1".

       

      I'm struggling with how to make the association for each of the 2 point rows to which document was actually liked.  I was hoping to see the same document title as the first row.  Does this data even exist in the Analytics Database?

       

      I found that I can run a query against the core database like this to determine that these were "contentLiked".  So what table should I look in for the object id's in question (13216, 13227, etc.), to link it back to what actual document was liked?

      select * from jivestatuslevelpnt where userid = 2113 and objecttype = -786106556 and containerid = 1322
       

      The results of this query look like

      Capture3.JPG

       

      Any help would be greatly appreciated.

       

      P.S.  I found a very helpful article about how to get the detail for "likes": https://community.jivesoftware.com/community/products/platform/analytics/blog/2012/04/27/query-most-liked-content by Brian Roginski.  His query focuses on "likes"; whereas I'm trying to figure out how to get the detail for points earned (some points equate to likes, but obviously there are many other ways to get points.)