Ad performance daily

1-- core.tiktok_ads.ad_performance_daily
2-- Daily delivery and conversion results per ad, the Ads Manager "Ads" tab by day.
3--
4-- Grain: date_day x account_id x ad_id. Currency: the advertiser's.
5-- Depends on: staging.tiktok_ads.ad_daily_report, .ad, .ad_group, .campaign, .advertiser
6--
7-- The first twelve columns are the cross-channel ad contract (date_day,
8-- account_id, account_name, campaign_id, campaign_name, ad_id, ad_name, spend,
9-- impressions, clicks, conversions, conversion_value) shared with the other paid
10-- ads templates in this repo, so they can be stacked with UNION ALL.
11-- In TikTok terms: account_id is the advertiser, the ad set level is the ad group.
12--
13-- conversions = TikTok's "Conversions" column: the number of times the ad's
14-- optimisation event happened (a purchase for a purchase-optimised ad group, a
15-- form submit for a lead ad group, an install for an app ad group), under the
16-- attribution window set on the ad group. What it counts therefore differs per
17-- ad group; optimization_goal and optimization_event say which.
18--
19-- conversion_value = the website Complete Payment value (count x average value).
20-- It is a purchase value, not the value of the optimisation event, so ROAS here
21-- is TikTok's "Complete payment ROAS". App purchases are kept apart in
22-- app_purchases and app_purchase_value. For an app or lead account,
23-- conversion_value will be 0 and ROAS empty.
24
25SELECT
26    r.date_day,
27    r.account_id,
28    adv.account_name,
29    COALESCE(a.campaign_id, g.campaign_id) AS campaign_id,
30    c.campaign_name,
31    r.ad_id,
32    a.ad_name,
33    r.spend,
34    r.impressions,
35    r.clicks,
36    CAST(r.conversions AS FLOAT64)         AS conversions,
37    r.complete_payment_value               AS conversion_value,
38
39    'tiktok_ads'                           AS source,
40    adv.currency,