【データ活用初心者マーケターへ】BigQueryとtrocco®でGoogle Search Consoleのデータを手軽に可視化する方法

## はじめに
::: note info
この記事は、データ可視化初心者マーケターによる、データ可視化初心者マーケターのための記事です。
:::

Google Search Consoleのデータは、ウェブサイトのパフォーマンスを評価し、SEOの戦略を策定する上で欠かせないツールです。
Google Search Consoleには標準のレポート機能があり、WebサイトのGoogle検索結果ページへの表示回数やページのクリック数、掲載順位の推移、どのようなクエリがサイト流入につながっているのか、といったことを確認できます。

Google Search Consoleはとても便利なツールではありますが、

**「数値の推移はわかった…！
でも、それ、月次・週次単位で比較したいんや…！」**

と思ったことがあるマーケティング担当の方は少なくないのではないでしょうか。

また、各数値を月次・週次で見るために、日々手動でデータをインポートし、スプレッドシートに張り付けて、グラフ化して…と頑張っている方もいるでしょう。

そこで今回は、Google BigQueryとtrocco®、Google Looker Studio、さらにChatGPTを使い、
「Google Search Consoleを月次・週次で見えるようにしたい」
という希望を**簡単**に叶えるための方法を紹介します。

2023年2月にリリースされた[Google Search Consoleの一括データエクスポート機能](https://developers.google.com/search/blog/2023/02/bulk-data-export?hl=ja)を使えばtrocco®を使わなくてもデータをBigQueryにエクスポートできますが、2022年のデータも可視化したかったので今回はtrocco®を使ってデータを可視化してみました。 
※trocco®は無料で利用できます。

なお、今回紹介する方法は、データベースやデータエンジニアリングに関する専門的な知識がない方でも行えるものです。
この記事を参考に、ぜひデータの可視化に挑戦してみてください。

### こんな人におすすめ
- データ集計を効率化したいと思っているマーケティング担当者
- SEO担当者・WebサイトのKPIを追っている方
- BIツールの活用に興味がある方

## データ可視化のゴール
今回はGoogle Search Consoleにあるデータ（imp、click、ctr）をGoogle BigQueryに転送し、Google Looker Studioで可視化することを目標にします。（下画像）

![Search Console 月次・週次レポート_完成イメージ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/1817da51-0b20-1ce3-26e0-476fcda4c56b.png)

## データ転送をするための事前準備
### trocco®アカウント（無料）の作成
Google Search Consoleのデータを転送するにあたり、分析基盤構築・運用の支援SaaS「[trocco®](https://trocco.io/lp/index.html)」を利用します。

trocco®にはクレジットカード不要・月額利用料0円のフリープランがあります。
事前に申し込みをしてアカウントを作成しておきましょう。
https://trocco.io/lp/inquiry_free.html

また、データ元のGoogle Search Consoleのアカウントや、データの転送先となるGoogle BigQueryのアカウントも事前にご準備ください。

### BigQueryの接続設定
#### trocco®とBigQueryの接続をする
詳しい手順はこちらをご覧ください。

https://documents.trocco.io/docs/connection-configuration-bigquery

#### データセットを用意する
データの転送先情報を接続するために使用します。
BigQuery上であらかじめデータセットの作成をしておきましょう。

:::note info
データセットとは
テーブルとビューへのアクセスを整理して制御するために使用される最上位のコンテナです。テーブルまたはビューはデータセットに属していなければなりません。したがって、データを BigQuery に読み込む前に、1 つ以上のデータセットを作成する必要があります。
:::

※引用: https://cloud.google.com/bigquery/docs/datasets-intro


詳しい手順は公式ドキュメントを参考にしてください。

https://cloud.google.com/bigquery/docs/datasets?hl=ja#dataset-naming

## Google Search Console から Google BigQueryへデータ転送をする
### 1. 転送設定
#### 1-1. 転送設定の新規作成
まずはtrocco®でデータ転送を行います。
はじめに、転送設定の新規作成を行います。
![転送設定1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/c39a175a-e5f1-f162-f868-be4ed1eb32f3.png)

転送元・転送先の選択を行います。
![転送設定2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/7ac0aa54-e325-ce7c-bbd6-24e17fd74763.png)

概要設定をします。
![転送設定3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/9f0fe860-e665-ccbc-126b-4982cec87dce.png)

#### 1-2. 転送元（Google BigQuery）の設定
転送元であるGoogle Search Consoleとの連携設定をします。

`Google Search Console接続情報`、`サーチタイプ`、`サイトURL`、`データ取得期間`、`ディメンション`を入力しましょう。
カスタム変数は必要に応じて設定します。
![転送元設定1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/ac97d82a-c9e5-41aa-bd12-508bf937ad56.png)

#### 1-3. 転送先（Google BigQuery）の設定

次に、転送先であるGoogle BigQueryとの接続設定をします。
`Google BigQuery接続情報`、`データセット`、`テーブル`等を設定していきます。
テーブルはtrocco®上で作れるので、事前にBigQuery側で作成しなくても大丈夫です。

 ![転送先設定1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/a7dd2c25-5d96-e4c0-88c5-9fe7c7e5a2cd.png)

ここまで完了したら、画面右下にある`次のSTEPへ`というボタンを押します。
そうすると`自動データ設定の選択`というポップがでるので、`自動データ設定を実行`を選択し、`決定して次へ進む`を押します。
![データ設定1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/feb8c0e3-ebe3-6672-4293-0bd3c9cba9cc.png)


### 2. データのプレビュー・スケジュール設定・転送設定の実行
#### 2-1. プレビュー
設定して取得できたサンプルデータに問題がなければ`確認画面へ`を押します。
![転送設定プレビュー1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/7ac558f3-9c4b-5286-1ba1-54400dd3d44c.png)

`保存して適用`を押します。
![転送設定プレビュー2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/df1ceac4-75e6-1835-882a-c206a5ebaa6d.png)

#### 2-2. スケジュール・通知設定
必要に応じてスケジュール・通知設定を行います。

![スケジュール・通知設定1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/fc8425ab-ae31-6b27-86c4-de9e6a7a3f10.png)

#### 2-3. データ転送ジョブの実行
転送設定が全て完了したら、画面右上の`実行`ボタンを押します。
![データ転送設定実行1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/80f96ad5-e0e4-f5ee-f328-a14e849d9b7b.png)

次の画面に遷移するので、再び`実行`ボタンを押します。
![データ転送設定実行2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/be38d66f-a9e9-8ebd-0c7a-e1c6bec15506.png)

`実行完了`と表示が出れば、データ転送の成功です！
![データ転送設定実行3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/0ea8d640-0272-ae60-3bc8-914134000a44.png)

### 3. データマートの作成
データ転送が終わったら、BigQueryに転送したデータをもとに、データマートを作成します。

:::note info
データマートとは
データの検証や分析など特定の目的のために作成されたデータベースのことです。
通常データ分析には社内データを統合したデータウェアハウスを使用し、データマートはそのデータウェアハウス上の別テーブルにデータを切り出す形で作成されます。
:::
※引用: https://blog.trocco.io/glossary/data-mart

`データマート`から設定画面を開き、`新規データマート定義作成`のボタンを押します。
![データマート作成1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/abed8ce1-9d0a-7212-bae9-2321a8a7731a.png)

BigQueryを選択します。
![データマート2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/410979b4-956f-7c7d-8d8e-aa9301bc1a1e.png)

`データマート定義名`や`Google BigQuery接続情報`など作成に必要な情報を設定します。
![データマート作成3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/56fd96c2-423e-4a6e-6b87-f475f067e2b5.png)

`クエリ設定`にて、クエリを入力し、データセットやデータテーブルを指定します。
`出力先テーブル`はtrocco®上から作成することができるので、事前にBigQueryで作成する必要はありません。

![データマート作成4.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/3c752f2c-6ea0-8d77-04bd-c456943ae96d.jpeg)

なお、SQLのクエリはChatGPTに書いてもらいました。

以下はその時のプロンプトです。

```
BigQueryで使うためのSQLのクエリを書いてください。

## やりたいこと
以下の要素をdateをもとに月次・週次で集計する
・impressions				
・clicks				
・ctr			

なお、ctrはすでにBigQueryにデータがあるので、割り算をする計算はSQL上ではしなくても大丈夫です。
```

そして出てきたクエリがこちらです。

```
-- 月次集計
SELECT
  DATE_TRUNC(CAST(date AS DATE), MONTH) as month_start,
  SUM(impressions) as total_impressions,
  SUM(clicks) as total_clicks,
  AVG(ctr)*100 as avg_ctr
FROM your_table
GROUP BY month_start

-- 週次集計
SELECT
  DATE_TRUNC(CAST(date AS DATE), WEEK) as week_start,
  SUM(impressions) as total_impressions,
  SUM(clicks) as total_clicks,
  AVG(ctr)*100 as avg_ctr
FROM your_table
GROUP BY week_start
```
※`your_table`の部分に、ご自身が使うデータセット名をいれてください。
※ctrの値をパーセンテージ表記っぽくしたかったので、SQLクエリ内でctrの値を100倍しています。

SQLを自分で書くとなると検索しながら書くことになり時間がかかるので、ChatGPTに頼れるのはとても助かりました。

最後に`シンク`というボタンを押し、遷移した画面右下の`シンクジョブを実行`というボタンを押したらデータマート設定は完了です。
![データマート作成5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/5d221a13-116f-f8b7-636b-4045658ed8cc.png)

週次集計をするものを先に作ったので、月次バージョンも同じように作成します。


### 4. ワークフローの作成
Search ConsoleのデータをBigQueryに転送したあと、先ほど作成したデータマートが実行されるようにしたいので、ワークフローの設定をします。

![ワークフロー作成1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/34ea31ce-39d4-7724-f273-308b8afd7179.png)

ワークフロー名を設定し、ジョブ実行設定をしたら、保存ボタンを押します。

保存を押したらフロー編集の画面に移るので、左の`タスク追加`から`trocco転送ジョブ`と`troccoデータマートシンク`からそれぞれ利用したいものをつなげます。
右上の保存ボタンを押したら完了です。

![ワークフロー作成2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/7d96dee5-3a4a-b7d7-2c0f-4252e6dfafa3.png)


右上の実行ボタンを押したらワークフローが実行されます。
![ワークフロー作成3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/a52cd2be-3b2a-aa57-3cbe-5843dc6ce865.png)


## Google BigQueryに転送したデータをGoogle Looker Studioで可視化する
Google Looler Studioを開きます。

`データの追加`を押し、`BigQuery`を選択します。
![可視化1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/d1432d84-c221-0deb-99c4-386647bf9a36.png)

プロジェクト、データセット、表をそれぞれ選択し、レポートに追加します。

今回は表に数値をまとめていきます。
![可視化2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/eeb5d382-d144-c1d5-0d97-592753116662.png)

月次・週次それぞれ用意したら、完成！

![Search Console 月次・週次レポート_完成イメージ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1480034/05eec7df-8916-b47b-a323-7d2e81e3534f.png)

## まとめ 
この記事を参考にしながら手を動かしてくださった皆さん、うまく設定はできましたか？

今回はGoogle Search Consoleにあるデータの中でもウォッチされることが一番多いであろう、
imp、click、ctrをGoogle BigQueryに転送し、Google Looker Studioで可視化してみました。

「データを利活用するための環境整備はデータエンジニアに依頼するもの」というイメージを持つマーケティング担当者もいらっしゃいますが、[trocco®](https://trocco.io/lp/index.html)があれば、簡単にデータを扱うことができます。

SQLのスキルが必要になるためマーケターにとってのハードルは0ではありませんが、
この記事で私が実践したようにAIサービスを駆使するなど工夫をすれば、都度エンジニアに頼らなくともデータの利活用はできます。

他にも可視化したいデータがある、日々のデータ集計を楽にしたい、と考えている方は、ぜひこの機会にtrocco®を利用してみてください。

▼フリープランのアカウント申し込みはこちら
https://trocco.io/lp/inquiry_free.html

## おまけ
データの可視化だけじゃなく、コンテンツ制作も効率化したいと思っているマーケターの方は、ぜひこちらもご覧ください。

https://qiita.com/__my/items/40b007499adc313ca663