### Pixel Conversion Example from conversions_with_relevance

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Custom attribution overview_notebook.md

Illustrates a single pixel conversion event that is relevant to multiple campaigns, showing how it appears in the 'conversions_with_relevance' data source.

```markdown
| conversion_id | conversion | event_category | campaign    |
| :------------------------ | :-------------------- | :------------------------- | :-------------------------------------------------- |
| 56789         | 1          | pixel        | campaign_c |
| 56789         | 1          | pixel        | campaign_d |
```

--------------------------------

### LEFT JOIN Example with CTEs

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

This example uses Common Table Expressions (CTEs) to calculate viewability rates and then performs a LEFT JOIN to combine this data with impression data. It includes all rows from the left table (impressions) and matching rows from the right table (viewability), with NULLs for non-matches.

```sql
-- Instructional Query: How to use LEFT JOIN()--

WITH imp_dv AS (
    SELECT
        campaign,
        campaign_id_string,
        SUM(viewable_impressions) AS viewable_impressions,
        SUM(measurable_impressions) AS measurable_impressions,
        SUM(viewable_impressions) / SUM(measurable_impressions) AS viewability_rate
    FROM 
        dsp_views
    GROUP BY 
        1,
        2
),

imp_di AS (
    SELECT
        campaign,
        campaign_id_string,
        SUM(impressions) AS impressions
    FROM 
        dsp_impressions
    GROUP BY 
        1,
        2
)

SELECT
    di.campaign,
    di.campaign_id_string,
    impressions,
    viewable_impressions,
    measurable_impressions,
    viewability_rate
FROM 
    imp_di di
LEFT JOIN 
    imp_dv dv 
ON 
    di.campaign = dv.campaign AND di.campaign_id_string = dv.campaign_id_string
```

--------------------------------

### Pixel Conversion Example from conversions

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Custom attribution overview_notebook.md

Shows a single pixel conversion event as recorded in the 'conversions' data source, which does not include campaign-specific details.

```markdown
| conversion_id | conversion | event_category |
| -------------- | ---------- | --------------- |
| 56789         | 1          | pixel        |
```

--------------------------------

### Example of RANDOM() non-deterministic function

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Demonstrates the use of the RANDOM() function, which can lead to different results on each execution. Avoid this function for deterministic queries.

```sql
-- Instructional Query: Example of non-determnistic function --
select RANDOM() as hello
```

--------------------------------

### Off-Amazon Path to Conversion Setup

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Path to conversion by audience segment_notebook.md

This SQL snippet sets up CTEs for tracking off-Amazon conversions using Events Managers signals. It requires customization for campaign IDs, ad tags, conversion event types, and audience segments.

```sql
-- Instructional Query: Path to conversion by audience segment (Off-Amazon)
-- AD PRODUCTS: Amazon DSP --

/*
 ------- Customization Instructions ------- 
 1) First, run the exploratory queries to identify the campaign ID strings, audience segment(s) of focus, and conversion event values
 2) REQUIRED UPDATE [1 OF 4]: Replace campaign_id_string and campaign_group placeholders with your campaign values and choice of groupings
 3) REQUIRED UPDATE [2 of 4]: Replace ad tag placeholders with Pixel IDs or your ad tags
 4) REQUIRED UPDATE [3 of 4]: Replace conversion_event_type_description placeholder with your chosen conversion event types
 5) REQUIRED UPDATE [4 of 4]: Replace audience segment ID number with the one corresponding to your focus audience
*/

WITH campaign_group (campaign_id_string, campaign_group) AS (
  VALUES  
    ('campaign_id_string', 'campaign_group')   -- REQUIRED UPDATE [1 OF 4]: Replace campaign_id_string and campaign_group placeholders
    ),
    
ad_tag (tracked_item) AS (VALUES ('tag1'), ('tag2')), -- REQUIRED UPDATE [2 of 4]: Replace ad tags with Pixel IDs and Amazon Ad Tags

conversion_event_type_description (conversion_event_type_description) AS (VALUES ('placeholder')), -- REQUIRED UPDATE [3 of 4]: Replace conversion_event_type_description placeholder

-- P2C Off-Amazon conversions

/* Query Start */

```

--------------------------------

### INNER JOIN Example

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

This query demonstrates how to use an INNER JOIN to combine impression and click data, filtering by campaign and grouping results. It returns only rows where matches exist in both tables.

```sql
-- Instructional Query: How to use INNER JOIN()--

SELECT
    I.campaign_id_string,
    I.campaign,
    SUM(impressions) AS impressions,
    SUM(C.clicks) AS clicks
FROM
    dsp_impressions I
JOIN 
    dsp_clicks C
ON 
    I.campaign_id_string = C.campaign_id_string
WHERE 
    I.campaign IN ('Campaign1', 'Campaign2')
GROUP BY
    1,
    2
```

--------------------------------

### CTE Syntax Example

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Illustrates the basic syntax for defining one or more Common Table Expressions (CTEs) before a terminal SELECT statement. Commas separate multiple CTE definitions.

```sql
WITH <cte_name1> 
AS (<query>),

<cte_name2> 
AS (<query>)

SELECT <column1>,<column2> 
FROM <cte_name>
```

--------------------------------

