Weld logo
amazon-ads logo
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    fields
Example 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.

Ready to start modeling your own amazon-ads data?

Get started building your data warehouse with amazon-ads and connect all your apps and databases.

amazon-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.