How to calculate a lead score in SQL

Intro

If you’ve ever worked at a B2B company, you understand precisely how essential lead generation is to your bottom line. The problem is that only a tiny percentage of your leads convert into real customers, so you need a simple way to prioritize the ones that matter.

What is Lead Scoring?

Identifying what makes a quality lead can be challenging because every company has a different business model and north star metric. For example, here at Hightouch, the north star metric is workspaces created. Depending on your business model, that metric will vary drastically.

Lead scoring applies a numerical value for every lead you generate in your business to indicate their likelihood of becoming a customer. The ultimate goal of lead scoring is to empower your sales and marketing teams to drive better customer experiences, improve personalization, and generate more revenue.

In most scenarios, outbound sales teams generate leads, inbound marketing teams, or referrals from partners. Either way, there are really only three types of data that affect lead scoring; behavioral data, demographic data, and historical data.

• Behavioral data includes all of the unique events and product usage data collected directly through your website, app, or even your marketing platform (e.g. pages viewed, emails opened, product selected, item added to cart, workspace created, messages sent, last login date, playlists, etc.)

• Demographic data includes everything around your customer (e.g. job title, first name, last name, email, industry, revenue, country, zip code, department, etc.)

• Historical data includes everything that led up to your lead converting to a paying customer (e.g. first meeting date, number of phone calls, emails sent, number of demos, POC date, etc.) Historical data can also come from the previous two data types.

Lead scoring analyzes all of these various data points to give your current leads a score on how closely they align with your ideal customer profile based on the actions they’ve taken and your previous customers’ actions.

How to Calculate a Lead Score

Unfortunately, there’s no single lead scoring system that you can implement immediately. You’ll probably need to identify a standard benchmark before you can start qualifying your leads. The easiest way to do this is by calculating your lead-to-customer conversion rate. Luckily for you, this formula is simply dividing your total number of new customers by your total number of leads.

If you generated 10 leads in the last month and acquired one new customer, your lead-to-customer conversion rate would be 10%. Conversion rates differ drastically from company to company, but the average tends to sit anywhere between two and five percent.

Once you’ve defined this metric, you can create your scoring system. To do this, you’ll need to identify the key attributes that you want to measure. For example, you might assign 5 points to leads who fall under a specific industry, another 3 points to leads who have viewed your product page more than two times, or even 10 points to leads who’ve exceeded product usage on your freemium product.

You can also apply negative points to filter out leads. For example, you might want to filter out leads from specific domains (e.g. competitors). You could even remove 5 points to users who’ve unsubscribed from your marketing emails or deduct another 5 points to leads who haven’t visited your website or opened a marketing email in the last 30 days.

Depending on your needs, the attributes that you measure will vary substantially. The idea with lead scoring is to create a grading system based on the criteria you define. The larger your criteria pool, the more accurate your lead score will be.

It really doesn’t matter how you categorize your leads. You just need to ensure you have a ranking system, whether from A-F, 0-10, or 0-100.

Most customer relationship management (CRM) platforms like Salesforce and Hubspot have an out-of-the-box lead scoring functionality but it’s often based on a cookie-cutter model that doesn’t fit the requirements of your business. To make matters worse your CRM only houses a subset of your customer data and it doesn’t have access to your key behavioral data.

The good news is that your data warehouse already has all your customer data, which means all you have to do is define your lead scoring model using SQL.

Why Does Lead Scoring Matter?

Lead scoring is important for several reasons. Firstly, it ensures that your sales team prioritizes the right leads in real-time. Without a lead scoring model, it’s challenging to target high-value leads and identify the common trends between them.

If you’re like most companies your sales team is probably broken into two pillars, sales development representatives (SDRs) and account executives (AEs). Most likely you want your AE’s focused on closing deals and your SDRs focused on generating new leads. With a lead score in place, your SDRs can take action to qualify your leads and your AEs can focus their efforts on leads that are ready to buy.

