Starbucks Promotion Optimization

Part 1 — The Attribution Problem

Raw Starbucks transaction logs contain a fundamental statistical trap: customers who would have purchased anyway ("organic buyers") are incorrectly counted as offer-driven conversions under simple aggregation. Traditional metrics overestimated campaign performance by 15–18%.

Attribution logic — before vs. after

📊 Counts Received → Completed regardless of sequence

🔇 Attribution noise from organic buyers

⚠️ Overstated effective rate by 15–18%

→

✅ Received → Viewed → Completed (strict order)

🎯 Completion only valid after offer was viewed

💡 True attribution: c.time >= v.time

SQL-based data pipeline

Raw Data Normalization

Parsed JSON event logs into structured transaction tables

SQL

Temporal Segmentation

Filtered events within offer valid window using valid_hours

SQL

Sequential Mapping

LEFT JOIN with strict time ordering: c.time >= v.time to exclude organic completions

Sequence Validation

Value Attribution

Calculated net_revenue_contribution = offer_spend − actual_reward

Finance

Customer-Centric Aggregation

Produced final table: effective_response_rate, avg_spend_per_offer, net_revenue_contribution

Output

            -- Validating chronological sequence to filter out Organic
              Buyers

            SELECT

              o.person_id,

              MIN(v.time)
            AS viewed_time,

              MIN(c.time)
            AS completed_time

            FROM Segmentation o

            LEFT JOIN df_viewed v 

              ON o.person_id =
            v.person

              AND v.time
            BETWEEN o.received_time
            AND o.received_time +
            o.valid_hours

            LEFT JOIN df_completed c 

              ON o.person_id =
            c.person

              AND c.time >= v.time
            -- Logic: Completion must happen AFTER viewing
          

Part 2 — RFM Segmentation & Targeting

With clean attribution data, I applied RFM scoring to segment 27 customer clusters (3 tiers × Recency × Frequency × Monetary), then overlaid demographic profiling to identify which segments respond to which offer types.

Key RFM segments

3-3-3

VVIP

Core revenue drivers. Highest brand loyalty. Largest single segment (n=1,355).

2-3-3

Potential

High spenders who haven't visited recently. Primary reactivation target.

1-1-1

Light / Churned

Low engagement, high churn risk. Reduce BOGO spend for this group.

Analysis — Offer Type Effectiveness by Demographic

Demographic comparison of VVIP (333) vs. Inactive (111) segments revealed that M_50s and F_50s dominate the best segment (2,899 and 2,757 effective completions respectively). Offer effectiveness also split sharply by gender and type.

Effective response rate by offer & gender

F_40s

~50%

F_50s

~50%

M_20s

24%

M_30s

32%

F_90s

52.9%

M_50s

44.9%

Discount avg

44.6%

BOGO avg

40.4%

Recommendations

Segment	Target Group	Action	Expected Impact
VVIP (333)	4060F	Prioritize Discount offers	Maximize ROI from highest-transaction group
Potential (233)	M_50s, F_50s	Reactivation via Discount	Recapture lapsed high-value customers
Light (111)	M_10s–20s	Stop BOGO; switch to Experience	Eliminate lowest ROI: 20.1% (10M BOGO)
Cherry Pickers	High completion, low spend	Increase offer difficulty	Ensure positive net revenue contribution

RFM Segments

15–18%

Attribution Noise Removed

44.6%

Discount Effective Rate

15–20%

CVR/Dollar Improvement

Starbucks PromotionOptimization

Part 1 — The Attribution Problem

Part 2 — RFM Segmentation & Targeting

Analysis — Offer Type Effectiveness by Demographic

Recommendations

Starbucks Promotion
Optimization