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.

Why SQL?

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.

Context

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?

Run this template against our sample database that mirrors real startup data. See the connection credentials, then connect in PopSQL.

Pro Tips

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!

• Automate this query to run hourly and then send results with the highest lead_scores to Slack with PopSQL’s scheduled query + Slack integration features.