【画像解説】Search Console API✕PythonでSEOデータ取得

from google_auth_oauthlib.flow import InstalledAppFlow  
from googleapiclient.discovery import build  
import os  

SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']  
CLIENT_SECRETS_FILE = 'client_secret.json'  
TOKEN_FILE = 'token.json'  

def authenticate():  
    creds = None  
    if os.path.exists(TOKEN_FILE):  
        from google.oauth2.credentials import Credentials  
        creds = Credentials.from_authorized_user_file(TOKEN_FILE, SCOPES)  

    if not creds or not creds.valid:  
        flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRETS_FILE, SCOPES)  
        creds = flow.run_local_server(port=0)  
        with open(TOKEN_FILE, 'w') as token:  
            token.write(creds.to_json())  

    return creds  

def get_service():  
    credentials = authenticate()  
    service = build('searchconsole', 'v1', credentials=credentials)  
    return service  

def list_sites(service):  
    site_list = service.sites().list().execute()  
    for site in site_list.get('siteEntry', []):  
        print(f"URL: {site['siteUrl']} | 権限: {site['permissionLevel']}")  

if __name__ == '__main__':  
    sc_service = get_service()  
    list_sites(sc_service)

from google.oauth2 import service_account
from googleapiclient.discovery import build

# --- 設定（自分の環境に合わせて変更） ---
SERVICE_ACCOUNT_FILE = 'your_service_account.json'  # JSONファイルのパス
SITE_URL = 'https://aoi345.com/'                   # Search Consoleに登録済のサイトURL
TARGET_URL = 'https://aoi345.com/memorandum/search-console-api-python/'      # 調べたいページのURL

# --- 認証とAPIビルド ---
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES
)
service = build('searchconsole', 'v1', credentials=credentials)

# --- URLインスペクション ---
request = {
    'inspectionUrl': TARGET_URL,
    'siteUrl': SITE_URL
}
response = service.urlInspection().index().inspect(body=request).execute()

# --- 結果の出力 ---
index_status = response.get('inspectionResult', {}).get('indexStatusResult', {})
coverage = index_status.get('coverageState', '不明')
verdict = index_status.get('verdict', '不明')
last_crawled = index_status.get('lastCrawlTime', '不明')

print(f"📄 URL: {TARGET_URL}")
print(f"📦 インデックス状況: {coverage}")
print(f"🔍 判定: {verdict}")
print(f"⏰ 最終クロール日時: {last_crawled}")

url

import pandas as pd
import time
from google.oauth2 import service_account
from googleapiclient.discovery import build

# --- 設定 ---
CSV_FILE = 'urls.csv'  # 入力CSV
SERVICE_ACCOUNT_FILE = 'your_service_account.json'  # ダウンロードしたJSON鍵
SCOPES = ['https://www.googleapis.com/auth/indexing']

# --- 認証とAPIクライアント作成 ---
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)
service = build('indexing', 'v3', credentials=credentials)

# --- CSVからURLを読み込み ---
df = pd.read_csv(CSV_FILE)
if 'url' not in df.columns:
    raise ValueError("CSVに 'url' 列が見つかりません")

urls = df['url'].dropna().tolist()

# --- URLごとにインデックス登録リクエスト ---
for url in urls:
    print(f"📤 リクエスト中: {url}")
    body = {
        "url": url,
        "type": "URL_UPDATED"
    }
    try:
        response = service.urlNotifications().publish(body=body).execute()
        print(f"✅ 成功: {response.get('notifyTime', '')}")
    except Exception as e:
        print(f"❌ 失敗: {e}")
    time.sleep(1.5)  # クォータ制限のため少し待機

url

import pandas as pd
import time
from google.oauth2 import service_account
from googleapiclient.discovery import build

# --- 設定 ---
CSV_FILE = 'urls.csv'  # 入力ファイル
SERVICE_ACCOUNT_FILE = 'your_service_account.json'
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
SITE_URL = 'https://aoi345.com/'  # Search Consoleに登録されたサイトURL（最後に / が必要）

# --- 認証 ---
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES
)
service = build('searchconsole', 'v1', credentials=credentials)

