I’m looking to do the same thing in January (just internal). Looking forward to hearing what everyone is doing for a ambassador/advocate program.
Hi Folks : Reposting in case anyone can speak to the original question. Thx.
1 person found this helpful
CMR can give you the top people creating content by Content Type, but I was looking for something related to Users answering Questions and Providing Helpful responses as we are looking to create a top Influencers Badge with gamification and I found this thread that could be helpful: Measuring customers helping other customers
I think you would need to do an Inner Join with the jiveUsers table to get the Usernames but it has the UserIDs and could help you filter out the top people helping users in the community. Here is what the query will return:
- The results of this query will tell you:
- ID of the Thread
- ID of the message that was the answer
- Who asked the question
- Who answered it
- When was the thread asked
- When was it answered
- State of resolution (blank = not resolved, 1 = assumed answered, 2 = answered (could be helpful or correct)
- Helpful answer (0 = No, 1 = Yes) also known as "AIDED"
- Correct Answer (0= No, 1 = Yes) also known as "RESOLVED"
Here is the Query:
select question.threadid, answer.messageid, question.userid as "Asker", answer.userid as "Answerer",to_timestamp(question.creationdate/1000) as "creationdate",to_timestamp(question.resolutiondate/1000) as "resolutiondate", question.resolutionstate,answer.helpfulanswer,answer.correctanswer
from jivequestion question
left join jiveanswer answer on answer.threadid = question.threadid
order by question.threadid asc
Informative ...thanks Mahal.