Gunosy Tech Blog

Gunosy Tech Blogは株式会社Gunosyのエンジニアが知見を共有する技術ブログです。

中間テーブルを生成してA/Bテストの集計を効率化する

はじめに

こんにちは、Gunosy新規事業開発室所属の渡辺（https://twitter.com/k6nta）です。 この記事はGunosy Advent Calendar 2017の23日目の記事です。

qiita.com

Gunosyでは施策の結果を振り返ったり、次の施策の実行に役立つ考察を行うためにA/Bテストを実施しています。 データ分析を行うためにユーザの行動ログを自社基盤で収集・集計しており、このログを利用してA/Bテストの集計を行っています。

生ログ（ユーザの行動ログ）を用いてデータの集計・分析を行う場合、ユーザが増えてくるとログの量が膨大になり、集計時間が長くなることで分析効率が悪くなります。 あるKPIの集計に10分~かかってくると、集計中に別作業が頻繁に発生するため、分析効率がぐっと落ちることが想像できると思います。 こういう状態になった場合、データストアを変更したり、現在のデータストアをスケールアップして対応することが可能ですが、前者は基盤のリプレイスが必要になるため開発コストがかかり、後者はインフラコストが増えます。

そこで、この記事では中間テーブルを適切に設計することで、基盤の変更やスケールアップを避けてA/Bテストの集計効率を上げる方法を紹介しようと思います。なお、この記事ではデータストアとしてAWS Redshiftを、可視化ツールとしてRedashを利用することを想定しています。

ログの形式

最初にログの形式を説明します。説明のために簡素化していますが、実際のシステムでも似たようなログの形式を利用しています。

行動ログ：actions
{
  'user_id': 123456,
  'ab': '{
    "test_experiment1": "control",  # keyがあるA/Bテストに該当するキー, ValueがそのA/Bテストの中でこのユーザに割り当てられているパターンに該当するキーとなります。
    "test_experiment2": "treatment"
  }',
  'action': 'article:click',
  'article_id': 987654,
  'created_at': '2017-12-23 23:45:00'
}

ユーザには複数のA/Bテストが当たる可能性があるため、abカラムにはjson形式で複数のA/Bテストの情報を詰め込めるようになっています。 actionには

  • 記事クリック：'article:click'
  • セッション：'app:session'

のような値が入ります。

ユーザ：users
{
  'id': 123456,
  'created_at': '2017-12-01 12:34:56'
}

KPI

実際のA/Bテスト集計では多くのKPIをモニタリングしますが、ここではA/Bテストのパターンごとに日別の

  • 記事クリックUU / DAU（ClickUU/DAU）
  • 記事クリック数 / DAU（Click/DAU）
  • 記事クリック数 / 記事クリックUU（Click/ClickUU）

を集計することを例として取り上げます。

生ログを利用する場合のクエリ

以下のようなクエリを用います。experiment_keyは集計対象のA/Bテストを指定するためのキーです。

WITH
    daily_active_users AS (
        SELECT
            actions.created_at::DATE AS date,
            JSON_EXTRACT_PATH_TEXT(actions, '{{experiment_key}}') AS variant_key,
            COUNT(DISTINCT actions.user_id) AS n_user
        FROM
            actions
        INNER JOIN
            users
        ON
            users.id = actions.user_id
        WHERE
           actions.action = 'app:session'
        AND
           JSON_EXTRACT_PATH_TEXT(actions, '{{experiment_key}}') <> ''
        AND
            actions.created_at BETWEEN '{{action_from}}' AND '{{action_to}}'
        AND
            users.created_at BETWEEN '{{user_from}}' AND '{{user_to}}'
        GROUP BY
            date,
            variant_key
  ),
  daily_action_users AS (
        SELECT
            actions.created_at::DATE AS date,
            JSON_EXTRACT_PATH_TEXT(actions, '{{experiment_key}}') AS variant_key,
            COUNT(DISTINCT actions.user_id) AS n_user,
            COUNT(1) AS n_action
        FROM
            actions
        INNER JOIN
            users
        ON
            users.id = actions.user_id
        WHERE
           actions.action = 'article:click'
        AND
           JSON_EXTRACT_PATH_TEXT(actions, '{{experiment_key}}') <> ''
        AND
            actions.created_at BETWEEN '{{action_from}}' AND '{{action_to}}'
        AND
            users.created_at BETWEEN '{{user_from}}' AND '{{user_to}}'
        GROUP BY
            date,
            variant_key
  )
SELECT
  daily_action_users.date,
  daily_action_users.variant_key,
  daily_action_users.n_user::REAL / daily_active_users.n_user AS "uu_per_dau",
  daily_action_users.n_action::REAL / daily_active_users.n_user AS "action_per_dau",
  daily_action_users.n_action::REAL / daily_action_users.n_user AS "action_per_uu",
FROM
  daily_action_users
INNER JOIN
  daily_active_users
ON
  daily_active_users.date = daily_action_users.date
AND
  daily_active_users.variant_key = daily_action_users.variant_key

