Zendesk & Salesforce Query: Ticket count by status per paying-existing customers for the Quarter-To-Date (QTD).

Description: This query shows how many zendesk tickets per status a paying customer has. Paying customers are derived from Salesforce data and the ticket count is derived from the Zendesk data. User data from the two datasources are linked by email.

Requirements: Collect the `Lead` and `Opportunity` objects with the Panoply Salesforce data source and also the `Users`, `Organizations`, and `Tickets` Resources form the Zendesk data source.

Usage: This query can be displayed in a tabular or pivot form to display the ticket count per status.

Modifications: The table in the `FROM` might need to be changed based on Schema and Destination settings in the data source. The Date Range Filter using the `created_at` in the `WHERE` clause can be changed.

Query:

WITH paying_users AS (
  SELECT
    DISTINCT sfl."email"
  FROM
    public.salesforce_lead sfl
  JOIN
    public.salesforce_opportunity sfo
    ON sfl."convertedopportunityid" = sfo."id"
  WHERE sfo.stagename = 'Closed Won'
),
users AS (
  SELECT
    zu."id",
    zu."name",
    zu."email",
    CASE
      WHEN zo."name" IS NULL THEN zu."email"
      ELSE zo."name"
    END AS requester
  FROM
    public.zendesk_users zu
  LEFT JOIN
    public.zendesk_organizations zo
    ON zu."organization_id" = zo."id"
  JOIN
    public.paying_users apu
    ON apzu."email" = zu."email"
)
SELECT
  u."name",
  u."requester",
  COUNT(CASE WHEN zt."status" = 'new' THEN 1 END) AS "new",
  COUNT(CASE WHEN zt."status" = 'open' THEN 1 END) AS "open",
  COUNT(CASE WHEN zt."status" = 'pending' THEN 1 END) AS "pending",
  COUNT(CASE WHEN zt."status" = 'hold' THEN 1 END) AS "hold",
  COUNT(CASE WHEN zt."status" = 'solved' THEN 1 END) AS "solved",
  COUNT(CASE WHEN zt."status" = 'closed' THEN 1 END) AS "closed",
  COUNT(*) AS "total"
FROM
  public.zendesk_tickets zt
JOIN
  users u
  ON zt."submitter_id" = u."id"
WHERE
  DATE_TRUNC('quarter', zt."created_at") = DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY
  1,
  2
ORDER BY
  total DESC

Query Results Dictionary

Column: description
------------------------------
name: Name of Zendesk User
requester: Zendesk Organization or the Zendesk User Email if organization is not set.
new: Zendesk tickets count under the "new" status
open: Zendesk tickets count under the "open" status
pending: Zendesk tickets count under the "pending" status
hold: Zendesk tickets count under the "hold" status
solved: Zendesk tickets count under the "solved" status
closed: Zendesk tickets count under the "closed" status
total: Total tickets count