• 日本語
  • English

    • AMC

    Entry ASIN別に継続購入の買い回りを集計して合算する

    by

    継続購入の買い回りをみてみる

    
--以下２つのEntry ASINそれぞれに集計した後にUNION ALLで合算する
--entryasin1
--entryasin2
-- allasinlist というテーブルを別途作成しアップロードしておいて販売してる全てのasinをリスト化しておく。自社で販売しているasinに限定してCustomer Lifetime Valueを計算する
 
WITH
  initial_purchases_1 AS (
    SELECT
      user_id,
      MIN(
        CONVERT_TIME_ZONE_FROM_UTC (event_date_utc, 'Asia/Tokyo')
      ) AS first_purchase_date
    FROM
      TABLE (
        EXTEND_TIME_WINDOW ('conversions_all', 'P300D', 'P1D')
      )
    WHERE
      new_to_brand = TRUE
      AND tracked_asin = 'entryasin1'
      AND CONVERT_TIME_ZONE_FROM_UTC (event_date_utc, 'Asia/Tokyo') >= CAST('2024-01-01' AS TIMESTAMP)
      AND CONVERT_TIME_ZONE_FROM_UTC (event_date_utc, 'Asia/Tokyo') < CAST('2024-03-01' AS TIMESTAMP)
    GROUP BY
      user_id
  ),
  repeat_purchases_1 AS (
    SELECT
      r.user_id,
      r.tracked_asin AS repeat_asin,
      'entryasin1' AS initial_asin,
      SUM(r.total_product_sales) AS total_repeat_revenue,
      SUM(r.total_units_sold) AS total_repeat_units
    FROM
      TABLE (
        EXTEND_TIME_WINDOW ('conversions_all', 'P300D', 'P1D')
      ) r
      JOIN initial_purchases_1 i ON r.user_id = i.user_id
      JOIN allasinlist p ON r.tracked_asin = p.asin
    WHERE
      r.new_to_brand = FALSE
      AND SECONDS_BETWEEN (
        i.first_purchase_date,
        CONVERT_TIME_ZONE_FROM_UTC (r.event_date_utc, 'Asia/Tokyo')
      ) > 0
      AND SECONDS_BETWEEN (
        i.first_purchase_date,
        CONVERT_TIME_ZONE_FROM_UTC (r.event_date_utc, 'Asia/Tokyo')
      ) <= 231 * 24 * 3600
      AND r.total_product_sales > 0
    GROUP BY
      r.user_id,
      r.tracked_asin
  ),
  initial_purchases_2 AS (
    SELECT
      user_id,
      MIN(
        CONVERT_TIME_ZONE_FROM_UTC (event_date_utc, 'Asia/Tokyo')
      ) AS first_purchase_date
    FROM
      TABLE (
        EXTEND_TIME_WINDOW ('conversions_all', 'P300D', 'P1D')
      )
    WHERE
      new_to_brand = TRUE
      AND tracked_asin = 'entryasin2'
      AND CONVERT_TIME_ZONE_FROM_UTC (event_date_utc, 'Asia/Tokyo') >= CAST('2024-01-01' AS TIMESTAMP)
      AND CONVERT_TIME_ZONE_FROM_UTC (event_date_utc, 'Asia/Tokyo') < CAST('2024-03-01' AS TIMESTAMP)
    GROUP BY
      user_id
  ),
  repeat_purchases_2 AS (
    SELECT
      r.user_id,
      r.tracked_asin AS repeat_asin,
      'entryasin2' AS initial_asin,
      SUM(r.total_product_sales) AS total_repeat_revenue,
      SUM(r.total_units_sold) AS total_repeat_units
    FROM
      TABLE (
        EXTEND_TIME_WINDOW ('conversions_all', 'P300D', 'P1D')
      ) r
      JOIN initial_purchases_2 i ON r.user_id = i.user_id
      JOIN allasinlist p ON r.tracked_asin = p.asin
    WHERE
      r.new_to_brand = FALSE
      AND SECONDS_BETWEEN (
        i.first_purchase_date,
        CONVERT_TIME_ZONE_FROM_UTC (r.event_date_utc, 'Asia/Tokyo')
      ) > 0
      AND SECONDS_BETWEEN (
        i.first_purchase_date,
        CONVERT_TIME_ZONE_FROM_UTC (r.event_date_utc, 'Asia/Tokyo')
      ) <= 231 * 24 * 3600
      AND r.total_product_sales > 0
    GROUP BY
      r.user_id,
      r.tracked_asin
  )
SELECT
  initial_asin,
  repeat_asin,
  SUM(total_repeat_revenue) AS total_repeat_revenue,
  SUM(total_repeat_units) AS total_repeat_units
FROM
  repeat_purchases_1
GROUP BY
  initial_asin,
  repeat_asin
UNION ALL
SELECT
  initial_asin,
  repeat_asin,
  SUM(total_repeat_revenue) AS total_repeat_revenue,
  SUM(total_repeat_units) AS total_repeat_units
FROM
  repeat_purchases_2
GROUP BY
  initial_asin,
  repeat_asin