Ad Group Report

1-- core.amazon_ads.ad_group_report
2-- Ad Group Report. This template extracts a comprehensive breakdown of advertising
3-- performance on Amazon, grouped by ad groups for sponsored products. It integrates
4-- data from the sponsored product ad group report with the profile and ad group
5-- tables to deliver metrics like cost, clicks, and impressions for each ad group.
6--
7-- Grain: day x profile x campaign x ad group. Reads the version 3 sponsored
8-- products ad group report.
9--
10-- From the weld.app template library, ported to the current synced schema. Not
11-- reconciled against Amazon Ads; check the output against the source before
12-- relying on it.
13
14with stats as (
15select *
16from {{raw.amazon_ads.sponsored_product_ad_group_report_v3}}
17), account_info as (
18select *
19from {{raw.amazon_ads.profile}}
20), ad_group as (
21select *
22from {{raw.amazon_ads.sponsored_product_ad_group_v3}}
23), fields as (
24select
25cast(stats.date as date) as date,
26account_info.account_info_name,
27account_info.account_info_id,
28account_info.country_code,
29stats.profile_id,
30coalesce(ad_group.name, stats.ad_group_name) as ad_group_name,
31stats.campaign_id,
32stats.ad_group_id,
33sum(stats.cost) as cost,
34sum(stats.clicks) as clicks,
35sum(stats.impressions) as impressions
36from stats
37left join ad_group
38on ad_group.ad_group_id = stats.ad_group_id
39and ad_group.profile_id = stats.profile_id
40left join account_info