Campaign Report

1-- core.amazon_ads.campaign_report
2-- Campaign Report. Extracts a comprehensive report of advertising performance for
3-- Amazon's Sponsored Products, grouped by campaigns and accounts. This report
4-- integrates data from the campaign report with the account and campaign tables to
5-- provide metrics such as cost, clicks, and impressions for each campaign.
6--
7-- Grain: day x profile x campaign. Reads the version 3 sponsored products campaign
8-- report, which can hold more than one row per campaign and day (one per
9-- applicable budget rule); the metrics are summed over them.
10--
11-- From the weld.app template library, ported to the current synced schema. Not
12-- reconciled against Amazon Ads; check the output against the source before
13-- relying on it.
14
15with stats as (
16select *
17from {{raw.amazon_ads.sponsored_product_campaign_report_v3}}
18), account_info as (
19select *
20from {{raw.amazon_ads.profile}}
21), campaign as (
22select *
23from {{raw.amazon_ads.sponsored_product_campaign}}
24), fields as (
25select
26cast(stats.date as date) as date,
27account_info.account_info_name,
28account_info.account_info_id,
29account_info.country_code,
30stats.profile_id,
31coalesce(campaign.name, stats.campaign_name) as campaign_name,
32stats.campaign_id,
33sum(stats.cost) as cost,
34sum(stats.clicks) as clicks,
35sum(stats.impressions) as impressions
36from stats
37left join campaign
38on campaign.campaign_id = stats.campaign_id
39and campaign.profile_id = stats.profile_id
40left join account_info