Google Search Consoleデータの可視化とクラスタリング分析

Last updated at 2024-06-14Posted at 2024-06-12

1. はじめに

この記事では、Google Search Console（GSC）のデータをGoogle BigQueryを使用して集計し、その結果をGoogle Colabで可視化する方法を解説します。前回、毎日のサイトのインプレッション数とクリック数を折れ線グラフに表示しました。今回は、毎日のサイトのインプレッション数とクリック数を詳細に見ていきます。具体的にはクラスタリングをして、それによる可視化を行います。また、適切にクラスタリングするためのエンベディングについても説明します。本記事では結果や概要を記載しますが、具体的な手順については今後別の記事で詳しく説明する予定ですので、気長にお待ちいただくか、他の記事を参考にしてください。

この記事は一意見ですので、間違いや修正、ご意見等ありましたらご教示いただけると幸いです。

2. 結果

以下は、各クラスタの毎日のインプレッション数とクリック数を表示した折れ線グラフの例です。

2-1. クラスタリング結果の分析

2-2. 各クラスタの毎日のインプレッション数とクリック数

全体的なトレンド

インプレッション数: すべてのクラスタで時間とともに減少しています。これは、検索ボリュームの減少やキーワードトレンドの変化を示しています。
クリック数: クリック数も全体的に減少傾向にありますが、特にクリック数が非常に少ないことが顕著です。これは、インプレッション数に対してクリック率が低いことを示しています。

クラスタごとの違い

Cluster 2: 圧倒的に多くのインプレッション数を持っていますが、クリック数が非常に少ないです。検索されやすいがクリックされにくいことを示しています。
Cluster 0とCluster 4: 比較的多くのインプレッション数を持っていますが、クリック数は少ないです。CTRを改善するための対策が必要です。
Cluster 1: インプレッション数が多いが、他のクラスタに比べて少しだけクリック数が多いです。CTRの改善が効果を発揮している可能性があります。

3. データの抽出

データの抽出はGoogle Colabで以下の手順を使用しました。

import pandas as pd
from google.cloud import bigquery
from google.colab import auth

# Google Cloudの認証
auth.authenticate_user()

# BigQueryクライアントの作成
client = bigquery.Client(project='your_project')

# クエリデータの読み込み
query = """
SELECT *
FROM `your_project.your_dataset.searchdata_url_query`
"""
query_data = client.query(query).to_dataframe()

# DataFrameの表示
print(query_data.head())

この段階でのデータは、以下のようなDataFrameです（サンプル）：

data_date	site_url	url	query
2023-12-18	https://example.com/	https://example.com/path1/	keyword1
2023-12-18	https://example.com/	https://example.com/path2/	keyword2
2023-12-18	https://example.com/	https://example.com/path3/	keyword3

4. エンベディングとクラスタリング

次に、取得したデータを使ってクエリをエンベディングし、クラスタリングを行いました。

from sentence_transformers import SentenceTransformer
from sklearn.cluster import KMeans

# Sentence-BERTモデルの読み込み
model = SentenceTransformer('paraphrase-MiniLM-L6-v2')

# クエリのエンベディング
query_embeddings = model.encode(query_data['query'].tolist())

# クエリのエンベディング結果のサンプル表示
print(query_embeddings[:3])

[[ 0.123, -0.456, 0.789, ...],
[ 0.234, -0.567, 0.890, ...],
[ 0.345, -0.678, 0.901, ...]]

クラスタリングを実行します。

num_clusters = 5
clustering_model = KMeans(n_clusters=num_clusters)
clustering_model.fit(query_embeddings)
query_data['cluster'] = clustering_model.labels_

# クラスタリング後のデータの表示
print(query_data.head())

data_date	site_url	url	query	cluster
2023-12-18	https://example.com/	https://example.com/path1/	keyword1	0
2023-12-18	https://example.com/	https://example.com/path2/	keyword2	1
2023-12-18	https://example.com/	https://example.com/path3/	keyword3	2

