...
| Code Block | ||
|---|---|---|
| ||
WITH email_rows AS (
-- Find emails that have made their first purchase in the last 12 months.
select billing.email_hash, MIN(payment.payment_dts) as first_order_dts FROM `my-data-warehouse.ultracart_dw.uc_orders` orders
group by billing.email_hash
having first_order_dts >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
),
item_rows as (
SELECT
billing.email_hash,
order_id,
items.merchant_item_id,
RANK() OVER ( PARTITION BY billing.email_hash ORDER BY order_id, items.cost.value desc ) AS rank,
1 as initial_order_count
FROM `my-data-warehouse.ultracart_dw.uc_orders` orders
CROSS JOIN UNNEST(items) as items
LEFT JOIN email_rows on email_rows.email_hash = orders.billing.email_hash
WHERE
orders.partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), WEEK)
AND payment.payment_dts between DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
AND orders.auto_order is null
AND orders.channel_partner is null
AND orders.billing.email_hash <> ''
AND orders.billing.email_hash is not null
AND payment.test_order = false
AND current_stage NOT LIKE "%Rejected%"
AND payment.payment_status NOT LIKE "Declined"
AND payment.payment_status NOT LIKE "Unprocessed"
AND payment.payment_status NOT LIKE "Skipped"
AND billing.email_hash in (select email_rows.email_hash from email_rows)
order by order_id desc
)
SELECT
item_rows.merchant_item_id,
ROUND(AVG(COALESCE(orders.summary.subtotal.value, 0) - COALESCE(orders.summary.subtotal_discount.value, 0)), 2) as additional_ltv,
COUNT(orders.order_id) as additional_order_count,
SUM(item_rows.initial_order_count) as initial_order_count,
FROM item_rows
LEFT JOIN `my-data-warehouse.ultracart_dw.uc_orders` orders
on orders.billing.email_hash = item_rows.email_hash and orders.order_id <> item_rows.order_id
and orders.partition_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), WEEK)
AND orders.auto_order is null
where rank = 1
group by item_rows.merchant_item_id
having initial_order_count >= 50
order by item_rows.merchant_item_id
|
UTM Sales By Week
| Code Block | ||||
|---|---|---|---|---|
| ||||
with order_rows as (
SELECT DATE(TIMESTAMP(creation_dts), "America/New_York") as creation_date_est,
order_id,
summary.subtotal.value as subtotal_before_discounts,
coalesce(summary.subtotal_discount.value, 0) as subtotal_discount,
summary.subtotal.value - coalesce(summary.subtotal_discount.value, 0) as subtotal,
summary.total.value,
payment.payment_status,
checkout.storefront_host_name,
(
select p.value from UNNEST(properties) p where p.name = 'ucasource' LIMIT 1
) as utm_source,
(
select p.value from UNNEST(properties) p where p.name = 'ucacampaign' LIMIT 1
) as utm_campaign,
(
select p.value from UNNEST(properties) p where p.name = 'ucaUtmTerm' LIMIT 1
) as utm_term,
(
select p.value from UNNEST(properties) p where p.name = 'ucaUtmContent' LIMIT 1
) as utm_content,
(
select p.value from UNNEST(properties) p where p.name = 'ucaUtmMedium' LIMIT 1
) as utm_medium,
(
select p.value from UNNEST(properties) p where p.name = 'ucaUtmId' LIMIT 1
) as utm_id,
FROM `my-data-warehouse.ultracart_dw.uc_orders`
order by creation_dts desc
)
select
CAST(DATE_TRUNC(order_rows.creation_date_est, WEEK) as STRING) as sheet_partition_key,
CONCAT(coalesce(utm_source, ''), ' - ', coalesce(utm_campaign, '')) as utm_source_campaign,
count(*) as order_count,
sum(order_rows.subtotal) as subtotal_revenue
from order_rows
where payment_status = 'Processed'
group by sheet_partition_key, utm_source, utm_campaign
order by sheet_partition_key desc, subtotal_revenue desc |