【初級編】BigQueryの実務活用方法について

目的

「普段BigQueryを勉強をしているけど、実際に実務でどう活用されているんだろう」と思っている方向けに、現場ではこういう使われ方をしているよというのを、実践ベースにまとめました。

実際に手を動かし、BigQueryの実務での使われ方を知ること

こちらを目的にやっていきましょう。

はじめに

今回の内容はBigQueryの基本的な知識がある前提でお話いたします。

もし、BigQueryってなに?という方は以下の記事を先にご確認いただくとわかりやすくなると思います。

データレイク、データウェアハウス、データマートって結局なんなん?

また、GCPは無料利用枠で実施いたします。GCPのご登録をお済みの上お進みいただけますと幸いです。

第2回:マーケティング担当のためのBigQuery入門 ~アカウント作成編~

題材例とデータの流れ

今回は、実際の現場をイメージしやすくするために題材例を用意しました。

ゴールとしては

ダッシュボードを作成し、分析結果を出せる状態にすること

データの流れは以下のようにしましょう。

GCPツールについて

今回使用するツールについて、簡単に触れておきます。

Google Cloud Storage

rawデータを保存しておく場所です。データレイクとしてよく使用されます。

詳細:https://www.isoroot.jp/blog/1403/

Google Data Portal

Googleの無料で使えるBIツールです。非常に簡単に可視化することができます。

詳細:https://quickdmp.ayudante.jp/basic/dataportal/

構築開始

GCSにデータを送る

実際に構築を進めていきます。

まずは、Google Cloud StorageにCSVデータを格納していきましょう。

使用するデータは以下です。

ローカルに保存してください。

方法

GCSにデータを送る方法は以下3つです。

  1. Management Consoleからデータを送る
  2. CLI(API)からコマンドを叩いてデータを送る
  3. GCPツールを使用する

今回は初級編ということもあり、一番簡単な①で実装します。

実装

①バケットを作成

②ファイルをアップロード

以上です。めっちゃ簡単ですね(笑)

GCSからBigQueryにデータを送る

つづいて、Google Cloud StorageからBigQueryにデータを送ります。

方法

Google Cloud StorageからBigQueryにデータを送る方法は以下4つです。

  1. BigQueryで直接呼び込む
  2. Data Transfer Serviceを使って送る
  3. CLI(API)からコマンドを叩いてデータを送る
  4. GCPツールを使用する

今回も一番簡単な方法である①で実装します。

その前にBigQueryの構造と機能について説明します。

BigQueryの構造について

BigQueryは、プロジェクト>データセット>テーブルという構成になっており、データ処理が高速なことで有名なデータウェアハウスです。BigQueryはなぜデータ処理が高速なのかご存知ですか?

カラムごとにデータが圧縮され、保存される仕様(カラム型指向データベース)のため、不要な列を読み込まず、必要な列のみにアクセスすることができるんですね。なので、データの処理が通常のRDBよりも早くなるみたいです。

ただ、デメリットもあります。

列ごとにデータが保存されているので、すべての行を取得して返される仕様になっています。そのためクエリコストが多くなってしまうんですね。

ただ、こんなことを思うかもしれません。

では、検証してみましょう。

WHERE句で区切っていないSQL文と区切ったSQL文のクエリ容量はどうなるでしょうか。

はい、同じですね。これはどういうことかというと、WHERE句で区切ったとしても「一度全部の行を見てからWHERE句でセグメントした行だけを抽出する」という仕様になってしまうので、結局全部みてるのと変わらないんですね。

このままではクエリのコストが高くなってしまう。どうするか・・・。

パーティショニングについて

BigQueryにはテーブルを作るときに「パーティショニング」という設定方法があります。これは「テーブルをある値に応じて内部的に分割して保存する」という機能になります。「ある値」というのは、例えば日付や時間など日時に関するものを指定するのがセオリーです。また、取り込み時間による分割も可能となっています。「内部的に」というのは、テーブルをUIでみれるような形でわけるのではなく、UIでは一つのようにみえるけど、中ではちゃんと分けて保存してるよという意味になります。

このようにパーティショニングテーブルとして作成すると・・・

先程のWHERE句でセグメントしたSQL文で、クエリ容量がKBからBまで削減できています。これはコストに大きく関わるのでなるべく設定することをおすすめします。

スキーマについて

スキーマというのは、「テーブル内にどういうデータがはいっているかをまとめたもの」というものです。テーブルを作成するときに「カラム名」「データ型」「モード」を指定して、テーブルを作成します。

説明は以上です。それでは、実際に作っていきましょう。

実装

①データセットの作成

②テーブルを作成

※今回のスキーマ設定はJSON形式で直接入力する方式で設定いたします。以下のコードをコピペしてご使用ください。

sample_ga

