Skip to main content
Log inGet a demo

How to Calculate a Lead Score in SQL

Learn how you can calculate a lead score in SQL.

Luke Kline.

Luke Kline

September 2, 2022

8 minutes

How to Calculate a Lead Score in SQL.
  • 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!

    More on the blog

    • What is Reverse ETL? The Definitive Guide .
  • Friends Don’t Let Friends Buy a CDP.
  • Snowflake

    Marketplace Partner of the Year

    Gartner

    Cool Vendor in Marketing Data & Analytics

    Fivetran

    Ecosystem Partner of the Year

    G2

    Best Estimated ROI

    Snowflake

    One to Watch for Activation & Measurement

    G2

    CDP Category Leader

    G2

    Easiest Setup & Fastest Implementation

    Activate your data in less than 5 minutes