Business Metrics and Unit Economics Questions

Approach: compute company_revenue per trip as the amount retained by the platform = gross_fare minus discount given and minus driver payout. Then aggregate by city for the last 30 days showing average company revenue per ride and the overall company take-rate (total company_revenue / total gross_fare). Handle nulls with COALESCE, exclude non-positive fares (assumed refunds or test trips).

SELECT
  r.city,
  COUNT(*)                         AS rides_count,
  SUM(company_revenue)             AS total_company_revenue,
  AVG(company_revenue)             AS avg_company_revenue_per_ride,
  SUM(gross_fare)                  AS total_gross_fare,
  -- take-rate = total company revenue divided by total gross fares; NULL if total_gross_fare = 0
  CASE WHEN SUM(gross_fare) = 0 THEN NULL
       ELSE SUM(company_revenue) / SUM(gross_fare)
  END                               AS company_take_rate
FROM (
  SELECT
    r.trip_id,
    r.city,
    -- normalize nulls to 0
    COALESCE(r.gross_fare, 0)       AS gross_fare,
    COALESCE(r.discount_amount, 0)  AS discount_amount,
    COALESCE(dp.driver_payout, 0)   AS driver_payout,
    -- company keeps fare minus discounts and driver payout
    (COALESCE(r.gross_fare, 0) - COALESCE(r.discount_amount, 0) - COALESCE(dp.driver_payout, 0))
                                    AS company_revenue
  FROM rides r
  LEFT JOIN driver_payouts dp USING (trip_id)
  WHERE r.start_time >= CURRENT_DATE - INTERVAL '30' DAY
    -- exclude clearly refunded or invalid trips (assumption)
    AND COALESCE(r.gross_fare, 0) > 0
) t
GROUP BY r.city
ORDER BY total_gross_fare DESC;

Assumptions and notes:

• company_revenue = gross_fare - discount_amount - driver_payout. Discounts are company-funded (not covered elsewhere). If promotions are recorded separately, subtract those instead.
• Null gross_fare/discount/driver_payout treated as 0 via COALESCE.
• Trips with gross_fare <= 0 are excluded (assumed refunds, test, or reversed trips). If you want to include refunded trips, adjust filter and handle negative values explicitly.
• Use SUM(company_revenue)/SUM(gross_fare) for a weighted take-rate (preferred to AVG(per-trip ratio)).
• If you need per-trip take-rate percent, compute AVG(company_revenue/gross_fare) but be careful about division-by-zero; current query returns NULL when total_gross_fare = 0.

Cohort LTV estimates lifetime value by grouping users who share an index event (e.g., acquisition week, signup month, campaign) and tracking their spend/retention over time. Aggregate-average LTV pools all users and computes an overall average revenue per user over a lookback window.

When cohort LTV is more appropriate

• When user behavior, acquisition mix, product, pricing, or seasonality change over time.
• For campaign-level ROI or channel performance decisions (attributing value to specific acquisition sources).
• When product changes (pricing, onboarding, features) likely change retention or monetization.
• For forecasting future cohorts’ value rather than backward-looking averages.

Biases introduced by aggregate LTV

• Survivor bias: older, high-value cohorts that persist inflate the average; churned cohorts’ early losses are diluted.
• Mix-shift bias: shifts in acquisition channels or geographies with different LTVs get masked.
• Time-aggregation bias: ignores lifecycle stage — early-stage users have different short-term vs long-term spend.
• Right-censoring bias: recent users have incomplete lifecycles; aggregating them with mature cohorts underestimates eventual LTV or leads to mis-timed decisions.

How cohort analysis addresses these

• Controls for time and event of acquisition so you compare like-with-like.
• Reveals trends in retention and ARPU across vintage cohorts, exposing deterioration or improvement.
• Enables survival/LTV curves and extrapolation per cohort (e.g., parametric survival models or Kaplan–Meier) to estimate full lifetime from partial data.
• Supports causal decisions: compare campaign A vs B using their cohort LTVs rather than global averages.

Practical tips for BI

• Use rolling cohort dashboards (weekly/monthly) showing retention, ARPU, cumulative LTV.
• Show confidence bounds and censoring dates; annotate product/pricing changes.
• Complement cohort LTV with aggregate metrics for high-level monitoring, but drive acquisition ROI and pricing decisions from cohort LTV.
• Where data is sparse, use hierarchical models to borrow strength across cohorts but preserve cohort-level effects.

Approach and formula

• Compute expected gross contribution each month = (avg revenue per ride) * (rides per active month) * (gross margin) * (probability the rider is active in that month).
• If retention probability r = 0.85 is the probability a rider who is active in month t remains active in month t+1, probability active in month t (starting with t=1 as cohort month) = r^(t-1).
• 12-month LTV = sum_{t=1..12} [R * M * r^(t-1)] = R * M * (1 - r^12) / (1 - r) (geometric series).

Given numbers

• Avg revenue per ride = $10
• Rides per active month = 1.2 → revenue per active month R = $12
• Gross margin M = 40% = 0.40 → margin per active month = R * M = $12 * 0.4 = $4.80
• r = 0.85

Compute

• r^12 ≈ 0.85^12 ≈ 0.1426
• (1 - r^12)/(1 - r) = (1 - 0.1426)/0.15 ≈ 0.8574/0.15 ≈ 5.716
• 12-month LTV ≈ $4.80 * 5.716 ≈ $27.44

Assumptions & interpretation

• Retention is constant month-to-month and independent of spend; no reactivation or seasonality modeled.
• No discounting of future cash flows (if needed, apply monthly discount factor).
• LTV ≈ $27.4 means an average rider from this cohort will generate ~$27.4 of gross contribution (after variable costs) over 12 months. This informs CAC targets, segmentation, and payback analyses.

