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

Gegen eine geringe Gebühr können Google Ads zu BigQuery Transfers eingerichtet werden. In BigQuery werden dann täglich die Daten aus Google Ads abgelegt. Das Interessante daran: Es werden nicht die Daten aus dem User-Interface von Google Ads exportiert, sondern von der API Version v201809.

Das eignet sich hervorragend für DataStudio Dashboards, da vielleicht nicht alle Daten, die im Dashboard genutzt werden sollen, über den eingebauten Connector Google Ads zu DataStudio abgebildet werden könnne.

Im ersten Teil ist der Use-Case, einen CTR Vergleich pro Keyword für die letzten 3, 7, 14 und 28 Tage.

google-ads-dashboard_page1 — Die Ansicht des finales Dashboards für den Use-Case

Google Ads BigQuery Transfer einrichten

Die Einrichtung des Transfers ist sehr einfach. Im BigQuery User Interface kann unter Transfers

der Google Ads Transfer konfiguriert werden.

Für den Export der Daten fallen Gebühren an, die dem Google Cloud Platform Billing Account zugeordnet werden.

Pro Google Ads Accounts werden dann in BigQuery Tables angelegt und täglich aktualisiert. Die Tables beziehen sich auf ein Kriterium (Campaign, Keyword, Ad, etc.) und die passenden Statistiken. Hier ein Beispiel der angelegten Tables für das Kriterum Keyword:

Die Tables stehen auch als partinionierter Table nach Tag zur Verfügung (Prefix p_). Als Suffix wird die Google Ads Account ID verwendet.

Je nach Table sind Spalten entsprechend der Google Ads API Version v201809 verfügbar.

Google Ads Data Studio Dashboards

Mit diesen Daten lässt sich natürlich intensiver arbeiten als mit den Daten aus dem User Interface, zum Beispiel beim Dashboarding mit Data Studio. Beginnen wir dem Use-Case: Die Keyword Click-Though-Rate (CTR) über mehrere Zeiträume (letzte 3, 7, 14, 28 Tage) vergleichen in einer Data Studio Tabelle.

Data Studio kann per eingebauten Connector direct auf BigQuery Tables zugreifen. Daher sieht der Workflow wie folgt aus:

Die CTR berechnet sich aus Clicks / Impressions. Beide Werte finden wir im Table KeywordBasicStats_:

googleads_bigquery_keyword_ctr — Die KeywordBasicStats Tabelle enthält Clicks, Date und Impressions

Da Zeiträume verglichen werden sollen, benötigen wir auch noch das Feld Date. Das Keyword selbst wird über CriterionId identifzierbar, die die ID des Keywords wiedergibt.

googleads_bigquery_keywordbasicstats_criterionid

Da eine ID im DataStudio Report nicht sehr nutzerfreundlich ist, muss die ID noch durch das Keyword ersetzt werden. Im Table Keyword_ ist die CriterionId dem Keyword, im Table Criteria, zugeordnet werden.

googleads_bigquery_criterionid_criteria_keywords — Keyword Table

Ein einfacher Workflow, der in ein SQL Statement gepackt wird. Zuerst wird die CriterionId, sowie die Summe der Clicks und der Impression aus der Tabelle geholt und damit die CTR berechet. Für die Daten der letzten 3 Tage ist folgendes SQL Statement in BigQuery zielführend:

SELECT Sum(Clicks) as Summe_Clicks_3d, Sum(Impressions) as Summe_Impressions_3d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))*100 as CTR_3d, CriterionId as CID_3d FROM `<YourTable>` WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 3 DAY) Group by CriterionId

Betrachten wir die Elemente des Statements einzeln:

Group by CriterionId

Die Daten werden einzeln pro CriterionID und damit pro Keyword ausgegeben

WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 3 DAY)
Vom aktuellen Datum wird ein Tag abgezogen (= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), um Daten erst ab dem gestrigen Tag zu erhalten. Dann werden von diesem Datum 3 weitere Tage abgezogen, (DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 3 DAY) um die richtige Zeitspanne zu erhalten. (Alternativ hätte dies auch in einer DATE_SUB Funktion kombiniert werden können, zum leichteren Verständnis habe ich hier darauf verzichtet). Nur die Impression und Click-Daten ab diesem Zeitpunkt werden damit betrachtet

SELECT Sum(Clicks) as Summe_Clicks_3d, Sum(Impressions) as Summe_Impressions_3d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))100 as CTR_3d, CriterionId as CID_3d

gibt an, welche Daten weiterverwendet werden sollen: Die Summe der Clicks, die Summe der Impressions, die CTR (Berechnet mit SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))100) und die CriterionID. SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))*100 dividiert die Clicks durch die Impressions, auch wenn Impressions den Wert 0 hat. In diesem Fall würde die Ausführung mit einem Fehler (Divided by Zero) verhindert und der Wert null würde gesetzt.

Dieses Statement wird nun mit den Daten für die anderen Zeiträume verknüpft. CriterionID ist das bei das verbindende Element:

