Context
At Census (acquired by Fivetran), I had a front-row seat to our own product data - and a nagging question nobody was answering: which free accounts were actually going to upgrade? So I built this. The approach - profiling your best customers and scoring everyone else against them - works for any company with tiered pricing and customer data in a warehouse.
The idea
Which lower-tier customers look like higher-tier customers? That's it. Find the traits that show up more often in your best customers, then check who else has those traits but hasn't upgraded yet.
Two data points per customer: an upsell score and the trait they match most. The score tells you who to talk to. The trait tells you what to say.
How I built it
Step 1: Prep the data
I wrote a query that grouped customers by their plan tier and pulled their traits into a single field per tier. Customers from the last 90-120 days - recent enough to be relevant. If you're not comfortable with SQL, you can hand this off to your data team. It's a straightforward pull.
Step 2: Run the trait analysis
I used AI to compare the highest tier against the lowest and find which traits show up more often in premium customers. The output is a list of traits ranked by how much more likely each one is to appear in a high-value customer.
Visit Frequency: Daily - 1.58x more likely to be high-value
Age Range: 18-25 - 1.43x more likely to be high-value
Personal Training: Yes - 1.35x more likely to be high-value
I copied the output into ChatGPT to build a clean table I could send to stakeholders for approval. Get the team to sign off before you score anything - if they don't trust the analysis, they won't use the scores.
Step 3: Score every customer
Once the analysis is approved, attach it to every customer record and run a second AI prompt. For each customer, compare their traits against the benchmark and output two separate fields: the upsell score (high, medium, low) and the top matching trait.
The top trait is the whole point. A customer who scores high because of personal training usage gets a different message than one who scores high because of visit frequency. Generic upgrade emails become targeted conversations.
Step 4: Validate and sync
Before you ship it, spot-check the outputs. Do the scores make sense for customers you know well? Once you're happy, sync the scores and top traits to wherever your team works - marketing automation, ad platforms, CRM. Create a segment, run the campaign.
What I learned
- One score isn't enough. The top trait is what makes this actionable. Without it, you're just sending the same upgrade email to everyone.
- Get buy-in on the analysis first. If your team doesn't agree with which traits matter, the scores won't get used.
- Recency matters. Customers from the last 90-120 days gave the best signal. Too far back and you're scoring against outdated patterns.