Shopify Data Pipeline
Build report tables
Create a reports/ subfolder:
mkdir -p ecommerce/assets/reports
Let Claude Code help with report SQL
Report queries can get complex, especially cohort retention and marketing attribution. You can ask Claude Code to generate any of these:
Create a Bruin SQL asset for a daily revenue report. It should read from staging.stg_orders and compute total orders, paid orders, gross and net revenue, AOV, and cancellation rate grouped by day. Add quality checks. Put it in ecommerce/assets/reports/rpt_daily_revenue.sql.
For the harder reports:
Write a monthly customer cohort retention query using staging.stg_orders and staging.stg_customers. Group by the month of first purchase, compute retention rate and revenue per customer for each month since first order.
The examples below show what each report should look like. Select the tab that matches your warehouse.
1) Daily revenue - rpt_daily_revenue.sql
The most basic ecommerce report: daily revenue, order count, average order value, and cancellation rate.
Create ecommerce/assets/reports/rpt_daily_revenue.sql:
/* @bruin
name: reports.rpt_daily_revenue
type: sql
materialization:
type: table
depends:
- staging.stg_orders
columns:
- name: order_date
type: date
checks:
- name: not_null
- name: unique
custom_checks:
- name: has_rows
query: "SELECT count(*) > 0 FROM reports.rpt_daily_revenue"
value: 1
@bruin */
SELECT
toDate(order_date) AS order_date,
count(*) AS total_orders,
countIf(payment_status = 'paid') AS paid_orders,
countIf(cancel_reason IS NOT NULL) AS cancelled_orders,
sum(order_total) AS gross_revenue,
sum(CASE WHEN payment_status = 'paid' THEN order_total ELSE 0 END) AS net_revenue,
sum(discount_amount) AS total_discounts,
sum(tax_amount) AS total_tax,
round(net_revenue / nullIf(paid_orders, 0), 2) AS avg_order_value,
round(cancelled_orders / nullIf(total_orders, 0) * 100, 2) AS cancellation_rate
FROM staging.stg_orders
GROUP BY toDate(order_date)
ORDER BY order_date
2) Customer cohorts - rpt_customer_cohorts.sql
Monthly cohort analysis showing retention and lifetime value. This tells you if you're acquiring customers who stick around.
Create ecommerce/assets/reports/rpt_customer_cohorts.sql:
/* @bruin
name: reports.rpt_customer_cohorts
type: sql
materialization:
type: table
depends:
- staging.stg_orders
- staging.stg_customers
columns:
- name: cohort_month
type: date
checks:
- name: not_null
@bruin */
WITH customer_orders AS (
SELECT
o.customer_email,
toStartOfMonth(c.first_seen_at) AS cohort_month,
toStartOfMonth(o.order_date) AS order_month,
o.order_total
FROM staging.stg_orders o
INNER JOIN staging.stg_customers c
ON o.customer_email = c.customer_email
WHERE o.payment_status = 'paid'
),
cohort_sizes AS (
SELECT
cohort_month,
count(DISTINCT customer_email) AS cohort_size
FROM customer_orders
GROUP BY cohort_month
)
SELECT
co.cohort_month,
cs.cohort_size,
dateDiff('month', co.cohort_month, co.order_month) AS months_since_first,
count(DISTINCT co.customer_email) AS active_customers,
round(active_customers / nullIf(cs.cohort_size, 0) * 100, 2) AS retention_rate,
sum(co.order_total) AS cohort_revenue,
round(cohort_revenue / nullIf(cs.cohort_size, 0), 2) AS revenue_per_customer
FROM customer_orders co
INNER JOIN cohort_sizes cs
ON co.cohort_month = cs.cohort_month
GROUP BY co.cohort_month, cs.cohort_size, months_since_first
ORDER BY co.cohort_month, months_since_first
3) Product performance - rpt_product_performance.sql
A product catalog report showing all products with their key attributes and status.
Note
A full product performance report would need an order_line_items staging table to connect orders to individual products. Shopify stores line items as nested JSON in the orders table. For production use, ask Claude Code to create a staging.stg_order_line_items asset that flattens this data. This simplified version shows product catalog metrics from stg_products.
Create ecommerce/assets/reports/rpt_product_performance.sql:
/* @bruin
name: reports.rpt_product_performance
type: sql
materialization:
type: table
depends:
- staging.stg_products
columns:
- name: product_id
type: varchar
checks:
- name: not_null
- name: unique
@bruin */
SELECT
product_id,
product_name,
category,
vendor,
price,
product_status,
created_at,
updated_at
FROM staging.stg_products
ORDER BY product_name
4) Marketing ROI - rpt_marketing_roi.sql
Spend, revenue, and ROAS by marketing channel. This is the report that answers "where should we spend more?"
Warning
Skipped some data sources? This report depends on staging.stg_marketing_spend and staging.stg_web_sessions. If you skipped some (but not all) of those data sources, adjust this query: remove the depends entries, CTEs, and LEFT JOINs that reference missing staging tables. Ask Claude Code to rewrite the query for the sources you actually have.
Create ecommerce/assets/reports/rpt_marketing_roi.sql:
/* @bruin
name: reports.rpt_marketing_roi
type: sql
materialization:
type: table
depends:
- staging.stg_marketing_spend
- staging.stg_web_sessions
- staging.stg_orders
columns:
- name: channel
type: varchar
checks:
- name: not_null
@bruin */
WITH channel_spend AS (
SELECT
spend_date,
channel,
sum(spend) AS total_spend,
sum(impressions) AS total_impressions,
sum(clicks) AS total_clicks,
sum(conversions) AS total_conversions
FROM staging.stg_marketing_spend
GROUP BY spend_date, channel
),
channel_sessions AS (
SELECT
session_date,
channel,
sum(total_sessions) AS sessions,
sum(new_users) AS new_users,
sum(purchase_events) AS purchases
FROM staging.stg_web_sessions
GROUP BY session_date, channel
),
channel_revenue AS (
SELECT
toDate(order_date) AS order_date,
ws.channel,
sum(o.order_total) AS attributed_revenue
FROM staging.stg_orders o
INNER JOIN staging.stg_web_sessions ws
ON toDate(o.order_date) = ws.session_date
WHERE o.payment_status = 'paid'
GROUP BY toDate(order_date), ws.channel
)
SELECT
cs.spend_date AS report_date,
cs.channel,
cs.total_spend,
cs.total_impressions,
cs.total_clicks,
cs.total_conversions,
sess.sessions,
sess.new_users,
cr.attributed_revenue,
round(cr.attributed_revenue / nullIf(cs.total_spend, 0), 2) AS roas,
round(cs.total_spend / nullIf(cs.total_conversions, 0), 2) AS cost_per_acquisition,
round(cs.total_clicks / nullIf(cs.total_impressions, 0) * 100, 2) AS click_through_rate
FROM channel_spend cs
LEFT JOIN channel_sessions sess
ON cs.spend_date = sess.session_date
AND cs.channel = sess.channel
LEFT JOIN channel_revenue cr
ON cs.spend_date = cr.order_date
AND cs.channel = cr.channel
ORDER BY cs.spend_date DESC, cs.total_spend DESC
5) Daily KPIs - rpt_daily_kpis.sql
A single unified daily view of the whole business. One table, all the numbers that matter.
Warning
Skipped advertising, email marketing, or web analytics? This report joins staging.stg_web_sessions and staging.stg_marketing_spend. If you skipped those data sources, remove the corresponding depends entries, CTEs (daily_sessions, daily_spend), LEFT JOINs, and columns (sessions, new_visitors, conversion_rate, total_ad_spend, overall_roas) from the query. Ask Claude Code to adjust it for you.
Create ecommerce/assets/reports/rpt_daily_kpis.sql:
/* @bruin
name: reports.rpt_daily_kpis
type: sql
materialization:
type: table
depends:
- reports.rpt_daily_revenue
- staging.stg_customers
- staging.stg_orders
- staging.stg_web_sessions
- staging.stg_marketing_spend
columns:
- name: kpi_date
type: date
checks:
- name: not_null
- name: unique
@bruin */
WITH daily_customers AS (
SELECT
toDate(o.order_date) AS order_date,
countIf(toDate(c.first_seen_at) = toDate(o.order_date)) AS new_customers,
countIf(toDate(c.first_seen_at) < toDate(o.order_date)) AS returning_customers
FROM staging.stg_orders o
LEFT JOIN staging.stg_customers c
ON o.customer_email = c.customer_email
WHERE o.payment_status = 'paid'
GROUP BY toDate(o.order_date)
),
daily_sessions AS (
SELECT
session_date,
sum(total_sessions) AS sessions,
sum(new_users) AS new_visitors,
sum(purchase_events) AS purchases
FROM staging.stg_web_sessions
GROUP BY session_date
),
daily_spend AS (
SELECT
spend_date,
sum(spend) AS total_ad_spend
FROM staging.stg_marketing_spend
GROUP BY spend_date
)
SELECT
r.order_date AS kpi_date,
r.net_revenue,
r.total_orders,
r.paid_orders,
r.avg_order_value,
r.cancellation_rate,
dc.new_customers,
dc.returning_customers,
ds.sessions,
ds.new_visitors,
round(ds.purchases / nullIf(ds.sessions, 0) * 100, 2) AS conversion_rate,
sp.total_ad_spend,
round(r.net_revenue / nullIf(sp.total_ad_spend, 0), 2) AS overall_roas
FROM reports.rpt_daily_revenue r
LEFT JOIN daily_customers dc ON r.order_date = dc.order_date
LEFT JOIN daily_sessions ds ON r.order_date = ds.session_date
LEFT JOIN daily_spend sp ON r.order_date = sp.spend_date
ORDER BY kpi_date DESC
Run the full pipeline end-to-end
Run the entire pipeline - ingestion, staging, and reports - in one command:
bruin run --start-date 2025-01-01 --end-date 2025-02-01 .
Bruin executes everything in dependency order: ingestor assets first, then staging, then reports. Quality checks run after each asset.
If your test month looks good, run a full backfill:
bruin run --start-date 2024-01-01 --end-date 2025-04-01 .
You can also use --full-refresh to drop and recreate all tables from scratch if you need a clean slate:
bruin run --full-refresh --start-date 2024-01-01 --end-date 2025-04-01 .
Validate the full pipeline:
bruin validate .
Verify results with Claude Code
After the pipeline runs, ask Claude Code to spot-check the reports:
Query reports.rpt_daily_revenue for the last 7 days and show me the results. Does anything look off?
Check if rpt_customer_cohorts has data for the last 6 months. Show me the retention rate for the most recent cohort.
Run bruin validate on the project and summarize any quality check failures.
If a report looks wrong, Claude Code can compare the report output against the staging tables to find the issue:
The rpt_marketing_roi table shows zero attributed_revenue for Facebook. Query the staging tables to trace where the data is dropping off.