### Workaround for GETDATE() using CAST

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

AMC does not support the GETDATE() function. Use the CAST function to get the current date or datetime as a workaround. This example shows how to cast strings to DATE and TIMESTAMP types.

```sql
-- Instructional Query: How to use CAST as a workaround for GETDATE() --

SELECT
CAST('today' AS DATE) AS Date_for_today, --for date
CAST('now' as timestamp) AS DateTime_for_now --for datetime

```

--------------------------------

### Rank Impressions within Campaigns using PARTITION BY

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Illustrates the use of the PARTITION BY clause with the RANK() window function to assign a rank to line items based on their impressions within each campaign. This helps in analyzing performance metrics on a per-campaign basis.

```sql
-- Instructional Query: How to use RANK() with PARTITION --

WITH campaigns AS (
    SELECT
    campaign,
    line_item,
    SUM(impressions) as impressions
    FROM dsp_impressions
    GROUP BY 1,2
)

SELECT 
    campaign,
    line_item,
    impressions,
    RANK() OVER(
        PARTITION BY campaign
        ORDER BY impressions DESC) Impression_Rank
FROM 
    campaigns

```

--------------------------------

### Using IF() for conditional logic

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Use the IF() function to return different values based on a condition. This example categorizes campaigns by impression count.

```sql
-- Instructional Query: How to use IF() --

WITH camp_imp AS (

    SELECT 
        campaign, 
        COUNT(impressions) AS Impression_count
    FROM 
        amazon_attributed_events_by_conversion_time 
    GROUP BY 
        1
 )

SELECT 
    campaign, 
    IF(Impression_count<1000, 'less than 1000 impressions', 'greater than 1000 impressions') AS Impression_scale
FROM 
    camp_imp
```

--------------------------------

### Path to Conversion Query for DSP Campaigns

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Path to conversion by campaign_notebook.md

This SQL query analyzes the path to conversion for DSP campaigns. It includes logic to deduplicate impressions at the user ID level and calculates various metrics such as reach, impressions, cost, purchases, and sales amount. The query also provides a user purchase rate.

```sql
-- Instructional Query: Path to Conversion Campaign / DSP campaigns only--
/*Bug Fix (8/31/22): A new CTE, filter_impressions_dedupe has been added to dedupe values at user_id level in the filter_impressions CTE. Please refer to comments in the query to understand more. */


WITH impressions AS
(
SELECT 
    -- OPTIONAL UPDATE Use the following code if you want to rename your campaigns. Update campaign_id and campaign group name. Make sure to comment out the campaign in the next line
    /*  CASE campaign_id 
     WHEN 11111111111111 THEN 'Display_Awareness'
     WHEN (222222222222) THEN 'Display_Conversion'
     ELSE 'Other' 
     END AS campaign,
     */
    --OPTIONAL UPDATE remove the campaign row below if you are using the campaign grouping code above.
    campaign,
    user_id,
    min(impression_dt) impression_dt_first,
    SUM(impressions) AS impressions,
    SUM(total_cost) AS total_cost
FROM dsp_impressions
WHERE user_id IS NOT NULL -- AND campaign_id IN (11111111111111,222222222222)
GROUP BY 1,2
)

-- gather conversions --
, converted AS
(
    SELECT
    -- OPTIONAL UPDATE Use the following code if you want to rename your campaigns. Update campaign_id and campaign group name. Make sure to comment out the campaign in the next line
    /*  CASE campaign_id 
     WHEN 11111111111111 THEN 'Display_Awareness'
     WHEN 222222222222 THEN 'Display_Conversion'
     ELSE 'Other' 
     END AS campaign,
     */
    --OPTIONAL UPDATE remove the campaign row below if you are using the campaign grouping code above.
        campaign,
        user_id,
        max(conversion_event_dt) AS conversion_event_dt_last,
        SUM(product_sales) AS product_sales,
        SUM(purchases) AS purchases
FROM amazon_attributed_events_by_traffic_time
WHERE purchases > 0 and user_id is not null -- AND campaign_id IN (11111111111111,222222222222)
GROUP BY 1,2
)

-- only include impressions that happened before conversion event time -- 
, filter_impressions AS 
( 
    SELECT i.user_id AS imp_user_id,
           c.user_id AS pur_user_id, 
           i.campaign,
           i.impressions,
           i.impression_dt_first,
           i.total_cost,
           c.conversion_event_dt_last,
           c.product_sales,
           c.purchases
    FROM impressions i 
    LEFT JOIN converted c ON c.user_id = i.user_id AND c.campaign = i.campaign
    WHERE ((c.user_id IS NOT NULL AND i.impression_dt_first < c.conversion_event_dt_last) 
    OR c.user_id IS NULL) 
), 

-- order campaigns based on impression event time--
 ranked AS
(
SELECT
    NAMED_ROW('order', ROW_NUMBER() OVER(PARTITION BY imp_user_id ORDER BY impression_dt_first),'campaign', campaign) AS campaign_order,
    imp_user_id
FROM filter_impressions 
WHERE imp_user_id is not null)

-- create campaign path group by user --
, assembled AS
(
SELECT
    ARRAY_SORT(COLLECT(distinct campaign_order)) AS path,
    imp_user_id
FROM ranked 
GROUP BY imp_user_id
),

/* Bug Fix: Start of Section added on 8/31/22 to dedupe values at user_id level */
filter_impressions_dedupe AS (
  SELECT
    imp_user_id                             AS imp_user_id,
    SUM(impressions)                        AS impressions,
    SUM(total_cost)                         AS total_cost,
    MAX(IF(pur_user_id IS NOT NULL,1,0))    AS pur_user_id,
    MAX(product_sales)                      AS product_sales,
    MAX(purchases)                          AS purchases
  FROM
    filter_impressions
  GROUP BY
    imp_user_id
),
/* Bug Fix: End of Section added on 8/31/22 to dedupe values at user_id level */

-- assemble impressions, clicks and conversions --
  assembled_with_imp_conv AS
    (
        SELECT path,
                count(distinct a.imp_user_id) as reach,
                SUM(b.impressions) as impressions,
                -- impression_cost and total_cost are reported in millicents in the dsp_impressions table. To calculate the cost in dollars/your currency, divide the cost value by 100,000. 
                SUM(b.total_cost)/100000 as imp_total_cost,
                SUM(b.pur_user_id) as users_that_purchased,
                SUM(b.product_sales) as sales_amount,
                SUM(b.purchases) as purchases
    FROM assembled a
    LEFT JOIN filter_impressions_dedupe b /*Bug Fix: Updated on 8/31/22 to dedupe values at user_id level*/
           ON a.imp_user_id = b.imp_user_id
    GROUP BY path
    )

    SELECT
        path,
        reach AS path_occurrences,
        impressions,
        imp_total_cost,
        users_that_purchased,
        sales_amount,
        purchases,
        (users_that_purchased/reach) as user_purchase_rate
    FROM assembled_with_imp_conv
```

