Growth Intelligence Report

VAHDAM DTC
Growth Strategy

Data-driven strategy for D2C revenue growth — powered by DuckDB analytics across Shopify, Klaviyo, and WebEngage

🔍 Key Questions ⚡ Retention Engine 📊 Channel Efficiency 🌍 Geo Prioritisation 🔄 Subscription Flywheel 🎯 Funnel Conversion
Analytics Playbook

Key Questions This Data Answers

Every metric exists to answer a specific business question. Below are the 18 questions VAHDAM's data layer is built to answer — including 5 cross-source combinations that are only possible when all four data sources are loaded together. Single-source metrics are straightforward; combination metrics are where the real edge is.

💰 Revenue & Margin 4 questions · Metrics 1, 2, 5, 8
Is our revenue growing — and which market is driving it?
Splits gross sales, discounts, and net sales by country code into US / UK / IN / Rest of World. Shows whether growth is broad-based or propped up by a single geography.
Metric 1 — Net Revenue by Market matrixify.orders
Healthy: All 3 core markets (US, UK, IN) growing MoM. No single market >60% of total.
Watch: One market declining while others grow — flag for geo strategy review.
→ Geo Prioritisation strategy ↑ Questions
Are we over-reliant on new customers to hit revenue targets?
Segments every order into "new" (customer_orders_count = 1) or "returning" and shows the monthly revenue split. If new customers drive >50% of revenue, growth is expensive and fragile — any slowdown in acquisition hits the top line immediately.
Metric 2 — New vs Returning Split matrixify.orders
Target: Returning customers >60% of revenue (signals strong retention moat).
Red flag: New customer share creeping above 45% — retention is eroding.
→ Retention Engine strategy ↑ Questions
What is our actual margin after cost of goods — by product type?
Calculates (price − variant_cost) / price × 100 at the line-item level and rolls up by month and product_type. Catches margin erosion from discounting or cost increases before it hits the P&L visibly.
Metric 5 — Gross Margin % matrixify.order_line_items
Healthy: Gross margin stable or improving MoM per product type.
Watch: Gift Sets or bundled SKUs showing lower margin — pricing or COGS review needed.
Standalone — no strategy dependency ↑ Questions
Is our average order value trending up, flat, or declining?
Monthly AOV with MoM % change. A consistent AOV decline often signals over-reliance on discount-driven campaigns or a product mix shift toward lower-value SKUs — both of which compress margin faster than revenue growth can compensate.
Metric 8 — AOV Trend matrixify.orders
Alert trigger: AOV drops >5% MoM — query flags this automatically.
Investigate: Cross-check against Email Revenue % — if email share is high during a drop, Klaviyo sends may be discount-heavy.
→ Channel Efficiency strategy ↑ Questions
📐 Customer Economics 4 questions · Metrics 3, 6, 12, Bonus LTV · 2 combinations
⚡ Combination — 2 sources
Are we paying more to acquire customers than they will ever be worth?
LTV:CAC requires joining customer lifetime revenue from matrixify.orders with channel spend from shopify_analytics.marketing_attribution. Neither table alone gives you this. The ratio tells you whether scaling a channel will compound returns or compound losses.
Metric 3 — LTV:CAC by Channel matrixify.orders shopify_analytics.marketing_attribution
Scale: Channels with LTV:CAC >3:1 — increase budget.
Pause: Channels <2:1 — the unit economics don't work.
Watch: Channels with no CAC data (organic, direct) — estimate via assisted conversions.
→ Channel Efficiency strategy ↑ Questions
What is a VAHDAM customer actually worth over their lifetime — by market?
Aggregates all revenue per customer and computes average and median LTV, orders, and AOV — split by shipping country. Median LTV is more useful than average here because a handful of high-CLV customers skew averages significantly in premium DTC.
Bonus — LTV by Market matrixify.orders
Use for: Setting market-specific CAC ceilings (e.g., if median LTV in UK = $130, max acceptable CAC = $43 at 3:1).
Watch: Large gap between avg and median LTV — signals a small VIP cohort carrying disproportionate revenue.
→ Geo Prioritisation strategy ↑ Questions
⚡ Combination — 2 sources
Which acquisition channels produce the highest-value customers — not just the most orders?
CAC by channel (from Shopify Analytics) only tells you the cost per acquisition. Pairing it with LTV segmented by utm_source from Matrixify orders tells you which channels acquire customers who keep buying vs customers who buy once and disappear. A channel with high CAC but high LTV may still be your best.
Metric 6 — CAC by Channel Bonus — LTV by Channel shopify_analytics.acquisition_metrics matrixify.orders (utm_source)
Action: Sort channels by LTV:CAC, not raw CAC. An influencer channel with $60 CAC but $280 LTV is 4.7× — keep it. A Google channel with $25 CAC but $60 LTV is 2.4× — review it.
→ Channel Efficiency strategy ↑ Questions
How long does it take for a new customer to make a second purchase?
Ranks each customer's orders chronologically and measures the gap in days between order 1 and order 2. The median value tells you when to fire the post-purchase re-engagement flow — trigger it before the median, not after. Split by market and UTM source to catch channel-specific differences.
Metric 12 — Time to 2nd Purchase matrixify.orders
From your data: Once you run Metric 12, your median days-to-2nd defines the benchmark. Below [ YOUR MEDIAN T2P ] = strong; above 90 = flow is firing too late.
Action: Set Klaviyo day-N post-purchase trigger to median_days − 7.
→ Retention Engine strategy ↑ Questions
🔁 Retention & Churn 5 questions · Metrics 4, 11, 13, 14, 15 · 1 combination
What % of new buyers place a second order within 90 days?
For each cohort month (customers who made their first-ever purchase in month X), calculates the % who returned within 90 days. The cohort view is critical — a blended repeat rate hides whether the rate is improving or declining over time as acquisition channels shift.
Metric 4 — Repeat Purchase Rate 90d matrixify.orders
Strong: 90d repeat rate >[ 25% — INDUSTRY BENCHMARK ] = healthy DTC retention.
Weak: Rate trending down across recent cohorts — investigate if a product, acquisition channel, or flow changed.
→ Retention Engine strategy ↑ Questions
Are customers still buying at 30, 60, and 90 days after first purchase?
More granular than the 90-day repeat rate — shows where the drop-off happens. If 30-day retention is fine but 60-day is poor, the second-purchase window closes between weeks 4 and 8. That's when your flow needs to fire, not at day 90.
Metric 11 — Cohort Retention 30/60/90d matrixify.orders
Read together with Metric 12: If median time-to-2nd is 40 days but 30d retention is <10%, customers are taking longer — not churning. If 90d retention = 30d retention, no one returns after the first window.
→ Retention Engine strategy ↑ Questions
How many of our active customers are drifting toward churn right now?
Klaviyo's predictive model scores every profile as low / medium / high churn risk / winback. The distribution tells you the scale of the retention problem before it shows up in revenue. High + winback combined is your actionable "at-risk" segment.
Metric 13 — Churn Risk Distribution klaviyo.profiles
Act when: High + winback >[ 25% — ROUGH BENCHMARK ] of active list.
Note: These thresholds are Klaviyo's predictions, not derived from your own order history. Cross-validate against Metric 4 cohorts.
→ Retention Engine strategy ↑ Questions
⚡ Combination — 2 sources
How much future revenue will we lose if we don't act on churn now?
At-risk revenue = SUM(predicted_clv_1y) filtered to churn_risk IN ('high','winback'). This combines Klaviyo's predictive CLV model with its churn risk score to put a dollar figure on the cost of inaction — making the case for win-back investment concrete and quantifiable. Break it down by country to see where the most revenue is at risk.
Metric 14 — At-Risk Revenue klaviyo.profiles (churn_risk × predicted_clv_1y)
Frame it this way: "We have $X in predicted 1-year CLV from customers who are likely to churn. A 30% recovery rate from win-back flows = $Y in recovered revenue."
Caveat: predicted_clv_1y is Klaviyo's model output — accuracy depends on how much purchase history Klaviyo has synced from Shopify.
→ Retention Engine strategy ↑ Questions
Which products are best at bringing customers back — regardless of what they bought?
For each SKU, calculates the % of first-time buyers of that product who made any subsequent purchase within 90 days. These are your "retention anchor" products — the ones that open the relationship. Products with high repeat rates should be prioritised for acquisition and subscription conversion, not just for margin.
Metric 15 — Product Repeat Rate matrixify.order_line_items + orders
Use for: Identify top-3 "gateway SKUs" → run paid acquisition to those products specifically → higher lifetime cohort value from the start.
Combine with: Subscription Mix — if a high-repeat SKU has low subscription penetration, that's your next conversion target.
→ Subscription Flywheel strategy → Retention Engine ↑ Questions
📧 Channel & Email Efficiency 3 questions · Metrics 7, 3+6 · 1 combination
⚡ Combination — 2 sources
How much of our total revenue can we directly attribute to Klaviyo?
Joins Klaviyo's revenue_attributed (from klaviyo.campaigns) against total net sales from shopify_analytics.revenue_metrics. The ratio tells you how hard your email list is working. Also breaks down by campaign vs flow — flows should outperform campaigns over time because they're always-on and targeted.
Metric 7 — Email Revenue % klaviyo.campaigns shopify_analytics.revenue_metrics
Target: [ 30–40% — INDUSTRY BENCHMARK ] of net sales attributed to Klaviyo.
Gap: Below 20% = significant flows are missing or poorly sequenced.
Caveat: Klaviyo uses last-touch attribution with a 5-day window by default — this overstates email influence when other channels are active.
→ Channel Efficiency strategy ↑ Questions
Are we investing in acquisition channels that actually generate profitable returns?
CAC by channel shows cost and conversion rate. Combined with the LTV:CAC query (Metric 3), this becomes a full profitability screen per channel. Run it monthly and reallocate budget from any channel below 3:1 to channels above it — or to email/organic where marginal cost is near zero.
Metric 6 — CAC by Channel Metric 3 — LTV:CAC shopify_analytics.acquisition_metrics
Reallocate trigger: Any channel with LTV:CAC <2:1 for 2 consecutive months → pause or significantly reduce budget.
Note: CAC from Shopify Analytics uses session-based attribution; treat it as a proxy, not ground truth.
→ Channel Efficiency strategy ↑ Questions
🎯 Conversion & Revenue Mix 4 questions · Metrics 9, 10, 15+10 · 1 combination
Where exactly in the checkout funnel are we losing customers?
WebEngage event_summary rolls up daily event counts for Product Viewed → Added To Cart → Checkout Created → Order Created. Aggregated by week, it pinpoints which stage has the biggest drop-off. Unlike Shopify's native funnel, this captures cross-session and cross-device behaviour.
Metric 9 — Checkout Conversion Rate webengage.event_summary
Your baseline: Run Metric 9 against your real WebEngage export — [ YOUR ATC→CHK RATE ] cart-to-checkout, [ YOUR CHK→ORDER RATE ] checkout-to-order.
Alert: Any stage drops >5% WoW → query flags it automatically.
Investigate by: Device type (mobile vs desktop gap), market (US vs UK checkout friction).
→ Funnel Conversion strategy ↑ Questions
What share of our revenue comes from subscription vs one-time orders?
Identifies subscription orders by checking whether any line item's properties JSON contains "subscription" or "frequency". This approach depends on your subscription app writing these properties to the line item — verify this against a known subscription order before trusting the output.
Metric 10 — Subscription Mix % matrixify.order_line_items + orders
Target: Push toward [ YOUR TARGET SUB MIX % ] subscription mix for revenue predictability.
Current: [ RUN METRIC 10 ] (from your real Matrixify export).
Watch: Month where subscription % drops sharply = subscriber churn spike → investigate pauses.
→ Subscription Flywheel strategy ↑ Questions
⚡ Combination — 2 metrics
Which SKUs have high repeat purchase rates but low subscription penetration — and should be converted?
Cross Metric 15 (Product Repeat Rate) against Metric 10 (Subscription Mix by SKU). A product where 25% of buyers repurchase within 90 days but only 5% are on subscription is a proven consumable that customers haven't been offered the subscription option for. These are your highest-conviction subscription conversion candidates.
Metric 15 — Product Repeat Rate Metric 10 — Sub Mix by SKU matrixify.order_line_items + orders
Action: Export this list → create a Klaviyo segment of buyers of these SKUs who are NOT subscribed → enrol in subscription offer flow day 7 post-purchase.
Expected outcome: [ 8–15% — INDUSTRY BENCHMARK ] subscription conversion rate on this warm audience.
→ Subscription Flywheel strategy ↑ Questions
⚡ Combination — 2 sources
Is a drop in checkout conversion causing revenue to fall, or is it a traffic quality problem?
When revenue drops, root cause splits two ways: less traffic converting (funnel problem) or worse traffic arriving (acquisition quality problem). Running Metric 9 (WebEngage checkout funnel) alongside Metric 6 (acquisition channel mix from Shopify Analytics) in the same week separates them. If CVR is stable but a low-quality channel suddenly dominates traffic, the fix is media — not UX.
Metric 9 — Checkout Conversion Metric 6 — Acquisition Channel Mix webengage.event_summary shopify_analytics.acquisition_metrics
Diagnosis tree: CVR down + channel mix stable → UX/pricing issue. CVR stable + new low-quality channel dominant → media problem. Both down → broader demand signal — check seasonality or competitor activity.
→ Funnel Conversion strategy → Channel Efficiency ↑ Questions
All numbers in this document are from synthetic seed data
The thresholds (LTV:CAC 3:1, email 30–40%, CAC $45, subscriber LTV 4×) are industry DTC benchmarks — not derived from VAHDAM's actual data. Run python run_all.py with your real Matrixify, Klaviyo, and WebEngage exports, then re-read queries/metrics.sql against vahdam_dtc.duckdb to replace every number with actuals before using this document with stakeholders.
North Star + Metrics Framework

