Transforming raw data into clear dashboardsdecisions

# Pivot the cohort query into a retention matrix
import os
from pathlib import Path
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(os.environ["WAREHOUSE_URL"])
sql = Path("cohort_retention.sql").read_text()
df = pd.read_sql(sql, engine, parse_dates=["cohort_month", "month"])

df["month_n"] = (df["month"].dt.year - df["cohort_month"].dt.year) * 12 \
    + df["month"].dt.month - df["cohort_month"].dt.month

matrix = df.pivot_table(index="cohort_month", columns="month_n",
                        values="retained_pct", aggfunc="first")

at_risk = matrix[matrix[3] < 55.0].index
matrix.round(1).to_csv("out/retention_matrix.csv")
print(f"{len(at_risk)} cohorts flagged for retention review")