O1Easy★ BA/DA to DS/MLE BridgePart O · BA-to-Python Data Science & Stats Kernels

Vectorized Cumulative Cohort ARPU Matrix (SQL to NumPy)

⚡Flashcard Drill➔

Replace slow Python loops and SQL self-joins with np.bincount, np.add.at scatter-add, and np.cumsum for exact LTV triangle matrices.

⏱️ Time: O(U + E + C*W)
💾 Space: O(C*W)
🎯 Interactive Self-Practice Loop (Attempt Before Peeking)
Practice in 4 steps: recall mechanism, complete core lines, check edge cases, and log mastery locally.
Step 1 · Mechanism Recall & Stability Self-Check
Step 2 · Complete Key Implementation Lines
import numpy as np
def compute_cohort_arpu_matrix(signup_cohorts: np.ndarray, event_offsets: np.ndarray, revenues: np.ndarray, num_cohorts: int, max_window: int) -> np.ndarray:
# signup_cohorts: (N_users,), event_offsets & revenues: (N_events,) paired with user_ids via event_user_idx
raise NotImplementedError('Use explicit signature below')
def cohort_arpu_matrix(user_cohort: np.ndarray, event_user_idx: np.ndarray, event_offset: np.ndarray, event_rev: np.ndarray, num_cohorts: int, max_offset: int) -> np.ndarray:
cohort_sizes =
valid = (event_offset >= 0) & (event_offset <= max_offset)
ev_c = user_cohort[event_user_idx[valid]]
ev_t = event_offset[valid]
ev_r = event_rev[valid]
rev_mat = np.zeros((num_cohorts, max_offset + 1), dtype=np.float64)
np.add.at(rev_mat, (ev_c, ev_t), ev_r)
denom = np.where(cohort_sizes > 0, cohort_sizes, 1.0)[:, None]
arpu = np.where(cohort_sizes[:, None] > 0, rev_mat / denom, 0.0)
return
Step 3 · Verify Boundary Cases & Assertions
Step 4 · Follow-Up Drill & Review Outcome
Q1: Why does rev_mat[ev_c, ev_t] += ev_r silently drop updates?
Q2: How do you prevent right-censored cohorts from biasing day-30 ARPU?
Next: Simpson's Paradox Detector & Kitagawa Funnel Decomposition →
💡

Core Mental Anchor / Mnemonic

bincount denominator, add.at scatter, cumsum LTV

📐 Mathematical Derivation & Core Formula

$$\text{ARPU}_{c,t} = \frac{\sum_{u \in c} R_{u,t}}{|\{u \in c : \text{active}_{u,0}\}|},\quad t \in [0, W]$$
Each cohort denominator equals initial signups ∣C∣|C| via bincount. Valid revenue events scatter-add into discrete (cohort, day_offset) buckets using unbuffered np.add.at, then divide by frozen cohort size and accumulate along axis=1 to preserve monotonicity.

🔄 Tensor Dimensions & Shape Flow

user_cohort: (U,) + event_rev: (E,) -> rev_mat: (C, W+1) -> cum_arpu: (C, W+1)

🛡️ Numerical Stability & Pitfalls Checklist

  • Always divide by initial cohort size rather than active daily survivors to prevent survivorship bias
  • Use np.add.at instead of fancy-indexed += so duplicate (cohort, day) collisions sum accurately
  • Filter negative offsets from clock skew and mask unobserved future days with NaN in production dashboards

💻 Reference Code Implementation

import numpy as np

def compute_cohort_arpu_matrix(signup_cohorts: np.ndarray, event_offsets: np.ndarray, revenues: np.ndarray, num_cohorts: int, max_window: int) -> np.ndarray:
    # signup_cohorts: (N_users,), event_offsets & revenues: (N_events,) paired with user_ids via event_user_idx
    raise NotImplementedError('Use explicit signature below')

def cohort_arpu_matrix(user_cohort: np.ndarray, event_user_idx: np.ndarray, event_offset: np.ndarray, event_rev: np.ndarray, num_cohorts: int, max_offset: int) -> np.ndarray:
    cohort_sizes = np.bincount(user_cohort, minlength=num_cohorts).astype(np.float64)
    valid = (event_offset >= 0) & (event_offset <= max_offset)
    ev_c = user_cohort[event_user_idx[valid]]
    ev_t = event_offset[valid]
    ev_r = event_rev[valid]
    rev_mat = np.zeros((num_cohorts, max_offset + 1), dtype=np.float64)
    np.add.at(rev_mat, (ev_c, ev_t), ev_r)
    denom = np.where(cohort_sizes > 0, cohort_sizes, 1.0)[:, None]
    arpu = np.where(cohort_sizes[:, None] > 0, rev_mat / denom, 0.0)
    return np.cumsum(arpu, axis=1)

🧪 Runnable Assertions & Validation

Copy and run directly in Python / Jupyter:

uc = np.array([0, 0, 1, 1])
eu = np.array([0, 1, 0, 2, 3])
eo = np.array([0, 1, 2, 0, 1])
er = np.array([10.0, 20.0, 10.0, 4.0, 6.0])
out = cohort_arpu_matrix(uc, eu, eo, er, 2, 2)
assert np.allclose(out[0], [5.0, 15.0, 20.0]), f'Unexpected cohort 0: {out[0]}'
assert np.allclose(out[1], [2.0, 5.0, 5.0]), f'Unexpected cohort 1: {out[1]}'
print('O1 PASS')

🎯 Core Follow-up Inquiries & Reference Answers

Q1:Why does rev_mat[ev_c, ev_t] += ev_r silently drop updates?
NumPy buffered fancy-indexing evaluates rhs once and writes indices sequentially without reading updated slots; np.add.at performs unbuffered atomic-like element accumulation.
Q2:How do you prevent right-censored cohorts from biasing day-30 ARPU?
Track each cohort's observation horizon max_age and mask cells where offset > max_age as NaN before averaging across cohorts.
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.
🎯 Drill This Card Now➔
← Prev: Ring-AllReduce (Reduce-Scatter + All-Gather)Back to Part O & All 105Next: Simpson's Paradox Detector & Kitagawa Funnel Decomposition →