こんにちは、Customer EngineerのShinです！

さて、ECサイトなどにおいてランキング表示することが昨今多くなっていますが、あれは論文でも実証されている購買を促進させる効果があるものなのです。

特に1位の商品は他の順位よりも売り上げの増加につながり、他の順位に関しては概ね同じ効果を持つとのことです。（参考：https://www.jc.u-aizu.ac.jp/news/management/gr/2020/10.pdf）

ただ、数万点の商品・数万人が購買をするサービスにおいて、毎月そういったランキング関連の施策を作るのは相当なデータ活用をしなければなりませんし、何より面倒です。

そこで、今回は、購入数の商品ランキングのメール配信を毎月自動実行する方法について紹介しようと思います。

前提として、今回の施策は購買ができるeコマースを想定します。ただ、人材・学習系サービスなど何かしらエンドユーザーにとって魅力のある商品があり、ランキング化できるものであれば効果のある施策となるでしょう。

全体像

処理の流れ

実現する処理の全体像は、次の通りです。

サービス内でユーザーが購入- すると、KARTEのbuyイベントが発火します。このイベントデータをKARTE Datahubで整形してランキング化
外部からKARTE Datahubに商品データを取り込んで、ランキング情報・ユーザーデータと紐付け
最後にKARTE Messageでメール配信する内容を作り、月に1度定期実行

という流れになっています。

最終アウトプット

以下のような1〜6位のランキングにして商品情報を含めてメール施策を作ります。（2位以下は見切れております。ご了承ください。）

利用するKARTEのツール・必要な知識

利用するKARTEのツール
必要な知識
- SQL
- 基礎的HTML・CSS

前提

今回作成する「全ユーザーの購入数に応じて、商品をランキング順にし、KARTE Messageで配信する」方法に関して以下が出来ていることが前提となります。

ランキング化する元の情報になるイベント（購買イベント）が実装されていること
- 詳しくはイベント概要を見てください
商品マスタをKARTE Datahubにインポートされていること
- 詳しくは「商品マスタをDatahubにインポートする」を見てください。
ユーザーを一意に特定するuser_idと配信先カラムのemailが入った顧客データのリストがKARTE Datahubに存在すること
- 詳しくは、外部サービス毎のデータ連携作業をするを見てください。

実装手順

実装の手順は次の通りです。

商品マスタをKARTE Datahubにインポートする
KARTE Datahubで商品のランキング化をする
KARTE Messageで配信のユーザーリストを作成する
購入数の商品ランキングのキャンペーンを作成する

順番に説明します。

1. 商品マスタをKARTE Datahubにインポートする

まず、以下の画像の黄色になっている部分、外部システムなどにある商品データをKARTE Datahubに取り込みをすることをやっていきます。

ここで言っている商品マスタとは、商品ID(item_id) / 商品名(name) / 商品画像(image) / 商品URL(url) / 商品価格(price) を最小構成にし、在庫情報などもわかるようなデータを指します。

既に、商品マスタデータがKARTE Datahubにある方は、この章を飛ばして「3. KARTE Datahubで商品のランキング化・配信のユーザーリストを作成する」に進んでください。

商品マスタをKARTE Datahubにインポートする方法はGCSやAmazonS3から取り込む形やGoogleスプレットシートに取り込むなど複数手段があります。詳細はこちらのサポートサイトを参考にして進めてください。

今回は、ダミーのアパレルショップのサイトをもとに施策を作ります。商品数も少ないので、手動で以下の画像のようなGoogleスプレッドシートを作りフェデレーションテーブルとしてKARTE Datahubに取り込む形にしています。

スクリーンショット0005-12-2615.18.21.png

Googleスプレッドシートが作れたら、Datahubに商品マスタのダミーデータをフェデレーションテーブルで作ってみました。
詳しくは、サポートタスクのフェデレーションテーブルを作成するを見てください。

以下の状態になれば完了です。

2. KARTE Datahubで商品のランキング化をする

次に、[すべてのメニュー > Datahub > クエリ]のページを開きます。[作成 > コレクションから作成]を押して「商品購入数/購入金額のランキング」のクエリを利用します。

