Skip to main content
Reference

Email Delivery and Engagement Queries

Overview​

These queries read the emails array on uc_orders. Every transactional email UltraCart sends for an order is stored as one element of that array, carrying the subject, the recipient, the send time, and the outcome: delivered, opened, clicked, skipped, and the bounce type, sub-type, and diagnostic code when the message failed. Because it is a repeated record, every query here starts by flattening it with CROSS JOIN UNNEST.

This is the only place the delivery results can be aggregated. The Email Delivery diagnostic answers the question for one order, and the REST API answers it for one record per call. Neither will give you a rate across a quarter.

Five things about this data will produce quietly wrong numbers rather than an error.

partition_date is a weekly bucket, not a date. It is keyed to the week the order was created, so an order created on a given day sits in a partition up to six days earlier. Filtering partition_date = <a date> silently drops most of the week. Every query below filters partition_date seven days wider than the range it wants, then narrows on creation_dts. Keep both: the partition filter is what limits the bytes you are billed for, and the creation_dts filter is what makes the range correct.

Filter on the email, not the order stage. An order only sits in current_stage = 'Accounts Receivable' while it is unresolved. Once the payment succeeds or the order is rejected, it moves on, and a query keyed to that stage sees only the orders still stuck there. That is a small and heavily biased fraction of the customers who were emailed, and it makes recovery look far worse than it is. To measure an email program, select on the email.

There is no template identifier, only the subject line. Nothing in the array records which transactional template produced a message, so the subject is the only key available, and it is whatever you have customized it to. Subjects that embed the order ID differ on every row and cannot be grouped without normalizing them first. Run Delivery and Engagement Rates by Email first to read your own subject lines out of the data, then paste the one you want into the queries that follow.

clicked_dts is not populated. The clicked boolean is reliable; the timestamp beside it is not. Use the boolean and take timing from send_dts or opened_dts.

Open rates overstate real opens. Apple Mail Privacy Protection and corporate security scanners fetch tracking pixels automatically, without a person reading anything, and both are counted as opens. Treat opens as a trend line and judge engagement on clicks. The Email Delivery page explains what the signal is and is not worth.

note

bounce_diagnostic_code often contains the recipient's email address, quoted back by the receiving mail server. Treat query output containing it as customer data when you export it or paste it into a ticket.

Delivery and Engagement Rates by Email​

Returns one row per subject line sent in the last 90 days, with delivery, bounce, open, and click rates. Run this first: it tells you what your account actually sends and gives you the exact subject strings the rest of the queries need. Delivery and bounce rates are percentages of messages actually attempted, so skipped messages do not drag them down, while open and click rates are percentages of delivered messages. Internal notifications to your own staff are excluded, and the HAVING clause drops one-off subjects that carry an order ID.

SELECT
  e.subject,
  COUNT(*) AS sent,
  COUNTIF(e.delivered) AS delivered,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.delivered), COUNTIF(NOT e.skipped)), 1) AS delivered_pct,
  COUNTIF(e.bounce_dts IS NOT NULL) AS bounced,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.bounce_dts IS NOT NULL), COUNTIF(NOT e.skipped)), 1) AS bounced_pct,
  COUNTIF(e.opened) AS opened,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.opened), COUNTIF(e.delivered)), 1) AS opened_pct,
  COUNTIF(e.clicked) AS clicked,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.clicked), COUNTIF(e.delivered)), 1) AS clicked_pct,
  COUNTIF(e.skipped) AS skipped
