Iris, a consumer electronics brand, would like to modify a query to only include purchase records from the underlying table. Which of the following represents how the query should be written?
SELECT campaign, COUNT(DISTINCT user_id) AS purchasers, SUM(purchases) AS purchases FROM conversions GROUP BY 1
SELECT campaign, COUNT(DISTINCT user_id) AS purchasers, SUM(purchases) AS purchases FROM amazon_attributed_events_by_traffic_time GROUP BY 1
SELECT campaign, COUNT(DISTINCT user_id) AS purchasers, SUM(purchases) AS purchases FROM amazon_attributed_events_by_traffic_time WHERE purchases = 1 GROUP BY 1
Correct Answer
SELECT campaign, COUNT(DISTINCT user_id) AS purchasers, SUM(purchases) AS purchases FROM amazon_attributed_events_by_traffic_time WHERE purchases = 1 GROUP BY 1
Topics in this question
About the Amazon Marketing Cloud Certification
The Amazon Marketing Cloud Certification covers Amazon's clean-room analytics environment: how AMC data is structured, writing SQL queries against it, and turning results such as overlap and path-to-conversion analysis into audience and media decisions.
Exam guide and all 95 Amazon Marketing Cloud questions →Related Amazon Marketing Cloud questions
- 1Which of these queries will only include purchase records from the underlying table?
- 2Iris, a consumer electronics brand, recently started using AMC to perform analytics on events related to their Amazon Ads campaigns. Why should Iris consider using the instructional query library?
- 3Super Power Batteries, a consumer-packaged goods brand, is new to AMC and wants to learn more about what tables are available to query. Where in the AMC UI can they find this information?
- 4Why should you wait until 14 days after the end date of your query date range to run analyses that leverage the amazon_attributed_events_by_traffic_time table?
- 5How would you adapt this query to limit results to Sponsored Products keyword targeting only? SELECT ad_product_type, targeting, customer_search_term, match_type, SUM (spend)/100000000 AS total_cost_dollars, ((SUM (spend)/100000000)/SUM (impressions)) *1000 AS avg_cpm, SUM (impressions) AS impressions, SUM (clicks) AS clicks, (SUM (clicks)/SUM (impressions)) AS ctr FROM sponsored_ads_traffic WHERE match_type IN (‘PHRASE’, ‘BROAD’, ‘EXACT’) GROUP BY 1,2,3,4
- 6Which table should you use in a query meant to help you choose new Amazon audiences that may benefit your Amazon DSP campaign performance?