SQLエディタのクエリを以下に変更します。

WITH analysis_data AS (
  SELECT
    user_id
    , JSON_QUERY(VALUES, '$.buy.items') AS items
  FROM
    {% karte_event period[0], period[1] %}
  WHERE
    event_name = 'buy'
    AND JSON_QUERY(VALUES, '$.buy.items') IS NOT NULL
)
, convert_to_array AS (
  SELECT
    user_id
    , SPLIT(SUBSTR(items, 2 , LENGTH(items) - 2), '},{') AS items
  FROM
    analysis_data
  ),
  unnested AS (
  SELECT
    user_id
    , item
  FROM
    convert_to_array, UNNEST(items) AS item
)
, restored_to_json AS (
  SELECT
    user_id
    , CONCAT('{', REPLACE(REPLACE(item, '{', ''), '}', ''), '}') AS item
  FROM
    unnested
)
, dataset AS (
  SELECT
    user_id
    , JSON_VALUE(item, '$.item_id') AS item_id
    , JSON_VALUE(item, '$.quantity') AS quantity
    , JSON_VALUE(item, '$.price') AS price
  FROM
    restored_to_json
)
, item_count AS (
  SELECT
    user_id
    , item_id
    , SUM(IFNULL(SAFE_CAST(quantity AS INT64), 1)) AS quantity_count
    , SUM(IFNULL(SAFE_CAST(quantity AS INT64), 1) * SAFE_CAST(price AS INT64)) AS total_amount
  FROM
    dataset
  GROUP BY
    item_id , user_id
)
, item_master AS (
  SELECT
    *
  FROM
   `prd-karte-per-client.item_master_{{api_key_safe_param}}.item_master` --データセット名およびテーブル名は必要に応じて書き換えて下さい
  WHERE
    item_id IS NOT NULL
)
, aggregate_data AS (
  SELECT
    a.user_id AS user_id
    , a.item_id AS item_id
    , b.name AS name
    , b.image AS image
    , b.url AS url
    , b.price AS price
    , ROW_NUMBER() OVER(ORDER BY a.quantity_count DESC) as quantity_rank
    , TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL {{ttl_hour}} HOUR) AS expired_at
  FROM
    item_count AS a
  LEFT JOIN item_master AS b
    ON a.item_id = b.item_id
  WHERE b.item_id IS NOT NULL
    AND b.name IS NOT NULL
    AND b.image IS NOT NULL
    AND b.url IS NOT NULL
  ORDER BY
    a.quantity_count DESC
)
, filter_aggregate_data AS (
  SELECT
    *
  FROM aggregate_data
  WHERE quantity_rank <= 6
)
, item AS (
  SELECT
    ARRAY_AGG(
      STRUCT(
        item_id
        , name
        , image
        , url
        , price
        , quantity_rank
        , expired_at
      )
    ) AS item_arr
  FROM
    filter_aggregate_data
)
, user AS (
  SELECT
    user_id
    , email
    , identify
    , user_fields
  FROM
    `karte-data.karte_user_ae2a974f9e050db947a1a4084d40c534.karte_user`
  WHERE
    user_id IS NOT NULL
      AND
    email IS NOT NULL
  ORDER BY sync_date ASC
)

SELECT
  user.user_id AS user_id
  , user.email AS email
  , item.item_arr AS item_arr
FROM
  user
CROSS JOIN item

上記のクエリでは、

商品の中から購入イベントが多い順に並び替える
6位以内の商品データをメールに埋め込むことができるようにレコード型に変換
メール配信のリストにしやすいようにユーザーデータにあるuser_idと結合させる

といったことをやっています。

クエリの中の prd-karte-per-client.item_master_{{api_key_safe_param}}.item_master --データセット名およびテーブル名は必要に応じて書き換えて下さい というところについては、商品マスタのテーブルのパスに変更してください。

ここまでできたら保存してください。

スクリーンショット0005-12-2615.22.52.png

3. KARTE Messageで配信のユーザーリストを作成する