--------------------------------

### Bin Data by Impression Timing and Ad Product

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Amazon DSP Display, Streaming TV and Sponsored Products 3-way overlap_notebook.md

Aggregates impressions based on impression timing and ad product type, also retaining purchase information.

```sql
binned_data as (
SELECT
 imp_user
 , pur_user
  ,SUM(IF(impression_timing = 'no purchase' AND imp_ad_type = 'p1', impressions, 0)) as no_purch_p1_impressions
  ,SUM(IF(impression_timing = 'pre-purchase' AND imp_ad_type = 'p1', impressions, 0)) as pre_purch_p1_impressions
  ,SUM(IF(impression_timing = 'post-purchase' AND imp_ad_type = 'p1', impressions, 0)) as post_purch_p1_impressions
  ,SUM(IF(impression_timing = 'other' AND imp_ad_type = 'p1', impressions, 0)) as other_p1_impressions
  ,SUM(IF(imp_ad_type = 'p1', impressions, 0)) as total_p1_impressions
  ,SUM(IF(impression_timing = 'no purchase' AND imp_ad_type = 'p2', impressions, 0)) as no_purch_p2_impressions
  ,SUM(IF(impression_timing = 'pre-purchase' AND imp_ad_type = 'p2', impressions, 0)) as pre_purch_p2_impressions
  ,SUM(IF(impression_timing = 'post-purchase' AND imp_ad_type = 'p2', impressions, 0)) as post_purch_p2_impressions
  ,SUM(IF(impression_timing = 'other' AND imp_ad_type = 'p2', impressions, 0)) as other_p2_impressions
  ,SUM(IF(imp_ad_type = 'p2', impressions, 0)) as total_p2_impressions
  ,SUM(IF(impression_timing = 'no purchase' AND imp_ad_type = 'p3', impressions, 0)) as no_purch_p3_impressions
  ,SUM(IF(impression_timing = 'pre-purchase' AND imp_ad_type = 'p3', impressions, 0)) as pre_purch_p3_impressions
  ,SUM(IF(impression_timing = 'post-purchase' AND imp_ad_type = 'p3', impressions, 0)) as post_purch_p3_impressions
  ,SUM(IF(impression_timing = 'other' AND imp_ad_type = 'p3', impressions, 0)) as other_p3_impressions
  ,SUM(IF(imp_ad_type = 'p3', impressions, 0)) as total_p3_impressions
  ,MAX(purchases) as purchases
  ,MAX(p1_purchases) as p1_purchases
  ,MAX(p2_purchases) as P2_purchases
  ,MAX(p3_purchases) as p3_purchases
  ,MAX(sales) as sales
FROM combined
GROUP BY 1,2
)
```

--------------------------------

### SUM() Function Syntax

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Basic syntax for using the SUM() function to aggregate values in a column. Use this to get the total sum of a numeric column.

```sql
SELECT SUM( <column1>) AS column1
FROM <table>
```

--------------------------------

### Ranking Impressions for Campaign Path Assembly

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Path to conversion by campaign_notebook.md

Ranks impressions for each user based on the first impression event time. This ranking is crucial for assembling the ordered campaign path.

```sql
, ranked AS
(
SELECT
    NAMED_ROW('order', ROW_NUMBER() OVER (PARTITION BY f.imp_user_id ORDER BY f.impression_dt_first),'campaign', f.campaign) AS campaign_order,
    imp_user_id
FROM filter_impressions f
WHERE f.imp_user_id is not null)

-- create campaign path group by user --
, assembled AS
(
SELECT
    ARRAY_SORT(COLLECT(distinct a.campaign_order)) AS path,
    a.imp_user_id
FROM ranked a
GROUP BY a.imp_user_id)
```

