Deal flow by month

1-- core.hubspot_v2.deal_flow_by_month
2-- Deals created, won and lost per month, per pipeline and owner, with win rate.
3-- Grain: month x pipeline x owner. Money: the portal's home currency.
4-- Depends on: core.hubspot_v2.deal_pipeline
5--
6-- Recreates HubSpot's "Deals created" and "Deals won/lost" reports by month.
7--
8-- WHICH DATE EACH EVENT LANDS ON:
9--   created     -> created_date.
10--   won / lost  -> close_date. HubSpot sets the close date to the day a deal moves
11--                  into a closed stage, so for closed deals this is the real close
12--                  day. A portal can turn that automation off in its pipeline
13--                  settings; if yours has, close_date is whatever the rep typed.
14--   forecast    -> close_date of open deals: what reps expect to close that month.
15--
16-- Built on a month spine, so an owner with a quiet month returns a row of zeros
17-- rather than vanishing and letting a line chart draw straight over the gap.
18
19WITH month_spine AS (
20    SELECT month
21    FROM UNNEST(GENERATE_DATE_ARRAY(
22        (SELECT DATE_TRUNC(MIN(created_date), MONTH) FROM {{core.hubspot_v2.deal_pipeline}}),
23        -- A year ahead, because open deals forecast into the future.
24        DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 YEAR), MONTH),
25        INTERVAL 1 MONTH
26    )) AS month
27),
28
29-- Owners and pipelines that actually carry deals. owner_id is coalesced to a
30-- placeholder so unassigned deals keep a row of their own instead of dropping out of
31-- the equality joins below.
32keys AS (
33    SELECT DISTINCT
34        hubspot_portal,
35        pipeline_id,
36        pipeline_label,
37        COALESCE(owner_id, 'unassigned') AS owner_key,
38        owner_id,
39        owner_name
40    FROM {{core.hubspot_v2.deal_pipeline}}