Abfragerichtlinien und Beispielabfragen

Best Practices für Abfragen für Bulk-Datenexporte

Aggregatfunktionen immer verwenden

Die Daten in den Tabellen können nicht in jedem Fall nach Datum, URL, Website oder einer beliebigen Kombination von Schlüsseln konsolidiert werden. Daher sollten Sie Ihre Felder (SUMME, ANZAHL usw.) immer zusammenfassen, damit Ihre Zeilenwerte mit Sicherheit konsolidiert sind. Angenommen, eine Tabelle enthält mehrere Zeilen mit der Abfrage „Fette Katzen“:

query     date       search_type   impressions ...
Fette Katzen 10-10-2022      WEB          1000
Fette Katzen 10-10-2022      WEB           200
Fette Katzen 10-10-2022    IMAGE          5000

Die einfache Suche nach der Suchanfrage „Fette Katzen“ kann – selbst bei der Suche nach einem bestimmten Datum und Suchtyp – mehrere Zeilen zurückgeben. Daher sollten Sie wie nachstehend gezeigt zusammenfassen, um die Gesamtzahl der Impressionen zu sehen:

/* FALSCHE Methode zum Anzeigen der beliebtesten Suchanfrage: nur eine Zeile pro Abfrage wird zurückgegeben */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
Fette Katzen   5000

/* RICHTIGE Methode zum Anzeigen der beliebtesten Suchanfrage */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Anonymisierte Abfragen ausfiltern */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Result:
Fette Katzen   6200

Abfragen nach Datum begrenzen, um Verarbeitungskosten zu sparen

Wenn Sie eine Abfrage in BigQuery ausführen, werden Ihnen die Kosten dafür in Rechnung gestellt. Außerdem können Ihre Tabellen sehr groß werden. Die exportierten Tabellen sind datumspartitioniert, was eine effiziente Begrenzung des Eingabescans auf relevante Tage erlaubt. Bei großen Websites kann dies zu beträchtlichen Einsparungen bei den Abfragekosten führen. Zum Minimieren der Abfragekosten empfiehlt sich die Verwendung einer WHERE-Klausel, mit der der Zeitraum in der nach Datum partitionierten Tabelle eingegrenzt wird.

Null-Abfragestrings herausfiltern

Anonymisierte Suchanfragen stellen oft die häufigste Suchanfrage für eine Website dar. Eine anonymisierte Suchanfrage wird in der Tabelle als String mit Nulllänge angegeben. Wenn Sie die beliebteste Anfrage für bestimmte Kriterien sehen möchten, sollten Sie Anfragewerte mit Nulllänge aus Ihrer SQL-Abfrage herausfiltern.

Beispielabfragen

Im Folgenden finden Sie einige Beispielabfragen, die Ihnen den Einstieg erleichtern.

Ergebnisse nach Website

Alle Webstatistiken nach Tag für die letzten zwei Wochen

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Nachstehend wird 1 hinzuaddiert, weil die Positionszählung bei 0 beginnt */
  ((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

Top-Suchanfragen im mobilen Web in den USA nach Klicks für die letzten zwei Wochen

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

Ergebnisse nach URL

Häufigste Discover-URLs nach Klicks an einem bestimmten Tag

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

Gesamtzahl der Suchanfragen und URLs für Rich-Suchergebnisse für FAQ für die letzten zwei Wochen

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

Alle Zugriffe bei Ihnen nach URL für die letzten zwei Wochen

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

Impressionen für Abfragen, die „meine_marke“ enthalten, für die letzten zwei Wochen

SELECT
  regexp_contains(query, 'meine_marke') 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