--------------------------------

### Exploratory Query for Sponsored Ads Campaigns

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Net-new reach by campaign group_notebook.md

This query helps identify Sponsored Ads campaign IDs and their start dates, essential for calculating net-new reach.

```sql
-- Instructional Query: Exploratory Query for Net-new reach (Sponsored ads campaigns)
-- AD PRODUCTS: Sponsored ads

SELECT
  advertiser,
  campaign_id_string,
  campaign,
  campaign_start_date,
  campaign_end_date,
  sum(impressions) as impressions
FROM
  sponsored_ads_traffic
GROUP BY
  advertiser,
  campaign_id_string,
  campaign,
  campaign_start_date,
  campaign_end_date

```

--------------------------------

### Prepare Conversion Data

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Tentpole phase overlap_notebook.md

Prepares conversion data by calculating new-to-brand sales and detail page views, filtering by ASIN if specified. This CTE aggregates sales and view data per user.

```sql
WITH conversions_prep AS (
  SELECT
    user_id,
    MAX(IF(total_product_sales > 0, conversion_event_dt, NULL)) AS last_purchase_dt,
    MAX(IF(total_detail_page_view > 0, conversion_event_dt, NULL)) AS last_dpv_dt,
    MAX(ntb_flag) AS ntb_flag,
    SUM(IF(
      CUSTOM_PARAMETER('include_brand_halo') = 1,
      total_product_sales,
      new_to_brand_total_product_sales
    )) AS sales,
    SUM(IF(
      CUSTOM_PARAMETER('include_brand_halo') = 1,
      total_product_sales,
      new_to_brand_total_product_sales
    )) AS ntb_sales,
    IF(
      CUSTOM_PARAMETER('include_brand_halo') = 1,
      SUM(C.total_detail_page_view),
      SUM(C.detail_page_view)
    ) AS dpv
  FROM
    amazon_attributed_events_by_traffic_time C
    JOIN sa_campaign S ON C.campaign = S.campaign_name
  WHERE
    user_id IS NOT NULL
    AND (
      total_product_sales > 0
      OR total_detail_page_view > 0
    )
    AND IF(
      CUSTOM_PARAMETER('include_asin_filter') = 1,
      ARRAY_CONTAINS(
        CUSTOM_PARAMETER('asin_filter_list'),
        tracked_asin
      ),
      1 = 1
    )
  GROUP BY
    1,
    2,
    3
),
conversions AS (
  SELECT
    user_id,
    MAX(IF(sales > 0, conversion_event_dt, NULL)) AS last_purchase_dt,
    MAX(IF(dpv > 0, conversion_event_dt, NULL)) AS last_dpv_dt,
    MAX(ntb_flag) AS ntb_flag,
    SUM(sales) AS sales,
    SUM(ntb_sales) AS ntb_sales,
    SUM(dpv) AS dpv
  FROM
    conversions_prep
  GROUP BY
    1
)
```

--------------------------------

### Using COALESCE() to handle NULL values

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Use the COALESCE() function to return the first non-null value from a list. This example assigns 'DSP' to rows where ad_product_type is NULL.

```sql
-- Instructional Query: How to COALESCE()--

SELECT
    distinct(COALESCE (ad_product_type, 'DSP')) AS Ad_product_type
FROM
    amazon_attributed_events_by_conversion_time
```

--------------------------------

### On-Amazon Path to Conversion (Amazon DSP Campaigns)

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Path to conversion by audience segment_notebook.md

This query template analyzes the on-Amazon path to conversion for Amazon DSP campaigns. It requires updating campaign ID strings and audience segment IDs. Optional ASIN filters can be uncommented.

```sql
-- Instructional Query: Path to conversion by audience segment (On-Amazon)
-- AD PRODUCTS: Amazon DSP --

/*
 ------- Customization Instructions ------- 
 1) First, run the exploratory queries to identify the campaign ID strings and audience segment(s) of focus
 2) REQUIRED UPDATE [1 of 2]: Replace campaign_id_string and campaign_group placeholders with your campaign values and choice of groupings
 3) REQUIRED UPDATE [2 of 2]: Replace audience segment ID number with the one corresponding to your focus audience
 5) OPTIONAL UPDATE [1 of 1]: Uncomment indicated line to apply ASIN filters. Keep this line commented to skip filters 
*/

WITH campaign_group (campaign_id_string, campaign_group) AS (
  VALUES  
    ('campaign_id_string', 'campaign_group')  -- REQUIRED UPDATE [1 of 2]: Replace campaign_id_string and campaign_group
    ),
    

--P2C Purchases

/* Query Start */
    
impressions AS (
  SELECT
    COALESCE(g.campaign_group, 'DSP-Others') AS campaign_group,
    'DSP' AS product_type,
    user_id,
    MIN(impression_dt_utc) AS impression_dt_first,
    SUM(impressions) AS impressions,
    SUM(total_cost) AS total_cost
  FROM
    dsp_impressions i  INNER JOIN campaign_group dsp on dsp.campaign_id_string = i.campaign_id_string
    LEFT JOIN campaign_group G ON g.campaign_id_string = i.campaign_id_string
  WHERE
    user_id IS NOT NULL
  GROUP BY
    1,
    2,
    3 
),

impressions_segment as (
  SELECT 
    user_id
  FROM
  dsp_impressions
  WHERE 
    impressions > 0 
    AND user_id IN (select user_id from impressions group by 1) -- exposed users
    AND ARRAY_CONTAINS(user_behavior_segment_ids, '9999') -- REQUIRED UPDATE [2 of 2]: Replace audience segment ID number 
  GROUP BY 1
),


```

