PythonでSearch Console APIからデータを取得して自前のデータベースを作る

15746 ワード
SEO googleapi SearchConsole Python Python3 Python テキストリンク

GoogleのSearch Consoleのデータを自前のDBに保存したいな〜と思い、PythonとGoogleから提供されているAPIを使って検索パフォーマンスを記録するプログラムを書いてみました。

データの形式

Search ConsoleからAPIで各種データ取得し、日付ごとに下記を記録するようにします。
- 合計Click数
- 合計Impression
- 平均CTR(Click-through Rate)
- 平均掲載順位

データベースのテーブルの中身にはこのようにデータが保存されるようにします。

前提/準備

  • Macのターミナル: Pythonコマンドを実行します
  • データベース(MySQL): データの保存先
  • Python 3.6

使いはじめの際にはGoogleの認証が必要なのですが、この記事では省きます。下記の記事などを参考に初期設定を済ませていただければと思います。
https://github.com/googleapis/google-cloud-python

Pythonのコード

search_console_daily.py
# -*- coding: utf-8 -*-

import sys
import datetime
import MySQLdb
import argparse
from googleapiclient import sample_tools

#コマンドラインの引数の指定
argparser = argparse.ArgumentParser(add_help=False)
argparser.add_argument('property_uri', type=str,\
            help=('Site URI to query data for (including trailing slash).'))
argparser.add_argument('table_name', type=str,\
            help=('table name for saving records of search console data'))

def main(argv):

    #DB接続情報
    conn = MySQLdb.connect(host = '{host_server_url}', db = '{database}',\
           user = '{user_name}', password = '{passwd}', charset = 'utf8mb4')
    c = conn.cursor()

    #第二引数をテーブル名として利用
    sqtbl = argv[2]

    service, flags = sample_tools.init(
            argv, 'webmasters', 'v3', __doc__, __file__, parents=[argparser],
            scope='https://www.googleapis.com/auth/webmasters.readonly')

    #インサート先テーブルから最新日を取得し、それ以降から今日時点までのデータのみ対象とする
    c.execute('select (max(date) + INTERVAL 1 DAY) as sd from ' + sqtbl)
    date_start_arry = c.fetchone()
    date_start = str(date_start_arry[0])
    if date_start == 'None':
        date_start = '2018-01-01'
    date_end = datetime.datetime.today().strftime("%Y-%m-%d")

    print('取得WEBサイト:', argv[1])
    print('取得開始日:', date_start)
    print('取得終了日:', date_end)

    request = {
            'startDate': date_start,
            'endDate': date_end,
            'dimensions': ['date'],
    }
    response = execute_request(service, flags.property_uri, request)

    if 'rows' not in response:
        print('Empty response')
        return
    else:
        #テーブル挿入用のデータ成形
        insert_data = []
        for row in response['rows']:
            insert_data.append([row['keys'][0], int(row['clicks']),\
                                int(row['impressions']),round(row['ctr'],3),\
                                round(row['position'],1)])

        #指定したテーブルへのデータ挿入
        c.executemany('insert into ' + sqtbl + ' values (%s,%s,%s,%s,%s)',insert_data)

        #確認用にDBから結果を取得して表示
        c.execute('select * from ' + sqtbl + ' order by Date desc limit 10;')
        for rowsql in c.fetchall():
            print(rowsql)
    conn.commit()

    conn.close()

def execute_request(service, property_uri, request):
    return service.searchanalytics().query(
            siteUrl=property_uri, body=request).execute()

if __name__ == '__main__':
    main(sys.argv)

テーブルの準備

基本的なSQLでのテーブル作成などの説明は省きます。下記がテーブル情報です。

CREATE TABLE `log_daily_jd` (
  `Date` date NOT NULL,
  `Clicks` int(11) DEFAULT NULL,
  `Impressions` int(11) DEFAULT NULL,
  `CTR` float DEFAULT NULL,
  `Position` float DEFAULT NULL,
  PRIMARY KEY (`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

コマンドの実行

下記のコマンドをMacのターミナルから実行します。

$ search_console_daily.py https://www.example.com table_name

引数を2つ取っていて、
・引数1: サイトURL(サーチコンソールに登録されているもの)
・引数2: データをINSERTするテーブル名
です。サイトごとに保存先テーブルを分ける想定だったので、INSERT先テーブル名も引数にしています。

実行してみる

$ python search_console_daily.py https://www.example.com table_name
取得WEBサイト: https://www.example.com
取得開始日: 2019-09-07
取得終了日: 2019-09-11
(datetime.date(2019, 9, 8), 1496, 40184, 0.037, 12.2)
(datetime.date(2019, 9, 7), 1335, 35857, 0.037, 13.4)
(datetime.date(2019, 9, 6), 1246, 35621, 0.035, 15.1)
(datetime.date(2019, 9, 5), 1291, 36817, 0.035, 14.7)
(datetime.date(2019, 9, 4), 1212, 40231, 0.03, 17.8)
(datetime.date(2019, 9, 3), 1326, 35701, 0.037, 14.0)
(datetime.date(2019, 9, 2), 1369, 38580, 0.035, 15.9)
(datetime.date(2019, 9, 1), 1489, 38202, 0.039, 15.1)
(datetime.date(2019, 8, 31), 1111, 35004, 0.032, 17.6)
(datetime.date(2019, 8, 30), 1193, 33872, 0.035, 16.6)

確認用の出力データが返ってきています。データベースを確認するとしっかりデータが記録されているかと思います。
これにて完了です。
バッチ処理化して自動でこれらの作業を行ってもいいと思います。

最後に

API制限がなければ検索クエリやURLの生データをすべて保存して色々遊べると思うのですが、月5~10万UUくらいのサイトになってくるともう全データ抽出は難しいかなと思います。
APIのリミット(google 公式サイト)

「もっとこういう書き方が便利だよ」とかあればコメントもらえると嬉しいです。喜びます。

また、上記のデータをMySQLではなくCSVとして保存する方法については自分のブログで書きました。
https://xxbxxqxx.com/post/186