Ad performance daily

1-- core.criteo.ad_performance_daily
2-- Daily delivery and sales results per ad, the Criteo Commerce Growth ad report
3-- by day.
4--
5-- Grain: date_day x campaign_id x adset_id x ad_id. Spend that Criteo cannot tie
6-- to an ad arrives under ad_id "-1", once per ad set; it is kept, so totals match
7-- the campaign view in Criteo.
8-- Currency: the one chosen in the connector settings, the same for every advertiser.
9-- Depends on: staging.criteo.ad_statistics_daily, .campaign, .adset, .ad, .advertiser
10--
11-- The first twelve columns are the cross-channel ad contract (date_day,
12-- account_id, account_name, campaign_id, campaign_name, ad_id, ad_name, spend,
13-- impressions, clicks, conversions, conversion_value) shared with the other paid
14-- ads templates in this repo, so they can be stacked with UNION ALL.
15--
16-- conversions = sales attributed post-click within 30 days, same device
17-- (Criteo's SalesPc30d); conversion_value is the matching revenue. Cross-device,
18-- post-view and your own (client) attribution are kept as extra columns. Swap
19-- them into conversions if your Criteo account reports on another model.
20
21SELECT
22    s.date_day,
23    COALESCE(c.account_id, g.account_id, a.account_id) AS account_id,
24    adv.account_name,
25    s.campaign_id,
26    COALESCE(c.campaign_name, s.campaign_name)         AS campaign_name,
27    s.ad_id,
28    COALESCE(a.ad_name, s.ad_name)                     AS ad_name,
29    s.spend,
30    s.impressions,
31    s.clicks,
32    s.sales_pc_30d                                     AS conversions,
33    s.revenue_pc_30d                                   AS conversion_value,
34
35    'criteo'                                           AS source,
36    s.currency,
37    s.adset_id,
38    COALESCE(g.adset_name, s.adset_name)               AS adset_name,
39    c.campaign_goal,
40    g.objective                                        AS adset_objective,