Magento Business Intelligence Help Center

Help desk reporting: Zendesk

Consolidating your Zendesk data with your transactional database is an excellent way to better understand how your customers are interacting with your sales or customer success teams and what type of customers are utilizing your support platform. In this article, we will demonstrate how to set up a dashboard to get granular reports about your Zendesk performance and tie in your transactional customers.

Before getting started, you'll want to connect your Zendesk. 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 [ZENDESK ANALYSIS], before getting started with this analysis.

Columns to track

  • audits table
    • _id
    • created_at
    • id
    • ticket_id
    • _updated_at
  • audits_~_events table
    • _sub_id
    • _id_of_parent
    • author_id
    • field_name
    • public
    • type
    • value
  • tickets table
    • _id
    • assignee_id
    • created_at
    • id
    • requester_id
    • status
    • updated_at
    • via_~_source_~_from_~_address
    • _updated_at
  • users table
    • _id
    • created_at
    • emails
    • id
    • role
    • updated_at
    • _updated_at

Filter sets to create

  • [Zendesk] Tickets table
    • Filter set name: Tickets we count
    • Filter set logic:
      • status != deleted

Columns to create

  • [Zendesk] user's table
    • User is agent? (Yes/No) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
  • [Zendesk] audits_~_events table
    • Author is agent? (Yes/No)
      • Select a definition: Joined Column
      • Create Path:
        • Many: [Zendesk] audits_~_events.author_id
        • One: [Zendesk] users.id
      • Select table: [Zendesk] users
      • Select column: User is agent? (Yes/No)
      • Path: [Zendesk] audits_~_events.author_id = [Zendesk] users.id
  • [Zendesk] audits table
    • Status changes to solved? (1/0)
      • Select a definition: Exists
      • Create Path:
        • Many: [Zendesk] audits_~_events._id_of_parent
        • One: [Zendesk] audits._id
      • Select table: [Zendesk] audits_~_events
      • Path: [Zendesk] audits_~_events._id_of_parent = [Zendesk] audits._id
      • Filter:
        • field_name = status
        • type = Change
        • value = solved
    • Is agent comment? (1/0)
      • Select a definition: Exists
      • Select table: [Zendesk] audits_~_events
      • Path: [Zendesk] audits_~_events._id_of_parent = [Zendesk] audits._id
      • Filter:
        • Author is agent? (Yes/No)
        • type = Comment
        • public = 1
  • [Zendesk] Tickets table
    • Requester's email
      • Select a definition: Joined Column
      • Create Path:
        • Many: [Zendesk] tickets.requester_id
        • One: [Zendesk] users.id
      • Select table: [Zendesk] users
      • Select column: email
      • Path: [Zendesk] tickets.requester_id = [Zendesk] users.id
    • Requester's role
      • Select a definition: Joined Column
      • Select table: [Zendesk] users
      • Select column: role
      • Path: [Zendesk] tickets.requester_id = [Zendesk] users.id
    • Ticket's latest solved date
      • Select a definition: Max
      • Create Path:
        • Many: [Zendesk] audits.ticket_id
        • One: [Zendesk] tickets.id
      • Select table: [Zendesk] audits
      • Select column: created_at
      • Path: [Zendesk] audits.ticket_id = [Zendesk] tickets.id
      • Filter:
        • status changed to solved = 1
    • First agent response date
      • Select a definition: Min
      • Select table: [Zendesk] audits
      • Select column: created_at
      • Path: [Zendesk] audits.ticket_id = [Zendesk] tickets.id
      • Filter:
        • Is agent comment? = 1
    • Seconds to resolution will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket ^
    • Seconds to first response will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket ^
    • Requester's ticket number will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
    • Ticket created_at (hour of day) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
    • Ticket created_at (day of week) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
  • customer_entity table
    • User's lifetime number of support tickets requested
      • Select a definition: Count
      • Create Path:
        • Many: [Zendesk] tickets.email
        • One: customer_entity.email
      • Select table: [Zendesk] tickets
      • Path: [Zendesk] tickets.email = customer_entity.email
      • Filter:
        • Tickets we count
    • Has user filed a support ticket? (Yes/No) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
  • [Zendesk] Tickets table
    • Requester's lifetime number of support tickets
      • Select a definition: Joined Column
      • Select table: customer_entity
      • Select column: User's lifetime number of support tickets requested
      • Path: [Zendesk] tickets.email = customer_entity.email

