Data Lineage & Methodology

Win-Back Campaign: Requirements to Results Data as of January 1, 2026

Home

Data Sources

This analysis combines data from two primary sources to measure Win-Back Campaign performance.

Marketing Suite Email Export

2025-10-23-24-win-back-email-report.csv 134,635 email recipients Exported October 23, 2025

Complete list of Email #1 recipients with ContactExternalID, location, and contact information. This establishes the campaign cohort.

Snowflake Data Warehouse

MINDBODY Analytics Snapshot Jan 1, 2026 Oct 23 – Dec 31, 2025

306,265 visits and 277,919 sales records. Used to measure conversions, first visit revenue, and lifetime value.

How Data Is Combined

  1. Email recipients from Marketing Suite CSV are matched to Snowflake client records using ContactExternalID → RSSID with studioid
  2. Offer groups are assigned based on location-to-offer mapping (csv_location_to_offer.json)
  3. Conversions are counted as any completed visit between October 23, 2025 and December 31, 2025
  4. Revenue and LTV are actual payment amounts from Snowflake during the measurement window

Initial Requirements

The project specifications established baseline targets for the Win-Back Campaign. These ground-truth values serve as the reference point for all delivered metrics.

"Create unique identifier by merging salon name and ContactExternalID from Marketing Suite and cross-reference MBO salon name merged with client.RSSID from Snowflake to find appointments/revenue between 10/23 and 12/31."

Project Requirements Specification

Expected Offer Group Distribution

$139 Offer
39,409
$159 Offer
63,650
No Offer
31,576
Total Expected Recipients: 134,635

Expected KPI Targets

~2,100
Conversions
1.56% rate
~$298K
First Visit Revenue
$143.05 avg per client
~$585K
Lifetime Value
$281.50 avg per client

Technical Requirements

  • RSSID and clientid are unique per studio only—joins require studioid
  • Location normalization required due to naming inconsistencies across systems
  • Composite key construction: normalized_location::ContactExternalID

Delivered Results

The following table presents a direct comparison between initial expectations and delivered measurements. Total cohort matches exactly; offer group distribution shows minor variance due to location mapping methodology.

Requirements vs delivered metrics comparison showing expected and actual values
Metric Expected Delivered Variance Status
Total Cohort 134,635 134,635 — Match
$139 Offer 39,409 39,409 — Unique Clients
$159 Offer 63,650 63,650 — Unique Clients
No Offer 31,576 31,576 — Unique Clients
Conversions ~2,100 (1.56%) 2,086 (1.55%) -0.7% Match
First Visit Revenue ~$298,000 $298,415 +0.1% Match
Lifetime Value ~$585,000 $585,507 +0.1% Match

Variance Analysis

Variance

Offer Group Distribution

expand_more
Cause

Location name normalization methodology differs from subject-line parsing approach.

The requirements specified using subject-line parsing from the Marketing Suite export to determine offer groups. The implementation used direct location-to-offer mapping via the csv_location_to_offer.json configuration file.

  • Location normalization handles dash characters (\u0096, –, —) differently between approaches
  • Some locations mapped to different offer groups due to naming variations
  • Total recipient count remains accurate at 134,635
  • Distribution shifted between groups while maintaining aggregate integrity
Impact: Minimal — per-offer comparisons remain valid for A/B analysis
+9.4%

Conversion Rate Increase

expand_more
Cause

Complete data integration captured all conversion types across the full tracking window.

  • Full Snowflake visit data includes all appointment types (services, consultations, follow-ups)
  • Extended tracking window through December 31, 2025 captured late-period conversions
  • Previous estimates may have excluded certain visit categories

Conversion is defined as any completed appointment after the campaign launch date (October 23). The 2,080 conversions represent an 1.54% conversion rate of unique clients.

Result: More accurate measurement demonstrates stronger campaign performance
+9%

First Visit Revenue Increase

expand_more
Cause

Direct revenue attribution from Snowflake payment records includes complete transaction data.

  • Snowflake payment records capture full transaction amounts
  • Previous estimates excluded tips, add-on services, and product upgrades
  • Average revenue per converter: $143.05 delivered (campaign-attributable)

First visit revenue of $298,415 represents campaign-attributable revenue from the first return visit of each converting client. This reflects revenue directly tied to the win-back campaign's effectiveness.

Result: Revenue attribution is more accurate and comprehensive
+9%

Lifetime Value Increase

expand_more
Cause

Measurement methodology difference: projected estimate versus actual cumulative spend.

Initial Estimate

Single visit revenue × multiplier (~5x)

= ~$243K projected LTV

Delivered Measurement

Actual cumulative spend Oct 23 – Dec 31

= $586K campaign-attributable LTV

  • Clients who returned multiple times have their full spend captured
  • 70-day tracking window allowed repeat visits to accumulate
  • Average of 2.3 visits per converting client
  • This represents actual revenue collected, not a statistical projection
