マーケター必見！SQLでA/Bテストを実施する方法と検定の方法

A/Bテストとは

A/Bテストとは、2つのバリエーションを比較して、どちらがより効果的かを判断するための実験的手法です。例えば、ウェブサイトのランディングページのデザインや、メールマガジンの件名や本文など、マーケティング施策の改善に活用されます。

SQLでの実施手順

A/BテストをSQLで実施する場合は、以下の手順で行います。
※本記事のSQLはmySQLを想定しています。

データを抽出する

比較する2つのデータを抽出します。例えば、AグループとBグループのデータを比較する場合は、以下のようなSQLクエリを使用します。

SELECT *
FROM ユーザーテーブル
WHERE グループID = 'A'; -- or 'B' for group B

統計量を計算する

データから、比較するための統計量を計算します。具体的には、コンバージョン率や売上額の平均などがあります。例えば、コンバージョン率を計算する場合は、以下のようなSQLクエリを使用します。

SELECT COUNT(*) AS ユーザー数, SUM(コンバージョンフラグ) AS コンバージョン数
FROM ユーザーテーブル
WHERE グループID = 'A'; -- or 'B' for group B

p値を計算する

統計量から、p値を計算します。p値は、どちらのグループが有意に効果があるかを判断するために使用されます。p値は、AグループとBグループのデータが偶然にも同じとなる確率を表しています。p値が小さいほど、偶然に起因する可能性が低く、結果が有意になるとされます。p値の閾値は、一般的に0.05以下とされます。例えば、p値を計算する場合は、以下のようなSQLクエリを使用します。

WITH
a AS (SELECT COUNT(*) AS ユーザー数, SUM(コンバージョンフラグ) AS コンバージョン数
      FROM ユーザーテーブル
      WHERE グループID = 'A'),
b AS (SELECT COUNT(*) AS ユーザー数, SUM(コンバージョンフラグ) AS コンバージョン数
      FROM ユーザーテーブル
      WHERE グループID = 'B')
SELECT ROUND(1 - CDF('NORMAL', ABS((a.コンバージョン数 / a.ユーザー数) - (b.コンバージョン数 / b.ユーザー数)) / SQRT((a.コンバージョン数 / a.ユーザー数) * (1 - a.コンバージョン数 / a.ユーザー数) / a.ユーザー数 + (b.コンバージョン数 / b.ユーザー数) * (1 - b.コンバージョン数 / b.ユーザー数) / b.ユーザー数)), 10) AS p値
FROM a, b;

t検定の流れと具体的なSQL例

t検定は、標本平均の差が、母集団平均の差によって説明できるかどうかを検定する方法です。t検定は、標本数が小さい場合でも有効であり、A/Bテストによく使用されます。t検定をSQLで実施する場合は、以下の手順で行います。

データを抽出する

比較する2つのデータを抽出します。例えば、AグループとBグループのデータを比較する場合は、以下のようなSQLクエリを使用します。

SELECT *
FROM ユーザーテーブル
WHERE グループID = 'A'; -- or 'B' for group B

平均値と標準偏差を計算する

データから、平均値と標準偏差を計算します。例えば、AグループとBグループの平均値と標準偏差を計算する場合は、以下のようなSQLクエリを使用します。

SELECT 'A' AS グループ, AVG(コンバージョンフラグ) AS 平均値, STDDEV_POP(コンバージョンフラグ) AS 標準偏差
FROM ユーザーテーブル
WHERE グループID = 'A'
UNION ALL
SELECT 'B' AS グループ, AVG(コンバージョンフラグ) AS 平均値, STDDEV_POP(コンバージョンフラグ) AS 標準偏差
FROM ユーザーテーブル
WHERE グループID = 'B';

t値を計算する

平均値と標準偏差から、t値を計算します。t値は、2つの平均値の差が、どれだけ大きいかを表します。例えば、t値を計算する場合は、以下のようなSQLクエリを使用します。

WITH
a AS (SELECT AVG(コンバージョンフラグ) AS 平均値, STDDEV_POP(コンバージョンフラグ) AS 標準偏差, COUNT(*) AS サンプル数
      FROM ユーザーテーブル
      WHERE グループID = 'A'),
b AS (SELECT AVG(コンバージョンフラグ) AS 平均値, STDDEV_POP(コンバージョンフラグ) AS 標準偏差, COUNT(*) AS サンプル数
      FROM ユーザーテーブル
      WHERE グループID = 'B')
SELECT ABS((a.平均値 - b.平均値) / SQRT(a.標準偏差 * a.標準偏差 / a.サンプル数 + b.標準偏差 * b.標準偏差 / b.サンプル数)) AS t値
FROM a, b;

p値を計算する

t値から、p値を計算します。p値は、どちらのグループが有意に効果があるかを判断するために使用されます。p値は、AグループとBグループのデータが偶然にも同じとなる確率を表しています。p値が小さいほど、偶然に起因する可能性が低く、結果が有意になるとされます。p値の閾値は、一般的に0.05以下とされます。例えば、p値を計算する場合は、以下のようなSQLクエリを使用します。

WITH
a AS (SELECT AVG(コンバージョンフラグ) AS 平均値, STDDEV_POP(コンバージョンフラグ) AS 標準偏差, COUNT(*) AS サンプル数
      FROM ユーザーテーブル
      WHERE グループID = 'A'),
b AS (SELECT AVG(コンバージョンフラグ) AS 平均値, STDDEV_POP(コンバージョンフラグ) AS 標準偏差, COUNT(*) AS サンプル数
      FROM ユーザーテーブル
      WHERE グループID = 'B')
SELECT 2 * (1 - CDF('student-t', ABS((a.平均値 - b.平均値) / SQRT(a.標準偏差 * a.標準偏差 / a.サンプル数 + b.標準偏差 * b.標準偏差 / b.サンプル数)), a.サンプル数 + b.サンプル数 - 2)) AS p値
FROM a, b;

結果の判断

p値を元に、結果を判断します。p値が0.05以下であれば、有意差があるとされます。ただし、p値が小さい場合でも、サンプル数が少ない場合は誤差が大きくなることがあります。そのため、サンプル数も考慮して判断する必要があります。

注意点

A/Bテストを行う場合、ユーザーの属性などの影響が考慮されているかを確認する必要があります。また、A/Bテストの期間や設計にも注意が必要です。詳細については、専門書などで学習することをお勧めします。

まとめ

SQLを使ってA/Bテストを行う手順について紹介しました。SQLを使うことで、簡単にA/Bテストを実施し、結果を検定することができます。ただし、A/Bテストの設計や期間には注意が必要です。

結果を分析する際には、p値だけでなく、実際の差分やサンプル数も考慮する必要があります。また、統計的有意差がある場合でも、ビジネス的に意味のある差分であるかを確認する必要があります。

SQLを使ったA/Bテストの実施方法と検定方法を学ぶことで、マーケティング担当者はデータを使った意思決定により貢献できるようになります。ただし、A/Bテストはビジネスにとって非常に重要なものであり、設計や期間には細心の注意が必要です。A/Bテストに関する知識を深め、正しい判断を行うことが重要です。