Data Engineering

Warehouse Modeling for Customer Analytics

A warehouse model for retention, repeat purchase, and cohorts.

Model style

Star schema

Primary user

Analyst

Focus

Metric trust

Context

Problem

Customer activity data needed a consistent model for retention, repeat purchase, and segmentation metrics.

Solution

Created fact and dimension tables with documented metric logic so analysts can query reliable customer cohorts.

Tools

SQL / Dimensional modeling / Data marts / PostgreSQL

Journey

  1. 01Define business questions around active customers, repeat purchase, and retention.
  2. 02Design customer, order, and calendar dimensions with stable keys.
  3. 03Create fact tables for events and transactions at query-friendly grains.
  4. 04Document metric definitions so dashboard numbers stay consistent.
  5. 05Build sample cohort queries for analyst and stakeholder use cases.

Output

  • Customer analytics mart with clean metric definitions.
  • Reusable SQL queries for cohort and retention analysis.
  • Schema notes explaining grain, keys, and assumptions.

Explore the model

Review sample queries and see how retention metrics are calculated from the warehouse layer.