Skip to main content
How-to

Zoho Desk Ticket Reporting Queries

Overview​

These queries answer the questions merchants ask most often about their Zoho Desk tickets, from plain volume counts through to comparisons against order data that Zoho Desk cannot make on its own. Each one runs as written once you set the project name, and each explains what the result tells you.

Run them in the BigQuery console under your data warehouse project. If the column names or value conventions in a query are unfamiliar, the Zoho Desk Ticket Data Reference explains them.

Set your project name first​

Every query below reads from a project named after your UltraCart merchant ID in lowercase. Replace yourmerchantid throughout before running anything:

ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets
                ^^^^^^^^^^^^                                        <- your merchant ID, lowercase

Most queries here read ultracart_dw_low, which has customer personal information removed and is enough for counting and rating. The two that name agents or match customers to orders read ultracart_dw_medium instead, which requires the higher access level.

Each query also opens with two date variables. Change the defaults to move the reporting window.

Count tickets by month​

Start here. Support volume by month tells you whether demand is growing, and it is the baseline that makes every other number meaningful.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

SELECT
  DATE_TRUNC(DATE(created_time), MONTH) AS month,
  COUNT(*)                              AS tickets,
  COUNTIF(status_type = 'Closed')       AS closed,
  COUNTIF(status_type = 'Open')         AS still_open
FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets`
WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
  AND DATE(created_time) BETWEEN start_date AND end_date
  AND NOT is_spam
GROUP BY month
ORDER BY month;

Change MONTH to WEEK in the DATE_TRUNC call for a weekly view.

The earliest months often look artificially small because ticket history starts when the Zoho Desk integration was connected. Treat the first full month after that as your real baseline.

tip

The two-part date filter in the WHERE clause is doing the cost control. Keep both lines in any query you adapt from this page. Dropping the partition_date line makes BigQuery read every ticket you have ever had.

See which channels tickets arrive from​

Knowing where support demand actually lands tells you where staffing and self-service changes will pay off.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

SELECT
  UPPER(IFNULL(channel, '(none)'))                 AS channel,
  COUNT(*)                                         AS tickets,
  ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets`
WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
  AND DATE(created_time) BETWEEN start_date AND end_date
  AND NOT is_spam
GROUP BY channel
ORDER BY tickets DESC;

The UPPER() wrapper matters. Zoho Desk records the same channel with different capitalization depending on how the ticket was created, so without it you get Email and EMAIL as separate rows that each undercount.

Measure how long tickets take to close​

Median resolution time shows the typical customer experience. The 90th percentile shows the tail, which is usually where complaints come from.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

WITH resolved AS (
  SELECT
    DATE_TRUNC(DATE(created_time), MONTH)          AS month,
    DATETIME_DIFF(closed_time, created_time, HOUR) AS hours_to_close
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets`
  WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
    AND DATE(created_time) BETWEEN start_date AND end_date
    AND NOT is_spam
    AND closed_time IS NOT NULL
)
SELECT
  month,
  COUNT(*)                                           AS tickets_closed,
  APPROX_QUANTILES(hours_to_close, 100)[OFFSET(50)]  AS median_hours,
  APPROX_QUANTILES(hours_to_close, 100)[OFFSET(90)]  AS p90_hours
FROM resolved
GROUP BY month
ORDER BY month;

Only closed tickets are counted, so the most recent month understates itself while its slowest tickets are still open. Compare completed months against each other rather than reading the current one as final.

Check the age of your open backlog​

This is a snapshot of today rather than a trend. A backlog that is healthy overall can still hide a tail of tickets that have been open for months.

DECLARE oldest_open DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH);

WITH open_tickets AS (
  SELECT DATE_DIFF(CURRENT_DATE(), DATE(created_time), DAY) AS age_days
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets`
  WHERE partition_date >= DATE_SUB(oldest_open, INTERVAL 7 DAY)
    AND status_type <> 'Closed'
    AND NOT is_spam
)
SELECT
  CASE
    WHEN age_days <= 1  THEN '1. 0 to 1 days'
    WHEN age_days <= 3  THEN '2. 2 to 3 days'
    WHEN age_days <= 7  THEN '3. 4 to 7 days'
    WHEN age_days <= 30 THEN '4. 8 to 30 days'
    ELSE                     '5. Over 30 days'
  END      AS age_bucket,
  COUNT(*) AS open_tickets
FROM open_tickets
GROUP BY age_bucket
ORDER BY age_bucket;

The oldest_open variable sets how far back to look for stale tickets. Widen it if you suspect tickets have been sitting open for longer than two years.

Find tickets that took a long back-and-forth​

A ticket resolved in one reply costs far less than one that ran to eight. Grouping by conversation length shows how much of your volume is genuinely quick.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

