Seit 1999: 27 Jahre IT & Online

08062 / 72 61 210 info@rsc24.de EN

Data Studio Reports: Ladezeiten deutlich verkürzen

Langsame Reports mit vielen Kanälen und großen Datenmengen? Mit einer vorbereiteten Datenquelle in BigQuery laden sie in Sekunden.

Veröffentlicht am · aktualisiert am · Reporting · von

Datenquellen fließen über BigQuery in eine flache Tabelle, daraus lädt ein Data-Studio-Dashboard in zwei Sekunden

Mit zunehmender Komplexität geht Data Studio in die Knie und bedankt sich mit langen Ladezeiten. Ein Multichannel-Reporting umfasst schnell mal 110 Diagramme und 48 Filter auf 13 Seiten. Daran hat sich auch mit der Rückbenennung von Looker Studio in Data Studio nichts geändert. Wer bei jedem Seitenwechsel zehn Sekunden auf drehende Kreise schaut, verliert die Lust am Reporting.

Die gute Nachricht: Es geht auch in wenigen Sekunden, selbst mit Hunderttausenden Produkten und Millionen Datenpunkten. Die lange Ladezeit entsteht vor allem, weil Data Studio Daten selbst zusammenführen, aggregieren und berechnen muss. Bereitest du die Daten vorher in BigQuery auf, entfällt dieser Schritt fast vollständig.

Wie sieht eine optimierte Datenquelle aus?

Ziel ist eine einzige, möglichst flache Datenquelle. Aus ihr zieht Data Studio die Daten für alle Diagramme und Filter, ohne sie erst mit anderen Quellen zu verknüpfen oder aufwendige berechnete Felder auszuwerten.

Überleg dir deshalb zuerst, welche Kennzahlen du zeigen willst und nach welchen Dimensionen du sie filtern oder aufteilen möchtest. In unserem Beispiel:

Damit du bei Zeiträumen, Vergleichszeiträumen und Darstellungen nach Woche oder Monat flexibel bleibst, brauchst du die Daten auf Tagesbasis. Die fertige Tabelle hat dann diese Spalten:

datum | kanal | unterkanal | besuche | bestellungen | neukunden | umsatz | kosten

Die berechneten Felder fehlen hier bewusst, dazu später mehr.

Woher kommen die Daten?

Besuche, Bestellungen, Neukunden und Umsatz je Datum und Kampagne kommen idealerweise aus deinem Webanalyse-Tool. Für Google Analytics 4 gibt es den kostenlosen BigQuery-Export, der die Rohdaten täglich direkt in BigQuery schreibt. Alternativ rufen Tools wie Supermetrics die Daten automatisiert ab und speichern sie in einem Google Sheet oder direkt in BigQuery.

Die Kosten stammen von den Werbeplattformen. In unserem Beispiel kommen sie von Google Ads, Microsoft Advertising, Meta und drei Preisvergleichsportalen. Für Google Ads gibt es den BigQuery Data Transfer Service, für die anderen großen Plattformen Tools wie Supermetrics. Bei den Preisvergleichern musst du vermutlich improvisieren: Der erste schickt die Daten als E-Mail-Anhang, der zweite lädt sie per FTP auf deinen Server und der dritte bietet eine API. Spätestens hier wechseln viele zur Handarbeit. Wir automatisieren das mit einem kleinen Bash-Script und Google Apps Script, Details im Anhang am Ende.

Die Daten in Form bringen

Die Kosten müssen später anhand von Datum und Kampagne den richtigen Zeilen aus der Webanalyse zugeordnet werden. Dabei hilft eine einheitliche Benennung aller Kampagnen oder ein durchgängiges Tracking mit URL-Parametern.

In unserem Beispiel nutzen wir vom Kunden definierte Kampagnen-Codes, die in allen Kampagnen als URL-Parameter angehängt werden. Über die Codes und eine Matching-Tabelle ordnen wir sowohl Kosten als auch Webanalyse-Daten eindeutig einem Kanal und Unterkanal zu. Als Zwischenstufe gibt es also drei Arten von Rohdaten:

Webanalyse:        datum | kampagnen_code | besuche | bestellungen | neukunden | umsatz
Kosten je Quelle:  datum | kampagnen_code | kosten
Matching-Tabelle:  kampagnen_code | kanal | unterkanal

Liegen die Daten so vor, ist es mit BigQuery relativ einfach, sie zu einer flachen Datenquelle zu kombinieren.

Vorbereitung

Du brauchst ein Google-Cloud-Projekt mit BigQuery und darin ein Dataset, zum Beispiel reporting. Zum Ausprobieren reicht die kostenlose BigQuery-Sandbox. Einen Überblick über Abfragen in BigQuery gibt die Einführung in BigQuery-Analysen.

Daten aus Google Sheets in BigQuery einbinden

