Ad performance daily

1-- core.pinterest_ads.ad_performance_daily
2-- Daily delivery and results per ad (promoted Pin), 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.pinterest_ads.ad_report, .ad, .ad_group, .campaign, .ad_account
6--
7-- An ad account holds campaigns, campaigns hold ad groups, ad groups hold ads.
8--
9-- Conversion definition, under Pinterest's default attribution (30 day click,
10-- 30 day engagement, 1 day view, dated on the ad action):
11--   conversions      = TOTAL_CONVERSIONS, every conversion event type Pinterest
12--                      counts (checkouts, sign-ups, leads, add to cart, page visits,
13--                      custom), across web, app and offline.
14--   conversion_value = checkout value across click, engagement and view
15--                      attribution. Only checkouts carry value in this report.
16-- So cost_per_conversion is spend per any conversion event, and roas is checkout
17-- value per unit of spend, the same as Ads Manager's "Total ROAS (Checkout)". The
18-- click, engagement and view split of checkout value is kept, so a click-only
19-- value can be rebuilt for cross-channel comparisons.
20--
21-- impressions are paid impressions; clicks are total Pin clicks (the report does
22-- not carry paid-only Pin clicks). See staging.pinterest_ads.ad_report.
23
24WITH perf AS (
25    SELECT
26        r.date_day,
27        r.account_id,
28        acc.account_name,
29        acc.currency,
30        r.campaign_id,
31        COALESCE(c.campaign_name, r.report_campaign_name) AS campaign_name,
32        r.ad_group_id,
33        g.ad_group_name,
34        r.ad_id,
35        a.ad_name,
36        r.spend,
37        r.impressions,
38        r.clicks,
39        r.conversions,
40        r.checkout_value                 AS conversion_value,