# --- 関数：インデックス状況を確認 ---
def check_index_status(target_url):
    try:
        request = {
            'inspectionUrl': target_url,
            'siteUrl': SITE_URL
        }
        response = service.urlInspection().index().inspect(body=request).execute()
        result = response.get('inspectionResult', {}).get('indexStatusResult', {})
        return {
            'coverageState': result.get('coverageState', '不明'),
            'verdict': result.get('verdict', '不明'),
            'lastCrawlTime': result.get('lastCrawlTime', 'なし')
        }
    except Exception as e:
        return {
            'coverageState': 'エラー',
            'verdict': str(e),
            'lastCrawlTime': ''
        }

# --- CSV読込 & チェック実行 ---
df = pd.read_csv(CSV_FILE)
if 'url' not in df.columns:
    raise ValueError("CSVに 'url' 列がありません")

results = []
for url in df['url']:
    print(f"🔍 チェック中: {url}")
    status = check_index_status(url)
    results.append({
        'url': url,
        *status
    })
    time.sleep(1.5)  # クォータ制限：1秒以上の待機を推奨

# --- 結果をCSV出力 ---
output_df = pd.DataFrame(results)
output_df.to_csv("index_status_result.csv", index=False)
print("✅ 出力完了：index_status_result.csv")

url

import pandas as pd
from datetime import datetime, timedelta, timezone
from google.oauth2 import service_account
from googleapiclient.discovery import build
import time

# === 設定 ===
SERVICE_ACCOUNT_FILE = 'your_service_account.json'  # サービスアカウントJSON
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
SITE_URL = 'https://aoi345.com/'  # GSC登録プロパティ（末尾 / を含める）
INPUT_CSV = 'urls.csv'
OUTPUT_CSV = 'gsc_url_metrics_grouped_by_metric.csv'
NUM_WEEKS = 54          # 取得する週数（直近N週）
MAX_URL_COUNT = 1000    # 最大URL数（クォータ制限対策）

# === 認証処理 ===
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)
service = build('searchconsole', 'v1', credentials=credentials)

# === 週別日付ラベル生成 ===
def generate_week_ranges(weeks=NUM_WEEKS):
    end_date = datetime.now(timezone.utc).date()
    ranges = []
    for i in range(weeks):
        to_date = end_date - timedelta(days=i * 7)
        from_date = to_date - timedelta(days=6)
        label = f"{from_date}~{to_date}"
        ranges.append((str(from_date), str(to_date), label))
    return list(reversed(ranges))  # 昇順

# === 各URL・週に対する指標取得 ===
def get_metrics(service, site_url, url, start_date, end_date):
    try:
        request = {
            'startDate': start_date,
            'endDate': end_date,
            'dimensions': ['page'],
            'dimensionFilterGroups': [{
                'filters': [{
                    'dimension': 'page',
                    'operator': 'equals',
                    'expression': url
                }]
            }],
            'rowLimit': 1
        }
        response = service.searchanalytics().query(siteUrl=site_url, body=request).execute()
        if 'rows' in response:
            row = response['rows'][0]
            return {
                'clicks': row.get('clicks', 0),
                'impressions': row.get('impressions', 0),
                'ctr': round(row.get('ctr', 0), 4),
                'position': round(row.get('position', 0), 2)
            }
        else:
            return {'clicks': 0, 'impressions': 0, 'ctr': 0.0, 'position': 0.0}
    except Exception as e:
        return {'clicks': 'ERR', 'impressions': 'ERR', 'ctr': 'ERR', 'position': 'ERR'}

# === メイン処理 ===
df = pd.read_csv(INPUT_CSV)
if 'url' not in df.columns:
    raise ValueError("CSVファイルに 'url' 列が必要です")

df = df.head(MAX_URL_COUNT)
week_ranges = generate_week_ranges()
results = []

for url in df['url']:
    print(f"🔍 処理中: {url}")
    row = {'url': url}
    clicks, impressions, ctrs, positions = {}, {}, {}, {}

    for start_date, end_date, label in week_ranges:
        metrics = get_metrics(service, SITE_URL, url, start_date, end_date)
        clicks[f"clicks_{label}"] = metrics['clicks']
        impressions[f"impr_{label}"] = metrics['impressions']
        ctrs[f"ctr_{label}"] = metrics['ctr']
        positions[f"pos_{label}"] = metrics['position']
        time.sleep(0.3)  # クォータ制限対策

    # 指標ごとに順番に結合
    row.update(clicks)
    row.update(impressions)
    row.update(ctrs)
    row.update(positions)
    results.append(row)