What We Measure & Why

15 metrics organised into a three-tier pyramid — from business health signals reviewed weekly, to deep retention intelligence reviewed monthly.

⭐
North Star Metric
Customer Lifetime Value (LTV)
Every strategy decision optimises for LTV. A customer who buys twice is worth 3–5× one who buys once. Growing average LTV is the compounding advantage that makes VAHDAM defensible at scale.
Tier 1 — Business Health Weekly Review
Matrixify Net Revenue by Market
Matrixify New vs Returning Mix
Shopify LTV:CAC by Channel
Matrixify Repeat Purchase Rate 90d
Matrixify Gross Margin %
Tier 2 — Growth Levers Weekly Review
Shopify CAC by Channel
Klaviyo Email Revenue %
Matrixify AOV Trend MoM
WebEngage Checkout Conversion
Matrixify Subscription Mix %
Tier 3 — Retention Intelligence Monthly Review
Matrixify Cohort Retention 30/60/90d
Matrixify Time to 2nd Purchase
Klaviyo Churn Risk Distribution
Klaviyo At-Risk Revenue
Matrixify Product Repeat Rate
Matrixify LTV by Market
Metrics Review Decision Protocol
Weekly Review Trigger Protocol Run Tier 1 Weekly Review Any metric below target? All clear — log reading + date NO YES Which tier breached? Tier 1 — Business → Check CAC + AOV → Revenue by market Tier 2 — Growth → Audit email flows → Review channel mix Tier 3 — Retention → Cohort deep-dive → Win-back segment
Metrics Review Decision Protocol
Weekly Review Trigger Protocol Run Tier 1 Weekly Review Any metric below target? All clear — log reading + date NO YES Which tier breached? Tier 1 — Business → Check CAC + AOV → Revenue by market Tier 2 — Growth → Audit email flows → Review channel mix Tier 3 — Retention → Cohort deep-dive → Win-back segment
Tier 1 — Business Health Weekly Review
M1 Net Revenue by Market Where is our revenue coming from — US, UK, India, or rest of world? Matrixify
Problem it solves

