Product Decisions and Business Outcomes Questions

MAU =
CALCULATE(
  DISTINCTCOUNT('Events'[UserID]),
  ALLEXCEPT('Date','Date'[Year],'Date'[Month])
)

MAU_MoM_Pct =
VAR CurrentMonthMAU =
    CALCULATE(
        DISTINCTCOUNT('Events'[UserID]),
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -1, MONTH)
    )
VAR PriorMonthMAU =
    CALCULATE(
        DISTINCTCOUNT('Events'[UserID]),
        DATEADD('Date'[Date], -1, MONTH)
    )
RETURN
IF(
    PriorMonthMAU = 0,
    BLANK(),
    DIVIDE(CurrentMonthMAU - PriorMonthMAU, PriorMonthMAU, 0)
)

-- aggregated daily table: kpi_daily(date, kpi, segment, value)
WITH baseline AS (
  SELECT
    kpi, segment,
    AVG(value) FILTER (WHERE date BETWEEN CURRENT_DATE - INTERVAL '35 day' AND CURRENT_DATE - INTERVAL '8 day') AS mu,
    STDDEV_SAMP(value) FILTER (WHERE date BETWEEN CURRENT_DATE - INTERVAL '35 day' AND CURRENT_DATE - INTERVAL '8 day') AS sigma
  FROM kpi_daily
  GROUP BY kpi, segment
),
today AS (
  SELECT kpi, segment, value
  FROM kpi_daily
  WHERE date = CURRENT_DATE - INTERVAL '1 day'
)
SELECT
  t.kpi, t.segment, t.value, b.mu, b.sigma,
  CASE
    WHEN b.sigma IS NULL THEN 'insufficient data'
    WHEN (t.value - b.mu)/b.sigma > 3 THEN 'positive_anomaly'
    WHEN (t.value - b.mu)/b.sigma < -3 THEN 'negative_anomaly'
    ELSE 'normal'
  END AS status,
  (t.value - b.mu)/NULLIF(b.sigma,0) AS zscore,
  (t.value - b.mu)/NULLIF(b.mu,1) AS pct_change
FROM today t
JOIN baseline b USING (kpi, segment)
WHERE b.mu IS NOT NULL;

# runs nightly per KPI+segment
def detect_anomaly(series):  # series = historical values with newest last
    baseline = series[-35:-7]  # exclude last week to avoid contamination
    med = median(baseline)
    mad = median([abs(x-med) for x in baseline]) or 1
    latest = series[-1]
    score = (latest - med) / (1.4826 * mad)  # approx z-score
    pct = (latest - med) / med
    if score < -3 and pct < -0.05:
        return {'level':'HIGH','score':score,'pct':pct}
    if score < -2 and pct < -0.03:
        return {'level':'MEDIUM','score':score,'pct':pct}
    return None

-- WAU by cohort (control vs treatment) over last 4 weeks
SELECT week, cohort, COUNT(DISTINCT user_id) AS wau
FROM events
WHERE event_date >= current_date - interval '28 days'
GROUP BY week, cohort;

SELECT cohort, COUNT(*) FILTER (WHERE event='session_start') AS sessions,
COUNT(*) FILTER (WHERE event='key_action') AS key_actions
FROM events
WHERE event_date >= current_date - interval '14 days'
GROUP BY cohort;

SELECT device_os, os_version, cohort, COUNT(DISTINCT user_id) FROM events GROUP BY 1,2,3;