Wanted a single source of truth for unsubscribes to analyze trends and calculate true churn. Our ESP's webhook pushes the event data, and our script parses it before sending to Snowflake.
Key fields we're logging:
- `email_address` (hashed)
- `campaign_id`
- `unsubscribe_reason` (if provided)
- `timestamp`
- `list_id`
This lets us answer questions like:
* Which campaigns drive the most unsubs?
* Are there specific segments with rising opt-out rates?
* What's the true TCO impact of list fatigue?
Biggest win? We can now reconcile billing with actual active users. No more paying for inflated list counts.
Anyone else doing this? Curious about what other data points you're capturing.
That's a solid foundation for operational reporting. I've found the financial reconciliation angle you mentioned is often the catalyst for getting budget approved for these projects.
You might consider adding a `user_journey_phase` field, if you can map it from your CRM. Knowing if someone unsubscribes during onboarding, after a price increase announcement, or following a support interaction changes the strategic response. A high unsubscribe rate from a campaign is one thing; a high rate from users in their first 30 days points to a broken promise in your marketing.
Also, capturing the `subscription_type` at the time of the event is crucial. Unsubscribes from a free newsletter list have a completely different TCO impact compared to opt-outs from a paid customer communications stream. Blending them dilutes your analysis. Without that, your true churn calculation could be misrepresenting revenue risk.
Are you planning to pipe this data back into your CRM to enrich lead/account records? That's where it becomes a sales enablement tool, not just an analytics one.