"""SOURCE render logic for TI-BC-01 (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_BC_01(reg: dict) -> dict:
"""Binge-Completion (M051) - season start->finish retention: of the households that watched episode 1,
the share that reached the finale. Drawn as a drop from 100% (started) to the completion %; the gap is the
audience lost. Biggest recent FINISHED seasons only (airing-guarded via snapshot stability)."""
chart_id = "TI-BC-01"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# Airing guard: a currently-airing season's completion RISES across snapshots (more reach the finale as it
# ages); a finished season is STABLE. Keep seasons present in a prior snapshot (>=21d back) with |delta|<=8.
GUARD = """
WITH d AS (
SELECT MAX(file_date) AS latest,
COALESCE((SELECT MAX(file_date) FROM metrics_binge_completion
WHERE file_date <= (SELECT MAX(file_date) FROM metrics_binge_completion) - INTERVAL '21 days'),
(SELECT MIN(file_date) FROM metrics_binge_completion)) AS prior
FROM metrics_binge_completion
),
latest_r AS (SELECT m.* FROM metrics_binge_completion m JOIN d ON m.file_date = d.latest),
prior_r AS (SELECT imdb_id, season, completion_pct AS cp_prior FROM metrics_binge_completion m JOIN d ON m.file_date = d.prior),
stable AS (
SELECT l.imdb_id, l.season, l.season_eps, l.e1_households, l.finale_households,
l.completion_pct, l.completion_pctl_platform, l.file_date
FROM latest_r l JOIN prior_r p USING (imdb_id, season)
WHERE ABS(l.completion_pct - p.cp_prior) <= 8
)
"""
df = lib.run_query(conn, GUARD + " SELECT * FROM stable WHERE e1_households >= 1500 ORDER BY e1_households DESC LIMIT 16;")
med = lib.run_query(conn, GUARD + " SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY completion_pct) AS m FROM stable WHERE e1_households >= 500;")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No stable (finished) binge-completion seasons with e1>=1500")
conn.close(); write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
n_queried = len(df)
df = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans
n_orphan_dropped = n_queried - len(df) # disclosed in the footer, never a silent cut
titles = lib.resolve_titles(conn, df["imdb_id"].unique().tolist()).drop_duplicates("imdb_id")
conn.close()
df = df.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left")
df["display_title"] = df["display_title"].fillna(df["imdb_id"])
# BRAND SAFETY (verifier escalation 2026-06-12): adult-only-source titles (the objective
# ANIME_SOURCE_BLOCKLIST criterion) are EXCLUDED from this general surface too, same as the
# Anime Pulse surfaces, with the rule disclosed in the footer - the earlier warn-and-render
# approach put a blocklisted title's name on the chart face and blocked the publish gate.
# Ecchi tiers are NOT applied here (they bind Anime Pulse callout copy only); every rendered
# row is checked against the blocklist, so the scan covers the whole cohort by construction.
excluded_adult_src = sorted(df.loc[df["imdb_id"].astype(str).isin(ANIME_SOURCE_BLOCKLIST), "display_title"].astype(str))
df = df[~df["imdb_id"].astype(str).isin(ANIME_SOURCE_BLOCKLIST)].copy()
for c in ["completion_pct", "e1_households", "finale_households", "season"]:
df[c] = pd.to_numeric(df[c], errors="coerce")
median_compl = float(med["m"].iloc[0]) if (not med.empty and pd.notna(med["m"].iloc[0])) else 62.0
df["label"] = df.apply(lambda r: f"{short_title(str(r['display_title']), 46)} · S{int(r['season'])}", axis=1)
df = df.sort_values("completion_pct", ascending=True).reset_index(drop=True)
# completion tiers are CLASSES, so the dots take no tier paint (the old green/navy/red buckets
# broke the color contract): navy = the best keeper (the headline subject) ONLY, field blue rest
dot_colors = lib.leader_colors(df["completion_pct"])
fig = go.Figure()
# the audience lost: a span from completion% out to 100% (where everyone started) - red reads as
# 'audience lost' (the contract's worse/falling lane), strong enough to survive downscaling
cx, cy = [], []
for lbl, c in zip(df["label"], df["completion_pct"]):
cx += [c, 100.0, None]; cy += [lbl, lbl, None]
fig.add_trace(go.Scatter(x=cx, y=cy, mode="lines", line=dict(color="rgba(176,70,78,0.45)", width=3),
showlegend=False, hoverinfo="skip"))
fig.add_trace(go.Scatter(
x=df["completion_pct"], y=df["label"], mode="markers+text",
marker=dict(size=13, color=dot_colors, line=dict(width=1, color="white")),
# uniform whole-number labels (no tier buckets left to justify a lone decimal)
text=[f"{float(c):.0f}% " for c in df["completion_pct"]], textposition="middle left",
textfont=dict(size=10, color=lib.INK), cliponaxis=False, showlegend=False,
customdata=df[["e1_households", "finale_households"]].values,
hovertemplate="%{y}
%{x:.0f}% finished the season
%{customdata[0]:,} started episode 1 · %{customdata[1]:,} reached the finale"))
fig.add_vline(x=100, line=dict(color=lib.MUTED, width=1))
fig.add_annotation(x=100, y=len(df) - 0.3, text="started (100%)", showarrow=False, xanchor="right", yanchor="bottom",
font=dict(size=11, color=lib.INK), xshift=-2)
fig.add_vline(x=median_compl, line=dict(color=lib.INK, width=1.1, dash="dash"))
# label sits to the RIGHT of the dashed line (left-anchored + shifted): centered text was
# struck through by the line it describes. TWO lines, so the text always terminates well
# short of the 100% 'started' reference line at the right edge (the one-line version collided).
fig.add_annotation(x=median_compl, y=-0.9,
text=f"typical finished season keeps {median_compl:.0f}%
(median of all tracked finished seasons)",
showarrow=False, xanchor="left", xshift=7, yanchor="top", align="left",
font=dict(size=11, color=lib.INK))
fig.update_xaxes(range=[0, 112], ticksuffix="%")
top = df.iloc[-1]; worst = df.iloc[0]
takeaway = (f"{str(top['display_title'])[:30]} held its audience best: {top['completion_pct']:.0f}% of the viewers who started it "
f"finished the season")
# 'biggest tracked seasons', not 'recent launches': the e1-audience cut can surface multi-year-old
# seasons (e.g. a 2022 prestige hit), so 'recent' would strain against the rows on the chart
what = ("Binge-completion = of the viewers who watched episode 1 of a season, the share who reached the finale. Each row "
"drops from 100% (everyone who started) to the % who finished - the red gap is the audience it shed (the biggest finished seasons we track, by episode-1 audience).")
why = (f"Audience demand says who pressed play; completion says who stayed - the closest behavioral read on whether a season actually delivered. The typical "
f"finished season we track keeps just {median_compl:.0f}% to the end, so the gap between a show people couldn't stop and one they quietly abandoned is huge "
f"(here, {top['completion_pct']:.0f}% vs {worst['completion_pct']:.0f}%). High completers are the safest bets to renew, feature and recommend.")
orphan_note = (f" · {n_orphan_dropped} qualifying season{'s' if n_orphan_dropped != 1 else ''} hidden: title not yet on "
"subscription/free streaming (demand-orphan rule)" if n_orphan_dropped else "")
footer = lib.assemble_footer(pc_id="PC-BC-01",
caution_labels=["CL-006 viewer panel (distinct viewers), not census", "M051 binge-completion (methodology v1)"],
freshness_date=str(df["file_date"].iloc[0]),
sources=["metrics_binge_completion"],
extra=f"finished seasons only (completion stable across snapshots); 1,500+ episode-1 viewers; "
f"excludes adaptations of adult-only source media{orphan_note}")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1200, height=780),
xaxis=lib.standard_xaxis("Share of episode-1 viewers who reached the finale"),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_why_and_footer(fig, why, footer)
# the footer-disclosure claim is CONDITIONAL: with zero orphans hidden the footer carries no
# orphan clause, and the manifest must not assert a disclosure the rendered surface lacks
orphan_rule = (f"{n_orphan_dropped} hidden, disclosed in the footer" if n_orphan_dropped
else "0 hidden this run, so no footer clause")
lesson = {"reader_title": takeaway, "reader_subtitle": what, "claim_strength": "strong",
"rules_applied": (f"stable/finished seasons, e1>=1500; {n_queried} queried, {len(df)} rendered after the "
f"demand-orphan drop ({orphan_rule}) and the adult-only-source exclusion "
f"({len(excluded_adult_src)} excluded, rule disclosed in the footer); "
"drop 100%->completion, sorted by completion")}
# the dashed benchmark rides into the sidecar (it is a median over ALL tracked finished seasons,
# not just the 13-16 rows drawn, so it is otherwise unreproducible from data.csv).
# csv headers say VIEWERS, matching every rendered surface ('households' is banned anywhere,
# csv headers included, and the hover prints these exact numbers as viewers).
lib.save_csv(df.rename(columns={"e1_households": "episode1_viewers", "finale_households": "finale_viewers"})
.assign(median_all_finished_seasons=median_compl), output_dir)
lib.export_chart(fig, output_dir, width=1200, height=780)
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-BC-01",
intended_lesson_from_spec="Binge-completion (M051): season start->finish retention",
rendered_lesson=lesson, row_count=len(df), freshness_date=str(df["file_date"].iloc[0]),
extra={"queried_seasons": int(n_queried), "demand_orphans_hidden": int(n_orphan_dropped),
"adult_only_source_excluded": excluded_adult_src,
"brand_safety_scan": ("all queried rows checked against the adult-only-source "
"blocklist; ecchi/no-spotlight tiers bind Anime Pulse "
"callout copy only, not this general surface")},
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}