ウェブ解析×SQL×Tableau｜目指す第一歩、SQLによる顧客データ抽出

以前Googleのカンファレンスで下記のデータ分析のワークフローを聞いたことがあります。

f:id:RaNxxx:20180917184408p:plain

実際のところ、この4つのフローを一気通貫行うというより、「収集→保存」のフェーズにフォーカスする人/部署、また、「加工/分析→可視化」のフェーズにフォーカスする人/部署のケースが多いでしょう。
※データ量の少ない企業やコンサルティング企業では、「収集→保存」の機能に対して、専門部署を作るというより、ツールに頼っていることが多いです。

更に、一定データ量を有している企業は、自社の様々なデータをデータベースに保存しているため、データ分析をするには、まずはSQLを使ってデータ抽出から行うことが多いでしょう。

そのために、自社サービスの分析をするのに、データアナリストは：

Adobe Analytics/Google Analyticsを使ってログデータを収集
BigQuery上で格納されたログデータを購買データとジョインし抽出する
Tableau上でデータ探索・分析を行う

の3つのステップを繰り返して行うことが想定されます。
本記事は、この想定シーンにおける「BigQuery上で格納されたログデータを購買データとジョインし抽出する」ところにフォーカスして、紹介していきたいと思います。

本記事紹介する内容を以下となる：

SQLを書く前に必ずに行うこと
データ抽出のためのSQLテンプレート

SQLを書く前に必ずに行うこと

言い方は様々ありますが、一言でいうと、「分析ゴールから分析イメージを書き出すこと」であります。

手順は以下となります：

分析要件を今一度整理すること
要件に沿って、メジャーとディメンションを設計する

分析要件の整理

分析要件を整理するには、企業によって色々なフォーマットがあります。
コンサルティング企業はクライアントに分析結果を納品する必要があるので、PPTで分析前の確認資料を作ることが多く、また、事業側はインナーのみとのやり取りとなるため、箇条書きで要点だけを書くこともあります。

分析要件は、下記の要素が含まれます：

分析の目的
この分析によって嬉しい/得られること
分析内容
分析イメージ

例えば、集客施策を分析する際に、目的は、「施策効果を正しく評価することによってROIの最大化を狙う」のであれば、分析によって嬉しいことは、「最も効果的施策とその反対で最も非効果的な施策を特定することができる」。
そのために、分析内容は、「過去三ヶ月のリスティング広告の効果を比較する」とします。
最後の分析イメージは、最後に可視化する時にこう表現したいグラフまたは表を指します。

ここでは、分析前にもしすでに仮説や注意点があるのであれば、その仮説を書くことも大事です。
※仮説例：クリエイティブA案は煽り表現が入っているため、CTRが高いはず
※注意点例：クリエイティブB案の配信期間はほかのより一週間短いため、ボリューム比較する際には平均を取る必要がある

メジャーとディメンションの設計

ディメンションの設計と似たような概念だと、「ディメンショナルモデル」や「ディメンションマップ」があります。

いずれにしても、どのディメンションを持って各メジャーを比較するかをリストアップすることがマストです。

前述したリスティング広告の例では、比較対象となるのは、クリエイティブ案のほかに、検索エンジン、配信端末やブラウザ、配信時間帯など様々です。
ただし、ここで、注意しなければいけないのは、ディメンションを増やすことは、データを倍に増やしている可能性があることです。

下記の画像を見れば一目瞭然です。
検索エンジンという一つのディメンションを持つ場合は、仮にClick数とCV数を抽出するのであれば、行数は2で済みますが、
もう一つクリエイティブ案を足した場合、必要な行数は4に増えて2倍となっています。

f:id:RaNxxx:20180917222932p:plain

こうやってディメンションを足すに連れ、抽出されるデータがより大きくなり、計算スピードも落ちるということは要注意です。

そのために、重くなるデータに対して、本当に必要なディメンションのみを見極めて、抽出することが大事です。

ディメンションを選択したあとに、残りはメジャーです。
※人やケースによって、メジャーを先に設計するケースもあります。

メジャーを設計する時に、4種類のメジャーを入れることをお薦めします：

コスト系データ（例：CPC、Total Cost）
量的なデータ（例：Imp数、Click数）
質的なデータ（例：サイト内回遊率）
CV系データ（例：登録人数、購買回数、CVR）

コストの最適化やCVの最大化とともに、時には影響を及ぼすボリュームや、CVへの間接的な影響を与える質的なデータを総合的に評価する必要があります。
また、施策全体の設計がある場合、消費者の長期的な態度変容を評価する指標を足すこともあります。

データ抽出のためのSQLテンプレート

