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

Overview

This alert will identify if an account or campaign stopped spending DoD, comparing yesterday to today.

Platforms	Meta
Datasource Type	`Facebook Ads` datasource type
Required Columns	Urgent issue

Instructions

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 Facebook/Meta datasource with the following required columns:

account_id
account_name
spend

Copy and paste the SQL below

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

CODE

WITH yesterday AS
  (SELECT account_id,
          account_name,
          sum(spend) AS yesterday_spend
   FROM *DATASOURCE*
   WHERE (date_start = convert_timezone('US/Central',SYSDATE)::DATE -1)
   GROUP BY account_id,
            account_name
    HAVING yesterday_spend > 50),
            
     today AS
  (SELECT account_id,
          account_name,
          sum(spend) AS cost
   FROM *DATASOURCE*
   WHERE date_start = 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 Facebook/Meta datasource with the following required columns:

account_id
account_name
campaign_id
campaign_name
spend

Copy and paste the SQL below

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

CODE

WITH yesterday AS
  (SELECT account_id,
          account_name,
          campaign_id,
          campaign_name,
          sum(spend) AS yesterday_spend
   FROM *DATASOURCE*
   WHERE (date_start = convert_timezone('US/Central',SYSDATE)::DATE -1)
   GROUP BY account_id,
            account_name,
            campaign_id,
            campaign_name
    HAVING yesterday_spend > 50),
            
     today AS
  (SELECT account_id,
          account_name,
          campaign_id,
          campaign_name,
          sum(spend) AS cost
   FROM *DATASOURCE*
   WHERE date_start = convert_timezone('US/Central',SYSDATE)::DATE
   GROUP BY account_id,
            account_name,
            campaign_id,
          campaign_name)
            
SELECT DISTINCT yesterday.account_id,
                yesterday.account_name,
                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.account_id = yesterday.account_id
and 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

You’re all set up, you will now receive an alert if your Facebook account or campaign stops spending.