Ad performance daily

1-- core.facebook_ads.ad_performance_daily
2-- Daily delivery and purchase results per ad, the Ads Manager "Ads" tab by day.
3--
4-- Grain: date_day x account_id x ad_id. Currency: the ad account's.
5-- Depends on: staging.facebook_ads.ad_roas_insight, .ad_roas_insight_actions,
6-- .ad_roas_insight_action_values, .ad, .ad_set, .campaign, .account,
7-- .ad_set_attribution
8--
9-- The leading columns follow the cross-channel ad contract shared with the other
10-- paid ads templates in this repo, so the models can be stacked with UNION ALL.
11--
12-- clicks is link clicks (Ads Manager "Link clicks"), the closest match to a
13-- click on a search or display ad; Meta's "Clicks (all)", which also counts
14-- likes, comments and profile clicks, is in clicks_all.
15--
16-- conversions is purchases: Meta's omni_purchase action (the Ads Manager
17-- "Purchases" column, de-duplicated across website, app and offline), falling
18-- back to the purchase action where omni_purchase is absent. Never both, so a
19-- purchase is not counted twice. conversion_value is the matching purchase value.
20-- Both use the attribution windows the connector requests (7-day click and
21-- 1-day view by default) and are dated on the day the purchase happened, not the
22-- day of the ad impression. For other conversion events, see
23-- conversions_by_action_type_daily.
24
25WITH purchases AS (
26    -- One row per ad and day: pick the purchase action types out of the
27    -- one-row-per-action-type tables before joining, so the join cannot fan out.
28    SELECT
29        date_day,
30        account_id,
31        ad_id,
32        COALESCE(SUM(IF(action_type = 'omni_purchase', action_count, NULL)),
33                 SUM(IF(action_type = 'purchase', action_count, NULL)))          AS purchases
34    FROM {{staging.facebook_ads.ad_roas_insight_actions}}
35    WHERE action_type IN ('omni_purchase', 'purchase')
36    GROUP BY 1, 2, 3
37),
38
39purchase_values AS (
40    SELECT