5. 可視化

可視化はGoogle Colabを使用して以下のように行いました。

import matplotlib.pyplot as plt

# クラスタごとのインプレッション数とクリック数を集計するデータフレームの例
data = {
    'date': ['2023-12-15', '2024-01-01', '2024-01-15', '2024-02-01', '2024-02-15', '2024-03-01'],
    'cluster': [0, 1, 2, 3, 4],
    'impressions': [3000, 4000, 5000, 2000, 1000],
    'clicks': [150, 200, 250, 100, 50]
}
df = pd.DataFrame(data)

# 日付ごとの集計
daily_summary = df.groupby(['date', 'cluster']).sum().reset_index()

# 可視化
plt.figure(figsize=(10, 7))
for cluster in daily_summary['cluster'].unique():
    cluster_data = daily_summary[daily_summary['cluster'] == cluster]
    plt.plot(cluster_data['date'], cluster_data['impressions'], label=f'Cluster {cluster} Impressions')
    plt.plot(cluster_data['date'], cluster_data['clicks'], linestyle='--', label=f'Cluster {cluster} Clicks')
plt.legend()
plt.title('Daily Impressions and Clicks by Cluster')
plt.xlabel('Date')
plt.ylabel('Count')
plt.show()

6. 今後の課題

6-1. クリック数の改善ポイント

クリック率（CTR）の低さ: すべてのクラスタにおいてクリック数が非常に低いことから、クリック率を改善するための施策が必要です。これには以下のような対策が考えられます。
タイトルとメタディスクリプションの最適化: より魅力的なタイトルとメタディスクリプションを作成することで、クリック率を向上させる。
コンテンツの品質向上: ユーザーがクリックしたくなるような高品質なコンテンツを提供する。

6-2. ピラーコンテンツとクラスターコンテンツの活用方法

ピラーコンテンツ: 初めのKMeansクラスタリングでピラーコンテンツとなるトピックを特定できます。これらは広範なトピックに関する包括的なガイドや記事です。
クラスターコンテンツ: ピラーコンテンツからリンクされる詳細な記事です。各クラスタ内でさらにKMeansクラスタリングを行い、サブクラスタごとにクラスターコンテンツを作成します。
次に進むべきステップの一つとして、今回行ったのは一つのクラスタを集約してオープンソースのLLMを使用したり、直接ChatGPTやGeminiに入力してキーワードを挙げてもらうことです。この手法により、さらに効果的なSEO対策を講じることができます。

7. まとめ

今回行った手順を以下にまとめます：

データの抽出
Google BigQueryからGoogle Colabにデータを取り込みました。
エンベディング
Sentence-BERTを使用してクエリのエンベディングを行いました。
クラスタリング
KMeansクラスタリングを使用してクエリをクラスタリングしました。
可視化
各クラスタの毎日のインプレッション数とクリック数を折れ線グラフで可視化しました。

次に進むべきステップの一つとして、今回行ったのは一つのクラスタを集約してLLMにキーワードを挙げてもらうことがあります。

追記

この記事では、Google BigQueryを使用してGoogle Search Console（GSC）のデータを集計し、Google Colabで毎日のサイトのインプレッション数とクリック数を折れ線グラフに表示する方法を解説しました。自分の理解を深めることを目的として、さらに詳細な分析を行うために以下の追加ステップを実施しました。

1. クエリのエンベディングとクラスタリング

上記と同じSentence-BERTを使用してクエリをエンベディングし、KMeansクラスタリングを行いました。なお、KMeansクラスタリングの結果は毎回異なる場合があるため、ランダムシードを設定して一貫性を保つように注意しました。

2. クラスタごとのキーワード集約と代表キーワードの抽出

