広告効果の可視化で使う小技（tableau関連）

ABEMAでサーバーサイドエンジニアをやっている末松です。

メディア事業部の横軸組織PTAのアドベントカレンダーの１２日目の記事となります。

はじめに

ABEMAでは、TV放送と同様の配信形態である リニア放送 と 好きな番組を好きな時間に見ることができる VOD があります。VODには有料コンテンツが含まれますが、無料コンテンツにはCMが流れています。

見ている視聴者に応じたCMを流したり、番組とマッチCMを流すために全員に同じCMを流したり、ケースバイケースでCMをコントロールしています。

そこで、今回は、CM効果を分析する上で、よく使っている 小技 をご紹介します。

tableauでフィルタの「すべて」は表示するべきか適宜考える

tableauでフィルタを設定したときに、デフォルトで「 （すべて） 」が表示されています。
例えば、「広告」、「自社広告」、「番宣」 それぞれで UU（ユニークユーザー）数を表示していた場合、（すべて） を選んだことで意図せず合算してしまう場合があります。

そのような誤解を回避するために、不適切な集計になる場合は （すべて） を下記のように、
「カスタマイズ」→ 「"すべて"の値を表示」 をOFFにします。

時系列で二次元のグラフを可視化

１次元追加する方法として、「色」や「形」があると思うのですが、日付自体をパラメータ化し、アニメーションによる変化もテクニックの一つかと思います。

下記の例では、CMを配信してから、FQ（CMを視聴した回数）別のUU（ユニークユーザー数）を表したものです。
初日、二日目、三日目と日が経つにつれて 接触回数1回 のユーザー数が顕著に増加していることがわかります。
ちなみに、接触回数7回が多くなっているのはFQキャップ（一人あたりに接触する最大回数）によるものです。デバイスやチャンネルによっては、FQキャップが効かない場合があるため、７回以上接触したユーザーが何人か存在しています。

日別でFQを表示する場合、日別の累積UUを求める必要があります。
tableau上でもWINDOW_MAX関数とWINDOW_SUM関数、FIRST関数を用いることで表現することができますが、今回は汎用的に使えるようSQLで表現しています。

tableau上で表現する場合は、こちらをご参考ください。

日別の累積UUをSQLで表現する方法

まずは、サンプルとなるデータを用意します。

WITH SAMPLEDATA AS (
  SELECT *
  FROM UNNEST([
    STRUCT(DATE '2019-01-01' AS dt,　100 AS campaign_id,  'USER1' AS user_id),
    (DATE '2019-01-02',　100, 'USER1'),
    (DATE '2019-01-02',　100, 'USER2'),
    (DATE '2019-01-03',　100, 'USER3'),
    (DATE '2019-01-04',　200, 'USER1')
  ])
)

dt	campaign_id	user_id
2019-01-01	100	USER1
2019-01-02	100	USER1
2019-01-02	100	USER2
2019-01-03	100	USER3
2019-01-04	200	USER1

次に、最終的にキャンペーン毎の接触回数を表現したいので、キャンペーン✕ユーザー単位で行番号を付与します。

SELECT
    campaign_id,
    dt,
    user_id,
    COUNT(1) AS imp,
    ROW_NUMBER() OVER (PARTITION BY campaign_id,user_id ORDER BY dt) AS ROW_NUMBER
    FROM SAMPLEDATA
    GROUP BY dt,campaign_id,user_id

campaign_id	dt	user_id	imp	ROW_NUMBER
100	2019-01-01	USER1	1	1
100	2019-01-02	USER1	1	2
100	2019-01-02	USER2	1	1
100	2019-01-03	USER3	1	1
200	2019-01-04	USER1	1	1

そして、次は、ROW_NUMBERが1となる日 = 初回接触日のみカウントするようにします

SELECT
      campaign_id,
      dt,
      SUM(IF(ROW_NUMBER = 1, 1, 0)) AS FIRST_UU,
      SUM(imp) AS imp
    FROM (
      /* ROW_NUMBERを付与したクエリ */
    )
    GROUP BY dt,campaign_id

campaign_id	dt	FIRST_UU	imp
100	2019-01-01	1	1
100	2019-01-02	1	2
100	2019-01-03	1	1
200	2019-01-04	1	1

これでこのユーザーを集約することができたため、後はSUM OVER関数を使ってキャンペーン毎のFIRST_UUの累積を求めれば日別の累積UUが算出されます。

SELECT
   campaign_id,
   dt,
  SUM(FIRST_UU) OVER (PARTITION BY campaign_id ORDER BY dt) AS UU,
  SUM(imp) OVER (PARTITION BY campaign_id ORDER BY dt) AS imp

campaign_id	dt	UU	imp
100	2019-01-01	1	1
100	2019-01-02	2	3
100	2019-01-03	3	4
200	2019-01-04	1	1

BigQuery上で確認できるクエリの全文はこちらです。

WITH SAMPLEDATA AS (
  SELECT *
  FROM UNNEST([
    STRUCT(DATE '2019-01-01' AS dt,　100 AS campaign_id,  'USER1' AS user_id),
    (DATE '2019-01-02',　100, 'USER1'),
    (DATE '2019-01-02',　100, 'USER2'),
    (DATE '2019-01-03',　100, 'USER3'),
    (DATE '2019-01-04',　200, 'USER1')
  ])
)
SELECT
   campaign_id,
   dt,
  SUM(FIRST_UU) OVER (PARTITION BY campaign_id ORDER BY dt) AS UU,
  SUM(imp) OVER (PARTITION BY campaign_id ORDER BY dt) AS imp
FROM (
    SELECT
      campaign_id,
      dt,
      SUM(IF(ROW_NUMBER = 1, 1, 0)) AS FIRST_UU,
      SUM(imp) AS imp
    FROM (
        SELECT
          campaign_id,
          dt,
          user_id,
          COUNT(1) AS imp,
          ROW_NUMBER() OVER (PARTITION BY campaign_id,user_id ORDER BY dt) AS ROW_NUMBER
        FROM SAMPLEDATA
        GROUP BY dt,campaign_id,user_id
    )
    GROUP BY dt,campaign_id
)
ORDER BY campaign_id,dt