# === 出力 ===
output_df = pd.DataFrame(results)
output_df.to_csv(OUTPUT_CSV, index=False)
print(f"✅ 出力完了: {OUTPUT_CSV}")

import pandas as pd
from datetime import datetime, timedelta, timezone
from google.oauth2 import service_account
from googleapiclient.discovery import build
import time

# === 設定 ===
SERVICE_ACCOUNT_FILE = 'your_service_account.json'  # サービスアカウントのJSON
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
SITE_URL = 'https://aoi345.com/'  # Search Consoleプロパティ（末尾/付き）
INPUT_CSV = 'urls.csv'
OUTPUT_CSV = 'gsc_url_position_by_day.csv'
NUM_DAYS = 30         # ← 直近の日数分だけ取得（例：30日）
MAX_URL_COUNT = 100   # ← 最大URL数（クォータ制限対策）

# === 認証 ===
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)
service = build('searchconsole', 'v1', credentials=credentials)

# === 日付リスト生成 ===
def generate_date_list(num_days=NUM_DAYS):
    today = datetime.now(timezone.utc).date()
    return [str(today - timedelta(days=i)) for i in range(num_days)][::-1]  # 昇順

# === 指定URLの指定日の掲載順位取得 ===
def get_position(service, site_url, url, target_date):
    try:
        request = {
            'startDate': target_date,
            'endDate': target_date,
            'dimensions': ['page'],
            'dimensionFilterGroups': [{
                'filters': [{
                    'dimension': 'page',
                    'operator': 'equals',
                    'expression': url
                }]
            }],
            'rowLimit': 1
        }
        response = service.searchanalytics().query(siteUrl=site_url, body=request).execute()
        if 'rows' in response:
            return round(response['rows'][0].get('position', 0), 2)
        else:
            return ''
    except Exception as e:
        return 'ERR'

# === メイン処理 ===
df = pd.read_csv(INPUT_CSV)
if 'url' not in df.columns:
    raise ValueError("CSVファイルに 'url' 列が必要です")

df = df.head(MAX_URL_COUNT)
date_list = generate_date_list()
results = []

for url in df['url']:
    print(f"🔍 処理中: {url}")
    row = {'url': url}
    for date in date_list:
        row[f"pos_{date}"] = get_position(service, SITE_URL, url, date)
        time.sleep(0.3)  # クォータ制限回避
    results.append(row)

# === 出力 ===
output_df = pd.DataFrame(results)
output_df.to_csv(OUTPUT_CSV, index=False)
print(f"✅ 出力完了: {OUTPUT_CSV}")

query
投資信託とは
新NISA
インデックス投資

import pandas as pd
from datetime import datetime, timedelta, timezone
from google.oauth2 import service_account
from googleapiclient.discovery import build
import time

# === 設定 ===
SERVICE_ACCOUNT_FILE = 'your_service_account.json'  # サービスアカウントJSON
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
SITE_URL = 'https://aoi345.com/'  # GSCプロパティURL（末尾スラッシュ付き）
INPUT_CSV = 'query.csv'
OUTPUT_CSV = 'gsc_query_metrics_grouped_by_metric.csv'
NUM_WEEKS = 54          # 直近何週分を取得するか
MAX_QUERY_COUNT = 1000  # 処理するクエリ数の上限

# === 認証処理 ===
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)
service = build('searchconsole', 'v1', credentials=credentials)

# === 日付範囲生成（7日間×N週）===
def generate_week_ranges(end_date=None, weeks=NUM_WEEKS):
    if end_date is None:
        end_date = datetime.now(timezone.utc).date()
    ranges = []
    for i in range(weeks):
        to_date = end_date - timedelta(days=i * 7)
        from_date = to_date - timedelta(days=6)
        label = f"{from_date}~{to_date}"
        ranges.append((str(from_date), str(to_date), label))
    return list(reversed(ranges))  # 古い順から並べる