クラスタごとにクエリを集約し、頻出キーワードを抽出することで、各クラスターの代表的なキーワードを特定しました。これにより、各クラスターがどのようなテーマやトピックに関連しているかを明確にしました。代表的なキーワードはChatGPT-4を使用して生成しました。

from sklearn.cluster import KMeans

# データの読み込みやエンベディングの処理は省略

# KMeansクラスタリングの実行（ランダムステートを設定）
num_clusters = 5
random_state_value = 42  # ランダムステートの値を設定
clustering_model = KMeans(n_clusters=num_clusters, random_state=random_state_value)
query_data['cluster'] = clustering_model.fit_predict(list(query_data['embeddings']))

# クラスタリング後のデータの表示
print(query_data.head())

データの成形

クエリデータの処理を行い、エンベディング、クラスタ、およびキーワードを主テーブルに加えました。以下のようにしてデータを成形しました。

data_date	query	cluster	embeddings	impressions	clicks	optimal_seo_keywords
2024-03-09	気候変動影響対策	4	[-0.014895036, 0.12735185, 0.015225887, -0.036...]	1	0	気候変動の影響と対策
2024-03-09	環境保護持続可能な開発	4	[-0.032890186, 0.11716446, -0.022130221, -0.03...]	1	0	気候変動の影響と対策
2024-03-09	貧困削減国際協力	2	[-0.013537554, 0.132205, 0.04712552, -0.006515...]	1	0	貧困削減のための国際協力
2024-03-09	人権社会的平等	0	[-0.039319195, 0.063533805, 0.032075543, 0.018...]	2	1	人権と社会的平等
2024-03-09	教育機会平等	0	[-0.04220097, 0.07534874, 0.028307002, 0.0454...]	1	0	人権と社会的平等
2024-03-20	ジェンダー平等女性の権利	3	[-0.057452284, 0.0922173, 0.0039815316, -0.052...]	1	0	ジェンダー平等と女性の権利
2024-03-20	経済発展持続可能	3	[-0.03070985, 0.13424337, -0.00023574012, 0.00...]	1	0	ジェンダー平等と女性の権利
2024-03-20	教育の質向上	3	[-0.0078021763, 0.117459506, -0.018321441, -0...]	1	0	ジェンダー平等と女性の権利
2024-03-20	気候変動防止持続可能	3	[-0.023001295, 0.10210825, -0.0202181, -0.037...]	1	0	ジェンダー平等と女性の権利
2024-03-20	持続可能な開発 30代	3	[-0.03518132, 0.120757036, 0.0032538634, 0.00...]	1	0	ジェンダー平等と女性の権利

3. t-SNEによる次元削減と可視化

エンベディング後のクエリをt-SNEを用いて次元削減し、各クラスターを可視化しました。今回はPlotlyを使用して3次元散布図を作成し、各クラスターの代表キーワードをラベルとして表示しました。

4. インプレッション数とクリック数の時系列データの再可視化

クラスタリング結果に基づいて、各クラスターのインプレッション数とクリック数の時系列データを再度可視化しました。これにより、各クラスターのパフォーマンスの違いを明確にし、特定のクエリ群がどのようにユーザーの関心を引いているかを分析しました。

注意点

ランダムシードの設定: KMeansクラスタリングやt-SNEの結果は、ランダムシードを設定しないと毎回異なる可能性があります。解析の一貫性を保つために、ランダムシードを設定することを推奨します。

クラスターごとのキーワード例

以下に各クラスターごとの代表キーワードを示します。これらのキーワードは、クエリのエンベディングとクラスタリングの結果から抽出されました。

Cluster 0: 障害者支援サービスの評判と口コミ
Cluster 1: 発達障害と職場適応方法
Cluster 2: 発達障害の適職探し
Cluster 3: 自閉症スペクトラムの恋愛事情
Cluster 4: ADHDとHSPの両方を持つ人の特徴

このようにして、各クラスターの特徴を把握し、それぞれのテーマに基づいたSEO対策を行うことができます。