Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Mehrzad Kootar
    Consolidated Dashboard - Analytics Cookbook Recipe
    Topic posted January 10, 2017 by Mehrzad KootarRed Ribbon: 250+ Points, last edited June 22, 2017 
    2073 Views, 3 Comments
    Title:
    Consolidated Dashboard - Analytics Cookbook Recipe
    Content:





    Consolidated Dashboard



    Rolling up various KPI metrics in a common view



     



    Author: Mehrzad Kootar



    Difficulty rating: (Intermediate)



    Target persona:  Managers, Supervisor, Analyst and  Executive



     



    Overall description/purpose:



    Customers seldom have the need to be able to see a variety of metrics in one report view. Due to the heavy computation of certain metrics and the inability to view in a single report causes for multiple reports to be built separately. The consolidated dashboard feature helps build individual reports which can be rolled up into a consolidated view



    Capability Highlights



    • Combine reports into a consolidated view
    • Format entire consolidated report view
    • Utilize existing Analytics runtime options in report


    Key Benefits



    • Analyze combined and formatted metrics easily
    • Simplify report join tree for specific use cases
    • Improve report performance for disparate data set reporting in parallel


    Acronyms in this recipe are as follows:



    • AHT - Average Handle Time (a measure of the time a user has an open incident assigned to them)
    • CSAT - Customer Satisfaction (average survey scores that a customer has submitted for specific survey and question).
    • C2FR - Create to Final Resolve (the time that an incident spent in an open state (i.e. incidents.closed - incidents.created) as found in the inc_performance table as intv_type = create to final resolved).


    Use case being served:



    Service Manager needs to compare how each of their Groups are performing with respect to the service metrics i.e. average service handle time (AHT), customer satisfaction scores (CSAT) and time to resolve an incident . These metrics often need to be compared side by side in order to get a view of which groups are performing better than the other and if there is a correlation between these metrics i.e is a low AHT leading to a bad CSAT and vice versa.



    Report 1: AHT by Group



    Ingredients:



    2 Tables (Incidents and User Transactions ).



    1 Inner Joins



    Columns using formulas ( count distinct ()date_diff(), avg(), date_group(), to_number() &



    to_char() )



    Filters 



    Rollup



    Variable (for date_grouping)



    Screen shot: Report 1: AHT by Group





    This report helps calculate the average handle time (AHT) by the Group and is rolled up by the date



     



    Report 2 : CSAT by Group



    Ingredients:



    6 Tables (Surveys, Flows, Question Sessions, incidents, question responses, and question choices).



    5 Inner Joins



    Columns 



    Filters (2 fixed, 4 runtime selectable)



    Slice 



    Screen shot: Report 2: CSAT by Group





    This report helps group the CSAT Scores by groups and is sliced  up by the date



     



    Report 3: Create to Final Resolve by Group



    Ingredients:



    1 Table (Incident Performance  ).



    Columns using formulas (  date_diff(), sum(), count() )



    Filters 



    Rollup



    Variable



    Screen shot: Report 3: Create to Final Resolve by Group





    The Create to Final Resolve report help capture the time when an incident was created to when it was finally resolved



    Comparison of Traditional dashboard vs Consolidated dashboard :



    Traditional dashboard can combine a variety of reports and display it . If a complex report takes a while to load independently it will also carry the lag in the traditional dashboard. In the consolidated dashboard you can split reports into independent reports and roll up the data into one report. This helps with the performance of the report as behind the scene there are 3 separate reports running to merge the data into one complex report . Consolidated dashboard will also inherit the format  of the parent or the first report irrespective of the format of the child reports. As you can see above the Report 2 CSAT by Group is sliced by date and the first report AHT by Group is a roll up. So the consolidated dashboard would inherit the reporting properties of AHT by Group .



    Screen shot: Dashboard: Consolidated KPI view





    Special functionality usage: This recipe uses a new feature that has been introduced called consolidated dashboard layout which is a part of the Feb 2016 release of Service Cloud Agent





    Desktop



    There are a few things to keep in mind while building the report



    “UNION like” requirements:




    • All reports must contain same # of columns



    • Same data types for columns across all reports



    • e.g. Report 1, column 3 is a text field…



    • Column 3 must be a text field across all reports




    Vertical layouts only




    • Top to bottom orientation only in consolidation



    • No side-by-side report orientations allowed



    • Vertical layout enforced with consolidation option




    Please add formatting example (top report formats the consolidation).



    • Rollups vs slice
    • Conditional formatting
    • column data formatting (hr/mn/sec vs float)


    High level flow for report creation (optional): The KPI dashboard has a combination of the three reports as mentioned above. After adding the reports in the layout each individual report will get rolled up and grouped into one. In the example above all the metrics are rolled up by date .



     Master Chef Approved (a.k.a. Report Validated)? Yes



    Attachments included are the following (in a .ZIP file):



    • Human readable PDF report definition
    • Report Configuration Details (Word Doc)
    • XML Report definition 
    • Screen shot 


    Comment

     

    • Barrilito van Dijk

      Hi Mehrza,

      Thank you for yet another cookbook!

      Regards

    • Erica (Leep) Anderson

      If you found this report recipe helpful, please give Merzhad's post a thumbs up and leave a comment letting him know you appreciate his help, how this report helped you and your organization, and/or how you changed this report to fit your unique requirements.

      If you have a question about this report recipe, please start a new topic in the "Reporting and Analysis" forum board and link to this report recipe in your question. DO NOT post your reporting question here. We will remove any comments asking for help in the comments of this post.

      Thanks,
      Erica, Community Manager

    • Jairo Rojas Mendez

      hank you  Mehrzad Kootar for your contribution to the Analytics Cookbook!!! cheeky