Growth Metrics & Unit Economics Questions

Situation: You need channel-level CAC and LTV that stakeholders trust, but instrumentation is limited and resources are constrained.

Pros/Cons

• Last-touch
  • Pros: Simple, deterministic, easy to compute from conversion event → channel; low engineering overhead; aligns with many marketing reports.
  • Cons: Over-credits the final touch, ignores upper-funnel influence; biases toward retargeting/paid search; distorts CAC and LTV decisions for channels that assist early.
• Multi-touch (heuristic or algorithmic)
  • Pros: More accurate reflection of the customer journey; distributes credit to awareness and nurturing channels; better for optimizing full-funnel spend.
  • Cons: Requires better instrumentation (user-level IDs, cross-device tracking), more complex pipelines, assumptions in model (rules or attribution algorithm) can be contested.

Practical approach for a mid-size company

1. Adopt a hybrid, pragmatic attribution: default to last-touch for reporting speed but provide a multi-touch “assisted” view using a simple, explainable rule (e.g., position-based: 40% first touch, 40% last touch, 20% evenly split across middle touches) and time-decay as an optional variant.
2. Instrumentation priorities: ensure persistent user_id (login or cookie + hashing), consistent UTM capture on entry, and store touch timestamp + channel/source/medium.
3. Reporting cadence: use last-touch in real-time dashboards; run nightly batch multi-touch attribution to produce adjusted CAC/LTV for weekly/monthly strategic reports.

Implementation in analytics pipelines

• Data collection: capture touch events table (user_id, touch_id, timestamp, channel, campaign, utm_params, session_id).
• Sessionize & stitch: backfill/clean user identifiers; deduplicate bots; join touches to conversion/payment events via user_id and time window (e.g., 90 days).
• Attribution job (SQL/ETL):
  • Build touch sequences per user → apply rule-based weights to each touch.
  • Aggregate weighted cost: channel_CAC = sum(weight * cost_at_touch) / conversions; LTV by assigning conversion/revenue credit proportionally.
    Example pseudocode SQL steps:
  1. window over user_id order by timestamp to enumerate touches;
  2. assign weight per position or time-decay factor;
  3. sum(weight * revenue) by channel; divide by conversions to get CAC.
• Validation & monitoring: compare last-touch vs multi-touch weekly; monitor shifts > X% and investigate; sample check with manual funnel analysis.
• Governance: document model, assumptions, and refresh cadence; store both metrics (last-touch and multi-touch) in BI layers so stakeholders can choose.

Key trade-offs and communication

• Be transparent: show both metrics and explain biases. Use last-touch for tactical campaign reporting, multi-touch for strategic budget allocation. Iterate attribution model as instrumentation improves (move to probabilistic or algorithmic models when user linking and event coverage are robust).

KPI 1 — Weekly cohort LTV drop >15% vs prior 4-week moving average

• Alert: Trigger when cohort-week LTV declines >15% and absolute change >$2 to avoid noise.
• Rationale: Catches sharp, material declines quickly while ignoring small volatility.
• Initial diagnostics:
  1. Cohort-level trend over 12 weeks to confirm persistence:

SELECT cohort_week, AVG(lifetime_value) AS avg_ltv
FROM cohort_ltv
WHERE cohort_id = :cohort
GROUP BY cohort_week
ORDER BY cohort_week DESC
LIMIT 12;

2. Check new versus returning user LTV split:

SELECT user_type, AVG(lifetime_value) FROM users u
JOIN cohort_ltv c ON u.user_id = c.user_id
WHERE c.cohort_id = :cohort AND c.cohort_week = :week
GROUP BY user_type;

KPI 2 — Day-over-day active payer count drop >20% for same cohort window

• Alert: Trigger when paying users fall sharply — often drives LTV declines before revenue changes.
• Diagnostics:
  1. Daily paying users and revenue per payer:

SELECT day, COUNT(DISTINCT user_id) AS payers, SUM(amount)/NULLIF(COUNT(DISTINCT user_id),0) AS rev_per_payer
FROM payments
WHERE cohort_week BETWEEN :start AND :end
GROUP BY day ORDER BY day DESC LIMIT 14;