Daten, die in einem Google Sheet landen, bindest du als externe Tabelle ein. In BigQuery legst du dazu im Dataset eine neue Tabelle an:

  1. Quelle Drive, Dateiformat Google Sheets, dazu die URL des Sheets.
  2. Als Ziel einen sprechenden Tabellennamen, zum Beispiel import_webanalyse.
  3. Das Schema legst du von Hand fest, mit klaren Feldnamen wie datum, kampagnen_code oder besuche und den passenden Datentypen. Das macht die SQL-Abfragen später lesbarer.
  4. Unter den erweiterten Optionen überspringst du die Kopfzeile des Sheets.

Mit „Tabelle erstellen“ ist das Sheet verknüpft. Das wiederholst du für jedes Sheet.

Bei jeder Abfrage liest BigQuery die Daten frisch aus dem Sheet. Bei großen Datenmengen bremst das. Nutze die Sheets deshalb besser als Zwischenspeicher: Eine geplante Abfrage hängt die neuen Zeilen regelmäßig an eine interne BigQuery-Tabelle an. Diese Methode funktioniert sehr gut für alles, was Tools automatisiert abrufen oder was in einem festen Format ankommt.

Die Datenquelle für Data Studio erstellen

Mit SQL führst du die Daten nicht nur zusammen, sondern kannst sie dabei auch umrechnen oder bereinigen. Eine Abfrage über sechs Kostenquellen und eine Matching-Tabelle ist sauber formatiert schnell über 100 Zeilen lang. Das Prinzip zeigt diese gekürzte Fassung:

CREATE OR REPLACE TABLE `projekt.reporting.report_daten`
PARTITION BY datum AS

WITH
kosten_google AS (
  SELECT datum, kampagnen_code, kosten
  FROM `projekt.reporting.kosten_google_ads`
),
kosten_meta AS (
  -- Der Code steckt im Kampagnennamen, z. B. "Herbst-Sale [K123]"
  SELECT
    datum,
    REGEXP_EXTRACT(kampagne, r'\[(K\d+)\]') AS kampagnen_code,
    ausgaben AS kosten
  FROM `projekt.reporting.kosten_meta`
),
kosten AS (
  SELECT * FROM kosten_google
  UNION ALL
  SELECT * FROM kosten_meta
),
zeilen AS (
  SELECT datum, kampagnen_code, besuche, bestellungen, neukunden, umsatz, 0 AS kosten
  FROM `projekt.reporting.webanalyse`
  UNION ALL
  SELECT datum, kampagnen_code, 0, 0, 0, 0, kosten
  FROM kosten
)

SELECT
  z.datum,
  IFNULL(m.kanal, 'Nicht zugeordnet') AS kanal,
  IFNULL(m.unterkanal, 'Nicht zugeordnet') AS unterkanal,
  SUM(z.besuche) AS besuche,
  SUM(z.bestellungen) AS bestellungen,
  SUM(z.neukunden) AS neukunden,
  SUM(z.umsatz) AS umsatz,
  SUM(z.kosten) AS kosten
FROM zeilen AS z
LEFT JOIN `projekt.reporting.matching_kampagnen` AS m
  ON z.kampagnen_code = m.kampagnen_code
GROUP BY datum, kanal, unterkanal

Was hier passiert:

Statt CREATE OR REPLACE TABLE kannst du das Ergebnis auch über die Abfrageeinstellungen im BigQuery-Editor in eine Zieltabelle schreiben. Aktuell hältst du die Tabelle mit einer geplanten Abfrage, die täglich läuft.

Datenquelle in Data Studio einbinden

In Data Studio fügst du die Tabelle über „Daten hinzufügen“ mit dem BigQuery-Connector hinzu: Projekt, Dataset und die Tabelle report_daten auswählen. Ab hier baust du deinen Report wie gewohnt, nur ohne verknüpfte Datenquellen und mit deutlich schnelleren Seiten.

Die berechneten Felder legst du direkt in der Data-Studio-Datenquelle an, zum Beispiel:

Konversionsrate        = SUM(bestellungen) / SUM(besuche)
Kosten-Umsatz-Relation = SUM(kosten) / SUM(umsatz)
Neukundenquote         = SUM(neukunden) / SUM(bestellungen)

Warum nicht schon in SQL? Quoten lassen sich nicht einfach summieren. Rechnest du die Konversionsrate pro Tag in BigQuery aus, zeigt Data Studio für einen Monat schnell einen falschen Durchschnitt. Mit SUM() im Zähler und Nenner stimmt der Wert für jeden Zeitraum und jeden Filter.

Noch mehr Tempo

Reicht das nicht, gibt es zwei weitere Stellschrauben:

Darauf kannst du zählen

Was die Optimierung bringt, zeigt sich vor allem bei umfangreichen Reports. Eines unserer Projekte besteht aus:

