Skip to content

Instantly share code, notes, and snippets.

SELECT
Questions.CreationDate as "Q.Date",
Questions.Score as "Q.Score",
Questions.ViewCount as "Q.Views",
Questions.Title as "Q.Title",
Questions.Body as "Q.Body",
Questions.Tags as "Q.Tags",
Questions.AnswerCount as "Q.AnswerCount",
Users.Reputation as "U.Rep",
Users.Location as "U.Loc",
@henryhund
henryhund / lead_scoring_complete_query.sql
Last active July 2, 2016 16:32
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
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
WITH
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 /*
/*
Include Part 1
*/
ae_oppys_won AS
( SELECT DISTINCT oppy_id,
oppy_date,
is_closed,
is_won,
account_id,
oppy_amount
/*
Include Parts 1 and 2
*/
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
/*
Include Parts 1, 2 and 3
*/
mrr_per_activity AS
( SELECT total_mrr_per_activity.visit_type,
total_mrr_per_activity.visit_details,
total_mrr,
total_count AS total_visits,
round(total_mrr/total_count, 2) AS mrr_per_visit
FROM total_mrr_per_activity
/*
This is the final part of this query, which gives us leads ordered by aggregate score!
Include Parts 1, 2, 3 and 4
*/
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
@henryhund
henryhund / Aptible Gridiron GDPR Slack Community Code of Conduct.md
Last active May 29, 2018 14:01
This is a code of conduct by which we govern the Aptible Gridiron GDPR Slack Community.

Join the Aptible Gridiron GDPR Slack Community here.

Aptible Gridiron GDPR Slack Code of Conduct

1. Purpose

A primary goal of Aptible Gridiron GDPR Slack is to be inclusive to the largest number of contributors, with the most varied and diverse backgrounds possible. As such, we are committed to providing a friendly, safe and welcoming environment for all, regardless of gender, sexual orientation, ability, ethnicity, socioeconomic status, and religion (or lack thereof).

This code of conduct outlines our expectations for all those who participate in our community, as well as the consequences for unacceptable behavior.