How-to Pause or Activate Meta (Facebook) Ads

Overview

In these instructions, we’ll focus on how to create the datasources and sql views to leverage the Alli Actions Ad Effective Status action to pause ads automatically. This can also be leveraged for the Adset Effective Status action to pause adsets, or the Campaign Effective Status action to pause campaigns.

Instructions

Create your reference sheet datasource

1. In your reference sheet, you need the following columns:

   1. ad_id (best) OR ad_name (good)

      1. Ensure ad_id is set to `plain text` and not number format

      2. We recommend including both or just ad_id, as ad_id is cleaner/simpler, and is required for activating ads

      3. If you select ad_id you can skip directly to creating your query

      4. If you select ad_name you will be using core data {client}_core.social_ad or {client}_core.social_adset or {client}_core.social_campaign in your SQL later on.

   2. date - the date you want the ad to be paused or activated

   3. status - the status you want implemented:

      1. Options are Active, Archived, Deleted, or Paused

USING YOUR GOOGLE SHEET TO POWER YOUR ACTION

The benefit of this method is no SQL and no report to manage. After you set this tab up fully, you will not touch it, nor should anyone else. - this is important

1. Create a new tab, name it appropriately (Recommended Actions Sheet - DO NOT EDIT).

2. Copy the headers from your first tab here.

   1. Rename your status column to effective_status

   2. Rename your ad_id (or campaign_id, adset_id, etc) column is named id

3. In Cell A2, place and update this function =FILTER(Sheet1!A:D,Sheet1!B:B,Sheet1!B:B=TODAY())

   1. Sheet1 should be the name of your first sheet

4. Check your google sheet timezone

   1. this is impactful because TODAY() gets the current date in the timezone. So if your action runs at 1 am eastern time on 2/10, but your google sheet is in pacific time, TODAY() in pacific time would be 2/9 (at 10 pm). TLDR - make sure your google sheet timezone makes sense with 1) what time & timezone your action will run in, and 2) what date you put in the first tab.

   2. File > Settings > Timezone

      

USING SQL TO POWER YOUR ACTION

1. Login to Alli Data and select the client you want to create a datasource for

2. Navigate to Reports, select Add New Datasource, and select the datasource type (Google Sheets, Smartsheet, Airtable, etc). Give your datasource a new name.

   1. In Advanced Settings, ensure you select Replace for New Uploads

      

   2. Ensure the Ad Id/Ad Name and the status columns are Numbers & Letters

   3. Ensure the Date column is a Date Timestamp

      

3. Save your new datasource & select Load Data

Create your SQL view

1. Login to Alli Data and select the client you want to create/find a datasource for

2. Navigate to Reporting → Reports and select New Data Report. Select the SQL option

   

3. Copy and paste the SQL Example in from below and update anything within and including { } to match your client settings

4. Click Preview to ensure the output is as expected

5. Once your SQL is updated, click Publish to update the query. Name your report and give it a description and click save

6. Click Preview to ensure the output is as expected

Create your Action

1. Navigate to Alli Actions by selecting Actions then select Create Action

   

2. Filter down the list and select the Ad Effective Status Action Type

   

3. Name your action and select your authenticated account

   

4. Select Alli Data and place the name of the view you created above

5. 

   Review your action setup

6. Save and Schedule your action to run

SQL Example: ID

• replace {reference} with the datasource you created (ensure you remove the brackets as well)

  • ref.status should reference the column name of the status column in your reference datasource

  • ref.date should reference the column name of the date column in your reference datasource

Ad Example (assuming your column is named ad_id)

select ref.ad_id::text as id, 
      ref.status as effective_status 
from {reference} as ref
where ref.date = current_date

Adset Example (assuming your column is named adset_id)

select ref.adset_id::text as id, 
      ref.status as effective_status 
from {reference} as ref
where ref.date = current_date

Ad Example (assuming your column is named campaign_id)

select ref.campaign_id::text as id, 
      ref.status as effective_status 
from {reference} as ref
where ref.date = current_date

Filled out example (Pretending my datasource you created in Create your reference sheet datasource is named client.ad_status_google_sheet

select ref.ad_id::text as id, 
      ref.status as effective_status 
from client.ad_status_google_sheet as ref
where ref.date = current_date

SQL Example: Ad Name (Not recommended, use ad_id if possible)

• replace {reference} with the datasource you created

  • ref.status should reference the column name of the status column in your reference datasource

  • ref.ad_name should reference the column name of the ad name column in your reference datasource

  • ref.date should reference the column name of the date column in your reference datasource

• replace {client} with your client name

  • fb.ad_id should reference the column name of the ad id within the facebook datasource

  • fb.ad_name should reference the column name of the ad name within the facebook datasource

select distinct(fb.ad_id) as id, 
      ref.status as effective_status 
from {reference} as ref
left join {client}_core.social_ad as fb
on ref.ad_name = fb.ad_name
where ref.date = current_date

SQL Example: Creating separate queries for activating vs pausing

You can use the same reference sheet for both activating and pausing ads, and if so we recommend leveraging the ad_id column, as new ads (that have not run yet) may not exist in your facebook datasource yet (until they receive impressions or spend)

To use the same reference sheet, if your ads pause and activate at the same time, you don’t need separate sql, but if you activate and pause ads at different times (or just want to keep the queries separate) you can use the below options.

Pausing (using Ad Name)

select distinct(fb.ad_id)::text as id, 
      ref.status as effective_status 
from {reference} as ref
left join {facebook} as fb
on ref.ad_name = fb.ad_name
where ref.date = current_date
and status = 'Paused'

Pausing (Using Ad Id)

select ref.ad_id::text as id, 
      ref.status as effective_status 
from {reference} as ref
where ref.date = current_date
and status = 'Paused'

Activating

select ref.ad_id::text as id, 
      ref.status as effective_status 
from {reference} as ref
where ref.date = current_date
and status = 'Active'

Related articles