Reference

Order and Revenue Queries

Overview

These queries read the uc_orders table. Orders carry the items, payment, shipping, coupon, and custom field data as nested arrays and structs, so most of them lean on CROSS JOIN UNNEST to flatten one of those arrays before grouping.

Adding EST time zone dates to orders

BigQuery is going to treat all dates as the UTC time zone by default. This can cause some challenges when performing certain types of reports. The following query can be used to create a view “uc_orders_est” which contains fields that are shifted into the EST time zone.

select 
DATETIME(TIMESTAMP(o.creation_dts), "America/New_York") as creation_datetime_est,
DATETIME(TIMESTAMP(o.payment.payment_dts), "America/New_York") as payment_datetime_est,
DATETIME(TIMESTAMP(o.shipping.shipping_date), "America/New_York") as shipping_datetime_est,
DATETIME(TIMESTAMP(o.refund_dts), "America/New_York") as refund_datetime_est,
DATE(TIMESTAMP(o.creation_dts), "America/New_York") as creation_date_est,
DATE(TIMESTAMP(o.payment.payment_dts), "America/New_York") as payment_date_est,
DATE(TIMESTAMP(o.shipping.shipping_date), "America/New_York") as shipping_date_est,
DATE(TIMESTAMP(o.refund_dts), "America/New_York") as refund_date_est,
o.*
from `my-data-warehouse.my_dataset.uc_orders` as o

Revenue Per Item Over a Certain Time Period

Totals revenue and units sold for each SKU over a date range. The items on an order are a nested array, so the query uses CROSS JOIN UNNEST to join them in as if they were a separate table. Once the two are joined it is an ordinary group-by-and-sum query. Kit components are excluded so their parent item is not double counted.

SELECT i.merchant_item_id, sum(i.total_cost_with_discount.value) as revenue_for_item, sum(i.quantity) as units_sold
FROM `my-data-warehouse.my_dataset.uc_orders` 
CROSS JOIN UNNEST(items) as i 
WHERE creation_dts between "2021-07-01" and "2021-08-01" and i.kit_component = false 
group by i.merchant_item_id order by i.merchant_item_id

Orders For Specific Items Shipped By a User on a Specific Date

Returns the order IDs that contain one of a set of SKUs and were shipped by a named user on a single date, with the ship date converted to Eastern time.

SELECT distinct order_id
FROM `my-data-warehouse.my_dataset.uc_orders` 
CROSS JOIN UNNEST(items) as i
WHERE DATE(TIMESTAMP(shipping.shipping_date), "America/New_York") = "2021-12-09" 
and i.shipped_by_user = 'bob' 
and i.merchant_item_id in (
  'PRODUCT-A',
  'PRODUCT-B'
) order by order_id

Orders with a Specific Custom Field Value

Returns orders whose checkout custom field holds a particular value, along with the payment status and the rest of the custom fields on the order.

SELECT 
order_id, 
payment.payment_status, 
creation_dts, 
checkout.custom_field3, 
checkout.custom_field4, 
checkout.custom_field5, 
checkout.custom_field6, 
checkout.custom_field7
FROM `my-data-warehouse.my_dataset.uc_orders` 
WHERE checkout.custom_field4 = '1234'
ORDER BY order_id

Find Order Id by Transaction Value

Finds the order carrying a given payment transaction detail value, such as a gateway transaction ID or a payment intent.

SELECT order_id FROM `my-data-warehouse.my_dataset.uc_orders` 
CROSS JOIN UNNEST(payment.transactions) t
CROSS JOIN UNNEST(t.details) d
WHERE d.value = 'abc123'

Count of Apple Pay/Google Pay/Microsoft Pay Transactions

Counts credit card orders paid through a digital wallet. The wallet is not reported directly, so the query infers it from the operating system and browser recorded at checkout.

with order_rows as (
  SELECT order_id, payment.payment_dts,
  case 
  when checkout.browser.os.family  = 'iOS' then 'Apple Pay'
  when checkout.browser.os.family  = 'Android' then 'Google Pay'
  when checkout.browser.user_agent.family  = 'Safari' then 'Apple Pay'
  when checkout.browser.user_agent.family  = 'Mobile Safari' then 'Apple Pay'
  when checkout.browser.user_agent.family  = 'Chrome' then 'Google Pay'
  when checkout.browser.user_agent.family  = 'Chrome Mobile iOS' then 'Apple Pay'
  when checkout.browser.user_agent.family  = 'Chrome Mobile' then 'Google Pay'
  when checkout.browser.user_agent.family  = 'Edge' then 'Microsoft Pay'
  when checkout.browser.os.family is not null then checkout.browser.os.family
  else 'unknown'
  end as payment_provider
  FROM `ultracart_dw.uc_orders` 
  CROSS JOIN UNNEST(payment.transactions) pt
  CROSS JOIN UNNEST(pt.details) ptd
  where payment.payment_method = 'Credit Card' and ptd.name = 'payment_intent'
)
select payment_provider, count(*) from order_rows group by payment_provider

