"""SOURCE render logic for TI-Cal-02 (REFERENCE ONLY). This is the exact function from our pipeline. It is DB-coupled — you cannot run it without our database. The query result it produces is materialized for you in data.csv, and the rendered output is figure.json (load that to display the chart). This file is provided so you can understand / re-theme the logic. """ def render_TI_Cal_02(reg: dict) -> dict: """Holiday Demand Lift Cards - DI in holiday weeks vs adjacent weeks.""" chart_id = "TI-Cal-02" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() with conn.cursor() as cur: cur.execute("SET statement_timeout = '240s'") # daily aggregate over 4 years t0 = time.time() # L-046 rework (audit #18): the old version compared holiday days against the ANNUAL mean of raw di - a # cross-time comparison contaminated by panel-size swings, and the measured "lifts" (-6%..+2%) were inside # panel noise while the copy claimed "predictable demand surges". Now: per actual calendar DATE (per year, # incl. the real 4th-Thursday Thanksgiving), holiday ±1d vs the SAME MONTH's non-holiday days (panel # coverage roughly cancels within a month), averaged across 2022-2025 on raw reach; if the effects stay # sub-noise the headline says so honestly. df = lib.run_query(conn, """ SELECT file_date, AVG(reach::numeric) AS mean_d FROM title_metrics_daily WHERE reach IS NOT NULL AND geo_code='US' AND file_date >= DATE '2022-01-01' AND file_date < DATE '2026-01-01' GROUP BY file_date ORDER BY file_date; """) q_sec = time.time() - t0 conn.close() if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No holiday data") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["file_date"] = pd.to_datetime(df["file_date"]) df["mean_d"] = df["mean_d"].astype(float) df["year"] = df["file_date"].dt.year; df["month"] = df["file_date"].dt.month def thanksgiving(year): # actual 4th Thursday of November first = pd.Timestamp(year=year, month=11, day=1) offset = (3 - first.dayofweek) % 7 # Thursday = 3 return first + pd.Timedelta(days=offset + 21) years = sorted(df["year"].unique()) holidays = {"Christmas": lambda y: pd.Timestamp(y, 12, 25), "New Year": lambda y: pd.Timestamp(y, 1, 1), "July 4th": lambda y: pd.Timestamp(y, 7, 4), "Thanksgiving": thanksgiving, "Valentine's": lambda y: pd.Timestamp(y, 2, 14), "Halloween": lambda y: pd.Timestamp(y, 10, 31)} rows = [] for name, fn in holidays.items(): lifts = [] for y in years: hd = fn(y) window = pd.date_range(hd - pd.Timedelta(days=1), hd + pd.Timedelta(days=1)) h_days = df[df["file_date"].isin(window)] base = df[(df["year"] == y) & (df["month"] == hd.month) & (~df["file_date"].isin(window))] if len(h_days) >= 2 and len(base) >= 10: lifts.append(h_days["mean_d"].mean() / base["mean_d"].mean() - 1) if lifts: rows.append({"holiday": name, "lift": float(np.mean(lifts)), "n_years": len(lifts)}) if not rows: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No holiday windows hit") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} out = pd.DataFrame(rows).sort_values("lift", ascending=True) # EMPIRICAL noise band (replaces the asserted ±10% constant): pseudo-"holidays" - random non-holiday # 3-day windows, same within-month-baseline formula, 4-year averages - give the ordinary day-to-day # range. Band = the 5th-95th percentile of those pseudo-lifts. holiday_days = set() for name, fn in holidays.items(): for y in years: hd = fn(y) holiday_days.update(pd.date_range(hd - pd.Timedelta(days=1), hd + pd.Timedelta(days=1))) rng_seed = np.random.default_rng(42) pseudo = [] by_my = {k: g for k, g in df.groupby(["year", "month"])} for _ in range(200): lifts = [] m = int(rng_seed.integers(1, 13)) for y in years: g = by_my.get((y, m)) if g is None or len(g) < 14: continue ok = g[~g["file_date"].isin(holiday_days)] if len(ok) < 5: continue center = ok["file_date"].iloc[int(rng_seed.integers(1, max(2, len(ok) - 1)))] window = pd.date_range(center - pd.Timedelta(days=1), center + pd.Timedelta(days=1)) h_days = g[g["file_date"].isin(window)] base = g[~g["file_date"].isin(window)] if len(h_days) >= 2 and len(base) >= 10: lifts.append(h_days["mean_d"].mean() / base["mean_d"].mean() - 1) if lifts: pseudo.append(float(np.mean(lifts))) if pseudo: p5, p95 = np.percentile(pseudo, [5, 95]) band = float(max(abs(p5), abs(p95))) else: band = 0.05 HOL_MONTH = {"Christmas": "December", "New Year": "January", "July 4th": "July", "Thanksgiving": "November", "Valentine's": "February", "Halloween": "October"} def cal02_color(v): if v < -band: return lib.RED if v > band: return lib.GREEN return lib.MUTED colors = [cal02_color(v) for v in out["lift"]] fig = go.Figure() fig.add_vrect(x0=-band, x1=band, fillcolor="#9aa1ad", opacity=0.10, line_width=0, layer="below") fig.add_trace(go.Bar(x=out["lift"], y=out["holiday"], orientation="h", marker=dict(color=colors), text=[f" {v:+.1%}" for v in out["lift"]], textposition="outside", textfont=dict(size=11, color=lib.INK), cliponaxis=False, showlegend=False, customdata=out[["n_years"]], hovertemplate="%{y}
%{x:+.1%} vs the rest of its month (avg of %{customdata[0]} years)")) fig.add_vline(x=0, line=dict(color=lib.INK, width=1)) fig.add_annotation(x=band, y=1.0, yref="paper", text="ordinary day-to-day range", showarrow=False, xanchor="left", yanchor="top", xshift=4, font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.8)") rng = max(band * 1.3, max(abs(out["lift"].min()), abs(out["lift"].max())) * 1.3, 0.08) fig.update_xaxes(range=[-rng, rng]) worst = out.iloc[0] best = out.iloc[-1] if float(worst["lift"]) < -band and float(best["lift"]) <= band: title_lesson = (f"Holidays don't boost at-home streaming - they dent it: {worst['holiday']} viewing runs " f"~{abs(worst['lift']):.0%} below a typical {HOL_MONTH.get(worst['holiday'], 'same-month')} day") why = ("The intuition that holidays bring big captive-audience surges has it backwards: people stream less on the big " "holidays - out, hosting, traveling - and shift WHAT they watch far more than HOW MUCH (see the genre-seasonality " "chart). Plan holiday slates around genre fit, not an expected audience spike.") strength = "moderate" elif float(best["lift"]) > band: title_lesson = f"{best['holiday']} brings the biggest holiday viewing shift - {best['lift']:+.1%} vs the rest of its month" why = ("Holiday effects on total at-home viewing are real but modest - the bigger seasonal action is which genres holidays favor " "(see the genre-seasonality chart). Use this to set expectations for premiere timing, not to bank on a surge.") strength = "moderate" else: title_lesson = ("Holidays barely move total at-home demand - every measured holiday effect sits inside the " "ordinary day-to-day range") why = ("The intuition that holidays bring big captive-audience surges does not show up in total at-home viewing - " "people shift WHAT they watch (see the genre-seasonality chart) far more than HOW MUCH. Plan holiday slates around " "genre fit, not an expected audience spike.") strength = "moderate" what = ("Each bar = average panel viewing on the holiday ±1 day vs the other days of that same month, averaged across " "2022-2025 (Thanksgiving uses each year's actual 4th Thursday). Gray = within the ordinary day-to-day range " "(shaded band, measured from non-holiday days); red = a real dip, green = a real lift.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1100, height=600), xaxis=lib.standard_xaxis("", tickformat="+.0%"), yaxis=lib.standard_yaxis(showgrid=False)) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-Cal-02", ["CL-006 viewer panel (distinct viewers), not census", "holiday vs same-month baseline (panel-safe); raw reach basis"], "2025-12-31", ["title_metrics_daily"], f"±1 day around each holiday vs that month's other days; per-year holiday dates; 2022-2025; " f"noise band ±{band:.1%} = 5th-95th pct of 200 bootstrap non-holiday windows")) lib.save_csv(out, output_dir); lib.export_chart(fig, output_dir, width=1100, height=600) lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{title_lesson}\n\n{what}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-Cal-02", intended_lesson_from_spec="Holiday demand lift (panel-safe, within-month baseline)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": strength, "rules_applied": f"holiday±1d vs same-month non-holiday days, per-year dates (true 4th-Thursday Thanksgiving), 2022-2025; empirical noise band ±{band:.3f} (200 bootstrap windows)"}, row_count=len(out), freshness_date="2025-12-31", query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(out)}