22 Replies Latest reply: Feb 21, 2013 7:49 AM by mnevill RSS

    Measuring customers helping other customers

    arose

      Hi-

       

      We have a support community on SBS and one of the things we are really interesting in measuring is the percentage of customer questions that get answered by other customers rather than by our own employees.  All employees are set up as a separate permission group in SBS.  Is anyone measuring this in their Jive instance?  Any tips on how I would be able to calculate this in SBS using Business Objects reporting?

       

      Thanks for any guidance you can provide.

       

      Andy

        • Re: Measuring customers helping other customers
          RyanSe

          Andy,

           

          Were you able to find an answer to this question?

           

          I am not measuring this...yet. I actually just got set up with our SBS reporting instance today and this was one of the questions that was asked of me so I am hoping you might have some additional insight since you made the original post.

            • Re: Measuring customers helping other customers
              david.sullivan

              If this has not already been addressed:

               

              If your Member Profiles fields contain a Company Name or other uniquely identifying field which would help you to differentiate between an employee and non-employee you could use this as a filter or create a variable to differentiate between the two.

               

              Look in Member Profiles, select Profile Field Value and Profile Field Name as result objects. Then check the returned values.

               

              Best regards,

               

              Dave

                • Re: Measuring customers helping other customers
                  arose

                  Hi Ryan and Dave-

                   

                  No, I still haven't been able to figure out a way to do this.  Dave, I was able to differentiate between employees and non-employees using the profile field for company.  It's not perfect because not everyone in the employee permission group is completing this field, but it should do for the most part.  What I can't figure out, however, is how to then use this information to determine whether a question was answered by one of these people or someone else in the community.  Is there a filter or variable I can create for this?  Ideally what I'd like to measure is whether the first reply to a question was from another community member or an employee.

                   

                  Thanks,

                  Andy

                    • Re: Measuring customers helping other customers
                      david.sullivan

                      Hi Andy,

                       

                      Yes that method does rely on the completeness of profile fields.

                       

                      I am not really sure where you are having the problem.

                       

                      Is it with defining the answered record?

                      If so:

                       

                      To define Answers use Activity Type 'Resolved' (Activity ID 300) then associate the Username,and Activity Date with that record.

                       

                       

                      Other?

                       

                      Let me know.

                       

                      Best regards,

                       

                      Dave

                       

                       

                       

                        • Re: Measuring customers helping other customers
                          arose

                          Hi Dave-

                           

                          I must be doing something wrong here.  I’m trying to determine the number of questions answered by employees vs. non-employees during a particular time period, but when I use the “resolved” activity type and associate it to usernames during a time period what it’s showing me is the usernames whose questions were marked as resolved.  These are the usernames who originally asked the question but I want to know the usernames of those who answered the question to see whether or not they are employees.  Does that make sense?

                           

                          Also, since not every user marks a response as answered or helpful and sometimes questions require a mix of employee and non-employee responses, I’m interested in measuring the number of threads where a non-employee (other than the original person that asked the question) posted the initial response.  Is this something that can be measured as well?

                           

                          As an aside, is there any plan to incorporate permission groups into the analytics so we can use them for filtering rather than relying on the profile fields?

                           

                          Thanks,

                          Andy

                            • Re: Measuring customers helping other customers
                              david.sullivan

                              Hi Andy,

                               

                              I may have found a solution to the Q&A issue posted so long ago.

                              I have uploaded a new report in the Demo Reports DIR under Jive Reports.

                               

                              Title:

                              Question Resolution - Duration
                              Take a look and see if this helps.
                              Best Regards,
                              Dave
                              ps - On permission groups -Not that I am aware of.
                                • Re: Measuring customers helping other customers
                                  Suzanne O'Brien

                                  Hi,

                                  I am looking to answer the same question as Andy: "...I want to know the usernames of those who answered the question..."

                                  I built a report of everyone who AIDED or RESOLVED, and how many times.  But I really need to know the username whose reply they marked.  This must be tracked since status points are generated from this metric.  Anyone have any idea?

                                  Suzanne

                                    • Re: Measuring customers helping other customers
                                      RyanSe

                                      Hi Suzanne,

                                       

                                      I am not sure how to do this in Business Objects but I do know how to do it via SQL query of the production/system database. It might not be the prettiest piece of code but it gets the job done.

                                       

                                      Do you have access to the the system database (probably Postgre SQL dmp files)?

                                        • Re: Measuring customers helping other customers
                                          Suzanne O'Brien

                                          Hi Ryan,

                                          Any way we can do this would be awesome.  I'm not technically experienced enough to approach the database, but other members of my team are.

                                          Suzanne

                                            • Re: Measuring customers helping other customers
                                              RyanSe

                                              That's great.

                                               

                                              Here is the query I use to get the basic information out. It is based on userid and not the username but that can be updated by linking the jivequestion and jiveanswer tables to jiveuser.

                                               

                                              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"

                                               

                                               

                                              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

                                               

                                              On a side note, if your folks want to also pull in the jivegroupuser and jivegroup tables you could identify any permissions group they are a member of.

                                               

                                              Personally, I pull this data and then put it in Access to do a slight bit of data massaging by combining the results of this query with an internally created list of our users. The end result is a basic dashboard that shows us what % of questions are being resolved by end users and what % are being resolved by our community staff. There are dozens of other ways to slice and dice the data as well.

                                                • Re: Measuring customers helping other customers
                                                  Suzanne O'Brien

                                                  Ryan,

                                                  This is fabulous.  The additional end result - knowing which perm group the answerers are with - will be a bonus.  It will help us support ROI and headcount objectives.  Thanks again.

                                                  Suzanne

                                                  • Re: Measuring customers helping other customers
                                                    Suzanne O'Brien

                                                    Just wanted to report back that my IT team was able to run this and I am already knee-deep in the data!.  They had to re-write the query to use in SQL Server.  Here is what they ended up with:

                                                     


                                                    select question.threadid, answer.messageid, question.userid as "Asker",
                                                    answer.userid as "Answerer",
                                                    DATEADD(SECOND, question.creationdate/1000,'19700101 00:00') as "creationdate",
                                                    DATEADD(SECOND, question.resolutiondate/1000,'19700101 00:00') 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

                                                     

                                                    We use LDAP so we can combine the results with our permission group and other user data from there as well.  Really great stuff.

                                                    Suzanne

                                                    • Re: Measuring customers helping other customers
                                                      mark.schwanke

                                                      Ryan-

                                                       

                                                      I'm looking through the documentation. What database schema was this in?

                                                       

                                                      http://docs.jivesoftware.com/jive_sbs/4.5/index.jsp

                                                       

                                                      Jive 4.5 > Developers Community Documents > References > Application Database Schema

                                                      Jive 4.5 > Developers Community Documents > References > Analytics Database Schema

                                                       

                                                      Secondly have you looked at any particular group...? Say your Mods are part of a permission group and you want to see who is producing correct answers. Competitively I'd like to see of my Mods who has the best Points/Posts ratio. To identify quality advocacy do you also look at your General population has the most Correct/Helpful answer desginations compared to posts.

                                                       

                                                      Mark

                                                        • Re: Measuring customers helping other customers
                                                          RyanSe

                                                          Hey Mark,

                                                           

                                                          The query is run against the Application database. The tables involved are:

                                                           

                                                          • jivequestion - has all of the "questions" in it
                                                          • jiveanswer - tracks the resolver and resolution type (helpful/correct)

                                                           

                                                          Optional add-on tables:

                                                          • jiveuser - jivequestion.userid = jiveuser.userid and/or jiveanswer.userid = jiveuser.userid (then put jiveuser.username in the select statement...for us username=NT)
                                                          • jivegroupuser - tracks what group everyone belongs to
                                                          • jivegroup - list of the groups
                                                          • jivecommunity - If you want to isolate specific spaces (contenttype 14) just join jivecommunity.communityid = jivequestion.containerid

                                                           

                                                          We do indeed look at activity within particular user groups as far as which group is resolving the most questions. Although I do that via Access merely because I set up the reporting prior to us setting up LDAP federation. In Access, I have a list that tracks what role the person has in our community (Product SME, Editor, Moderator, Rep, Leadership, etc).

                                                           

                                                          As far as points/posts, I prefer to focus more on helpful-correct over posts to get that ratio since I haven't found points to be that meaningful for our group.

                                                           

                                                          I am also working on isolating "Most Helpful" and "Top Resolvers" as independent queries. That way we keep our community focused on resolution and not points accumulation.

                                                • Re: Measuring customers helping other customers
                                                  Cameron Radis

                                                   

                                                   

                                                   

                                                  I may have found a solution to the Q&A issue posted so long ago.

                                                  I have uploaded a new report in the Demo Reports DIR under Jive Reports.

                                                   

                                                  Title:

                                                  Question Resolution - Duration
                                                  Take a look and see if this helps.

                                                  Dave,
                                                  I am trying to find the Demo report that you uploaded a while back in response to Andy's question. I don't exactly know where to look to locate the document. Could you please help direct me to  where I could find that.
                                                  Thanks,
                                                  Cameron
                                                    • Re: Measuring customers helping other customers
                                                      david.sullivan

                                                      Hi Cameron,

                                                       

                                                      This was a report posted in Andy's BIOD(Business Information On Demand - aka SAP Business Objects) account.

                                                      As such it is not publicly distributable.

                                                       

                                                      It provides some of the info, but not as complete a picture as Ryan's solution.

                                                       

                                                      Best regards,

                                                       

                                                      Dave

                                                        • Re: Measuring customers helping other customers
                                                          mnevill

                                                          This is a metric that is vital for us as it should be a pretty accurate way to measure direct call deflection.  Is there a way to run the SQL query Ryan or Suzanne posted within BI ondemand?  If not, how do we get access to the system database to run something like this?

                                                           

                                                          Thanks,
                                                          Matt

                                                            • Re: Measuring customers helping other customers
                                                              mdcrocker

                                                              Hi,

                                                              Why BI Ondemand can tell you helpful and answered, you need to do an SQL on the database to get assumed answered.  we are also looking at call deflection.  The problem is that the customer who asked the question has to tell us.  I have started prompting them when the reply string stops and is a week old.  I have asked them to at least mark it as assumed answered so we can track questions that get an answer and who provides useful answers.

                                                              Since they come to solve an issue, once it is solved, they are off to other work and do not have an incentive  to return.

                                                               

                                                              What are you using to determine if it deflects a call?

                                                              Mike

                                                                • Re: Measuring customers helping other customers
                                                                  mnevill

                                                                  We have our Support Engineers mark it as assumed answered if we (or another community member) gave them a valid next step or solution to their question and they do not return to the thread to ask more questions or let us know if it worked within a reasonable amount of time.  At the same time we reply letting them know we marked it as assumed answered, but that it would really help the community out if they would return to the thread and select any correct or helpful answers.  We do not have a silver bullet for complete call deflection (if you do please share).  We measure part of it as a user marking another user's response as correct where our employees were not involved.  Can we run SQL queries inside of BI ondemand?  If not how do we get the data so we can run the queries?

                                              • Re: Measuring customers helping other customers
                                                mnevill

                                                I created a video showing how to get this (link below) and just wanted to associate this thread to it so others can find it later:

                                                Using Postgres SQL queries and Excel to determine non-employee answers

                                                 

                                                Thanks,
                                                Matt