Entry ASIN別にLTV Customer Lifetime Value (CLTV)算出

--４ステップ展開
--初回購入日から各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;