Opportunity outcomes monthly

1-- core.go_high.opportunity_outcomes_monthly
2-- Win rate and won value by the month deals were created, per pipeline and lead source:
3-- the cohort view behind HighLevel's opportunity and lead source reports.
4--
5-- Grain: one row per month created x location x pipeline x source.
6-- Depends on: staging.go_high.opportunity, .pipeline
7--
8-- Cohorted on created month, so a deal created in March and won in May counts as a March
9-- win. That answers "how well did March's leads convert", and recent months keep
10-- improving as their open deals close.
11-- `win_rate` is won over closed (won + lost); abandoned deals are excluded from it and
12-- counted separately. `days_to_win` uses the last status change as the close time.
13
14SELECT
15    DATE_TRUNC(DATE(o.created_at), MONTH)                       AS created_month,
16    o.location_id,
17    o.pipeline_id,
18    p.pipeline_name,
19    COALESCE(o.source, '(none)')                                AS source,
20
21    COUNT(*)                                                    AS opportunities,
22    COUNTIF(o.status = 'open')                                  AS open_opportunities,
23    COUNTIF(o.status = 'won')                                   AS won_opportunities,
24    COUNTIF(o.status = 'lost')                                  AS lost_opportunities,
25    COUNTIF(o.status = 'abandoned')                             AS abandoned_opportunities,
26    SAFE_DIVIDE(COUNTIF(o.status = 'won'),
27                COUNTIF(o.status IN ('won', 'lost')))           AS win_rate,
28
29    SUM(o.monetary_value)                                       AS total_value,
30    SUM(IF(o.status = 'won', o.monetary_value, 0))              AS won_value,
31    SUM(IF(o.status = 'open', o.monetary_value, 0))             AS open_value,
32    AVG(IF(o.status = 'won',
33        TIMESTAMP_DIFF(o.last_status_change_at, o.created_at, HOUR) / 24, NULL)) AS avg_days_to_win
34FROM {{staging.go_high.opportunity}} o
35LEFT JOIN {{staging.go_high.pipeline}} p ON p.pipeline_id = o.pipeline_id
36WHERE o.created_at IS NOT NULL
37GROUP BY 1, 2, 3, 4, 5