Loyalty Redemption Rate
Also known as: Points Redemption Rate, Loyalty Points Redemption Rate, Reward Redemption Rate
Loyalty points redeemed by members in the period as a share of points earned in the same period. A low rate means an unused points liability is building up; a rising rate means members value the rewards.
Business Use Case
1️⃣ Loyalty Marketing: rate falls → email members whose balance already clears the lowest reward threshold. 2️⃣ Finance: rate shifts for two quarters → re-estimate breakage and adjust the points liability accrual. 3️⃣ Merchandising: one tier redeems far less → add lower-cost rewards to that tier's catalogue.
Business Criticality
Medium
Journey / Navigation Area
Engagement
Owning Function
Marketing
Scope
Global
Formula
Calculation Notes
1️⃣ Numerator: redemption debits in the loyalty ledger dated in the period, net of redemptions cancelled or reversed. 2️⃣ Denominator: earn credits in the same period (purchase, bonus and promotional awards), net of points clawed back on refunds. 3️⃣ Exclude expired points, manual adjustments, points transferred between members and test or employee accounts from both sides. 4️⃣ Period-based, not cohort-based: points earned earlier can be redeemed now, so a single month can exceed 100%; trend it quarterly. 5️⃣ Sum points, never currency; programmes with several point types convert them to one base point before summing. 6️⃣ The ledger holds online, app and in-store activity; the digital Loyalty Reward Redemption event is for funnel analysis, not this rate. 7️⃣ When no points were earned in the period, report null, not 0% or 100%.
Technical Details
Events
SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-- Ledger entries in the period from real members, in base points; expiries, manual adjustments and transfers are left out by type
WITH ledger AS (
SELECT
l.transaction_type,
ABS(l.points) * pt.base_point_conversion_rate AS base_points
FROM `your-project.crm.loyalty_ledger` AS l
JOIN `your-project.crm.loyalty_point_types` AS pt ON pt.point_type = l.point_type
JOIN `your-project.crm.loyalty_members` AS m ON m.member_id = l.member_id
WHERE l.transaction_date BETWEEN start_date AND end_date
AND l.transaction_type IN ('redemption', 'redemption_cancel', 'redemption_reversal',
'earn_purchase', 'earn_bonus', 'earn_promotion', 'refund_clawback')
AND m.account_type NOT IN ('test', 'employee')
)
-- Net points redeemed / net points earned x 100; null when no points were earned
SELECT
ROUND(100 * SAFE_DIVIDE(
SUM(CASE WHEN transaction_type = 'redemption' THEN base_points
WHEN transaction_type IN ('redemption_cancel', 'redemption_reversal') THEN -base_points
ELSE 0 END),
SUM(CASE WHEN transaction_type IN ('earn_purchase', 'earn_bonus', 'earn_promotion') THEN base_points
WHEN transaction_type = 'refund_clawback' THEN -base_points
ELSE 0 END)
), 2) AS loyalty_redemption_rate_pct
FROM ledger;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Ledger entries in the period (last 90 days to yesterday) from real members, in base points; expiries, manual adjustments and transfers are left out by type
WITH ledger AS (
SELECT
l.transaction_type,
ABS(l.points) * pt.base_point_conversion_rate AS base_points
FROM crm_loyalty_ledger AS l
JOIN crm_loyalty_point_types AS pt ON pt.point_type = l.point_type
JOIN crm_loyalty_members AS m ON m.member_id = l.member_id
WHERE l.transaction_date BETWEEN DATE_SUB(CURRENT_DATE, 90) AND DATE_SUB(CURRENT_DATE, 1)
AND l.transaction_type IN ('redemption', 'redemption_cancel', 'redemption_reversal',
'earn_purchase', 'earn_bonus', 'earn_promotion', 'refund_clawback')
AND m.account_type NOT IN ('test', 'employee')
)
-- Net points redeemed / net points earned x 100; null when no points were earned
SELECT
ROUND(100 * SUM(CASE WHEN transaction_type = 'redemption' THEN base_points
WHEN transaction_type IN ('redemption_cancel', 'redemption_reversal') THEN -base_points
ELSE 0 END)
/ NULLIF(SUM(CASE WHEN transaction_type IN ('earn_purchase', 'earn_bonus', 'earn_promotion') THEN base_points
WHEN transaction_type = 'refund_clawback' THEN -base_points
ELSE 0 END), 0), 2) AS loyalty_redemption_rate_pct
FROM ledger;Source Data
CRM
Dependencies
Prerequisite: List the dependencies required for this KPI
Events:
Metrics:
Dimensions:
Report Attributes
Customer Loyalty Tier Loyalty Member Status Device Category Customer Tenure Band Acquisition Cohort
Recommended Reporting Views
Segment Eligibility
true
Governance
Governance
GitHub
catalog/kpis/loyalty-redemption-rate.yml