Im gesamten Reporting gibt es nur sechs berechnete Felder und keine einzige zusammengeführte Datenquelle. Die Seiten laden spürbar schneller als ein vergleichbares Reporting mit direkter Anbindung von Google Analytics und Google Ads.

Anhang: Sonderfälle automatisieren

Manche Daten lassen sich nicht über Tools abrufen. Dann gilt es zwei Aufgaben zu lösen: die Daten automatisch empfangen und sie in eine einheitliche Form bringen. Erschwerend kommt oft hinzu, dass gleichartige Quellen völlig unterschiedlich ankommen.

In unserem Beispiel schickt der erste Preisvergleicher die Kosten pro Tag inklusive Impressionen und Klicks per E-Mail. Der zweite lädt per FTP eine Datei mit einer Zeile pro Klick hoch, ohne Kosten. Der dritte bietet eine REST-API für Klicks pro Tag, aber ohne Impressionen. Am Ende soll ein Google Sheet mit dieser Kopfzeile entstehen:

datum | preisvergleicher | impressionen | klicks | kosten

Dafür nutzen wir Google Apps Script, weil es sehr einfach mit Gmail und Google Sheets zusammenarbeitet. Eine Variante liest E-Mail-Anhänge aus einem eigenen Reporting-Postfach, die andere fragt APIs ab. Beide schreiben über dieselbe Funktion ins gemeinsame Sheet. Die FTP-Dateien schickt ein Bash-Script ebenfalls ans Reporting-Postfach. Das fertige Sheet bindest du wie oben beschrieben in BigQuery ein.

Die folgenden Beispiele sind bewusst schlank gehalten. Passe Spalten, Trennzeichen und Suchbegriffe an deine Quellen an.

Ins Google Sheet schreiben und Dubletten vermeiden

Neue Zeilen sammeln wir in output und schreiben sie am Ende in einem Rutsch ins Sheet. Vorher prüfen wir, ob es für Datum und Preisvergleicher schon eine Zeile gibt.

const FILE_ID = 'DEINE-SHEET-ID';
const SHEET_NAME = 'kosten_preisvergleicher';
const TIMEZONE = 'Europe/Berlin';
const output = [];
let existingKeys = null;

function addRow(date, provider, impressions, clicks, cost) {
  const key = date + '|' + provider;
  if (getExistingKeys().has(key)) {
    return;
  }
  existingKeys.add(key);
  output.push([date, provider, impressions, clicks, cost]);
}

function getExistingKeys() {
  if (existingKeys === null) {
    const sheet = SpreadsheetApp.openById(FILE_ID).getSheetByName(SHEET_NAME);
    const lastRow = sheet.getLastRow();
    const values = lastRow > 1 ? sheet.getRange(2, 1, lastRow - 1, 2).getDisplayValues() : [];
    existingKeys = new Set(values.map(row => row[0] + '|' + row[1]));
  }
  return existingKeys;
}

function writeData() {
  if (output.length === 0) {
    return;
  }
  const sheet = SpreadsheetApp.openById(FILE_ID).getSheetByName(SHEET_NAME);
  const range = sheet.getRange(sheet.getLastRow() + 1, 1, output.length, output[0].length);
  range.offset(0, 0, output.length, 1).setNumberFormat('yyyy-mm-dd');
  range.setValues(output);
}

function formatDate(date) {
  return Utilities.formatDate(date, TIMEZONE, 'yyyy-MM-dd');
}

function toNumber(value) {
  return parseFloat(String(value).replace(',', '.')) || 0;
}

E-Mail-Anhänge auslesen

Das Script sucht ungelesene Mails eines Absenders mit Anhang, verarbeitet den CSV-Anhang und archiviert den Thread, damit er beim nächsten Lauf nicht noch einmal auftaucht.

function readMailAttachments(provider, query) {
  const threads = GmailApp.search(query, 0, 20);
  for (const thread of threads) {
    const messages = thread.getMessages();
    const message = messages[messages.length - 1];
    const attachment = message.getAttachments({ includeInlineImages: false })[0];
    if (attachment) {
      processAttachment(provider, message.getDate(), attachment);
    }
    thread.moveToArchive();
  }
}

// Beispiel: readMailAttachments('anbieter_a', 'from:report@anbieter-a.example has:attachment in:inbox');

Unterschiedliche Formate vereinheitlichen

Jede Quelle liefert ihr eigenes Format. Eine switch-Anweisung übersetzt sie in unsere einheitliche Zeile.

