E-Commerce Analytics Lab

SQL — the return-rate discipline

-- Grain: one row per calendar day. Question 1 (business performance
-- baseline). Depends on fct_orders.

CREATE OR REPLACE TABLE `...marts_20260921.mart_daily_performance` AS
SELECT
  DATE(created_at) AS order_date,
  COUNT(DISTINCT order_id) AS orders,
  COUNT(DISTINCT user_id) AS customers,
  SUM(revenue) AS revenue,
  SUM(profit) AS gross_profit,
  SAFE_DIVIDE(SUM(revenue), COUNT(DISTINCT order_id)) AS aov,
  SAFE_DIVIDE(SUM(profit), SUM(revenue)) AS margin_pct,
  SAFE_DIVIDE(COUNTIF(has_return), COUNT(*)) AS order_return_rate
FROM `...marts_20260921.fct_orders`
WHERE NOT is_cancelled
GROUP BY order_date
ORDER BY order_date;

SQL — the funnel's terminal-outcome logic

-- Grain: one row per session. funnel_outcome takes the furthest real
-- stage reached, with purchase/cancel as the two mutually exclusive
-- terminal outcomes once a session reaches cart -- confirmed from real
-- event sequences, not assumed.

SELECT
  session_id,
  ANY_VALUE(user_id) IS NULL AS is_anonymous,
  LOGICAL_OR(event_type = 'cart') AS reached_cart,
  CASE
    WHEN LOGICAL_OR(event_type = 'purchase') THEN 'purchase'
    WHEN LOGICAL_OR(event_type = 'cancel') THEN 'cancel'
    WHEN LOGICAL_OR(event_type = 'cart') THEN 'abandoned_at_cart'
    WHEN LOGICAL_OR(event_type = 'product') THEN 'abandoned_at_product'
    ELSE 'abandoned_at_home'
  END AS funnel_outcome
FROM `...raw_snapshot_20260921.events`
GROUP BY session_id;

Python — testing the identification hypothesis

# Is identification the real bottleneck, or is checkout friction?
by_anon = query_df("""
    SELECT is_anonymous,
           COUNTIF(reached_cart) AS reached_cart,
           COUNTIF(reached_purchase) AS reached_purchase,
           SAFE_DIVIDE(COUNTIF(reached_purchase),
                       COUNTIF(reached_cart)) AS cart_to_purchase_rate
    FROM `marts_20260921.mart_funnel`
    WHERE reached_cart
    GROUP BY is_anonymous
""")
# identified: 99.31%   anonymous: 0.00% -- zero exceptions, 680,987 sessions

Python — statistically real vs. practically large

# Statistically significant is not the same as practically large.
t, p = stats.ttest_ind(male_rev, female_rev, equal_var=False)
d = (male_rev.mean() - female_rev.mean()) / np.sqrt(
    (male_rev.std() ** 2 + female_rev.std() ** 2) / 2)

rng = np.random.default_rng(42)
diffs = [rng.choice(male_rev.values, len(male_rev), replace=True).mean()
         - rng.choice(female_rev.values, len(female_rev), replace=True).mean()
         for _ in range(2000)]
# p=1.05e-64 (real), Cohen's d=0.127 (small) -- both true at once