Coupon Usage Summary

Totals the discounted subtotal and the order count for each base coupon code over a date range in Eastern time, excluding test orders.

SELECT 
c.base_coupon_code, sum(summary.subtotal.value - summary.subtotal_discount.value) as subtotal, count(*) as usage
FROM `ultracart_dw.uc_orders` 
CROSS JOIN UNNEST(coupons) c
where DATETIME(TIMESTAMP(creation_dts), "America/New_York") between DATETIME('2023-05-01 00:00:00', 'America/New_York') and DATETIME('2023-06-30 23:59:59', 'America/New_York')
and payment.test_order is false
group by c.base_coupon_code
order by c.base_coupon_code

Coupon Usage Detail

Lists one row per order for every coupon used in a date range, with the customer, IP address, StoreFront, and the most heavily discounted item on the order.

WITH order_rows as (
SELECT 
    c.base_coupon_code, UPPER(c.coupon_code), order_id, DATETIME(TIMESTAMP(creation_dts), "America/New_York") as creationdate,
    summary.subtotal.value - summary.subtotal_discount.value as subtotal,
    billing.first_name,
    billing.last_name,
    billing.email,
    checkout.customer_ip_address,
    checkout.storefront_host_name,
    i.merchant_item_id,
    ROW_NUMBER() OVER (PARTITION BY c.base_coupon_code, order_id ORDER BY i.discount.value DESC, i.item_index) AS item_index
    FROM `ultracart_dw_medium.uc_orders` 
    CROSS JOIN UNNEST(coupons) c
    CROSS JOIN UNNEST(items) i
    where DATETIME(TIMESTAMP(creation_dts), "America/New_York") between DATETIME('2023-05-01 00:00:00', 'America/New_York') and DATETIME('2023-06-30 23:59:59', 'America/New_York')
    and payment.test_order is false
)
select * from order_rows where item_index = 1 
order by base_coupon_code, order_id

Order Count and Revenue by Hour of Day

Buckets the last year of paid orders by hour of day in Eastern time, so you can see the hours your customers actually order in.

WITH order_rows as (
  SELECT 
  DATETIME(TIMESTAMP(payment.payment_dts), "America/New_York") as payment_datetime_est, -- Convert the payment date from EST
  summary.total.value as total
  FROM `ultracart_dw.uc_orders` 
  where payment.payment_dts is not null -- make sure the payment has been processed
)
select
EXTRACT(hour from payment_datetime_est) as hour, -- Extract the hour
COUNT(*) as order_count, -- Count the orders
SUM(total) as revenue -- Sum the revenue
from order_rows 
where order_rows.payment_datetime_est >= DATETIME_SUB(CURRENT_DATETIME(), interval 1 year) -- where clause to filter to a particular time period
group by hour
order by hour

Last X Replacement Shipment Order Ids and Placed By

Lists the 1,000 most recent replacement shipment orders and the user who created each one, read from the replacement_shipment_by order property.

SELECT order_id, 
(
  select p.value from UNNEST(properties) p where p.name = 'replacement_shipment_by'
) as replacement_shipment_by
FROM `ultracart_dw.uc_orders`
where exists (
  select 1 from UNNEST(properties) p where p.name = 'replacement_shipment_by'
)
order by creation_dts desc
LIMIT 1000;

Replacement Shipment Count by User for a Period of Time

Counts the replacement shipments each user created over the last month.

with replacement_rows as (
  SELECT order_id, 
  (
    select p.value from UNNEST(properties) p where p.name = 'replacement_shipment_by'
  ) as replacement_shipment_by
  FROM `ultracart_dw.uc_orders`
  where exists (
    select 1 from UNNEST(properties) p where p.name = 'replacement_shipment_by'
  ) and creation_dts between CURRENT_DATETIME - INTERVAL 1 month and CURRENT_DATETIME
)
select replacement_shipment_by, count(*) 
from replacement_rows
group by 1 order by 2 desc

Was this page helpful?

Overview​

Adding EST time zone dates to orders​

Revenue Per Item Over a Certain Time Period​

Orders For Specific Items Shipped By a User on a Specific Date​

Orders with a Specific Custom Field Value​

Find Order Id by Transaction Value​

Count of Apple Pay/Google Pay/Microsoft Pay Transactions​

Coupon Usage Summary​

Coupon Usage Detail​

Order Count and Revenue by Hour of Day​

Last X Replacement Shipment Order Ids and Placed By​

Replacement Shipment Count by User for a Period of Time​