function processAttachment(provider, mailDate, attachment) {
  const rows = Utilities.parseCsv(attachment.getDataAsString(), ';').slice(1);
  let date = formatDate(mailDate);
  let impressions = 0;
  let clicks = 0;
  let cost = 0;

  switch (provider) {
    case 'anbieter_a': // eine Zeile pro Kampagne: Impressionen, Klicks, Kosten in Spalte C bis E
      for (const row of rows) {
        impressions += toNumber(row[2]);
        clicks += toNumber(row[3]);
        cost += toNumber(row[4]);
      }
      break;
    case 'anbieter_b': // eine Zeile pro Klick, Kosten in Spalte F, Datum im Dateinamen
      date = attachment.getName().match(/(\d{4}-\d{2}-\d{2})/)[1];
      for (const row of rows) {
        if (row.length > 5) {
          clicks += 1;
          cost += toNumber(row[5]);
        }
      }
      break;
  }
  addRow(date, provider, impressions, clicks, cost);
}

Abruf über eine REST-API

Am Beispiel von idealo: Das Script holt sich ein Zugriffstoken und lädt den Klick-Report für gestern. Weil der Report keine Kosten enthält, rechnen wir sie über den vereinbarten Klickpreis aus. Zugangsdaten gehören nicht in den Code, sondern in die Script-Eigenschaften (Projekteinstellungen › Script-Properties).

function idealoMain() {
  const props = PropertiesService.getScriptProperties();
  const shopId = props.getProperty('IDEALO_SHOP_ID');
  const token = idealoGetAccessToken(props.getProperty('IDEALO_CLIENT_ID'), props.getProperty('IDEALO_CLIENT_SECRET'));
  const yesterday = formatDate(new Date(Date.now() - 24 * 60 * 60 * 1000));
  const cpc = 0.12; // vereinbarter Klickpreis

  const report = idealoDownloadDailyClickReport(shopId, 'IDEALO_DE', yesterday, yesterday, token);
  for (const row of report.slice(1)) {
    if (!row[0]) {
      break;
    }
    const clicks = toNumber(row[1]);
    addRow(row[0].substring(0, 10), 'idealo', 0, clicks, clicks * cpc);
  }
  writeData();
}

function idealoGetAccessToken(clientId, clientSecret) {
  const response = UrlFetchApp.fetch('https://businessapi.idealo.com/api/v1/oauth/token', {
    method: 'post',
    headers: { Authorization: 'Basic ' + Utilities.base64Encode(clientId + ':' + clientSecret) },
    muteHttpExceptions: true,
  });
  return JSON.parse(response.getContentText()).access_token;
}

function idealoDownloadDailyClickReport(shopId, siteId, fromDate, toDate, token) {
  const url = 'https://businessapi.idealo.com/api/v1/shops/' + shopId +
    '/daily-click-reports/download?site=' + siteId + '&from=' + fromDate + '&to=' + toDate;
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true,
  });
  return Utilities.parseCsv(response.getContentText(), ';');
}

Endpunkte und Spalten des Reports können sich ändern. Prüf sie in der aktuellen API-Dokumentation des jeweiligen Anbieters.

Workaround für FTP-Uploads

Damit per FTP empfangene Dateien denselben Weg nehmen, schickt ein Bash-Script jede neue Datei als Anhang ans Reporting-Postfach und verschiebt sie danach ins Archiv. Es läuft dauerhaft auf dem Server und braucht inotify-tools und mutt.

#!/bin/bash
WATCH_DIR="/pfad/zum/ftp-upload"
ARCHIVE_DIR="/pfad/zum/archiv"
MAILTO="reporting@example.com"

inotifywait -m -r "$WATCH_DIR" -e create -e moved_to |
while read -r dir action file; do
  sleep 10 # warten, bis der Upload vollständig ist
  subdir="main"
  if [[ $dir =~ /([^/]*)/$ ]]; then
    subdir="${BASH_REMATCH[1]}"
  fi
  echo "Datei '$file' von '$subdir' via FTP empfangen." |
    mutt -s "Reporting $subdir" -a "$dir$file" -- "$MAILTO"
  target="$ARCHIVE_DIR/$subdir/$(date +%Y%m%d)"
  mkdir -p "$target"
  mv -f "$dir$file" "$target/"
done

Das Archiv liegt bewusst außerhalb des überwachten Ordners. Sonst würde das Verschieben selbst wieder eine Mail auslösen.

Fazit

Langsame Data-Studio-Reports sind fast immer ein Datenproblem, kein Diagrammproblem. Verlagerst du das Zusammenführen und Aufbereiten nach BigQuery, bekommt Data Studio nur noch eine flache, tagesgenaue Tabelle und muss kaum noch rechnen. Mit geplanten Abfragen, ein paar Zeilen Apps Script für die Sonderfälle und berechneten Feldern an der richtigen Stelle läuft das Reporting danach weitgehend von allein und lädt in Sekunden.

Dieser Artikel ist 2022 zuerst bei adtraffic erschienen und wurde für rsc24 überarbeitet. Code und Hinweise wurden dabei aktualisiert.

Erstveröffentlichung: www.adtraffic.de

Zurück zur Übersicht