Lead scoring also has implications for marketing. Let’s say you have several leads who have shown interest in your product but have not taken action to indicate that they are ready to purchase. With lead scoring, your marketing team can build segments for different marketing campaigns (e.g. nurture campaigns and drip campaigns) to try and further qualify them for sales.

What Are the Impacts of Lead Scoring?

Lead scoring can have several benefits, but usually, it improves efficiency across business teams because it can help you identify exactly which efforts yield the most conversions. For example, if you’ve identified that leads who book a demo are twice as likely to close, you could offer gift cards to all leads who sit through an initial demo with you. Doing this would most likely increase the number of meetings your SDR team can book.

This is not just theoretical. By syncing a lead scoring model to Hubspot, Gorgias is able to better supercharge demand generation by matching leads and accounts with different plans and business tiers. The outbound team can enroll contacts in customized email sequences in real-time, increasing net new customers by 60%, nearly doubling quarterly revenue, and growing the outbound pipeline by 60-70%.

Calculating Your Lead Score Using SQL

Calculating your lead scoring model using SQL is relatively straightforward.

The first step is to define all the events that a lead could do (ex: download a whitepaper, attend an event, etc) that signal intent. To do that, you’ll likely need to join your sales data (likely in your CRM like Salesforce or HubSpot) with your website/product analytics data (ex: Google Analytics, Segment, Amplitude, or Mixpanel events). Your data warehouse is the best place to join all that data together.

For example, here’s a subquery we use at Hightouch to pull all “marketing qualified events” that show intent. We first pull all relevant leads and intentionally exclude leads/signups from low-intent domains. Then we join those leads with event data.

with leads as (
  select 
    email, 
    min(created_date) as created_at 
  from 
    leads 
  where 
    lead_source in (‘relevant lead sources’) 
    and email not like '%hightouch.com' 
  group by 
    1
), 
mqe_events as (
  select 
    distinct event_id, 
    anonymous_id, 
    email, 
    event_time, 
    event_type, 
    event_index 
  from 
    events 
  where 
    event_type in (‘List of events we care about’)
), 
priority_events_of_leads as (
  select 
    mqe_events.* 
  from 
    mqe_events 
    join leads using (email)
),

Finally, we can use the COUNT() function to count how many unique events each lead has to give a basic lead score.

select 
  email, 
  count(*) as lead_score 
from 
  priority_events_of_leads 
group by 
  1

That’s it! You now have a basic lead score based on how many high intent actions a lead has done.

If we wanted to get fancier, we could assign “points” to each action/event based on intent (ex: booking a demo is much higher intent than just reading a blog post).

with lead_score_inputs as (
  select 
    id, 
    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 visited pricing page
    case when viewed_pricing = TRUE then 1 else 0 end as pricing_page_score, 
    -- creating score for inviting other members to join
    case when invited_other_users = TRUE then 1 else 0 end as member_invitation_score, 
    -- creating score for daily activity
    case when daily_active = TRUE then 1 else 0 end as activity_score, 
  from 
    fct_users
) 
select 
  (
    email_score + pricing_page_score + member_invitation_score + activity_score
  ) as lead_score, 
  id, 
  first_name, 
  last_name, 
  email 
from 
  lead_score_inputs 
order by 
  1 desc;

How to Implement Lead Scoring

Once you’ve compiled your lead scoring model in SQL, the next logical step is to make that data available in your downstream business tools (e.g. Hubspot, Salesforce, Marketo, Braze, Iterable, etc.) so that business teams can prioritize high-value leads. Making data available in downstream business tolls is exactly the problem Hightouch solves.

Hightouch queries directly against your data warehouse and syncs data to 100+ different destinations – no more custom scripts, API integrations, or CSVs. You simply define your data using SQL and map the columns in your warehouse to the appropriate fields in your destinations. You can sign up for a free Hightouch Workspace today!