Amazon Marketing Streamによって配信されるトラフィックデータとコンバージョンデータの集計と結合

Amazon Marketing Streamでは少なくとも1回のメッセージ配信が保証されるため、メッセージの中には複数回送信されるものもあります。このため、トラフィックデータセットとコンバージョンデータセットでは、idempotency_idというフィールドを使用します。これらのデータセットのデルタレコードを正確に合計するには、集計前に重複レコードを削除する必要があります。

操作の順序

最も正確な結果を得るには、以下の順序で手順を実行する必要があります。

1. idempotency_idキーを使用して重複を識別します。
2. トラフィックテーブルの重複を集計して削除します。
3. コンバージョンテーブルの重複を集計して削除します。
4. トラフィックテーブルとコンバージョンテーブルを結合します。

実装例

以下のコードサンプルは、sp-trafficデータセットとsp-conversionデータセットに対してこの順序の操作を実行する方法を示しています。他の広告プログラムのトラフィックデータセットとコンバージョンデータセットのスキーマは若干異なる場合があることに注意してください。利用可能な各データセットのスキーマを確認するには、データガイドをご覧ください。

注

この例では、row_number()というウィンドウ関数を使用してidempotency_idキーを使用し、重複を識別します。ウィンドウ関数の詳細についてはこちらをご覧ください。

-- Identify duplicates in both tables

with sp_conv as
(
    SELECT *, row_number() OVER (PARTITION BY idempotency_id ORDER BY idempotency_id ASC) AS rn
    FROM [INSERT CONVERSION TABLE HERE]

   --Input initial WHERE filter criteria here.
),
sp_traff as
(
    SELECT *, row_number() OVER (PARTITION BY idempotency_id ORDER BY idempotency_id ASC) AS rn
    FROM [INSERT TRAFFIC TABLE HERE]

    --Input initial WHERE filter criteria here.
),

-- Aggregate and remove duplicates from sp-conversions

sp_conv_agg as
(
    SELECT    "advertiser_id"
            , "marketplace_id"
            , "time_window_start"
            , "campaign_id"
            , "ad_group_id"
            , "ad_id"
            , "keyword_id"
            , "placement"
            , "currency"
            , sum("attributed_sales_1d") as "attributed_sales_1d"
            , sum("attributed_sales_1d_same_sku") as "attributed_sales_1d_same_sku"
            , sum("attributed_sales_7d") as "attributed_sales_7d"
            , sum("attributed_sales_7d_same_sku") as "attributed_sales_7d_same_sku"
            , sum("attributed_sales_14d") as "attributed_sales_14d"
            , sum("attributed_sales_14d_same_sku") as "attributed_sales_14d_same_sku"
            , sum("attributed_sales_30d") as "attributed_sales_30d"
            , sum("attributed_sales_30d_same_sku") as "attributed_sales_30d_same_sku"
            , sum("attributed_conversions_1d") as "attributed_conversions_1d"
            , sum("attributed_conversions_1d_same_sku") as "attributed_conversions_1d_same_sku"
            , sum("attributed_conversions_7d") as "attributed_conversions_7d"
            , sum("attributed_conversions_14d_same_sku") as "attributed_conversions_14d_same_sku"
            , sum("attributed_conversions_30d") as "attributed_conversions_30d"
            , sum("attributed_conversions_30d_same_sku") as "attributed_conversions_30d_same_sku"
            , sum("attributed_units_ordered_1d") as "attributed_units_ordered_1d"
            , sum("attributed_units_ordered_1d_same_sku") as "attributed_units_ordered_1d_same_sku"
            , sum("attributed_units_ordered_7d") as "attributed_units_ordered_7d"
            , sum("attributed_units_ordered_7d_same_sku") as "attributed_units_ordered_7d_same_sku"
            , sum("attributed_units_ordered_14d") as "attributed_units_ordered_14d"
            , sum("attributed_units_ordered_14d_same_sku") as "attributed_units_ordered_14d_same_sku"
            , sum("attributed_units_ordered_30d") as "attributed_units_ordered_30d"
            , sum("attributed_units_ordered_30d_same_sku") as "attributed_units_ordered_30d_same_sku"
    FROM sp_conv

    --Ensures only 1 unique idempotency_id record is used (in the event of duplicates)
    WHERE rn = 1

    --GROUP BY all the keys identified above
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

),