FROM `my-data-warehouse.my_dataset.uc_orders` AS o
CROSS JOIN UNNEST(o.emails) AS e
WHERE o.partition_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 97 DAY)
  AND DATE(o.creation_dts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND NOT e.internal
GROUP BY e.subject
HAVING sent >= 25
ORDER BY sent DESC

If your subjects embed the order ID, group on a normalized subject instead of the raw one by replacing e.subject in both the SELECT and the GROUP BY with:

REGEXP_REPLACE(e.subject, r'[A-Z][A-Z0-9]{2,4}-\d+', '{order id}') AS subject

Billing Update Email Performance by Month​

Tracks one email's delivery and engagement month by month over the last 180 days. Written for the billing update email that goes to customers whose payment declined, which is the one worth watching closely because its failure costs revenue directly, but it works for any subject. Substitute your own subject line, taken from the query above. A bounce rate climbing month over month is the signal to act on: it means the list is decaying or the sending reputation is slipping, and it will keep getting worse on its own.

SELECT
  DATE_TRUNC(DATE(e.send_dts), MONTH) AS month,
  COUNT(*) AS sent,
  COUNTIF(e.delivered) AS delivered,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.delivered), COUNTIF(NOT e.skipped)), 1) AS delivered_pct,
  COUNTIF(e.bounce_dts IS NOT NULL) AS bounced,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.bounce_dts IS NOT NULL), COUNTIF(NOT e.skipped)), 1) AS bounced_pct,
  COUNTIF(e.opened) AS opened,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.opened), COUNTIF(e.delivered)), 1) AS opened_pct,
  COUNTIF(e.clicked) AS clicked,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(e.clicked), COUNTIF(e.delivered)), 1) AS clicked_pct
FROM `my-data-warehouse.my_dataset.uc_orders` AS o
CROSS JOIN UNNEST(o.emails) AS e
WHERE o.partition_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 187 DAY)
  AND DATE(o.creation_dts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND e.subject IN ('YOUR BILLING UPDATE SUBJECT')   -- <- from the query above
GROUP BY month
ORDER BY month

Why Billing Update Emails Bounce​

Groups the last 90 days of bounces for one email by cause, with an example of the diagnostic string the receiving server returned. The split matters because the causes need different responses. Permanent bounces are dead addresses and will never succeed. Permanent with a sub-type of Suppressed means the send never left UltraCart, because the address was already suppressed after earlier bounces. Transient with MailboxFull may clear on its own. Transient with ContentRejected is a content or reputation problem on your side, not a bad address, and it is the one worth investigating first because it affects deliverable customers.

SELECT
  e.bounce_type,
  e.bounce_sub_type,
  COUNT(*) AS bounces,
  ROUND(100 * SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER ()), 1) AS pct_of_bounces,
  ANY_VALUE(SUBSTR(e.bounce_diagnostic_code, 1, 160)) AS example_diagnostic
FROM `my-data-warehouse.my_dataset.uc_orders` AS o
CROSS JOIN UNNEST(o.emails) AS e
WHERE o.partition_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 97 DAY)
  AND DATE(o.creation_dts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND e.subject IN ('YOUR BILLING UPDATE SUBJECT')
  AND e.bounce_dts IS NOT NULL
GROUP BY e.bounce_type, e.bounce_sub_type
ORDER BY bounces DESC

Payment Recovery by Billing Update Email Engagement​

Buckets orders by how far the customer got with the billing update email, then reports how many of each bucket ended up paying. This is the query that tells you whether the email is doing its job, and which part of the funnel to fix. If recovery barely moves between not delivered and delivered, the email is not the constraint. If it jumps sharply for customers who clicked, the constraint is getting people to click, and the call to action is where to spend effort.

The window ends 30 days ago so every order in it has had time to reach a final state. Orders are counted by their furthest engagement, so an order that was opened and clicked appears only under clicked.

WITH orders_emailed AS (
  SELECT
    o.order_id,
    o.current_stage,
    (SELECT LOGICAL_OR(e.delivered) FROM UNNEST(o.emails) AS e
      WHERE e.subject IN ('YOUR BILLING UPDATE SUBJECT')) AS delivered,
    (SELECT LOGICAL_OR(e.opened) FROM UNNEST(o.emails) AS e
      WHERE e.subject IN ('YOUR BILLING UPDATE SUBJECT')) AS opened,
    (SELECT LOGICAL_OR(e.clicked) FROM UNNEST(o.emails) AS e
      WHERE e.subject IN ('YOUR BILLING UPDATE SUBJECT')) AS clicked
  FROM `my-data-warehouse.my_dataset.uc_orders` AS o
  WHERE o.partition_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 127 DAY)
    AND DATE(o.creation_dts) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 120 DAY)
                                 AND DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND EXISTS (SELECT 1 FROM UNNEST(o.emails) AS e
                WHERE e.subject IN ('YOUR BILLING UPDATE SUBJECT'))
)
SELECT
  CASE
    WHEN clicked   THEN '4. Clicked'
    WHEN opened    THEN '3. Opened, not clicked'
    WHEN delivered THEN '2. Delivered, not opened'
    ELSE                '1. Never delivered'
  END AS furthest_engagement,
  COUNT(*) AS orders,
  COUNTIF(current_stage IN ('Completed Order', 'Shipping Department')) AS payment_recovered,
  ROUND(100 * SAFE_DIVIDE(
    COUNTIF(current_stage IN ('Completed Order', 'Shipping Department')), COUNT(*)), 1) AS recovered_pct,
  COUNTIF(current_stage = 'Rejected') AS rejected,
  COUNTIF(current_stage = 'Accounts Receivable') AS still_in_ar
