Skip to main content
How-to

Reporting on Zoho Desk Custom Fields

Overview​

Custom fields are where the most useful reporting usually hides. A checkbox your agents tick when a refund was avoidable, a dropdown recording why a subscription was cancelled, a note explaining why a suggested reply was wrong: none of that exists in the standard ticket columns, and all of it arrives in your data warehouse.

Because every merchant defines their own fields, the queries here are templates. You supply the field name and the query works unchanged. If you have not read Zoho Desk Ticket Data Reference, the section on how custom fields arrive is worth reading first, since custom fields do not behave like ordinary columns.

Replace yourmerchantid in every query with your UltraCart merchant ID in lowercase before running it.

List the custom fields you actually have​

Run this before anything else. It lists every custom field arriving from your Zoho Desk account and, more usefully, shows which ones agents are genuinely filling in.

SELECT
  field.key                                   AS field_api_name,
  COUNT(DISTINCT t.id)                        AS tickets_with_field,
  COUNTIF(field.value <> 'null')              AS tickets_filled_in,
  COUNT(DISTINCT NULLIF(field.value, 'null')) AS distinct_values
FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets` AS t,
     UNNEST(t.cf) AS field
GROUP BY field_api_name
ORDER BY tickets_filled_in DESC;

Read the results this way:

  • tickets_with_field counts tickets where the field exists at all. A field added recently shows a lower number than one that has always been on the layout.
  • tickets_filled_in counts tickets where someone actually entered a value. A field with a high tickets_with_field and a tickets_filled_in of zero is on the layout but unused, and no report built on it will return anything.
  • distinct_values separates checkboxes and dropdowns, which have a handful of values, from free-text fields, which have nearly as many values as tickets.

The field_api_name values are what you paste into the queries below. They start with cf_.

warning

A field that shows tickets_filled_in as zero is the most common reason a custom field report comes back empty. Check this query before assuming a reporting problem.

Find the display name behind an API name​

API names are not always obvious. cf_cb_1 tells you nothing about what the field is for. Zoho Desk derives the API name from the label the field had when it was created, so matching on that pattern recovers most of the mapping:

WITH api_names AS (
  SELECT DISTINCT field.key AS field_api_name
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets` AS t,
       UNNEST(t.cf) AS field
),
display_names AS (
  SELECT DISTINCT field.key AS field_display_name
  FROM `ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets` AS t,
       UNNEST(t.custom_fields) AS field
)
SELECT
  a.field_api_name,
  d.field_display_name
FROM api_names a
LEFT JOIN display_names d
  ON LOWER(REPLACE(REGEXP_REPLACE(a.field_api_name, r'^cf_', ''), '_', ' '))
     = LOWER(d.field_display_name)
ORDER BY a.field_api_name;

A blank field_display_name means that field has been renamed in Zoho Desk since it was created, so its API name no longer resembles its label. Those are the ones to look up directly under Setup > Customization > Layouts and Fields in Zoho Desk.

warning

The cf and custom_fields arrays are not in the same order as each other, so pairing them by position produces confident and completely wrong answers. Match on the name pattern above instead, which leaves a renamed field blank rather than mislabelling it.

Use the API name in your reports. Display names change whenever someone renames a field in the Zoho Desk admin screens, and a renamed field silently breaks a saved query.

Report on a checkbox field over time​

Checkbox fields are the most useful custom fields for tracking a rate, such as how often a refund was avoidable or how often a ticket needed escalation. Set field_name to your field and run it.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();
DECLARE field_name STRING DEFAULT 'cf_your_boolean_field';  -- <- your field API name

WITH tickets AS (
  SELECT
    DATE_TRUNC(DATE(created_time), MONTH)                AS month,
    (SELECT value FROM UNNEST(cf) WHERE key = field_name) AS field_value
  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
  month,
  COUNTIF(field_value = 'true')  AS yes_count,
  COUNTIF(field_value = 'false') AS no_count,
  COUNTIF(field_value = 'null')  AS not_answered,
  ROUND(
    100 * COUNTIF(field_value = 'true')
    / NULLIF(COUNTIF(field_value IN ('true', 'false')), 0), 1) AS yes_rate_pct
FROM tickets
GROUP BY month
ORDER BY month;

The not_answered column is the one to watch. The rate is calculated only over tickets that were answered either way, so a month where agents stopped filling the field in produces a rate that looks stable while resting on almost no data. A sudden improvement in a rate is very often a drop in answering rather than a change in what happened.

Checkbox values are the strings 'true' and 'false' rather than BigQuery booleans, which is why every comparison here is quoted.

Report on a dropdown or picklist field​

Dropdown fields work the same way, with the values grouped instead of counted as yes and no. This tells you which options agents actually pick.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();
DECLARE field_name STRING DEFAULT 'cf_your_dropdown_field';  -- <- your field API name

WITH tickets AS (
  SELECT NULLIF((SELECT value FROM UNNEST(cf) WHERE key = field_name), 'null') AS field_value
  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
  IFNULL(field_value, '(not answered)')            AS answer,
  COUNT(*)                                         AS tickets,
  ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM tickets
GROUP BY answer
ORDER BY tickets DESC;

Zoho Desk multi-select fields put every chosen option into one value separated by semicolons, so Other;Return Instructions arrives as a single row rather than two. Splitting them needs UNNEST(SPLIT(field_value, ';')) in place of the plain column.

Rank the answers in a free-text field​

Free-text fields hold the reasons behind a number, which is usually what you want once a rate tells you something is wrong. This query reads ultracart_dw_medium, because free-text answers can contain customer details and are removed from the lower datasets.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();
DECLARE field_name STRING DEFAULT 'cf_your_text_field';  -- <- your field API name

WITH tickets AS (
  SELECT NULLIF((SELECT value FROM UNNEST(cf) WHERE key = field_name), 'null') AS field_value
  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
)
SELECT
  LOWER(TRIM(field_value)) AS answer,
  COUNT(*)                 AS tickets
FROM tickets
WHERE field_value IS NOT NULL
GROUP BY answer
ORDER BY tickets DESC
LIMIT 25;

Lowercasing and trimming merges the most obvious duplicates. Genuine variations in wording still split, so a reason written three different ways appears as three rows. Read the top of the list for themes rather than treating the counts as exact.

Break a custom field down by classification or agent​

Once a field shows a rate worth acting on, the next question is where it concentrates. Adding a grouping column to any of the queries above answers that:

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);
DECLARE end_date   DATE DEFAULT CURRENT_DATE();
DECLARE field_name STRING DEFAULT 'cf_your_boolean_field';  -- <- your field API name

WITH tickets AS (
  SELECT
    IFNULL(classification, '(unclassified)')              AS classification,
    (SELECT value FROM UNNEST(cf) WHERE key = field_name) AS field_value
  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
  classification,
  COUNT(*)                      AS tickets,
  COUNTIF(field_value = 'true') AS yes_count,
  ROUND(
    100 * COUNTIF(field_value = 'true')
    / NULLIF(COUNTIF(field_value IN ('true', 'false')), 0), 1) AS yes_rate_pct
FROM tickets
GROUP BY classification
HAVING tickets >= 50
ORDER BY yes_rate_pct DESC;

A result where a few classifications carry every positive and the rest sit at zero usually means the field is only being used on those ticket types, not that the others are problem-free. Confirm which before drawing a conclusion. Swap classification for the agent name from Zoho Desk Ticket Reporting Queries to see the same breakdown by person, remembering that agent names need the ultracart_dw_medium dataset.

Was this page helpful?