WITH tickets AS (
  SELECT SAFE_CAST(thread_count AS INT64) AS replies
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets`
  WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
    AND DATE(created_time) BETWEEN start_date AND end_date
    AND NOT is_spam
)
SELECT
  CASE
    WHEN replies IS NULL THEN '(unknown)'
    WHEN replies <= 1    THEN '1 message'
    WHEN replies <= 3    THEN '2 to 3 messages'
    WHEN replies <= 6    THEN '4 to 6 messages'
    ELSE                      '7 or more messages'
  END      AS conversation_length,
  COUNT(*) AS tickets
FROM tickets
GROUP BY conversation_length
ORDER BY tickets DESC;

thread_count is stored as text, so SAFE_CAST is what makes the comparisons behave as numbers. Without it, 10 sorts before 9.

Compare workload across agents​

This query names agents, so it reads ultracart_dw_medium. Look at the two columns together. A high ticket count with a high median time means a different problem than a low count with a high median.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

WITH tickets AS (
  SELECT
    CONCAT(IFNULL(assignee.first_name, ''), ' ', IFNULL(assignee.last_name, '')) AS agent,
    assignee_id,
    status_type,
    DATETIME_DIFF(closed_time, created_time, HOUR) AS hours_to_close
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_medium.uc_zoho_desk_tickets`
  WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
    AND DATE(created_time) BETWEEN start_date AND end_date
    AND NOT is_spam
    AND assignee_id IS NOT NULL
)
SELECT
  agent,
  COUNT(*)                                           AS tickets_assigned,
  COUNTIF(status_type = 'Closed')                    AS tickets_closed,
  APPROX_QUANTILES(hours_to_close, 100)[OFFSET(50)]  AS median_hours_to_close
FROM tickets
GROUP BY agent
HAVING tickets_assigned >= 25
ORDER BY tickets_assigned DESC;

Agent names are built from first_name and last_name because assignee.name is empty even when the agent is known. The HAVING clause keeps agents with only a handful of tickets out of the result, since a median over five tickets is noise.

Resolution time varies by the kind of ticket an agent handles, so a slower median is not by itself a performance finding. Treat it as a prompt to look at what those tickets were about.

Compare support volume against order volume​

Raw ticket counts rise and fall with sales. Dividing tickets by orders separates the two and shows whether each sale is getting cheaper or more expensive to support. This is the first question that needs the data warehouse rather than Zoho Desk.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

WITH monthly_tickets AS (
  SELECT DATE_TRUNC(DATE(created_time), MONTH) AS month, COUNT(*) AS tickets
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets`
  WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
    AND DATE(created_time) BETWEEN start_date AND end_date
    AND NOT is_spam
  GROUP BY month
),
monthly_orders AS (
  SELECT DATE_TRUNC(DATE(creation_dts), MONTH) AS month, COUNT(*) AS orders
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_orders`
  WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
    AND DATE(creation_dts) BETWEEN start_date AND end_date
  GROUP BY month
)
SELECT
  month,
  o.orders,
  t.tickets,
  ROUND(t.tickets / NULLIF(o.orders, 0), 3) AS tickets_per_order
FROM monthly_orders o
FULL JOIN monthly_tickets t USING (month)
ORDER BY month;

A rising tickets_per_order while orders fall is worth investigating. It usually points at a product, shipping, or billing change rather than at the support team.

See whether high-value customers contact support more​

Matching tickets to customers by email address shows how support load is distributed across your customer base. The answer often reshapes how a team thinks about staffing.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();

WITH customer_value AS (
  SELECT
    LOWER(billing.email)     AS email,
    SUM(summary.total.value) AS lifetime_spend
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_medium.uc_orders`
  WHERE billing.email IS NOT NULL
  GROUP BY email
),
customer_tickets AS (
  SELECT LOWER(email) AS email, COUNT(*) AS tickets
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_medium.uc_zoho_desk_tickets`
  WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
    AND DATE(created_time) BETWEEN start_date AND end_date
    AND NOT is_spam
    AND email IS NOT NULL
  GROUP BY email
)
SELECT
  CASE
    WHEN v.lifetime_spend <  100 THEN '1. Under 100'
    WHEN v.lifetime_spend <  250 THEN '2. 100 to 250'
    WHEN v.lifetime_spend <  500 THEN '3. 250 to 500'
    WHEN v.lifetime_spend < 1000 THEN '4. 500 to 1000'
    ELSE                              '5. 1000 and up'
  END                                            AS lifetime_value_band,
  COUNT(*)                                       AS customers,
  SUM(IFNULL(t.tickets, 0))                      AS tickets,
  ROUND(SUM(IFNULL(t.tickets, 0)) / COUNT(*), 3) AS tickets_per_customer
FROM customer_value v
LEFT JOIN customer_tickets t USING (email)
GROUP BY lifetime_value_band
ORDER BY lifetime_value_band;

Both email addresses are lowercased before matching, because customers type their address inconsistently across a support form and a checkout. Expect a small share of tickets to find no matching order: those are prospects, wrong addresses, or people who ordered under a different email.

The uc_orders side has no date filter, since lifetime spend means every order. That makes this the most expensive query on the page. Adding a partition_date lower bound narrows it to recent customers if cost is a concern.

Was this page helpful?