Magento Business Intelligence Help Center

Help desk reporting: Zendesk

Note: This article contains instructions for clients that are utilizing the original architecture and new architecture. You are on the new architecture if you have the "Data Warehouse Views" section available after selecting "Manage Data" from the main toolbar.

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
    • status != deleted
    • Filter set name: Tickets we count
    • Filter set logic:

Columns to create

  • [Zendesk] user's table
  • Original architecture
    • User is agent? (Yes/No) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
  • New architecture
    • User is agent? (Yes/No) 
    • * Column type - "Same Table -> Calculation"

      * Input columns - `role`, `email`

      SQL Calculation - `case when A is not null and A!='end-user' then 'Yes' when B is not null and B like '%@magento.com' then 'Yes' else 'No' end`

      * Replace '@magento.com' with your domain

      * Datatype - String

  • [Zendesk] audits_~_events table
    • 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
    • Author is agent? (Yes/No)
  • [Zendesk] audits table
    • 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
    • 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
    • Status changes to solved? (1/0)
    • Is agent comment? (1/0)
  • [Zendesk] Tickets table
    • 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
    • Select a definition: Joined Column
    • Select table: [Zendesk] users
    • Select column: role
    • Path: [Zendesk] tickets.requester_id = [Zendesk] users.id
    • 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
    • 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
    • Requester's email
    • Requester's role
    • Ticket's latest solved date
    • First agent response date
    • Seconds to resolution will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket ^
    • New architecture Seconds to resolution
      • * Column type - "Same Table -> Date Difference"

        * `Ticket's latest solved date` minus `created_at`

    • Seconds to first response will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket ^
    • New architecture Seconds to first response
      • * Column type - "Same Table -> Date Difference"

        * `First agent response date` minus `created_at`

    • Requester's ticket number will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
    • New architecture Requester's ticket number 
      • * Column type - "Same Table -> Event Number"

        * Event Owner - `requester_id`

        * Event Rank - `created_at`

    • Ticket created_at (hour of day) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
    • New architecture Ticket created_at (hour of day)
      • * Column type - "Same Table -> Calculation"

        * Input columns - `created_at`

        * SQL Calculation - `to_char(A,'HH24')::int

        * Datatype – Integer

    • Ticket created_at (day of week) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
    • New architecture Ticket created_at (day of week)
      • * Column type - "Same Table -> Calculation"

        * Input columns - `created_at`

        * Calculation - `to_char(A,'D')||'. '||to_char(A,'Day')`

        * Datatype – String

  • customer_entity table
    • 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
    • User's lifetime number of support tickets requested
    • Has user filed a support ticket? (Yes/No) will be created by an analyst as part of your [ZENDESK ANALYSIS] ticket
    • New architecture Has user filed a support ticket? (Yes/No)
      • * Column type - "Same Table -> Calculation"

        * Input columns - `User's lifetime number of support tickets requested`

        * Calculation - `case when A>0 then 'Yes' else 'No' end`

        * Datatype – String

  • [Zendesk] Tickets table
    • 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
    • Requester's lifetime number of support tickets

^ 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
    • Tickets we count
    • In the [Zendesk] tickets table
    • This metric performs a Count
    • On the id column
    • Ordered by the created_at timestamp
    • Filter:
  • [Zendesk] Solved tickets
    • Tickets we count
    • status IN closed, solved
    • In the [Zendesk] tickets table
    • This metric performs a Count
    • On the id column
    • Ordered by the created_at timestamp
    • Filter:
  • [Zendesk] Distinct users filing tickets
    • Tickets we count
    • In the [Zendesk] tickets table
    • This metric performs a Count Distinct
    • On the requester_id column
    • Ordered by the created_at timestamp
    • Filter:
  • [Zendesk] Average/median ticket resolution time
    • Tickets we count
    • status IN closed, solved
    • 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:
  • [Zendesk] Average/median time to first response
    • Tickets we count
    • status IN closed, solved
    • 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:

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

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