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.
Wie im Bild zu sehen, sollen drei Graphen im Dashboard verwendet werden:
- Keywords bei denen sich der QualityScore geändert hat
- Die Anzahl der Veränderungen geteilt in Höher, Niedriger und Gleich
- 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.
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.
Um das Datum im Dashboard auswählen zu können, kann ein Datepicker neben die Grafik platziert werden. Damit ist dieser Graph bereits fertig.
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.CriteriaDamit wird der folgende Table erstellt:
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.
Da nur die Keywords mit Veränderung berücksichtigt werden sollen, werden die Keywords mit einer Differenz von 0 in DataStudio herausgefiltert.
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.
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.
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.
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.
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.
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.QueryDie 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.
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.