Tolan/dashboard/streamlit_growth_dashboard_demo.py

"""
Growth Dashboard - Demo 版：使用本地 SQLite demo_growth.db，便于与 MySQL 版对照。
启动: streamlit run streamlit_growth_dashboard_demo.py
"""
import os
from datetime import date, timedelta

import pandas as pd
import streamlit as st

try:
    from sqlalchemy import create_engine
except ImportError:
    st.set_page_config(page_title="Growth Dashboard (Demo)", layout="wide")
    st.error("请安装: pip install sqlalchemy")
    st.stop()

st.set_page_config(page_title="Growth Dashboard (Demo)", layout="wide")

# ---- SQLite demo_growth.db ----
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
DEFAULT_DB = os.path.join(SCRIPT_DIR, "demo_growth.db")
DB_PATH = os.getenv("DEMO_DB_PATH", DEFAULT_DB)

if not os.path.exists(DB_PATH):
    st.error(f"Demo 数据库不存在: {DB_PATH}")
    st.stop()

# SQLite 使用 ? 占位符；SQLAlchemy 连接避免 pandas 警告
@st.cache_resource
def _get_engine():
    return create_engine(f"sqlite:///{DB_PATH}", connect_args={"check_same_thread": False})


_engine = _get_engine()


def read_df(sql: str, params: tuple = ()) -> pd.DataFrame:
    return pd.read_sql_query(sql, _engine, params=params)


st.title("Lyrria Growth Dashboard (Demo)")
st.sidebar.caption("数据源: SQLite · demo_growth.db")

# ---- Sidebar ----
st.sidebar.header("Filters")

segments = read_df("SELECT DISTINCT user_segment_snapshot AS seg FROM user_daily_activity ORDER BY seg")["seg"].dropna().tolist()
orgs = read_df("SELECT DISTINCT org_id_snapshot AS org FROM user_daily_activity WHERE org_id_snapshot IS NOT NULL ORDER BY org")["org"].tolist()

segment_default = [s for s in segments if s != "internal"] if segments else []
segment_sel = st.sidebar.multiselect("user_segment_snapshot", options=segments, default=segment_default)
org_sel = st.sidebar.multiselect("org_id_snapshot (school)", options=orgs, default=[])

exclude_test = st.sidebar.checkbox("Exclude users.is_test_user = 1", value=True)

# Date bounds (from daily table)
max_day = pd.to_datetime(read_df("SELECT MAX(activity_date) AS d FROM user_daily_activity")["d"].iloc[0]).date()
min_day = pd.to_datetime(read_df("SELECT MIN(activity_date) AS d FROM user_daily_activity")["d"].iloc[0]).date()

target_day = st.sidebar.date_input("Target date", value=max_day, min_value=min_day, max_value=max_day)
lookback_days = st.sidebar.selectbox("Trend window (days)", [14, 30, 60, 90], index=1)

# ---- Load users (for universe + test filter) ----
users = read_df("SELECT user_id, is_test_user FROM users")
if exclude_test:
    users = users[users["is_test_user"].fillna(0).astype(int) == 0].copy()
universe_users = set(users["user_id"].astype(str).tolist())

# ---- Pull daily activity for trend window + recent history (for 7/30d sets) ----
start_recent = target_day - timedelta(days=max(lookback_days, 35))

daily = read_df(
    """
    SELECT activity_date, user_id, active_flag, session_count, message_count,
           first_seen_date, user_segment_snapshot, org_id_snapshot
    FROM user_daily_activity
    WHERE activity_date >= ? AND activity_date <= ?
    """,
    (start_recent.isoformat(), target_day.isoformat()),
)

daily["activity_date"] = pd.to_datetime(daily["activity_date"]).dt.date
daily["first_seen_date"] = pd.to_datetime(daily["first_seen_date"]).dt.date

if segment_sel:
    daily = daily[daily["user_segment_snapshot"].isin(segment_sel)]
if org_sel:
    daily = daily[daily["org_id_snapshot"].isin(org_sel)]
daily = daily[daily["user_id"].isin(universe_users)]

if segment_sel or org_sel:
    cohort_universe = set(daily["user_id"].unique().tolist())
else:
    cohort_universe = universe_users

# ---- Trend (DAU) ----
st.subheader("DAU trend")
trend = (
    daily[(daily["activity_date"] >= (target_day - timedelta(days=lookback_days))) & (daily["activity_date"] <= target_day)]
    .groupby("activity_date")["user_id"].nunique()
    .reset_index(name="dau")
)
trend["activity_date"] = pd.to_datetime(trend["activity_date"])
st.line_chart(trend.set_index("activity_date")["dau"])

# ---- Segment computation on target_day ----
d0 = target_day
d7 = d0 - timedelta(days=7)
d30 = d0 - timedelta(days=30)

