Custom Queries in Google Data Studio

Im letzten Post haben wir die ersten Diagramme in unserem COVID-19 Dashboard erstellt. Unter anderem haben wir eine Tabelle erstellt, mit der wir alle bestätigten Fälle jedes Landes vergleichen können. Dabei fällt allerdings auf, dass die Werte etwas zu hoch zu sein scheinen.

Wir untersuchen die Tabelle und stellen fest, dass die Werte der Spalte confirmed die Gesamtzahl bestätigter Fälle zeigen. Mit der Standard-Gruppierung von Google Data Studio wird allerdings jeder Wert mehrfach aufaddiert. Dieses Problem können wir umgehen, indem wir die Gruppierung als maximum der Tabelle pro Land festlegen.

Allerdings scheint das immer noch nicht ganz richtig. Die USA haben doch deutlich mehr als 211.260 bestätigten Fälle!

Wir fragen diese Tabelle über BigQuery nochmal ab und sehen, dass die Daten aus den USA nach Bundesstaat aufgeteilt sind. Der Wert 211.260 bezieht sich also nur auf einen einzelnen Bundesstaat.

SELECT
  *
FROM `bigquery-public-data.covid19_jhu_csse_eu.summary`
WHERE country_region = 'US'
ORDER BY date DESC 

Wir haben verschiedene Möglichkeiten diese Daten trotzdem zu integrieren. Zum Beispiel könnten wir neue Felder in Data Studio erstellen oder aber den Datensatz als Custom Query anbinden.

Data Studio erlaubt es uns, eine neue View über das Interface zu erstellen, die nur in Data Studio benutzt wird. Das ist extrem nützlich, wenn wir eine Metrik nur in einem einzelnen Diagramm benötigen oder wir Daten über unterschiedliche Zeiträume vergleichen möchten.

Genau wie im ersten Artikel binden wir eine neue Datenquelle an, doch dieses Mal wählen wir die Option Custom Query (auf Deutsch Benutzerdefinierte Abfrage) anstatt Public Data-Set. Hier können wir die Spalten abfragen, die für uns relevant sind und alle Funktionen von BigQuery in Data Studio nutzen.

SELECT
  date,
  country_region,
  SUM(confirmed) AS confirmed,
  SUM(deaths) AS deaths,
  SUM(recovered) AS recovered,
  SUM(active) AS active
FROM `bigquery-public-data.covid19_jhu_csse_eu.summary`
GROUP BY date, country_region 

Für unser Zweck ist hier eine einfache Summe der Werte, gruppiert nach Tagen und Ländern völlig ausreichend.

Wir wollen aber vielleicht Custom Queries nutzen, um noch mehr Informationen über unsere Daten zu gewinnen. Zum Beispiel könnten wir weitere Berechnungen zum Anstieg der Fälle darstellen. Wie viele bestätigte Fälle gibt es im Vergleich zum Tag davor?

SELECT
  s.date,
  s.country_region,
  SUM(s.confirmed), s_prev.confirmed,
  SAFE_DIVIDE(SUM(s.confirmed), s_prev.confirmed) AS confirmed_growth,
FROM bigquery-public-data.covid19_jhu_csse_eu.summary s
LEFT JOIN (
  SELECT
    date,
    country_region,
    SUM(confirmed) as confirmed
  FROM bigquery-public-data.covid19_jhu_csse_eu.summary
  GROUP BY date, country_region) s_prev
  ON s.date = DATE_ADD(s_prev.date, INTERVAL 1 DAY)
  AND s.country_region = s_prev.country_region
  GROUP BY date, country_region, s_prev.confirmed 

Diese Abfrage führt einen Join durch, der es ermöglicht, die Fälle jedes Tages durch die Fälle des Tages davor zu dividieren. Der Sub-Select sorgt für eine deutlich schnellere Query, damit der Nutzer des Dashboards schneller seine Daten sieht.

Für Deutschland sieht dieses Diagramm so aus:

Obwohl Data Studio manchmal nicht so flexibel ist, wie wir es gern hätten, können wir mittels Custom Queries viele Beschränkungen umgehen. Wir können beispielsweise in einer einzelnen Tabelle Daten aus unterschiedlichen Zeiträumen vergleichen, oder Kampagnen-Scores auf Basis der aktuellen Performance berechnen. Jeder Use Case ist unterschiedlich, schreib uns eine Mail und wir finden zusammen eine geeignete Lösung!