ga4_monitoring_script.js

/**
 * 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);
    }
  }
}