How-to set up an alert if your Google Account or Campaign stops spending

Overview

Send an alert if your Google Ads account or campaign stopped spending day-over-day, comparing yesterday to today.

Instructions

Before beginning your setup, you will need a google ads datasource in Alli Data that contains the required columns.

Platform	Google Ads
Datasource Type	`Google Ads` datasource type
Required Columns	Urgent issue

Step One: Create your day-over-day report

Navigate to Explorer in Alli
Select New Data Report
Name your report, provide a description and select Custom SQL as your source
Copy and paste the SQL provided below, and made the necessary adjustments.

Account SQL

You will need a Google Ads datasource with the following required columns:

customer_id
customer_descriptive_name
cost

Copy & paste the SQL Below

Replace *DATASOURCE* with your Google Ads datasource (remember it’s client.datasource not just datasource)

CODE

WITH yesterday AS
  (SELECT customer_id as account_id,
          customer_descriptive_name as account_name,
          sum(cost) AS yesterday_spend
   FROM *DATASOURCE*
   WHERE (date = convert_timezone('US/Central',SYSDATE)::DATE -1)
   GROUP BY account_id, 
            account_name
    HAVING yesterday_spend > 50),
            
     today AS
  (SELECT customer_id as account_id,
          customer_descriptive_name as account_name,
          sum(cost) AS cost
   FROM *DATASOURCE*
   WHERE date = convert_timezone('US/Central',SYSDATE)::DATE
   GROUP BY account_id, 
            account_name)
            
SELECT DISTINCT yesterday.account_id,
                yesterday.account_name,
                yesterday_spend,
                CASE
                    WHEN cost IS NULL THEN 0
                    ELSE today.cost
                END AS today_spend
FROM yesterday
LEFT JOIN today AS today ON today.account_id = yesterday.account_id
WHERE today_spend = 0

Campaign SQL

You will need a Google Ads datasource with the following required columns:

campaign_id
campaign_name
cost

Copy & paste the SQL Below

Replace *DATASOURCE* with your Google Ads datasource (remember it’s client.datasource not just datasource)

CODE

WITH yesterday AS
  (SELECT campaign_id,
          campaign_name,
          sum(cost) AS yesterday_spend
   FROM *DATASOURCE*
   WHERE (date = convert_timezone('US/Central',SYSDATE)::DATE -1)
   GROUP BY campaign_id,
          campaign_name
    HAVING yesterday_spend > 50),
            
     today AS
  (SELECT campaign_id,
          campaign_name,
          sum(cost) AS cost
   FROM *DATASOURCE*
   WHERE date = convert_timezone('US/Central',SYSDATE)::DATE
   GROUP BY campaign_id,
          campaign_name)
            
SELECT DISTINCT yesterday.campaign_id,
                yesterday.campaign_name,
                yesterday_spend,
                CASE
                    WHEN cost IS NULL THEN 0
                    ELSE today.cost
                END AS today_spend
FROM yesterday
LEFT JOIN today AS today ON today.campaign_id = yesterday.campaign_id
WHERE today_spend = 0

5. Click Preview and confirm if the output is correct

6. Once you are QA’d, click Publish and move to Step 2 (Marketplace App)

Step Two: Set up your Marketplace Alert

In the Marketplace tab, search for Alli Custom Alerts and select to install the app
Follow the provided setup instructions to complete your Marketplace app
1. Remember for the view_name it should match the report you just created! So in the example, we created a report called report_name, so the total view_name should be client.report_name
2. Don’t forget to set a schedule if necessary!

Your day-over-day alert is set up and will send you alerts if there is no spend