Amazon ads
Ad Group Report
This template extracts a comprehensive breakdown of advertising performance on Amazon, grouped by ad groups for sponsored products. It integrates data from the sponsored product ad group report with the profile and ad group tables to deliver metrics like cost, clicks, and impressions for each ad group.
1with
2 report as (
3 select
4 *
5 from
6 {{raw.amazon_ads.sponsored_product_ad_group_report}}
7 )
8 , account_info as (
9 select
10 *
11 from
12 {{raw.amazon_ads.profile}}
13 )
14 , ad_group as (
15 select
16 *
17 from
18 {{raw.amazon_ads.sponsored_product_ad_group}}
19 )
20 , fields as (
21 select
22 report.date
23 , account_info.account_info_name
24 , account_info.account_info_id
25 , account_info.country_code
26 , account_info.profile_id
27 , ad_group.name as ad_group_name
28 , report.campaign_id
29 , sum(report.cost) as cost
30 , sum(report.clicks) as clicks
31 , sum(report.impressions) as impressions
32 from
33 report
34 left join ad_group on ad_group.ad_group_id = report.ad_group_id
35 left join account_info on account_info.profile_id = ad_group.profile_id
36 group by
37 1
38 , 2
39 , 3
40 , 4
41 , 5
42 , 6
43 , 7
44 )
45select
46 *
47from
48 fieldsExample of output from model:
+------------+------------------+----------------+--------------+------------+--------------+------------+-------+--------+-------------+
| date | account_info_name| account_info_id| country_code | profile_id | ad_group_name | campaign_id| cost | clicks| impressions |
+------------+------------------+----------------+--------------+------------+--------------+------------+-------+--------+-------------+
| 2023-04-20 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 200.0 | 500 | 3000 |
| 2023-04-19 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 210.0 | 510 | 3100 |
| 2023-04-18 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 215.0 | 515 | 3150 |
| 2023-04-17 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 220.0 | 520 | 3200 |
| 2023-04-16 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 225.0 | 525 | 3250 |
| 2023-04-15 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 230.0 | 530 | 3300 |
| 2023-04-14 | Account_Amazon | acc_amz_01 | US | prf_001 | AdGroup_1 | camp_amz_01| 235.0 | 535 | 3350 |
+------------+------------------+----------------+--------------+------------+--------------+------------+-------+--------+-------------+This SQL model offers a granular, daily snapshot of advertising activity on Amazon related to sponsored products at the ad group level. By combining data from the sponsored product ad group report with additional information from profile and ad group tables, this template provides an extensive view of key metrics such as cost, clicks, and impressions. Marketers can use this data to fine-tune their advertising strategies and make informed decisions.