Without market-level breakdown you can't tell if growth is concentrated in one market masking decline in another, or whether currency exposure is distorting the top line.

Why it is important

Drives resource allocation, market-specific pricing, and determines where to direct the next acquisition dollar. Every market strategy is built on this number.

SQL Reference

queries/metrics.sql — Metric 1

M2 New vs Returning Revenue Is growth coming from new customers or from existing customers buying again? Matrixify
Problem it solves

Blended revenue hides whether you're on a treadmill — churn replacing acquisition at net-zero customer growth — or genuinely compounding. A business that looks like it's growing can be dying if returning revenue share is falling.

Why it is important

If returning revenue share declines month over month, you have a retention problem even while total revenue grows. This ratio diagnoses acquisition dependency before it becomes a crisis.

SQL Reference

queries/metrics.sql — Metric 2

M3 LTV:CAC Ratio by Channel Is each acquisition channel generating more lifetime value than it costs to acquire customers from it? Shopify Analytics
Problem it solves

CAC alone is meaningless without LTV context. A $50 CAC is brilliant if LTV is $300 and destructive if LTV is $55. Channels below 3:1 destroy value with every customer acquired.

Why it is important

The single most important channel health check. Determines which channels to scale, which to pause, and where reallocated budget will compound fastest.

SQL Reference

queries/metrics.sql — Metric 3

M4 Repeat Purchase Rate 90d What percentage of new buyers come back within 90 days — tracked by cohort month? Matrixify
Problem it solves

A blended repeat rate hides whether the rate is improving or deteriorating. Viewing by cohort reveals whether a product change, channel shift, or flow modification caused a structural change in behaviour.

Why it is important

The 90-day repeat rate is the best single predictor of long-term LTV for a consumable DTC brand. A cohort with a higher 90d rate will be worth 2–4× more at 12 months.

SQL Reference

queries/metrics.sql — Metric 4

M5 Gross Margin % by Product Type Which product categories make money after cost of goods — and which are eroding profitability? Matrixify
Problem it solves

Revenue without margin context is vanity. A high-revenue SKU at 10% margin funds less future growth than a mid-revenue SKU at 65% margin. Promotion decisions made without this view optimise for the wrong outcome.

Why it is important

Sets the floor for viable CAC per product type, determines which SKUs to prioritise in acquisition, and drives subscription pricing decisions. Required for any unit-economics model.

SQL Reference

queries/metrics.sql — Metric 5

Tier 2 — Growth Levers Weekly Review
M6 CAC by Channel How much does it cost to acquire one new customer from each marketing channel? Shopify Analytics
Problem it solves

Marketing spend without per-channel CAC creates allocation by gut feel. Channels that look productive by session volume often have the worst cost-per-new-customer once the denominator is right.

Why it is important

The denominator in LTV:CAC. Without accurate CAC you can't know whether any channel is profitable. Also exposes which channels bring in high-intent vs low-intent traffic.

SQL Reference

queries/metrics.sql — Metric 6

M7 Email Revenue % by Month What share of total net revenue can be directly attributed to Klaviyo campaigns and flows? Klaviyo
Problem it solves

If email isn't contributing at least 20% of revenue, critical flows are missing or the list is unhealthy. Over-reliance on paid acquisition makes growth fragile and expensive.

Why it is important

Email/SMS are the highest-ROI owned channels. A growing email revenue % reduces overall CAC structurally — every percentage point is margin that doesn't have to be paid to an ad platform.

SQL Reference

queries/metrics.sql — Metric 7

M8 AOV Trend MoM Is the average order value increasing, flat, or declining month over month — and what is driving it? Matrixify
Problem it solves

Flat AOV with stable order count means revenue is stagnating. Without MoM tracking, bundle and upsell effectiveness is invisible — you can't tell if pricing changes or promotions moved the needle.

Why it is important

AOV × purchase frequency = LTV. A 10% AOV improvement compounds across every customer. It also signals whether discounting is eroding basket value or whether upsell strategies are working.

SQL Reference

queries/metrics.sql — Metric 8

M9 Checkout Conversion Funnel At which exact stage — product view, add-to-cart, checkout, or payment — are we losing the most customers? WebEngage
Problem it solves

If 70% of people who add to cart never complete a purchase, every dollar spent on acquisition is partially wasted. Funnel leaks are invisible without stage-by-stage event data.

Why it is important

Funnel improvements multiply all upstream marketing spend. A fix that recovers 5% of cart abandonment benefits every channel simultaneously — highest-leverage conversion investment available.

SQL Reference

queries/metrics.sql — Metric 9

M10 Subscription Mix % What percentage of monthly revenue comes from subscription orders vs one-time purchases? Matrixify
Problem it solves

