web広告のレポート作成のために、Google データポータル（旧 Google データスタジオ）を使っている。
その際に、Big Query （Google のデータベースサービス）で sql文 （データベースを操作するプログラミング）を書いたことがあったので、そのノウハウについて書いていきたい。
Google データポータル、BI ツールについて
Google データポータルは、様々なデータをグラフや表に可視化してレポートを作成を効率化したり、データからインサイトを発見するためのツールである。
「グラフや表の作成なら、Excel を使えばいいのでは？」と思う人も多いかもしれない。
今回作るようなweb広告レポートやwebアクセス数レポートなど、フォーマットが決まっているレポートを、Excel でレイアウトをいちいち整えて作成するのは非効率だ。
そこで登場するのが、「データポータル」。
「データソース」という元データを接続して、あとはレポートのフォーマットを作っておけば、システムが自動でデータを取得してリアルタイムにレポートを作成してくれる。
Google データポータルをはじめ、このようなツールを総称して「BIツール」と呼ぶ。ほかには、マイクロソフトのPower BIや Tableau、Amazon QuickSight などがある。
今回の作業のデータの流れ
今回は、web広告に関するデータをGoogle データポータル上でレポート化する。そのデータの流れについて、軽く触れておく。
① 複数媒体の広告データを一括して取得（外部サービスを利用）
Google データポータルは Google のサービスなので、Google の広告ならそのままデータソースとして選択してレポートを作成することができる。
しかしながら、web広告は Google、Yahoo、facebook などの様々な媒体上で運用されることが多い。そんな時に、データがあちこちに散らばっているうえに、同じものを意味しているにも関わらず指標の名称が微妙に異なったりして、複数媒体のweb広告を同じレポートに出力するのは難しい。
そこで、「Databeat」と呼ばれるサービスに自社のweb広告アカウントを連携させ、異なる広告媒体のデータを一括して扱えるようにするのだ。
②Databeat から、Google BIg Query 上に広告データを出力する
Databeat は、複数の広告のデータを一括して扱える他にも、統一されたデータ形式で Google Big Query にデータを出力する機能もある。
Google Big Query は、ビッグデータを高速で扱えるGoogle のデータベースサービスだ。
Google Big Query に統一された形式でデータがストックされていれば、Google データポータルのデータソースとして思いのままにレポートを作ることができる。
③Google データポータルでデータを可視化する
作りたいレポートとGoogle Big Query のデータソースを接続できたらば、あとは Google データポータル上で自分の思うようにレポートをつくるだけだ。
データポータルでの表の作り方やデータの扱い方については、また別に機会に紹介したい。
Big Queryをデータソースにして、Google データポータルでレポートを作成する方法
言葉で説明されてもわかりづらいと思われるので、データソースの接続方法とレポートの作成方法について書いていきたい。
データソースを作成
左上の「＋ 作成」を押し、その次に表示される「データソース」という部分をクリック。
データソースの種類から、Big Query を選択
次に、データソースを選ぶ画面になる。
左上部分で、データソースの名前を変更することができる。何のためのデータなのか分かりやすい名前を付けておきたい。
今回はBig Query を使用するので、Big Query の部分をクリックする。
Big Query との接続の種類
BIg Query に出力されたデータに対して特に編集せず、そのまま使うには「マイプロジェクト」→「データセット」→「表」の順で必要なものを選択し、右上部分にある「接続」のボタンを押す。
すると、選択したデータソースに含まれている指標の一覧の画面になる。このデータソースを使ってそのままレポート作成に進む場合は、右上にある「レポートを作成」に進む。
カスタムクエリで入力するsql文の解説
先ほどは「マイプロジェクト」から設定する方法を紹介したが、今度はデータソースをsql文によって手を加えて使用する方法を紹介する。
カスタムクエリは、一番左のタブから「カスタムクエリ」を選択し、そのあと、使いたいデータが入っているプロジェクトを選択すると、sql文を入力する画面が現れる。
今回のweb広告データを扱う際に、主に2つの目的でカスタムクエリを入力した。
① デバイスの名称が媒体によって異なっているので、それを統一する
② Google, Yahoo Sponsord Serch, Yahoo Display Network にまたがる同一広告主のデータを統合する
（マイプロジェクト→データセット→表 で選択したデータには、1広告主の 1媒体分のデータしか含まれていない）
それぞれの方法のために書いた sql 文を、以下で順を追って紹介していきたい。
① デバイスの名称が媒体によって異なっているので、それを統一する
SELECT
WHEN Device='Tablets with full browsers' THEN "TABLET"
WHEN Device='Mobile devices with full browsers' THEN 'SP'
WHEN Device='Computers' THEN 'PC'
END
As Device2
FROM `[プロジェクト名].[データセット名].[表の名前]` WHERE Date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
1行目の「select」は、sql文でデータを抽出するときのお約束となっている文。
8行目の「FROM」は、どのデータセットからデータを抽出すべきかを指定している。[プロジェクト名], [データセット名], [表の名前] の部分は、マイプロジェクトから単一データとして選択するときの名前を入力する。（実際に名前を入力する際は、カッコは不要）
8行目の WHERE 以下の部分は、どの範囲のデータを取得するかを命令している。上記の場合は、データが存在する一番最初のものから、一番最後のものまですべて選択している。この部分は、特にいじらず使いまわしていた。
2~5行目が、場合分けによってデバイス名を新しくつけ直していることを意味している。（CASE ~ END という構文になる）
「Device」の列が「Tablets with full browsers」というデータの場合は「TABLET」というデータを入力、「Mobile devices with full browsers」のときは「SP」(Smart Phone の略) と入力、ということを表している。
そして、6行目にある「As Device2」という部分で、「Device」という列から派生した新しい列に対して「Device2」という名前を付けること意味している。
② Google, Yahoo Sponsord Serch, Yahoo Display Network にまたがる同一広告主のデータを統合する
ここから、いきなりステップアップして実戦に使用したコードを掲載する。
長くなっているが、決まった要素の繰り返しでしかないので、一つ一つ分解していけば問題ない。
SELECT *
FROM(
SELECT *,
CASE
WHEN AdvertisingChannelType='SEARCH' THEN "GSS"
WHEN AdvertisingChannelType='DISPLAY' THEN 'GDN'
END
As ServiceName,
CASE
WHEN Device='TABLET' THEN "TABLET"
WHEN Device='HIGH_END_MOBILE' THEN 'SP'
WHEN Device='DESKTOP' THEN 'PC'
END
As Device2
FROM `[プロジェクト名].[データセット名].[表の名前]` WHERE Date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
)
UNION ALL
SELECT *
FROM(
SELECT *,"YSS" As ServiceName,
CASE
WHEN Device='Tablets with full browsers' THEN "TABLET"
WHEN Device='Mobile devices with full browsers' THEN 'SP'
WHEN Device='Computers' THEN 'PC'
END
As Device2
FROM `[プロジェクト名].[データセット名].[表の名前]` WHERE Date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
)
UNION ALL
SELECT *
FROM(SELECT *,"YDN" as ServiceName,
CASE
WHEN Device='Tablet' THEN "TABLET"
WHEN Device='SmartPhone' THEN 'SP'
WHEN Device='PC' THEN 'PC'
END
As Device2
FROM `[プロジェクト名].[データセット名].[表の名前]` WHERE Date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
)21行目と 37行目の「UNION ALL」という部分で、Google , Yahoo 検索、Yahoo ディスプレイ の3媒体にまたがるデータを統合している。
5~9行目は、Google のデータに「ServiceName」という列を追加し、条件分岐によって「GSN」（検索広告）や「GDN」（ディスプレイ広告）という名前を付けている処理だ。
（Yahoo は検索広告とディスプレイのデータが別の表になっている一方で、Googleは検索広告とディスプレイ広告が同じ表に含まれており、「AdvertysingChannelType」という列の値によって区別できる）
「Select *」のアスタリスクマークは、もともとの表にあるすべての列を取得するという意味である。
From のあとに Select を置いたりカッコを付けたりしているのは、sqlの処理順序を考慮したためだ。
UNION ALL をするときは、統合するすべての列の名前と順序がそろっている必要がある。
sqlの詳細については、こちらの記事を参考にして頂きたい。
カスタムクエリを使うときの注意点
日付パラメータを有効にする
sql文で上記のようなカスタムクエリを書いた場合、下図にある「日付パラメータを有効にする」の部分にチェックを入れる必要がある。
チェックを入れ忘れると、エラーが返された。おそらく、WHERE 文で@DS_START_DATE, @DS_END_DATEという文を使っているためであると思われる。
プロジェクト名が日本語やスペースを含む時
カスタムクエリを作成しようとしていて、sqlの文において
FROM `[プロジェクト名].[データセット名].[表の名前]`
という部分があったが、big query のプロジェクト名に日本語やスペースが含まれることがある。
データソース選択画面に表示されているスペース含みのプロジェクト名をsql文に入力してしまうと、エラーが返される。
そんな時は、プロジェクト名のタブにカーソルを置くと、「別タブで開く」という意味の矢印が表示される。
その矢印を押すと、データソースとなるBig Query の画面が別タブで開かれる。（これまで開いていたのはデータポータルの画面）
Big Query の画面左の方に、さっき選んだプロジェクト名のタブがあるはず。そこにカーソルを合わせると、「core-synthesis-[6ケタくらいの数字] 」 が小さく表示される。
この、core-synthesis-******* をプロジェクト名のところに使えば、エラーは生じない。この情報は、Big Query のURLからもわかる。
カスタムクエリを書いたエラーが吐きだされる場所
データポータル上の画面でカスタムクエリを打ち込んでいると、当然エラーが返されることがある。
しかしながら、プログラミングしたときに返されるエラーとは異なり、エラーコードしか返ってこない。エラーコードだけだと、どんなエラーが生じたのかは具体的に知ることはできない。
そんな時は、先ほどの方法で Big Query の該当プロジェクトのページを開いてほしい。
画面左上に「Query History」という部分がるので、そこを押せばデータポータル上で生じたエラーを見に行くことができる。