FROM orders_emailed
GROUP BY furthest_engagement
ORDER BY furthest_engagement

Read the result as correlation, not proof. A customer who intends to pay is more likely to click in the first place, so the gap between the clicked bucket and the others overstates what a better button alone would win you. The ranking still identifies the constraint even though the size of the effect is inflated.

Every Email Sent for One Order​

Returns the full delivery history for a single order, in send order, with every field the warehouse holds. This is the SQL equivalent of the Email Delivery diagnostic, useful when you are working a support case in a notebook or pulling the history into a ticket rather than reading it on screen.

SELECT
  e.send_dts,
  e.subject,
  e.internal,
  e.delivered,
  e.delivery_dts,
  e.opened,
  e.opened_dts,
  e.clicked,
  e.bounce_type,
  e.bounce_sub_type,
  e.bounce_diagnostic_code,
  e.skipped,
  e.skip_reason,
  e.smtp_response,
  e.message_id
FROM `my-data-warehouse.my_dataset.uc_orders` AS o
CROSS JOIN UNNEST(o.emails) AS e
WHERE o.order_id = 'DEMO-1234'   -- <- the order you are investigating
ORDER BY e.send_dts

This one scans the whole table, because order_id is not the partition key. Add a partition_date filter around the order's creation week if the cost matters.

Addresses That Never Receive Your Email​

Finds addresses that have been mailed repeatedly over the last 180 days, have bounced at least twice, and have never once been delivered to. Every future send to these addresses is wasted, and worse than wasted: continuing to mail addresses that hard bounce damages the sending reputation that decides whether your deliverable customers see anything. Feed the result into your list hygiene process, and check whether the same customers are being asked to update a payment method they will never be told about.

SELECT
  e.email,
  COUNT(*) AS attempts,
  MAX(e.send_dts) AS last_attempt,
  COUNTIF(e.bounce_dts IS NOT NULL) AS bounces,
  ANY_VALUE(e.bounce_type) AS bounce_type,
  ANY_VALUE(e.bounce_sub_type) AS bounce_sub_type
FROM `my-data-warehouse.my_dataset.uc_orders` AS o
CROSS JOIN UNNEST(o.emails) AS e
WHERE o.partition_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 187 DAY)
  AND DATE(o.creation_dts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND NOT e.internal
GROUP BY e.email
HAVING COUNTIF(e.delivered) = 0 AND bounces >= 2
ORDER BY attempts DESC, last_attempt DESC

e.email is plain text only in the ultracart_dw_medium and ultracart_dw_high datasets. In ultracart_dw and ultracart_dw_low the address is not exposed, so group on e.email_hash instead and match the hashes back against your own records.

Was this page helpful?