ここでの問題点としては、クエリを実行する度にactionsテーブルを参照して日別のDAUやクリック数を集計する必要があるため実行時間が長くなることです。 実際の分析では、集計対象のユーザ登録日時や、クリックの日時をずらしたり、クリック以外にも多くのKPIを集計するため、個々のクエリの集計時間が長くなると分析効率が悪くなります。そこで以下のような中間テーブルを生成します。

中間テーブルの生成

日別のアクティブユーザテーブル：active_users
{
  'user_id': 123455,
  'date': '2017-12-23'
}
日別の記事クリックテーブル：article_click_users
{
  'user_id': 123455,
  'date': '2017-12-23',
  'value': 8 # クリック数
}
日別のA/Bテーブル：ab_users
{
  'user_id': 123456,
  'date': '2017-12-23',
  'experiment_key': 'test_experiment1', # 当たっているA/Bテストに対応する
  'variant_key': 'treatment' # 当たっているA/Bテストのパターンに対応する
}

これらのテーブルには、夜中に集計したデータを挿入しておきます。

中間テーブルを利用したKPIの集計

上記の中間テーブルを利用して、生ログを利用したクエリと同じ結果が以下のクエリで得られます。

WITH
    daily_active_users AS (
        SELECT
            active_users AS date,
            ab_users.variant_key,
            COUNT(DISTINCT active_users.user_id) AS n_user
        FROM
            active_users
        INNER JOIN
            ab_users
        ON
            ab_users.user_id = active_users.user_id
        AND
            ab_users.date = active_users.date
        INNER JOIN
            users
        ON
            users.id = actions.user_id
        WHERE
            ab_users.experiment_key = '{{experiment_key}}'
        AND
            active_users.date BETWEEN '{{action_from}}' AND '{{action_to}}'
        AND
            users.created_at BETWEEN '{{user_from}}' AND '{{user_to}}'
        GROUP BY
            date,
            variant_key
  ),
  daily_action_users AS (
        SELECT
            action_users.date,
            ab_users.variant_key,
            COUNT(DISTINCT action_users.user_id) AS n_user,
            COUNT(1) AS n_action
        FROM
            article_click_users AS action_users
        INNER JOIN
            ab_users
        ON
            ab_users.user_id = action_users.user_id
        AND
            ab_users.date = action_users.date
        INNER JOIN
            users
        ON
            users.id = actions.user_id
        WHERE
            ab_users.experiment_key = '{{experiment_key}}'
        AND
            action_users.date BETWEEN '{{action_from}}' AND '{{action_to}}'
        AND
            users.created_at BETWEEN '{{user_from}}' AND '{{user_to}}'
        GROUP BY
            date,
            variant_key
  )
SELECT
  daily_action_users.date,
  daily_action_users.variant_key,
  daily_action_users.n_user::REAL / daily_active_users.n_user AS "uu_per_dau",
  daily_action_users.n_action::REAL / daily_active_users.n_user AS "action_per_dau",
  daily_action_users.n_action::REAL / daily_action_users.n_user AS "action_per_uu",
FROM
  daily_action_users
INNER JOIN
  daily_active_users
ON
  daily_active_users.date = daily_action_users.date
AND
  daily_active_users.variant_key = daily_action_users.variant_key

メリデメ

　日別にユーザごとにアクティブであったかどうかと、アクション数を事前に集計して保存しておくことで、その後の日別のKPI集計を高速に行うことができます。 とくに弊社ではA/Bテストを並列で10個以上走らせることも多いため、A/Bテストごとに生ログを集計する必要がなくなり、分析効率を上げることができます。 また、上記のテーブルを利用することで、「A/Bパターンごとに、登録日からの経過日数別のClick/DAUを集計する」といったクエリも書くことができるなど、ある程度柔軟に分析を行うことができます。 　一方で、日別より細かな時間単位で集計は行えなかったり、アプリのバージョンを指定して集計を行えなかったりします。その場合は、各テーブルのdateカラムをさらに細かな時間単位（例えばdate + hour）に対応できるように変更したり、アプリのバージョンカラムを追加することで対応できますが、その分クエリの集計時間は増えるといったデメリットがあります。

まとめ

  • ユーザが増えてくると生ログを利用したA/BテストのKPI集計に時間がかかる
  • データストアを変更・スケールアップして対処可能だが、コストがかかる
  • 中間テーブルを適切に設計して生成することで、スケールアップをせずにKPI集計を効率化できるが、集計の柔軟性と集計速度のトレードオフは存在する

最後に

Gunosyでは共にプロダクト開発を行ってくれるメンバーを募集しています。新規事業開発室では、グノシーやニュースパスでの開発の知見を最大限に活かしながらかなりのスピード感を持って新しいプロダクトの開発・グロースを行なっています。少しでも興味のある方は以下のフォームからエントリーするか、https://twitter.com/k6ntaに気軽に声をかけてください。

https://hrmos.co/pages/1009778707507720193/jobs/0000004hrmos.co

https://hrmos.co/pages/1009778707507720193/jobs/0000063hrmos.co

https://hrmos.co/pages/1009778707507720193/jobs/0000003hrmos.co