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. <\/p>\n\n
This script was developed to efficiently monitor e-commerce transactions and send immediate notifications in case of irregularities. The main functions include: <\/p>\n\n
BigQuery data query:<\/strong> The script connects to Google BigQuery to retrieve current sales data. Time and resource efficiency:<\/strong> Automated monitoring reduces manual effort and allows you to focus on strategic tasks. Setting up Google BigQuery:<\/strong> Ensure that sales data is available in BigQuery. view raw<\/a> ga4_monitoring_script.js<\/a> hosted with \u2764 by GitHub<\/a><\/p>\n\n 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. <\/p>\n","protected":false},"excerpt":{"rendered":" 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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","slim_seo":{"title":"Automate GA4 monitoring with BigQuery","description":"An automated script monitors e-commerce transactions via GA4 + BigQuery and alerts you to outages. This way, you can keep an eye on revenue, activity, and data quality."},"footnotes":""},"categories":[28],"tags":[],"class_list":["post-1875","post","type-post","status-publish","format-standard","hentry","category-big-query"],"taxonomy_info":{"category":[{"value":28,"label":"Big Query"}]},"featured_image_src_large":false,"author_info":{"display_name":"admin","author_link":"https:\/\/webmasterei-prange.de\/en\/author\/admin\/"},"comment_info":0,"category_info":[{"term_id":28,"name":"Big Query","slug":"big-query","term_group":0,"term_taxonomy_id":28,"taxonomy":"category","description":"","parent":0,"count":12,"filter":"raw","cat_ID":28,"category_count":12,"category_description":"","cat_name":"Big Query","category_nicename":"big-query","category_parent":0}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/comments?post=1875"}],"version-history":[{"count":1,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1875\/revisions"}],"predecessor-version":[{"id":1876,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/posts\/1875\/revisions\/1876"}],"wp:attachment":[{"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/media?parent=1875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/categories?post=1875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/webmasterei-prange.de\/en\/wp-json\/wp\/v2\/tags?post=1875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Benefits for Google Ads Managers:<\/h2>\n\n
The implementation of this monitoring script is straightforward:<\/h2>\n\n
The script<\/h2>\n\n
\/**\n * Google Apps Script to control Google Analytics properties via Apps Script + warning.\n * written by Bernhard Prange\n * This script is an e-commerce activity monitoring system implemented using Google Apps Script. It performs the following main functions: \n * - It connects to Google BigQuery to query data from a configured table.\n * - It executes a specific query to determine how many hours have passed since the last purchase event.\n * - It compares this period with a predefined threshold (24 hours).\n * - If the time since the last order exceeds the threshold, the script automatically sends a warning message.\n * - Notification can be sent either by email or via Slack, depending on the configuration.\n * - If no order data is found in the last 3 days, a warning message will also be sent.\n **\/\n\n\/\/ Configuration\nconst PROJECT_ID = 'MYPROJECT'; \/\/ Replace with your Google Cloud project ID\nconst DATASET_ID = 'analytics_123456789'; \/\/ Replace with your BigQuery dataset\nconst TABLE_ID = 'events_intraday_*'; \/\/ Replace with your BigQuery table\nconst MAX_NO_ORDERS = 24;\nconst MESSAGE_SYSTEM = 'email'; \/\/ Possible values: 'slack' or 'email'\nconst EMAIL_RECIPIENT = 'my_warning_email'; \/\/ Add the email address\nconst 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) \n\n\/**\n * Executes a SQL query to BigQuery and returns the results\n * @param {string} query - The SQL query\n * @return {object} The query results\n *\/\nfunction runBigQueryQuery(query) {\n try {\n \/\/ Execute query\n const request = {\n query: query,\n useLegacySql: false\n };\n \n const queryResults = BigQuery.Jobs.query(request, PROJECT_ID);\n \n \/\/ Wait for the query to complete if it is still running\n const jobId = queryResults.jobReference.jobId;\n let sleepTimeMs = 500;\n let isQueryRunning = queryResults.jobComplete === false;\n \n while (isQueryRunning) {\n Utilities.sleep(sleepTimeMs);\n sleepTimeMs *= 2; \/\/ Exponential backoff\n \n const job = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);\n isQueryRunning = job.jobComplete === false;\n }\n \n \/\/ Get finished results\n const completeResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);\n return completeResults;\n } catch (e) {\n Logger.log('BigQuery query error: ' + e.toString());\n throw e;\n }\n}\n\n\/**\n * Sends a message via email\n * @param {string} message - The message to be sent\n *\/\nfunction sendEmail(message) {\n try {\n const subject = `Warning: ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID} No orders for a long time`;\n MailApp.sendEmail({\n to: EMAIL_RECIPIENT,\n subject: subject,\n body: message\n });\n Logger.log('Email sent successfully');\n } catch (e) {\n Logger.log('Error sending email: ' + e.toString());\n }\n}\n\n\/**\n * Sends a message to Slack\n * @param {string} message - The message to be sent\n *\/\nfunction sendSlackMessage(message) {\n try {\n const payload = {\n 'text': message\n };\n \n const options = {\n 'method': 'post',\n 'contentType': 'application\/json',\n 'payload': JSON.stringify(payload)\n };\n \n UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options);\n Logger.log('Slack message sent successfully');\n } catch (e) {\n Logger.log('Error sending Slack message: ' + e.toString());\n }\n}\n\nfunction executeQueryAndWriteToSheet() {\n \/\/ Example SQL query\n const query = `\n SELECT \n TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),TIMESTAMP_MICROS(event_timestamp),HOUR) time_since_last_order,\n FROM \\`${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}\\`\n 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\"))\n AND event_name = \"purchase\"\n ORDER BY event_timestamp desc\n LIMIT 1\n `;\n \n \/\/ Execute query\n const results = runBigQueryQuery(query);\n Logger.log(results);\n \n \/\/ Check if results are available\n if (results && results.rows && results.rows.length > 0) {\n const timeSinceLastOrder = results.rows[0].f[0].v;\n Logger.log('Time since last order (hours): ' + timeSinceLastOrder);\n \n \/\/ Check if MAX_NO_ORDERS has been exceeded\n if (timeSinceLastOrder > MAX_NO_ORDERS) {\n 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.`;\n \n \/\/ Send notification based on MESSAGE_SYSTEM\n if (MESSAGE_SYSTEM.toLowerCase() === 'email') {\n sendEmail(message);\n } else if (MESSAGE_SYSTEM.toLowerCase() === 'slack') {\n sendSlackMessage(message);\n } else {\n Logger.log('Unknown message system: ' + MESSAGE_SYSTEM);\n }\n } else {\n Logger.log('Everything is OK. Time since the last order: ' + timeSinceLastOrder + ' hours');\n }\n } else {\n const message = 'WARNING ${PROJECT_ID}.${DATASET_ID}.${TABLE_ID}: No order data was found in the last 3 days!';\n \n \/\/ Send notification based on MESSAGE_SYSTEM\n if (MESSAGE_SYSTEM.toLowerCase() === 'email') {\n sendEmail(message);\n } else if (MESSAGE_SYSTEM.toLowerCase() === 'slack') {\n sendSlackMessage(message);\n } else {\n Logger.log('Unknown message system: ' + MESSAGE_SYSTEM);\n }\n }\n}<\/pre>\n\nConclusion<\/h2>\n\n