BigQuery RFM segmentation with Power BI

Result: the pipeline segments customers in BigQuery and exposes the segments in a Power BI dashboard. The customer count and lift are add customer count and lift.

Problem

A segmentation table is only useful if a team can act on it. Raw RFM scores are not a segment. The project turns transactions into named groups, then puts the group size and behavior in a dashboard a non analyst can read.

Data

The dataset is add source and observation window. The customer key is add key definition. The transaction table is add table and date range. All public screenshots use aggregated data only.

Approach and why

Define a customer and an observation window.
Score recency, frequency, and monetary value with explicit bins or quantiles.
Combine the three scores into named segments with written rules.
Store the segment table in BigQuery.
Build the Power BI dashboard for segment size, revenue share, and recency.

I used explicit rules instead of a clustering model because the segments have to be explained to a business team. A cluster without a name is a hard sell.

Results

Metric	Value	Notes
Customers segmented	add count	After the observation window
Segments	add count and names	Written rules
Lift or retention	add metric, comparison group, and result	Definition required

Evaluation

Leakage: not applicable. The observation window and the outcome window must not overlap if a lift metric is added.
Baseline: the comparison group for lift is add group definition.
Validation: SQL tests for null keys, duplicate customers, and segment coverage.
Metric: segment size and revenue share are descriptive. Lift needs a target action and a base rate.
Reproducibility: the SQL is versioned, the window is parameterized, and the dashboard refresh is scheduled.

A reviewer should challenge "lift" without a comparison group. They should also challenge a dashboard screenshot that shows row level customer data.

Limitations

The lift metric is not defined yet. The dashboard can go stale without a refresh schedule. The BigQuery dataset location and the Power BI data source region can conflict, and the fix is add the region conflict note and resolution. The current dashboard is a snapshot, not a live product.

Next steps

Define the lift metric and comparison group, add a scheduled refresh with a last updated timestamp, document the region decision, and add a privacy review for every dashboard page.