ここのSQL構文は、BigQueryで適用する標準SQLです。
このテンプレートは、ログデータを企業の購買データと結合することを想定し、書いています。

購買データと結合するメリットはいうまでもなく、前述のリスティング広告例ですと、仮にCVは購買とすれば、リスティング流入のユーザーは、いったいなにを買って、いくら買ったかまで計測することが可能になります。

テンプレートを貼る前に、まずこういったSQLを書く時の考え方を記述したいと思います。

条件が多いので、基本はWITH句で徐々に書いていくことがお薦めです
最初のWITH句では、ターゲットユーザーを絞り出すことを目的とします
抽出したいログデータのディメンションとメジャーを次のWITH句で書きます
2と3をLEFT JOINします
抽出したい購買データを更に次のWITH句で書きます
4と5をLEFT JOINします
あとでTableauの計算を軽減するために、ここでは一部のデータに対してflgを立てます

7について、少し補足したいです。
量のあまり多くない企業では、Tableau上で計算するのに、あまり待つことはないでしょうが、一定量以上のデータを保持する企業では、全件レコードをTableau上で計算しようと思うと、待つ時間でアナリストの心が枯れてしまうと言っても過言ではありません。

そこで、ディメンションを設計する時に、必要な情報に絞ること以外は、TableauでCOUNTD（SQLではCOUNT DISTINCT）をできるだけ使わないのと、複雑なLOD表現を使う必要な箇所に対して、予めSQLで計算させた二点に注意を払いましょう。

flgを立てることはまさに後ほどTableau上で探索・分析することをより楽にさせるための処理となります。
通常は、CVしたvisit（session）やvisitorに対して「CVしたなら1していないなら0」という処理をかけることが多いです。
※「CVしたならidを返す、していないならNULLを返す」こともSQLではできますが、後ほどTableauの計算スピードを上げるために、ここでは1と0を返すことが無難です。

テンプレートは以下のコードとなります。

CREATE TEMPORARY FUNCTION _start_date() AS (TIMESTAMP('2018-06-30'));
CREATE TEMPORARY FUNCTION _end_date() AS (TIMESTAMP('2018-07-30'));

/* テーブルとして保存したほうが、Tableauで計算する時にスピードがあがります */
-- CREATE OR REPLACE TABLE `templete` AS

WITH check_data AS
(
SELECT　DISTINCT /* 指定の期間の前の期間に訪問したことのあるユーザー */
  visitor_id
FROM
  `adobe_data`   /* ここでは自社のデータマットに書き換えてください */
WHERE
  _PARTITIONTIME BETWEEN _start_date() AND _end_date()
  -- AND other_rules
  /* ここでは、分析要件に沿って、必要な除外条件を足してください */
)


, search_data AS
(
SELECT /* 指定の期間の前の期間に訪問したことのあるユーザー */
  visitor_id
  , visit_id
  , visit_num
  , visit_page_num
  , purchaseid
  , hit_time_gmt  /* 日付データは最後に整形したほうがより計算スピードがあがります */
 -- ,TIMESTAMP(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',TIMESTAMP_SECONDS(hit_time_gmt), 'Asia/Tokyo')) as hit_time_gmt_formatted

FROM
  `adobe_data`

WHERE
  _PARTITIONTIME BETWEEN _start_date() AND _end_date()
)


, target_data AS
(
SELECT
  search_data.*
FROM search_data
LEFT JOIN check_data
ON search_data.visitor_id=check_data.visitor_id
WHERE check_data.visitor_id IS NULL
)


, purchase_data AS
(
  SELECT
   purchase_id
   , purchase_time
   , *
    /* 購買データは、ログデータに比較し、データが正確かつ量が少ない特徴があるため、指定期間の全データを*で抽出することも可能です */
  FROM `purchase`
  WHERE
    TIMESTAMP(DATE(purchase_time)) BETWEEN _start_date() AND _end_date()
)


, combined_data AS
(
  SELECT
    *
    , DENSE_RANK() OVER(PARTITION BY visitor_id ORDER BY visit_num) AS visit_number_renew /*visit_numを1から振り直す*/
    , COUNT(1)OVER(PARTITION BY visit_id) AS visit_pv

  FROM target_data
  LEFT JOIN purchase_data
  ON target_data.purchaseid =purchase_data.purchase_id
  /* JOIN する場合は、visitorでJOINすると、行が非常に増えるため、purchaseのidでJOINしたほうがいい */
)


, flg_data AS
(
SELECT
  *
  , MAX(CASE WHEN purchaseid IS NOT NULL THEN 1 ELSE 0 END)OVER(PARTITION BY visitor_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS visitor_rsv_flg

FROM combined_data
)


SELECT
  *
FROM flg_data