...
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
paymentorders.paymentpartition_dtsdate < >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 113 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
where rank = 1and 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
|
Related Documentation
https://towardsdatascience.com/how-to-monitor-repeat-purchase-behavior-using-google-data-studio-and-bigquery-c2b5adbe8ebb