Company 360

1-- core.hubspot_v2.company_360
2-- One row per company: firmographics, owner, contacts, open and won pipeline, and
3-- last touch. Grain: one row per company. Money: the portal's home currency.
4-- Depends on: staging.hubspot_v2.company, .contact, .owner,
5--             core.hubspot_v2.deal_pipeline
6--
7-- Deals are counted against the ONE company deal_pipeline assigns them to (the
8-- primary association), so summing any deal column over all companies gives the
9-- same total as summing deal_pipeline. Deals with no associated company appear in
10-- no row here; deal_pipeline has them with company_id NULL.
11--
12-- Contacts are counted by HubSpot's primary company property on the contact, for
13-- the same reason.
14
15WITH deals AS (
16    SELECT
17        hubspot_portal,
18        company_id,
19        COUNT(*)                              AS deals_total,
20        COUNTIF(is_open)                      AS deals_open,
21        COUNTIF(is_won)                       AS deals_won,
22        COUNTIF(is_lost)                      AS deals_lost,
23        SUM(open_amount)                      AS open_amount,
24        SUM(weighted_open_amount)             AS weighted_open_amount,
25        SUM(won_amount)                       AS won_amount,
26        MIN(CASE WHEN is_won THEN close_date END) AS first_won_date,
27        MAX(CASE WHEN is_won THEN close_date END) AS last_won_date,
28        MAX(created_date)                     AS last_deal_created_date
29    FROM {{core.hubspot_v2.deal_pipeline}}
30    WHERE company_id IS NOT NULL
31    GROUP BY 1, 2
32),
33
34contacts AS (
35    SELECT
36        hubspot_portal,
37        associated_company_id AS company_id,
38        COUNT(*)              AS contacts,
39        MAX(last_contacted_at) AS last_contact_contacted_at
40    FROM {{staging.hubspot_v2.contact}}