Ad Report

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