Search Consoleエクスポートデータのバックアップ【BigQuery】

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に作成しておくこと。

参照:データセットの作成

次に、コードをクエリのスケジューリングで日次実行の設定を行う。
※「クエリ結果の書き込み先」は指定不要

コード解説

複数のステートメントを一括で処理する必要があるためBigQueryのマルチステートメントクエリを使用している。マルチステートメントクエリの利点の1つとして、クエリのスケジューリングやDataformなどの機能を使わずとも1つのSQLクエリの中で複数のステートメントを処理できる利点がある。1つのクエリの中で処理できる内容のものであればマルチステートメントクエリを採用することが望ましい。

テーブルのバックアップは日次で総入れ替えを行うため、クエリコストが多少発生する。小規模サイトであれば許容範囲内のデータ抽出量に収まることが想定されるが、気になる場合は差分更新の方法を選択する必要がある。差分更新については必要性が出てくれば追記する。

エラーハンドリング

バックアップ対象のテーブルが欠損している場合にはバックアップ処理が止まる処理となる。バックアップ対象が欠損している状態でテーブルの複製を作成しても、バックアップのデータも欠損することになるため処理を止める必要がある。再度、バックアップ処理を再開させる場合は一旦、「BK_ExportLog」テーブルを削除する必要がある。

実行結果

指定したデータセット内にテーブルのレプリカと、エクスポートログが生成される。

補足

クエリ内容について、まだ修正したい点があるが100%満足できるラインまでもっていくのと、今回のテーブルの総入れ替えによるバックアップということを考慮すると、一旦これでこのケースでは完成としたい。必要性があれば差分更新によるバックアップによる更新を検討したい。

関連記事

BigQueryのWITH句(CTE)とサブクエリの使い分け

BigQueryでJavaScript UDFを活用

BigQuery workflowsでGA4スキーマ履歴を管理

PAGE TOP