-- Aggregate and remove duplicates from sp-traffic

sp_traff_agg as
(
    SELECT    "advertiser_id"
            , "marketplace_id"
            , "time_window_start"
            , "campaign_id"
            , "ad_group_id"
            , "ad_id"
            , "keyword_id"
            , "keyword_text"
            , "placement"
            , "currency"
            , sum("cost") as "cost"
            , sum("impressions") as "impressions"
            , sum("clicks") as "clicks"
    FROM sp_traff

    --Ensures only 1 unique idempotency_id record is used (in the event of duplicates)
    WHERE rn = 1

    --GROUP BY all the keys identified above
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
),

-- Join sp-traffic and sp-conversion

SELECT    spt."advertiser_id"
        , spt."marketplace_id"
        , spt."time_window_start"
        , spt."campaign_id"
        , spt."ad_group_id"
        , spt."ad_id"
        , spt."keyword_id"
        , spt."keyword_text"
        , spt."placement"
        , spt."currency"
        , spt."cost"
        , spt."impressions"
        , spt."clicks"
        , spc."attributed_sales_1d"
        , spc."attributed_sales_1d_same_sku"
        , spc."attributed_sales_7d"
        , spc."attributed_sales_7d_same_sku"
        , spc."attributed_sales_14d"
        , spc."attributed_sales_14d_same_sku"
        , spc."attributed_sales_30d"
        , spc."attributed_sales_30d_same_sku"
        , spc."attributed_conversions_1d"
        , spc."attributed_conversions_1d_same_sku"
        , spc."attributed_conversions_7d"
        , spc."attributed_conversions_14d_same_sku"
        , spc."attributed_conversions_30d"
        , spc."attributed_conversions_30d_same_sku"
        , spc."attributed_units_ordered_1d"
        , spc."attributed_units_ordered_1d_same_sku"
        , spc."attributed_units_ordered_7d"
        , spc."attributed_units_ordered_7d_same_sku"
        , spc."attributed_units_ordered_14d"
        , spc."attributed_units_ordered_14d_same_sku"
        , spc."attributed_units_ordered_30d"
        , spc."attributed_units_ordered_30d_same_sku"
FROM sp_traff_agg spt
LEFT JOIN sp_conv_agg spc

--START OF JOIN KEY LOGIC --
ON  spt.advertiser_id   = spc.advertiser_id
AND spt.marketplace_id  = spc.marketplace_id
AND spt.campaign_id     = spc.campaign_id
AND spt.ad_group_id     = spc.ad_group_id
AND spt.ad_id           = spc.ad_id
AND spt.keyword_id      = spc.keyword_id
AND spt.placement       = spc.placement
AND spt.time_window_start = spc.time_window_start
AND spt.currency        = spc.currency
--END OF JOIN KEY LOGIC --

--Add any additional WHERE filtering criteria here

--Change ORDER clause to desired order
ORDER BY time_window_start

警告

正確な結果を得るには、フィルター条件がCTEの各レベル間で一致する必要があります。上記の例では、sp_convフィルター条件はsp_traffフィルター条件と一致する必要があります。同様に、集計された2つのCTE（sp_traffic_aggとsp_conv_agg）にも同じフィルタリング条件が必要です。一般に、コマンドチェーンの上位で事前フィルタリングを行うと、パフォーマンスが向上します。エラーの可能性を減らすために、CTE間で共有されるすべてのフィルターをパラメーター化することをおすすめします。