(SELECT Sum(Clicks) as Summe_Clicks_3d, Sum(Impressions) as Summe_Impressions_3d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))100 as CTR_3d,CriterionId as CID_3d FROM <YourTable>.KeywordBasicStats_XXXXX WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 3 DAY) Group by CriterionId ) a JOIN (SELECT Sum(Clicks) as Summe_Clicks_7d, Sum(Impressions) as Summe_Impressions_7d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))100 as CTR_7d,CriterionId as CID_7d FROM <YourTable>.KeywordBasicStats_XXXXX WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 7 DAY) Group by CriterionId) b
 ON a.CID_3d = b.CID_7d
 JOIN
 (SELECT Sum(Clicks) as Summe_Clicks_14d, Sum(Impressions) as Summe_Impressions_14d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))100 as CTR_14d,CriterionId as CID_14d FROM <YourTable>.KeywordBasicStats_XXXXX WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 14 DAY) Group by CriterionId) c ON a.CID_3d = c.CID_14d JOIN (SELECT Sum(Clicks) as Summe_Clicks_28d, Sum(Impressions) as Summe_Impressions_28d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))100 as CTR_28d,CriterionId as CID_28d FROM <YourTable>.KeywordBasicStats_XXXXX WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 28 DAY) Group by CriterionId) d
 ON a.CID_3d = d.CID_28d

In a, b, c und d werden dabei die Ergebnisse des SELECTS gespeicherts. Nun muss nur noch das Keywords auf die CriterionId gematched werden und die Differenz der CTRs berechnet werden.

Das Gesamt SQL Statement:

SELECT e, a,b,c,d, (1-SAFE_DIVIDE(a.CTR_3d,b.CTR_7d))*100 as CTRDIFF3_7, (1-SAFE_DIVIDE(b.CTR_7d,c.CTR_14d))*100 as CTRDIFF7_14, (1-SAFE_DIVIDE(c.CTR_14d,d.CTR_28d))*100 as CTRDIFF14_28 FROM 
(SELECT Sum(Clicks) as Summe_Clicks_3d, Sum(Impressions) as Summe_Impressions_3d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))*100 as CTR_3d,CriterionId as CID_3d FROM `<YourTable>.KeywordBasicStats_XXXXX` WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 3 DAY) Group by CriterionId ) a
JOIN
(SELECT Sum(Clicks) as Summe_Clicks_7d, Sum(Impressions) as Summe_Impressions_7d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))*100 as CTR_7d,CriterionId as CID_7d FROM `<YourTable>.KeywordBasicStats_XXXXX` WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 7 DAY) Group by CriterionId) b
ON a.CID_3d = b.CID_7d
JOIN
(SELECT Sum(Clicks) as Summe_Clicks_14d, Sum(Impressions) as Summe_Impressions_14d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))*100 as CTR_14d,CriterionId as CID_14d FROM `<YourTable>.KeywordBasicStats_XXXXX` WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 14 DAY) Group by CriterionId) c
ON a.CID_3d = c.CID_14d
JOIN
(SELECT Sum(Clicks) as Summe_Clicks_28d, Sum(Impressions) as Summe_Impressions_28d, SAFE_DIVIDE(Sum(Clicks),Sum(Impressions))*100 as CTR_28d,CriterionId as CID_28d FROM `<YourTable>.KeywordBasicStats_XXXXX` WHERE Date >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 28 DAY) Group by CriterionId) d
ON a.CID_3d = d.CID_28d
JOIN
(SELECT Criteria , CriterionId FROM `<YourTable>.KeywordBasicStats_XXXXX` Group by Criteria, CriterionId) e
ON a.CID_3d = e.CriterionId

In e wird die das Keyword der CriterionId zugeordent. Dann kann die Abfrage im äußersten Select erfolgen und die Differenz der CTR.

Ausgabe als BigQuery Table sieht nun so aus:

googleads_bigquery_result_table — Ausschnitt des BigQuery Tables nach dem SQL Statement

Um eine tägliche Ausführung des SQL Statements einzurichten, kann der Query als Scheduled Query eingerichtet werden:

googleads_bigquery_scheduledquery — Scheduled Query Funktion in BigQuery

Die Location (US, EU, etc.) des Datasets der Google Ads Daten und des Ziel Datasets muss den gleichen Wert besitzen. Als Destination table write preference ist Overwrite für diesen Anwendungsfall sinnvoll.

googleads_bigquery_scheduledquery_config — Scheduled Query Config Menu

Unterhalb des Queries wird dann eine Infoboy angezeigt:

googleads_bigquery_scheduledquery_infobox — BigQuery Scheduled Query Infobox

Data Studio Report

Nun kann in Data Studio der Connector für BigQuery gewählt werden.

googleads_bigquery_datastudio_connector — Datastudio Connector zu BigQuery

Alle Spalten aus dem Table sind nun in Data Studio verfügbar.

googleads_bigquery_datastudio_fields — Datafields aus dem Table in BigQuery

und können in Tabelle genutzt werden:

Eine Tabelle mit dem Vergleich der CTRs sieht dann folgendermaßen aus

CTR für 3,7, 14 und 28 Tage sowie die prozentuale Veränderung innerhalb der Zeiträume

Mit dem Google Ads Daten in BigQuery lassen sich natürlich noch viel mächtigere Reports erstellen. Ein Report wird Gegenstand des nächsten Beitrags dieser Serie.