はじめに

この記事では、Google Search Console（GSC）のデータをGoogle BigQueryを使用して集計し、その結果をLooker Studioで可視化する方法を解説します。最終的には毎日のサイトのインプレッション数とクリック数を折れ線グラフに表示しました。
本記事の内容はBigQueryに焦点を当てています。GCPやLooker Studioの設定に関する具体的な手順については今後別の記事で詳しく説明する予定ですので、気長にお待ちいただくか、他の記事を参考にしてください。

結果の可視化

最終結果としての可視化例を以下に示します。x軸が日付でy軸がそれぞれインプレッション数とクリック数です。

データセットの準備

元のデータセット

使用するデータセットは、Google Search Consoleから取得した検索クエリ、インプレッション数、クリック数などの情報を含んでいます。

メモ:
異なるリージョンにデータセットが存在する場合、直接クエリを実行できないことがあります。例えば、以下のようなエラーが発生しました。
Not found: Dataset your_project:your_dataset was not found in location your_region
この問題を解決するために、データセットのリージョンを一致させる必要があります。今回は、プロジェクトとデータセットのリージョンを同じにする基本的な方法で解決しました。

新しいデータセットの作成

まず、BigQueryコンソールで新しいデータセットを作成します。このデータセットのリージョンは、元のデータセットと一致させます。

新しいデータセットの作成手順

BigQueryコンソールにアクセス:
GCPコンソールにログインし、BigQueryサービスを開きます。
新しいデータセットの作成:

プロジェクト内に新しいデータセットを作成します。このデータセットのリージョンはUSに設定します。

サマリーテーブルの作成

新しいデータセット内でサマリーテーブルを作成し、GSCデータを集計します。

CREATE TABLE `your_project.your_dataset0.site_impressions_clicks_summary` AS
SELECT
  data_date,
  site_url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM
  `your_project.your_dataset1.searchdata_url_impression`
GROUP BY
  data_date,
  site_url
ORDER BY
  data_date,
  site_url;

Looker Studioでの可視化

データソースの追加

Looker Studioにログインし、新しいレポートを作成します。
「データソースを追加」をクリックし、「Google BigQuery」を選択します。
プロジェクト内のデータセットを選択し、テーブルsite_impressions_clicks_summaryを選択します。

折れ線グラフの追加

レポートキャンバスに戻り、「チャートを追加」ボタンをクリックし、「折れ線グラフ」を選択します。
次元をdata_date、指標をtotal_impressionsおよびtotal_clicksに設定します。上でもご紹介しましたが下記の様なグラフを生成しました。

結論

GSCデータをLooker Studioで可視化する際に直面したリージョンの問題は、データセットのリージョンを一致させることで解決しました。Google Cloud Storageを使用してデータを移動する方法もありますが、私の経験ではデータセットのリージョンを揃えることが最もシンプルな解決策でした。この情報が皆さんの参考になれば幸いです。

関連記事

tan0ry0(2024.6), BigQueryを用いた野球チーム(MLB)の勝率計算とLooker Studioでの可視化