Risk Segmentation — WO Forecast & Profitability Dashboard

Loan-level write-off (WO) forecast pipeline from BigQuery to an interactive HTML dashboard. Forecasts FPG → TPG → DPD30 → Loss via a regression chain with gap-method prediction intervals, then layers cohort profitability (real revenue/cost vs. forecasted credit loss) and "what-if" simulations on top — all with multi-segment, on-the-fly filtering.

Quick Start

The dashboard has in-page "Refresh Data" / "Rebuild Raw Table" buttons, which need a real backend to call — so run it via the Flask server, not a bare static file server:

# 1. Install dependencies
pip install -r requirements.txt

# 2. One-time: authenticate gcloud with BOTH BigQuery and Drive scopes
#    (Drive scope is needed for export_costs.py's Google Sheets read)
gcloud auth login --enable-gdrive-access

# 3. Export data from BigQuery + the Unit Economics sheet
python export_segmented.py
python export_cohort.py
python export_costs.py

# 4. Launch the server
python server.py
# Open http://127.0.0.1:8080

Once running, the dashboard's own "Refresh Data" button re-runs all three export scripts for you (step 3), and "Rebuild Raw Table" rebuilds the underlying wo_forecast_raw_v2 BigQuery table from source — see Pipeline Overview below for how those fit together.

python build_standalone.py (single-file, no server) exists but is currently stale — see Known Limitations.

Pipeline Overview

Three independent data sources feed the dashboard; only one of them depends on the BigQuery rebuild step:

                     ┌───────────────────────────────────────┐
                     │ create_wo_forecast_raw_v2.sql          │
                     │  reads:  julo-datamart.julosilver.*    │
                     │  writes: julo-analytics:user_fraharjo. │
                     │          wo_forecast_raw_v2 (BQ table) │
                     └───────────────────┬─────────────────────┘
                       triggered by "Rebuild Raw Table" button
                       (server.py POST /rebuild -> `bq query`)
                                         │
                                         ▼
     ┌─────────────────────────────────────────────────────────────┐
     │ export_segmented.py  (runs export_agg_segmented.sql)        │
     │   reads:  wo_forecast_raw_v2        <- depends on rebuild   │
     │   writes: data_segmented.json                               │
     ├─────────────────────────────────────────────────────────────┤
     │ export_cohort.py  (runs export_cohort.sql)                   │
     │   reads:  julo-datamart.julosilver.olap_loan(_daily)         │
     │   writes: data_cohort.json           <- independent of rebuild │
     ├─────────────────────────────────────────────────────────────┤
     │ export_costs.py                                              │
     │   reads:  "Unit Economics" Google Sheet (Sheets API)         │
     │   writes: data_costs.json             <- independent of BQ    │
     └─────────────────────────────────────────────────────────────┘
                       triggered by "Refresh Data" button
                (server.py POST /refresh -> runs all 3 in sequence,
                 backs up + restores each JSON if one export fails)
                                         │
                                         ▼
                    dashboard.html fetches the 3 .json files
                    directly as static files — no other server logic

Important: "Refresh Data" does not trigger "Rebuild Raw Table" — they're independent buttons. Since export_segmented.py reads from wo_forecast_raw_v2, the Trends/Drivers/Triage/Diagnostics tabs only reflect new loan data after a rebuild; Cohorts and cost/profitability data are independent of it. If you want everything current, run rebuild then refresh.

Forecast Chain

Stage	Method	Description
TPG ~ FPG	Linear regression	Trained on mature cohorts
DPD30 ~ TPG	Linear regression	Trained on post-regime-change data
Loss	Gap method	Loss(M) = DPD30(M) + mean(Loss − DPD30) over last 3 mature months

Prediction intervals use the t-distribution with n−1 degrees of freedom. Bounds fan linearly from 0 at the last mature month to full width at the last forecast month. Hard cap at 0%. See FORECAST_PIPELINE.md for the full derivation and revision history.

