Offline Matching Queries

These are primarily used to investigate low Offline Matching.

For additional steps to investigate Offline Matching, see: Offline Attribution QA Issues 

-- MATCHED SALES W LEADS
SELECT sales.sales_raw.new_or_used, sales.sales_raw.sale_type, sales.sales_standardized.*, sales.matched_cdc.*, sales.leads_standardized.*, sales.leads_raw.raw_source, sales.leads_raw.raw_source_detail
    FROM sales.sales_standardized
    JOIN sales.sales_raw ON sales.sales_standardized.raw_sale_id = sales_raw.raw_sale_id
    JOIN sales.matched_cdc ON sales.sales_standardized.sale_id = matched_cdc.sale_id
    JOIN sales.leads_standardized ON sales.leads_standardized.lead_id = matched_cdc.lead_id
    JOIN sales.leads_raw ON leads_standardized.raw_lead_id = leads_raw.raw_lead_id
WHERE   sales_standardized.property_id = ${propid}
AND   sales_standardized.sales_date >= '${startdate YYYY-MM-DD}'
AND   sales_standardized.sales_date <  (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') + interval '1 month')
AND   NOT(leads_standardized.lead_type = 'CRM Lead' and leads_raw.raw_source is null AND leads_raw.raw_source_detail is null)     -- exclude CRM Leads where lead raw_source and raw_source_detail are both blank
AND   NOT(leads_standardized.lead_type = 'Chat' and leads_raw.raw_source is null AND leads_raw.raw_source_detail is null)     -- exclude Chat leads where lead raw_source and raw_source_detail are both blank
AND   NOT(leads_standardized.lead_type = 'Phone' and leads_standardized.campaign_name is null AND leads_standardized.campaign_phone_number is null  AND leads_standardized.campaign_id is null)     -- exclude Phone leads where all campaign fields are blank
order by sales_standardized.first_name, sales_standardized.last_name;
-- These sales matched leads, but may not count towards Offline Matching IF:
--   - raw_source and raw_source_detail combination match an internal classification
--  You will need to compare these manually.


-- UNMATCHED SALES
SELECT sales.sales_raw.new_or_used as "sales_raw.new_or_used", sales.sales_raw.sale_type as "sales_raw.sale_type", sales.sales_standardized.*
FROM   sales.sales_standardized
JOIN   sales.sales_raw on sales_standardized.raw_sale_id = sales_raw.raw_sale_id
WHERE  NOT EXISTS (
   SELECT  -- SELECT list mostly irrelevant; can just be empty in Postgres
   FROM   sales.matched_cdc
   WHERE  matched_cdc.sale_id = sales_standardized.sale_id
   )
AND   sales_standardized.property_id = ${propid}
AND   sales_standardized.sales_date >= '${startdate YYYY-MM-DD}'
AND   sales_standardized.sales_date <  (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') + interval '1 month')
order by sales_standardized.first_name, sales_standardized.last_name;
-- May not include Sales where all matched Leads meet these criteria:
--             - CRM Leads with blank raw_source and raw_source_detail values
--             - Chat with blank raw_source and raw_source_detail values
--             - Phone leads with blank campaign


-- UNMATCHED LEADS NEW
SELECT sales.leads_raw.raw_source, sales.leads_raw.raw_source_detail, sales.leads_standardized.*
FROM   sales.leads_standardized
JOIN   sales.leads_raw on leads_standardized.raw_lead_id = leads_raw.raw_lead_id
WHERE (NOT EXISTS(
        SELECT -- SELECT list mostly irrelevant; can just be empty in Postgres
        FROM sales.matched_cdc
        WHERE matched_cdc.lead_id = leads_standardized.lead_id
                 )
    OR (leads_standardized.lead_type = 'CRM Lead' AND leads_raw.raw_source is null AND
        leads_raw.raw_source_detail is null) -- include CRM Leads w/ blank raw_source and raw_source_detail
    OR (leads_standardized.lead_type = 'Chat' and leads_raw.raw_source is null AND
        leads_raw.raw_source_detail is null) -- include Chat leads where lead raw_source and raw_source_detail are both blank
    OR (leads_standardized.lead_type = 'Phone' and leads_standardized.campaign_name is null AND
        leads_standardized.campaign_phone_number is null AND
        leads_standardized.campaign_id is null) -- include Phone leads where all campaign fields are blank
       )
AND   leads_standardized.property_id = ${propid}
AND   leads_standardized.start_time >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') - interval '2 month')    -- should be a 3 month date range as we match 3 months back
AND   leads_standardized.start_time <  (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') + interval '1 month')
order by leads_raw.raw_source, leads_raw.raw_source_detail;

-- ====================================================================================

-- INCOMPLETE LEAD DATA
SELECT sales.leads_raw.raw_source, sales.leads_raw.raw_source_detail, sales.leads_standardized.*
FROM   sales.leads_standardized
JOIN   sales.leads_raw on leads_standardized.raw_lead_id = leads_raw.raw_lead_id
WHERE (
     (leads_standardized.lead_type = 'CRM Lead' AND leads_raw.raw_source is null AND
        leads_raw.raw_source_detail is null) --  CRM Leads w/ blank raw_source and raw_source_detail
    OR (leads_standardized.lead_type = 'Chat' and leads_raw.raw_source is null AND
        leads_raw.raw_source_detail is null) --  Chat leads where lead raw_source and raw_source_detail are both blank
    OR (leads_standardized.lead_type = 'Phone' and leads_standardized.campaign_name is null AND
        leads_standardized.campaign_phone_number is null AND
        leads_standardized.campaign_id is null) --  Phone leads where all campaign fields are blank
    )
AND   leads_standardized.property_id = ${propid}
AND   leads_standardized.start_time >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') - interval '2 month')    -- should be a 3 month date range as we match 3 months back
AND   leads_standardized.start_time <  (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') + interval '1 month')
order by leads_raw.raw_source, leads_raw.raw_source_detail;


-- FILTERED LEADS COUNTS BY REASON
SELECT sales.leads_filtered.reason, count(*) FROM sales.leads_raw
         JOIN sales.leads_filtered ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
WHERE  sales.leads_raw.property_id = ${propid}
AND   leads_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') - interval '2 month')    -- created in the past 2 months (gives us the last 3 months of leads)
GROUP BY leads_filtered.reason;

-- FILTERED LEADS COUNTS BY REASON
SELECT sales.leads_filtered.reason, sales.leads_raw.* FROM sales.leads_raw
         JOIN sales.leads_filtered ON sales.leads_raw.raw_lead_id = sales.leads_filtered.raw_lead_id
WHERE  sales.leads_raw.property_id = ${propid}
AND   leads_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') - interval '2 month');    -- created in the past 2 months (gives us the last 3 months of leads)


-- FILTERED SALES COUNTS BY REASON for DV or CDK
SELECT sales.sales_filtered.reason, count(*) FROM sales.sales_raw
         JOIN sales.sales_filtered ON sales.sales_raw.raw_sale_id = sales.sales_filtered.raw_sale_id
WHERE  sales.sales_raw.property_id = ${propid}
AND   sales_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD'))    -- created in the sales month
GROUP BY sales_filtered.reason;

-- FILTERED SALES (DETAIL) BY REASON for DV or CDK
SELECT DISTINCT ON (sales.sales_raw.vin)       -- dedupe based on VIN
  sales.sales_filtered.reason, sales.sales_raw.* FROM sales.sales_raw
  JOIN sales.sales_filtered ON sales.sales_raw.raw_sale_id = sales.sales_filtered.raw_sale_id
WHERE  sales.sales_raw.property_id = ${propid}
AND   sales_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD'))    -- created in the sales month
;


-- FILTERED SALES COUNTS BY REASON for COLLECT SALES FILES
SELECT sales.sales_filtered.reason, count(*) FROM sales.sales_raw
         JOIN sales.sales_filtered ON sales.sales_raw.raw_sale_id = sales.sales_filtered.raw_sale_id
WHERE  sales.sales_raw.property_id = ${propid}
AND   sales_raw.created_ts >= (to_date('${startdate YYYY-MM-DD}','YYYY-MM-DD') + interval '1 month')    -- created in the month after the sales month (when we would have loaded the sales file)
GROUP BY sales_filtered.reason;