^ If you are interested in analyzing only business hours for Seconds to resolution or Seconds to first response, let the analyst know when requesting the ticket.

  • [Zendesk] New tickets
    • In the [Zendesk] tickets table
    • This metric performs a Count
    • On the id column
    • Ordered by the created_at timestamp
    • Filter:
      • Tickets we count
  • [Zendesk] Solved tickets
    • In the [Zendesk] tickets table
    • This metric performs a Count
    • On the id column
    • Ordered by the created_at timestamp
    • Filter:
      • Tickets we count
      • status IN closed, solved
  • [Zendesk] Distinct users filing tickets
    • In the [Zendesk] tickets table
    • This metric performs a Count Distinct
    • On the requester_id column
    • Ordered by the created_at timestamp
    • Filter:
      • Tickets we count
  • [Zendesk] Average/median ticket resolution time
    • In the [Zendesk] tickets table
    • This metric performs an Average (or Median)
    • On the Seconds to resolution column
    • Ordered by the created_at timestamp
    • Filter:
      • Tickets we count
      • status IN closed, solved
  • [Zendesk] Average/median time to first response
    • In the [Zendesk] tickets table
    • This metric performs an Average (or Median)
    • On the Seconds to first response column
    • Ordered by the created_at timestamp
    • Filter:
      • Tickets we count
      • status IN closed, solved

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

  • New/Open/Pending tickets
    • Metric A: New tickets
      • Metric: New Tickets
      • Filter:
        • status IN new, open, pending
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Closed/Solved tickets
    • Metric A: New tickets
      • Metric: New Tickets
      • Filter:
        • status IN solved, closed
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Average time to first response
    • Metric A: Average time to first response
      • Metric: Average time to first response
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Average time to resolution
    • Metric A: Averae time to resolution
      • Metric: Average time to resolution
      • Filter:
        • status IN solved, closed
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Tickets by status
    • Metric A: New tickets
      • Metric: New Tickets
    • Time period: All time
    • Interval: Monthly
    • Group by: status
    • Chart Type: Stacked Column
  • Number of new and solved tickets
    • Metric A: New tickets
      • Metric: New Tickets
    • Metric B: Solved tickets
      • Metric: New Tickets
    • Time period: All time
    • Interval: Monthly
    • Chart Type: Line
  • Time to first response
    • Metric A: Average time to first response
      • Metric: Average time to first response
    • Time period: All time
    • Interval: Monthly
    • Chart Type: Column
  • Time to resolution
    • Metric A: Averae time to resolution
      • Metric: Average time to resolution
      • Filter:
        • status IN solved, closed
    • Time period: All time
    • Interval: Monthly
    • Chart Type: Column
  • Distinct users filing tickets
    • Metric A: Distinct users filing tickets
      • Metric: Distinct users filing tickets
    • Time period: All time
    • Interval: Monthly
    • Chart Type: Column
  • Peak ticket days
    • Metric A: New tickets
      • Metric: New Tickets
    • Time period: All time
    • Interval: None
    • Group by: Ticket created_at (day of week)
    • Chart Type: Pie
  • Peak ticket hours
    • Metric A: New tickets
      • Metric: New Tickets
    • Time period: All time
    • Interval: None
    • Group by: Ticket 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 tickets
    • Metric A: Average lifetime revenue
      • Metric: Average lifetime revenue
    • Time period: All time
    • Interval: Monthly
    • Group by: User has filed a support ticket?
    • Chart Type: Column
  • Number of new users who have and have not filed tickets
    • Metric A: New users
      • Metric: Users
    • Time period: All time
    • Interval: Monthly
    • Group by: User has filed a support ticket?
    • Chart Type: Column

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