Automatisierte Überwachung mit Google Analytics 4 und BigQuery

In der dynamischen Welt des E-Commerce ist es für Online-Marketing-Manager entscheidend, stets den Überblick über Verkaufsaktivitäten zu behalten. Ein effektives Monitoring-System kann dabei helfen, Umsatzeinbußen zu vermeiden und schnell auf Veränderungen zu reagieren. In diesem Zusammenhang bietet ein speziell entwickeltes Google Apps Script, das Google BigQuery integriert, eine leistungsstarke Lösung.

Funktionen des Überwachungsskripts

Dieses Skript wurde entwickelt, um E-Commerce-Transaktionen effizient zu überwachen und bei Unregelmäßigkeiten sofortige Benachrichtigungen zu versenden. Die Hauptfunktionen umfassen:

Datenabfrage aus BigQuery: Das Skript verbindet sich mit Google BigQuery, um aktuelle Verkaufsdaten abzurufen.
Analyse der Verkaufsaktivität: Es berechnet die Zeitspanne seit der letzten Bestellung und vergleicht diese mit einem festgelegten Schwellenwert (standardmäßig 24 Stunden).
Automatische Benachrichtigung: Wenn der Schwellenwert überschritten wird, sendet das Skript automatisch eine Warnung per E-Mail oder über Slack.
Überprüfung auf Inaktivität: Sollten innerhalb der letzten drei Tage keine Bestellungen eingegangen sein, wird ebenfalls eine Benachrichtigung ausgelöst.

Vorteile für Google Ads Manager:

Zeit- und Ressourceneffizienz: Automatisierte Überwachung reduziert den manuellen Aufwand und ermöglicht es, sich auf strategische Aufgaben zu konzentrieren.
Schnelle Reaktionsfähigkeit: Durch sofortige Benachrichtigungen können Marketingmaßnahmen zeitnah angepasst werden, um potenzielle Umsatzverluste zu minimieren.
Einfache Integration: Das Skript lässt sich nahtlos in bestehende Google-Umgebungen integrieren, ohne dass umfangreiche technische Anpassungen erforderlich sind.
Flexibilität bei Benachrichtigungen: Je nach Präferenz können Warnungen entweder per E-Mail oder über Slack empfangen werden.
Implementierung des Skripts

Die Implementierung dieses Überwachungsskripts gestaltet sich unkompliziert:

Einrichtung von Google BigQuery: Sicherstellen, dass die Verkaufsdaten in BigQuery verfügbar sind.
Erstellung des Google Apps Scripts: In Google Apps Script ein neues Projekt anlegen und den bereitgestellten Code einfügen.
Anpassung der Konfiguration: Parameter wie Projekt-ID, Dataset-ID, Tabellenname, Schwellenwert und Benachrichtigungskanal entsprechend den eigenen Anforderungen konfigurieren.
Festlegung von Triggern: Automatische Ausführung des Skripts in regelmäßigen Intervallen durch entsprechende Trigger-Einstellungen.

Das Script

/**
 * Google Apps Script zur Kontrolle von Google Analytics Properties via Apps Script + Warnung.
 * geschrieben von Bernhard Prange
 * Das Script ist ein Überwachungssystem für E-Commerce-Aktivitäten, das über Google Apps Script implementiert wurde. Es führt folgende Hauptfunktionen aus:
 * - Es verbindet sich mit Google BigQuery, um Daten aus einer konfigurierten Tabelle abzufragen.
 * - Es führt eine spezifische Abfrage aus, die ermittelt, wie viele Stunden seit der letzten Bestellung ("purchase"-Event) vergangen sind.
 * - Es vergleicht diese Zeitspanne mit einem vordefinierten Schwellenwert (24 Stunden).
 * - Wenn die Zeit seit der letzten Bestellung den Schwellenwert überschreitet, sendet das Script automatisch eine Warnmeldung.
 * - Die Benachrichtigung kann wahlweise per E-Mail oder über Slack erfolgen, je nach Konfiguration.
 * - Falls keine Bestellungsdaten in den letzten 3 Tagen gefunden werden, wird ebenfalls eine Warnmeldung gesendet.
 **/

// Konfiguration
const PROJECT_ID = 'MYPROJECT'; // Ersetze mit deiner Google Cloud Projekt-ID
const DATASET_ID = 'analytics_123456789';    // Ersetze mit deinem BigQuery Dataset
const TABLE_ID = 'events_intraday_*';     // Ersetze mit deiner BigQuery Tabelle
const MAX_NO_ORDERS = 24;
const MESSAGE_SYSTEM = 'email'; // Mögliche Werte: 'slack' oder 'email'
const EMAIL_RECIPIENT = 'my_warning_email'; // Füge die E-Mail-Adresse hinzu
const SLACK_WEBHOOK_URL = 'https://hooks.slack.com/services/slack/webhook/url'; // Füge deine Slack Webhook URL hinzu. (Dazu musst Du eine Slack App erstellen)

/**
 * Führt eine SQL-Abfrage an BigQuery aus und gibt die Ergebnisse zurück
 * @param {string} query - Die SQL-Abfrage
 * @return {object} Die Abfrageergebnisse
 */
