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
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
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.)