いよいよレポート作成
この記事では、Googleが提供するBIツールLooker studioでBigquery上のデータをレポート化する手順について初心者向けに解説しています。
前回の記事を読んでいない方は、是非こちらからご覧ください。
さて、今回はいよいよ、実際にBigquery上でレポートを作成していきます。
前編の記事にも書いた通り、複数のテーブルを参照するためにSQLでテーブル結合をする必要がある項目は後にして、先にSQLを書く必要がない項目のレポートを作っていこうと思います。
レポート作成(SQLを使わない項目)
いよいよLooker studio上でレポートを作成していきます。まずは前回と同様に、Looker studioを開きます。
戻ってきました。先ほどの確認の際にテーブル自体は追加したので、すでに取り込まれています。(右の赤い四角の部分にテーブル一覧が出ている)
違う方法でデータ確認した方も、このタイミングでテーブル追加してしまいましょう。追加の方法については前回の記事で紹介しているのでご確認ください。
さて、レポートに取り掛かります。本当はすべての項目について作っていく様子を記事に書きたいのですが、途方もない分量になってしまいそうなので…
・どのくらいの人が使っているのか(サイト管理者)
・売上総額はどのくらいか(サイト管理者)
・どこから流入した人がどのくらい登録しているか(マーケティング担当者)
の3項目について手順を紹介しながらレポートを作っていきます。
どのくらいの人が使っているのか(サイト管理者)
始めは、サイト管理者用のレポートの一項目である、「どのくらいの人が使っているのか」。これは、“users”テーブルのカラム数(つまり、登録者の総数)で定義しました。
レポートを作るときは、
の順でやっていきます。①のフィールドの設定は、既存のフィールドをもとに四則演算をおこなったり関数に入れたりして、新しいフィールドを作る必要がある場合に行います。この項目はカラムの数をただ数えたいだけなので、ここは今回無視してよさそうです。
次に、表・グラフの形式を選びます。
ページ上部、グラフを追加のところをクリックすると候補となる表やグラフがずらっと出てくるので、合いそうなものを選びます。今回は登録者数を表示させたいだけなので、スコアカードにします。
適当なところにスコアカードをドラッグして置いたら、指標を設定します。今回は“users”テーブルで、カラムの数を数えたいので、Record countの指標を使います。(Record countについては初期設定で指標があります)
テーブルの設定は「データソース」のところをクリック、指標の設定は「指標」のところをクリックすれば変えられます。(画像の赤い四角で囲ったところ)
どうやらこのECサイトに登録しているユーザーは98210人いるようです。
最後に、見た目を整えていきます。全体的にレイアウトしたり色を整えたりといったことは一番最後にやろうと思うのですが、とりあえず何の項目か分からなくなってしまいそうなので…
指標名(上に小さくRecord countとか出ていたやつ)を非表示にして、左に「登録者数」のテキストを挿入しておきました。とりあえずこれで完成です。
売上総額はどのくらいか(サイト管理者)
次の項目は売上総額です。定義は「注文された商品の売値の合計」となっています。(先ほどまとめたExcelファイル参照)
ということは、注文商品テーブルの売値フィールドにSUM関数を使って新しい「売上合計」フィールドを作る必要があります。
新しいフィールドを作るには、「リソース」→「追加済みのデータソースの管理」から、
フィールドを追加したいテーブルの✎編集を押し、(今回はorder_itemsテーブル)
＋フィールドを追加ボタンを押し、「計算フィールドを追加」を選択します。
するとフィールドの設定場面に飛ぶことができるので、必要に応じて設定を行います。今回は売値フィールド(sale_price)にSUM関数を使ったフィールドが欲しいので、このように設定しました。
フィールドに名前を付けると、追加できるようになります。
ちなみに、構文などに誤りがある場合には、エラーが出るようになっています。
あとの手順は先ほどとほとんど一緒です。表・グラフの形式を選択して、指標を設定します。今回も特にグラフにする要素がないかなと思いスコアカードにしました。
指標は、今さっき設定して名前をつけた新しいフィールドです。
先ほどと同じように見た目を整えるところまでやると、このようになります。
「売上総額」の文字と「＄(単位)」は、テキスト挿入機能で入れました。この項目もこれで完成。先ほども少し書きましたが、全体のレイアウトを整えたりデザインを整えたりといったことは最後にやろうと思います。
どこから流入した人がどのくらい登録しているか(マーケティング担当者)
3つ目の項目を作っていきます。登録者がどこから(何をきっかけに)登録したかということを知りたいので、“uses”テーブルのトラフィックソースを参照します。人数を比較して流入元ごとに割合を出したいので、指標はRecord countにします。
「グラフを追加」から今回は円グラフを選んで、指標はトラフィックソースのフィールドのRecord countにしたのですが、ここで問題が。
このフィールドは英語で情報が入っていたので、円グラフの項目名も英語になってしまいました。
これで伝わるのであれば英語でも問題はないのですが、より見やすくするために項目を日本語にすることにしました。 このように項目名を変えたいときは、新しいフィールドを追加してCASE構文を使います。
こんな感じです。
このフィールドをディメンションにしてもう一度ドーナツグラフを作ると、
無事日本語になりました。このCASE構文は色々と便利そうです。
ということでSQLを使わない項目に関しては3つ項目を抜き出してやってみましたが、他の項目についても同じような要領で作ってみます。
レポート作成-2(SQLを使う項目)
次に、複数のテーブルを参照する必要がある項目のレポートを作成していきます。複数のテーブルを参照する必要がある項目とは、先ほど整理したExcel表⇩
でいうと、青色になっている項目のことです。複数のテーブルを参照するには、テーブル同士を結合し、結合したテーブルを参照してレポートを作る必要があります。
Looker studioの機能でもテーブルを結合することはできるのですが、一部手順が煩雑になってしまうため、今回は自分でSQLを書いてテーブルを結合します。
自分で…といっても、前述したように自分で0からSQL文を書くというのは初心者の私にとってはハードルが高いしなによりエラーが出た時に自分で直せる自信もないので、弊社でリリースしているSQLクエリ作成が簡単に行えるツールMagiqを使って書いてみることにします。
この記事では、サイト管理者用の項目であるリピート率の項目を作る様子について紹介します。
まずは項目の定義を考えて、どのようなテーブルができればレポートを作ることができるのかということをイメージしていきます。
項目の定義はExcel表の方にまとめてありますが、「登録者のうち、その後半年間でもう一度購入している割合」でした。
ということは、登録しているユーザーごとに1回目に購入してから二回目に購入するまでにかかった期間を出して、それが半年以内(今回は簡単に、180日以内)であれば定義に当てはまっていると考えることができます。購入日の情報が入っているのは“order”テーブルです。年齢や性別など登録ユーザーの情報が入っているのは“users”テーブルで、ユーザーの情報に応じてリピート率を比較することを考えると、このテーブルの情報もあった方が良さそうです。
というわけで、この二つのテーブルが結合できれば良さそうです。 ここで、そもそもテーブル同士を結合するとは？ということが分からない、知りたいという方は、こちらの記事でテーブルの結合やその種類について解説しておりますので、ぜひご覧ください。
本記事では、リンク先の記事で説明があるような結合についての知識を前提に進めていきます。
さて、欲しいテーブルの完成イメージは次のようになります。
1st_DATEが1回目に購入した日付、2nd_DATEが2回目に購入した日付として、その差をとったカラムを「差期間」として作り、リピート期間を調べます。
完成のイメージが付いたところで、このテーブルを作るために、今あるテーブル同士をどのように結合していけばよいか考えます。
いくつか方法はあると思います。どの関数を使うかによっても結合の仕方は変わってきますし、慣れている人は一つ一つサブクエリに分割しなくても一気に結合できるかもしれません。
しかし、不慣れな私は、ひとまず必要な要素を一つずつサブクエリに分割して元テーブルから抽出していき、メインクエリにてそれを一気に結合するという方法をとることにしました。
イメージとしては図のような感じです。
とにかく初心者の私でも分かりやすく、必要な情報を一つずつサブクエリに抜き出していき、メインクエリですべての情報を結合するという方法にしました。
サブクエリ1で、顧客の性質ごとに分析を行う際に必要となる年齢、性別といった情報を“users”テーブルから抜き出しました。 次に“order”テーブルから顧客ごとの1番目、2番目の注文日時をそれぞれ抜き出したいところですが、その前にまず注文日時を順番に並べ順位をつける必要があります。その作業を行っているのが、サブクエリ2です。注文日付を抜き出し、RANK関数を使って順位をつけています。
RANK関数についてより詳しく知りたいという方は、こちらの記事に説明がありますので、是非ご覧ください。
順位をつけられたら、1番目の注文日時をサブクエリ3で、2番目の注文日時をサブクエリ4で抜き出します。
必要な情報をサブクエリでそれぞれ抜き出せたら、メインクエリにてサブクエリ同士を結合していきます。結合の種類は、LEFT JOINにしました。INNER JOINにすると、サブクエリ3の2nd_DATEが空欄だった場合(1回目までしか購入をしていない場合など)にデータが抜き出されなくなってしまうからです。※結合の種類についての解説参照
これでリピート率を計算するために必要な表が出来上がりそうです。本当は先ほど言及したように1番目と2番目の注文日時の「差期間」も出さなくてはならないのですが、それはLooker studio上で行うことにします(もちろんMagiqを使うこともできますが、簡単な処理でLooker studio上でも完結しそうなので、こちらでやってしまうことにします)。
結合の方法まで決められたら、あとはその通りにMagiqを使ってクエリを作っていきます。
「クエリビルダ」タブから、クエリを作成していきます。
まずはサブクエリ1から。
サブクエリ1では、“users”テーブルから会員ID、年齢、性別の情報を抜き出すのでした。
Magiqでは、抜き出す元のテーブルと抜き出したいカラムさえ指定すれば、データの抽出をドラッグアンドドロップで直感的に行うことができます。
さっそく、決めておいた通りに入れてみます。
こんな感じです。簡単にできました。
サブクエリ2についてはもう少し複雑です。
まずは、結合の際に必要となる共通のカラム(この場合は顧客ID)を抽出します。(「取得フィールド」の一番上)
Sub2を直接ほかのテーブルと結合するわけではないですが、ここからこのIDの情報をsub3や4に引っ張って行かなくてはならないので、ここで取得しておく必要があります。
次に、注文日時を取得します。今回“order”テーブルにおいて注文日時はタイムスタンプ型のデータタイプで格納されているので、DATE関数を使って日付型に直しています。 最後に、注文日時に順位をつけていきます。
顧客ごとに順位をつけたいので、PARTITION BYの後に来るのは顧客IDになります。
次に、サブクエリ3です。
サブクエリ3では、先ほど順位付けした注文日時のうち1番目のものを抜き出します。結合する際のキーとなる顧客IDも忘れないように取得します。
サブクエリ4もほとんど同じです。
クエリが作成出来たら、ページ上部にあるスケジュールクエリ設定が完了していることを確認します。
完了していない場合は、テーブル名・スケジュール(データを取ってくる頻度)、宛先データセット・テーブル(データがどこに入るか)などを設定します。ステータスは「有効」の状態にしておきます。
設定できていたら、ページ下部の「文法チェック」を押し、文法が正しいことが確認出来たら「登録」ボタンでクエリを登録します。
あとは、これまでと同じようにLooker studio上で先ほど付けた名前のテーブルを追加し、レポートを作成していきます。
見やすいレポート作成のコツ
というわけで、最後までレポートを作ってみました。
こんな感じです。
なかなかいい感じにできたんじゃないかなと思います。下のURLから今回作ったレポート全体を見ることができるので、是非ご確認ください。
サイト管理者用のレポート：https://lookerstudio.google.com/reporting/ec22aa94-b7cc-42f5-b88f-9730ebc221d2
マーケティング担当者用のレポート：https://lookerstudio.google.com/reporting/15cac459-4592-49be-b513-c9c600be365f
作ってみて感じた見やすいレポートにするためのコツとして、「レイアウトを揃える」というものがあります。
グラフの配置であったり文字の大きさであったりといった全体の配置をページによって変えないようにしておくと、見る側にとってもページを切り替えたときの脳の処理コストが減って見やすくなります。
今回のレポートもそれを意識して作成してみたので、是非ご確認ください。
SQL文の生成を助けるお役立ちツール
今回は、Looker studioを用いて架空のECサイトの運用状況を一目で確認できるレポートを作成する様子をご紹介しました。
レポート作成では、今回購入率を計算した際のように、テーブル結合が必要となりSQLを書くことが求められることが非常に多くあります。取得したデータをLooker studioにそのまま入れて有用なレポートになるという場合はあまり多くありません。実際には、SQLによってデータを知りたい情報に合わせ加工する必要があるのです。
Magiqでは、Looker studioレポート作成のためのデータ成形に用いるSQL構文も今回のようにUIを用いて簡単に作成することができます。
SELECT文といった基本の構文だけでなくウィンドウ関数などの一段階複雑な構文も直感的な操作によって作成可能であるため、SQLになじみのない方や初心者の方など、1から自分で書くのは不安という方でも簡単にSQL文を書くことができます。
Magiqでは現在、β版モニターを募集中です。是非お気軽にお問い合わせください。
お問い合わせはこちら！