Retail Loyalty Program & Rewards Analytics data model
Kimball warehouse for retail loyalty: a transaction-grain points earn/burn fact, a periodic member-period snapshot for balances and liability, a redemption-event fact for reward economics, and a tier-change-event fact with role-played previous/new tier dimensions. Conformed dimensions cover member (SCD2 with PII), tier, store, offer, reward catalog, transaction type, product category, redemption channel, and change reason, with date and period for daily and periodic grains.
Retail loyalty program & rewards analytics for an omnichannel retailer: points earned on purchases and burned on catalog rewards, tiers (silver / gold / platinum / diamond) with points and spend thresholds, and a member lifecycle of new → active → at-risk → lapsed. The mart supports active-member counts, points liability, redemption rate, tier mix, attach rate, breakage percent, and cohort retention across in-store, web, app, and partner channels. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 15
- tables
- 141
- columns
- 19
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Transaction-grain points fact for earn-and-burn
Loyalty programs are intrinsically event-driven: every earn, redeem, adjust, and expire moves the points balance. Modelling the fact at one row per points event preserves the full event stream needed for points-flow, breakage, and channel attribution.
Periodic member snapshot for balance and liability
Active-member counts, points liability, and tier mix are best answered from a periodic snapshot at the member-by-period grain rather than re-aggregating the event stream, and the snapshot also carries lifecycle segment and tenure for retention cohorts.
Dedicated redemption fact for reward economics
Reward redemption has its own grain (one row per burn event) and its own measures — points used, face value, cost-to-serve, partner fulfilment — that don't belong inside a general points-transaction fact; separating the redemption fact keeps measures clean and reward-catalog drill-down crisp.
Tier-change fact with role-played previous and new tier
Tier movement is the primary engagement signal in a loyalty program. Capturing each transition as its own fact with two role-playing FKs into dim_tier (previous_tier_key and new_tier_key) makes upgrade / downgrade and anniversary-reset analysis a straightforward dimensional query.
SCD2 on dim_member to preserve as-was tier and segment
Tier and lifecycle segment change over time as members earn, lapse, and recover; type-2 history on dim_member preserves the as-was tier and segment so cohort retention, attach rate, and win-back attribution remain comparable across periods.
Tables in the Retail Loyalty Program & Rewards Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_points_transaction
Grain: one row per points earn / burn / adjust / expire event (transaction grain)
Transaction-grain earn-and-burn fact powering points-flow and breakage analysis.
| Column | Type | Keys |
|---|---|---|
| points_transaction_key | bigint | PK |
| transaction_date_key | bigint | |
| member_key | bigint | |
| store_key | bigint | |
| transaction_type_key | bigint | |
| offer_key | bigint | |
| product_category_key | bigint | |
| transaction_id | varchar | |
| sales_receipt_id | varchar | |
| points_earned | bigint | |
| points_redeemed | bigint | |
| points_adjusted | bigint | |
| points_expired | bigint | |
| transaction_amount | decimal | |
| points_value | decimal | |
| currency | varchar | |
| channel | varchar | |
| is_bonus | boolean |
fct_member_period_snapshot
Grain: one row per member per reporting period (periodic snapshot of program state)
Periodic-snapshot fact for points liability, tier mix, and lifecycle reporting.
| Column | Type | Keys |
|---|---|---|
| member_period_snapshot_key | bigint | PK |
| period_key | bigint | |
| member_key | bigint | |
| current_tier_key | bigint | |
| points_balance | bigint | |
| points_earned_period | bigint | |
| points_redeemed_period | bigint | |
| points_expired_period | bigint | |
| lifetime_points_earned | bigint | |
| spend_amount_period | decimal | |
| lifetime_spend | decimal | |
| order_count_period | integer | |
| tenure_months | integer | |
| days_since_last_activity | integer | |
| currency | varchar | |
| membership_status | varchar | |
| is_paying_tier | boolean |
fct_reward_redemption
Grain: one row per reward redemption (burn event grain)
Burn-event fact for redemption rate, reward economics, and partner attribution.
| Column | Type | Keys |
|---|---|---|
| reward_redemption_key | bigint | PK |
| redemption_date_key | bigint | |
| member_key | bigint | |
| reward_catalog_key | bigint | |
| store_key | bigint | |
| redemption_channel_key | bigint | |
| redemption_id | varchar | |
| points_used | bigint | |
| reward_face_value | decimal | |
| redemption_cost | decimal | |
| discount_applied | decimal | |
| currency | varchar | |
| redemption_outcome | varchar | |
| is_partner_reward | boolean |
fct_tier_change_event
Grain: one row per member tier transition (upgrade / downgrade / anniversary reset)
Tier-transition fact with role-played previous/new tier for movement analysis.
| Column | Type | Keys |
|---|---|---|
| tier_change_event_key | bigint | PK |
| change_date_key | bigint | |
| member_key | bigint | |
| previous_tier_key | bigint | |
| new_tier_key | bigint | |
| change_reason_key | bigint | |
| event_id | varchar | |
| days_in_previous_tier | integer | |
| points_at_change | bigint | |
| spend_at_change | decimal | |
| currency | varchar | |
| is_upgrade | boolean | |
| is_downgrade | boolean | |
| is_anniversary_reset | boolean |
Dimension tables (11)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean |
dim_period
Grain: one row per reporting period (month / quarter) for periodic snapshots
| Column | Type | Keys |
|---|---|---|
| period_key | bigint | PK |
| period_code | varchar | BK |
| period_type | varchar | |
| period_start_date | date | |
| period_end_date | date |
dim_member
Grain: one row per loyalty member per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| member_key | bigint | PK |
| member_id | varchar | BK |
| member_name | varchar | PII |
| varchar | PII | |
| join_date | date | |
| age_band | varchar | |
| gender | varchar | |
| country | varchar | |
| region | varchar | |
| signup_channel | varchar | |
| current_tier_at_load | varchar | |
| lifecycle_segment | varchar | |
| is_active | boolean |
dim_tier
Grain: one row per loyalty tier (silver / gold / platinum / diamond)
| Column | Type | Keys |
|---|---|---|
| tier_key | bigint | PK |
| tier_id | varchar | BK |
| tier_name | varchar | |
| tier_level | smallint | |
| points_threshold_min | bigint | |
| spend_threshold_min | decimal | |
| benefits_summary | varchar | |
| is_paid_tier | boolean | |
| currency | varchar |
dim_store
Grain: one row per physical or digital store
| Column | Type | Keys |
|---|---|---|
| store_key | bigint | PK |
| store_id | varchar | BK |
| store_name | varchar | |
| store_type | varchar | |
| region | varchar | |
| country | varchar | |
| opened_date | date | |
| is_active | boolean |
dim_offer
Grain: one row per promotional offer or campaign earning rule
| Column | Type | Keys |
|---|---|---|
| offer_key | bigint | PK |
| offer_id | varchar | BK |
| offer_name | varchar | |
| offer_type | varchar | |
| valid_from | date | |
| valid_to | date | |
| points_multiplier | decimal | |
| is_targeted | boolean |
dim_reward_catalog
Grain: one row per redeemable reward in the catalog
| Column | Type | Keys |
|---|---|---|
| reward_catalog_key | bigint | PK |
| reward_id | varchar | BK |
| reward_name | varchar | |
| reward_category | varchar | |
| points_required | bigint | |
| face_value | decimal | |
| partner | varchar | |
| is_active | boolean |
dim_transaction_type
Grain: one row per points transaction type (earn / redeem / expire / adjust / transfer)
| Column | Type | Keys |
|---|---|---|
| transaction_type_key | bigint | PK |
| transaction_type | varchar | BK |
| description | varchar | |
| is_positive_balance_impact | boolean |
dim_product_category
Grain: one row per product category for attach-rate analysis
| Column | Type | Keys |
|---|---|---|
| product_category_key | bigint | PK |
| category_id | varchar | BK |
| category_name | varchar | |
| category_parent | varchar | |
| attaches_loyalty | boolean | |
| is_high_margin | boolean |
dim_redemption_channel
Grain: one row per channel a reward can be redeemed through
| Column | Type | Keys |
|---|---|---|
| redemption_channel_key | bigint | PK |
| redemption_channel | varchar | BK |
| channel_name | varchar | |
| is_digital | boolean |
dim_change_reason
Grain: one row per tier-change reason (threshold-reached / anniversary-reset / manual-adjust / expiration)
| Column | Type | Keys |
|---|---|---|
| change_reason_key | bigint | PK |
| change_reason | varchar | BK |
| description | varchar | |
| is_automatic | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_points_transaction.transaction_date_key → dim_date (many-to-one)
- fct_points_transaction.member_key → dim_member (many-to-one)
- fct_points_transaction.store_key → dim_store (many-to-one)
- fct_points_transaction.transaction_type_key → dim_transaction_type (many-to-one)
- fct_points_transaction.offer_key → dim_offer (many-to-one)
- fct_points_transaction.product_category_key → dim_product_category (many-to-one)
- fct_member_period_snapshot.period_key → dim_period (many-to-one)
- fct_member_period_snapshot.member_key → dim_member (many-to-one)
- fct_member_period_snapshot.current_tier_key → dim_tier (many-to-one)
- fct_reward_redemption.redemption_date_key → dim_date (many-to-one)
- fct_reward_redemption.member_key → dim_member (many-to-one)
- fct_reward_redemption.reward_catalog_key → dim_reward_catalog (many-to-one)
- fct_reward_redemption.store_key → dim_store (many-to-one)
- fct_reward_redemption.redemption_channel_key → dim_redemption_channel (many-to-one)
- fct_tier_change_event.change_date_key → dim_date (many-to-one)
- fct_tier_change_event.member_key → dim_member (many-to-one)
- fct_tier_change_event.previous_tier_key → dim_tier (many-to-one)
- fct_tier_change_event.new_tier_key → dim_tier (many-to-one)
- fct_tier_change_event.change_reason_key → dim_change_reason (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.