Skip to content

Digital Analytics Blog

von Marcus Stade

Menu
Menu
google-ads-dashboard_page

Google Ads – BigQuery Transfer: Mehr Möglichkeiten für Data-Studio Dashboards – Teil 2

Posted on 8. September 20198. September 2019 by mstade

Im ersten Teil der Serie wurde gezeigt, wie Google Ads Daten nach BigQuery exportiert und diese dann für einen Vergleich der CTR über verschiedene Zeiträume genutzt werden. Ein zweiter Use-Case ist die Überwachung des Quality Score per DataStudio.


Vor dem Lesen dieses Artikels sollte der erste Teil gelesen werden, da dort die einzelnen Schritte für Scheduled Queries, Anbindung an DataStudio, etc. gezeigt werden. Diese werden hin diesem Artikel nicht erneut behandelt.

google-ads-dashboard_page
Google Ads Dashboard zur Überwachung des Quality Scores

Wie im Bild zu sehen, sollen drei Graphen im Dashboard verwendet werden:

  1. Keywords bei denen sich der QualityScore geändert hat
  2. Die Anzahl der Veränderungen geteilt in Höher, Niedriger und Gleich
  3. Die Entwicklung des durchschnittlichen Quality Scores im Konto im Zeitverlauf

Für den dritten Graph kann direkt auf den BigQuery Table Criteria_XXXXX zugegriffen werden. Dieser enthält den Quality Score.

google-ads-dashboard_criteria_qs
Criteria_XXXXX Table in BigQuery

Mit der Anbindung des Tables in DataStudio über den BigQuery Connector kann direkt der Quality Score als Metrik mit der Aggregation AVG und das Datum in _DATA_DATE in einem Time Series Chart genutzt werden. Damit entsteht direkt die Grafik für den durchschnittlichen Quality Score.

googleads_bigquery_qs_timeseries_datastudio
DataStudio Settings für den QualityScore Verlauf Graph

Um das Datum im Dashboard auswählen zu können, kann ein Datepicker neben die Grafik platziert werden. Damit ist dieser Graph bereits fertig.

googleads_bigquery_qs_timeseries_graph

Graphen für QualityScore Änderungen

Für die beiden Quality Score Graphen, die die Änderungen anzeigen, werden die Daten wieder in BigQuery berechnet. Dabei wird der Wert des gestrigen Tages mit den denen sieben Tage davor verglichen. Mit dem Query erfolgt die Berechnung (Details zu den Queries in Teil 1):

SELECT a.Criteria as Keyword, a.QS_7 ,b.QS_1, b.QS_1 - a.QS_7 as diff, a._DATA_DATE FROM
(SELECT Criteria, Qualityscore as QS_7, _DATA_DATE FROM `<YourTable>` WHERE _DATA_DATE = DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) Group by Criteria,Qualityscore,_DATA_DATE) a
JOIN
(SELECT Criteria, Qualityscore as QS_1, _DATA_DATE FROM `<YourTable>` WHERE _DATA_DATE = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) Group by Criteria,Qualityscore, _DATA_DATE) b
ON a.Criteria = b.Criteria

Damit wird der folgende Table erstellt:

googleads_bigquery_qs_table
Schema des Table für die QualityScore Graphen

In der Spalte „diff“ ist nun die Veränderung des Qualityscores verfügbar. Um die Daten aktuell zu halten, sollte der Query als täglicher Scheduled Query mit der Option „Overwrite“ eingerichtet werden.

Dieser Table wird wieder als Datenquelle mit DataStudio verbunden und zur Dimension Keyword die Metriken QS_1 als „Neuer QS“ und QS_7 als „Alter QS“ verknüpft.

googleads_bigquery_qs_neu_alt
Einstellung für den Keyword-QualityScore Graph in DataStudio

Da nur die Keywords mit Veränderung berücksichtigt werden sollen, werden die Keywords mit einer Differenz von 0 in DataStudio herausgefiltert.

google-ads-dashboard_qs_kw
Filter in DataStudio, der Keywords ohne Veränderung im QualityScore verbirgt

Damit ist der Barchart für die Keywords mit Veränderung der QualityScores komplett.

Das zweite Barchart zur Anzeige, der Anzahl der Keyword die Veränderungen im QualityScore aufweisen, wird direkt in DataStudio auf Basis des bereits erstellten Tables QS berechnet. Hierfür wird ein Calculated Field erstellt, das auf Basis der Spalte „diff“ die Kategorien Höher, Gleich oder Niedriger zuordnet.

googleads_bigquery_qs_category
Calculated Field QS_Category in DataStudio
googleads_bigquery_qs_category_formula
Formeld zur Berechnung der Kategorie im Calculated Field QS_Category

Dieses Feld wird dann als Dimension mit der Metrik Record Count genutzt. Damit zeigt das Barchart die Anzahl der Einträge pro Option des berechneten Feldes an.

googleads_bigquery_qs_category_barchart
Das finale BarChart mit den Veränderungen des QualityScores anhand der Kategorien

