Automated Monitoring with Google Analytics 4 and BigQuery

In the dynamic world of e-commerce, it is crucial for online marketing managers to constantly keep an overview of sales activities. An effective monitoring system can help prevent revenue losses and quickly respond to changes. In this context, a specially developed Google Apps Script that integrates Google BigQuery offers a powerful solution.

Functions of the Monitoring Script

This script was developed to efficiently monitor e-commerce transactions and send immediate notifications in case of irregularities. The main functions include:

BigQuery data query: The script connects to Google BigQuery to retrieve current sales data.
Sales activity analysis: It calculates the time since the last order and compares it with a set threshold (24 hours by default).
Automatic notification: When the threshold is exceeded, the script automatically sends an alert via email or Slack.
Inactivity check: If no orders have been received within the last three days, a notification will also be triggered.

Benefits for Google Ads Managers:

Time and resource efficiency: Automated monitoring reduces manual effort and allows you to focus on strategic tasks.
Fast responsiveness: Immediate notifications allow marketing measures to be adjusted promptly to minimize potential revenue losses.
Easy integration: The script can be seamlessly integrated into existing Google environments without the need for extensive technical adjustments.
Flexibility with notifications: Depending on your preference, alerts can be received either via email or Slack.
Implementation of the script

The implementation of this monitoring script is straightforward:

Setting up Google BigQuery: Ensure that sales data is available in BigQuery.
Creating the Google Apps Script: Create a new project in Google Apps Script and insert the provided code.
Customize the configuration: Configure parameters such as project ID, dataset ID, table name, threshold, and notification channel according to your requirements.
Setting triggers: Automatically execute the script at regular intervals using appropriate trigger settings.

The script

/**
  * Google Apps Script to control Google Analytics properties via Apps Script + warning.
  * written by Bernhard Prange
  * This script is an e-commerce activity monitoring system implemented using Google Apps Script. It performs the following main functions: 
  * - It connects to Google BigQuery to query data from a configured table.
  * - It executes a specific query to determine how many hours have passed since the last purchase event.
  * - It compares this period with a predefined threshold (24 hours).
  * - If the time since the last order exceeds the threshold, the script automatically sends a warning message.
  * - Notification can be sent either by email or via Slack, depending on the configuration.
  * - If no order data is found in the last 3 days, a warning message will also be sent.
  **/

// Configuration
const PROJECT_ID = 'MYPROJECT'; // Replace with your Google Cloud project ID
const DATASET_ID = 'analytics_123456789'; // Replace with your BigQuery dataset
const TABLE_ID = 'events_intraday_*'; // Replace with your BigQuery table
const MAX_NO_ORDERS = 24;
const MESSAGE_SYSTEM = 'email'; // Possible values: 'slack' or 'email'
const EMAIL_RECIPIENT = 'my_warning_email'; // Add the email address
const SLACK_WEBHOOK_URL = 'https://hooks.slack.com/services/slack/webhook/url'; // Add your Slack webhook URL. (You'll need to create a Slack app for this) 

/**
  * Executes a SQL query to BigQuery and returns the results
  * @param  {string}  query - The SQL query
  * @return  {object}  The query results
  */
function runBigQueryQuery(query) {
  try {
  // Execute query
  const request = {
  query: query,
  useLegacySql: false
  };
    
  const queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
    
  // Wait for the query to complete if it is still running
  const jobId = queryResults.jobReference.jobId;
  let sleepTimeMs = 500;
  let isQueryRunning = queryResults.jobComplete === false;
    
  while (isQueryRunning) {
  Utilities.sleep(sleepTimeMs);
  sleepTimeMs *= 2; // Exponential backoff
      
  const job = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  isQueryRunning = job.jobComplete === false;
  }
    
  // Get finished results
  const completeResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  return completeResults;
  } catch (e) {
  Logger.log('BigQuery query error: ' + e.toString());
  throw e;
  }
}

/**
  * Sends a message via email
  * @param  {string}  message - The message to be sent
  */
function sendEmail(message) {
  try {
  const subject = `Warning: ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}  No orders for a long time`;
  MailApp.sendEmail({
  to: EMAIL_RECIPIENT,
  subject: subject,
  body: message
  });
  Logger.log('Email sent successfully');
  } catch (e) {
  Logger.log('Error sending email: ' + e.toString());
  }
}

/**
  * Sends a message to Slack
  * @param  {string}  message - The message to be sent
  */
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 message sent successfully');
  } catch (e) {
  Logger.log('Error sending Slack message: ' + e.toString());
  }
}

function executeQueryAndWriteToSheet() {
  // Example SQL query
  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
  `;
  
  // Execute query
  const results = runBigQueryQuery(query);
  Logger.log(results);
  
  // Check if results are available
  if (results && results.rows && results.rows.length > 0) {
  const timeSinceLastOrder = results.rows[0].f[0].v;
  Logger.log('Time since last order (hours): ' + timeSinceLastOrder);
    
  // Check if MAX_NO_ORDERS has been exceeded
  if (timeSinceLastOrder > MAX_NO_ORDERS) {
  const message = `WARNING ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}: Since ${timeSinceLastOrder}  No orders have been registered for hours. This exceeds the threshold of $ {MAX_NO_ORDERS}  hours.`;
      
  // Send notification based on MESSAGE_SYSTEM
  if (MESSAGE_SYSTEM.toLowerCase() === 'email') {
  sendEmail(message);
  } else if (MESSAGE_SYSTEM.toLowerCase() === 'slack') {
  sendSlackMessage(message);
  } else {
  Logger.log('Unknown message system: ' + MESSAGE_SYSTEM);
  }
  } else {
  Logger.log('Everything is OK. Time since the last order: ' + timeSinceLastOrder + ' hours');
  }
  } else {
  const message = 'WARNING ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}: No order data was found in the last 3 days!';
    
  // Send notification based on MESSAGE_SYSTEM
  if (MESSAGE_SYSTEM.toLowerCase() === 'email') {
  sendEmail(message);
  } else if (MESSAGE_SYSTEM.toLowerCase() === 'slack') {
  sendSlackMessage(message);
  } else {
  Logger.log('Unknown message system: ' + MESSAGE_SYSTEM);
  }
  }
}

view raw ga4_monitoring_script.js hosted with ❤ by GitHub

Conclusion

The automation of e-commerce transaction monitoring using Google Apps Script and BigQuery offers online marketing managers an efficient way to keep track of sales activities and proactively respond to changes. Through seamless integration into existing systems and flexibility in notification options, daily work is simplified and effectiveness is increased.