Deal pipeline

1-- core.hubspot_v2.deal_pipeline
2-- Every deal with its pipeline, stage, outcome, owner and primary company.
3-- Grain: one row per deal. Money: the portal's home currency (amount_home_currency).
4-- Depends on: staging.hubspot_v2.deal, .pipeline, .pipeline_stage, .owner,
5--             .deal_association, .company, .account_details
6--
7-- This is the deal-grain model the other reports read. It recreates the numbers
8-- behind HubSpot's deal board and "Deal forecast" report: open pipeline, weighted
9-- pipeline, closed won and closed lost.
10--
11-- OUTCOME comes from HubSpot's own hs_is_closed / hs_is_closed_won flags, which
12-- follow the closed-won / closed-lost setting of each pipeline stage. No stage names
13-- are pattern-matched, so custom stages are classified the way HubSpot classifies
14-- them.
15--
16-- ONE COMPANY PER DEAL. A deal can be associated with several companies. Picking
17-- one keeps this model at deal grain and stops a deal's amount being counted once per
18-- company in company_360. The primary association (HubSpot's association type 5,
19-- deal to company) wins; a deal with no primary company falls back to the lowest
20-- associated company id, so the choice is stable between runs.
21--
22-- ACTIVITY. num_activities and num_times_contacted are HubSpot's own roll-ups
23-- on the deal. Counts of the activities logged against each deal live in
24-- core.hubspot_v2.deal_activity, so this model builds without the engagement
25-- stream.
26--
27-- CURRENT STATE. This is where every deal stands now. For how deals moved through
28-- the stages, use core.hubspot_v2.deal_stage_history.
29
30WITH portal AS (
31    SELECT hubspot_portal, time_zone, home_currency
32    FROM {{staging.hubspot_v2.account_details}}
33),
34
35deal_company AS (
36    SELECT
37        hubspot_portal,
38        deal_id,
39        to_id AS company_id
40    FROM {{staging.hubspot_v2.deal_association}}