2. Segmentation by channel/campaign to isolate acquisition issues.

KPI 3 — Gross margin per user drop >10% vs baseline month

• Alert: Trigger if margins shrink (price, discounts, costs) — LTV may fall even if revenue stable.
• Diagnostics:
  1. Margin components trend:

SELECT month, SUM(revenue) AS rev, SUM(cost) AS cost, SUM(revenue)-SUM(cost) AS gross_margin, (SUM(revenue)-SUM(cost))/COUNT(DISTINCT user_id) AS margin_per_user
FROM monthly_financials
WHERE cohort_month >= date_trunc('month', current_date)-interval '3 months'
GROUP BY month;

2. Drill into refund rates, promo codes, and product mix by cohort.

Alerting strategy (general)

• Multi-stage alerts: Warning (5–10% drop) email to analytics + product; Critical (threshold hit) Slack + paging to ops and product owners.
• Include automated snapshot in alert with top 5 diagnostic query results and affected cohorts.
• Suppression window (24–48h) and auto-escalation if persists for 2 consecutive checks.
• Attach runbook with next steps: validate data quality (ETL lag, schema changes), confirm sample users, check upstream events (promo start, pricing changes), and notify responsible stakeholders.

This approach balances sensitivity and noise reduction, provides immediate diagnostics to triage root cause, and embeds operational response.

Customer Acquisition Cost (CAC): the average cost to acquire one paying customer, calculated as total sales & marketing spend over a period divided by number of new customers acquired in that period. It captures acquisition efficiency and should be measured per channel and cohort.

Customer Lifetime Value (LTV): the net present value of gross profit from a customer over their expected relationship with the company — typically average revenue per user (ARPU) × gross margin × expected lifetime (or 1/(churn rate) for subscription models). LTV shows how much value a customer brings.

LTV:CAC ratio significance: this ratio measures payback and unit economics. A higher ratio means each dollar spent on acquisition returns proportionally more long-term value — guiding whether growth is sustainable, how much you can bid in paid channels, and whether to prioritize retention vs acquisition.

Two rule-of-thumb thresholds to recommend scaling paid acquisition:

1. LTV:CAC ≥ 3:1 — generally indicates attractive unit economics and justifies increased paid spend (after ensuring accuracy of LTV inputs and channel-level performance).
2. Payback period ≤ 12 months — even if LTV:CAC is healthy, require that CAC is recovered within ~12 months (or business-specific target) to preserve cash flow and enable rapid reinvestment.

Practical BI notes: report both cohort LTV and channel-level CAC, show sensitivity (churn, margin), and include payback curves so stakeholders can see trade-offs before scaling.

Approach: For each user, determine their acquisition month (DATE_TRUNC to month), then sum transactions that occurred within 90 days (3 months) from acquired_at (inclusive). Aggregate revenue by acquisition channel + month and divide by count of users acquired in that month to get Revenue Per Acquired User (RPAU).

Example schema snippet:
users(user_id INT, acquired_channel VARCHAR, acquired_at TIMESTAMP)
transactions(transaction_id INT, user_id INT, amount DECIMAL(10,2), occurred_at TIMESTAMP)

Assumptions:

• All timestamps in UTC (or already normalized); if not, normalize with AT TIME ZONE.
• Null acquired_channel treated as 'unknown'.
• Users with no transactions contribute zero revenue.
• "3-month" defined as 90 days (or use DATE_ADD/INTERVAL '3' MONTH for calendar months—choose one and be consistent).

Query (ANSI SQL, using INTERVAL for 3 months):

