Lead funnel daily

1-- core.salesforce.lead_funnel_daily
2-- Leads created and converted per day per lead source, with the conversion of
3-- each day's new leads followed through to a won opportunity.
4-- Grain: date_day x salesforce_org x lead_source.
5-- Depends on: staging.salesforce.lead, core.salesforce.opportunity_pipeline
6--
7-- TWO VIEWS OF CONVERSION, both on this row:
8--   leads_converted   -> leads whose converted_date is this day, whenever they
9--                        were created. Activity view: "how many did we convert
10--                        today".
11--   cohort_*          -> of the leads CREATED this day, how many have converted
12--                        so far, how long it took, and how many became a won
13--                        opportunity. Cohort view: "how good were these leads".
14--                        Recent cohorts keep rising as their leads convert.
15--
16-- ROLLING UP. Sum the parts and divide once: conversion_rate is
17-- cohort_converted / leads_created, average_days_to_convert is
18-- cohort_days_to_convert / cohort_converted.
19--
20-- lead_source NULL is reported as '(none)', so it stays visible in a chart and
21-- joins on the spine. Lead conversion into an existing opportunity is possible,
22-- so two leads can point at the same opportunity; each lead is still counted once.
23
24WITH leads AS (
25    SELECT
26        l.salesforce_org,
27        COALESCE(l.lead_source, '(none)')                   AS lead_source,
28        DATE(l.created_at)                                  AS created_date,
29        l.is_converted,
30        l.converted_date,
31        l.converted_opportunity_id,
32        DATE_DIFF(l.converted_date, DATE(l.created_at), DAY) AS days_to_convert,
33        COALESCE(p.is_won, FALSE)                           AS converted_opportunity_won
34    FROM {{staging.salesforce.lead}} l
35    -- opportunity_pipeline is one row per opportunity, so this cannot fan out.
36    LEFT JOIN {{core.salesforce.opportunity_pipeline}} p
37           ON p.salesforce_org = l.salesforce_org
38          AND p.opportunity_id = l.converted_opportunity_id
39),
40