One-time orders create unpredictable, lumpy revenue — every month starts from zero. Subscription revenue compounds: once a customer subscribes, revenue is near-certain until they cancel.

Why it is important

Subscription mix directly determines revenue predictability, LTV ceiling, and valuation multiple. Moving from 20% to 50% subscription can increase valuation 2–3× on the same revenue base.

SQL Reference

queries/metrics.sql — Metric 10

Tier 3 — Retention Intelligence Monthly Review
M11 Cohort Retention 30/60/90d Are customers still buying at 30, 60, and 90 days after first purchase — and where does the cohort drop off? Matrixify
Problem it solves

A single repeat rate doesn't show WHERE the drop-off happens. If 30d and 90d retention are identical, no one returns after the first window. The shape of the curve changes the entire flow strategy.

Why it is important

Determines optimal trigger timing for every post-purchase flow. If median second purchase is day 45, a day-7 re-engagement email fires 38 days too early. Cohort shape is the structural input for automation.

SQL Reference

queries/metrics.sql — Metric 11

M12 Time to 2nd Purchase How many days does it typically take for a customer to make a second purchase — segmented by market? Matrixify
Problem it solves

Post-purchase flows fire at arbitrary intervals by default. If your email fires at day 60 but customers who return typically do so at day 21, you are missing the conversion window by 6 weeks.

Why it is important

The single structural input that sets trigger timing for every Klaviyo post-purchase flow. Run once per market segment — US and UK median values should drive different flow timing.

SQL Reference

queries/metrics.sql — Metric 12

M13 Churn Risk Distribution How many active customers are predicted to churn in the next 90 days — and how is risk distributed? Klaviyo
Problem it solves

Churn is invisible until it registers in revenue, typically 2–3 months after the behaviour change. Klaviyo's predictive score gives a 30–90 day early warning. Without it, you win-back customers who have already bought elsewhere.

Why it is important

Win-back is 5–7× cheaper than new acquisition. Acting on predicted churn before it happens is the highest-ROI retention lever. Knowing the distribution tells you the scale of risk and whether it requires urgent budget reallocation.

SQL Reference

queries/metrics.sql — Metric 13

M14 At-Risk Revenue What is the total predicted 12-month CLV of customers currently flagged as high-risk or winback? Klaviyo
Problem it solves

Retention investment decisions require a dollar figure. '500 at-risk customers' doesn't move a budget conversation. '$180K in predicted CLV is at risk this quarter' does.

Why it is important

Converts churn probability into a concrete revenue line. If 30% of at-risk CLV can be recovered, the expected recovery amount directly sets the maximum viable spend on win-back flows and incentives.

SQL Reference

queries/metrics.sql — Metric 14

M15 Product Repeat Rate (Top 10) Which specific SKUs are most effective at bringing customers back — regardless of what they buy next? Matrixify
Problem it solves

Not all products are equal as relationship openers. A low-priced sampler might generate more lifetime value than a premium SKU by pulling customers into repeat purchase behaviour. Without this view, acquisition targets high-margin products that may not retain.

Why it is important

Identifies gateway SKUs — the products that open long-term customer relationships. These are your highest-value acquisition targets and top subscription conversion candidates.

SQL Reference

queries/metrics.sql — Metric 15

BONUS LTV by Market Are US, UK, and India customers worth the same over their lifetime — or should each market have a different CAC ceiling? Matrixify
Problem it solves

Applying the same CAC ceiling to all markets is wrong if LTV differs significantly. If UK customers have 2× the LTV of US customers, you are systematically overspending in the US and underspending in the UK.

Why it is important

The market-level LTV split determines the correct CAC ceiling per geography — the primary input for media budget allocation. A market with 50% higher LTV can justify 50% higher CAC before the same 3:1 ratio breaks.

SQL Reference

queries/metrics.sql — BONUS query

↑ Top › Key Questions › Metrics Framework › Strategy 1 — Retention Engine
Strategy 1

Retention-First Revenue Engine

Acquire less expensively by retaining more effectively. VAHDAM's repeat purchase rate signals that retention — not acquisition — is the primary growth lever.

Why This Strategy

VAHDAM already has a strong repeat purchase signal. The opportunity is to systematise what's happening organically: identify who's about to churn before they do, segment by predicted lifetime value, and intervene at the right moment with the right offer. Klaviyo's predictive CLV and churn risk scores make this operationally feasible without a data science team.

Recovery Workflow

DuckDB
churn query
→
Identify
churn cohort
→
Cross-ref
predicted CLV
→
Segment by
value tier
→
Trigger
Klaviyo flow
→
Recovered
revenue

Decision Tree

If
High churn risk + predicted CLV > [ YOUR HIGH-VALUE CLV CUTOFF ]
Then
Personal win-back: [ YOUR WIN-BACK OFFER ]. Assign to VIP rep flow. High-touch, high-value worth the margin cost.
If
High churn risk + predicted CLV < [ YOUR HIGH-VALUE CLV CUTOFF ]
Then
Low-cost reactivation: editorial content email, no discount. Preserve margin. Let content do the work.
If
Medium churn + days since last order > 60
Then
Product recommendation based on last SKU purchased. "You loved X — try Y." Curiosity-led re-engagement, no discount.
If
Time to 2nd purchase > [ YOUR MEDIAN T2P ] average
Then
Shorten the window. Trigger a day-[ MEDIAN T2P − 7 ] post-purchase educational flow. Show use cases, brew guides, complementary SKUs.

Metrics Used

Churn Risk Distribution At-Risk Revenue Time to 2nd Purchase Cohort Retention 90d Klaviyo Flow Conversion
👤
Customer Lifecycle Funnel
How many customers survive each stage of the relationship — from first purchase to high-LTV subscriber
New customers acquired (month)
[ YOUR NEW CUSTOMERS ]
100% of top
[ YOUR ACQUISITION COST ] drop to next stage
Returned within 30 days
[ RUN M11: 30d retention ]
35% of top
[ 65% — typical drop ] drop to next stage
Returned within 90 days
[ RUN M4: 90d repeat rate ]
28% of top
[ 20% — typical drop ] drop to next stage
Placed 3+ orders (loyal tier)
[ RUN M2: returning segment ]
18% of top
[ 10% — typical drop ] drop to next stage
Converted to subscription
[ RUN M10: sub mix ]
10% of top
[ 8% — typical drop ] drop to next stage
High-LTV top 20% of base
[ RUN BONUS: LTV by market ]
5% of top
Each bar = share of the stage above. Values are placeholders — run the linked metric query to fill with your real data.
Customer Win-Back Email Sequence
Customer Places First Order Day 0: Post-purchase email + order confirmation D+0 Day 30: re-ordered? D+30 Exit to retention flow (high-value buyer) YES NO Day 45: “How's your tea?” soft re-engagement email D+45 Day 60: purchased? D+60 Exit — add to loyalty nurture sequence YES NO Day 74: Win-back + offer (CLV > $200 → 15% off) D+74 Responded? Re-activated — monitor 90d YES NO Lapsed — CLV scored in Klaviyo
Key DuckDB Query — Identify High-Value Churn Targets
-- Customers: high churn risk + CLV > $200, no order in 60+ days
SELECT
    p.profile_id,
    p.email,
    p.churn_risk,
    p.predicted_clv_1y,
    p.historic_number_of_orders,
    MAX(o.processed_at) AS last_order_date,
    DATEDIFF('day', MAX(o.processed_at), CURRENT_DATE) AS days_since_order