active_today = set(daily.loc[(daily["activity_date"] == d0) & (daily["active_flag"] == 1), "user_id"])
active_past_7 = set(daily.loc[(daily["activity_date"] < d0) & (daily["activity_date"] >= d7) & (daily["active_flag"] == 1), "user_id"])
active_past_30 = set(daily.loc[(daily["activity_date"] < d0) & (daily["activity_date"] >= d30) & (daily["active_flag"] == 1), "user_id"])

params = [d0.isoformat()]
user_filter_sql = ""
if cohort_universe:
    placeholders = ",".join(["?"] * len(cohort_universe))
    user_filter_sql = f" AND user_id IN ({placeholders}) "
    params += list(cohort_universe)

last_active_df = read_df(
    f"""
    SELECT user_id, MAX(activity_date) AS last_active_date
    FROM user_daily_activity
    WHERE active_flag = 1
      AND activity_date < ?
      {user_filter_sql}
    GROUP BY user_id
    """,
    tuple(params),
)
if not last_active_df.empty:
    last_active_df["last_active_date"] = pd.to_datetime(last_active_df["last_active_date"]).dt.date
prev_last_active = dict(zip(last_active_df["user_id"], last_active_df["last_active_date"])) if not last_active_df.empty else {}


def prev_last_active_date(uid: str):
    return prev_last_active.get(uid, None)


first_seen_map = daily.groupby("user_id")["first_seen_date"].min().to_dict()
new_users = {u for u in active_today if first_seen_map.get(u) == d0}
current_users = {u for u in active_today if u in active_past_7}
reactivated_users = {u for u in active_today if (u in active_past_30) and (u not in active_past_7)}
resurrected_users = {
    u for u in active_today
    if (prev_last_active_date(u) is not None) and (prev_last_active_date(u) < d30)
}
at_risk_wau = {u for u in active_past_7 if u not in active_today}
at_risk_mau = {u for u in active_past_30 if (u not in active_past_7) and (u not in active_today)}
dormant_users = {
    u for u in cohort_universe
    if (u not in active_today) and ((prev_last_active_date(u) is None) or (prev_last_active_date(u) < d30))
}

# ---- Segments visualization ----
st.subheader(f"Segments on {d0.isoformat()}")
other_active = active_today - new_users - current_users - reactivated_users - resurrected_users
active_buckets = [
    ("New", len(new_users)),
    ("Current", len(current_users)),
    ("Reactivated", len(reactivated_users)),
    ("Resurrected", len(resurrected_users)),
    ("Other active", len(other_active)),
]
active_total = len(active_today)
inactive_total = max(0, len(cohort_universe) - len(active_today))
inactive_buckets = [
    ("At-risk (7d)", len(at_risk_wau)),
    ("At-risk (30d)", len(at_risk_mau)),
    ("Dormant (≥30d)", len(dormant_users)),
]


def segment_card(label: str, value: int, denom: int, hint: str):
    pct = (value / denom) if denom > 0 else 0.0
    st.markdown(
        f"**{label}**"
        f"<span style='font-size:30px; font-weight:800;'>{value:,}</span>",
        unsafe_allow_html=True,
    )
    st.progress(min(max(pct, 0.0), 1.0))
    st.caption(f"{pct:.1%} of {denom:,}. {hint}")


st.markdown("")
left, right = st.columns(2, gap="large")
with left:
    t1, t2 = st.columns([3, 1], vertical_alignment="center")
    with t1:
        st.markdown("### Active today composition")
    with t2:
        st.metric("DAU", len(active_today))
    st.caption("How today's DAU is composed (buckets sum to DAU, with 'Other active' as remainder).")
    for label, val in active_buckets:
        if val == 0:
            continue
        segment_card(label, val, active_total, "Share of DAU.")
with right:
    t1, t2 = st.columns([2, 2], vertical_alignment="center")
    with t1:
        st.markdown("### Not active today")
    with t2:
        st.metric("Inactive / Total", f"{inactive_total:,} / {len(cohort_universe):,}")
    st.caption("Breakdown of users who are not active today (within the current filtered cohort).")
    for label, val in inactive_buckets:
        if val == 0:
            continue
        segment_card(label, val, inactive_total, "Share of users not active today.")

st.markdown("---")
st.subheader("Drill-down (first 50 user_ids)")
tab1, tab2, tab3 = st.tabs(["Active today", "At-risk", "Dormant"])
with tab1:
    st.write(sorted(list(active_today))[:50])
with tab2:
    st.write({
        "at_risk_wau": sorted(list(at_risk_wau))[:50],
        "at_risk_mau": sorted(list(at_risk_mau))[:50],
    })
with tab3:
    st.write(sorted(list(dormant_users))[:50])