Ad stats by country monthly

1-- core.google_ads.ad_stats_by_country_monthly
2-- Ad stats by country monthly. Generates monthly ad statistics by country,
3-- including spend, clicks, CPC, cost per conversion, CTR, conversions, conversion
4-- value, impressions, and interactions, using data from Google Ads. It groups the
5-- data by month and country criterion ID.
6--
7-- Grain: month x country criterion ID. country_criterion_id is Google's
8-- geo target ID (2208 is Denmark, 2840 the United States); join Google's geo
9-- target list to name it. Spend is in the account currency, converted from
10-- micros; with several accounts in different currencies, filter to one first.
11-- Rates are worked out from the month's totals; ctr is a fraction.
12--
13-- From the weld.app template library, corrected. Not reconciled against Google
14-- Ads; check the output against the source before relying on it.
15
16select
17    date_trunc(cast(date as date), month) as month
18  , country_criterion_id
19  , sum(cost_micros) / 1e6 as spend
20  , sum(clicks) as clicks
21  , safe_divide(sum(cost_micros) / 1e6, sum(clicks)) as cpc
22  , safe_divide(sum(cost_micros) / 1e6, sum(conversions)) as cost_per_conversion
23  , safe_divide(sum(clicks), sum(impressions)) as ctr
24  , sum(conversions) as conversions
25  , sum(conversions_value) as conversions_value
26  , sum(impressions) as impressions
27  , sum(interactions) as interactions
28from
29    {{raw.google_ads.ad_group_country_stats}}
30group by
31    1
32  , 2