Approach: convert records into a pandas DataFrame, normalize dates, filter transactions for the target month, compute total revenue per active user in that month, and return the mean (ARPU). Active user = any user with at least one transaction in the month.

import pandas as pd
from typing import List, Tuple, Union

def compute_arpu(transactions: List[Tuple[Union[int,str], float, str]], month: str) -> float:
    """
    transactions: list of (user_id, amount, occurred_at) where occurred_at is ISO date/time string
    month: target month in "YYYY-MM" format
    Returns: ARPU (float). Returns 0.0 if no active users or no transactions in month.
    """
    if not transactions:
        return 0.0

    df = pd.DataFrame(transactions, columns=["user_id", "amount", "occurred_at"])
    # parse dates robustly
    df["occurred_at"] = pd.to_datetime(df["occurred_at"], errors="coerce")
    df = df.dropna(subset=["occurred_at", "amount", "user_id"])

    # filter by month string YYYY-MM
    try:
        year, mon = map(int, month.split("-"))
    except Exception:
        raise ValueError("month must be 'YYYY-MM'")

    mask = (df["occurred_at"].dt.year == year) & (df["occurred_at"].dt.month == mon)
    df_month = df.loc[mask]

    if df_month.empty:
        return 0.0

    # total revenue per active user in month
    revenue_per_user = df_month.groupby("user_id")["amount"].sum()
    arpu = revenue_per_user.mean()
    return float(arpu)

Key points:

• Time complexity: O(n) to parse/filter/group where n = number of records.
• Space: O(n) for DataFrame.
  Edge cases handled:
• Empty input or no transactions in month -> returns 0.0
• Malformed dates are coerced to NaT and dropped
• Multiple transactions per user aggregated
• Negative/refund amounts included in net ARPU
• Invalid month format raises ValueError

Possible extensions: support timezone-aware parsing, accept month as datetime, or return ARPU alongside active user count and total revenue for dashboards.

Clarify scope & goal

• Goal: explain why cost-per-ride (CPR) rose in City X last quarter vs prior quarter. Confirm metric definition (CPR = total cost / completed rides) and data freshness (payments, incentives, distance, surge).

Key metrics to compute

• CPR overall and by component: driver_payouts_per_ride, incentives_per_ride, platform_fee_per_ride, refunds_per_ride.
• Volume metrics: rides_count, canceled_rides, avg_trip_distance, avg_trip_time.
• Mix metrics: %shared vs solo, %surge trips, vehicle type mix.
• Unit economics: gross_bookings_per_ride, net_revenue_per_ride.

Segments to slice by

• Geography (neighborhoods / zip), time (week, daypart), rider cohort (new vs repeat), vehicle type, driver tenure, fare buckets, booking channel.

Sample SQLs

-- CPR and components by week
SELECT week, 
  SUM(driver_payout) / COUNT(*) AS driver_payout_per_ride,
  SUM(incentive) / COUNT(*) AS incentive_per_ride,
  SUM(refund) / COUNT(*) AS refund_per_ride,
  SUM(total_cost) / COUNT(*) AS cost_per_ride,
  COUNT(*) as rides
FROM rides
WHERE city = 'City X' AND ride_date BETWEEN '2025-07-01' AND '2025-09-30'
GROUP BY week
ORDER BY week;

-- Avg distance and time by neighborhood
SELECT neighborhood, AVG(distance_km) as avg_km, AVG(duration_min) as avg_min, COUNT(*) as rides
FROM rides
WHERE city='City X' AND ride_date BETWEEN ...
GROUP BY neighborhood
HAVING COUNT(*) > 100;

-- Incentives trend by driver cohort
SELECT driver_cohort, week, SUM(incentive)/COUNT(*) as incentive_per_ride
FROM rides JOIN drivers USING(driver_id)
WHERE city='City X' AND ...
GROUP BY driver_cohort, week;

Charts to build

• Time-series stacked area of CPR components (driver payout, incentives, refunds, other).
• Heatmap: neighborhoods by delta CPR vs prior quarter.
• Scatter: avg_distance vs driver_payout_per_ride by neighborhood.
• Waterfall chart: decompose CPR change into contribution by component & volume.

Investigation & prioritization framework

1. Verify reporting integrity (fast, low cost): confirm no schema/reporting changes, duplicated joins, late-arriving refunds. Run row-count diffs and sanity checks. If reporting bug found, fix first.
2. Component impact analysis (high priority): decompose CPR delta into components (driver payouts, incentives, refunds). Use contribution = (component_per_ride_q2 - q1) * rides_q2 to quantify $ impact.
3. Operational drivers (medium priority): check avg distance/time, surge share, vehicle mix — large increases suggest real cost drivers.
4. Business levers (medium-high): increased incentives or new driver guarantees — check promo runs, finance spend.
5. Outliers & segmentation (medium): local events, seasonal effects, new zones or pricing changes.

Example prioritization decision rules

• If >60% of CPR increase attributable to incentives -> prioritize marketing/operations to review incentive programs.
• If driver_payout_per_ride rise correlates with avg_distance rise -> investigate routing/traffic or pricing inaccuracies.
• If reporting inconsistency explains >25% -> stop further action until corrected.

Actionable next steps

• Build the CPR components dashboard with filters for time, neighborhood, vehicle type.
• Run RCA queries above and present top 3 contributing factors with dollar impact.
• Recommend short-term fixes (pause incentive program, investigate refunds) and long-term (re-price, optimize driver supply).