entry asin別に一定期間（６週間とか）初回購入以降の継続購入を売上、個数で集計。
時間が日本時間になっていないためCONVERT_TIME_ZONE_FROM_UTC(event_date_utc, ‘Asia/Tokyo’)をつかって日本時間に設定して全てを計算。
--４ステップ展開
--初回購入日から各user別に同じ日数で継続購入を集計するSQL文です
--初回購入で評価したasinを限定列挙しておく。ここでは仮に
entryasin1
entryasin2
entryasin3
entryasin4
の４つを想定（SQL文ではasinの値を''で囲んでください、↓例示）
--自社で販売しているasinをリスト化してallasinlist というテーブルをアップロードしておく
WITH initial_purchases AS (
SELECT
user_id,
tracked_asin AS initial_asin, -- Track the ASIN for grouping purposes
CAST(CONVERT_TIME_ZONE_FROM_UTC(MIN(event_date_utc), 'Asia/Tokyo') AS TIMESTAMP) AS first_purchase_date,
COALESCE(SUM(total_product_sales), 0) AS total_initial_revenue, -- Sum of revenue for this ASIN
COALESCE(SUM(total_units_sold), 0) AS initial_unit_count -- Sum of units sold for initial purchases
FROM
TABLE(EXTEND_TIME_WINDOW('conversions_all', 'P60D', 'P1D')) -- Keep window for Jan-Feb
WHERE
new_to_brand = TRUE
AND CONVERT_TIME_ZONE_FROM_UTC(event_date_utc, 'Asia/Tokyo') BETWEEN '2024-01-01' AND '2024-02-29'
AND tracked_asin IN ('entryasin1', 'entryasin2', 'entryasin3', 'entryasin4') -- Filter for specific ASINs
GROUP BY
user_id, tracked_asin -- Group by user_id and tracked_asin to ensure breakdown by ASIN
),
-- Step 2: Repeat purchases with unit count, ensuring > 0 revenue for repeat purchases
repeat_purchases AS (
SELECT
r.user_id,
i.initial_asin, -- Include initial ASIN for grouping purposes
COALESCE(SUM(r.total_product_sales), 0) AS total_repeat_revenue,
COALESCE(SUM(r.total_units_sold), 0) AS repeat_unit_count -- Sum of units sold for repeat purchases
FROM
TABLE(EXTEND_TIME_WINDOW('conversions_all', 'P300D', 'P1D')) r -- Extended time window for repeat purchases
JOIN
initial_purchases i ON r.user_id = i.user_id -- Join on user_id
JOIN
allasinlist p ON r.tracked_asin = p.asin -- Filter repeat purchases by ASINs in external table
WHERE
r.new_to_brand = FALSE
AND CONVERT_TIME_ZONE_FROM_UTC(r.event_date_utc, 'Asia/Tokyo') >= i.first_purchase_date
AND CONVERT_TIME_ZONE_FROM_UTC(r.event_date_utc, 'Asia/Tokyo') <= '2024-11-25'
AND r.total_product_sales > 0 -- Only include purchases with non-zero revenue
GROUP BY
r.user_id, i.initial_asin -- Group by user_id and initial ASIN
)
-- Step 3: Final output with ASIN breakdown using COUNT(DISTINCT)
SELECT
i.initial_asin, -- Group results by ASIN
COUNT(DISTINCT i.user_id) AS initial_user_count, -- Exact count of distinct users for initial purchases
COALESCE(SUM(i.total_initial_revenue), 0) AS total_initial_revenue, -- Total initial revenue
COALESCE(SUM(i.initial_unit_count), 0) AS total_initial_units, -- Total initial units sold
COALESCE(SUM(r.total_repeat_revenue), 0) AS total_repeat_revenue, -- Total repeat revenue
COALESCE(SUM(r.repeat_unit_count), 0) AS total_repeat_units, -- Total repeat units sold
COUNT(DISTINCT r.user_id) AS repeat_user_count -- Exact count of distinct users for repeat purchases
FROM
initial_purchases i
LEFT JOIN
repeat_purchases r ON i.user_id = r.user_id AND i.initial_asin = r.initial_asin -- Join on user_id and ASIN
WHERE
i.initial_asin IS NOT NULL
GROUP BY
i.initial_asin -- Group by initial ASIN;
Amazon Marketing Cloud Practitioner