FROM klaviyo.profiles p
LEFT JOIN matrixify.orders o
    ON o.email = p.email
    AND o.financial_status NOT IN ('refunded', 'voided')
WHERE p.churn_risk IN ('high', 'winback')
  AND p.predicted_clv_1y > 200
GROUP BY 1,2,3,4,5
HAVING DATEDIFF('day', MAX(o.processed_at), CURRENT_DATE) > 60 -- replace: your lapse window in days (e.g. avg days between orders × 1.5)
ORDER BY p.predicted_clv_1y DESC
LIMIT 500;
↑ Top › Key Questions › Metrics Framework › Strategy 2 — Channel Efficiency
Strategy 2

Channel Efficiency Reallocation

Most DTC brands over-invest in paid, under-invest in owned. Klaviyo should generate 30–40% of revenue — if it doesn't, that gap is free money left on the table.

Why This Strategy

Paid channels have a built-in ceiling — as spend increases, marginal efficiency drops. Email has near-zero marginal cost and compounds: a list built today generates revenue for years. VAHDAM's Klaviyo investment should be benchmarked against total net sales monthly, and any gap below 30% should trigger a flow audit before increasing paid spend.

Monthly Budget Reallocation Workflow

Shopify
Analytics
→
CAC by
channel
→
LTV by
channel cohort
→
LTV:CAC
ratio
→
>[ YOUR SCALE THRESHOLD ] → increase
<[ YOUR PAUSE THRESHOLD ] → pause
→
Higher blended
ROAS

Email Revenue Gap Workflow

Klaviyo
campaigns
→
Email revenue
% of total
→
If < [ YOUR EMAIL FLOOR % ]
→ gap identified
→
Audit missing
flows
→
Build & activate
flows
→
Incremental
revenue

Target Benchmarks

MetricFloor / TargetAction if Below
LTV:CAC ratio (all paid channels)[ 3:1 — INDUSTRY BENCHMARK ]Reduce spend on underperforming channel; reallocate to email or top performer
Email revenue % of net sales[ 30–40% — INDUSTRY BENCHMARK ]Audit flow coverage: welcome, abandon cart, post-purchase, win-back, browse abandon must all be live
Klaviyo CTOR (click-to-open rate)[ 15%+ — INDUSTRY BENCHMARK ]Segment sends more tightly; test subject lines; review list hygiene
Revenue per Klaviyo recipient[ $0.10+ — INDUSTRY BENCHMARK ]Improve personalisation; introduce dynamic product recommendations
Paid CAC — US market[ YOUR US CAC CEILING ]Pause high-CAC ad sets; consolidate to best-performing creative
Paid CAC — UK market[ YOUR UK CAC CEILING ]UK CPMs higher; consider retention-first approach, reduce new acquisition spend

Metrics Used

LTV:CAC by Channel CAC by Channel Email Revenue % Klaviyo CTOR Revenue per Recipient
💰
Revenue Attribution Funnel
How gross revenue flows through discounts and channel attribution to arrive at net channel contribution
Gross Revenue (all orders)
[ RUN M1: gross sales ]
100% of top
[ YOUR DISCOUNT RATE ] drop to next stage
Net Revenue (after discounts)
[ RUN M1: net sales ]
92% of top
[ YOUR EMAIL SHARE ] drop to next stage
Email-attributed (Klaviyo)
[ RUN M7: email revenue % ]
35% of top
[ remaining to paid ] drop to next stage
Paid channel revenue
[ RUN M6: CAC by channel ]
40% of top
[ YOUR ORGANIC % ] drop to next stage
Organic / direct revenue
[ remaining = organic + direct ]
17% of top
Each bar = share of the stage above. Values are placeholders — run the linked metric query to fill with your real data.
Key DuckDB Query — LTV:CAC by Channel
-- LTV:CAC ratio per acquisition channel — flag below 3:1
WITH customer_ltv AS (
    SELECT
        COALESCE(utm_source, 'organic') AS channel,
        customer_id,
        SUM(total_price)              AS lifetime_value
    FROM matrixify.orders
    WHERE financial_status NOT IN ('refunded', 'voided')
      AND cancelled_at IS NULL
    GROUP BY 1, 2
),
cac_data AS (
    SELECT channel,
           SUM(spend)         AS total_spend,
           SUM(new_customers) AS new_customers
    FROM shopify_analytics.marketing_attribution
    GROUP BY channel
)
SELECT
    l.channel,
    ROUND(AVG(l.lifetime_value), 2)                          AS avg_ltv,
    ROUND(c.total_spend / NULLIF(c.new_customers, 0), 2)  AS cac,
    ROUND(AVG(l.lifetime_value) /
          NULLIF(c.total_spend / NULLIF(c.new_customers,0),0), 2) AS ltv_cac_ratio,
    CASE WHEN AVG(l.lifetime_value) /
              NULLIF(c.total_spend / NULLIF(c.new_customers,0),0) < 3
         THEN '⚠ BELOW 3:1 — REVIEW' -- replace 3 with your minimum acceptable LTV:CAC ratio
         ELSE '✓ OK' END                                        AS flag
FROM customer_ltv l
LEFT JOIN cac_data c USING (channel)
GROUP BY l.channel, c.total_spend, c.new_customers
ORDER BY ltv_cac_ratio DESC NULLS LAST;
↑ Top › Key Questions › Metrics Framework › Strategy 3 — Geo Prioritisation
Strategy 3

Geo-Market LTV Prioritisation

US and UK customers have distinct LTV profiles and different price sensitivities. Market-specific CAC thresholds drive far better budget allocation than blended averages.

Why This Strategy

VAHDAM operates across three meaningful markets with meaningfully different economics. UK customers show slightly lower AOV but strong repeat frequency. US customers show higher AOV. IN customers have the lowest absolute LTV but organic acquisition cost near zero. Each market warrants a different playbook — conflating them into a blended average destroys insight.

Market Budget Allocation Decision Tree
Quarterly LTV by Market Pull US — Primary UK — Retain IN — Organic LTV:CAC > 3 : 1 ? LTV:CAC > 2.5 : 1 ? Paid spend relevant? YES NO Scale paid + sub conv. Pause: audit CAC ceiling YES NO Retain: email- first strategy Reduce paid; retention only YES NO SEO + organic social only Gifting + corporate Reallocate quarterly marketing budget
Market Budget Allocation Decision Tree
Quarterly LTV by Market Pull US — Primary UK — Retain IN — Organic LTV:CAC > 3 : 1 ? LTV:CAC > 2.5 : 1 ? Paid spend relevant? YES NO Scale paid + sub conv. Pause: audit CAC ceiling YES NO Retain: email- first strategy Reduce paid; retention only YES NO SEO + organic social only Gifting + corporate Reallocate quarterly marketing budget

