Help desk reporting for Zendesk

Last update: July 25, 2023

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User
NOTE
This is only available for clients that are on the Pro plan and using the 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. It also helps you know what type of customers are using your support platform. This topic demonstrates how to set up a dashboard to get granular reports about your Zendesk performance and tie in your transactional customers.

Before getting started, you want to connect your Zendesk. This analysis contains advanced calculated columns.

Getting Started

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:

Calculated Columns

Columns to create

  • Zendesk user's table

    • User is agent? (Yes/No)

      • Column type - Same Table > Calculation

      • Input columns - role, email

      • SQL Calculation - case when Ais notnullandA!=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_id8

    • One: Zendesk users.id

    • Select a table: Zendesk users

    • Select a 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 a 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 a 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 a table: Zendesk users

    • Select a column: email

    • Path: Zendesk tickets.requester_id = Zendesk users.id

    • Select a definition: Joined Column

    • Select a table: Zendesk users

    • Select a 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 a table: Zendesk audits

    • Select a column: created_at

    • Path: Zendesk audits.ticket_id = Zendesk tickets.id

    • Filter:

    • status changed to solved = 1

    • Select a definition: Min

    • Select a table: Zendesk audits

    • Select a 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

      • Column type - Same Table > Date Difference

      • Ticket's latest solved date minus created_at

  • Seconds to first response

      • Column type - Same Table > Date Difference

      • First agent response date minus created_at

  • Requester's ticket number

      • Column type - Same Table > Event Number

      • Event Owner - requester_id

      • Event Rank - created_at

  • 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)

      • 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 a 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)

      • 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 a table: customer_entity
    • Select a column: User's lifetime number of support tickets requested
    • Path: Zendesk tickets.email = customer_entity.email

  • Requester's lifetime number of support tickets

Metrics

  • 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 that are counted
    • 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.