Measuring what new supporters do next
From my résumé Figures from my résumé; examples are made up
Redesigned acquisition metrics so they follow every action a new supporter takes after the first one, for 185K+ new supporters, plus a retention scoring framework.
- Status
- My job at EDF · no employer data shown
- Relevant to
- Analytics and BI, Data engineering
Results · Figures from my résumé; examples are made up
- From my résuméFrom my résumé, for my job at Environmental Defense Fund. The records, queries and internal figures behind it stay private.new supporters covered by the redesigned metricsFrom my résumé
- From my résuméFrom my résumé, for my job at Environmental Defense Fund. The records, queries and internal figures behind it stay private.follow-up actions surfaced that the old definitions didn’t countFrom my résumé
The problem
Acquisition reporting counted the first action, usually a sign-up, and stopped there. Teams comparing sources and vendors couldn’t see which ones brought people who stayed involved.
My role
I redesigned the acquisition engagement metrics and the Hex reporting, and designed the retention scoring framework. The framework is a design; it isn’t running.
How it works
1.Acquire
Identify each supporter’s first action and where it came from, with sources and UTM tags normalized.
2.Cohort
Group supporters by the month they joined and by source or vendor.
3.Follow
Count every qualifying action after the first one, over the same windows for every cohort.
4.Compare
Compare sources and vendors on follow-up engagement per new supporter, not raw totals.
5.Review
Teams filter by UTM and source in Hex, with data loaded automatically.
What I built
- New metric definitions that capture every action after a supporter’s first one.
- Multi-select UTM filtering and automatic data loading in Hex.
- Source normalization and UTM reconciliation, so the same source isn’t split across spellings.
- Monthly acquisition cohorts and vendor comparisons normalized per new supporter.
- A retention scoring framework (designed, not running): weighted behaviors, lapse definitions and four segments, Core Activists, Engaged, At-Risk and Dormant, with a dependency on deduplication noted.
Example
| Join month | Source | New supporters | Took a second action |
|---|---|---|---|
| Month A | Petition partner | 1,000 | 18% |
| Month A | Paid social | 1,000 | 9% |
| Month B | Petition partner | 1,000 | 21% |
Decisions and tradeoffs
- Separate the acquisition from the follow-up
- The first action is how someone arrived; later actions are whether they stayed. Mixing them makes every source look the same.
- Normalize per new supporter
- A vendor that brings more people will always have more total actions. Per-supporter rates make vendors comparable.
- Retention scores wait for deduplication
- Scoring people before duplicates are merged splits one person’s activity across records and understates engagement.
Validation
- The new dashboard numbers were checked against the source data before teams used them.
Limitations
- Described without employer data. The example table is made up.
- The retention framework is a design, not a running system.
Why it matters for the job
Marketing analytics, growth and RevOps roles all ask the same question: which channels bring customers who stay. This is that work, with the metric definitions done carefully.
Stack
Snowflake, SQL, Hex, Tableau
Related work
- Finding 44K members missing from the CRM
My job at EDF · no employer data shown
- Did the marketing campaign actually work?
Write-up ready, code not public yet
- Spotting customers about to cancel
Write-up ready, code not public yet