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 output
+ ------------+------------------+------------------+--------------+------------+----------------+--------------+-------+--------+-------------+
| 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.