Compare Actual Mail Plan to Forecasted Mail Plan

compare_mail_plan.sql

DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';

WITH _sources AS
(
  SELECT
     CASE
       WHEN source LIKE 'MB[QL]%'
       THEN LEFT(source, 7)
       ELSE LEFT(source, 9)
     END                               AS label
    ,technique
    ,COALESCE(mailed_on, '1999-12-31') AS dropped_on
    ,solicitations_count_source
  FROM usga_stage.dimensions.sources
  WHERE initiative_month >= '2014-08-01'
    AND source LIKE 'MB[^NU]%'
    AND solicitations_count_source > 0
)
,_actuals AS
(
  SELECT
     label
    ,technique
    ,dropped_on
    ,SUM(solicitations_count_source) AS sols_count
  FROM _sources
  GROUP BY
     label
    ,technique
    ,dropped_on
)
,_forecast AS
(
  SELECT
     label
    ,technique
    ,mail_plan_event_on           AS dropped_on
    ,SUM(simulated_targets_count) AS sols_count
  FROM usga_apps.seer.simulator_log
  WHERE forecast_snapshot_id = @forecast_snapshot_id
    AND technique IN ('MM', 'MW')
    AND simulated_targets_count > 0
  GROUP BY
     label
    ,technique
    ,mail_plan_event_on
)
,_union AS
(
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _actuals
  UNION
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _forecast
)
SELECT
   u.label
  ,u.technique
  ,u.dropped_on
  ,a.sols_count AS actuals_sols_count
  ,f.sols_count AS forecast_sols_count
FROM _union u
LEFT OUTER JOIN _actuals  a ON a.label = u.label AND a.technique = u.technique AND a.dropped_on = u.dropped_on
LEFT OUTER JOIN _forecast f ON f.label = u.label AND f.technique = u.technique AND f.dropped_on = u.dropped_on
ORDER BY
   label
  ,technique
  ,dropped_on
;

Updated with more data columns:

DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';

WITH _sources AS
(
  SELECT
     CASE
       WHEN source LIKE 'MB[QL]%'
       THEN LEFT(source, 7)
       ELSE LEFT(source, 9)
     END                               AS label
    ,technique
    ,COALESCE(mailed_on, '1999-12-31') AS dropped_on
    ,solicitations_count_source
        ,adjusted_payments_count
        ,payments_count_source
  FROM usga_stage.dimensions.sources
  WHERE initiative_month >= '2014-08-01'
    AND source LIKE 'MB[^NU]%'
    AND solicitations_count_source > 0
)
,_actuals AS
(
  SELECT
     label
    ,technique
    ,dropped_on
        ,SUM(adjusted_payments_count) AS adjusted_payments_count
        ,SUM(payments_count_source)   AS payments_count_source
    ,SUM(solicitations_count_source)                                                            AS sols_count
        ,SUM(adjusted_payments_count) / CAST(SUM(solicitations_count_source) AS FLOAT) AS adjusted_response_rate
        ,SUM(payments_count_source) / CAST(SUM(solicitations_count_source) AS FLOAT)   AS response_rate_source
  FROM _sources
  GROUP BY
     label
    ,technique
    ,dropped_on
)
,_forecast AS
(
  SELECT
     label
    ,technique
    ,mail_plan_event_on           AS dropped_on
    ,SUM(simulated_targets_count) AS sols_count
  FROM usga_apps.seer.simulator_log
  WHERE forecast_snapshot_id = @forecast_snapshot_id
    AND technique IN ('MM', 'MW')
    AND simulated_targets_count > 0
  GROUP BY
     label
    ,technique
    ,mail_plan_event_on
)
,_union AS
(
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _actuals
  UNION
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _forecast
)
SELECT
   u.label
  ,u.technique
  ,u.dropped_on
  ,a.sols_count      AS actuals_sols_count
  ,f.sols_count      AS forecast_sols_count
    ,a.payments_count_source
    ,a.adjusted_payments_count
    ,a.response_rate_source
    ,a.adjusted_response_rate 
FROM _union u
LEFT OUTER JOIN _actuals  a ON a.label = u.label AND a.technique = u.technique AND a.dropped_on = u.dropped_on
LEFT OUTER JOIN _forecast f ON f.label = u.label AND f.technique = u.technique AND f.dropped_on = u.dropped_on
--WHERE a.sols_count is null
ORDER BY
   label
  ,technique
  ,dropped_on
;