--------------------------------

### Using IN() to filter by multiple values

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Use the IN() function in a WHERE clause to filter results based on a list of possible values. This example selects campaigns with 'sponsored_products' or 'sponsored_display' ad types.

```sql
-- Instructional Query: How to use IN() --

SELECT 
    campaign,
    ad_product_type,
    SUM(purchases) AS purchases
FROM
    amazon_attributed_events_by_traffic_time
WHERE
    ad_product_type IN ('sponsored_products', 'sponsored_display')
GROUP BY
    1,
    2
```

--------------------------------

### Using CASE() in SELECT Statement

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Example of using the CASE() expression within a SELECT statement to categorize marketplace data. This helps in transforming raw data into more readable categories.

```sql
-- Instructional Query: How to use CASE()in SELECT --  

SELECT
    distinct(advertiser),
    conversion_event_date_utc,
    CASE
      WHEN conversion_event_marketplace_name = 'AMAZON.COM' THEN 'Online'
      WHEN conversion_event_marketplace_name = 'PRIME_NOW_US' THEN 'Online'
      WHEN conversion_event_marketplace_name = 'AMAZON_FRESH_STORES_US' THEN 'Offline'
      WHEN conversion_event_marketplace_name = 'WHOLE_FOODS_MARKET_US' THEN 'Offline'
    END AS marketplace,
    SUM(purchases) AS purchases
FROM
    amazon_attributed_events_by_conversion_time
WHERE
    purchases > 0
GROUP BY
    1,
    2,
    3
```

--------------------------------

### Calculate First Impressions Per User

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Amazon DSP Display, Streaming TV and Sponsored Products 3-way overlap_notebook.md

Aggregates impressions to find the first impression date and total impressions for each user and ad type.

```sql
impressions_first_total as
(
SELECT
user_id
,imp_ad_type
,min(impression_dt) as impression_dt_first
,sum(impressions) as impressions
from impressions
GROUP BY 1,2
)
```

--------------------------------

### Prepare Impressions Data

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Tentpole phase overlap_notebook.md

Aggregates impression data by user and strategy, calculating total impressions, cost, and duration. This is a preparatory step for further analysis.

```sql
impressions AS (
  SELECT
    user_id,
    strategy,
    MAX(duration_days) AS duration_days,
    MIN(impression_dt) AS first_impression_dt,
    SUM(impressions) AS impressions,
    SUM(cost) AS cost
  FROM
    impressions_prep
  GROUP BY
    1,
    2
)
```

--------------------------------

### Deterministic query with layered ORDER BY

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Shows how to make a query deterministic by adding more columns to the ORDER BY clause, ensuring consistent results across executions.

```sql
-- Instructional Query: Example of converting a non-determnistic query to deterministic by layering more columns in the ORDER BY --

WITH CTE (campaign_group, campaign_name) AS (
VALUES
('1', 'Campaign_A'),
('1', 'Campaign_B'),
('1', 'Campaign_C'),
('2', 'Campaign_D'),
('2', 'Campaign_E'),
('2', 'Campaign_F')
)
SELECT
ROW_NUMBER() OVER (
ORDER BY
campaign_group,
campaign_name
) AS row_id,
campaign_group,
campaign_name
FROM
CTE
```

--------------------------------

### Exploratory Query for Amazon DSP Campaigns

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Net-new reach by campaign group_notebook.md

Use this query to gather information about Amazon DSP campaigns, including their IDs and start dates, which are needed for net-new reach calculations.

```sql
-- Instructional Query: Exploratory Query for Net-new reach (Amazon DSP campaigns)
-- AD PRODUCTS: Amazon DSP

SELECT
  advertiser,
  campaign_id_string,
  campaign,
  campaign_start_date,
  campaign_end_date,
  sum(impressions) as impressions
FROM
  dsp_impressions
GROUP BY
  advertiser,
  campaign_id_string,
  campaign,
  campaign_start_date,
  campaign_end_date

```

--------------------------------

### Assemble Campaign Path for Each User

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Off-Amazon conversion path by ad product exposure_notebook.md

This section collects all distinct campaign groups associated with a user and sorts them to create a unique campaign path. This path represents the sequence of ad exposures.

```sql
assembled AS (
  SELECT
    imp_user_id,
    ARRAY_SORT(COLLECT(DISTINCT a.campaign_group)) AS path
  FROM
    ranked a
  GROUP BY
    1
)
```

--------------------------------

### Example of ROW_NUMBER() non-deterministic query

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Illustrates a non-deterministic query using ROW_NUMBER() without sufficient ORDER BY clauses. Running this multiple times may yield different row orders.

