You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Any ideas? We have been working through a sample query I am sharing below for any feedback from the community.
WITH campaigns (campaign_id_string, matched_behavior_segment_ids) AS (
SELECT
campaign_id_string,
matched_behavior_segment_ids
FROM
sponsored_ads_traffic
WHERE
matched_behavior_segment_ids IS NOT NULL
AND
ad_product_type IN (
'sponsored_products',
'sponsored_brands')
),
traffic AS (
SELECT
campaign,
campaign_id_string,
ad_product_type,
user_id,
matched_behavior_segment_ids,
event_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks
FROM
sponsored_ads_traffic
WHERE
ad_product_type IN (
'sponsored_products',
'sponsored_brands')
GROUP BY
1,
2,
3,
4,
5,
6
),
attributed_events AS (
SELECT
campaign,
campaign_id_string,
ad_product_type,
user_id,
traffic_event_id,
SUM(purchases) AS purchases
FROM
amazon_attributed_events_by_traffic_time
WHERE
campaign_id_string IN (
SELECT
campaign_id_string
FROM
campaigns
)
GROUP BY
1,
2,
3,
4,
5
),
combined AS (
SELECT
t.campaign,
t.campaign_id_string,
[t.ad](http://t.ad/)_product_type,
t.matched_behavior_segment_ids,
t.impressions,
t.clicks,
t.user_id,
a.purchases
FROM
traffic t
LEFT JOIN attributed_events a
ON t.campaign = a.campaign
AND t.user_id = a.user_id
AND t.event_id = a.traffic_event_id
)
SELECT
campaign,
campaign_id_string,
ad_product_type,
matched_behavior_segment_ids,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SUM(purchases) AS purchases
FROM
combined c
GROUP BY
1,
2,
3,
4
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Any ideas? We have been working through a sample query I am sharing below for any feedback from the community.
Beta Was this translation helpful? Give feedback.
All reactions