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

14422 ワード
Tableau BigQuery 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