Dashboard Tabs

Tab	What it shows
Trends	Main KPI cards, alerts, multi-line forecast chart (any segment combination side-by-side), loan-amount-by-segment chart, coverage chart
Drivers	Waterfall decomposition of month-over-month change in the selected forecast stage, by segment contribution
Triage	Loss rate × volume scatter, a sortable segment triage table, and a segment × month heatmap — filterable by data-quality tier
Cohorts	Cohort curves / vintage heatmap of cumulative loss by month-on-book, from data_cohort.json
Profitability	Real revenue/cost (from the Unit Economics sheet) vs. this dashboard's own forecasted credit loss, per cohort — with a segment picker and collapsible cost breakdown
Simulations	Two parts: (1) a disbursement × loss-rate profitability heatmap for a hypothetical cohort, with independently-toggleable historical cohorts for revenue/variable cost/fixed cost; (2) a cohort builder — assemble a hypothetical cohort from multiple segments, each sized/rated manually or from a historical reference, and see the resulting cohort's net profitability
Diagnostics	In-sample backtest of the full FPG→TPG→DPD30→Loss chain against actual outcomes

Scripts

Active Pipeline

Script	Purpose	Reads from	Writes
server.py	Flask app: serves the dashboard + JSON files, exposes /refresh, /rebuild, /last-rebuild	—	—
export_segmented.py	Run export_agg_segmented.sql, auto-detect flag columns, generate dashboard data	wo_forecast_raw_v2 (BQ)	data_segmented.json
export_cohort.py	Run export_cohort.sql, generate cohort/vintage data	olap_loan, olap_loan_daily (BQ)	data_cohort.json
export_costs.py	Pull per-cohort revenue/cost rates from the Unit Economics Google Sheet	Google Sheets API	data_costs.json
build_standalone.py	Embed data_segmented.json into a single-file dashboard	dashboard.html + data_segmented.json	dashboard_standalone.html

Legacy / Archived — not part of the current pipeline

These predate the current BigQuery-driven export flow and are not called by server.py or any active script. Kept for reference only.

Script	Purpose
fill_forecasts.py	Old gap-method forecast fill from a CSV export (hardcoded C:\Users\...\Documents paths)
print_gap_forecasts.py	Debug script — prints gap-method forecast tables to stdout from the same old CSV
archive/fill_forecasts_v2.py	Same idea as fill_forecasts.py, redone with scikit-learn LinearRegression
archive/*.sql	Superseded/exploratory SQL (old raw-table versions, ad-hoc coverage checks)

SQL (BigQuery)

File	Purpose
create_wo_forecast_raw_v2.sql	Build the per-loan master table with flag columns (run via "Rebuild Raw Table")
export_agg_segmented.sql	Pre-aggregate wo_forecast_raw_v2 by segments, for export_segmented.py
export_cohort.sql	Cohort/vintage aggregation direct from the datamart, for export_cohort.py
pull_loan_data.sql	Raw loan data pull
loan_p1_default_v*.sql	Loan payment default queries

server.py Endpoints

Route	Method	Purpose
/	GET	Serves dashboard.html
/refresh	POST	Runs export_segmented.py → export_cohort.py → export_costs.py in sequence; on any failure, restores each JSON file to its pre-refresh contents
/rebuild	POST	Runs create_wo_forecast_raw_v2.sql via bq query --destination_table=... --replace
/last-rebuild	GET	Returns wo_forecast_raw_v2's last-modified timestamp via bq show
/<file>.{html,json,js,css,csv,png,svg,ico,txt}	GET	Serves any other file in the repo root by name

Self-Hosting / Automated Refresh

To run this unattended on a server instead of locally:

1. Auth is the main thing to set up. bq (used by rebuild, and internally by the segmented/cohort exports) and the Sheets API call in export_costs.py both rely on gcloud auth login --enable-gdrive-access having been run once — an interactive user login, not a service account. gcloud persists a refresh token locally and auto-refreshes access tokens on every call, so this works fine unattended after the one-time interactive login on that machine (or switch to a service account with BigQuery Data Viewer/Job User roles and share the Unit Economics sheet with its email, then gcloud auth activate-service-account).
2. No OS-specific blockers — the only Windows-specific paths in the active scripts are bq.cmd/gcloud.cmd fallback lookups, only used if those tools aren't already on PATH.
3. Schedule rebuild before refresh, since export_segmented.py depends on the rebuilt table. With the server running, the simplest approach is to curl its own endpoints from cron (or a systemd timer) rather than reimplementing the backup/rollback logic already in server.py:

   curl -X POST http://localhost:8080/rebuild
   curl -X POST http://localhost:8080/refresh
   

4. numpy/pandas/scikit-learn in requirements.txt are only needed by the archived fill_forecasts_v2.py — the live pipeline (export_*.py, server.py) only needs flask and requests.

Requirements

• Python 3.7+
• Google Cloud SDK (bq + gcloud CLIs)
• BigQuery access to julo-analytics and julo-datamart datasets
• Read access to the Unit Economics Google Sheet, and the Drive API scope on your gcloud login (--enable-gdrive-access)

Project Structure

.
├── dashboard.html                  # Interactive dashboard (all tabs, all JS)
├── server.py                       # Flask server: serves files, /refresh, /rebuild
├── build_standalone.py             # Single-file dashboard builder (currently stale)
├── export_segmented.py             # BQ -> data_segmented.json
├── export_cohort.py                # BQ -> data_cohort.json
├── export_costs.py                 # Google Sheet -> data_costs.json
├── create_wo_forecast_raw_v2.sql   # Rebuilds the wo_forecast_raw_v2 BQ table
├── export_agg_segmented.sql        # Used by export_segmented.py
├── export_cohort.sql               # Used by export_cohort.py
├── data_segmented.json             # Dashboard input (generated)
├── data_cohort.json                # Dashboard input (generated)
├── data_costs.json                 # Dashboard input (generated)
├── fill_forecasts.py               # Legacy — see Scripts table
├── print_gap_forecasts.py          # Legacy — see Scripts table
├── archive/                         # Superseded scripts/SQL, kept for reference
├── requirements.txt                # Python dependencies
├── FORECAST_PIPELINE.md            # Forecast methodology deep-dive + revision history
├── CHANGELOG.md                    # Version history
└── .planning/                      # PROPOSAL/SPEC/design/task_plan docs per feature

Key Concepts

Term	Meaning
FPG	First Payment Guarantee — default probability at first payment
TPG	Third Payment Guarantee — default probability at third payment
DPD30	Days Past Due 30 at month 5 — standard default indicator
Loss	Write-off rate (DPD181+ eventually reached)
Mature	Coverage ≥ 99.5% — all loans observed for the relevant window
Gap method	Loss(M) = DPD30(M) + mean(Loss − DPD30) over recent mature months
Line	User-defined segment combination with an optional complement, on the Trends tab
Blended rate	A cohort's FTC + Repeat rates combined, weighted by each one's own disbursement share (not loan count)
Fixed vs. variable cost	OPEX doesn't scale with disbursement (a flat $ total); Cost of Fund, Transaction Cost, and CAC & Servicing do (a rate × disbursement) — this distinction drives both the Profitability tab and the Simulations tab

Known Limitations

• build_standalone.py is stale. It was written against an earlier version of dashboard.html's data-loading code and no longer matches it (the segmented-data fetch call it looks for has since changed signature), and it never handled data_costs.json at all. Running it today will not correctly inline the current dashboard's data — it needs an update before it's usable again.
• claude-in-chrome/browser automation was disconnected for the session that built the Profitability/Simulations tabs, so their on-screen layout and interactions (dropdowns, drag-select, collapse animation) are verified via a Node.js harness against real data, not confirmed visually in a live browser.