Keyword Report

1-- core.amazon_ads.keyword_report
2-- Keyword Report. This template extracts a comprehensive breakdown of advertising
3-- performance on Amazon, grouped by keywords for sponsored products. It integrates
4-- data from the sponsored product keyword report with the profile and keyword
5-- tables to deliver metrics like cost, clicks, and impressions for each keyword.
6--
7-- Grain: day x profile x campaign x keyword. Reads the version 3 sponsored products
8-- keyword report, which holds the broad, phrase and exact match keywords.
9--
10-- From the weld.app template library, ported to the current synced schema. Not
11-- reconciled against Amazon Ads; check the output against the source before
12-- relying on it.
13
14with stats as (
15select *
16from {{raw.amazon_ads.sponsored_product_keyword_report_v3}}
17), account_info as (
18select *
19from {{raw.amazon_ads.profile}}
20), keyword as (
21select *
22from {{raw.amazon_ads.sponsored_product_keyword}}
23), fields as (
24select
25cast(stats.date as date) as date,
26account_info.account_info_name,
27account_info.account_info_id,
28account_info.country_code,
29stats.profile_id,
30coalesce(keyword.keyword_text, stats.keyword) as keyword_text,
31stats.campaign_id,
32stats.keyword_id,
33sum(stats.cost) as cost,
34sum(stats.clicks) as clicks,
35sum(stats.impressions) as impressions
36from stats
37left join keyword
38on keyword.keyword_id = stats.keyword_id
39and keyword.profile_id = stats.profile_id
40left join account_info