Pipeline flow daily

1-- core.salesforce.pipeline_flow_daily
2-- Opportunities created, won and lost, and open pipeline due, per day per owner.
3-- Grain: date_day x salesforce_org x owner_id x currency_code.
4-- Depends on: core.salesforce.opportunity_pipeline
5--
6-- WHICH DATE EACH COUNT LANDS ON:
7--   created         -> created_date, a system timestamp (UTC day).
8--   won / lost      -> close_date. Salesforce does not set it on close, so it is
9--                      the date the rep left in the field. Most orgs require it to
10--                      be today or earlier on close; check yours.
11--   open, due       -> close_date of open opportunities: the expected close date.
12--
13-- ROLLING UP. Every ratio here has its parts alongside it. For a month, a team or
14-- a quarter, sum the parts and divide once: win_rate is opportunities_won /
15-- (opportunities_won + opportunities_lost), average_sales_cycle_days is
16-- won_cycle_days / opportunities_won. Averaging the daily ratios weights a quiet
17-- day the same as a busy one.
18--
19-- Built on a spine, so an owner with a quiet day returns zeros instead of a gap.
20-- The spine stops two years ahead: open opportunities expected to close later
21-- than that are left out of open_amount_due (they stay in opportunity_pipeline).
22
23WITH bounds AS (
24    SELECT
25        -- A close date can be backdated before any creation date.
26        LEAST(MIN(created_date), COALESCE(MIN(close_date), MIN(created_date))) AS first_day,
27        LEAST(
28            GREATEST(MAX(close_date), CURRENT_DATE()),
29            DATE_ADD(CURRENT_DATE(), INTERVAL 2 YEAR)
30        )                                                        AS last_day
31    FROM {{core.salesforce.opportunity_pipeline}}
32),
33
34day_spine AS (
35    SELECT date_day
36    FROM bounds, UNNEST(GENERATE_DATE_ARRAY(first_day, last_day)) AS date_day
37),
38
39owners AS (
40    -- Only people who own at least one opportunity, so admin and integration