[
  {
    "mode": "NULLABLE",
    "name": "date",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "channel_grouping",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "medium",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "sessions",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "pageviews",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "bounces",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "session_duration",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "exits",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "users",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "new_users",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "goal1_completions",
    "type": "INTEGER"
  }
]

sample_googleads

[
  {
    "mode": "NULLABLE",
    "name": "segments_date",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "campaign_id",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "campaign_name",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_cost_micros",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_impressions",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_clicks",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "metrics_conversions",
    "type": "INTEGER"
  }
]

sample_yahooads

[
  {
    "mode": "NULLABLE",
    "name": "DAY",
    "type": "DATE"
  },
  {
    "mode": "NULLABLE",
    "name": "CAMPAIGN_ID",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "CAMPAIGN_NAME",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "COST",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "IMPS",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "CLICKS",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "CONVERSIONS",
    "type": "INTEGER"
  }
]

上記工程のように「sample_googleads」「sample_yahooads」も実施し、3つのテーブルを作成する

BigQueryでSQLを作成し、DataPortalでダッシュボードを作る

最後にBigQueryでSQLを作成して、DataPortalで可視化します。

方法

BigQueryからDataPortalにデータを送る方法は以下2つです。

  1. BigQueryでテーブルを作り、読み込む
  2. カスタムクエリで読み込む

2つとも方法を説明します。

1.BigQueryでテーブルを作り、読み込む

クエリコストを考慮すると、普段のダッシュボード構築は1を選んだほうがいいかと思います。

1の場合は、データを定期的に自動で更新するために「スケジューリングクエリ」の設定が必要です。

スケジューリングクエリ

説明は以上です。それでは、実際に作っていきましょう。

実装

①SQLクエリを作成し、保存する

使用するクエリ

CREATE OR REPLACE TABLE
merchandise_store.merchandise_store_datamart
PARTITION BY
day AS
WITH
ad AS (
SELECT
day,
"Paid Search" AS channel,
SUM(cost) AS cost
FROM (
SELECT
segments_date AS day,
metrics_cost_micros AS cost
FROM
`自身のプロジェクト名.merchandise_store.sample_googleads`
UNION ALL
SELECT
DAY AS day,
COST AS cost
FROM
`自身のプロジェクト名.merchandise_store.sample_yahooads` )
GROUP BY
day),
ga AS (
SELECT
date AS day,
channel_grouping AS channel,
sessions AS session,
goal1_completions AS cv
FROM
`自身のプロジェクト名.merchandise_store.sample_ga`)
SELECT
ga.day,
ga.channel,
ad.cost,
ga.session,
ga.cv
FROM
ga
LEFT JOIN
ad
ON
ga.day = ad.day
AND ga.channel = ad.channel

②スケジューリングクエリの設定

マークが緑になっていれば問題なく実行されます。

BigQuery側の操作は以上になります。

続いて、DataPortalでの操作になります。

①DataPortalにログイン

https://marketingplatform.google.com/intl/ja/about/data-studio/

最後に体裁を整えて完成です。

2.カスタムクエリで読み込む

カスタムクエリは、アドホックに分析したいときに使用するのがいいかと思います。カスタムクエリ上でJOINやUNIONをしたクエリを実装してしまうと、DataPortalで多いクエリ容量で動き続けることになるので、コストの増加につながります。

カスタムクエリ

説明は以上です。それでは実際に作っていきましょう。

実装

①SQLクエリを作成し、保存する

使用するクエリ

WITH
ad AS (
SELECT
day,
"Paid Search" AS channel,
SUM(cost) AS cost
FROM (
SELECT
segments_date AS day,
metrics_cost_micros AS cost
FROM
`自身のプロジェクト名.merchandise_store.sample_googleads`
UNION ALL
SELECT
DAY AS day,
COST AS cost
FROM
`自身のプロジェクト名.merchandise_store.sample_yahooads` )
GROUP BY
day),
ga AS (
SELECT
date AS day,
channel_grouping AS channel,
sessions AS session,
goal1_completions AS cv
FROM
`自身のプロジェクト名.merchandise_store.sample_ga`)
SELECT
ga.day,
ga.channel,
ad.cost,
ga.session,
ga.cv
FROM
ga
LEFT JOIN
ad
ON
ga.day = ad.day
AND ga.channel = ad.channel

BigQuery側の操作は以上になります。

続いて、DataPortalでの操作になります。

①DataPortalにログイン

https://marketingplatform.google.com/intl/ja/about/data-studio/

↑作成したクエリをコピペします。

最後に体裁を整えて完成です。

まとめ

お疲れさまでした!!

無事構築することはできましたでしょうか?

これで、GCS→BigQuery→DataPortalの一通りの流れの構築ができるようになりました。

今回実装した内容はあくまで初級編ということで手動メインの構築になりました。

次回は、処理を自動化させて構築するフェーズのお話をしたいと思います。

お楽しみに!