Amazon ads
Ad Report
This template extracts a comprehensive breakdown of advertising performance on Amazon, grouped by product ads for sponsored products. It integrates data from the sponsored product product ad report with the profile and ad tables to deliver metrics like cost, clicks, and impressions for each product ad.
1with
2 report as (
3 select
4 *
5 from
6 {{raw.amazon_ads.sponsored_product_product_ad_report}}
7 )
8 , account_info as (
9 select
10 *
11 from
12 {{raw.amazon_ads.profile}}
13 )
14 , ad as (
15 select
16 *
17 from
18 {{raw.amazon_ads.sponsored_product_product_ad}}
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 , report.campaign_id
28 , sum(report.cost) as cost
29 , sum(report.clicks) as clicks
30 , sum(report.impressions) as impressions
31 from
32 report
33 left join ad on ad.ad_id = ad.ad_id
34 left join account_info on account_info.profile_id = ad.profile_id
35 group by
36 1
37 , 2
38 , 3
39 , 4
40 , 5
41 , 6
42 )
43select
44 *
45from
46 fieldsExample of output from model:
+------------+------------------+----------------+--------------+------------+------------+-------+--------+-------------+
| date | account_info_name| account_info_id| country_code | profile_id | campaign_id| cost | clicks| impressions |
+------------+------------------+----------------+--------------+------------+------------+-------+--------+-------------+
| 2023-04-20 | Account_Amazon | acc_amz_01 | US | prf_001 | camp_amz_01| 120.0 | 300 | 2000 |
| 2023-04-19 | Account_Amazon | acc_amz_01 | US | prf_001 | camp_amz_01| 125.0 | 305 | 2050 |
| 2023-04-18 | Account_Amazon | acc_amz_01 | US | prf_001 | camp_amz_01| 130.0 | 310 | 2100 |
| 2023-04-17 | Account_Amazon | acc_amz_01 | US | prf_001 | camp_amz_01| 135.0 | 315 | 2150 |
| 2023-04-16 | Account_Amazon | acc_amz_01 | US | prf_001 | camp_amz_01| 140.0 | 320 | 2200 |
+------------+------------------+----------------+--------------+------------+------------+-------+--------+-------------+This SQL model offers a granular, daily snapshot of advertising activity on Amazon related to sponsored products at the product ad level. By combining data from the sponsored product product ad report with additional information from profile and ad 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.
