scripts/data_repair/fix_yodeck_zero_measures.py

"""One-off data repair: Yodeck Finance rebuild (YOD_08250414_EPU_sch), 2026-08-25.

Same generator defect as the Triumph run (see fix_triumph_zero_measures.py):
derived measure columns all zero while inputs are populated. Recomputes:

SUBSCRIPTION_REVENUE:  ARR = MRR*12; NET_NEW_MRR = expansion-churned;
                       GROSS_CHURN_RATE = churned/MRR; NRR = (MRR+exp-churn)/MRR
INVOICE_COLLECTIONS:   UNCOLLECTED = invoice-collected; OVERDUE = uncollected when DSO>45;
                       COLLECTION_RATE = collected/invoice; OVERDUE_RATE = overdue/invoice
COGS_AND_MARGIN:       GROSS_PROFIT = revenue-COGS; GROSS_MARGIN_PERCENT = GP/revenue

Input ranges pre-verified: churn 2-12% of MRR, expansion 4-52%, collections 72-99%,
COGS < revenue on every row -> all derived values land in realistic ranges.
"""
import sys
sys.path.insert(0, "/Users/mike.boone/_source/demoprep")
from snowflake_auth import get_snowflake_connection

conn = get_snowflake_connection()
cur = conn.cursor()
cur.execute('USE SCHEMA DEMOBUILD_2026_08."YOD_08250414_EPU_sch"')

cur.execute("""
UPDATE SUBSCRIPTION_REVENUE
SET ANNUAL_RECURRING_REVENUE   = ROUND(MONTHLY_RECURRING_REVENUE * 12, 2),
    NET_NEW_MRR                = ROUND(EXPANSION_MRR - CHURNED_MRR, 2),
    GROSS_CHURN_RATE           = ROUND(CHURNED_MRR / NULLIF(MONTHLY_RECURRING_REVENUE, 0), 4),
    NET_REVENUE_RETENTION_RATE = ROUND((MONTHLY_RECURRING_REVENUE + EXPANSION_MRR - CHURNED_MRR)
                                       / NULLIF(MONTHLY_RECURRING_REVENUE, 0), 4)
""")
print("SUBSCRIPTION_REVENUE updated:", cur.rowcount)

cur.execute("""
UPDATE INVOICE_COLLECTIONS
SET UNCOLLECTED_BALANCE = ROUND(INVOICE_AMOUNT - AMOUNT_COLLECTED, 2),
    AMOUNT_OVERDUE      = CASE WHEN DAYS_SALES_OUTSTANDING > 45
                               THEN ROUND(INVOICE_AMOUNT - AMOUNT_COLLECTED, 2) ELSE 0 END,
    COLLECTION_RATE     = ROUND(AMOUNT_COLLECTED / NULLIF(INVOICE_AMOUNT, 0), 4),
    OVERDUE_RATE        = CASE WHEN DAYS_SALES_OUTSTANDING > 45
                               THEN ROUND((INVOICE_AMOUNT - AMOUNT_COLLECTED) / NULLIF(INVOICE_AMOUNT, 0), 4)
                               ELSE 0 END
""")
print("INVOICE_COLLECTIONS updated:", cur.rowcount)

cur.execute("""
UPDATE COGS_AND_MARGIN
SET GROSS_PROFIT         = ROUND(REVENUE_ALLOCATED - COST_OF_GOODS_SOLD, 2),
    GROSS_MARGIN_PERCENT = ROUND((REVENUE_ALLOCATED - COST_OF_GOODS_SOLD) / NULLIF(REVENUE_ALLOCATED, 0), 4)
""")
print("COGS_AND_MARGIN updated:", cur.rowcount)
conn.commit()

for tbl, cols in [
    ("SUBSCRIPTION_REVENUE", ["ANNUAL_RECURRING_REVENUE", "NET_NEW_MRR", "GROSS_CHURN_RATE", "NET_REVENUE_RETENTION_RATE"]),
    ("INVOICE_COLLECTIONS", ["AMOUNT_OVERDUE", "COLLECTION_RATE", "OVERDUE_RATE", "UNCOLLECTED_BALANCE"]),
    ("COGS_AND_MARGIN", ["GROSS_PROFIT", "GROSS_MARGIN_PERCENT"]),
]:
    sel = ", ".join(f"MIN({c}), MAX({c}), ROUND(AVG({c}),4)" for c in cols)
    cur.execute(f"SELECT {sel} FROM {tbl}")
    row = cur.fetchone()
    print(f"== {tbl}")
    for i, c in enumerate(cols):
        print(f"   {c:30s} min={row[i*3]} max={row[i*3+1]} avg={row[i*3+2]}")