This query accompanies a blog post about marketing and sales alignment and engagement lead scoring. You can find this blog post here: https://medium.com/@henryhund/traditional-lead-scoring-methods-are-expensive-and-flawed-you-can-do-better-here-s-how-584eb01f9570

lead_scoring_complete_query.sql

 WITH 
    /*
    Find every visit
    */ 
    all_prospects_visits AS
  ( SELECT DISTINCT prospects.id AS prospect_id,
                    activity.created_at AS visit_date,
                    details AS visit_details,
                    activity.type AS visit_type,
                    activity.created_at created_at
   FROM pardot.prospect prospects
   INNER JOIN pardot.visitoractivity activity ON prospects.id = activity.prospect_id
   WHERE /*
                Limit to views, form submissions, and video (>=75% watched) i.e.,
                activity types 2, 4 and 34.
                See http://developer.pardot.com/kb/api-version-3/object-field-references/#visitor-activity-types
                for a list of all activity types
            */ activity.type IN (2,
                                 34,
                                 4) -- exclude form views

     AND (activity.type_name NOT IN ('Form')
          OR activity.type IN (4,
                               34))
     AND visit_details IS NOT NULL ),
    /*
    Find count of activity. In Pardot, activiities have different types (submissions and views). We are looking at each distinctly.
    */
    count_activities AS
  ( SELECT visit_details,
           visit_type,
           count(*) AS total_count
   FROM all_prospects_visits
   GROUP BY visit_details,
            visit_type ),
    /*
    Find all closed won opportunities that have an amount (MRR) specified.
    */
    ae_oppys_won AS
  ( SELECT DISTINCT oppy_id,
                    oppy_date,
                    is_closed,
                    is_won,
                    account_id,
                    oppy_amount
   FROM
     ( SELECT DISTINCT oppy.stagename AS oppy_stage,
                       oppy.id AS oppy_id,
                       oppy.createddate AS oppy_date,
                       oppy.isclosed AS is_closed,
                       oppy.iswon AS is_won,
                       account.id AS account_id,
                       oppy.amount::float AS oppy_amount
      FROM salesforce.account account
      INNER JOIN salesforce.opportunity oppy ON account.id = oppy.accountid
      WHERE is_closed = 1
        AND is_won = 1
        AND oppy.amount > 0 ) ),
    /*
    Find activities associated with closed won opportunities.
    */
    ae_oppys_won_visits AS
  ( SELECT DISTINCT prospects.id AS prospect_id,
                    account_id,
                    activity.created_at AS visit_date,
                    visit_details AS visit_details,
                    activity.visit_type AS visit_type,
                    oppy_amount AS amount,
                    oppy_id
   FROM ae_oppys_won
   INNER JOIN salesforce.contact contacts ON ae_oppys_won.account_id = contacts.accountid
   INNER JOIN pardot.prospect prospects ON contacts.id = prospects.crm_contact_fid
   INNER JOIN all_prospects_visits activity ON prospects.id = activity.prospect_id
   WHERE -- make sure the visit happend during the 3 months leading up to the oppy
 visit_date > dateadd(MONTH,-3, oppy_date)
     AND visit_date < oppy_date ),
    /*
    Find value associated with each activity that we found to be related to a closed won opportunity.
    This is assuming even attribution for each visit. Other methods could be weighting first or last touch more, or doing some sort of time-based depreciation.
    */
    ae_oppy_closed_won_activity_values AS
  ( SELECT ae_oppys_won_visits.oppy_id AS oppy_id, visit_type, visit_details, COUNT, amount,
                                                                                     round(amount/COUNT,2) AS mrr_per_visit
   FROM ae_oppys_won_visits
   INNER JOIN
     ( SELECT COUNT(*) AS COUNT,
                   oppy_id
      FROM ae_oppys_won_visits
      GROUP BY oppy_id ) counts ON counts.oppy_id = ae_oppys_won_visits.oppy_id ),
    /*
    Find total revenue generated by each activity, ever.
    */
    total_mrr_per_activity AS
  ( SELECT visit_type,
           visit_details,
           sum(mrr_per_visit) AS total_mrr
   FROM ae_oppy_closed_won_activity_values
   GROUP BY visit_type,
            visit_details ),
    /*
    Find revenue generated for each prospect engaging in this activity.
    This is a proxy for revenue per touch. In other words, each time someone does this activity, we should expect this amount of dollars in closed won opportunities later.
    */
    mrr_per_activity AS
  ( SELECT total_mrr_per_activity.visit_type AS visit_type,
           total_mrr_per_activity.visit_details AS visit_details,
           total_mrr,
           total_count AS total_visits,
           round(total_mrr/total_count, 2) AS mrr_per_visit
   FROM total_mrr_per_activity
   LEFT JOIN count_activities ON total_mrr_per_activity.visit_details = count_activities.visit_details
   AND total_mrr_per_activity.visit_type = count_activities.visit_type
   ORDER BY mrr_per_visit DESC )
   
SELECT sum(mrr_per_visit) as lead_score,
       prospect_id
FROM all_prospects_visits 
JOIN mrr_per_activity
ON all_prospects_visits.visit_type = mrr_per_activity.visit_type
AND all_prospects_visits.visit_details = mrr_per_activity.visit_details
GROUP BY prospect_id
ORDER BY lead_score DESC