前回、Google Search Console APIを使えるようにするgoogle側の設定が終わって、アプリの鍵も手に入れたのでアプリを作っていく。
- アプリの構想
- 環境
- Pythonのパッケージを追加
- Google Search Console APIサンプルアプリ作成
- dbの作成
- Pythonでメール送信サンプルアプリ作成
- googleから取得してdbに書き込むアプリを作る
- 自動実行
アプリの構想
今回の目的は、後でどうにでもできるようにデータを溜め込んでいくこと。dbのUIはまた今度。
ざっと、以下のような感じ。
- Google Search Console APIで検索パフォーマンスのデータを取得。
- 取得したデータはMySQLのdbに突っ込む。
- 毎日、自動で実行する。(これはcronを使う)
- 外的要因等でスクリプトが動作しないことを考慮し、過去10日分を取得する。
- 取得した10日分の内、dbに未登録なものだけ登録する。
ここまでを今回作るアプリに実装する。
環境
- OS：Ubuntu 22.04.1 LTS(確認コマンド：lsb_release -a)
- python：3.10.12(確認コマンド：python -V)
pythonは以下のエイリアスを切って使ってる。
alias python="/usr/bin/python3"
alias pip="/usr/bin/pip3"
Pythonのパッケージを追加
pipでパッケージをインストールする。
sudo pip3 install --upgrade pip # MySQLを使うためのパッケージ pip install mysql-connector-python # Google API クライアント ライブラリ pip install google-api-python-client # googleのユーザー承認用ライブラリ pip install google-auth google-auth-oauthlib google-auth-httplib2 oauth2client # HTTPライブラリ pip install requests # その他 pip install pandas
Google Search Console APIサンプルアプリ作成
ゆーはさんの「Search ConsoleのAPIを使ってみる」を参考にSearch Console APIを使ったサンプルを作ってみる。
作ったのがこれ(test_api.py)。赤字のところがgoogleからダウンロードした鍵ファイル。
from datetime import datetime, timedelta import pandas as pd from apiclient.discovery import build from oauth2client.service_account import ServiceAccountCredentials scope = ['https://www.googleapis.com/auth/webmasters.readonly'] key_file_location = '/work/google/keys/searchconsoleapi01-hoge-hogehoge.json' url = 'https://donbura.hatenablog.com/' credentials = ServiceAccountCredentials.from_json_keyfile_name(key_file_location, scope) webmasters = build('webmasters', 'v3', credentials=credentials) dimensions_list = ['date', 'query', 'page', 'device', 'country'] start_date = '2023-12-02' end_date = '2023-12-03' row_limit = 5 request = { 'startDate': start_date, 'endDate': end_date, 'dimensions': dimensions_list, 'rowLimit': row_limit } response = webmasters.searchanalytics().query(siteUrl=url, body=request).execute() df = pd.json_normalize(response['rows']) for i, dimension in enumerate(dimensions_list): df[dimension] = df['keys'].apply(lambda row: row[i]) df2 = df.drop(columns='keys') print(df2)
実行すると、全角まじりだからインデントが乱れてて見にくいけど何か取得出来たっぽい。
kirin@cf-n10$ python test_api.py clicks impressions ctr position date query page device country 0 1 1 1.0 7 2023-12-02 android tv vlc https://donbura.hatenablog.com/entry/%E3%83%AA... DESKTOP jpn 1 1 1 1.0 10 2023-12-02 vlc ts 再生できない https://donbura.hatenablog.com/entry/VLC_for_A... DESKTOP jpn 2 1 2 0.5 38 2023-12-03 arduino gpib https://donbura.hatenablog.com/entry/arduino%E... DESKTOP jpn 3 1 1 1.0 3 2023-12-03 mc22 オイル交換 https://donbura.hatenablog.com/entry/CBR250RR%... MOBILE jpn 4 1 1 1.0 54 2023-12-03 ブラビア usbメモリ 再生できない https://donbura.hatenablog.com/entry/%E3%83%AA... MOBILE jpn
長い文字列が短く省略されるのはPythonの仕様で、下記の命令で変更できる。
pd.set_option("display.max_colwidth", 100) # 横幅の変更
pd.set_option("display.max_rows", 100) # 高さの変更
取得する期間はstart_dateの2023/12/2からend_dateの2023/12/3までで、row_limitで最大取得数えお5個に制限してる。ここを変えると期間や取得するデータ数を変更できる。
row_limitの最大は25,000。その他APIのパラメータの詳細はgoogleのリファレンスに書いてある。
dbの作成
MySQLにログインしてdbとテーブルを作る。
mysql> create database searchConsole;
mysql> use searchConsole;
mysql> create table basic_hatena (entry_date datetime, type varchar(20), clicks int, impressions int, ctr float, position float, event_date date, query varchar(256), page varchar(256), device varchar(16), country varchar(16));
できたテーブルはこんな感じ。
mysql> desc basic_hatena; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | entry_date | datetime | YES | | NULL | | dbに登録した日時を記録する | type | varchar(20) | YES | | NULL | | リクエストしたtypeを記録する
| clicks | int | YES | | NULL | | | impressions | int | YES | | NULL | | | ctr | float | YES | | NULL | | | position | float | YES | | NULL | | | event_date | date | YES | | NULL | | | query | varchar(256) | YES | | NULL | | | page | varchar(256) | YES | | NULL | | | device | varchar(16) | YES | | NULL | | | country | varchar(16) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+
サンプルには無かったけど、リクエストのtypeで画像検索で来たのか、動画検索できたのか等がわかるっぽいので、type毎に記録するためにtype列を追加した。discoverとgoogleNewsはエラーになってしまう。discoverは何かよくわからないし、2つともココの流入になる気がしないので深追いせずに除外した。
entry_dateは単純にdbに書き込んだ時間を記録する。多分、デバッグでしか使わない予定。
Pythonでメール送信サンプルアプリ作成
自動実行は信用できないところもあるので、実行毎にメールで通知したい。その為にまずはPythonでメールを送信するサンプルを作ってみる。
参考にしたのは「PythonエンジニアによるPython3学習サイト」の「Pythonでメール送信」。
出来たサンプルがこれ。gmailからの送信専用になってる。google_app_passにはアプリパスワード、その他赤字の箇所は任意のgmailのアドレスを入れる。
from email.mime.text import MIMEText import smtplib google_app_pass="hoge hoge hoge hoge" # SMTP認証情報 account = "hoge@gmail.com" password = google_app_pass # 送受信先 to_email = "hoge@gmail.com" from_email = "hoge@gmail.com" # MIMEの作成 subject = "テストメール" message = "テストメール" msg = MIMEText(message, "html") msg["Subject"] = subject msg["To"] = to_email msg["From"] = from_email # メール送信処理 server = smtplib.SMTP("smtp.gmail.com", 587) server.starttls() server.login(account, password) server.send_message(msg) server.quit()
自分はgoogleを2段階認証で使ってるので参考にしたサイトのコードのまま(passwordに通常のメールクライアントで使ってるパスワードを使う)だと下記エラーになって動かなかった。
kirin@cf-n10$ python test_mail.py Traceback (most recent call last): File "/work/google/searchConsole/test_mail.py", line 26, inserver.login(account, password) File "/usr/lib/python3.10/smtplib.py", line 750, in login raise last_exception File "/usr/lib/python3.10/smtplib.py", line 739, in login (code, resp) = self.auth( File "/usr/lib/python3.10/smtplib.py", line 662, in auth raise SMTPAuthenticationError(code, resp) smtplib.SMTPAuthenticationError: (535, b'5.7.8 Username and Password not accepted. For more information, go to\n5.7.8 https://support.google.com/mail/?p=BadCredentials q14-hogehoge.195 - gsmtp')
そこで、Kaitoさんの「PythonでGmailを自動送信する方法」の最後の方にある補足でアプリパスワードが必要ということが分かったのでgoogleの「アプリ パスワードでログインする」の手順に従ってアプリパスワードを生成した。そのパスワードをgoogle_app_passに入れると先のスクリプトで送信できた。
googleから取得してdbに書き込むアプリを作る
材料は揃ったので本番アプリを作ってみる。
googleから取得するスクリプトはこれ(searchConsole.py)。
from datetime import datetime, timedelta import pandas as pd from apiclient.discovery import build from oauth2client.service_account import ServiceAccountCredentials import searchConsole_db import notice scope = ['https://www.googleapis.com/auth/webmasters.readonly'] key_file_location = '/work/google/keys/searchconsoleapi01-hoge-hogehoge.json' url = 'https://donbura.hatenablog.com/' credentials = ServiceAccountCredentials.from_json_keyfile_name(key_file_location, scope) webmasters = build('webmasters', 'v3', credentials=credentials) entry_count = {}; nowadays = datetime.now() # before_yesterday = nowadays - timedelta(360*5) # db作成後の初回 # before_yesterday = nowadays - timedelta(30) # 毎週定期実行 before_yesterday = nowadays - timedelta(10) # 毎日定期実行用 yesterday = nowadays - timedelta(1) # 昨日 dimensions_list = ['date', 'query', 'page', 'device', 'country'] type_list = ['web', 'image', 'video', 'news'] # start_date = '2023-12-02' # end_date = '2023-12-03' start_date = before_yesterday.strftime('%Y-%m-%d') end_date = yesterday.strftime('%Y-%m-%d') row_limit = 25000 # row_limit = 10 for type in type_list: entry_count[type] = 0 print("type : ", type) request = { 'startDate': start_date, 'endDate': end_date, 'dimensions': dimensions_list, 'rowLimit': row_limit, 'type': type } response = webmasters.searchanalytics().query(siteUrl=url, body=request).execute() # print("response:", response) try: df = pd.json_normalize(response['rows']) for i, dimension in enumerate(dimensions_list): df[dimension] = df['keys'].apply(lambda row: row[i]) df2 = df.drop(columns='keys') except: print("no data in ", type) continue result = searchConsole_db.add_record(type, df2) entry_count[type] = result message = "実行時間：" + nowadays.strftime('%Y-%m-%d %H:%M:%S') + "\n" message = message + "取得開始日： " + before_yesterday.strftime('%Y-%m-%d') + "\n" message = message + "取得終了日： " + yesterday.strftime('%Y-%m-%d') + "\n\n" for type in type_list: message = message + "新規登録数(" + type + ")：" + str(entry_count[type]) + "\n" subject = "サーチコンソールデータ取得：はてなブログ" print(subject+"\n"+message) notice.send_mail(subject, message)
初回実行時は before_yesterday(試しながら作ってたから実際の意味と変数名が乖離してしまった)を大きくしてgoogleが持っているデータを全て取る。その後は定期実行する周期に合わせて短くして運用する。
緑の部分は各type毎にデータを取得するために変更した箇所で、type_listのweb、image、video、newsを順番に取得するようにしている。
オレンジの部分が取得したデータを別ファイルのdb処理に渡してdbに登録させてる。
紫の部分は処理結果をメールで送信する処理。
dbに登録するスクリプトはこれ(searchConsole_db.py)。赤字の部分は環境に合わせて変更する。
import pandas as pd
import mysql.connector
from datetime import datetime
from urllib.parse import unquote
# 変数
conn = None
# 現在時刻
current_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# print(current_date)
def add_record(search_type, data_df):
#######################################################
###### connect db
#######################################################
conn = db_conn()
#######################################################
###### check & add record
#######################################################
entry_count = 0
for rows in data_df.iterrows():
row = rows[1]
row['query'] = (row['query']).replace("'", "\\'") # 2024/01/05 siba mod
exists = db_check_exist(conn, search_type, row)
if exists == False:
db_add_record(conn, search_type, row)
entry_count = entry_count + 1
#######################################################
###### disconnect db
#######################################################
db_disconn(conn)
return entry_count
#######################################################
###### connect db
#######################################################
def db_conn():
conn = mysql.connector.connect(
user='root', # ユーザー名
password='hogehoge', # パスワード
host='localhost', # ホスト名(IPアドレス）
database='searchConsole' # データベース名
)
return conn
#######################################################
###### disconnect db
#######################################################
def db_disconn(conn):
conn.close
return
#######################################################
###### 既に登録済のデータかチェック
#######################################################
def db_check_exist(conn, type, row):
sql="select exists(select * from basic_hatena where event_date='{0}' and query='{1}' and page='{2}' and device='{3}' and country='{4}' and type='{5}');".format(
row['date'],
row['query'],
unquote(row['page']),
row['device'],
row['country'],
type
)
# print("check sql:",sql)
cur = conn.cursor(buffered=True)
cur.execute(sql)
if cur.fetchone()[0]==0:
print("■登録：{:0} {:40} {:10} {:5} {:4}".format(row['date'],row['query'],row['device'],row['country'],unquote(row['page'])))
return False
else:
print("登録済：{:0} {:40} {:10} {:5} {:4}".format(row['date'],row['query'],row['device'],row['country'],unquote(row['page'])))
return True
#######################################################
###### データを登録
#######################################################
def db_add_record(conn, type, row):
sql="insert into basic_hatena (entry_date, type, clicks, impressions, ctr, position, event_date, query, page, device, country) values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}');".format(
current_date,
type,
row['clicks'],
row['impressions'],
row['ctr'],
row['position'],
row['date'],
row['query'],
unquote(row['page']),
row['device'],
row['country']
)
# print("add sql:",sql)
cur = conn.cursor(buffered=True)
cur.execute(sql)
conn.commit()
return
db_check_exist()の登録済データのチェック方法は今後変更するかも。
2024/1/5 追記：検索クエリにシングルクオートが含まれていると動作しなかったので、青字の行を追加してエスケープ文字を挿入するようにした。
メール送信スクリプトはこれ(notice.py)。例のごとく赤字は環境に合わせて変更する。
from email.mime.text import MIMEText import smtplib google_app_pass="hoge hoge hoge hoge" # SMTP認証情報 account = "hoge@gmail.com" password = google_app_pass # 送受信先 to_email = "hoge@gmail.com" from_email = "hoge@gmail.com" def send_mail(subject, message): # MIMEの作成 msg = MIMEText(message) msg["Subject"] = subject msg["To"] = to_email msg["From"] = from_email # メール送信処理 server = smtplib.SMTP("smtp.gmail.com", 587) server.starttls() server.login(account, password) server.send_message(msg) server.quit()
実行してdbに書かれた一部を見てみるとこんな感じ。
mysql> select * from basic_hatena where type = "web" limit 10; +---------------------+------+--------+-------------+----------+----------+------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+---------+ | entry_date | type | clicks | impressions | ctr | position | event_date | query | page | device | country | +---------------------+------+--------+-------------+----------+----------+------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+---------+ | 2023-12-16 12:10:15 | web | 2 | 6 | 0.333333 | 2.66667 | 2023-07-28 | gpib arduino | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | | 2023-12-16 12:10:15 | web | 2 | 4 | 0.5 | 1.75 | 2023-08-02 | arduino gpib | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | | 2023-12-16 12:10:15 | web | 2 | 2 | 1 | 1.5 | 2023-08-09 | arduino gpib | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | | 2023-12-16 12:10:15 | web | 2 | 3 | 0.666667 | 1 | 2023-08-23 | arduino gpib | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | MOBILE | jpn | | 2023-12-16 12:10:15 | web | 2 | 4 | 0.5 | 3.5 | 2023-10-08 | mc22 オイル交換 | https://donbura.hatenablog.com/entry/CBR250RR(MC22)のオイル交換とオイル量の測り方 | MOBILE | jpn | | 2023-12-16 12:10:15 | web | 2 | 4 | 0.5 | 3 | 2023-12-08 | vlc レジューム android | https://donbura.hatenablog.com/entry/VLC_for_AndroidでTS再生時のシーク、音声切替、レジューム等の | MOBILE | jpn | | 2023-12-16 12:10:15 | web | 1 | 1 | 1 | 12 | 2023-07-05 | gpib arduino | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | | 2023-12-16 12:10:15 | web | 1 | 1 | 1 | 29 | 2023-07-08 | cbr250rr オイル量 | https://donbura.hatenablog.com/entry/CBR250RR(MC22)のオイル交換とオイル量の測り方 | MOBILE | jpn | | 2023-12-16 12:10:15 | web | 1 | 3 | 0.333333 | 7.66667 | 2023-07-18 | arduino gpib | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | | 2023-12-16 12:10:15 | web | 1 | 4 | 0.25 | 8 | 2023-07-18 | gpib arduino | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | +---------------------+------+--------+-------------+----------+----------+------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+---------+---------+ 10 rows in set (0.00 sec) mysql> select * from basic_hatena where type = "image" limit 10; +---------------------+-------+--------+-------------+------+----------+------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------+---------+ | entry_date | type | clicks | impressions | ctr | position | event_date | query | page | device | country | +---------------------+-------+--------+-------------+------+----------+------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------+---------+ | 2023-12-16 12:10:15 | image | 1 | 1 | 1 | 6 | 2023-11-14 | arduino gpib | https://donbura.hatenablog.com/entry/arduinoを使ったGP-IBアダプタでpython使ってリモートワーク | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 1 | 1 | 1 | 15 | 2023-11-22 | mc22 フロントフォーク | https://donbura.hatenablog.com/entry/CBR250RR(MC22)のフロントフォークのオーバーホール：組み立 | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 1 | 1 | 1 | 21 | 2023-11-29 | blender アバター作成 | https://donbura.hatenablog.com/entry/ClusterのアバターをBlender_3.2で作ってみた | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 1 | 1 | 1 | 56 | 2023-12-06 | blender アバター | https://donbura.hatenablog.com/entry/ClusterのアバターをBlender_3.2で作ってみた | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 0 | 1 | 0 | 96 | 2023-06-19 | ダイソー すっきりバー | https://donbura.hatenablog.com/entry/CBR250RR(MC22)のステムベアリング交換：調整 | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 0 | 1 | 0 | 90 | 2023-06-21 | blender ガンダム 作り方 | https://donbura.hatenablog.com/entry/ClusterのアバターをBlender_3.2で作ってみた | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 0 | 1 | 0 | 283 | 2023-06-21 | mediatomb | https://donbura.hatenablog.com/entry/録画サーバのmediatombのコンテンツリストが壊れる | DESKTOP | mkd | | 2023-12-16 12:10:15 | image | 0 | 1 | 0 | 161 | 2023-06-21 | ステアリングステムレンチ | https://donbura.hatenablog.com/entry/CBR250RR(MC22)のステムベアリング交換：調整 | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 0 | 1 | 0 | 74 | 2023-06-22 | blender アバター作成 | https://donbura.hatenablog.com/entry/ClusterのアバターをBlender_3.2で作ってみた | DESKTOP | jpn | | 2023-12-16 12:10:15 | image | 0 | 1 | 0 | 119 | 2023-06-25 | cbr250rr フロントフォーク | https://donbura.hatenablog.com/entry/CBR250RR(MC22)のフロントフォークのオーバーホール：組み立 | MOBILE | jpn | +---------------------+-------+--------+-------------+------+----------+------------+--------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------+---------+ 10 rows in set (0.01 sec) mysql> select * from basic_hatena where type = "video" limit 10; Empty set (0.01 sec) mysql> select * from basic_hatena where type = "news" limit 10; Empty set (0.01 sec)
query列とpage列はマスターテーブルに分ける方が良さそうな気がするけど、とりあえずこのまま行くことにする。
自動実行
rootで自動実行するためにrootにもpythonのパッケージをインストールした。
その上でsudo crontab -eで下記エントリを追加して毎日12:01に実行するようにした。
01 12 * * * python3 /work/google/searchConsole/searchConsole.py
以上で、最低限やりたかったことはできたはずなので、そのうちdbのUIを作りたい。
気が向いたら感想をお願いします。(ログイン不要、ボタンを押すだけです)
