BigQuery + GSC: Abfragen, die deine Agentur nicht laufen laesst

Das Search-Console-Interface deckelt dich bei 1.000 Zeilen pro Report und aggregiert Daten so, dass mehr versteckt als gezeigt wird. Verbindest du GSC via Bulk Data Export mit BigQuery, kippt das Spiel: du bekommst Millionen Zeilen Impression, Query, Seite und Device, ohne Sampling. Die meisten Agenturen bleiben beim Default-Dashboard stehen und liefern einen Report mit Liniendiagramm. Das Problem ist nicht Datenmangel, sondern SQL-Mangel. Dieser Post liefert Abfragen, die in Produktion laufen und Fragen beantworten, die das Panel nicht kann: Welche Seiten verlieren Monat fuer Monat Impressionen bei Bottom-of-Funnel-Queries?

Vor jeder Abfrage: Setup. Aktiviere Bulk Data Export unter Settings > Bulk Data Export und richte ihn auf ein GCP-Projekt mit aktivem Billing. Die Tabellen, die zaehlen, sind searchdata_site_impression und searchdata_url_impression. Die erste haelt anonymisierte gefilterte Queries und eignet sich fuer aggregierte Trends; die zweite bietet URL-Granularitaet. Kosten? Eine Site mit 5M Impressionen/Monat erzeugt rund 200 MB/Tag, was bei sauberen On-Demand-Queries unter einem Dollar pro Monat bleibt. Bevor du in SQL eintauchst, stell sicher, dass dein On-Page-Tracking sauber ist, wie On-Page-SEO ohne Vermutungen pruefen: ein datenbasiertes Audit zeigt, sonst korrelierst du Rauschen.

Abfrage 1: Content Decay mit 28-Tage-Rolling-Window. Nutze LAG() ueber eine woechentliche Aggregation pro URL, um Seiten zu erkennen, die mehr als 30% an Klicks gegenueber dem vorigen Fenster verloren haben, gefiltert auf mindestens 100 Klicks/Woche, um Rauschen zu kappen. Das Ergebnis ist eine priorisierte Rewrite-Liste, unendlich besser, als das Dashboard zu oeffnen und auf rote Pfeile zu starren. Verknuepfe mit dem Sheet aus Content Decay: Posts erkennen, die heimlich Traffic verlieren und du hast bereits Backlog fuers Quartal. Um zwischen Umschreiben und Neuaufbau zu entscheiden, liefert Neuschreiben oder neubauen: Entscheidung anhand von SERP-Daten das SERP-Daten-Framework, das direkt in diese Pipeline passt.

Abfrage 2: Striking-Distance-Keywords auf Position 8-20 mit CTR unter Benchmark. Joine searchdata_url_impression mit einer CTE aus Positions-Benchmarks (nimm die Zahlen aus CTR-Benchmark nach Position: aktualisierte Daten 2026 oder rechne aus der eigenen Site). Wenn eine URL bei Position 11 mit 0,8% CTR auftaucht und der Benchmark bei 2,1% liegt, hast du ein Title- oder Meta-Description-Problem, kein Ranking-Problem. Das ist die Abfrage, die den Berater allein bezahlt: sie liefert eine Liste von 50-200 Seiten, bei denen das Aendern eines Titles in einem Sprint im Folgemonat Klicks bringt. Kombiniere mit den Patterns aus Title-Tags, die konvertieren: 7 in echten SERPs getestete Muster.

Abfrage 3: echte Kannibalisierung, nicht die von Ahrefs. Gruppiere nach Query und zaehle distinkte URLs mit mehr als 10 Impressionen in derselben Woche. Erscheint eine Query mit 3+ URLs, hast du tatsaechliche Kannibalisierung (mit Nachfragesignal), nicht nur zwei Seiten, die sich ein Wort teilen. Exportiere als CSV und verschneide mit den echten Canonical-Tags aus einem Screaming-Frog-Crawl. Zwanzig Prozent der Faelle sind falsch gesetzte Canonicals, siehe Canonical Tags: haeufige Fehler, die organischen Traffic ausbluten. Der Rest ist redaktionelle Entscheidung: konsolidieren, 301 weiterleiten oder eine der Seiten auf eine andere Intention umpositionieren.

Abfrage 4: Branded vs. Non-Branded mit REGEXP_CONTAINS. Baue eine berechnete Spalte, die eine Query als Brand klassifiziert, wenn sie auf dein Marken-Regex matcht (typische Tippfehler einschliessen). Gruppiere monatlich und du siehst, was viele Agenturen verstecken: 60% des organischen Traffics vieler Kunden ist Brand Search, also nicht durch SEO gebracht. Diese Abfrage rettet Beziehungen und rechtfertigt ehrliche Investitionen, im Einklang mit Ehrliche SEO-KPIs: jenseits von Ranking und Traffic und SEO-Attribution: ROI ohne Last-Click belegen. Zeig sie dem CFO, bevor er fragt.

Abfrage 5: Intent-Mismatch ueber Query-Patterns. Klassifiziere Queries nach Intention (informational, transactional, navigational, vergleichend) mit CASE WHEN ueber einfache Regex ("wie", "beste", "preis", "vs", "test"). Verschneide mit dem Seitentyp (PLP, PDP, Blog, Home) und du findest Produktseiten, die fuer informationelle Queries ranken oder umgekehrt. Das ist Gold fuers E-Commerce, wie On-Page fur E-Commerce: PLP vs PDP ohne Kannibalisierung ausfuehrt. Um die Klassifikation zu fundieren, liefert Suchintention: 4 Typen und wie man sie in der SERP mappt die Taxonomie, die ich nutze.

Abfrage 6: Page-Query-Fingerprint zur Erkennung von Algo-Updates. Snapshotte die Top-20-Queries pro URL woechentlich. Aendert sich der Satz um mehr als 40% in einer Woche ohne On-Page-Aenderungen, hat Google die Seite neu interpretiert. Das siehst du nur mit BigQuery, das Dashboard wird es dir nie sagen. Praktisches Takeaway: plane diese sechs Abfragen in BigQuery Scheduled Queries, materialisiere sie in Wochentabellen, haeng Looker Studio drauf und du hast ein Observatorium fuer unter 5 Dollar/Monat, das jede 500-Dollar-Plattform schlaegt. SQL ist kein Luxus grosser Agenturen, sondern der Boden fuer alle, die SEO ernst nehmen.