1 Reply Latest reply on Nov 7, 2016 9:10 AM by Caton Guilbault

    Analytics query for time to first response

    rvs_narayana

      Hi

       

      We are using Jive Analytics DB for reporting and I am looking for help on measuring the below metric :

       

      • Time taken for the first response for each question/discussion thread.

       

      Can anyone help me with the query if they have done this before ?

       

      Thanks for the help.

        • Re: Analytics query for time to first response
          Caton Guilbault

          Hi Rayaprolu,

           

          I haven't finished wrapping my efforts on this topic up into a single query yet but here are some building blocks that might help. I use a mixture of CORE DB & Analytics DB queries/results and pull them all together via Excel so we can easily inspect the results at various granularity.

           

          Fist I pull a thread list from CORE DB:

          --ThreadTable - with question status for Time to Staff View
          select  jivethread.threadid,
              question.threadid as "QuestionID",
              (TIMESTAMP 'epoch' + jivethread.creationdate * interval '1 millisecond') as CreationDate,--converts epoch to postgre local timezone
              question.resolutionstate,
              date_part('year',to_timestamp(jivethread.creationdate/1000)) as "Year"
          from    jivethread
              left join jivequestion question on question.threadid = jivethread.threadid
             
          Where    to_timestamp(jivethread.creationdate/1000) >= current_date - 180
          
          order by jivethread.threadid asc;
          

           

          2nd I pull all the view & response activities from our Staff members from Analytics DB:

          --Analytics - Staff Views on Threads YTD
          select 
              jivedw_object.object_id,
              jivedw_activity_fact.activity_ts,
              jivedw_user.username
              
          from     jivedw_activity_fact
          join     jivedw_object on jivedw_object.dw_object_id = jivedw_activity_fact.direct_dw_object_id
          left join     jivedw_user on jivedw_user.user_id = jivedw_activity_fact.user_id
          
          where 
               jivedw_object.object_type = 1 -- Threads Object type (reference Activity and Object Type Definitions)
          and    jivedw_activity_fact.activity_type = 10 --View activity Type (reference Activity and Object Type Definitions)
          and     jivedw_activity_fact.activity_ts >= current_date - 180
          and     jivedw_user.username like (E'tmo\\_%') --This is how we identify our staff member change for your needs
          
          order by 
              jivedw_object.object_id,
              jivedw_activity_fact.activity_ts;
          
          --QGP - Time to Staff response all threads
          select 
              jivedw_object.object_id as MessageID,
              obj2.object_id as ThreadID,
              jivedw_activity_fact.activity_ts,
              jivedw_user.username
              
          from     jivedw_activity_fact
          join     jivedw_object on jivedw_object.dw_object_id = jivedw_activity_fact.direct_dw_object_id
          join    jivedw_object obj2 on obj2.dw_object_id = jivedw_activity_fact.indirect_dw_object_id
          left join jivedw_user on jivedw_user.user_id = jivedw_activity_fact.user_id
          
          where 
               jivedw_object.object_type = 2 -- Threads Object type (reference Activity and Object Type Definitions)
          and    jivedw_activity_fact.activity_type = 20 --Create activity Type (reference Activity and Object Type Definitions)
          and     jivedw_activity_fact.activity_ts >= current_date - 180
          and     jivedw_user.username like (E'tmo\\_%') --This is how we identify our staff member change for your needs
          
          order by 
              jivedw_object.object_id, obj2.object_id,
              jivedw_activity_fact.activity_ts;
          

           

          I report the results at a weekly level and separate by our Hours of Operation for staff members. Here are the Excel steps/formulas for mashing everything together:

          1. Open data from both query results & cleanup and place on separate tabs
            • Ensure data is sorted by threadID ascending & Timestamp Old to New to ensure correct Vlookup value for next steps
          2. Add following columns to data from CoreDB
            • Question? - =IF(B2>0,"Yes","No")
            • Staff View? - =IFERROR(VLOOKUP(A2, [ANALYTICS DB Data Tab] ,3,FALSE),"NoView")
            • Staff View Time - =IFERROR(VLOOKUP(A2, [ANALYTICS DB Data Tab] ,2,FALSE),"NoView")
            • Time to View Calc - =IFERROR(I2-C2,"NoView")
            • Viewed w/in Week? - =IFERROR((I2-C2)<=7,"NoView")
            • PT Post time - =C2-7/24
            • Hour of Post PT - =HOUR(L2)
                • Note this needs to be adjusted for DST 
            • Hour of Operation? - =IF(AND(M2>=7,M2<=17),"Yes","No")

           

          Once you have all that completed a pivot or two later you can get something like this: (axis and data values scrubbed to protect the innocent)

           

          There is also a great thread about this from other users though its more conceptual:Re: Time to First Response in Support Communities / Service Level Agreements (SLA) I'll cross post if you don't mind so they can benefit from this as well.

           

          I hope that helps get you started. =)

          2 people found this helpful