BONUS: Lin Rodnitzsy Ratio

Auf der ersten Seite des Dashboards ist in der oberen rechten Ecke ein Scoreboard mit der Bezeichnung LR zu sehen. LR ist hier die Kurzform für die Lin Rodnitzsy Ratio. Diese gibt an, ob der Account sehr konservativ, mit moderaten Risiko oder sehr aggresiv gemanaged wird.

google-ads-dashboard_page1
Lin Rodnitzsy Ratio (LR) im DataStudio Dashboard

Die Formel hierzu ist sehr einfach und basiert auf den Search Terms. Die Kosten pro Conversions aller Search-Terms mit mindestens einer Conversion werden durch die Kosten pro Conversions aller Search-Terms geteilt.

googleads_bigquery_lr
Formel für die Lin Rodnitzsy Ratio

Im Allgemeinen wir ein Wert zwischen 1,5 und 2 als Ideal angesehen. Ein niedriger Wert bedeutet das Konto wird zu konservativ, darüber zu aggressiv gemanaged. Diese Werte sind nur Richtwerte und können sicherlich nicht verallgemeinert werden. Zudem sollte der Zeitraum für die Betrachtung mindestens zwei Wochen, besser zwei Monate betragen.

Die Search Terms finden sich im Table SearchQueryStats_XXXXX, ebenso die damit verbunden Kosten.

googleads_bigquery_lr_searchquerystats
BigQuery Table mit den SearchQueryStats

Mit folgenden Query kann diese Berechnung für die letzten 14 Tage erfolgen:

SELECT SAFE_DIVIDE(SUM(a.c1),1000000) as Cost_allConv, SAFE_DIVIDE(SUM(b.c2),1000000) as Cost_trueConv, SAFE_DIVIDE(SAFE_DIVIDE(SUM(a.c1), SUM(a.co1)),1000000) as CPA_allConv, SAFE_DIVIDE(SAFE_DIVIDE (SUM(c2), SUM(co2)),1000000) as CPA_TrueConv, SAFE_DIVIDE(SAFE_DIVIDE(SUM(a.c1), SUM(a.co1)), SAFE_DIVIDE (SUM(c2), SUM(co2))) as LR 

FROM

(SELECT Query, SUM(Cost) as c1, SUM(Conversions) as co1 FROM `<YourTable>.SearchQueryStats_XXXXX` WHERE _DATA_DATE >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 14 DAY) GROUP BY Query) a

LEFT JOIN

(SELECT Query, SUM(Cost) as c2, SUM(Conversions) as co2 FROM `<YourTable>.SearchQueryStats_XXXXX` WHERE Conversions !=0 AND _DATA_DATE >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 14 DAY) GROUP BY Query) b
ON a.Query = b.Query

Die Division durch 1000000 ist bedingt durch das Format, indem Ads Kosten BigQuery abgelegt werden. Mit der Division wird dieses in das gebräuchliche Format überführt.

googleads_bigquery_kostenformat
Google Ads Kostenformat in BigQuery

Der Table wird wieder mit DataStudio verbunden und LR als Dimension im Scoreboard eingefügt.

Fazit

Mit den BigQuery Export von Google Ads Daten können einfach Dashboards in DataStudio erstellt werden, deren Funktionalität nicht über den Standard-Connector Google Ads – DataStudio abgebildet werden kann. Selbstverständlich können nicht nur die Daten für Reporting sondern ebenfalls für Forecasting genutzt werden. Das wird Thema in Teil 3 behandelt.

Keep in Touch

Supporter: InPignus GmbH

ImPignus

mohrstade

mohrstade.de

Analytics Pioneers

Schlagwörter

App+Web App-Tracking Basic Big Query Customer Data Platform DataQuality DataStudio Events Google Analytics für Firebase ITP Machine Learning Property PWA Quick-Tipp User-Journey Wordpress Überblick

Podcast Empfehlung:

beyond pageview

Neueste Beiträge

  • Tag Management Platform: Commanders Act Platform X – DataCollection (Teil 2)
  • Tag Management Platform: Übersicht (Teil 1)
  • IP und User Agent Identifier: Nachteile
  • GA4 Recipes: Machine Learning Features für Predictive Audiences in anderen Tools nutzen, am Beispiel von Tealium AudienceStream
  • Quick Tipp: GA4 Configuration Tag vs. Universal Analytics Setting Variable – Sequence matters

Kategorien

  • Allgemein
  • Cloud
  • Dashboard
  • Firebase Analytics
  • Google Analytics
  • Google Analytics 4 (App+Web)
  • Google Optimize
  • Google Tag Manager
  • Machine Learning
  • Matomo
  • Tag Management

Links

  • Datenschutzerklärung
  • Hear Me Speak
  • Impressum
  • Meet & Eat
  • Über den Blog
©2025 Digital Analytics Blog | Built using WordPress and Responsive Blogily theme by Superb