Weld logo
amazon-ads
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 100+ more apps and databases available.

amazon-ads
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.