Campaign country performance daily

1-- core.pinterest_ads.campaign_country_performance_daily
2-- Daily campaign performance by the viewer's country, the Ads Manager country
3-- breakdown.
4--
5-- Grain: date_day x account_id x campaign_id x country_code. Currency: the ad
6-- account's.
7-- Depends on: staging.pinterest_ads.campaign_country_report, .campaign, .ad_account
8--
9-- Read from Pinterest's own country report, not summed from ads: the ad report has
10-- no country dimension. Same conversion definitions as ad_performance_daily
11-- (all conversion events; checkout value; default attribution windows).
12--
13-- Pinterest only reports a country when it can resolve one, so the country rows of
14-- a campaign day can sum to less than campaign_performance_daily for that day.
15-- Use this model for shares and comparisons between countries, the campaign
16-- model for totals.
17
18SELECT
19    r.date_day,
20    r.account_id,
21    acc.account_name,
22    acc.currency,
23    r.campaign_id,
24    COALESCE(c.campaign_name, r.report_campaign_name) AS campaign_name,
25    r.country_code,
26
27    r.spend,
28    r.impressions,
29    r.clicks,
30    r.conversions,
31    r.checkout_value                                   AS conversion_value,
32
33    SAFE_DIVIDE(r.clicks, r.impressions)               AS ctr,
34    SAFE_DIVIDE(r.spend, r.clicks)                     AS cpc,
35    SAFE_DIVIDE(r.spend, r.impressions) * 1000         AS cpm,
36    SAFE_DIVIDE(r.spend, r.conversions)                AS cost_per_conversion,
37    SAFE_DIVIDE(r.checkout_value, r.spend)             AS roas
38FROM {{staging.pinterest_ads.campaign_country_report}} r
39LEFT JOIN {{staging.pinterest_ads.campaign}} c
40    ON c.account_id = r.account_id