Amazon ads
Campaign Report
Extracts a comprehensive report of advertising performance for Amazon's Sponsored Products, grouped by campaigns and accounts. This report integrates data from the campaign report with the account and campaign tables to provide metrics such as cost, clicks, and impressions for each campaign.
1with
2 report as (
3 select
4 *
5 from
6 {{raw.amazon_ads.sponsored_product_campaign_report}}
7 )
8 , account_info as (
9 select
10 *
11 from
12 {{raw.amazon_ads.profile}}
13 )
14 , campaigns as (
15 select
16 * from{{raw.amazon_ads.sponsored_product_campaign}}
17 )
18 , fields as (
19 select
20 report.date
21 , account_info.account_info_name
22 , account_info.account_info_id
23 , account_info.country_code
24 , account_info.profile_id
25 , campaigns.name as campaign_name
26 , report.campaign_id
27 , sum(report.cost) as cost
28 , sum(report.clicks) as clicks
29 , sum(report.impressions) as impressions
30 from
31 report
32 left join campaigns on campaigns.campaign_id = report.campaign_id
33 left join account_info on account_info.profile_id = campaigns.profile_id
34 group by
35 1
36 , 2
37 , 3
38 , 4
39 , 5
40 , 6
41 , 7
42 )
43select
44 *
45from
46 fieldsExample of output from model:
+------------+------------------+------------------+--------------+------------+----------------+--------------+-------+--------+-------------+
| date | account_info_name| account_info_id | country_code | profile_id | campaign_name | campaign_id | cost | clicks | impressions |
+------------+------------------+------------------+--------------+------------+----------------+--------------+-------+--------+-------------+
| 2023-04-20 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 200.0 | 500 | 7000 |
| 2023-04-19 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 210.0 | 510 | 7100 |
| 2023-04-18 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 215.0 | 515 | 7200 |
| 2023-04-17 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 220.0 | 520 | 7300 |
| 2023-04-16 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 225.0 | 525 | 7400 |
| 2023-04-15 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 230.0 | 530 | 7500 |
| 2023-04-14 | Amazon_Account_1 | account_001 | US | profile_01 | Product_A | camp_01 | 235.0 | 535 | 7600 |
+------------+------------------+------------------+--------------+------------+----------------+--------------+-------+--------+-------------+This SQL model delivers a granular report of advertising activity on Amazon's Sponsored Products at the campaign level. By combining insights from the campaign report with details about specific accounts and campaigns, marketers receive a complete view of metrics like cost, clicks, and impressions. This comprehensive analysis assists in optimizing advertising strategies by highlighting the performance of individual sponsored product campaigns.