function runBigQueryQuery(query) {
  try {
    // Abfrage ausführen
    const request = {
      query: query,
      useLegacySql: false
    };
    
    const queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
    
    // Auf Abschluss der Abfrage warten, falls sie noch läuft
    const jobId = queryResults.jobReference.jobId;
    let sleepTimeMs = 500;
    let isQueryRunning = queryResults.jobComplete === false;
    
    while (isQueryRunning) {
      Utilities.sleep(sleepTimeMs);
      sleepTimeMs *= 2;  // Exponentielles Backoff
      
      const job = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
      isQueryRunning = job.jobComplete === false;
    }
    
    // Fertige Ergebnisse abrufen
    const completeResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
    return completeResults;
  } catch (e) {
    Logger.log('Fehler bei der BigQuery-Abfrage: ' + e.toString());
    throw e;
  }
}

/**
 * Sendet eine Nachricht per E-Mail
 * @param {string} message - Die zu sendende Nachricht
 */
function sendEmail(message) {
  try {
    const subject = `Warnung: ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID} Keine Bestellungen seit längerer Zeit`;
    MailApp.sendEmail({
      to: EMAIL_RECIPIENT,
      subject: subject,
      body: message
    });
    Logger.log('E-Mail erfolgreich gesendet');
  } catch (e) {
    Logger.log('Fehler beim Senden der E-Mail: ' + e.toString());
  }
}

/**
 * Sendet eine Nachricht an Slack
 * @param {string} message - Die zu sendende Nachricht
 */
function sendSlackMessage(message) {
  try {
    const payload = {
      'text': message
    };
    
    const options = {
      'method': 'post',
      'contentType': 'application/json',
      'payload': JSON.stringify(payload)
    };
    
    UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options);
    Logger.log('Slack-Nachricht erfolgreich gesendet');
  } catch (e) {
    Logger.log('Fehler beim Senden der Slack-Nachricht: ' + e.toString());
  }
}

function executeQueryAndWriteToSheet() {
  // Beispiel-SQL-Abfrage
  const query = `
    SELECT 
      TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),TIMESTAMP_MICROS(event_timestamp),HOUR) time_since_last_order,
    FROM \`${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}\`
    WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE("Europe/Berlin"), INTERVAL 3 DAY)) AND FORMAT_DATE('%Y%m%d',CURRENT_DATE("Europe/Berlin"))
      AND event_name = "purchase"
    ORDER BY event_timestamp desc
    LIMIT 1
  `;
  
  // Abfrage ausführen
  const results = runBigQueryQuery(query);
  Logger.log(results);
  
  // Prüfen, ob Ergebnisse vorhanden sind
  if (results && results.rows && results.rows.length > 0) {
    const timeSinceLastOrder = results.rows[0].f[0].v;
    Logger.log('Zeit seit der letzten Bestellung (Stunden): ' + timeSinceLastOrder);
    
    // Prüfen, ob MAX_NO_ORDERS überschritten wurde
    if (timeSinceLastOrder > MAX_NO_ORDERS) {
      const message = `WARNUNG ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}: Es wurden seit ${timeSinceLastOrder} Stunden keine Bestellungen registriert. Das überschreitet den Schwellenwert von ${MAX_NO_ORDERS} Stunden.`;
      
      // Benachrichtigung senden basierend auf MESSAGE_SYSTEM
      if (MESSAGE_SYSTEM.toLowerCase() === 'email') {
        sendEmail(message);
      } else if (MESSAGE_SYSTEM.toLowerCase() === 'slack') {
        sendSlackMessage(message);
      } else {
        Logger.log('Unbekanntes Nachrichtensystem: ' + MESSAGE_SYSTEM);
      }
    } else {
      Logger.log('Alles in Ordnung. Zeit seit der letzten Bestellung: ' + timeSinceLastOrder + ' Stunden');
    }
  } else {
    const message = 'WARNUNG ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}: Es wurden keine Bestellungsdaten in den letzten 3 Tagen gefunden!';
    
    // Benachrichtigung senden basierend auf MESSAGE_SYSTEM
    if (MESSAGE_SYSTEM.toLowerCase() === 'email') {
      sendEmail(message);
    } else if (MESSAGE_SYSTEM.toLowerCase() === 'slack') {
      sendSlackMessage(message);
    } else {
      Logger.log('Unbekanntes Nachrichtensystem: ' + MESSAGE_SYSTEM);
    }
  }
}

view rawga4_monitoring_script.js hosted with ❤ by GitHub

Fazit

Die Automatisierung der Überwachung von E-Commerce-Transaktionen mithilfe von Google Apps Script und BigQuery bietet Online-Marketing-Managern eine effiziente Möglichkeit, Verkaufsaktivitäten im Blick zu behalten und proaktiv auf Veränderungen zu reagieren. Durch die nahtlose Integration in bestehende Systeme und die Flexibilität bei den Benachrichtigungsoptionen wird der Arbeitsalltag erleichtert und die Effektivität gesteigert.