Magento Business Intelligence Help Center

Help desk reporting: Desk.com

In this article, we demonstrate how to set up a dashboard that will analyze your customer service cases. We'll also show you how to join this data to your Magento store to determine CLV of customers who have and have not filed a request.

Before getting started, you'll want to connect your desk account.

This analysis contains advanced calculated columns that can only be created by a Magento BI analyst. Once you've completed the Getting Started section, file a support request with the subject [DESK.COM ANALYSIS], before moving to the next section.

Columns to track

  • cases table
    • _id
    • created_at
    • customer_id
    • Id
    • Priority
    • resolved_at
    • Status
    • type
    • _updated_at
  • cases_~_labels table
    • All fields
  • customers table
    • _id
    • created_at
    • Id
    • primary_email
    • _updated_at
  • replies table
    • _id
    • case_id
    • created_at
    • customer_id
    • Direction
    • hidden
    • Status
    • updated_at
    • _updated_at

Filter sets to create

  • [Desk] Cases table
    • Cases we count
      • status != deleted
      • Any additional filter logic you would like
  • [Desk] Replies table
    • Latest of created_at or updated_at will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
  • [Desk] Cases table
    • Customer's email
      • Select a definition: Joined Column
      • Create Path:
        • Many: [Desk] cases.customer_id
        • One: [Desk] customers._id
      • Select table: [Desk] customers
      • Select column: primary_email
      • Path: [Desk] cases.customer_id = [Desk] customers._id
    • Case created_at (hour of day) will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
    • Case created_at (day of week) will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
    • User's case number will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
    • Seconds to resolution will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
    • Seconds to resolution (business hours only) will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
    • First agent response date
      • Select a definition: Minimum value
      • Create Path:
        • Many: [Desk] replies.case_id
        • One: [Desk] cases.id
      • Select table: [Desk] replies
      • Select column: latest of created_at or updated_at
      • Path: [Desk] replies.case_id = [Desk] cases.id
      • Filters
        • hidden != 1
        • direction != out
        • status = sent
        • created_at IS NOT null
    • Seconds to first response will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
    • Seconds to first response (business hours only) will be created by an analyst as part of your [DESK.COM ANALYSIS] ticket
  • Customer_entity table
    • User's lifetime number of support cases requested
      • Select a definition: Count
      • Create Path:
        • Many: [Desk] cases.Customer's email
        • One: customer_entity.customer_email
      • Select table: [Desk] cases
      • Path: [Desk] cases.customer's email = customers_entity.customer_email
      • Filter: Tickets we count
    • User has filed support case? (1=yes/0=no)
      • Select a definition: Exists
      • Select table: [Desk] cases
      • Path: [Desk] cases.customer's email = customers_entity.customer_email
      • Filter: Tickets we count
  • [Desk] Cases table
    • Requester's lifetime number of support cases
      • Select a definition: Joined column
      • Select table: customer_entity
      • Select column: User's lifetime number of support cases requested
      • Path: [Desk] cases.customer's email = customers_entity.customer_email
  • [Desk] New cases
    • In the [Desk] Cases table
    • This metric performs a Count
    • On the id column
    • Ordered by the created_at timestamp
    • Filter:
      • Cases we count
  • [Desk] Solved cases
    • In the [Desk] Cases table
    • This metric performs a Count
    • On the id column
    • Ordered by the resolved_at timestamp
    • Filter:
      • Cases we count
      • status IN closed, resolved
  • [Desk] Distinct users filing cases
    • In the [Desk] Cases table
    • This metric performs a Count Distinct Values
    • On the customer_id column
    • Ordered by the created_at timestamp
    • Filter:
      • Cases we count
  • [Desk] Average time to resolution
    • In the [Desk] Cases table
    • This metric performs an Average
    • On the Seconds to resolution column
    • Ordered by the created_at timestamp
    • Filtering for:
      • Cases we count
      • status IN closed, resolved
  • [Desk] Average time to first response
    • In the [Desk] Cases table
    • This metric performs an Average
    • On the Seconds to first response column
    • Ordered by the created_at timestamp
    • Filtering for: Cases we count

Note: Make sure to add all new columns as dimensions to metrics before building new reports.

  • New/Open/Pending cases
    • Metric A: New cases
      • Metric: New cases
      • Filter: status IN new, open, pending
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Closed/Solved cases
    • Metric A: New cases
      • Metric: New cases
      • Filter status IN resolved, closed
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Average time to resolution
    • Metric A: Average time to resolution
      • Metric: Average time to resolution
      • status IN resolved, closed
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Cases by status
    • Metric A: New cases
      • Metric: New cases
    • Time period: All time
    • Interval: None
    • Group by: status
    • Chart Type: Column
  • Number of new and solved cases
    • Metric A: New cases
      • Metric: New cases
    • Metric B: Solved cases
      • Metric: Solved cases
    • Time period: All time
    • Interval: Monthly
    • Chart Type: Line
  • Time to resolution
    • Metric A: Average time to resolution
      • Metric: New cases
      • Filter: status IN resolved, closed
    • Time period: All time
    • Interval: Month
    • Chart Type: Column
  • Time to first response
    • Metric A: Average time to first response
      • Metric: Average time to first response
    • Time period: All time
    • Interval: Month
    • Chart Type: Column
  • Distinct users filing cases
    • Metric A: Distinct users filing cases
      • Metric: Distinct users filing cases
    • Time period: All time
    • Interval: Month
    • Chart Type: Column
  • Peak case days
    • Metric A: New cases
      • Metric: New cases
    • Time period: All time
    • Interval: None
    • Group by: created_at (day of the week)
    • Chart Type: Pie
  • Peak case hours
    • Metric A: New cases
      • Metric: New cases
    • Time period: All time
    • Interval: None
    • Group by: created_at (hour of the day)
      • Show top/bottom: Top 100% sorted by created_at (hour of the day)
    • Chart Type: Pie
  • Avg LTV of users who have and have not filed cases
    • Metric A: Average LTV
      • Metric: Average lifetime revenue
    • Time period: All time
    • Interval: Month
    • Group by: User has filed a support case? (1=yes/0=no)
    • Chart Type: Column
  • Number of new users who have and have not filed cases
    • Metric A: New users
      • Metric: New cases
    • Time period: All time
    • Interval: Month
    • Group by: User has filed a support case? (1=yes/0=no)
    • Chart Type: Column

After compiling all the reports, you can organize them on the dashboard as you desire.

If you run into any questions while building this analysis, or simply want to engage our professional services team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk