Predictive models in the data warehouse

7predictive models

M+customers scored on schedule

[ Architecture ]

How it works

Run a scenario to follow a request through the system. Select a component to read its role and its flows.

batch

Scheduled scoring run

The schedule fires and refreshes every model.

ConsoleSimulated run · illustrative data

Press Score the customer base to watch the request go through the system.

01Source tablesSchedule fires · latest tables read
02Feature pipelineFeatures refreshed in SQL
03BigQuery ML modelsSeven models score the customer base · version and metrics tracked
04Indicator tablesIndicator tables written
05Analyst dashboardsDashboards refreshed for analysts
✓Dashboards hold fresh scores without anyone launching a job, and every score traces back to a tracked model version.

Problem

The business wanted to understand why customers buy, model by model, in the data warehouse it already uses, and to keep the scores fresh without manual work.

Data

Customer and purchase tables in the warehouse, prepared into model inputs in SQL. The data stays in the warehouse throughout.

Approach

Models where the data lives. Seven models, built with XGBoost and logistic regression in BigQuery ML, with no data movement.
SQL preparation. The feature pipeline is versioned and rerunnable.
Automated deployments and tracking. Each model version is tracked with its metrics.
Indicators for analysts. Scores and their drivers are written to tables read through usual dashboards, refreshed on a schedule.

Evaluation

Each model was compared on held-out customers before release, and a new version is compared to the previous one before it replaces it.

Result

Seven models score millions of customers on schedule, with no manual step before analysts read the indicators.

My role

I handled the data preparation, the models, the automated deployments, the model tracking and the indicators used for business analysis.