```sql
-- Instructional Query: Example of non-determnistic query --
WITH CTE (campaign_group, campaign_name) AS (
  VALUES
    ('1', 'Campaign_A'),
    ('1', 'Campaign_B'),
    ('1', 'Campaign_C'),
    ('2', 'Campaign_D'),
    ('2', 'Campaign_E'),
    ('2', 'Campaign_F')
)
SELECT
  ROW_NUMBER() OVER (
    ORDER BY
      campaign_group
  ) AS row_id,
  campaign_group,
  campaign_name
FROM
  CTE
```

--------------------------------

### Compare Purchase Path: First Search to Purchase (SQL)

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Compare purchase path of converters versus non-converters_notebook.md

This SQL query evaluates the differences in purchase paths between users who made a branded search and those who did not. It requires customization for Amazon DSP campaign groupings, IDs, and branded search keywords.

```sql
-- Instructional Query: Compare Purchase Path of Converters versus Non-Converters: First Search to Purchase --
-- AD PRODUCTS: Amazon DSP and/or sponsored ads
-- Enrollment (trial or subscription) to Flexible Shopping Insights (FSI) or Amazon Insights

/*
 ------- Customization Instructions -------
 Search REQUIRED UPDATE and enter information:
 1) REQUIRED UPDATE [1 of 3]: Enter Amazon DSP Campaign Groupings: Define the group of Amazon DSP campaigns by campaign type.
 2) REQUIRED UPDATE [2 of 3]: Enter Amazon DSP Campaign IDs: Insert campaign ids of defined Amazon DSP campaigns above.
 3) REQUIRED UPDATE [3 of 3]: Enter the branded search keyword. 
 */

WITH campaign_group (campaign_id_string, campaign_group) AS (
  /* REQUIRED UPDATE [1 of 3]: Enter Amazon DSP Campaign Groupings: Define the group of Amazon DSP campaigns by campaign type. */
  VALUES
    ('910000000000000000', 'OLV'),
    ('910000000000000000', 'DSP')
),

dsp_campaigns (campaign_id_string) AS (
  /* REQUIRED UPDATE [2 of 3]: Enter Amazon DSP Campaign IDs: Insert campaign ids of defined Amazon DSP campaigns above. */
  VALUES
    ('910000000000000000'),
    ('910000000000000000')
),

-- Grab Amazon DSP and sponsored ads Impressions
impressions AS (
  SELECT
    COALESCE(campaign_group, 'Amazon DSP') AS campaign_group
   ,'DSP' AS product_type
   , user_id
   , impression_dt_utc AS impression_dt
   , impressions AS impressions
  FROM
    dsp_impressions i
  INNER JOIN
     campaign_group G ON g.campaign_id_string = i.campaign_id_string
  WHERE
    user_id IS NOT NULL
  UNION ALL
   SELECT
     ad_product_type AS campaign_group
    ,'sponsored ads' AS product_type
    , user_id
    , event_dt_utc AS impression_dt
    , impressions AS impressions
   FROM
     sponsored_ads_traffic i
  WHERE
    user_id IS NOT NULL
),

-- Grab First Branded Searches
searches AS (
  SELECT
    user_id,
    MIN(event_dt_utc) as first_search
  FROM
    conversions_all
  WHERE
    user_id IS NOT NULL
  AND
     event_subtype = 'searchConversion'
  AND
     conversions > 0
  AND
   /* REQUIRED UPDATE [3 of 3]: Enter Keywords */
     (tracked_item SIMILAR TO '(?i)brand')
  GROUP BY
     1
),

-- Grab First Purchase event
converted AS (
  SELECT
   user_id,
   MIN(event_dt_utc) as first_purchase_dt
  FROM
   conversions_all
  WHERE
   user_id IS NOT NULL
  AND
   event_type_description = 'Product purchased'
  AND
    event_category = 'purchase'
  AND
    conversions > 0
  GROUP BY
    1
),

-- Determine first search prior to first purchase
searches_combined AS (
   SELECT
         s.user_id AS search_id
       , s.first_search
       , c.user_id AS conversion_id
       , c.first_purchase_dt
       , CASE WHEN c.user_id IS NULL THEN 'No Purchase'
         ELSE 'Purchase'
         END as customer_type
    FROM
         searches s
    LEFT JOIN
         converted c ON s.user_id = c.user_id
    WHERE
        (c.user_id IS NOT NULL AND s.first_search < c.first_purchase_dt)
    OR
        c.user_id IS NULL
),

-- Determine days between first search and purchase
search_days AS (
     SELECT
         search_id
       , first_search
       , first_purchase_dt
       , customer_type
       , seconds_between(first_search,first_purchase_dt) AS days_diff_search
     FROM
         searches_combined
    WHERE
         customer_type = 'Purchase'
)


```

--------------------------------

### Compute User Strategy Exposure (Purchase)

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Tentpole phase overlap_notebook.md

Calculates the unique Tentpole strategies a user was exposed to before making a purchase. This helps understand the path to conversion.

