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

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

メディア事業部の横軸組織PTAの[アドベントカレンダー](https://adventar.org/calendars/5741)の１２日目の記事となります。

# はじめに

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

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

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

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

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

<img width="400" alt="フィルタ" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/73861/4fbac775-1123-c488-1768-f1c8afb74cfa.gif">

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

下記の例では、CMを配信してから、FQ（CMを視聴した回数）別のUU（ユニークユーザー数）を表したものです。
初日、二日目、三日目と日が経つにつれて **接触回数1回** のユーザー数が顕著に増加していることがわかります。
ちなみに、接触回数7回が多くなっているのはFQキャップ（一人あたりに接触する最大回数）によるものです。デバイスやチャンネルによっては、FQキャップが効かない場合があるため、７回以上接触したユーザーが何人か存在しています。
<img width="600" alt="FQ" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/73861/8e58096a-4fe4-50fe-1b36-1b329fa5038d.gif">

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

tableau上で表現する場合は、[こちら](https://kb.tableau.com/articles/howto/calculating-a-running-count-distinct)をご参考ください。

# 日別の累積UUをSQLで表現する方法
まずは、サンプルとなるデータを用意します。

```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   | 

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

```sql
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となる日 = 初回接触日のみカウントするようにします

```sql
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が算出されます。

```sql
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上で確認できるクエリの全文はこちらです。

```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')
  ])
)
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
```