資料工程師的分析 FAQ

Situation  Marketing and finance reported different "new customer" counts for the same month
           (12.4k vs 10.9k), and both numbers were in board slides.
Task       Own one definition and make every dashboard use it.
Action     - Traced both: marketing counted sign-ups; finance counted first paid orders,
             and excluded refunds within 14 days.
           - Agreed a written definition with both teams: first paid order, net of refunds.
           - Built fct_first_order at one row per customer, incremental by order date,
             and moved both dashboards onto it.
           - Added a daily reconciliation check against the billing system (alert at > 0.5%).
Result     One number in every report; the monthly reconciliation meeting was cancelled;
           the check caught a duplicated payment-webhook load two months later.

-- DAU, written so the definition is visible in the query
SELECT
  DATE(event_ts)              AS activity_date,      -- event_ts stored in UTC
  COUNT(DISTINCT user_id)     AS dau
FROM   events
WHERE  is_foreground = TRUE                                     -- foreground app events only
  AND  user_id IS NOT NULL                                       -- logged-in users only
  AND  NOT EXISTS (SELECT 1 FROM internal_users i                -- staff and test accounts
                   WHERE i.user_id = events.user_id)             -- (NOT IN breaks on a NULL id)
  AND  NOT EXISTS (SELECT 1 FROM known_bots b                    -- known bots
                   WHERE b.user_id = events.user_id)
GROUP  BY DATE(event_ts);

                orders   visits   conversion
Mobile             900   30,000       3.0%
Desktop            100      500      20.0%

Average of ratios:  (3.0% + 20.0%) / 2      = 11.5%   ← wrong
Ratio of sums:      (900 + 100) / 30,500    =  3.3%   ← the site's conversion rate

-- Store the numerator and denominator; divide only at the end
SELECT
  SUM(orders) * 1.0 / NULLIF(SUM(visits), 0) AS conversion_rate
FROM   daily_platform_stats
WHERE  stat_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

raw events ──► cleaned events ──► facts at a grain ──► daily aggregates ──► metrics layer ──► dashboards
 (bronze)        (silver)          fct_orders           agg_daily_country    one definition     BI, notebooks
                                   one row per order    one row per          per metric
                                                        day × country

-- Wrong: sums daily distinct counts — a user active on 5 days counts 5 times
SELECT SUM(dau) AS wau_wrong
FROM   agg_daily_active
WHERE  activity_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

-- Exact: recompute from the detail table
SELECT COUNT(DISTINCT user_id) AS wau
FROM   fct_user_activity_daily
WHERE  activity_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

-- Approximate and cheap (BigQuery): store a HyperLogLog++ sketch per day, merge any range
CREATE TABLE agg_daily_active_sketch AS
SELECT activity_date, HLL_COUNT.INIT(user_id) AS users_sketch
FROM   fct_user_activity_daily
GROUP  BY activity_date;

SELECT HLL_COUNT.MERGE(users_sketch) AS wau_approx
FROM   agg_daily_active_sketch
WHERE  activity_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

assignment ──► exposure log ──► join to metric facts ──► per-user outcomes ──► analysis
 hash(user,     user saw the      by user_id, from        one row per user      lift, CI,
 experiment)    variant at ts     exposure time onward    per variant           SRM check

import hashlib

def assign_variant(user_id: str, experiment: str, treatment_share: float = 0.5) -> str:
    """Same user + same experiment always gives the same variant."""
    key = f"{experiment}:{user_id}".encode("utf-8")
    bucket = int(hashlib.sha256(key).hexdigest()[:8], 16) % 10_000   # 0..9999
    return "treatment" if bucket < treatment_share * 10_000 else "control"

print(assign_variant("user_42", "checkout_v2"))   # stable on every call

def srm_chi_square(control: int, treatment: int, expected_treatment_share: float = 0.5) -> float:
    """Chi-square statistic for observed vs expected group sizes (1 degree of freedom)."""
    total = control + treatment
    expected_treatment = total * expected_treatment_share
    expected_control = total - expected_treatment
    return ((control - expected_control) ** 2 / expected_control
            + (treatment - expected_treatment) ** 2 / expected_treatment)

stat = srm_chi_square(control=50_800, treatment=49_200)
print(round(stat, 1))   # 25.6
# Critical values at 1 df: 3.84 (p = 0.05), 10.83 (p = 0.001).
# Teams flag SRM at a strict threshold such as p < 0.001 — 25.6 is far past it.

from math import erf, sqrt

def two_proportion_z_test(conv_a: int, n_a: int, conv_b: int, n_b: int) -> tuple:
    """Return (absolute lift, z, two-sided p-value) for conversion B vs A."""
    p_a, p_b = conv_a / n_a, conv_b / n_b
    pooled = (conv_a + conv_b) / (n_a + n_b)
    standard_error = sqrt(pooled * (1 - pooled) * (1 / n_a + 1 / n_b))
    z = (p_b - p_a) / standard_error
    p_value = 2 * (1 - 0.5 * (1 + erf(abs(z) / sqrt(2))))
    return p_b - p_a, z, p_value

lift, z, p = two_proportion_z_test(conv_a=1_000, n_a=20_000, conv_b=1_100, n_b=20_000)
print(f"lift={lift:.4f} z={z:.2f} p={p:.3f}")   # lift=0.0050 z=2.24 p=0.025

-- Conversion by platform, this week vs last week: did a rate move, or the mix?
WITH by_platform AS (
  SELECT
    platform,
    SUM(CASE WHEN week = 'this' THEN visits ELSE 0 END) AS visits_this,  -- 0, not NULL, when a
    SUM(CASE WHEN week = 'last' THEN visits ELSE 0 END) AS visits_last,  -- platform is new or gone
    SUM(CASE WHEN week = 'this' THEN orders ELSE 0 END) AS orders_this,
    SUM(CASE WHEN week = 'last' THEN orders ELSE 0 END) AS orders_last
  FROM weekly_platform_stats
  GROUP BY platform
)
SELECT
  platform,
  orders_last * 1.0 / NULLIF(visits_last, 0)                       AS rate_last,
  orders_this * 1.0 / NULLIF(visits_this, 0)                       AS rate_this,
  visits_last * 1.0 / NULLIF(SUM(visits_last) OVER (), 0)          AS mix_last,
  visits_this * 1.0 / NULLIF(SUM(visits_this) OVER (), 0)          AS mix_this
FROM by_platform
ORDER BY platform;