```sql
user_strategy_purchase AS (
  SELECT
    imp_user_id AS imp_user_id,
    -- Note: purchase_strategy is defined as the unique path comprising Phase One, Phase Two, and Phase Three that the user was exposed to before they made a purchase.
    ARRAY_SORT(COLLECT(DISTINCT strategy)) AS purchase_strategy,
    SUM(duration_days) AS purchase_duration_days
  FROM
    combined_prep
  WHERE
    impression_timing_purchase IN ('NO PURCHASE', 'PRE-PURCHASE')
  GROUP BY
    1
)
```

--------------------------------

### Query to Find Campaigns Starting with 'Kit'

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

This query demonstrates how to use the LIKE operator with a wildcard (%) to find all campaigns whose names begin with 'Kit'. Ensure the 'dsp_impressions' table is available.

```sql
-- Instructional Query: How to use LIKE operator --
-- The query below will return all campaigns that begin with "Kit" --

SELECT 
    campaign,
    campaign_id_string,
    SUM(impressions) AS impressions
FROM
    dsp_impressions
WHERE
 campaign LIKE 'Kit%' -- % represents zero or more characters

GROUP BY
    1,
    2
```

--------------------------------

### Using CASE() in WHERE Clause

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Example of using the CASE() expression within a WHERE clause to filter data based on conditional logic applied to marketplace and device type. This allows for complex filtering scenarios.

```sql
-- Instructional Query: How to use CASE()in WHERE clause --      

SELECT 
    distinct(advertiser),
    device_type,
    conversion_event_date_utc,
    conversion_event_marketplace_name,
    SUM(purchases) AS purchases
FROM
    amazon_attributed_events_by_conversion_time
WHERE
    purchases > 0
AND
   CASE
      WHEN conversion_event_marketplace_name = 'AMAZON.COM' THEN device_type = 'PC'
      WHEN conversion_event_marketplace_name = 'PRIME_NOW_US' THEN device_type = 'Mobile'
      WHEN conversion_event_marketplace_name = 'AMAZON_FRESH_STORES_US' THEN device_type = Null
      WHEN conversion_event_marketplace_name = 'WHOLE_FOODS_MARKET_US' THEN device_type = Null
    END  
GROUP BY
    1,
    2,
    3,
    4
```

--------------------------------

### Calculate Sample Standard Deviation and Variance

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

Use STDDEV() and VARIANCE() (synonyms for STDDEV_SAMP() and VAR_SAMP()) to calculate the standard deviation and variance for a sample set of data. This example uses a small, predefined dataset.

```sql
-- Instructional Query: How to use STDDEV(), VARIANCE() --

-- Test data below is a sample of the population for illustrative purposes. 
WITH test_data(test) AS (
  VALUES
  
    (20), 
    (45), 
    (30), 
    (66), 
    (50), 
    (60), 
    (85), 
    (70)

)
SELECT
  STDDEV(test) as STDDEV_sample_stardard_deviation,
  VARIANCE(test) as VARIANCE_sample_variance

    
FROM
  test_data

```

--------------------------------

### Path to Conversion Analysis Query Template

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Amazon Brand Store Insights in AMC_notebook.md

This SQL query template helps analyze customer paths to conversion by integrating data from Amazon DSP, Sponsored Brands, and Brand Store page views. Customize campaign IDs, objectives, and include additional sponsored ad touchpoints as needed.