Quarterly Geo Reallocation Workflow

matrixify
.orders
→
LTV by
country
→
AOV + order
frequency/market
→
Market CAC
ceiling
→
Budget
reallocation

Market Playbooks

🇺🇸 United States
Primary
Largest revenue share ([ YOUR US REV % ]), highest AOV. Highest competition for paid.
  • →Aggressive new acquisition while LTV:CAC > 3:1
  • →Full flow coverage in Klaviyo
  • →Subscription conversion priority
  • →CAC ceiling: [ YOUR US CAC CEILING ]
🇬🇧 United Kingdom
Retention-First
[ YOUR UK REV % ] revenue share. Strong tea culture = high repeat propensity. CPMs higher than US.
  • →Reduce new acquisition paid spend
  • →Prioritise Klaviyo retention flows
  • →UK-specific copy (£ pricing, heritage)
  • →CAC ceiling: [ YOUR UK CAC CEILING ]
🇮🇳 India
Organic Only
[ YOUR IN REV % ] revenue share, lowest AOV in absolute $. Price-sensitive market.
  • →Zero paid acquisition spend
  • →SEO + organic social only
  • →Gifting + corporate channels
  • →Monitor LTV trend quarterly

Metrics Used

LTV by Market Net Revenue by Market AOV by Market Repeat Rate by Market
Key DuckDB Query — LTV by Market
-- Per-market LTV, AOV, order frequency, repeat rate
WITH customer_stats AS (
    SELECT
        customer_id,
        CASE
            WHEN shipping_country_code = 'US' THEN 'US'
            WHEN shipping_country_code = 'GB' THEN 'UK'
            WHEN shipping_country_code = 'IN' THEN 'IN'
            ELSE 'RoW'
        END                         AS market,
        SUM(total_price)            AS ltv,
        COUNT(*)                    AS order_count,
        AVG(total_price)            AS aov
    FROM matrixify.orders
    WHERE financial_status NOT IN ('refunded', 'voided')
      AND cancelled_at IS NULL
    GROUP BY customer_id, shipping_country_code
)
SELECT
    market,
    COUNT(DISTINCT customer_id)      AS customers,
    ROUND(AVG(ltv), 2)              AS avg_ltv,
    ROUND(MEDIAN(ltv), 2)           AS median_ltv,
    ROUND(AVG(order_count), 2)      AS avg_orders,
    ROUND(AVG(aov), 2)             AS avg_aov
FROM customer_stats
GROUP BY market
ORDER BY avg_ltv DESC;
↑ Top › Key Questions › Metrics Framework › Strategy 4 — Subscription Flywheel
Strategy 4

Subscription Conversion Flywheel

Subscription customers generate 3–5× the LTV of one-time buyers with dramatically lower churn. Growing subscription mix % is the highest-leverage revenue stability play available to VAHDAM.

Why This Strategy

Subscription revenue is predictable, compounds with retention, and makes paid acquisition math work more favourably. At [ YOUR CURRENT SUB MIX % ] subscription mix, VAHDAM is already well-positioned — the opportunity is to push this to [ YOUR TARGET SUB MIX % ] by converting the highest-repeat one-time buyers at the moment of peak engagement (post-purchase day 7), and by making subscription the frictionless default for the top 5 SKUs.

Conversion Workflow

One-time
buyers
→
Identify
repeat SKUs
→
Day 7
post-purchase
→
Subscription offer
+ savings
→
Higher LTV
+ lower churn

Subscription Health Monitoring

Weekly
check
→
Sub mix %
vs target
→
Subscriber
churn rate
→
Pause vs
cancel ratio
→
Intervene
before cancel

LTV Comparison: Subscriber vs One-Time Buyer

One-Time Buyers
baseline
Avg LTV: [ YOUR ONE-TIME LTV ]
Repeat (Non-Sub)
[ REPEAT / OTB ]
Avg LTV: [ YOUR REPEAT LTV ]
Subscribers
[ SUB / OTB ]
Avg LTV: [ YOUR SUBSCRIBER LTV ]

Metrics Used

Subscription Mix % Product Repeat Rate LTV Sub vs One-Time Klaviyo Flow Conversion
🔄
Subscription Conversion Funnel
From all one-time buyers to active subscribers — where the conversion opportunity lives
All one-time buyers (active base)
[ RUN M2: one-time count ]
100% of top
[ eligible SKU filter ] drop to next stage
Bought a subscription-eligible SKU
[ RUN M15: product repeat rate ]
72% of top
[ sub offer coverage ] drop to next stage
Received a subscription offer (flow)
[ Klaviyo flow sent count ]
55% of top
[ YOUR OFFER CVR ] drop to next stage
Converted to subscription
[ RUN M10: sub mix % ]
10% of top
[ 90d retention ] drop to next stage
Still active subscriber at 90 days
[ Recharge / sub app data ]
7% of top
Each bar = share of the stage above. Values are placeholders — run the linked metric query to fill with your real data.
Key DuckDB Query — Top Subscription Conversion Candidates
-- One-time buyers of high-repeat SKUs: prime sub conversion targets
WITH buyer_history AS (
    SELECT
        o.customer_id,
        o.email,
        li.product_id,
        li.title AS product_title,
        COUNT(DISTINCT o.id)   AS times_purchased,
        MAX(o.processed_at)    AS last_purchase,
        MAX(CASE WHEN
            LOWER(CAST(li.properties AS VARCHAR)) LIKE '%subscription%'
            THEN 1 ELSE 0 END)  AS already_subscribed
    FROM matrixify.order_line_items li
    JOIN matrixify.orders o ON li.order_id = o.id
    WHERE o.financial_status NOT IN ('refunded', 'voided')
    GROUP BY 1, 2, 3, 4
)
SELECT customer_id, email, product_title,
       times_purchased,
       DATEDIFF('day', last_purchase, CURRENT_DATE) AS days_since_last
FROM buyer_history
WHERE times_purchased >= 2
  AND already_subscribed = 0
  AND DATEDIFF('day', last_purchase, CURRENT_DATE) BETWEEN 5 AND 21 -- replace upper bound: use Metric 12 median_days for your data
ORDER BY times_purchased DESC, days_since_last
LIMIT 1000;
↑ Top › Key Questions › Metrics Framework › Strategy 5 — Funnel Conversion
Strategy 5

Checkout Funnel Pressure Reduction

WebEngage gives event-level funnel data Shopify doesn't. A 2% improvement in checkout conversion rate is material incremental revenue with zero additional spend.

Why This Strategy

Conversion rate optimisation is the only channel where the return is unlimited — fixing a funnel leak converts already-acquired traffic. WebEngage captures the full event stream from product view to order confirmation, making it possible to pinpoint exactly where and when users drop off, on which device, from which market. Every percentage point recovered here is the most efficient revenue possible.

