閾値の課題 | aggregation threshold

AMCのSQLを実行する上で必ず出くわすのが閾値の問題です。英語で言うとaggregation_thresholdなどと呼ばれAMC公式にも解説があります。これはAMCのプライバシー保護の観点から演算の対象となるデータが１００個とか未満になる場合に結果を戻さないというものです。

たとえば以下はクエリーでASIN =’asinabc123′ 新規にローンチした商品の購買を週次でこのブランドを新規で買った人と継続で買った場合にわけ週次の個数を集計するものです。ただアウトプット以下をみるとわかりますが、ご覧の通りweek_startの３行目と４行目が空白で歯抜けなってますが、これは対象となる値が70だったり1だったりした場合にweek_startのラベルを戻さない、空欄で戻す、みたいな仕様で起こります。この場合は設定した集計期間（クエリの外側）が2024-03-03からだったため、70の行は2024-03-03T00:00:00.000Zだろうと想像することができ、実務上はマニュアルで補完してのりきってます。

SQL

SELECT
  DATE_TRUNC ('WEEK', event_dt_utc) AS week_start,
  SUM(
    CASE
      WHEN new_to_brand = TRUE THEN total_units_sold
      ELSE 0
    END
  ) AS ntb_units_purchased,
  SUM(
    CASE
      WHEN new_to_brand = TRUE THEN 0
      ELSE total_units_sold
    END
  ) AS non_ntb_units_purchased,
  SUM(total_units_sold) AS total_units_purchased
FROM
  conversions_all
WHERE
  event_category = 'purchase'
  AND tracked_asin = 'asinabc123'
  AND total_units_sold > 0
  AND user_id IS NOT NULL
GROUP BY
  DATE_TRUNC ('WEEK', event_dt_utc)
 

実際には、もっと長い期間になってくると、この空欄が複数でてきてどの週の値なのかわからない状態となり結果分析がつかえないという状況に陥ります。新規にローンチした商材などは当然立ち上げ時期は対象数がすくないため起こりがちです。そこで考えた対処策は週次の集計をto_dateにして、開始からその週までの合算値を１のクエリーでだす。つまり、５週経過したのなら、1週のクエリー、２週までの合算クエリー、。。。５週までの合算クエリーという風にすれば、合算なので対象の数は増えるのみ閾値問題を回避することができます。集計結果はそれぞれダウンロードしてきて、エクセルで引き算して差分を出すイメージです。もちろん実際エクセルでその引き算は大変ですから、python(＜＝AMCの場合こんなところで重宝してます）をつかって編集してます。そうすると一瞬で週次の表に変換することができややマニュアルを伴いますがさほど負荷なく週次のレポートが作成できてます。