```sql
-- Instructional Query: Amazon Brand Store Insights - Path to Conversion analysis --
-- Enrollment (trial or subscription) to Amazon Brand Store Insights is required-- 
-- Please follow through the instrucutions in comments to customize this insight
-- AD PRODUCTS: Sponsored Brands and Amazon DSP (Display and Video)
WITH 
/* Start with a grouping of DSP ad exposure */
relevant_dsp_impressions AS (
  SELECT
    creative_category,
    user_id,
    MIN(impression_dt) AS impression_dt_first,
    MAX(impression_dt) AS impression_dt_last,
    SUM(impressions) AS impressions,
    SUM(total_cost) AS total_cost
  FROM
    dsp_impressions i 
  WHERE
    user_id IS NOT NULL 
    AND creative_category in ('Video', 'Display')
  GROUP BY
    1,
    2
),
impressions AS (
  SELECT
    'DSP-Video' AS campaign_group,
    'DSP' AS product_type,
    user_id,
    impression_dt_first,
    impression_dt_last,
    impressions,
    total_cost
  FROM
    relevant_dsp_impressions 
  WHERE
    creative_category = 'Video' 
  -- DSP display creative ad exposure Section START
  UNION ALL
  SELECT
    'DSP-Display' AS campaign_group,
    'DSP' AS product_type,
    user_id,
    impression_dt_first,
    impression_dt_last,
    impressions,
    total_cost
  FROM
    relevant_dsp_impressions 
  WHERE
    creative_category = 'Display' -- DSP Display Section END
    -- Brand Store page visits START
  UNION ALL
  SELECT
    'BSI' AS campaign_group,
    'BSI' AS product_type,
    user_id,
    MIN(event_dt_utc) AS impression_dt_first,
    MAX(event_dt_utc) AS impression_dt_last,
    COUNT(distinct visit_id) AS impressions,
    SUM(0) AS total_cost -- there is no cost associated with a Brand Store page visit, this is earned media and not paid media
  FROM
    amazon_brand_store_page_views
  WHERE
    user_id IS NOT NULL
  GROUP BY
    1,
    2,
    3 -- Brand Store page visits END
    -- Sponsored Brands Section START
  UNION ALL
  SELECT
    'SB-Others' AS campaign_group,
    'SB' AS product_type,
    user_id,
    MIN(event_dt_utc) AS impression_dt_first,
    MAX(event_dt_utc) AS impression_dt_last,
    SUM(impressions) AS impressions,
    SUM(spend) AS total_cost
  FROM
    sponsored_ads_traffic a 
  WHERE
    user_id IS NOT NULL
    AND ad_product_type = 'sponsored_brands'
  GROUP BY
    1,
    2,
    3 -- Sponsored Brands Section END
),
-- gather conversions --
converted AS (
  SELECT
    user_id,
    MAX(conversion_event_dt) AS conversion_event_dt_last,
    -- Sales metrics
    SUM(
      IF(
        ad_product_type IS NULL,
        product_sales,
        total_product_sales
      )
    ) AS product_sales,
    -- replace product_sales with total_product_sales to include brand halo sales from DSP
    SUM(
      IF(ad_product_type IS NULL, purchases, total_purchases)
    ) AS purchases,
    -- replace purchases with total_purchases to include brand halo purchases from DSP
    -- NTB sales metrics
    MAX(IF(new_to_brand = TRUE, 1, 0)) AS ntb_cust,
    SUM(
      IF(
        ad_product_type IS NULL,
        new_to_brand_product_sales,
        new_to_brand_total_product_sales
      )
    ) AS ntb_product_sales,
    -- replace new_to_brand_product_sales with new_to_brand_total_product_sales to include brand halo sales from DSP
    SUM(
      IF(
        ad_product_type IS NULL,
        new_to_brand_purchases,
        new_to_brand_total_purchases
      )
    ) AS ntb_purchases -- replace new_to_brand_purchases with new_to_brand_total_purchases to include brand halo purchases from DSP
  FROM
    amazon_attributed_events_by_traffic_time
  WHERE
    IF(ad_product_type IS NULL, purchases, total_purchases) > 0 -- replace purchases with total_purchases to include brand halo conversions from DSP
    AND user_id IS NOT NULL 
    -- the AMC user has the option to apply inline ASIN filtering using tracked_item or tracked_asin columns if desired for the analysis
  GROUP BY
    1
),
-- only include impressions that happened before conversion event time --
filter_impressions AS (
  SELECT
    i.user_id AS imp_user_id,
    c.user_id AS pur_user_id,
    i.campaign_group,
    i.impressions,
    i.impression_dt_first,
    i.impression_dt_last,
    i.product_type,
    -- DSP cost is reported in millicents. To calculate the cost in dollars/your currency, divide the cost value by 100,000 (1e5). sponsored ads spend is reported as microcents. Divide by 100,000,000 (1e8) to get the cost in dollars/your currency.
    IF(
      i.product_type = 'DSP',
      (i.total_cost / 1e5),
      (i.total_cost / 1e8)

```

--------------------------------

### Cross Join Example in AMC SQL

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Introduction to AMC SQL_notebook.md

This query shows how to use a CROSS JOIN to retrieve all possible ASIN combinations that can be purchased together. It first creates a CTE of distinct ASINs with purchases and then joins the CTE with itself.

```sql
-- Instructional Query: How to use CROSS JOIN --
-- Use Case: Retrieve all possible ASIN combinations that can be purchased together --

WITH asin_CTE AS (
    SELECT 
        distinct(tracked_asin) as asin
    FROM
        amazon_attributed_events_by_conversion_time
    WHERE
        purchases > 0
    AND user_id IS NOT NULL
)

SELECT 
    A.asin as Asin1, B.asin as Asin2
FROM 
    asin_CTE A
CROSS JOIN 
    asin_CTE B
WHERE 
    A.asin <> B.asin

```

--------------------------------

### Sponsored Products Campaigns Configuration

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Custom attribution - first touch_notebook.md

Define Sponsored Products campaign names for attribution. Use the provided query structure if you do not have existing campaigns.

```sql
sp_campaigns (campaign) AS (
 /* REQUIRED UPDATE: Add sponsored ads campaign names. Use the query below if you do not have sponsored ads campaigns. */
 VALUES
 ('campaign_name_1'),
 ('campaign_name_2'),
 ('campaign_name_3'),
 ('campaign_name_4')
)
```

--------------------------------

### Exploratory Query for Sponsored Display and Sponsored Products Overlap

Source: https://github.com/idealerror/amazon-ads-amc-use-cases/blob/main/Use case: Sponsored Products and Sponsored Display overlap_notebook.md

Use this query to get a high-level overview of purchases attributed to Sponsored Display and Sponsored Products. It aggregates total purchases by campaign and ad product type.

```sql
-- Instructional Query: Exploratory Query for Sponsored Display and Sponsored Products Overlap --
SELECT
  campaign,
  ad_product_type,
  SUM(purchases) AS purchases
FROM
  amazon_attributed_events_by_traffic_time
WHERE
  ad_product_type IN ('sponsored_products', 'sponsored_display') 
  -- OPTIONAL: UPDATE the list below to only retrieve campaigns that tracked a particular set of ASINs.
  -- AND tracked_asin in ('ASIN1234', 'ASIN5678')
GROUP BY
  1,
  2

```