Weld logo
amazon-ads logo
Amazon ads

Keyword Report

This template extracts a comprehensive breakdown of advertising performance on Amazon, grouped by keywords for sponsored products. It integrates data from the sponsored product keyword report with the profile and keyword tables to deliver metrics like cost, clicks, and impressions for each keyword.
1with
2    report as (
3        select
4            *
5        from
6            {{raw.amazon_ads.sponsored_product_keyword_report}}
7    )
8  , account_info as (
9        select
10            *
11        from
12            {{raw.amazon_ads.profile}}
13    )
14  , keyword as (
15        select
16            *
17        from
18            {{raw.amazon_ads.sponsored_product_keyword}}
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          , keyword.keyword_text as keyword_text
28          , report.campaign_id
29          , sum(report.cost) as cost
30          , sum(report.clicks) as clicks
31          , sum(report.impressions) as impressions
32        from
33            report
34            left join keyword on keyword.keyword_id = report.keyword_id
35            left join account_info on account_info.profile_id = keyword.profile_id
36        group by
37            1
38          , 2
39          , 3
40          , 4
41          , 5
42          , 6
43          , 7
44    )
45select
46    *
47from
48    fields
Example of output from model:
+------------+------------------+----------------+--------------+------------+--------------+------------+-------+--------+-------------+
|    date    | account_info_name| account_info_id| country_code | profile_id | keyword_text | campaign_id|  cost | clicks| impressions  |
+------------+------------------+----------------+--------------+------------+--------------+------------+-------+--------+-------------+
| 2023-04-20 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 120.0 |  300  |    2000      |
| 2023-04-19 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 125.0 |  305  |    2050      |
| 2023-04-18 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 130.0 |  310  |    2100      |
| 2023-04-17 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 135.0 |  315  |    2150      |
| 2023-04-16 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 140.0 |  320  |    2200      |
| 2023-04-15 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 145.0 |  325  |    2250      |
| 2023-04-14 | Account_Amazon   | acc_amz_01     | US           | prf_001    |  shoes       | camp_amz_01| 150.0 |  330  |    2300      |
+------------+------------------+----------------+--------------+------------+--------------+------------+-------+--------+-------------+

This SQL model offers a granular, daily snapshot of advertising activity on Amazon related to sponsored products at the keyword level. By combining data from the sponsored product keyword report with additional information from profile and keyword 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.

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 logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

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