Predictive purchase models in the data warehouse

7predictive models

Millionsof customers scored on a schedule

[ Architecture ]

How it works

Run a scenario to follow it through the system, step by step. Select a component to read its role and see what it connects to.

batch

Scheduled scoring run

The scheduled run starts and refreshes every score.

ConsoleSimulated run · illustrative data

Press Score the customer base to watch each step run through the system.

01Source tablesScheduled run starts · 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 teams 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.