クエリ ガイドラインとサンプルクエリ

一括データのエクスポートのクエリに関するベスト プラクティス

常に集計関数を使用する

テーブル内のデータは、日付、URL、サイト、または任意のキーの組み合わせで統合されるとは限りません。したがって、行の値が確実に統合されるように、SUM や COUNT などを使用して常にフィールドを集計する必要があります。たとえば、「太った猫」というクエリの行がテーブルに複数あるとします。

query     date       search_type   impressions ...
太った猫 10-10-2022      WEB          1000
太った猫 10-10-2022      WEB           200
太った猫 10-10-2022    IMAGE          5000

「太った猫」というクエリを特定の日付や検索タイプで検索するだけでも、複数の行になる可能性があります。そのため、インプレッションの合計を確認するには、次のように集計します。

/* よく使われるクエリを表示するための不適切な方法: クエリごとに 1 行のみを取得 */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
太った猫   5000

/* よく使われるクエリを表示するための正しい方法 */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* 匿名化されたクエリを除外 */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Result:
太った猫   6200

クエリを日付で制限して処理費用を抑える

BigQuery でクエリを実行すると、料金が請求されるだけでなく、テーブルが非常に大きくなる可能性があります。エクスポートされるテーブルは日付でパーティション分割されるため、入力スキャンの対象を目的の日のみに制限できます。そのため、大規模なサイトではクエリの費用を大幅に削減することが可能です。クエリの費用を最小限に抑えるには、WHERE 句を使用して日付パーティション分割テーブルの期間を制限することをおすすめします。

null のクエリ文字列を除外する

多くのサイトに対して最もよく使用されるクエリは、匿名化されたクエリです。匿名化されたクエリは、テーブルで長さが 0 の文字列として報告されます。特定の条件で最もよく使用されているクエリを確認したい場合は、長さがゼロのクエリ値を SQL クエリから除外することをおすすめします。

サンプルクエリ

データを効率的に扱うためのサンプルクエリを以下に示します。

サイト別の結果

すべてのウェブ統計（日単位、過去 2 週間）

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* 掲載順位がゼロベースのため、以下で 1 を追加 */
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date

ORDER BY date desc
  LIMIT 1000

米国でのクリック数が上位のモバイルウェブ クエリ（過去 2 週間）

SELECT
  query,
  device,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
    AND country = 'usa'
    AND device = 'MOBILE'
    AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000

URL 別の結果

クリック数が上位の Discover URL（特定の日）

SELECT
  url,
  sum(impressions),
  sum(clicks)
  FROM searchconsole.searchdata_url_impression
WHERE search_type = 'DISCOVER'
  AND data_date = DATE(2022, 02, 01)
GROUP BY url
ORDER BY 3
LIMIT 1000

よくある質問のリッチリザルトの合計クエリと URL の統計情報（過去 2 週間）

SELECT
  url,
  query,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_tpf_faq = true
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2 
ORDER BY clicks
LIMIT 1000

URL 別のすべてのトラフィック（過去 2 週間）

SELECT
  url,
  search_type,
  sum(impressions) as impressions,
  sum(clicks) as clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY 3 desc
LIMIT 1000

「your_brand」を含むクエリのインプレッション数（過去 2 週間）

SELECT
  regexp_contains(query, 'your_brand') AS contains_brand,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY 2 desc
LIMIT 1000