henryhund

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

 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

henryhund

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 /*

henryhund

/*
Include Part 1
*/
   ae_oppys_won AS
  ( SELECT DISTINCT oppy_id,
                    oppy_date,
                    is_closed,
                    is_won,
                    account_id,
                    oppy_amount

henryhund

/*
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

henryhund

/*
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

henryhund

/*
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

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.