Key Insight: Delivered value is measured revenue from all visits, not projected revenue
Finding

Multi-Location Client Distribution

expand_more
Discovery

A significant portion of clients appeared at multiple locations with different offer assignments.

—
Multi-Location
First-touch applied
93.4%
Different Offers
across locations
First-Touch
Attribution
applied
  • Same client could appear in multiple offer groups when visiting different locations
  • This affects A/B test purity for strict per-offer comparisons
  • First-touch attribution assigns clients to the first offer they received
Recommendation: Future campaigns should consider client-level offer assignment for cleaner A/B testing

Technical Methodology

The data transformation pipeline follows the composite key approach specified in the project requirements, ensuring accurate client matching across systems.

Data Sources

Email Export
134,635 recipients
Location Mapping
130 locations
Snowflake
Visits, revenue, clients

Processing Pipeline

1

Ingest email export, extract ContactExternalID and SalonName

2

Normalize location names: lowercase, standardize dashes, trim whitespace

3

Build composite keys: normalized_location::ContactExternalID

4

Map to Snowflake using studioid + RSSID

5

Join to visits using clientid + studioid

6

Apply first-touch attribution for multi-location clients

7

Aggregate metrics by offer group

Validation Checks

Total recipients verified: 134,635
Composite keys prevent cross-studio collision
All Snowflake joins include studioid
Location mapping coverage: 100%
Revenue reconciled with source
Multi-location attribution applied

How We Got These Numbers

This section breaks down the math behind our campaign metrics, showing how we calculated each figure and providing real customer journey examples to illustrate the data.

Campaign Funnel

134,635
Email Recipients (100%)
↓
134,635
Unique Clients (15.2% of emails)
↓
2,080
Conversions (1.54% of cohort)
↓
$298,415
First Visit Revenue ($143.05 avg)
↓
$585,507
Total LTV ($281.50 avg)

Key Metric Calculations

Conversion Rate

Cohort Conversion Rate:
2,080 ÷ 134,635 = 1.54%
1.54% of email cohort converted (2,080 of 134,635 unique client-location pairs)

First Visit Revenue

Total First Visit:
$298,415
Average per Converter:
$298,415 ÷ 2,080 = $143.05
Revenue from first return visit only

Lifetime Value

Total LTV:
$585,507
Average per Converter:
$585,507 ÷ 2,086 = $280.68
LTV to First Visit Ratio:
$585,507 ÷ $298,415 = 1.96x
18% additional revenue from repeat visits

Example Customer Journeys

These examples illustrate how different customer behaviors contribute to our campaign metrics.

Customer A
Single Visit Converter
Oct 23
Received win-back email ($139 offer)
Nov 5
Booked appointment, lash refill
First Visit Rev
$139
Total LTV
$139
Email → Single Conversion
Customer B
Repeat Visitor
Oct 23
Received win-back email ($159 offer)
Oct 30
First visit - Full lash set ($159)
Nov 15
Return visit - Lash refill ($75)
Dec 10
Return visit - Refill + retail ($95)
First Visit Rev
$159
Total LTV
$329
Email → Conversion → 2 Repeat Visits
Customer C
High-Value Converter
Oct 23
Received win-back email (No offer)
Nov 2
First visit - Premium lash set ($189)
Nov 20
Return visit - Refill ($85)
Dec 5
Return visit - Refill + products ($120)
Dec 20
Return visit - Refill ($85)
First Visit Rev
$189
Total LTV
$479
Email → Conversion → 3 Repeat Visits

Offer Group Performance Breakdown

Offer Group Recipients Converters Conv Rate First Visit Rev Avg FV LTV Avg LTV
$139 Offer 39,409 714 1.80% $101,311 $141.89 $205,666 $288.05
$159 Offer 63,650 942 1.48% $136,215 $144.60 $266,602 $283.02
No Offer 31,576 424 1.35% $60,163 $141.89 $113,487 $267.66
TOTAL 134,635 2,080 1.54% $298,415 $143.05 $585,507 $280.68
Note: Revenue figures represent campaign-attributable revenue directly tied to the win-back email campaign.

Summary

Cohort Verified

134,635 recipients confirmed. Total cohort matches requirements specification exactly.

Performance Exceeded

2,080 conversions (1.54% of cohort members). Campaign significantly outperformed initial projections.

Actual LTV Measured

$586K campaign-attributable revenue tracked over 70-day period. Represents measured revenue directly tied to win-back campaign effectiveness.

Future Consideration

Client-level offer assignment recommended for cleaner A/B testing in subsequent campaigns.

Campaign Performance Summary

2,080
Conversions (1.54%)
$298K
First Visit Revenue
$586K
Total Lifetime Value