WITH users_norm AS (
  SELECT
    user_id,
    COALESCE(acquired_channel, 'unknown') AS acquired_channel,
    DATE_TRUNC('month', acquired_at) AS acq_month,
    acquired_at
  FROM users
),
tx_in_window AS (
  SELECT
    u.acquired_channel,
    u.acq_month,
    u.user_id,
    SUM(t.amount) AS revenue_3m
  FROM users_norm u
  LEFT JOIN transactions t
    ON t.user_id = u.user_id
    AND t.occurred_at >= u.acquired_at
    AND t.occurred_at < u.acquired_at + INTERVAL '3' MONTH
  GROUP BY u.acquired_channel, u.acq_month, u.user_id
),
agg AS (
  SELECT
    acquired_channel,
    acq_month,
    COUNT(user_id) AS users_acquired,
    SUM(COALESCE(revenue_3m, 0)) AS total_revenue_3m
  FROM tx_in_window
  GROUP BY acquired_channel, acq_month
)
SELECT
  acquired_channel,
  acq_month,
  users_acquired,
  total_revenue_3m,
  CASE WHEN users_acquired = 0 THEN 0
       ELSE total_revenue_3m / users_acquired END AS revenue_per_acquired_user_3m
FROM agg
ORDER BY acquired_channel, acq_month;

Key points:

• Use LEFT JOIN so users without transactions are included (revenue = 0).
• Use DATE_TRUNC('month', acquired_at) for cohort month.
• Choose INTERVAL '3' MONTH for calendar-based cohorts; if business prefers exact 90-day windows, replace with INTERVAL '90' DAY.
• Handle null channels and null timestamps per data quality rules.

Approach summary: build a per-customer unit-economics sheet that separately captures direct revenue, direct costs, allocated support costs, and expected fraud loss. Compare alternative allocation rules (per-ticket, per-user, hybrid) and validate which best explains observed downstream costs/behaviour.

Concrete spreadsheet layout (single workbook, one row per customer / cohort):

• Raw inputs sheet:
  • customer_id, cohort_month, lifetime_days, revenue_period, tickets_created, tickets_resolved, avg_handle_time_min, support_agent_hourly_cost, fraud_flag (1/0), fraud_loss_amount, tx_count, avg_tx_value
• Cost parameters sheet:
  • agent_burden_rate, fixed_support_ovh, fraud_detection_cost_per_case, model_assumptions
• Allocation sheet (calculation columns per customer):
  • Support_Cost_PerTicket = (support_agent_hourly_cost/60)*avg_handle_time_min
  • Allocated_Support_ByTicket = tickets_created * Support_Cost_PerTicket
  • Allocated_Support_PerUser = (fixed_support_ovh + SUM(all agent labor))/TOTAL_ACTIVE_USERS * 1 (or prorate by usage)
  • Hybrid_Allocated = alpha * Allocated_Support_ByTicket + (1-alpha) * Allocated_Support_PerUser
  • Fraud_Loss_Observed = fraud_loss_amount
  • Fraud_Expected_Model = prob_fraud(customer_features) * avg_fraud_loss
  • Unit_Contribution = revenue_period - direct_costs - Allocated_Support_* - Fraud_Allocated

How to test which allocation best reflects economics:

1. Fit explanatory models: run regressions where the dependent variable is observed downstream support-related costs or churn and independent variables include allocated support under each method. Higher R² and significant coefficients indicate better explanatory power.
   • Example: churn_binary ~ Allocated_Support_PerTicket + controls; compare AIC/BIC and ROC-AUC.
2. Prediction / holdout test: use past period to compute allocations, predict next-period actual support spend, churn, or refund volume. Evaluate RMSE/MAPE for each allocation method.
3. Correlation with driver metrics: check correlation of allocations with drivers (tickets, active usage, tx_count). The allocation that aligns with causal drivers and minimizes residuals is preferred.
4. Sensitivity & scenario analysis: sweep alpha in hybrid to find minimization of prediction error; run cohort-level P&L under different allocations.
5. Business validation: present to support ops and finance—check face validity (e.g., heavy-ticket enterprise accounts should have higher allocated cost).

Practical tips:

• Start at cohort granularity to reduce noise; roll up to customer level when stable.
• Use winsorization or log transforms for skewed ticket counts.
• Include overhead as fixed vs variable split (support platform costs fixed; agent labor variable).
• Document assumptions and refresh tests quarterly.