# === 指標取得関数 ===
def get_metrics(service, site_url, query, start_date, end_date):
    try:
        request = {
            'startDate': start_date,
            'endDate': end_date,
            'dimensions': ['query'],
            'dimensionFilterGroups': [{
                'filters': [{
                    'dimension': 'query',
                    'operator': 'equals',
                    'expression': query
                }]
            }],
            'rowLimit': 1
        }
        response = service.searchanalytics().query(siteUrl=site_url, body=request).execute()
        if 'rows' in response:
            row = response['rows'][0]
            return {
                'clicks': row.get('clicks', 0),
                'impressions': row.get('impressions', 0),
                'ctr': round(row.get('ctr', 0), 4),
                'position': round(row.get('position', 0), 2)
            }
        else:
            return {'clicks': 0, 'impressions': 0, 'ctr': 0.0, 'position': 0.0}
    except Exception as e:
        return {'clicks': 'ERR', 'impressions': 'ERR', 'ctr': 'ERR', 'position': 'ERR'}

# === メイン処理 ===
df = pd.read_csv(INPUT_CSV)
if 'query' not in df.columns:
    raise ValueError("CSVファイルに 'query' 列が必要です")

df = df.head(MAX_QUERY_COUNT)
date_ranges = generate_week_ranges()
results = []

for query in df['query']:
    print(f"🔍 クエリ処理中: {query}")
    row = {'query': query}
    clicks = {}
    impressions = {}
    ctr = {}
    position = {}

    for start_date, end_date, label in date_ranges:
        metrics = get_metrics(service, SITE_URL, query, start_date, end_date)
        clicks[f"clicks_{label}"] = metrics['clicks']
        impressions[f"impr_{label}"] = metrics['impressions']
        ctr[f"ctr_{label}"] = metrics['ctr']
        position[f"pos_{label}"] = metrics['position']
        time.sleep(0.3)  # クォータ制限対策

    # 指標ごとに列をまとめて順番に追加
    row.update(clicks)
    row.update(impressions)
    row.update(ctr)
    row.update(position)
    results.append(row)

# === 出力 ===
output_df = pd.DataFrame(results)
output_df.to_csv(OUTPUT_CSV, index=False)
print(f"✅ 出力完了: {OUTPUT_CSV}")

import pandas as pd
from datetime import datetime, timedelta, timezone
from google.oauth2 import service_account
from googleapiclient.discovery import build
import time

# === 設定 ===
SERVICE_ACCOUNT_FILE = 'your_service_account.json'  # サービスアカウントJSON
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
SITE_URL = 'https://aoi345.com/'  # GSCに登録済のプロパティ（末尾/付き）
INPUT_CSV = 'query.csv'
OUTPUT_CSV = 'gsc_query_position_by_day.csv'
NUM_DAYS = 30         # ← 過去の日数（例：30日分）
MAX_QUERY_COUNT = 100 # ← クエリの最大処理数

# === GSC認証 ===
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)
service = build('searchconsole', 'v1', credentials=credentials)

# === 直近の日付リストを生成 ===
def generate_date_list(num_days=NUM_DAYS):
    today = datetime.now(timezone.utc).date()
    return [str(today - timedelta(days=i)) for i in range(num_days)][::-1]

# === 指定クエリ・日付の position を取得 ===
def get_position(service, site_url, query, date):
    try:
        request = {
            'startDate': date,
            'endDate': date,
            'dimensions': ['query'],
            'dimensionFilterGroups': [{
                'filters': [{
                    'dimension': 'query',
                    'operator': 'equals',
                    'expression': query
                }]
            }],
            'rowLimit': 1
        }
        response = service.searchanalytics().query(siteUrl=site_url, body=request).execute()
        if 'rows' in response:
            return round(response['rows'][0].get('position', 0), 2)
        else:
            return ''
    except Exception as e:
        return 'ERR'

# === メイン処理 ===
df = pd.read_csv(INPUT_CSV)
if 'query' not in df.columns:
    raise ValueError("CSVファイルに 'query' 列が必要です")

df = df.head(MAX_QUERY_COUNT)
date_list = generate_date_list()
results = []

for query in df['query']:
    print(f"🔍 クエリ処理中: {query}")
    row = {'query': query}
    for date in date_list:
        row[f'pos_{date}'] = get_position(service, SITE_URL, query, date)
        time.sleep(0.3)  # クォータ制限対策
    results.append(row)

# === CSV出力 ===
output_df = pd.DataFrame(results)
output_df.to_csv(OUTPUT_CSV, index=False)
print(f"✅ 出力完了: {OUTPUT_CSV}")