こんにちは！
PLAIDでカスタマーエンジニアをしているkipこと和田吉平です。
今回は、私がメインで担当しているメールやアプリプッシュを行えるプロダクト、KARTE Messageでユーザーごとに配信頻度をコントロールする方法をご紹介します。

背景

メールやアプリプッシュはいわゆる、プッシュ型施策でお客様に情報を送る行為になります。適切なタイミングで配信を行えれば、サイト外にいるお客様にも顧客体験の向上を図れます。一方で、何度も似たような情報を送りつけられてくると、鬱陶しいコミュニケーションになってしまい顧客体験を損なうことにもつながってしまいます。

そこで、同種の情報提供をし続けて鬱陶しいコミュニケーションにならないように、配信頻度をユーザー毎にコントロールし、一日x通以上届いたユーザーには配信しないなどの制御を行いたいと思います。

KARTE Messageとは？

今回紹介するKARTE Messageというプロダクトはサイト外でのコミュニケーションを最適化できるプロダクトです。所謂MA的なプロダクトとなっており、記事執筆現在、メール・アプリプッシュの二つのチャネルで皆さんの顧客にコミュニケーションを行えます。（LINEも現状開発中でまもなくチャネルとして追加されます！）

参考）https://karte.io/product/ma/

KARTE Messageの簡単な仕組みの解説

KARTE MessageはKARTE Datahubにあるデータを参照し、メールやアプリプッシュを配信します。
スクリーンショット2023-07-2621.16.34.png

KARTE Messageの機能：配信対象抽出

また、特徴の一つとして配信対象の絞り込み機能があります。具体的には、以下のような形で絞り込みが可能です。

Datahubにあるデータからリストという送信先情報をKARTE Messageに作成。
リストに一定の条件を付与してレコードを抽出したフィルターリストを作成。
リストやフィルターリストを組み合わせて送信先を抽出

参考）リスト作成
参考）フィルターリスト作成
参考）配信対象抽出

今回はこの配信対象抽出の機能を転用して配信頻度の制御を行います。

配信頻度を調整する

今回は、配信頻度を調整するために配信した通数をユーザー毎に集計しリストにします。このリストに、「today_send_count（本日配信した数）が 3以上」などの条件をつけてフィルターリストを作成して、キャンペーンで除外対象にすることで配信頻度調整を行います。

スクリーンショット2023-07-2622.04.32.png

配信ログから配信通数を集計する

まず、配信ログから配信通数を集計しましょう。
汎用性を考えて今回は、「配信当日」「直近1週間」「直近1ヶ月」「任意の期間」でそれぞれ「送信」「開封」「クリック」をユーザー毎に集計します。

WITH raw AS (
  SELECT
    event_name,
    campaign_id,
    schedule_task_id,
    user_id,
    timestamp,
    plugin_type,
  FROM `prd-karte-message-per-client.csv_upload_{{api_key | safe_param}}.masspush_event_log`, UNNEST(user_data_list) AS user_data
  WHERE
    event_name IN ('message_send', 'message_open', 'message_click')
    AND user_id IS NOT null
    AND (timestamp BETWEEN PARSE_TIMESTAMP('%Y%m%d', {{extract_period[0]}}) AND TIMESTAMP_TRUNC(TIMESTAMP_ADD(PARSE_TIMESTAMP('%Y%m%d', {{extract_period[1]}}), INTERVAL 1 DAY), DAY))
    {% if plugin_type != 'all' %}
    AND plugin_type = {{plugin_type}}
    {% endif %}
)

, today AS (
  SELECT DISTINCT user_id, campaign_id, schedule_task_id, event_name FROM raw
  WHERE timestamp BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), DAY) AND TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 DAY)
)

, week AS (
  SELECT DISTINCT user_id, campaign_id, schedule_task_id, event_name FROM raw
  WHERE timestamp BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), DAY) AND TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 DAY)
)

, month AS (
  SELECT DISTINCT user_id, campaign_id, schedule_task_id, event_name FROM raw
  WHERE timestamp BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY), DAY) AND TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 1 DAY)
)

, all_period AS (
  SELECT DISTINCT user_id, campaign_id, schedule_task_id, event_name FROM raw
)

, agg_today AS (
  SELECT 
    user_id
    , COUNT(CASE WHEN event_name = 'message_send' THEN 1 ELSE NULL END) AS today_send_count
    , COUNT(CASE WHEN event_name = 'message_open' THEN 1 ELSE NULL END) AS today_open_count
    , COUNT(CASE WHEN event_name = 'message_click' THEN 1 ELSE NULL END) AS today_click_count
  FROM today
  GROUP BY user_id
)

, agg_week AS (
  SELECT 
    user_id
    , COUNT(CASE WHEN event_name = 'message_send' THEN 1 ELSE NULL END) AS week_send_count
    , COUNT(CASE WHEN event_name = 'message_open' THEN 1 ELSE NULL END) AS week_open_count
    , COUNT(CASE WHEN event_name = 'message_click' THEN 1 ELSE NULL END) AS week_click_count
  FROM week
  GROUP BY user_id
)