次に、[すべてのメニュー > Messageβ > リスト管理]のページを開いてください。[リストを作成 > Datahubから作成]を押して以下のように設定を進めてください。

リスト名：任意の名前（ここでは”商品購入数のランキング(1~6)＿ユーザー_20231220”としておきます）
テーブル名：先ほどKARTE Datahubで作成したクエリを選択
配信対象
- 配信チャネル：その他のリスト
- ユーザーIDの指定：user_id

以上のように設定できたら保存を押します。

スクリーンショット0005-12-2610.47.34.png

4. 購入数の商品ランキングのキャンペーンを作成する

最後に実際にメール配信するキャンペーンを作成してみます。以下のようにランキングの商品データを埋め込む形になるように進めていきます。

[すべてのメニュー > Messageβ > キャンペーン管理]のページを開いて、[キャンペーンを作成]を押してください。

以下のように設定していってください。

基本設定
- キャンペーン名：任意の名前を付けます
コンテンツ： メール配信 を選択
対象ユーザー
- マスターリスト：ユーザーを一意に特定する user_id とメール配信する用のリストを選択します（ここでは 全ユーザーというリストを選択）
- さらに絞り込む： 商品購入数のランキング(1~6)＿ユーザー_20231220 を選択

アクション

アクションを追加します

[コンテンツ > HTML]をドラッグ&ドロップして、[コンテンツのプロパティ]に以下のHTMLを追加して保存します。

<table style="display:flex; align-items: center;  justify-content: center;">
  {{#each view_items_area}}
    <tr>
      <td style="padding: 1px;">
        <h2 style="overflow-wrap: break-word;">{{QUANTITY_RANK}}位</h2>
        <img width="200px" src={{IMAGE}} alt={{NAME}} />
        <p style="overflow-wrap: break-word; font-weight: bold;">{{NAME}}</p>
        <p style="overflow-wrap: break-word; font-weight: bold;">¥{{PRICE}}</p>
        <a href="{{URL}}">
          <button style="padding:6px 16px ; border-radius: 4px; border: none; color: white; font-size:12px; background-color: #12a0a0;">詳しくはこちら</button>
        </a>
      </td>
    </tr>
  {{/each}}
</table>

上記のHTMLの中には

Handlebars構文を使っており、これによって要素を繰り返してデータを参照する
ボタンをクリックすると商品のURLに飛ぶ

といったことを設定しています。細かなコンテンツや装飾に関しては自由に編集してください。

埋め込み変数の指定
- 変数名： {{VIEW_ITEMS_AREA}}
- 紐付けるフィールド： 商品購入数のランキング(1~6)＿ユーザー_20231220/item_arr を選択
スケジュール
- 繰り返し
- 実行間隔：毎月・最終日・12時・0分

全部設定できたら保存を押します。
これで配信する施策の準備が整いました。

実際に動かしてみる

実際に、キャンペーンの画面を開いて、配信する施策が出来ているか確認しましょう。

先ほど作ったキャンペーンを開いて[キャンペーンを編集 > 埋め込み変数の指定 > プレビュー]を押します。以下のように1〜6位までの商品が埋め込まれていれば成功です。

これで配信予約すれば、月に1度、商品購入数のランキングが自動で配信されるようになります。

13のコピー.png

おわりに

今回は「全ユーザーの購入数に応じて、商品をランキング順にし、KARTE Messageで配信する」について紹介しました。

また、この施策は閲覧・購買・購入の合計金額などランキングにする指標はまだまだありますので、これらを組み合わせて総合的なランキングにして配信するなど応用することができます。

ぜひ皆さんも、KARTEを使って様々な施策を試してみてください！

プレイドでは、世の中にインパクトを与える新しいソリューションを一緒につくる仲間を募集しています！ぜひ気軽にお問い合わせください。

↓ パートナー企業になりたい

CustomerData-Driven Solution を一緒に創りませんか？ | PLAID Ecosystem

↓ PLAIDで働きたい

商品を全ユーザーの購入数に応じてランキング化し、毎月KARTE Messageで配信する