BigQueryへエクスポートされたSearch Consoleのデータを念のためバックアップする方法についての解説。基本的に誤った操作をしなけば消えることはなく、このようなSearch ConsoleやGA4のデータレイクをバックアップするかについては、個人的には不要だと考える。というのも、データの価値は薄れていくものであり、2年も3年も前のデータが必要になるかといえば、絶対になくなって困るほどの価値が高いものではないからだ。逆にいえば、価値が高いのであれば念のため、バックアップをとることを推奨するが、保管するにしても保管するためのストレージ料金がかかるため、その料金と天秤にかけることになる。
またSearch ConsoleのGA4のデータのどちらが価値が高いかといえば、どちらかといえばSearch Consoleの方だと思う。GA4のデータはサイト流入・回遊のデータであるため、ある程度の期間、1年程のデータがあれば傾向が掴めるし、それ以上の長い期間があってもデータの価値が累乗的に高まるわけではない。対してSearch Consoleのメインのデータは検索クエリであり、1年間分と5年間分の検索クエリのデータの価値では後者の方が価値が高まる。そのため長くデータを蓄積することに価値が出てくるSearch Consoleのデータの方がデータ活用的に価値が高い。
Search Consoleエクスポートデータのバックアップ方法
バックアップ手法は様々あり、ここでは最も単純にBigQuery上にテーブルをコピーする方法を解説し、随時必要性があれば他の方法についても追記していく。
BigQueryにSearch Consoleテーブルの複製を日次で作成
BigQueryにエクスポートされたSearch Consoleのテーブルを日次でテーブルをクリエイトして複製するため、単純な方法であるが、クエリ抽出量がテーブル規模によってはコスト面で考慮する必要性が出てくる点がデメリットとなる。ただ、テーブルサイズはGA4よりもかなりコンパクトであるため、小規模サイトであればこの方法でも問題ない。
BEGIN
DECLARE log_table_exists BOOL;
DECLARE url_row_count INT64;
DECLARE last_url_row_count INT64;
DECLARE site_row_count INT64;
DECLARE last_site_row_count INT64;
DECLARE next_log_id INT64;
DECLARE latest_url_status STRING;
DECLARE latest_site_status STRING;
SET log_table_exists = (
SELECT COUNT(1) > 0
FROM `BK_searchconsole.INFORMATION_SCHEMA.TABLES`
WHERE table_schema = 'BK_searchconsole'
AND table_name = 'BK_ExportLog'
);
IF log_table_exists = FALSE THEN
CREATE TABLE `BK_searchconsole.BK_ExportLog` (
log_id INT64,
backup_time STRING,
backup_status STRING,
table_name STRING,
row_count INT64,
error_message STRING
);
END IF;
SET latest_url_status = (
SELECT backup_status
FROM `BK_searchconsole.BK_ExportLog`
WHERE table_name = 'url'
ORDER BY log_id DESC
LIMIT 1
);
SET latest_site_status = (
SELECT backup_status
FROM `BK_searchconsole.BK_ExportLog`
WHERE table_name = 'site'
ORDER BY log_id DESC
LIMIT 1
);
IF latest_url_status = 'FAILED' OR latest_site_status = 'FAILED' THEN
INSERT INTO `BK_searchconsole.BK_ExportLog`
(log_id, backup_time, backup_status, table_name, error_message)
VALUES (
(SELECT COALESCE(MAX(log_id), 0) + 1 FROM `BK_searchconsole.BK_ExportLog`),
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo'),
'FAILED',
'system',
'Backup process skipped: Previous backup failed for URL or Site.'
);
RETURN;
END IF;
SET next_log_id = (SELECT COALESCE(MAX(log_id), 0) + 1 FROM `BK_searchconsole.BK_ExportLog`);
SET last_url_row_count = (
SELECT COALESCE(row_count, 0)
FROM `BK_searchconsole.BK_ExportLog`
WHERE table_name = 'url'
ORDER BY log_id DESC
LIMIT 1
);
SET url_row_count = (SELECT COUNT(*) FROM `<project>.<dataset>.searchdata_url_impression`);
IF url_row_count < last_url_row_count THEN
INSERT INTO `BK_searchconsole.BK_ExportLog`
(log_id, backup_time, backup_status, table_name, row_count, error_message)
VALUES (
next_log_id,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo'),
'FAILED',
'url',
url_row_count,
'Data validation failed: URL impression data is below previous backup row count'
);
RETURN;
ELSE
DROP TABLE IF EXISTS `BK_searchconsole.BK_searchdata_url_impression`;
CREATE OR REPLACE TABLE `BK_searchconsole.BK_searchdata_url_impression`
PARTITION BY data_date AS
SELECT *
FROM `<project>.<dataset>.searchdata_url_impression`;
INSERT INTO `BK_searchconsole.BK_ExportLog`
(log_id, backup_time, backup_status, table_name, row_count)
VALUES (
next_log_id,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo'),
'SUCCESS',
'url',
url_row_count
);
END IF;
SET next_log_id = (SELECT COALESCE(MAX(log_id), 0) + 1 FROM `BK_searchconsole.BK_ExportLog`);
SET last_site_row_count = (
SELECT COALESCE(row_count, 0)
FROM `BK_searchconsole.BK_ExportLog`
WHERE table_name = 'site'
ORDER BY log_id DESC
LIMIT 1
);
SET site_row_count = (SELECT COUNT(*) FROM `<project>.<dataset>.searchdata_site_impression`);
IF site_row_count < last_site_row_count THEN
INSERT INTO `BK_searchconsole.BK_ExportLog`
(log_id, backup_time, backup_status, table_name, row_count, error_message)
VALUES (
next_log_id,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo'),
'FAILED',
'site',
site_row_count,
'Data validation failed: Site impression data is below previous backup row count'
);
RETURN;
ELSE
DROP TABLE IF EXISTS `BK_searchconsole.BK_searchdata_site_impression`;
CREATE OR REPLACE TABLE `BK_searchconsole.BK_searchdata_site_impression`
PARTITION BY data_date AS
SELECT *
FROM `<project>.<dataset>.searchdata_site_impression`;
INSERT INTO `BK_searchconsole.BK_ExportLog`
(log_id, backup_time, backup_status, table_name, row_count)
VALUES (
next_log_id,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo'),
'SUCCESS',
'site',
site_row_count
);
END IF;
END;
コード実行手順
事前にバックアップ先となるデータセットをBigQueryに作成しておくこと。
次に、コードをクエリのスケジューリングで日次実行の設定を行う。
※「クエリ結果の書き込み先」は指定不要
GA4のレポーティングする際には常に最新データが反映されていなければならない。またそれは「自動化」されていることも重要な要件となる。そうでなければ、静的なある一時のデータを可視化することにBIツールを使うことにあまり意味を持たなくなるためだ。 ここではBigQueryの「クエリのスケジューリ...
コード解説
複数のステートメントを一括で処理する必要があるためBigQueryのマルチステートメントクエリを使用している。マルチステートメントクエリの利点の1つとして、クエリのスケジューリングやDataformなどの機能を使わずとも1つのSQLクエリの中で複数のステートメントを処理できる利点がある。1つのクエリの中で処理できる内容のものであればマルチステートメントクエリを採用することが望ましい。
テーブルのバックアップは日次で総入れ替えを行うため、クエリコストが多少発生する。小規模サイトであれば許容範囲内のデータ抽出量に収まることが想定されるが、気になる場合は差分更新の方法を選択する必要がある。差分更新については必要性が出てくれば追記する。
エラーハンドリング
バックアップ対象のテーブルが欠損している場合にはバックアップ処理が止まる処理となる。バックアップ対象が欠損している状態でテーブルの複製を作成しても、バックアップのデータも欠損することになるため処理を止める必要がある。再度、バックアップ処理を再開させる場合は一旦、「BK_ExportLog」テーブルを削除する必要がある。
実行結果
指定したデータセット内にテーブルのレプリカと、エクスポートログが生成される。
補足
クエリ内容について、まだ修正したい点があるが100%満足できるラインまでもっていくのと、今回のテーブルの総入れ替えによるバックアップということを考慮すると、一旦これでこのケースでは完成としたい。必要性があれば差分更新によるバックアップによる更新を検討したい。