, agg_month AS (
  SELECT 
    user_id
    , COUNT(CASE WHEN event_name = 'message_send' THEN 1 ELSE NULL END) AS month_send_count
    , COUNT(CASE WHEN event_name = 'message_open' THEN 1 ELSE NULL END) AS month_open_count
    , COUNT(CASE WHEN event_name = 'message_click' THEN 1 ELSE NULL END) AS month_click_count
  FROM month
  GROUP BY user_id
)

, agg_all AS (
  SELECT
    user_id
    , COUNT(CASE WHEN event_name = 'message_send' THEN 1 ELSE NULL END) AS all_send_count
    , COUNT(CASE WHEN event_name = 'message_open' THEN 1 ELSE NULL END) AS all_open_count
    , COUNT(CASE WHEN event_name = 'message_click' THEN 1 ELSE NULL END) AS all_click_count
  FROM all_period
  GROUP BY user_id
)

SELECT
  agg_all.user_id,
  CASE 
    WHEN today_send_count IS null THEN 0
    ELSE today_send_count
  END AS today_send_count,
  CASE 
    WHEN today_open_count IS null THEN 0
    ELSE today_open_count
  END AS today_open_count,
    CASE 
    WHEN today_click_count IS null THEN 0
    ELSE today_click_count
  END AS today_click_count,
  CASE 
    WHEN week_send_count IS null THEN 0
    ELSE week_send_count
  END AS week_send_count,
  CASE 
    WHEN week_open_count IS null THEN 0
    ELSE week_open_count
  END AS week_open_count,
  CASE 
    WHEN week_click_count IS null THEN 0
    ELSE week_click_count
  END AS week_click_count,
  CASE 
    WHEN month_send_count IS null THEN 0
    ELSE month_send_count
  END AS month_send_count,
  CASE 
    WHEN month_open_count IS null THEN 0
    ELSE month_open_count
  END AS month_open_count,
  CASE 
    WHEN month_click_count IS null THEN 0
    ELSE month_click_count
  END AS month_click_count,
  CASE 
    WHEN all_send_count IS null THEN 0
    ELSE all_send_count
  END AS all_send_count,
  CASE 
    WHEN all_open_count IS null THEN 0
    ELSE all_open_count
  END AS all_open_count,
  CASE 
    WHEN all_click_count IS null THEN 0
    ELSE all_click_count
  END AS all_click_count,
FROM agg_all
LEFT OUTER JOIN agg_month
  ON agg_all.user_id = agg_month.user_id
LEFT OUTER JOIN agg_week
  ON agg_all.user_id = agg_week.user_id
LEFT OUTER JOIN agg_today
  ON agg_all.user_id = agg_today.user_id

extract_period:
  type: period
  label: 抽出期間
  default: past 60 days
  tooltip: 長くすると、クエリリソースを多く消費します
plugin_type:
  type: select
  label: 集計対象
  default: all
  options:
    labels:
      - 全てのチャンネル
      - メール
      - アプリプッシュ
      - LINE
    values:
      - all
      - ses
      - native_app
      - line
  tooltip: 配信チャネルごとの回数を確認できます

このようにクエリを書いてみました。
この結果、ユーザー毎に、以下のようなカラム構成でデータを取得できました。
スクリーンショット2023-07-2622.35.49.png

集計結果をキャンペーンで利用する

では、このまとめたクエリ結果をリストにして条件を付与して利用しましょう。
Messageのリスト管理画面に移動して、リストを作成します。
参考）リスト作成

リストが作成できたら、フィルターリストを作成します。以下画像のような条件を設定しユーザーを抽出します。
スクリーンショット2023-07-2622.43.16.png

フィルターリストができたら、キャンペーンに除外対象として設定します。
スクリーンショット2023-07-2622.46.08.png

これで、このキャンペーンでは今日すでに3回送信を行なっているユーザーへの配信を停止するようになります。

実際に試す際には？

今回、作成したクエリですが実は皆様が利用しやすいようにクエリコレクションとして追加してみました！！
クエリコレクションから「Messageの配信頻度をプロジェクト全体で制御する」「Messageの配信頻度をキャンペーン毎で制御する」をインストールしてご利用ください。
この記事で取り上げているのは、「Messageの配信頻度をプロジェクト全体で制御する」ですが、同種のコミュニケーションが増えないように同じキャンペーンを何回も送らないようにしたいというニーズもあるかと思います。
その場合は、「Messageの配信頻度をキャンペーン毎で制御する」をご利用いただき、キャンペーンIDをフィルターリストの条件に加えてみてください。
スクリーンショット2023-07-2622.48.20.png

使ってみた感想もお聞かせいただけると幸いです。
KARTE管理画面左下にある「気分」から簡単にコメントいただけますので、ぜひお気軽に改善要望など送ってみてください！

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

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

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

↓ PLAIDで働きたい

KARTE Messageでユーザー毎に配信頻度をコントロールできるようにしてみた