Weekly Funnel Audit Workflow

webengage
.events
→
PV→Cart
rate
→
Cart→Checkout
rate
→
Checkout→
Purchase rate
→
If drop >5%
WoW → alert
→
Identify stage
→ intervene

Funnel Stages

100%
Product
Viewed
baseline
→
[ YOUR ATC RATE ]
Added
To Cart
↓ [ YOUR PV→ATC DROP ] drop
→
[ YOUR CHK RATE ]
Checkout
Created
↓ [ YOUR ATC→CHK DROP ] drop
→
[ YOUR CVR ]
Order
Created
✓ goal

Decision Tree

If
Drop at Cart → Checkout > 5% WoW
Then
Trigger Klaviyo cart abandonment flow within 1 hour. Check if price increase or shipping cost change coincides.
If
Drop at Checkout → Purchase
Then
Audit payment options (BNPL availability?), shipping costs, and trust signals. A/B test checkout page layout.
If
Mobile CVR < Desktop CVR by > [ YOUR MOBILE GAP THRESHOLD ]
Then
Mobile checkout UX review. Check tap target sizes, form fields, Apple/Google Pay prominence, page load time on 4G.
If
US / UK conversion gap > [ YOUR GEO CVR THRESHOLD ]
Then
Localise checkout: ensure local currency, market-specific shipping options displayed, UK trust badges (reviews, delivery SLA).

Metrics Used

Checkout Conversion Rate Cart Abandonment Rate Device Split WebEngage Funnel Events
🛒
Checkout Conversion Funnel
Every stage where a potential buyer is lost — the exact drop-off points that determine weekly revenue
Product Viewed
[ RUN M9: product_viewed ]
100% of top
[ YOUR PV→ATC DROP ] drop to next stage
Added to Cart
[ RUN M9: added_to_cart ]
14% of top
[ YOUR ATC→CHK DROP ] drop to next stage
Checkout Created
[ RUN M9: checkout_created ]
9% of top
[ YOUR CHK→ORDER DROP ] drop to next stage
Order Created
[ RUN M9: order_created ]
6% of top
Each bar = share of the stage above. Values are placeholders — run the linked metric query to fill with your real data.
Key DuckDB Query — Weekly Funnel Drop-Off
-- Weekly conversion funnel with WoW drop detection
WITH weekly_funnel AS (
    SELECT
        DATE_TRUNC('week', summary_date)   AS week,
        event_name,
        SUM(event_count)               AS cnt
    FROM webengage.event_summary
    WHERE event_name IN (
        'Product Viewed', 'Added To Cart',
        'Checkout created', 'Order created'
    )
    GROUP BY 1, 2
),
pivoted AS (
    SELECT week,
        MAX(CASE WHEN event_name='Product Viewed'   THEN cnt END) AS pv,
        MAX(CASE WHEN event_name='Added To Cart'    THEN cnt END) AS atc,
        MAX(CASE WHEN event_name='Checkout created' THEN cnt END) AS chk,
        MAX(CASE WHEN event_name='Order created'    THEN cnt END) AS ord
    FROM weekly_funnel GROUP BY week
)
SELECT
    week,
    ROUND(atc  * 100.0 / NULLIF(pv,  0), 1) AS pv_to_atc_pct,
    ROUND(chk  * 100.0 / NULLIF(atc, 0), 1) AS atc_to_chk_pct,
    ROUND(ord  * 100.0 / NULLIF(chk, 0), 1) AS chk_to_ord_pct,
    ROUND(ord  * 100.0 / NULLIF(pv,  0), 1) AS overall_cvr_pct,
    CASE WHEN
        (ROUND(ord*100.0/NULLIF(pv,0),1) -
         LAG(ROUND(ord*100.0/NULLIF(pv,0),1)) OVER (ORDER BY week)) < -5
    THEN '⚠ ALERT: CVR dropped >5%' -- replace -5 with your own alert sensitivity
    ELSE '' END                              AS alert
FROM pivoted
ORDER BY week DESC
LIMIT 12;
↑ Top › Key Questions › Metrics Framework › Data Architecture
Data Architecture

How the Data Flows — Story by Story

Four sources, one database, fifteen metrics. Every number in this document traces back to a specific table, a specific join, a specific SQL block. This section makes that path visible.

1

The Flow — where data comes from, where it lands, what it produces

Everything begins as a CSV export. Every number you act on begins here.

Data Sources
📦 Matrixify
Shopify raw export app
orders.csv, customers.csv, products.csv, order_line_items.csv…
📊 Shopify Analytics
Pre-aggregated reports
revenue_metrics.csv, acquisition_metrics.csv, marketing_attribution.csv…
✉️ Klaviyo
Email + SMS CDP
profiles.csv, campaigns.csv, flows.csv, events.json…
📱 WebEngage
Behavioural event stream
events.csv, user_profiles.csv, revenue_mapping.csv…
→
vahdam_dtc.duckdb
matrixify
20 tables · raw transactional data
shopify_analytics
13 tables · aggregated channel & revenue reports
klaviyo
9 tables · email profiles, campaigns, churn predictions
webengage
4 tables · behavioural events + funnel summaries
python run_all.py → upsert + dedup on every run
→
What It Produces
📐
15 Metric Queries
queries/metrics.sql — M1 through M15 + BONUS. Run once against real data to fill every placeholder in this document.
🗺
5 Growth Strategies
Each strategy in this document maps to specific metric outputs — retention, channel, geo, subscription, funnel.
⚡
6 Cross-Source Signals
Metrics that require joining across schemas — LTV:CAC, at-risk revenue, email revenue %, SKU subscription gap.
2

The Four Schemas — purpose, key tables, what each one powers

Each schema is a world with a specific job. Understanding the job determines which schema to query first.

matrixify
The source of truth for every customer transaction. Every order, every line item, every product and variant lives here as Shopify exported it.
Key Tables
ordersorder_line_items customersproducts product_variantsrefunds
Powers
M1–M5 · M8 · M10–M15 Cohort retention LTV by market
shopify_analytics
Pre-aggregated Shopify reports. Where matrixify is rows, shopify_analytics is summaries — daily revenue, sessions, channel attribution already rolled up.
Key Tables
revenue_metrics acquisition_metrics marketing_attribution channel_performance
Powers
M3 LTV:CAC M6 CAC by channel M7 Email revenue %
klaviyo
The CDP layer. Klaviyo knows the future that Shopify can't see — churn risk scores, predicted CLV, and which email flow influenced which order.
Key Tables
profiles campaigns flows events deliverability_metrics
Powers
M7 Email % M13 Churn risk M14 At-risk revenue
webengage
The event layer. Every product view, cart add, checkout start, and order is a timestamped event. This schema turns the storefront into a funnel.
Key Tables
events event_summary user_profiles revenue_mapping
Powers
M9 Checkout funnel Device + geo split Weekly CVR trend
3

