第3回までの作業で、Google Search Consoleのデータを毎朝5時に自動取得し、BigQueryへ保存できるようになりました。
ただし、BigQueryに保存されているデータは、日付、ページ、検索キーワード、端末、国の組み合わせごとに分かれた細かな明細です。この状態のままAIへ渡すと、同じ記事が何百行にも分かれ、全体の変化をつかみにくくなります。
そこで第4回では、BigQueryの明細データをサイト別・ページ別にまとめ、直近28日分をGoogleスプレッドシートへ自動出力します。そのうえでGemini Sparkにスプレッドシートを参照させ、検索流入が落ちているサイトや、表示回数が伸びている記事などを分析できる状態にします。
今回は、毎朝集めているGoogle Search Consoleの数字を、そのままAIへ渡すのではなく、AIが比較しやすい表へ整理します。サイト全体の変化を見る表と、記事ごとの変化を見る表を作り、Googleスプレッドシートへ自動で書き出します。最後にGemini Sparkへ「この表を毎朝確認して、変化が大きい記事を教えて」と仕事を任せます。
- この記事で完成する状態
- シリーズ構成
- 前回までに必要な設定
- ページ別の日次集計ビューを作成する
- ページ別集計ビューの内容を確認する
- サイト別の日次集計ビューを作成する
- サイト別の直近7日比較ビューを作成する
- Spark用のGoogleスプレッドシートを作成する
- Google Sheets APIを有効化する
- スプレッドシートIDを設定ファイルへ追加する
- Googleスプレッドシート書き込み処理を作る
- Spark向けデータ抽出処理を作る
- main.py にスプレッドシート出力処理を追加する
- スプレッドシート出力機能を再デプロイする
- スプレッドシート出力までテスト実行する
- スプレッドシートの出力結果を確認する
- Gemini Sparkへ分析を任せる
- 毎朝の定期分析へ変更する
- テストをしてみよう
- まとめ
- 今後の改善点
この記事で完成する状態
シリーズ構成
- 目次：GSC自動取得からGemini Spark分析までの全体像
- 第1回：Google CloudとBigQueryの初期設定
- 第2回：PythonでGSC APIデータを取得・保存
- 第3回：Cloud Run JobsとCloud Schedulerで毎朝自動実行
- 第4回：BigQueryのデータをGemini Sparkで分析
前回までに必要な設定
この記事は、第1回から第3回までの作業が完了していることを前提にしています。
ページ別の日次集計ビューを作成する
1．BigQuery Studioを開く
Google Cloud Console左上の三本線メニューからBigQuery→ Studioと進んでください。
2．新しいクエリを開く
BigQuery Studio画面上部にある、
ボタンを押して新しいSQLクエリを作成します。すると次のように「無題のクエリ」が作成され、クエリエディタ画面になります。
3．ビュー作成用SQLを貼り付ける
次のSQLをそのまま貼り付けてください。
CREATE OR REPLACE VIEW
`lennonsoft-blog-analytics.blog_analytics.gsc_page_daily`
AS
SELECT
site_id,
data_date,
page,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
SAFE_DIVIDE(
SUM(clicks),
SUM(impressions)
) AS ctr,
SAFE_DIVIDE(
SUM(position * impressions),
SUM(impressions)
) AS position
FROM
`lennonsoft-blog-analytics.blog_analytics.gsc_query_page_daily`
GROUP BY
site_id,
data_date,
page;このSQLでは、同じ日・同じページに属する検索クエリや端末などをまとめています。
CTRは各行の平均ではなく、次の考え方で再計算しています。
合計クリック数 ÷ 合計表示回数掲載順位も単純平均ではなく、表示回数を重みとして計算します。表示回数が1回の検索と100回の検索を同じ重みで平均すると、実際の検索結果での見え方からずれやすいためです。
実行後、blog_analyticsデータセットにgsc_page_dailyのビューが表示されることを確認してください。
ページ別集計ビューの内容を確認する
今のBigQuery画面をそのまま使います。
1．新しいSQLクエリを開く
BigQuery Studio画面上部にある、ボタンを押して新しいSQLクエリを作成します。すると次のように「無題のクエリ」が作成され、クエリエディタ画面になります。
2．確認用SQLを貼り付ける
次のSQLを貼り付けて実行してください。
SELECT
site_id,
data_date,
page,
clicks,
impressions,
ctr,
position
FROM
`lennonsoft-blog-analytics.blog_analytics.gsc_page_daily`
ORDER BY
data_date DESC,
impressions DESC
LIMIT 20;実行後確認するポイントは次のとおりです。
サイト別の日次集計ビューを作成する
次は、各サイト全体の推移を見るためのビューを作成します。
1．次のSQLを貼り付ける
次のSQLを貼り付けて実行してください。
CREATE OR REPLACE VIEW
`lennonsoft-blog-analytics.blog_analytics.gsc_site_daily`
AS
SELECT
site_id,
data_date,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions,
SAFE_DIVIDE(
SUM(clicks),
SUM(impressions)
) AS ctr,
SAFE_DIVIDE(
SUM(position * impressions),
SUM(impressions)
) AS position
FROM
`lennonsoft-blog-analytics.blog_analytics.gsc_query_page_daily`
GROUP BY
site_id,
data_date;左側のエクスプローラを更新し、下の図のように3つが表示されれば正常です。
サイト別の直近7日比較ビューを作成する
Gemini Sparkに毎朝分析を任せるなら、単に現在のクリック数を見せるだけでは不十分です。
直近7日が、その前の7日より増えたのか減ったのかを比較できる状態にしておくと、Sparkが変化の大きいサイトをすぐに見つけられます。
1．次のSQLを貼り付ける
引き続きSQLクエリエディターを使用します。次のSQLを貼り付けて実行して下さい。
CREATE OR REPLACE VIEW
`lennonsoft-blog-analytics.blog_analytics.gsc_site_7d_comparison`
AS
WITH latest_date AS (
SELECT
MAX(data_date) AS max_date
FROM
`lennonsoft-blog-analytics.blog_analytics.gsc_site_daily`
),
period_summary AS (
SELECT
site_id,
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 6 DAY) AND max_date,
clicks,
0
)
) AS current_clicks,
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 13 DAY)
AND DATE_SUB(max_date, INTERVAL 7 DAY),
clicks,
0
)
) AS previous_clicks,
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 6 DAY) AND max_date,
impressions,
0
)
) AS current_impressions,
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 13 DAY)
AND DATE_SUB(max_date, INTERVAL 7 DAY),
impressions,
0
)
) AS previous_impressions,
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 6 DAY) AND max_date,
clicks,
0
)
)
/
NULLIF(
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 6 DAY) AND max_date,
impressions,
0
)
),
0
) AS current_ctr,
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 13 DAY)
AND DATE_SUB(max_date, INTERVAL 7 DAY),
clicks,
0
)
)
/
NULLIF(
SUM(
IF(
data_date BETWEEN DATE_SUB(max_date, INTERVAL 13 DAY)
AND DATE_SUB(max_date, INTERVAL 7 DAY),
impressions,
0
)
),
0
) AS previous_ctr,
max_date
FROM
`lennonsoft-blog-analytics.blog_analytics.gsc_site_daily`
CROSS JOIN
latest_date
GROUP BY
site_id,
max_date
)
SELECT
site_id,
DATE_SUB(max_date, INTERVAL 6 DAY) AS current_start_date,
max_date AS current_end_date,
DATE_SUB(max_date, INTERVAL 13 DAY) AS previous_start_date,
DATE_SUB(max_date, INTERVAL 7 DAY) AS previous_end_date,
current_clicks,
previous_clicks,
SAFE_DIVIDE(
current_clicks - previous_clicks,
previous_clicks
) AS clicks_change_rate,
current_impressions,
previous_impressions,
SAFE_DIVIDE(
current_impressions - previous_impressions,
previous_impressions
) AS impressions_change_rate,
current_ctr,
previous_ctr,
current_ctr - previous_ctr AS ctr_change
FROM
period_summary;このビューは、パソコンの日付ではなく、BigQuery内に存在する最新日を基準にしています。Google Search Consoleのデータは当日分がすぐに確定するわけではないため、保存済みデータの最新日を使うほうが安定します。
2. 確認する
作成後は、次のSQLで確認できます。
SELECT
*
FROM
`lennonsoft-blog-analytics.blog_analytics.gsc_site_7d_comparison`
ORDER BY
site_id;現時点ではBigQueryに7日分しか入っていないため、「その前の7日」の数値は0またはNULLになる可能性があります。
これは異常ではありません。毎朝データが蓄積され、14日分以上そろうと正しい比較が表示されるようになります。
Spark用のGoogleスプレッドシートを作成する
BigQueryは大量データの保存や集計には向いていますが、Gemini Sparkから日常的に参照するデータとしてはGoogleスプレッドシートのほうが扱いやすくなります。
今回は、BigQueryに全データを残したまま、Sparkの分析に必要な直近28日分だけをスプレッドシートへ出力します。
1．Googleドライブに保存フォルダを作る
Googleドライブを開き、次のフォルダを作成します。
マイドライブ\Blog Analytics PlatformGSC専用のフォルダ名にしない理由は、今後GA4やAdSenseのデータも同じ仕組みに追加できるようにするためです。
2．スプレッドシートを作成する
Blog Analytics Platformフォルダの中に、空白のGoogleスプレッドシートを作成してください。
ファイル名は次のとおりです。
Blog Analytics Spark Data最初のシート名はREADMEへ変更します。後からプログラムが他のシートを自動作成します。
3. スプレッドシートIDを確認する
Cloud Runからこのスプレッドシートへ書き込むため、ファイルIDを確認します。
作成した「Blog Analytics Spark Data」を開いた状態で、ブラウザのアドレス欄を見てください。
URLは次のような形式です。
https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/editこのうち、/d/と/editの間がスプレッドシートIDです。
このスプレッドシートIDは後でconfig/sites.yamlへ登録します。
4. スプレッドシートを実行用サービスアカウントと共有する
スプレッドシート右上の「共有」を開き、次のサービスアカウントを追加します。
blog-analytics-runner@lennonsoft-blog-analytics.iam.gserviceaccount.com権限は「編集者」にします。
一般公開や「リンクを知っている全員」の共有は不要です。スプレッドシートへ書き込む実行用アカウントだけを追加してください。
Google Sheets APIを有効化する
Cloud Runからスプレッドシートへ書き込むには、GCPプロジェクトでGoogle Sheets APIを有効にします。今回はAPIの有効化だけです。
1．左メニューからAPIライブラリを開く
Google Cloud Console左上の三本線メニューからAPIとサービス→ ライブラリと進めてください。
2．Google Sheets APIを検索する
画面上記の検索欄にGoogle Sheets APIと入力して検索します。
検索結果から、名称が正確に次のものを選択してください。
「Google Drive API」や「Google Docs API」は今回は選びません。
スプレッドシートIDを設定ファイルへ追加する
次に、Cloud Runのプログラムが書き込み先を判断できるよう、config/sites.yamlへスプレッドシートIDを登録します。今回は設定ファイルの変更だけです。
1．Cloud Shellを開く
Google Cloud Console右上にある、次のアイコンをクリックしてください。
2．作業フォルダへ移動する
次を実行してください。
cd ~/blog-analytics-platform現在地を確認します。
pwd次のように表示されれば問題ありません。
/home/satoshi_i2009/blog-analytics-platform3．スプレッドシートIDを追加する
次のコマンドをそのまま実行してください。
python3 - <<'EOF'
from pathlib import Path
path = Path("config/sites.yaml")
text = path.read_text(encoding="utf-8")
old = """ gsc_refresh_days: 7
"""
new = """ gsc_refresh_days: 7
spark_spreadsheet_id: "ここへスプレッドシートIDを入力"
"""
if "spark_spreadsheet_id:" in text:
print("spark_spreadsheet_id はすでに登録されています。")
elif old not in text:
raise SystemExit(
"追加位置が見つかりません。ファイルは変更していません。"
)
else:
path.write_text(
text.replace(old, new),
encoding="utf-8",
)
print("スプレッドシートIDを追加しました。")
EOF4．設定内容を確認する
次を実行します。
tail -n 10 config/sites.yaml最後の部分が次のようになれば正常です。
settings:
project_id: lennonsoft-blog-analytics
bigquery_dataset: blog_analytics
timezone: Asia/Tokyo
gsc_refresh_days: 7
spark_spreadsheet_id: "スプレッドシートID"5．設定ファイルを読み込めるか確認する
次を実行してください。
python3 - <<'EOF'
from common.settings import load_settings
config = load_settings()
settings = config["settings"]
print(
"spark_spreadsheet_id:",
settings["spark_spreadsheet_id"],
)
EOF次のように表示されれば完了です。
spark_spreadsheet_id: "スプレッドシートID"スプレッドシートIDは設定値なので、Pythonコードへ直接埋め込みません。将来ファイルを変更する場合も、sites.yamlだけを直せる構成にします。
Googleスプレッドシート書き込み処理を作る
指定したシートを自動作成し、内容を上書きできる共通処理を作ります。BigQueryからのデータ抽出は次のタスクで追加します。
Cloud Shellで、次をそのまま実行してください。
cat > loaders/google_sheets.py <<'EOF'
from typing import Any
import google.auth
from googleapiclient.discovery import build
SHEETS_SCOPES = [
"https://www.googleapis.com/auth/spreadsheets",
]
def create_sheets_service() -> Any:
"""実行環境のサービスアカウントでSheets APIへ接続する。"""
credentials, _ = google.auth.default(
scopes=SHEETS_SCOPES,
)
return build(
"sheets",
"v4",
credentials=credentials,
cache_discovery=False,
)
def ensure_sheet_exists(
service: Any,
spreadsheet_id: str,
sheet_name: str,
) -> None:
"""指定したシートが存在しなければ新規作成する。"""
spreadsheet = (
service.spreadsheets()
.get(
spreadsheetId=spreadsheet_id,
fields="sheets.properties.title",
)
.execute()
)
existing_names = {
sheet["properties"]["title"]
for sheet in spreadsheet.get("sheets", [])
}
if sheet_name in existing_names:
return
(
service.spreadsheets()
.batchUpdate(
spreadsheetId=spreadsheet_id,
body={
"requests": [
{
"addSheet": {
"properties": {
"title": sheet_name,
}
}
}
]
},
)
.execute()
)
def replace_sheet_values(
spreadsheet_id: str,
sheet_name: str,
values: list[list[Any]],
) -> None:
"""
指定シートの既存データを削除し、
valuesの内容で上書きする。
"""
service = create_sheets_service()
ensure_sheet_exists(
service=service,
spreadsheet_id=spreadsheet_id,
sheet_name=sheet_name,
)
escaped_sheet_name = sheet_name.replace("'", "''")
sheet_range = f"'{escaped_sheet_name}'"
(
service.spreadsheets()
.values()
.clear(
spreadsheetId=spreadsheet_id,
range=sheet_range,
body={},
)
.execute()
)
if not values:
return
(
service.spreadsheets()
.values()
.update(
spreadsheetId=spreadsheet_id,
range=f"{sheet_range}!A1",
valueInputOption="RAW",
body={
"majorDimension": "ROWS",
"values": values,
},
)
.execute()
)
EOF次は、BigQueryからSpark用データを取り出す処理を作ります。
Spark向けデータ抽出処理を作る
今回は新しく exporters/spark_data.py を作成します。
スプレッドシートへ出す範囲は、毎朝更新する直近28日分にします。
gsc_site_daily：サイト別の日次データ
gsc_page_daily：ページ別の日次データ全期間を出力し続けるとスプレッドシートが大きくなるため、Sparkの分析に必要な直近28日だけを出します。
1．exportersフォルダを作る
下記を貼り付けて実行し、フォルダを作成します。
mkdir -p exporters
touch exporters/__init__.py2．抽出処理を作成する
次をそのまま貼り付けて実行してください。
cat > exporters/spark_data.py <<'EOF'
from datetime import date, datetime
from typing import Any
from google.cloud import bigquery
BQ_LOCATION = "asia-northeast1"
def normalize_value(value: Any) -> Any:
"""Sheets APIへ渡せる形式に変換する。"""
if isinstance(value, (date, datetime)):
return value.isoformat()
return value
def query_to_values(
client: bigquery.Client,
sql: str,
) -> list[list[Any]]:
"""BigQueryの検索結果を見出し付きの二次元配列へ変換する。"""
query_job = client.query(
sql,
location=BQ_LOCATION,
)
rows = list(query_job.result())
headers = [
field.name
for field in query_job.result().schema
]
values: list[list[Any]] = [headers]
for row in rows:
values.append(
[
normalize_value(row[field])
for field in headers
]
)
return values
def get_gsc_site_daily_values(
project_id: str,
dataset_id: str,
export_days: int = 28,
) -> list[list[Any]]:
"""サイト別の日次データを取得する。"""
client = bigquery.Client(project=project_id)
sql = f"""
WITH latest_date AS (
SELECT
MAX(data_date) AS max_date
FROM
`{project_id}.{dataset_id}.gsc_site_daily`
)
SELECT
site_id,
data_date,
clicks,
impressions,
ctr,
position
FROM
`{project_id}.{dataset_id}.gsc_site_daily`
CROSS JOIN
latest_date
WHERE
data_date BETWEEN
DATE_SUB(max_date, INTERVAL {export_days - 1} DAY)
AND max_date
ORDER BY
data_date DESC,
site_id
"""
return query_to_values(
client=client,
sql=sql,
)
def get_gsc_page_daily_values(
project_id: str,
dataset_id: str,
export_days: int = 28,
) -> list[list[Any]]:
"""ページ別の日次データを取得する。"""
client = bigquery.Client(project=project_id)
sql = f"""
WITH latest_date AS (
SELECT
MAX(data_date) AS max_date
FROM
`{project_id}.{dataset_id}.gsc_page_daily`
)
SELECT
site_id,
data_date,
page,
clicks,
impressions,
ctr,
position
FROM
`{project_id}.{dataset_id}.gsc_page_daily`
CROSS JOIN
latest_date
WHERE
data_date BETWEEN
DATE_SUB(max_date, INTERVAL {export_days - 1} DAY)
AND max_date
ORDER BY
data_date DESC,
impressions DESC,
site_id,
page
"""
return query_to_values(
client=client,
sql=sql,
)
EOFBigQueryのPythonクライアントでクエリを実行し、その結果を行単位で取得する処理です。取得結果は、Google Sheets APIのvalues.updateへ渡せる二次元配列に変換します。
main.py にスプレッドシート出力処理を追加する
今回は、GSC取得とBigQuery保存が完了した後に、次の2シートを更新する処理をつなぎます。
gsc_site_daily
gsc_page_dailyCloud Shellで次をそのまま実行してください。
cat > main.py <<'EOF'
import logging
import sys
from collectors.gsc import fetch_gsc_rows
from common.date_utils import get_gsc_date_range
from common.settings import load_settings
from exporters.spark_data import (
get_gsc_page_daily_values,
get_gsc_site_daily_values,
)
from loaders.bigquery import replace_gsc_rows
from loaders.google_sheets import replace_sheet_values
logging.basicConfig(
level=logging.INFO,
format="%(asctime)s %(levelname)s %(message)s",
)
def export_spark_data(
project_id: str,
dataset_id: str,
spreadsheet_id: str,
) -> None:
"""BigQueryの集計データをSpark参照用スプレッドシートへ出力する。"""
logging.info("Spark向けスプレッドシート出力を開始します。")
site_daily_values = get_gsc_site_daily_values(
project_id=project_id,
dataset_id=dataset_id,
export_days=28,
)
replace_sheet_values(
spreadsheet_id=spreadsheet_id,
sheet_name="gsc_site_daily",
values=site_daily_values,
)
logging.info(
"gsc_site_daily更新完了: %s行",
max(len(site_daily_values) - 1, 0),
)
page_daily_values = get_gsc_page_daily_values(
project_id=project_id,
dataset_id=dataset_id,
export_days=28,
)
replace_sheet_values(
spreadsheet_id=spreadsheet_id,
sheet_name="gsc_page_daily",
values=page_daily_values,
)
logging.info(
"gsc_page_daily更新完了: %s行",
max(len(page_daily_values) - 1, 0),
)
logging.info("Spark向けスプレッドシート出力が完了しました。")
def main() -> int:
config = load_settings()
settings = config["settings"]
sites = config["sites"]
project_id = settings["project_id"]
dataset_id = settings["bigquery_dataset"]
timezone = settings.get("timezone", "Asia/Tokyo")
refresh_days = int(settings.get("gsc_refresh_days", 7))
spreadsheet_id = settings["spark_spreadsheet_id"]
start_date, end_date = get_gsc_date_range(
refresh_days=refresh_days,
data_delay_days=2,
timezone=timezone,
)
logging.info(
"GSC取得開始: %s ～ %s、対象サイト数=%s",
start_date,
end_date,
len(sites),
)
failed_sites: list[str] = []
for site in sites:
site_id = site["site_id"]
site_url = site["gsc_property"]
try:
logging.info(
"サイト処理開始: site_id=%s, property=%s",
site_id,
site_url,
)
rows = fetch_gsc_rows(
site_id=site_id,
site_url=site_url,
start_date=start_date,
end_date=end_date,
)
loaded_count = replace_gsc_rows(
project_id=project_id,
dataset_id=dataset_id,
site_id=site_id,
start_date=start_date,
end_date=end_date,
rows=rows,
)
logging.info(
"サイト処理完了: site_id=%s, 取得件数=%s, 保存件数=%s",
site_id,
len(rows),
loaded_count,
)
except Exception:
logging.exception(
"サイト処理失敗: site_id=%s, property=%s",
site_id,
site_url,
)
failed_sites.append(site_id)
if failed_sites:
logging.error(
"一部サイトで失敗しました: %s",
", ".join(failed_sites),
)
return 1
try:
export_spark_data(
project_id=project_id,
dataset_id=dataset_id,
spreadsheet_id=spreadsheet_id,
)
except Exception:
logging.exception(
"Spark向けスプレッドシート出力に失敗しました。"
)
return 1
logging.info("全処理が正常に完了しました。")
return 0
if __name__ == "__main__":
sys.exit(main())
EOFスプレッドシート出力機能を再デプロイする
ローカルのコード変更をCloud Run Jobへ反映します。Cloud Shellで次を実行してください。
gcloud run jobs deploy blog-analytics-gsc \
--source . \
--region asia-northeast1 \
--service-account blog-analytics-runner@lennonsoft-blog-analytics.iam.gserviceaccount.com \
--tasks 1 \
--max-retries 1 \
--task-timeout 30m \
--memory 1Gi \
--cpu 1 \
--project lennonsoft-blog-analytics同じジョブ名なので、新しいジョブは増えず、既存のblog-analytics-gscが更新されます。最後に次が表示されれば成功です。デプロイは少しだけ時間がかかります。
Job [blog-analytics-gsc] has successfully been deployed.スプレッドシート出力までテスト実行する
Cloud Shellで次を実行してください。
gcloud run jobs execute blog-analytics-gsc \
--region asia-northeast1 \
--project lennonsoft-blog-analytics \
--wait今回の実行では、次の処理が行われます。
GSCから4サイト分を取得
↓
BigQueryを更新
↓
BigQueryの集計ビューを読み込む
↓
Googleスプレッドシートへ出力成功した場合は、最後に次のように表示されます。
Execution [実行名] has successfully completed.スプレッドシートの出力結果を確認する
Googleドライブから「Blog Analytics Spark Data」を開きます。次の2シートが追加されていることを確認してください。
それぞれ開いて、次を確認してください。
各シートの見出しかあっているかどうか
gsc_site_dailyシート
site_id
data_date
clicks
impressions
ctr
positiongsc_page_dailyシート
site_id
data_date
page
clicks
impressions
ctr
positionはい。GSCの自動取得基盤としては、Google Cloud側はいったん完成です。
完成している範囲は次のとおりです。
毎朝5時
Cloud Scheduler
↓
Cloud Run Job
↓
Search Console APIから4サイト分を取得
↓
BigQueryへ保存・更新
↓
Googleスプレッドシートへ出力加えて、再実行時の重複防止、サービスアカウント分離、BigQueryの明細・ページ別・サイト別ビューまで整っています。
残っているのは主にSpark側です。
Google Cloud側で今後追加する可能性があるのは、GA4やAdSense対応、実行ログの通知、スプレッドシートの見た目整形などです。ただし、GSC単体の基盤としては一度区切って問題ありません。
Gemini Sparkへ分析を任せる
ここまでで、Gemini Sparkへ渡すデータの準備は完了です。
Sparkは、継続する作業をタスクとして管理し、決めた時刻に実行できるAIエージェントです。Google Workspaceとの接続が有効であれば、Googleドライブ内のファイルを参照する作業へ利用できます。
画面名や配置はアップデートで変わる可能性がありますが、基本的な流れは次のとおりです。
Google Workspaceとの接続を確認する
Geminiを開いて Spark → アプリ連携 と進んでアプリ連携の確認をしましょう。
Sparkで新しいタスクを作成する
Gemini SparkのスケジュールをクリックしてGeminiで作成を選んでください。
最初から定期実行にせず、まずは一度だけ手動で分析させます。正しくシートを読めることと、期待する結果が返ることを確認してからスケジュールを追加するほうが安全です。
最初の分析プロンプト
Geminiで作成を選ぶとプロンプトを入力できるので早速下記のプロンプトを貼り付けて実行してください。そうするとAIエージェントSparkがいろいろと考えながら作業を進めてくれます。
Googleドライブにある「Blog Analytics Spark Data」を確認してください。
利用するシートは次の2つです。
・gsc_site_daily：サイト別の日次データ
・gsc_page_daily：ページ別の日次データ
データ内の最新日を基準に、直近7日とその前の7日を比較してください。
次の順番で日本語のレポートを作成してください。
1. 4サイト全体の状況
2. クリック数が大きく増えたサイトと減ったサイト
3. 表示回数が増えているページ
4. 表示回数は多いがCTRが低いページ
5. 掲載順位が悪化している可能性があるページ
6. 優先して確認すべきページを最大5件
7. 各ページについて確認すべき理由と改善の方向性
数値が不足している場合は推測で補わず、「比較に必要なデータが不足している」と明記してください。
ページの内容を確認していない段階では、タイトルや本文の具体的な修正案を断定しないでください。このプロンプトで重要なのは、AIへ単に「分析して」と頼まないことです。
比較期間、見る指標、出力順、推測を避ける条件まで指定すると、毎回のレポートが安定しやすくなります。
分析結果の出力
2～3分待つと分析結果が大量に出力されます。
やはりAIエージェントへの指示は簡単でいいですね。簡単に命令するだけでしっかりと作業してくれます。
毎朝の定期分析へ変更する
一度だけの分析が成功したら、Sparkのタスクへスケジュールを追加します。
Cloud Schedulerは毎朝5時にデータ取得を開始するため、午前5時ちょうどにSparkも実行すると、Googleスプレッドシートの更新前に古いデータを読む可能性があります。
処理時間に余裕を持たせるため、1時間程度ずらす設計が分かりやすいです。定期実行用の指示例は次のとおりです。
先ほど指示したプロンプトを毎朝6時に実行をするスケジュールにしてください。
毎朝6時に実行した後に、そのレポートを下記のアドレス宛にメール送信してください。
送付先メールアドレス：satoshi@lennonsoft.com
メールタイトル：【毎朝実行】GSCレポート
メール本文：レポート内容そうすると下記のように設定完了の返事が返ってきました。
ご指示いただいたプロンプトの実行およびメール送信のスケジュール設定が完了いたしました。
スケジュールの設定内容
タイトル: GSCレポートの生成と送信
実行頻度: 毎朝 6:00頃（JST）
実行内容:
Googleドライブ内の Blog Analytics Spark Data（gsc_site_daily および gsc_page_daily）のデータを取得。
直近7日とその前の7日を比較し、指定の7項目で分析レポートを作成
（データ不足時は「比較に必要なデータが不足している」と明記）。
作成したレポートを以下の通りメール送信。
宛先: satoshi@lennonsoft.com
件名: 【毎朝実行】GSCレポート
本文: 分析レポートの内容
テストをしてみよう
設定ができたので本当にメールをしっかり送ってくれるかテストをしてみましょう。
即時テスト実行してこう入力するとすぐにテストを開始してくれます。そしてメール送信まで来たところで確認画面が出てきました。送信ボタンを押してください。
しばらく待つとメールが届きました。これで毎日の定時実行システムは完成です。
まとめ
第1回から第3回では、Google Search Consoleのデータを毎朝自動取得してBigQueryへ保存する仕組みを作りました。
第4回では、その明細データをサイト別とページ別に整理し、直近28日分をGoogleスプレッドシートへ自動出力しました。さらに、Gemini Sparkへスプレッドシートを参照させ、直近7日と前の7日を比較するタスクを設定しました。
完成した流れは次のとおりです。
毎朝5時
↓
Cloud Scheduler
↓
Cloud Run Jobs
↓
Google Search Console APIから4サイト分を取得
↓
BigQueryへ保存・更新
↓
サイト別・ページ別に集計
↓
Googleスプレッドシートへ直近28日分を出力
↓
毎朝6時
↓
Gemini Sparkが変化を分析これにより、人が毎朝Google Search Consoleを開いて複数サイトを巡回しなくても、確認すべきサイトやページをSparkに絞り込ませることができます。
ただし、現在のデータはGoogle Search Consoleだけです。今後GA4とAdSenseを追加すれば、「検索された」「訪問された」「収益が発生した」という流れを一つの分析基盤で確認できるようになります。
今後の改善点
最初からすべてを組み込むより、まずGSCだけで毎朝の分析が安定するか確認してから、GA4とAdSenseへ広げるほうが問題を切り分けやすくなります。
コメント