# The AMC Query Library

> Ready-to-run Amazon Marketing Cloud queries for customer analytics, ad attribution, and lifetime value analysis.

Source: https://adlabs.app/guides/amc-query-library/ (updated 2026-10-19)

Introduction

## What is Amazon Marketing Cloud?

**Amazon Marketing Cloud (AMC)** is Amazon's clean room analytics environment that lets advertisers run custom SQL queries across their advertising and retail data. Unlike standard Amazon reporting, AMC lets you join purchase history with ad impressions, clicks, and conversions at the user level, unlocking insights like customer lifetime value, cross-sell paths, organic vs. paid acquisition, and full-funnel attribution.

This guide provides **15 production-tested SQL queries** that you can copy and run directly in your AMC instance. Each query has been optimized for AMC's SQL dialect, tested against real data, and includes analysis recommendations for turning raw output into actionable insights.

### Who This Guide Is For

This guide is for Amazon advertisers, brand managers, and agencies who have access to AMC and want to build customer analytics, LTV reports, or attribution models. You should be comfortable with basic SQL concepts (SELECT, JOIN, GROUP BY), though every query is provided ready to run.

### Key AMC Rules & Gotchas

AMC has a unique SQL dialect with specific limitations, reserved words, unit conversions, and date column traps that will save you hours of debugging. For the full reference, see the [AMC Complete Reference Guide →](https://adlabs.app/guides/amc-reference-guide/)

### Paid vs. Free AMC Tables

Not all AMC tables are available on the free tier. Amazon offers **paid data subscriptions** (such as Flexible Shopping Insights) that unlock additional tables beyond what's included with base AMC access. If you try to run a query that references a paid table you haven't subscribed to, the query will fail with an error.

10 of 15 Queries Require Paid AMC Table Subscriptions

The following tables used in this guide require an active Amazon paid data subscription:

* `amazon_retail_purchases`: required for Q1, Q2, Q4, Q5, Q6, Q10, Q12, Q13, Q15
* `conversions_all`: required for Q3

The remaining 5 queries (Q7, Q8, Q9, Q11, Q14) use only standard tables included with base AMC access: `sponsored_ads_traffic`, `dsp_impressions`, `dsp_clicks`, and `amazon_attributed_events_by_conversion_time`.

**How to check:** In your AMC instance, open the **Paid Features** tab to see which subscriptions are active. Amazon has made its first-party paid features, including Amazon Retail Purchases, free to subscribe through December 31, 2026\. If you don't see the option to opt in, reach out to your Amazon rep to get access.

Throughout this guide, queries requiring paid tables are flagged with a Paid table badge, and free queries are marked ✓ Free.

### The Four Query Categories

The 15 queries are organized into four categories, each targeting a different aspect of your Amazon business:



Revenue & Customer Analytics

3 queries

* Q1 Monthly Revenue & Customers
* Q2 Purchase Frequency
* Q3 Subscribe & Save



Customer Lifetime Value

3 queries

* Q4 Cohort LTV Analysis
* Q5 Cross-Sell Paths
* Q6 Time to Second Purchase



Ad Spend Tracking

3 queries

* Q7 Sponsored Ads Spend
* Q8 DSP Spend by Campaign
* Q9 SB/SBV/SD Spend



Attribution & Acquisition

6 queries

* Q10 Organic vs Paid First Touch
* Q11 Full-Funnel Attribution
* Q12 Organic vs Paid by ASIN
* Q13 NTB vs Repeat by ASIN
* Q14 First-Touch Attribution
* Q15 Path to Purchase

### How AMC Data Flows

Understanding the end-to-end workflow, from raw AMC tables to actionable dashboards:



AMC Tables

amazon\_attributed\_events dsp\_impressions sponsored\_ads\_traffic amazon\_retail\_purchases dsp\_clicks 



AMC Queries

SQL queries run inside AMC UI 15 ready-to-run 



CSV Export

Download results as CSV files 



Excel / Sheets

Pivot tables Charts & graphs Filter & sort 

Reference

## Quick Reference Table

All 15 queries at a glance. Click any query name to jump to its full documentation.

| #   | Query Name                                 | Category    | Tables Used                                                                               | Subscription | Est. Rows      |
| --- | ------------------------------------------ | ----------- | ----------------------------------------------------------------------------------------- | ------------ | -------------- |
| Q1  | [Monthly Revenue & Customers by ASIN](#q1) | Revenue     | amazon\_retail\_purchases                                                                 | Paid      | \~600          |
| Q2  | [Purchase Frequency Distribution](#q2)     | Revenue     | amazon\_retail\_purchases                                                                 | Paid      | \~5            |
| Q3  | [Subscribe & Save Analysis](#q3)           | Revenue     | conversions\_all                                                                          | Paid      | \~5,600        |
| Q4  | [Cohort LTV Analysis](#q4)                 | LTV         | amazon\_retail\_purchases                                                                 | Paid      | \~1,800        |
| Q5  | [Cross-Sell Paths](#q5)                    | LTV         | amazon\_retail\_purchases                                                                 | Paid      | \~4,600        |
| Q6  | [Time to Second Purchase](#q6)             | LTV         | amazon\_retail\_purchases                                                                 | Paid      | \~185          |
| Q7  | [Sponsored Ads Spend by ASIN](#q7)         | Spend       | sponsored\_ads\_traffic                                                                   | ✓ Free       | \~1,500        |
| Q8  | [DSP Spend by Campaign](#q8)               | Spend       | dsp\_impressions                                                                          | ✓ Free       | \~175          |
| Q9  | [SB/SBV/SD Spend by Campaign](#q9)         | Spend       | sponsored\_ads\_traffic                                                                   | ✓ Free       | \~200          |
| Q10 | [Organic vs Paid First Touch](#q10)        | Attribution | amazon\_retail\_purchases sponsored\_ads\_traffic dsp\_clicks                             | Paid      | \~120          |
| Q11 | [Full-Funnel Attribution](#q11)            | Attribution | amazon\_attributed\_events\_by\_conversion\_time                                          | ✓ Free       | \~19,000       |
| Q12 | [Organic vs Paid by ASIN](#q12)            | Attribution | amazon\_retail\_purchases sponsored\_ads\_traffic dsp\_clicks                             | Paid      | \~3,000–5,000  |
| Q13 | [NTB vs Repeat by ASIN](#q13)              | Attribution | amazon\_retail\_purchases                                                                 | Paid      | \~5,000–10,000 |
| Q14 | [First-Touch Attribution by Channel](#q14) | Attribution | amazon\_attributed\_events\_by\_conversion\_time sponsored\_ads\_traffic dsp\_impressions | ✓ Free       | \~5            |
| Q15 | [Path to Purchase](#q15)                   | Attribution | sponsored\_ads\_traffic dsp\_impressions amazon\_retail\_purchases                        | Paid      | \~500–2,000    |

Category 1

## Revenue & Customer Analytics

These queries extract core business metrics from `amazon_retail_purchases`: monthly revenue, customer counts, purchase frequency, and subscription data. They form the foundation of any AMC analytics setup.

Q1 Monthly Revenue & Customers by ASIN Paid table required 

The foundational revenue query. Pulls monthly revenue, unique customer count, and units sold for every ASIN in your catalog. This is the starting point for any product-level financial analysis. Use it to identify top sellers, track growth trends, and build category-level dashboards.

#### Output Columns

| Column            | Type      | Description                               |
| ----------------- | --------- | ----------------------------------------- |
| purchase\_month   | TIMESTAMP | First day of the month (truncated)        |
| asin              | TEXT      | Product ASIN                              |
| asin\_name        | TEXT      | Product name as stored in AMC             |
| unique\_customers | INTEGER   | Count of distinct buyers                  |
| revenue           | DECIMAL   | Total revenue (unit\_price × units\_sold) |
| units             | INTEGER   | Total units sold                          |

#### AMC Tables Used

amazon\_retail\_purchases 

#### SQL Query

Query 1: Monthly Revenue & Customers by ASINSELECT DATE\_TRUNC('MONTH', purchase\_date\_utc) AS purchase\_month, asin, asin\_name, COUNT(DISTINCT user\_id) AS unique\_customers, SUM(unit\_price \* purchase\_units\_sold) AS revenue, SUM(purchase\_units\_sold) AS units FROM amazon\_retail\_purchases GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Monthly Revenue & Customers by ASIN

Top products for January 2026: revenue, unique customers, and units sold

| purchase\_month | asin        | asin\_name       | unique\_customers | revenue  | units |
| --------------- | ----------- | ---------------- | ----------------- | -------- | ----- |
| 2026-01         | B0EXAMPLE01 | Cat Litter 40LB  | 2,530             | $104,200 | 2,930 |
| 2026-01         | B0EXAMPLE02 | Puppy Pads 100ct | 1,110             | $28,200  | 1,280 |
| 2026-01         | B0EXAMPLE03 | Waste Bags 600ct | 930               | $18,800  | 1,010 |
| 2026-01         | B0EXAMPLE04 | Pet Shampoo 32oz | 680               | $16,200  | 770   |
| 2026-01         | B0EXAMPLE05 | Litter Mat XL    | 590               | $15,000  | 660   |
| 2026-01         | B0EXAMPLE06 | Pet Wipes 400ct  | 450               | $10,200  | 490   |

Top 5 ASINs by Revenue: January 2026

Cat Litter 40LB

$104,200

Puppy Pads

$28,200

Waste Bags

$18,800

Pet Shampoo

$16,200

Litter Mat XL

$15,000

Pro Tip: Revenue Units

Revenue in `amazon_retail_purchases` is calculated from `unit_price × purchase_units_sold`. Unlike ad spend, this is already in dollars, so no conversion is needed.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** ASIN (or product category tag)
* **Columns:** purchase\_month
* **Values:** SUM of revenue, SUM of unique\_customers

**Best Chart Types:**

* **Line chart**: Revenue trends over time by product category
* **Stacked bar chart**: Revenue composition by category per month
* **Heatmap**: ASIN × month matrix to spot seasonal patterns

**Product Tagging:** Create an ASIN-to-category mapping table (e.g., "Litter", "Grooming", "Training"). Add a VLOOKUP column to group ASINs into categories, then pivot by category for cleaner dashboards.

**Calculated Fields to Add:**

* `Revenue per Customer` \= revenue / unique\_customers
* `Average Order Value` \= revenue / units (approximate)
* `MoM Growth %` \= (this\_month - last\_month) / last\_month

Q2 Purchase Frequency Distribution Paid table required 

Segments your entire customer base by how many orders they've placed (1, 2, 3–5, 6–10, 11+). Reveals the repeat purchase rate and shows how LTV scales with purchase frequency. Essential for understanding customer retention and the value of repeat buyers.

#### Output Columns

| Column                | Type    | Description                                                |
| --------------------- | ------- | ---------------------------------------------------------- |
| order\_bucket         | TEXT    | Frequency segment (1\_order, 2\_orders, 3-5\_orders, etc.) |
| customers             | INTEGER | Number of customers in this bucket                         |
| avg\_ltv              | DECIMAL | Average lifetime revenue per customer                      |
| avg\_unique\_products | DECIMAL | Average number of distinct ASINs purchased                 |

#### AMC Tables Used

amazon\_retail\_purchases 

#### SQL Query

Query 2: Purchase Frequency DistributionWITH customer\_stats AS ( SELECT user\_id, COUNT(DISTINCT purchase\_id) AS total\_orders, SUM(unit\_price \* purchase\_units\_sold) AS lifetime\_revenue, COUNT(DISTINCT asin) AS unique\_asins FROM amazon\_retail\_purchases GROUP BY user\_id ) SELECT CASE WHEN total\_orders = 1 THEN '1\_order' WHEN total\_orders = 2 THEN '2\_orders' WHEN total\_orders BETWEEN 3 AND 5 THEN '3-5\_orders' WHEN total\_orders BETWEEN 6 AND 10 THEN '6-10\_orders' ELSE '11+\_orders' END AS order\_bucket, COUNT(DISTINCT user\_id) AS customers, AVG(lifetime\_revenue) AS avg\_ltv, AVG(unique\_asins) AS avg\_unique\_products FROM customer\_stats GROUP BY 1

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Purchase Frequency Distribution

How many orders customers place over their lifetime, with average LTV per bucket

| order\_bucket | customer\_count | pct\_of\_total | avg\_ltv | avg\_units |
| ------------- | --------------- | -------------- | -------- | ---------- |
| 1 order       | 23,050          | 68.2%          | $32      | 1.2        |
| 2 orders      | 5,920           | 17.5%          | $71      | 2.6        |
| 3–5 orders    | 3,440           | 10.2%          | $134     | 5.1        |
| 6–10 orders   | 1,010           | 3.0%           | $292     | 11.3       |
| 11+ orders    | 370             | 1.1%           | $684     | 26.8       |

Customer Distribution

23.1K

68%

1

5.9K

2

3.4K

3–5

1.0K

6–10

370

11+

Orders per Customer

Average LTV by Frequency

1

$32

2

$71

3–5

$134

6–10

$292

11+

$684

Orders per Customer → Avg Lifetime Revenue

Key Insight

In most Amazon brands, 60% of customers are one-time buyers. But the 40% who come back often account for 70%+ of total revenue. This query quantifies that exact split for your brand.

Data Analysis Recommendations 

**Best Chart Types:**

* **Bar chart**: Customers by order bucket (shows the distribution)
* **Bar chart with secondary axis**: Customers (bars) vs. avg\_ltv (line) by bucket
* **Pie/donut chart**: % of total customers in each bucket

**Calculated Fields to Add:**

* `% of Total Customers` \= customers / SUM(customers)
* `Total Revenue per Bucket` \= customers × avg\_ltv (shows total revenue contribution)
* `LTV Multiplier` \= avg\_ltv / avg\_ltv\_of\_1\_order\_bucket

**Key Question to Answer:** What is the LTV multiplier from 1-order to 2-orders? If it's 3x, every dollar spent driving repeat purchases generates 3x the value.

Q3 Subscribe & Save Analysis Paid table required 

Adds the `purchase_retail_program` dimension to break out Subscribe & Save orders from standard purchases. Helps quantify subscription revenue and identify which products have the strongest S&S adoption. Note: S&S data availability varies by AMC instance.

#### Output Columns

| Column                    | Type      | Description                                         |
| ------------------------- | --------- | --------------------------------------------------- |
| purchase\_month           | TIMESTAMP | First day of the month                              |
| tracked\_asin             | TEXT      | Product ASIN                                        |
| purchase\_retail\_program | TEXT      | Purchase program (e.g., Subscribe & Save, or empty) |
| units                     | INTEGER   | Total units sold                                    |
| revenue                   | DECIMAL   | Total revenue                                       |

#### AMC Tables Used

conversions\_all 

#### SQL Query

Query 3: Subscribe & Save AnalysisSELECT DATE\_TRUNC('MONTH', CAST(event\_date\_utc AS TIMESTAMP)) AS purchase\_month, tracked\_asin, purchase\_retail\_program, SUM(total\_units\_sold) AS units, SUM(total\_product\_sales) AS revenue FROM conversions\_all GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Subscribe & Save Analysis

S&S enrollment and retention, comparing subscription vs one-time buyers

| purchase\_month | tracked\_asin | purchase\_retail\_program | units | revenue  |
| --------------- | ------------- | ------------------------- | ----- | -------- |
| 2026-01         | B0EXAMPLE01   | (empty)                   | 2,930 | $104,200 |
| 2026-01         | B0EXAMPLE02   | (empty)                   | 1,280 | $28,200  |
| 2026-01         | B0EXAMPLE03   | (empty)                   | 1,010 | $18,800  |
| 2026-01         | B0EXAMPLE04   | (empty)                   | 770   | $16,200  |

S&S Data Not Available 

The `purchase_retail_program` field returned empty for all rows. This is common when the AMC instance doesn't have Subscribe & Save data populated, or when the brand doesn't have S&S enabled on their products. The query still runs; it just won't differentiate between S&S and one-time purchases. 

What it would look like with S&S data Example

| purchase\_month | tracked\_asin | purchase\_retail\_program | units | revenue |
| --------------- | ------------- | ------------------------- | ----- | ------- |
| 2026-01         | B0EXAMPLE01   | Subscribe & Save          | 1,150 | $39,000 |
| 2026-01         | B0EXAMPLE01   | One-Time Purchase         | 1,780 | $65,200 |
| 2026-01         | B0EXAMPLE02   | Subscribe & Save          | 370   | $8,000  |
| 2026-01         | B0EXAMPLE02   | One-Time Purchase         | 910   | $20,200 |

Known Issue: Empty S&S Data

`purchase_retail_program` may return empty for all rows in some AMC instances. This is a known limitation: S&S data availability varies. You can also check for `sns_subscription_id` in `conversions_all` as an alternative indicator.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** ASIN or product category
* **Columns:** purchase\_retail\_program
* **Values:** SUM of revenue, SUM of units

**Best Chart Types:**

* **Stacked bar**: S&S vs standard revenue by product category
* **Line chart**: S&S adoption rate over time (S&S revenue / total revenue)

**Calculated Fields:**

* `S&S Adoption Rate` \= S&S revenue / total revenue per ASIN
* `S&S Revenue %` \= S&S revenue / total revenue (brand-wide)

If `purchase_retail_program` returns empty, this query still works as a basic revenue query with an extra empty column; use it as a fallback.

Category 2

## Customer Lifetime Value

These queries analyze customer behavior over time: cohort retention, cross-sell paths, and repurchase timing. Together they paint a complete picture of how customers interact with your brand after their first purchase.

Q4 Cohort LTV Analysis Paid table required 

Groups customers into monthly cohorts based on their first purchase date, then tracks their cumulative revenue and activity over subsequent months. This is the gold standard for LTV analysis. It shows you exactly how much revenue each cohort generates over time and whether your newer cohorts are more or less valuable than older ones.

#### Output Columns

| Column            | Type      | Description                                           |
| ----------------- | --------- | ----------------------------------------------------- |
| cohort\_month     | TIMESTAMP | Month of first purchase (defines the cohort)          |
| revenue\_month    | TIMESTAMP | Month when revenue was generated                      |
| active\_customers | INTEGER   | Customers from the cohort who purchased in this month |
| revenue           | DECIMAL   | Total revenue from this cohort in this month          |
| units             | INTEGER   | Total units sold                                      |

#### AMC Tables Used

amazon\_retail\_purchases 

#### SQL Query

Query 4: Cohort LTV AnalysisWITH first\_purchase AS ( SELECT user\_id, MIN(DATE\_TRUNC('MONTH', purchase\_date\_utc)) AS cohort\_month FROM amazon\_retail\_purchases GROUP BY user\_id ) SELECT fp.cohort\_month, DATE\_TRUNC('MONTH', rp.purchase\_date\_utc) AS revenue\_month, COUNT(DISTINCT rp.user\_id) AS active\_customers, SUM(rp.unit\_price \* rp.purchase\_units\_sold) AS revenue, SUM(rp.purchase\_units\_sold) AS units FROM amazon\_retail\_purchases rp INNER JOIN first\_purchase fp ON rp.user\_id = fp.user\_id GROUP BY 1, 2

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Cohort LTV Analysis

Revenue per customer by acquisition cohort over time

| cohort\_month | revenue\_month | active\_customers | revenue | units |
| ------------- | -------------- | ----------------- | ------- | ----- |
| 2025-07       | 2025-07        | 3,510             | $89,300 | 4,030 |
| 2025-07       | 2025-08        | 690               | $18,900 | 850   |
| 2025-07       | 2025-09        | 410               | $11,200 | 510   |
| 2025-08       | 2025-08        | 3,670             | $94,200 | 4,250 |
| 2025-08       | 2025-09        | 750               | $20,500 | 920   |
| 2025-09       | 2025-09        | 3,290             | $84,600 | 3,810 |

Revenue Retention Heatmap

Avg revenue per customer (darker = higher retention value)

Jul '25Aug '25Sep '25 Oct '25Nov '25Dec '25 

Mo 0Mo 1Mo 2Mo 3Mo 4Mo 5Mo 6 

$25

$11

$7

$5

$4

$3

$2

$26

$11

$8

$5

$4

$3

$26

$12

$8

$6

$4

$24

$10

$7

$5

$29

$13

$9

$28

$11

Low 

High 

Pro Tip: Building LTV Curves

Post-export, calculate `months_since_first = DATEDIF(cohort_month, revenue_month, "M")` and create a cumulative sum of revenue per cohort. Plot these as line charts where X = months since first purchase and each line = a cohort. This gives you classic LTV curves showing how revenue accumulates over time.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** cohort\_month
* **Columns:** months\_since\_first (calculated: DATEDIF or manual month difference)
* **Values:** SUM of revenue (or CUMULATIVE SUM for LTV curves)

**Best Chart Types:**

* **Line chart (LTV curves)**: X = months since acquisition, Y = cumulative revenue per customer, each line = a cohort. The most important chart in this guide.
* **Heatmap**: Cohort × month matrix showing revenue density. Dark cells = high activity.
* **Retention chart**: active\_customers / cohort\_size per month (requires joining with Q1 for initial cohort size)

**Calculated Fields:**

* `Months Since First` \= (YEAR(revenue\_month) - YEAR(cohort\_month)) × 12 + (MONTH(revenue\_month) - MONTH(cohort\_month))
* `Cumulative Revenue` \= Running sum of revenue within each cohort
* `LTV per Customer` \= Cumulative Revenue / Cohort Size
* `Retention Rate` \= active\_customers / initial cohort size

Q5 Cross-Sell Paths Paid table required 

Identifies what customers buy second after their first purchase. For each first-ASIN → second-ASIN pair, shows how many customers followed that path. Use this to optimize product bundling, recommendation widgets, and advertising strategy. Knowing that 500 customers who bought Product A next bought Product B is gold for targeting.

#### Output Columns

| Column          | Type    | Description                                         |
| --------------- | ------- | --------------------------------------------------- |
| first\_asin     | TEXT    | The ASIN of the customer's first purchase           |
| second\_asin    | TEXT    | The ASIN of the customer's second purchase          |
| customer\_count | INTEGER | Number of customers who followed this path (min 10) |

#### AMC Tables Used

amazon\_retail\_purchases 

#### SQL Query

Query 5: Cross-Sell PathsWITH ranked AS ( SELECT user\_id, asin, purchase\_day\_utc, ROW\_NUMBER() OVER (PARTITION BY user\_id ORDER BY purchase\_day\_utc, asin) AS rn FROM amazon\_retail\_purchases ) SELECT r1.asin AS first\_asin, r2.asin AS second\_asin, COUNT(DISTINCT r1.user\_id) AS customer\_count FROM ranked r1 INNER JOIN ranked r2 ON r1.user\_id = r2.user\_id AND r2.rn = 2 WHERE r1.rn = 1 GROUP BY 1, 2 HAVING COUNT(DISTINCT r1.user\_id) >= 10

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Cross-Sell Paths (First → Second ASIN)

Most common product journeys: what customers buy after their first purchase

| first\_asin | first\_product  | second\_asin | second\_product          | customer\_count |
| ----------- | --------------- | ------------ | ------------------------ | --------------- |
| B0EXAMPLE01 | Cat Litter 40LB | B0EXAMPLE01  | Cat Litter 40LB (repeat) | 1,700           |
| B0EXAMPLE01 | Cat Litter 40LB | B0EXAMPLE03  | Waste Bags 600ct         | 370             |
| B0EXAMPLE02 | Puppy Pads      | B0EXAMPLE02  | Puppy Pads (repeat)      | 310             |
| B0EXAMPLE03 | Waste Bags      | B0EXAMPLE01  | Cat Litter 40LB          | 230             |
| B0EXAMPLE04 | Pet Shampoo     | B0EXAMPLE04  | Pet Shampoo (repeat)     | 210             |
| B0EXAMPLE01 | Cat Litter 40LB | B0EXAMPLE05  | Litter Mat XL            | 130             |

Top Cross-Sell Flows

Line thickness = relative purchase volume

First Purchase

Second Purchase

Cat Litter 40LB

Puppy Pads

Waste Bags

Pet Shampoo

Litter Mat XL

Cat Litter 40LB

Waste Bags

Puppy Pads

Pet Shampoo

Litter Mat XL

Key Insight

The `HAVING >= 10` filter removes noise from low-volume paths. If you want more granularity, lower it to 5, but be aware this increases output rows significantly. For brands with 100+ ASINs, keep it at 10 or higher.

Data Analysis Recommendations 

**Best Analysis Approach:**

* Map ASINs to product names/categories using a VLOOKUP column
* Filter by a specific `first_asin` to answer: "What do customers buy after buying Product X?"
* Sort by `customer_count` descending (remember: sort post-export, not in AMC)

**Best Chart Types:**

* **Horizontal bar chart**: For a given entry product, top 10 second purchases by customer count
* **Sankey diagram**: First product → Second product flow (use a tool like SankeyMATIC)
* **Pivot table**: Rows = first\_asin category, Columns = second\_asin category, Values = SUM(customer\_count)

**Actionable Insights:**

* If first\_asin == second\_asin for many customers → strong repurchase signal for that product
* High cross-sell pairs → create Amazon bundles or Sponsored Display retargeting campaigns
* Use top paths to inform DSP audience strategy: target buyers of Product A with ads for Product B

Q6 Time to Second Purchase Paid table required 

Measures how long it takes customers to come back for a second purchase, broken down by their first ASIN. Shows both average and median days to repeat. The median is more useful since a few extreme outliers can skew the average. Use this to time your retargeting campaigns and email follow-ups.

#### Output Columns

| Column                   | Type    | Description                                             |
| ------------------------ | ------- | ------------------------------------------------------- |
| first\_asin              | TEXT    | The ASIN of the customer's first purchase               |
| customers\_with\_repeat  | INTEGER | Number of customers who made a second purchase (min 10) |
| avg\_days\_to\_repeat    | DECIMAL | Average days between first and second purchase          |
| median\_days\_to\_repeat | DECIMAL | Median days between first and second purchase           |

#### AMC Tables Used

amazon\_retail\_purchases 

#### SQL Query

Query 6: Time to Second PurchaseWITH ranked AS ( SELECT user\_id, asin, purchase\_dt\_utc, ROW\_NUMBER() OVER (PARTITION BY user\_id ORDER BY purchase\_dt\_utc, asin) AS rn FROM amazon\_retail\_purchases ) SELECT f.asin AS first\_asin, COUNT(DISTINCT f.user\_id) AS customers\_with\_repeat, AVG(SECONDS\_BETWEEN(f.purchase\_dt\_utc, s.purchase\_dt\_utc) / 86400.0) AS avg\_days\_to\_repeat, MEDIAN(SECONDS\_BETWEEN(f.purchase\_dt\_utc, s.purchase\_dt\_utc) / 86400.0) AS median\_days\_to\_repeat FROM ranked f INNER JOIN ranked s ON f.user\_id = s.user\_id AND s.rn = 2 WHERE f.rn = 1 GROUP BY 1 HAVING COUNT(DISTINCT f.user\_id) >= 10

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Time to Second Purchase

How long it takes customers to come back, by first product purchased

| first\_asin | product\_name    | customers\_with\_repeat | avg\_days | median\_days |
| ----------- | ---------------- | ----------------------- | --------- | ------------ |
| B0EXAMPLE01 | Cat Litter 40LB  | 2,300                   | 38.2      | 32           |
| B0EXAMPLE03 | Waste Bags 600ct | 670                     | 42.6      | 36           |
| B0EXAMPLE02 | Puppy Pads 100ct | 570                     | 45.1      | 38           |
| B0EXAMPLE04 | Pet Shampoo 32oz | 370                     | 52.8      | 45           |
| B0EXAMPLE05 | Litter Mat XL    | 290                     | 48.3      | 41           |
| B0EXAMPLE06 | Pet Wipes 400ct  | 190                     | 56.4      | 48           |

Median Days to Second Purchase by Product

Cat Litter 40LB

32 days

Waste Bags

36 days

Puppy Pads

38 days

Litter Mat XL

41 days

Pet Shampoo

45 days

Pet Wipes

48 days

AMC Date Math

AMC doesn't have a `DATEDIFF` function. Use `SECONDS_BETWEEN(start, end) / 86400.0` to convert to days. This works with `purchase_dt_utc` (TIMESTAMP type); don't use the INTEGER date columns.

Data Analysis Recommendations 

**Best Chart Types:**

* **Bar chart**: Median days to repeat by product category (sorted ascending)
* **Scatter plot**: X = median\_days\_to\_repeat, Y = customers\_with\_repeat (identifies high-volume fast-repeat products)

**Product Category Grouping:** Map first\_asin to categories, then calculate weighted averages within each category. Consumable products (litter, waste bags) should have shorter repeat cycles than durable goods.

**Actionable Insights:**

* If median is 45 days → schedule retargeting to start at day 30
* If avg ≫ median → you have long-tail repeat customers pulling the average up. The median is your true signal.
* Products with short repeat cycles are prime candidates for Subscribe & Save pushes

Category 3

## Ad Spend Tracking

These queries pull ad spend from Sponsored Ads and DSP, broken down by ASIN and campaign. Combined with the revenue queries in Category 1, they let you calculate true Customer Acquisition Cost (CAC) and ROAS at the product level.

Q7 Sponsored Ads Spend by ASIN (Monthly) ✓ Free 

Pulls monthly Sponsored Products, Brands, and Display spend broken down by the creative ASIN and ad product type. This is your primary spend-by-product query for Sponsored Ads. Note that SB/SBV campaigns may not have a `creative_asin`; use Q9 for campaign-level SB/SD spend.

#### Output Columns

| Column             | Type      | Description                                                   |
| ------------------ | --------- | ------------------------------------------------------------- |
| spend\_month       | TIMESTAMP | First day of the month                                        |
| creative\_asin     | TEXT      | ASIN associated with the ad creative                          |
| ad\_product\_type  | TEXT      | sponsored\_products, sponsored\_brands, or sponsored\_display |
| ad\_spend\_dollars | DECIMAL   | Ad spend in dollars (converted from microcents)               |
| clicks             | INTEGER   | Total clicks                                                  |
| impressions        | INTEGER   | Total impressions                                             |

#### AMC Tables Used

sponsored\_ads\_traffic 

#### SQL Query

Query 7: Sponsored Ads Spend by ASIN (Monthly)SELECT DATE\_TRUNC('MONTH', event\_date\_utc) AS spend\_month, creative\_asin, ad\_product\_type, SUM(spend) / 100000000.0 AS ad\_spend\_dollars, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM sponsored\_ads\_traffic GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Sponsored Ads Spend by ASIN (Monthly)

Monthly ad spend breakdown by ad type: SP, SB, and SD

| spend\_month | creative\_asin | ad\_product\_type                                                            | spend   | clicks | impressions |
| ------------ | -------------- | ---------------------------------------------------------------------------- | ------- | ------ | ----------- |
| 2025-10      | B0EXAMPLE01    | Sponsored Products                                                           | $11,050 | 14,510 | 1,105,000   |
| 2025-10      | B0EXAMPLE01    | [Sponsored Brands](https://adlabs.app/guides/amazon-sponsored-brands-guide/) | $2,920  | 3,250  | 374,000     |
| 2025-10      | B0EXAMPLE01    | Sponsored Display                                                            | $1,280  | 1,910  | 292,000     |
| 2025-11      | B0EXAMPLE01    | Sponsored Products                                                           | $13,610 | 17,120 | 1,274,000   |
| 2025-11      | B0EXAMPLE01    | Sponsored Brands                                                             | $3,740  | 4,130  | 445,000     |
| 2025-11      | B0EXAMPLE01    | Sponsored Display                                                            | $1,910  | 2,710  | 341,000     |

Monthly Ad Spend by Type: All ASINs

$13.0K

Sep

$19.6K

Oct

$25.9K

Nov

$23.2K

Dec

$21.0K

Jan

 Sponsored Products

 Sponsored Brands

 Sponsored Display

Microcents Conversion

Sponsored Ads `spend` is in microcents. Dividing by **100,000,000** (108) converts to dollars. If your spend looks like it's in the trillions, you forgot this step. If it looks like it's in the pennies, you divided by the wrong number.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** creative\_asin (mapped to product category)
* **Columns:** spend\_month
* **Values:** SUM of ad\_spend\_dollars
* **Filter:** ad\_product\_type (to slice by SP vs SB vs SD)

**Calculated Fields:**

* `CPC` \= ad\_spend\_dollars / clicks
* `CTR` \= clicks / impressions
* `CPM` \= (ad\_spend\_dollars / impressions) × 1000

**Combine with Q1:** Join this data with Q1 output on ASIN + month to calculate ACOS = ad\_spend / revenue and CAC = ad\_spend / new\_customers.

Q8 DSP Spend by Campaign (Monthly) ✓ Free 

Pulls monthly DSP spend at the campaign level from the `dsp_impressions` table. DSP campaigns don't have ASIN-level granularity in their spend data, so this groups by campaign name and ID. Use campaign naming conventions to map spend back to product categories.

#### Output Columns

| Column              | Type      | Description                                      |
| ------------------- | --------- | ------------------------------------------------ |
| spend\_month        | TIMESTAMP | First day of the month                           |
| campaign            | TEXT      | DSP campaign name                                |
| campaign\_id        | TEXT      | DSP campaign ID                                  |
| dsp\_spend\_dollars | DECIMAL   | DSP spend in dollars (converted from millicents) |
| impressions         | INTEGER   | Total impressions served                         |

#### AMC Tables Used

dsp\_impressions 

#### SQL Query

Query 8: DSP Spend by Campaign (Monthly)SELECT DATE\_TRUNC('MONTH', impression\_date\_utc) AS spend\_month, campaign, campaign\_id, SUM(total\_cost) / 100000.0 AS dsp\_spend\_dollars, SUM(impressions) AS impressions FROM dsp\_impressions GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

DSP Spend by Campaign (Monthly)

Amazon DSP programmatic spend and impression volume by campaign

| spend\_month | campaign                                | dsp\_spend\_dollars | impressions |
| ------------ | --------------------------------------- | ------------------- | ----------- |
| 2025-10      | Litter - DSP - Retargeting - Views      | $5,050              | 1,284,000   |
| 2025-10      | Pet Care - DSP - Prospecting - InMarket | $3,170              | 1,116,000   |
| 2025-11      | Litter - DSP - Retargeting - Views      | $6,980              | 1,704,000   |
| 2025-11      | Pet Care - DSP - Prospecting - InMarket | $4,270              | 1,428,000   |
| 2025-12      | Litter - DSP - Retargeting - Views      | $5,920              | 1,512,000   |

Total Monthly DSP Spend Trend

$15K$12K$9K$6K$3K$0 

Sep '25Oct '25Nov '25Dec '25Jan '26 

Millicents ≠ Microcents

DSP `total_cost` uses **millicents** (÷ 100,000 for dollars), while Sponsored Ads `spend` uses **microcents** (÷ 100,000,000). Don't mix them up; it's a 1,000x difference.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** campaign (or extracted campaign category)
* **Columns:** spend\_month
* **Values:** SUM of dsp\_spend\_dollars, SUM of impressions

**Calculated Fields:**

* `CPM` \= (dsp\_spend\_dollars / impressions) × 1000
* `% of Total DSP Spend` \= campaign\_spend / SUM(all\_campaign\_spend)

**Campaign Category Mapping:** If your DSP campaigns follow a naming convention (e.g., "Retargeting - Litter" or "Prospecting - Grooming"), extract the category prefix to group spend. Use `LEFT(campaign, FIND(" - ", campaign)-1)` or similar text formula.

Q9 SB/SBV/SD Spend by Campaign (Monthly) ✓ Free 

Extracts Sponsored Brands (including SBV) and Sponsored Display spend at the campaign level. Unlike Q7 which groups by `creative_asin`, SB and SD campaigns often don't have a meaningful creative ASIN. This query uses campaign names, which typically contain category prefixes like "Litter - NBR - SBV...", to enable category-level spend mapping.

#### Output Columns

| Column             | Type      | Description                                  |
| ------------------ | --------- | -------------------------------------------- |
| spend\_month       | TIMESTAMP | First day of the month                       |
| campaign           | TEXT      | Campaign name (contains category prefixes)   |
| ad\_product\_type  | TEXT      | sponsored\_brands or sponsored\_display      |
| ad\_spend\_dollars | DECIMAL   | Spend in dollars (converted from microcents) |
| clicks             | INTEGER   | Total clicks                                 |
| impressions        | INTEGER   | Total impressions                            |

#### AMC Tables Used

sponsored\_ads\_traffic 

#### SQL Query

Query 9: SB/SBV/SD Spend by Campaign (Monthly)SELECT DATE\_TRUNC('MONTH', event\_date\_utc) AS spend\_month, campaign, ad\_product\_type, SUM(spend) / 100000000.0 AS ad\_spend\_dollars, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM sponsored\_ads\_traffic WHERE ad\_product\_type IN ('sponsored\_brands', 'sponsored\_display') GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

SB/SBV & SD Spend by Campaign

Sponsored Brands (including Video) and Sponsored Display spend breakdown

| spend\_month | campaign                    | ad\_product\_type      | spend  | clicks | impressions |
| ------------ | --------------------------- | ---------------------- | ------ | ------ | ----------- |
| 2025-10      | Brand - SB - Top of Search  | Sponsored Brands       | $1,940 | 2,320  | 251,000     |
| 2025-10      | Litter - SBV - Product Page | Sponsored Brands Video | $970   | 1,280  | 172,000     |
| 2025-10      | Litter - SD - Retargeting   | Sponsored Display      | $890   | 1,340  | 205,000     |
| 2025-11      | Brand - SB - Top of Search  | Sponsored Brands       | $2,510 | 2,770  | 314,000     |
| 2025-11      | Litter - SBV - Product Page | Sponsored Brands Video | $1,240 | 1,610  | 209,000     |
| 2025-11      | Litter - SD - Retargeting   | Sponsored Display      | $1,700 | 2,350  | 292,000     |

Monthly SB vs SD Spend Comparison

$3.0K

Sep

$3.8K

Oct

$5.4K

Nov

$4.8K

Dec

$4.3K

Jan

 Sponsored Brands (incl. Video)

 Sponsored Display

Campaign Name Category Mapping

Extract the first word or phrase before the first " - " delimiter in the campaign name to identify the product category. For example, "Litter - NBR - SBV Top" → category = "Litter". This lets you spread SB/SD spend across product categories in a CAC/LTV report.

Data Analysis Recommendations 

**Key Steps:**

* Add a "Category" column using text extraction from campaign name
* Pivot by Category × spend\_month for a clean spend-over-time view
* Combine with Q7 data to get total Sponsored spend (SP from Q7 + SB/SD from Q9)

**Best Chart Types:**

* **Stacked bar**: SB vs SD spend by category
* **Line chart**: Monthly SB/SD spend trends by category
* **Treemap**: Share of SB/SD spend by category

**Why This Matters for CAC:** Without Q9, you'd only have SP spend by ASIN (Q7). Many brands allocate significant [budget](https://adlabs.app/guides/amazon-ppc-budget-guide/) to SB/SBV campaigns that don't have ASIN-level tracking. This query fills that gap by letting you map SB/SD spend to categories via campaign names.

Category 4

## Attribution & Acquisition

The most powerful queries in the library. These join purchase data with ad interaction data to determine how customers were acquired (organic vs. paid), attribute conversions to specific campaigns, and classify buyers as new-to-brand or repeat, all at the product level.

Q10 Organic vs Paid First Touch (Account-Level) Paid table required 

For each new customer, determines whether they ever clicked a Sponsored Ad or DSP ad before their first purchase. Classifies customers as "paid\_touched" or "organic\_only" and groups by cohort month. This is the simplest organic/paid split. It answers: "What percentage of our new customers were influenced by ads?"

#### Output Columns

| Column            | Type      | Description                             |
| ----------------- | --------- | --------------------------------------- |
| cohort\_month     | TIMESTAMP | Month of the customer's first purchase  |
| acquisition\_type | TEXT      | 'paid\_touched' or 'organic\_only'      |
| new\_customers    | INTEGER   | Number of new customers in this segment |

#### AMC Tables Used

amazon\_retail\_purchases sponsored\_ads\_traffic dsp\_clicks 

#### SQL Query

Query 10: Organic vs Paid First Touch (Account-Level)WITH first\_purchase AS ( SELECT user\_id, MIN(DATE\_TRUNC('MONTH', purchase\_date\_utc)) AS cohort\_month FROM amazon\_retail\_purchases GROUP BY user\_id ), ad\_touched AS ( SELECT user\_id, 1 AS is\_paid FROM sponsored\_ads\_traffic WHERE clicks > 0 GROUP BY user\_id UNION SELECT user\_id, 1 AS is\_paid FROM dsp\_clicks WHERE clicks > 0 GROUP BY user\_id ) SELECT fp.cohort\_month, CASE WHEN adtch.user\_id IS NOT NULL THEN 'paid\_touched' ELSE 'organic\_only' END AS acquisition\_type, COUNT(DISTINCT fp.user\_id) AS new\_customers FROM first\_purchase fp LEFT JOIN ad\_touched adtch ON fp.user\_id = adtch.user\_id GROUP BY 1, 2

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Organic vs Paid First Touch

How new customers discovered the brand: organic search/browse vs ad-attributed

| cohort\_month | acquisition\_type | new\_customers |
| ------------- | ----------------- | -------------- |
| 2025-08       | Organic           | 2,300          |
| 2025-08       | Paid              | 1,370          |
| 2025-09       | Organic           | 2,170          |
| 2025-09       | Paid              | 1,480          |
| 2025-10       | Organic           | 2,470          |
| 2025-10       | Paid              | 1,700          |

New Customer Acquisition: Organic vs Paid (Monthly)

3,670

Aug

3,650

Sep

4,170

Oct

4,860

Nov

4,380

Dec

3,960

Jan

 Organic

 Paid (Ad-Attributed)

"Ad Touched" vs "Ad Attributed"

This query uses a broad definition: a customer is "paid\_touched" if they _ever_ clicked any ad, even if that click wasn't directly before their purchase. This overestimates paid attribution compared to last-click models, but it's the correct approach for understanding ad influence across the full customer journey.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** cohort\_month
* **Columns:** acquisition\_type
* **Values:** SUM of new\_customers

**Best Chart Types:**

* **100% stacked bar**: Organic vs paid mix over time (shows trend shifts)
* **Line chart**: Organic % over time: organic / (organic + paid)
* **Area chart**: Stacked area showing absolute new customer volume by type

**Key Metrics:**

* `Organic %` \= organic\_only / (organic\_only + paid\_touched) × 100
* `Paid CAC` \= Total ad spend (Q7 + Q8 + Q9) / paid\_touched customers
* `Blended CAC` \= Total ad spend / ALL new customers

Q11 Full-Funnel Attribution ✓ Free 

Amazon's own attribution model, using the `amazon_attributed_events_by_conversion_time` table. Provides campaign-level attribution with new-to-brand flags, broken down by ad product type, campaign, and tracked ASIN. This is the most detailed attribution data available in AMC, but it only covers ad-attributed events (up to 25 months of data).

#### Output Columns

| Column              | Type      | Description                                                                 |
| ------------------- | --------- | --------------------------------------------------------------------------- |
| conversion\_month   | TIMESTAMP | Month when the conversion occurred                                          |
| ad\_product\_type   | TEXT      | sponsored\_products, sponsored\_brands, sponsored\_display, or NULL for DSP |
| campaign            | TEXT      | Campaign name                                                               |
| tracked\_asin       | TEXT      | ASIN attributed to the conversion                                           |
| new\_to\_brand      | BOOLEAN   | Whether the purchaser is new to the brand                                   |
| customers           | INTEGER   | Unique customers                                                            |
| purchases           | INTEGER   | Total purchases                                                             |
| attributed\_revenue | DECIMAL   | Total attributed product sales                                              |
| ntb\_purchases      | INTEGER   | New-to-brand purchase count                                                 |
| ntb\_revenue        | DECIMAL   | New-to-brand attributed revenue                                             |

#### AMC Tables Used

amazon\_attributed\_events\_by\_conversion\_time 

#### SQL Query

Query 11: Full-Funnel AttributionSELECT DATE\_TRUNC('MONTH', CAST(conversion\_event\_date\_utc AS TIMESTAMP)) AS conversion\_month, ad\_product\_type, campaign, tracked\_asin, new\_to\_brand, COUNT(DISTINCT user\_id) AS customers, SUM(purchases) AS purchases, SUM(total\_product\_sales) AS attributed\_revenue, SUM(new\_to\_brand\_purchases) AS ntb\_purchases, SUM(new\_to\_brand\_total\_product\_sales) AS ntb\_revenue FROM amazon\_attributed\_events\_by\_conversion\_time WHERE purchases > 0 GROUP BY 1, 2, 3, 4, 5

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Full-Funnel Attribution (Ad-Attributed Purchases)

Revenue attributed to each ad type: which channels drive conversions

| conversion\_month | ad\_product\_type  | campaign                           | tracked\_asin | customers | attributed\_revenue |
| ----------------- | ------------------ | ---------------------------------- | ------------- | --------- | ------------------- |
| 2026-01           | Sponsored Products | Litter - SP - Exact - Top Keywords | B0EXAMPLE01   | 1,100     | $44,900             |
| 2026-01           | Sponsored Products | Bags - SP - Auto                   | B0EXAMPLE03   | 370       | $13,800             |
| 2026-01           | Sponsored Brands   | Brand - SB - Top of Search         | B0EXAMPLE01   | 290       | $11,600             |
| 2026-01           | Sponsored Display  | Litter - SD - Retargeting          | B0EXAMPLE01   | 170       | $6,800              |
| 2026-01           | DSP                | Pet Care - DSP - Prospecting       | B0EXAMPLE01   | 110       | $4,500              |

Attributed Revenue by Ad Type: January 2026

Sponsored Products  
$58,70071.9%

Sponsored Brands  
$11,60014.2%

Sponsored Display  
$6,8008.3%

DSP  
$4,5005.5%

CAST Required for conversion\_event\_date\_utc

Unlike other date columns, `conversion_event_date_utc` requires an explicit `CAST(... AS TIMESTAMP)` before you can use `DATE_TRUNC` on it. Without the CAST, the query will fail.

Limited Date Range

The `amazon_attributed_events_by_conversion_time` table holds up to 25 months of data. For longer historical NTB/repeat analysis going back years, use Q13 instead.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** ad\_product\_type (or campaign category)
* **Columns:** conversion\_month
* **Values:** SUM of attributed\_revenue, SUM of ntb\_revenue
* **Filter:** tracked\_asin (mapped to product category)

**Key Calculated Fields:**

* `NTB %` \= ntb\_revenue / attributed\_revenue × 100
* `NTB ROAS` \= ntb\_revenue / ad\_spend (join with Q7/Q8/Q9)
* `Attributed ROAS` \= attributed\_revenue / ad\_spend

**Best Chart Types:**

* **Stacked bar**: NTB vs repeat attributed revenue by ad type
* **Line chart**: NTB % trends over time by ad type
* **Bubble chart**: Campaign comparison: X = spend, Y = attributed revenue, size = NTB %

Q12 Organic vs Paid New Customer Acquisition by ASIN Paid table required 

The ASIN-level version of Q10\. For each new customer's first purchase, determines whether they were ad-touched, broken down by the specific ASIN they first bought. This enables product-level organic vs. paid analysis, essential for understanding which products drive organic acquisition and which depend on ads.

#### Output Columns

| Column            | Type      | Description                        |
| ----------------- | --------- | ---------------------------------- |
| cohort\_month     | TIMESTAMP | Month of first purchase            |
| asin              | TEXT      | ASIN of first purchase             |
| acquisition\_type | TEXT      | 'paid\_touched' or 'organic\_only' |
| new\_customers    | INTEGER   | Number of new customers            |

#### AMC Tables Used

amazon\_retail\_purchases sponsored\_ads\_traffic dsp\_clicks 

#### SQL Query

Query 12: Organic vs Paid New Customer Acquisition by ASINWITH first\_purchase AS ( SELECT user\_id, DATE\_TRUNC('MONTH', purchase\_date\_utc) AS cohort\_month, asin, purchase\_date\_utc, ROW\_NUMBER() OVER (PARTITION BY user\_id ORDER BY purchase\_date\_utc, asin) AS rn FROM amazon\_retail\_purchases ), first\_only AS ( SELECT user\_id, cohort\_month, asin, purchase\_date\_utc FROM first\_purchase WHERE rn = 1 ), ad\_touched AS ( SELECT user\_id, 1 AS is\_paid FROM sponsored\_ads\_traffic WHERE clicks > 0 GROUP BY user\_id UNION SELECT user\_id, 1 AS is\_paid FROM dsp\_clicks WHERE clicks > 0 GROUP BY user\_id ) SELECT fo.cohort\_month, fo.asin, CASE WHEN adtch.user\_id IS NOT NULL THEN 'paid\_touched' ELSE 'organic\_only' END AS acquisition\_type, COUNT(DISTINCT fo.user\_id) AS new\_customers FROM first\_only fo LEFT JOIN ad\_touched adtch ON fo.user\_id = adtch.user\_id GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Organic vs Paid New Customers by ASIN

Per-product breakdown of how new customers were acquired

| cohort\_month | asin        | product\_name   | acquisition\_type | new\_customers |
| ------------- | ----------- | --------------- | ----------------- | -------------- |
| 2026-01       | B0EXAMPLE01 | Cat Litter 40LB | Organic           | 1,490          |
| 2026-01       | B0EXAMPLE01 | Cat Litter 40LB | Paid              | 1,040          |
| 2026-01       | B0EXAMPLE02 | Puppy Pads      | Organic           | 740            |
| 2026-01       | B0EXAMPLE02 | Puppy Pads      | Paid              | 370            |
| 2026-01       | B0EXAMPLE03 | Waste Bags      | Organic           | 500            |
| 2026-01       | B0EXAMPLE03 | Waste Bags      | Paid              | 430            |

New Customer Acquisition by Product: January 2026

1.5K

1.0K

Cat  
Litter

740

370

Puppy  
Pads

500

430

Waste Bags

410

270

Pet  
Shampoo

350

240

Litter  
Mat

 Organic

 Paid

Why Q12 Over Q10

Q10 gives you the account-level organic/paid split. Q12 adds the ASIN dimension, letting you build product category filters. If product A is 90% organic and product B is 40% organic, you know exactly where your ads are driving acquisition and where organic brand strength carries the weight.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** ASIN (mapped to product category)
* **Columns:** acquisition\_type
* **Values:** SUM of new\_customers
* **Filter/Slicer:** cohort\_month (for time-based filtering)

**Best Chart Types:**

* **100% stacked bar**: Organic vs paid % by product category
* **Grouped bar chart**: Absolute new customers: organic vs paid per category
* **Line chart with category filter**: Organic % trend over time for a selected category

**Product Category Slicing:** Create an ASIN → Category mapping, add as a column, then create a slicer/filter pill for category. This lets you toggle between "All Products", "Litter", "Grooming", etc. in your dashboard.

Q13 NTB vs Repeat Purchases by ASIN (Full History) Paid table required 

Classifies every purchase as New-to-Brand or Repeat based on whether it's the customer's first month purchasing from your brand. Unlike Q11 (which covers up to 25 months of ad-attributed data), this query spans the full retail history (up to 5 years) and covers ALL purchases, not just ad-attributed ones. The definitive NTB analysis query.

#### Output Columns

| Column          | Type      | Description                  |
| --------------- | --------- | ---------------------------- |
| purchase\_month | TIMESTAMP | Month of purchase            |
| asin            | TEXT      | Product ASIN                 |
| customer\_type  | TEXT      | 'new\_to\_brand' or 'repeat' |
| customers       | INTEGER   | Unique buyers                |
| units           | INTEGER   | Units sold                   |
| revenue         | DECIMAL   | Total revenue                |

#### AMC Tables Used

amazon\_retail\_purchases 

#### SQL Query

Query 13: NTB vs Repeat Purchases by ASIN (Full History)WITH first\_purchase AS ( SELECT user\_id, MIN(purchase\_date\_utc) AS first\_purchase\_date FROM amazon\_retail\_purchases GROUP BY user\_id ) SELECT DATE\_TRUNC('MONTH', rp.purchase\_date\_utc) AS purchase\_month, rp.asin, CASE WHEN DATE\_TRUNC('MONTH', rp.purchase\_date\_utc) = DATE\_TRUNC('MONTH', fp.first\_purchase\_date) THEN 'new\_to\_brand' ELSE 'repeat' END AS customer\_type, COUNT(DISTINCT rp.user\_id) AS customers, SUM(rp.purchase\_units\_sold) AS units, SUM(rp.unit\_price \* rp.purchase\_units\_sold) AS revenue FROM amazon\_retail\_purchases rp INNER JOIN first\_purchase fp ON rp.user\_id = fp.user\_id GROUP BY 1, 2, 3

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

NTB vs Repeat Purchases by ASIN (Full History)

New-to-brand vs returning customers: monthly trend across all products

| purchase\_month | asin        | product\_name   | customer\_type | customers | units | revenue |
| --------------- | ----------- | --------------- | -------------- | --------- | ----- | ------- |
| 2026-01         | B0EXAMPLE01 | Cat Litter 40LB | NTB            | 1,580     | 1,720 | $70,700 |
| 2026-01         | B0EXAMPLE01 | Cat Litter 40LB | Repeat         | 950       | 1,210 | $33,500 |
| 2026-01         | B0EXAMPLE02 | Puppy Pads      | NTB            | 770       | 850   | $19,500 |
| 2026-01         | B0EXAMPLE02 | Puppy Pads      | Repeat         | 340       | 430   | $8,700  |
| 2026-01         | B0EXAMPLE03 | Waste Bags      | NTB            | 610       | 650   | $12,400 |
| 2026-01         | B0EXAMPLE03 | Waste Bags      | Repeat         | 320       | 360   | $6,400  |

NTB vs Repeat Customers: Monthly Trend (All ASINs)

4,140

Aug

4,140

Sep

4,680

Oct

5,460

Nov

5,160

Dec

4,860

Jan

 New-to-Brand (NTB)

 Repeat Customers

NTB Definition

A customer is "new\_to\_brand" if their purchase occurs in the **same month** as their very first purchase from your brand. All subsequent months = repeat. This is conservative: even if they buy a new product for the first time, if they've purchased ANY product before, they're classified as repeat.

Large Result Set

This query can return 5,000–10,000+ rows (ASIN × month × customer\_type). If it times out in AMC, add `HAVING COUNT(DISTINCT rp.user_id) >= 5` to filter out very low-volume ASINs.

Data Analysis Recommendations 

**Recommended Pivot Table Setup:**

* **Rows:** purchase\_month
* **Columns:** customer\_type
* **Values:** SUM of customers, SUM of revenue
* **Filter/Slicer:** ASIN (mapped to product category)

**Best Chart Types:**

* **Stacked area chart**: NTB vs repeat customers over time (shows the full history)
* **100% stacked bar**: NTB % by product category
* **Line chart**: NTB % trend over time (new\_to\_brand / total × 100)

**Calculated Fields:**

* `NTB %` \= new\_to\_brand\_customers / (new\_to\_brand + repeat) × 100
* `NTB Revenue %` \= new\_to\_brand\_revenue / total\_revenue × 100
* `Revenue per NTB Customer` \= new\_to\_brand\_revenue / new\_to\_brand\_customers
* `Revenue per Repeat Customer` \= repeat\_revenue / repeat\_customers

**Combine with Q12:** Use Q12 (organic vs paid) + Q13 (NTB vs repeat) together to build a 2×2 matrix: Organic NTB, Organic Repeat, Paid NTB, Paid Repeat: the most complete customer acquisition view possible.

New-to-Brand vs Repeat Customers

Monthly customer acquisition breakdown

New-to-Brand

Repeat

1.2k9006003000 

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Q14 First-Touch Attribution by Channel ✓ Free 

For each converting customer, identifies their very first ad touchpoint and attributes the conversion to that channel. Shows SP, SB, SD, DSP, or Organic breakdown with NTB rates. Essential for understanding which channels drive initial awareness and which types of customers each channel attracts.

#### Output Columns

| Column                      | Type    | Description                                                                                               |
| --------------------------- | ------- | --------------------------------------------------------------------------------------------------------- |
| first\_touch\_channel       | TEXT    | Channel of first ad touchpoint (Sponsored Products, Sponsored Brands, Sponsored Display, DSP, or Organic) |
| customer\_count             | INTEGER | Number of converting customers attributed to this channel                                                 |
| total\_revenue              | DECIMAL | Total revenue from these customers                                                                        |
| total\_units                | INTEGER | Total units sold                                                                                          |
| pct\_of\_customers          | DECIMAL | Percentage of total converting customers                                                                  |
| avg\_revenue\_per\_customer | DECIMAL | Average revenue per customer for this channel                                                             |
| ntb\_customer\_count        | INTEGER | Number of new-to-brand customers                                                                          |
| ntb\_pct                    | DECIMAL | Percentage of customers who are NTB                                                                       |

#### AMC Tables Used

amazon\_attributed\_events\_by\_conversion\_time sponsored\_ads\_traffic dsp\_impressions 

#### SQL Query

Query 14: First-Touch Attribution by ChannelWITH conversions AS ( SELECT user\_id, MAX(CASE WHEN new\_to\_brand = true THEN 1 ELSE 0 END) AS is\_ntb, SUM(COALESCE(units\_sold, 0)) AS total\_units, SUM(COALESCE(total\_product\_sales, 0)) AS total\_revenue FROM amazon\_attributed\_events\_by\_conversion\_time WHERE conversion\_event\_date\_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND total\_purchases > 0 GROUP BY user\_id ), sp\_touches AS ( SELECT user\_id, event\_date\_utc AS touch\_time, 1 AS ch\_code FROM sponsored\_ads\_traffic WHERE event\_date\_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND ad\_product\_type = 'sponsored\_products' ), sb\_touches AS ( SELECT user\_id, event\_date\_utc AS touch\_time, 2 AS ch\_code FROM sponsored\_ads\_traffic WHERE event\_date\_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND ad\_product\_type = 'sponsored\_brands' ), sd\_touches AS ( SELECT user\_id, event\_date\_utc AS touch\_time, 3 AS ch\_code FROM sponsored\_ads\_traffic WHERE event\_date\_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND ad\_product\_type = 'sponsored\_display' ), dsp\_touches AS ( SELECT user\_id, impression\_date\_utc AS touch\_time, 4 AS ch\_code FROM dsp\_impressions WHERE impression\_date\_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) ), all\_touches AS ( SELECT user\_id, touch\_time, ch\_code FROM sp\_touches UNION ALL SELECT user\_id, touch\_time, ch\_code FROM sb\_touches UNION ALL SELECT user\_id, touch\_time, ch\_code FROM sd\_touches UNION ALL SELECT user\_id, touch\_time, ch\_code FROM dsp\_touches ), first\_touch\_ranked AS ( SELECT user\_id, ch\_code, ROW\_NUMBER() OVER (PARTITION BY user\_id ORDER BY touch\_time ASC) AS rn FROM all\_touches ), first\_touch AS ( SELECT user\_id, ch\_code FROM first\_touch\_ranked WHERE rn = 1 ), attributed AS ( SELECT COALESCE(ft.ch\_code, 0) AS ch\_code, c.user\_id, c.is\_ntb, c.total\_units, c.total\_revenue FROM conversions c LEFT JOIN first\_touch ft ON c.user\_id = ft.user\_id ), channel\_stats AS ( SELECT ch\_code, COUNT(DISTINCT user\_id) AS customer\_count, SUM(total\_revenue) AS total\_revenue, SUM(total\_units) AS total\_units, SUM(is\_ntb) AS ntb\_customer\_count FROM attributed GROUP BY ch\_code ), grand\_total AS ( SELECT SUM(customer\_count) AS total\_customers FROM channel\_stats ) SELECT CASE cs.ch\_code WHEN 1 THEN 'Sponsored Products' WHEN 2 THEN 'Sponsored Brands' WHEN 3 THEN 'Sponsored Display' WHEN 4 THEN 'DSP' ELSE 'Organic' END AS first\_touch\_channel, cs.customer\_count, cs.total\_revenue, cs.total\_units, ROUND(100.0 \* cs.customer\_count / NULLIF(gt.total\_customers, 0), 2) AS pct\_of\_customers, ROUND(cs.total\_revenue \* 1.0 / NULLIF(cs.customer\_count, 0), 2) AS avg\_revenue\_per\_customer, cs.ntb\_customer\_count, ROUND(100.0 \* cs.ntb\_customer\_count / NULLIF(cs.customer\_count, 0), 2) AS ntb\_pct FROM channel\_stats cs CROSS JOIN grand\_total gt

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

First-Touch Attribution by Channel

Which channel first introduced converting customers to your brand

| first\_touch\_channel | customer\_count | total\_revenue | total\_units | pct\_of\_customers | avg\_revenue\_per\_customer | ntb\_customer\_count | ntb\_pct |
| --------------------- | --------------- | -------------- | ------------ | ------------------ | --------------------------- | -------------------- | -------- |
| Sponsored Products    | 11,680          | $310,300       | 14,600       | 60.8%              | $26.57                      | 7,610                | 65.2%    |
| DSP                   | 3,790           | $120,800       | 4,740        | 19.7%              | $31.87                      | 1,280                | 33.8%    |
| Sponsored Brands      | 2,390           | $67,800        | 2,990        | 12.4%              | $28.37                      | 1,640                | 68.6%    |
| Sponsored Display     | 790             | $23,100        | 990          | 4.1%               | $29.24                      | 310                  | 39.2%    |
| Organic               | 550             | $24,500        | 690          | 2.9%               | $44.55                      | 190                  | 34.5%    |

Customers by First-Touch Channel

Which channel first introduced converting customers

Sponsored Products

11,680

DSP

3,790

Sponsored Brands

2,390

Sponsored Display

790

Organic

550

NTB Rate by First-Touch Channel

Percentage of new-to-brand customers per channel

Sponsored Brands

68.6%

Sponsored Products

65.2%

Sponsored Display

39.2%

Organic

34.5%

DSP

33.8%

Pro Tip

Sponsored Brands typically shows the highest NTB rate because it captures customers **during brand discovery searches**. SP dominates volume (60%+) but SB punches above its weight for new customer acquisition. Organic first-touch customers convert at the highest revenue per customer, proof that brand awareness pays off. Use integer codes instead of strings in CTEs to avoid AMC dropping labels through UNION ALL chains. The hardcoded dates in Q14 and Q15 must sit inside the date range you pick in the AMC UI, or the query returns zero rows.

Data Analysis Recommendations 

**Key Metrics to Calculate:**

* **Cost per First Touch:** Combine with Q7/Q8 spend data to calculate acquisition cost by channel
* **NTB Cost per Acquisition:** Channel spend ÷ ntb\_customer\_count
* **Revenue per NTB Customer:** Filter to NTB only and compare avg\_revenue across channels

**Best Chart Types:**

* **Horizontal bar chart**: Customer count by channel (as shown above)
* **Grouped bar chart**: NTB vs Repeat side by side per channel
* **Pie/donut chart**: Share of customers by first-touch channel

**Strategic Insight:** If SP dominates first-touch (>50%), you're heavily search-dependent; consider diversifying into DSP/SB for top-of-funnel growth. If Organic first-touch customers show higher avg revenue, that validates upper-funnel investment to build brand awareness before the search moment.

Q15 Path to Purchase Paid table required 

Maps the ad touchpoint journey for converting customers. It shows the first 5 touchpoints in sequence (e.g., "sponsored\_products > dsp > sponsored\_brands"). Reveals the most common paths customers take before purchasing, helping you understand which channel combinations drive the best outcomes.

#### Output Columns

| Column             | Type    | Description                                           |
| ------------------ | ------- | ----------------------------------------------------- |
| path               | TEXT    | Sequence of up to 5 ad touchpoints separated by " > " |
| customers          | INTEGER | Number of customers who followed this path            |
| revenue            | DECIMAL | Total revenue from customers on this path             |
| rev\_per\_customer | DECIMAL | Average revenue per customer on this path             |

#### AMC Tables Used

sponsored\_ads\_traffic dsp\_impressions amazon\_retail\_purchases 

#### SQL Query

Query 15: Path to PurchaseWITH all\_touches AS ( SELECT user\_id, event\_date\_utc AS touch\_time, ad\_product\_type AS ad\_type FROM sponsored\_ads\_traffic WHERE event\_date\_utc >= CAST('2025-01-01' AS TIMESTAMP) AND event\_date\_utc < CAST('2026-02-01' AS TIMESTAMP) UNION ALL SELECT user\_id, impression\_date\_utc AS touch\_time, 'dsp' AS ad\_type FROM dsp\_impressions WHERE impression\_date\_utc >= CAST('2025-01-01' AS TIMESTAMP) AND impression\_date\_utc < CAST('2026-02-01' AS TIMESTAMP) ), numbered AS ( SELECT user\_id, ad\_type, ROW\_NUMBER() OVER (PARTITION BY user\_id ORDER BY touch\_time) AS rn FROM all\_touches ), pivoted AS ( SELECT user\_id, MAX(CASE WHEN rn = 1 THEN ad\_type END) AS t1, MAX(CASE WHEN rn = 2 THEN ad\_type END) AS t2, MAX(CASE WHEN rn = 3 THEN ad\_type END) AS t3, MAX(CASE WHEN rn = 4 THEN ad\_type END) AS t4, MAX(CASE WHEN rn = 5 THEN ad\_type END) AS t5 FROM numbered WHERE rn <= 5 GROUP BY user\_id ), paths AS ( SELECT user\_id, CONCAT( COALESCE(t1, ''), CASE WHEN t2 IS NOT NULL THEN CONCAT(' > ', t2) ELSE '' END, CASE WHEN t3 IS NOT NULL THEN CONCAT(' > ', t3) ELSE '' END, CASE WHEN t4 IS NOT NULL THEN CONCAT(' > ', t4) ELSE '' END, CASE WHEN t5 IS NOT NULL THEN CONCAT(' > ', t5) ELSE '' END ) AS path FROM pivoted ), user\_purchases AS ( SELECT user\_id, SUM(unit\_price \* purchase\_units\_sold) AS revenue FROM amazon\_retail\_purchases WHERE purchase\_date\_utc >= CAST('2025-01-01' AS TIMESTAMP) AND purchase\_date\_utc < CAST('2026-02-01' AS TIMESTAMP) GROUP BY user\_id ) SELECT pa.path, COUNT(\*) AS customers, SUM(p.revenue) AS revenue, SUM(p.revenue) / COUNT(\*) AS rev\_per\_customer FROM user\_purchases p INNER JOIN paths pa ON p.user\_id = pa.user\_id GROUP BY pa.path

#### Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Path to Purchase

Most common ad touchpoint journeys before conversion

| path                                                          | customers | revenue  | rev\_per\_customer |
| ------------------------------------------------------------- | --------- | -------- | ------------------ |
| sponsored\_products                                           | 7,700     | $296,700 | $38.53             |
| sponsored\_products > sponsored\_products                     | 2,770     | $123,400 | $44.55             |
| dsp > sponsored\_products                                     | 1,310     | $64,200  | $49.01             |
| sponsored\_brands > sponsored\_products                       | 1,100     | $56,200  | $51.09             |
| sponsored\_products > dsp > sponsored\_products               | 740       | $40,400  | $54.59             |
| dsp                                                           | 590       | $22,500  | $38.14             |
| sponsored\_products > sponsored\_brands > sponsored\_products | 520       | $29,600  | $56.92             |
| dsp > sponsored\_products > sponsored\_products               | 430       | $23,100  | $53.72             |

Top 5 Paths to Purchase

Customer journey flows: touchpoint sequence before conversion

SP

7,700 customers

SP → SP

2,770

DSP → SP

1,310

SB → SP

1,100

SP → DSP → SP

740

Pro Tip

Add `HAVING COUNT(*) >= 50` to filter noise paths. The most actionable insight here is which ad types appear as the **FIRST touch** in high-revenue paths; that tells you where to invest top-of-funnel budget.

Data Analysis Recommendations 

**Recommended Analysis:**

* **Sort by rev\_per\_customer DESC**: multi-touch paths almost always have higher value per customer
* **Group by first touchpoint**: extract the first element of each path to see top-of-funnel channel performance
* **Group by path length**: compare 1-touch vs 2-touch vs 3+ touch conversion value

**Best Chart Types:**

* **Horizontal bar chart**: Top paths by customer count (as shown above)
* **Sankey/flow diagram**: If you have visualization tools that support it, Sankey charts are ideal for path data
* **Scatter plot**: Customers (x) vs Rev/Customer (y) to find high-value paths

**Combine with Q14:** Use Q14 for channel-level first-touch summary, then Q15 to understand the full journey. Together they answer "where do customers start?" and "what path do they take?"

Reference

## AMC Tables Reference

The five core AMC tables used across this query library. Each table contains many more columns than listed here; these are the key columns relevant to the queries in this guide.

### amazon\_retail\_purchases

The core retail purchases table. Contains every purchase made by customers of your brand on Amazon. Used by 9 of the 15 queries in this library.

| Column                  | Type      | Notes                                                   |
| ----------------------- | --------- | ------------------------------------------------------- |
| user\_id                | TEXT      | Non-viewable; used for JOINs and COUNT(DISTINCT) only |
| asin                    | TEXT      | Product ASIN                                            |
| asin\_name              | TEXT      | Product name                                            |
| purchase\_date\_utc     | TIMESTAMP | Purchase date; use with DATE\_TRUNC                     |
| purchase\_dt\_utc       | TIMESTAMP | Purchase datetime; use for SECONDS\_BETWEEN             |
| purchase\_day\_utc      | INTEGER   | Day number; usable for ORDER BY in window functions     |
| purchase\_id            | TEXT      | Unique order identifier                                 |
| unit\_price             | DECIMAL   | Price per unit in dollars (already converted)           |
| purchase\_units\_sold   | INTEGER   | Units in this line item                                 |
| purchase\_program\_name | TEXT      | Subscribe & Save indicator (may be empty)               |

### sponsored\_ads\_traffic

All Sponsored Products, Sponsored Brands, and Sponsored Display traffic events: impressions, clicks, and spend. Used by Q7, Q9, Q10, Q12, Q14, and Q15.

| Column            | Type      | Notes                                                      |
| ----------------- | --------- | ---------------------------------------------------------- |
| user\_id          | TEXT      | Non-viewable                                             |
| event\_date\_utc  | TIMESTAMP | Use this for DATE\_TRUNC (not event\_day\_utc)             |
| creative\_asin    | TEXT      | ASIN shown in the ad; may be empty for SB/SD               |
| ad\_product\_type | TEXT      | sponsored\_products, sponsored\_brands, sponsored\_display |
| campaign          | TEXT      | Campaign name                                              |
| campaign\_id      | TEXT      | Campaign ID                                                |
| spend             | BIGINT    | In microcents; divide by 100,000,000 for dollars        |
| clicks            | INTEGER   | Number of clicks                                           |
| impressions       | INTEGER   | Number of impressions                                      |

### dsp\_impressions

DSP impression-level data with spend information. Used by Q8 for DSP spend tracking, and by Q14 and Q15 for touchpoints.

| Column                | Type      | Notes                                               |
| --------------------- | --------- | --------------------------------------------------- |
| user\_id              | TEXT      | Non-viewable                                      |
| impression\_date\_utc | TIMESTAMP | Use this for DATE\_TRUNC (not impression\_day\_utc) |
| campaign              | TEXT      | DSP campaign name                                   |
| campaign\_id          | TEXT      | DSP campaign ID                                     |
| total\_cost           | BIGINT    | In millicents; divide by 100,000 for dollars     |
| impressions           | INTEGER   | Number of impressions                               |

### dsp\_clicks

DSP click-level data. Used by Q10 and Q12 to determine if a customer was "ad-touched" via DSP.

| Column           | Type      | Notes             |
| ---------------- | --------- | ----------------- |
| user\_id         | TEXT      | Non-viewable    |
| click\_date\_utc | TIMESTAMP | Click timestamp   |
| campaign         | TEXT      | DSP campaign name |
| clicks           | INTEGER   | Number of clicks  |

### amazon\_attributed\_events\_by\_conversion\_time

Amazon's attribution table, the largest table (392 columns). Links ad traffic to conversion events. Used by Q11 for full-funnel attribution and by Q14 for conversions.

| Column                                | Type    | Notes                                                 |
| ------------------------------------- | ------- | ----------------------------------------------------- |
| user\_id                              | TEXT    | Non-viewable                                        |
| conversion\_event\_date\_utc          | TEXT    | Requires CAST(... AS TIMESTAMP) before DATE\_TRUNC |
| ad\_product\_type                     | TEXT    | Ad type that drove the conversion (NULL for DSP)      |
| campaign                              | TEXT    | Campaign name                                         |
| tracked\_asin                         | TEXT    | ASIN attributed to the conversion                     |
| new\_to\_brand                        | BOOLEAN | Amazon's NTB flag                                     |
| purchases                             | INTEGER | Number of attributed purchases                        |
| total\_product\_sales                 | DECIMAL | Total attributed sales revenue                        |
| new\_to\_brand\_purchases             | INTEGER | NTB purchase count                                    |
| new\_to\_brand\_total\_product\_sales | DECIMAL | NTB revenue                                           |