Three joins that matter — and why each one exists

Most of the analytic power in this system comes from three cross-table relationships. Each join connects a piece of data that neither table can answer alone.

1
matrixify.orders
JOIN
matrixify.order_line_items
ON order_line_items.order_id = orders.id
Plain English: An order is a basket receipt — it tells you the total and the customer. A line item is each individual product in that basket. This join lets you go from "VAHDAM made $50 on this order" to "this customer bought Turmeric Latte (×2) and Earl Grey Tin (×1)." Without it, you can measure revenue but not what people actually buy.
Enables: M5 Gross Margin by Product M10 Subscription Mix M15 Product Repeat Rate SKU subscription gap analysis
2
klaviyo.profiles
JOIN
matrixify.orders
ON profiles.email = orders.email
Plain English: Klaviyo knows the prediction — this customer is high churn risk, their predicted 12-month CLV is $180. Shopify knows the history — this customer bought 4 times, spent $240 total. Joining them on email bridges the future forecast to the purchase record. The result: you can say "we have $X in predicted revenue from customers who are likely to churn this quarter" — a dollar figure that justifies win-back spend.
Enables: M13 Churn Risk Distribution M14 At-Risk Revenue CLV validation vs Klaviyo prediction
3
klaviyo.campaigns
JOIN
shopify_analytics.revenue_metrics
ON DATE_TRUNC('month', sent_at) = month
Plain English: Klaviyo tracks what email generated ($12K attributed to this campaign). Shopify tracks total revenue ($80K net sales this month). Joining them by calendar month and dividing gives you the email revenue share — the single most important measure of whether your owned channel is pulling its weight. If email accounts for less than 20% of revenue, you are over-dependent on paid channels that can become expensive or unavailable overnight.
Enables: M7 Email Revenue % Channel efficiency benchmark Flow vs campaign contribution split
4

Three views — pre-built queries you get out of the box

These are the three queries you will run most. Each one joins across schemas and produces a result you can act on directly.

customer_lifetime_value
One row per customer — LTV, order count, market, churn risk, and predicted CLV side by side.
matrixify × klaviyo
Output Columns
customer_idVARCHAR
marketVARCHARUS / UK / IN / RoW
total_ordersINT
actual_ltvDECIMALsum of all orders
predicted_clv_1yDECIMALfrom Klaviyo model
churn_riskVARCHARlow/medium/high/winback
Use It For
Export high-LTV + high-risk customers → Klaviyo win-back segment
Compare Klaviyo predicted_clv vs actual_ltv to validate model accuracy
Set per-market CAC ceiling: median_ltv ÷ target_ratio
Feed subscription conversion targeting: high LTV + no active sub
weekly_checkout_funnel
Weekly conversion rates at every funnel stage — with automatic alert flag when CVR drops >5% WoW.
webengage.event_summary
Output Columns
weekDATE
product_viewedINTtop of funnel
added_to_cartINT
checkout_createdINT
order_createdINTbottom of funnel
overall_cvr_pctDECIMALview → order
alertVARCHARfires if CVR drops >5%
Use It For
Spot the exact funnel stage where this week's revenue dropped
Distinguish traffic quality decline from UX/checkout friction
Alert fires automatically — no manual monitoring needed
Segment by device / market in WHERE clause for root cause
channel_roi
CAC, LTV, LTV:CAC ratio, and traffic volume per acquisition channel — the monthly budget reallocation screen.
shopify_analytics × matrixify
Output Columns
channelVARCHARgoogle / meta / email / organic…
new_customersINT
total_spendDECIMAL
cacDECIMALspend ÷ new_customers
avg_ltvDECIMALfrom matrixify.orders
ltv_cac_ratioDECIMALflag if <3:1
Use It For
Monthly budget meeting: which channels to scale vs pause
Any channel below 2:1 for 2 months → pause spending immediately
Set CAC ceiling per market: UK median LTV ÷ 3 = UK CAC ceiling
Track email as a channel — its CAC is near zero, making its ratio the benchmark
→

Path to scale

DuckDB (now)
Local file, instant queries, zero infra cost
→
MotherDuck
Same SQL, cloud hosted, team-shareable
→
BI Layer
Metabase / Evidence.dev dashboards
→
Alerting
Metric drops trigger Slack / email
↑ Top › Key Questions › Metrics Framework › Roadmap
Implementation Roadmap

From Setup to Autonomous Analytics

Four phases from first CSV to a fully automated, alerting analytics layer running on real VAHDAM data.

Implementation Phase Gate Progression
1 Week 1 Foundation First data in GATE 1 ✓ 2 Wk 2–3 Full Coverage All 4 sources + M15 GATE 2 ✓ 3 Month 2 Automate Dashboards + flows GATE 3 ✓ 4 Month 3+ Intelligence Predict · Alert · Act GATE 4 ✓ Start Month 3+
Implementation Phase Gate Progression
1 Week 1 Foundation First data in GATE 1 ✓ 2 Wk 2–3 Full Coverage All 4 sources + M15 GATE 2 ✓ 3 Month 2 Automate Dashboards + flows GATE 3 ✓ 4 Month 3+ Intelligence Predict · Alert · Act GATE 4 ✓ Start Month 3+
1
Week 1
Foundation — First Data In
  • Run python _init_db.py to create vahdam_dtc.duckdb with all 46 tables
  • Export orders.csv + customers.csv from Matrixify, drop into data/matrixify/
  • Run python run_all.py --source matrixify
  • Execute Metrics 1, 2, 4, 8 (revenue, new/returning, repeat rate, AOV) manually
  • Validate row counts match Shopify admin dashboard
2
Weeks 2–3
Full Source Coverage + Validation
  • Export and load all 4 sources — Matrixify, Shopify Analytics, Klaviyo, WebEngage
  • Run all 15 metric queries, cross-check totals against source dashboards
  • Identify discrepancies (attribution windows, timezone differences) and document
  • Build first LTV:CAC comparison by channel (Strategy 2)
  • Export churn risk list from Klaviyo, import profiles.csv, run Metric 13 + 14
3
Month 2
Automate + Visualise
  • Schedule python run_all.py nightly via Windows Task Scheduler or cron
  • Connect Metabase or Evidence.dev to vahdam_dtc.duckdb (or migrate to MotherDuck)
  • Build dashboards for the 5 Tier 1 metrics (auto-refresh daily)
  • Configure Klaviyo win-back flow based on Metric 13 churn segments
  • Set AOV alert: flag if MoM drops >5% (Metric 8)
4
Month 3+
Intelligence Layer — Predict, Alert, Act
  • Add Slack/email alerting: metric drops trigger notifications to team
  • Build subscription conversion audiences: export from DuckDB → Klaviyo segment daily
  • Add RFM segmentation on top of existing cohort queries
  • Evaluate MotherDuck for team-shared access and scheduled queries
  • Expand to predictive: time-series AOV forecast, seasonal demand signals
↑