Deal activity

1-- core.hubspot_v2.deal_activity
2-- Activities logged against each deal: counts by type, connected calls,
3-- completed meetings, and the first and latest activity.
4-- Grain: one row per deal with at least one logged activity. Join to
5-- core.hubspot_v2.deal_pipeline on (hubspot_portal, deal_id); a deal with no
6-- row here has nothing logged against it.
7-- Depends on: staging.hubspot_v2.engagement_deal, .engagement, .deal
8--
9-- ATTRIBUTION. An activity counts on every deal its association list names, so
10-- a call logged against two deals counts on both. The columns are right per
11-- deal and too high when summed over deals. For activity totals use
12-- rep_activity, which counts each activity once.
13--
14-- Only activities that have already happened count towards
15-- last_logged_activity_at, so a meeting booked for next week does not make a
16-- deal look freshly worked.
17--
18-- Deleted (archived) deals are dropped, so this covers the same deals as
19-- deal_pipeline. This model needs the engagement stream; deal_pipeline does not.
20
21WITH linked AS (
22    SELECT
23        ed.hubspot_portal,
24        ed.deal_id,
25        ed.engagement_id,
26        e.engagement_type,
27        e.status,
28        e.meeting_outcome,
29        e.occurred_at
30    FROM {{staging.hubspot_v2.engagement_deal}} ed
31    JOIN {{staging.hubspot_v2.engagement}} e
32      ON e.hubspot_portal = ed.hubspot_portal
33     AND e.engagement_id  = ed.engagement_id
34    -- Only deals that still exist.
35    JOIN {{staging.hubspot_v2.deal}} d
36      ON d.hubspot_portal = ed.hubspot_portal
37     AND d.deal_id        = ed.deal_id
38)
39
40SELECT