Creating Lead Scores in SQL
Not all leads have the same probability of converting into customers.
Time is precious. Obviously, you need to focus on only the best leads.
It will take a few tries to get your scoring right. SQL is fast and flexible (especially in PopSQL -- our UI makes ad hoc, iterative exploration a piece of 🍰 ).
Eventually a data engineer will incorporate your Lead Score into a data pipeline. There are many compelling reasons why she'll do her data transformations in SQL. By starting with SQL, you make it easy.
Imagine you lead the sales team. After much customer research, you've found that 4 criteria that highly predict Closed Won. Namely, the user has:
- signed up with a work email account (see our template on how to tag emails as work vs. personal),
- invited a friend
- visited the pricing page,
- and taken 50 actions in the product.
SQL you can copy / paste
This query assigns weights to each of the criteria above. First, make a CTE called
lead_score_inputs. Next, aggregate the inputs into a lead score.
with lead_score_inputs as ( select id, first_name, last_name, email, -- creating score for email (simplified) case when email similar to '%(gmail|yahoo|outlook|hotmail)%' then -1 else 0 end as email_score, -- creating score for invited a friend case when shared_canvas = TRUE then 5 -- sharing a canvas in our demo data else 0 end as shared_canvas_score, -- creating score for visited pricing page case when visited_pricing_page = TRUE then 1 else 0 end as pricing_page_score, -- creating score for activity count case when activity_count > 50 TRUE then 1 else 0 end as activity_score, from fact_users where created_at > now() - interval '1 day' ) select (email_score + shared_canvas_score + pricing_page_score + activity_score) as lead_score, id, first_name, last_name, email from lead_score_inputs order by 1 desc;
Note: this example uses a
fact_users table that nicely pre-aggregates our needed inputs (e.g.
activity_count). In reality, you’d likely need to create extra CTEs to compute each input, then join all the CTEs on
user_id. Let us know if you'd like to see that as a template.
Try it yourself?
Be thoughtful and iterative with your weights
- Email Score: we didn’t weigh it too negatively (sometimes great leads sign up with personal accounts).
- Shared Canvas: we gave it the heaviest positive weight since we’ve found that to be the Aha moment (template coming soon).
Compare against existing engaged customers
- As you iterate, generate lead scores for your existing engaged customers retroactively (looking at data in their first ~1 month of usage of your product)
- Does your weighting model score them accurately?
Further enrich your leads with Clearbit
- By enriching your leads with Clearbit data, you can add extra weight if a user signs up from a large company, is a director or executive, etc. It’s powerful!
Get this data where you see it: Slack!
Spread the wordTweet