4. Actions to Pause Over-Pacing Campaigns

These actions build upon the views/SQL created to connect Creative / Budget Sheet information to Facebook spend. You can see instructions on how to create that base view here:

In these instructions, we’ll focus on how to take that query to the next level to:

Create Actions to automatically pause over-pacing campaigns.

Single Out Over-Pacing Campaigns

Instructions:

Create your over-pacing SQL
1. Login to Alli Data and select the client you want to create an action for
2. Navigate to Reports → Explorer and select New Data Report
3. Give your report a Name and update the Report Type to Custom Redshift
4. Copy and paste the SQL Example in from below and update anything within and including { } to match your client settings
5. Once your SQL is updated, click Publish to update the query
6. Click Preview to ensure the output is as expected
Create your Action
1. Navigate to Alli Actions by selecting Automate Campaigns then select Create Action
2. Filter down the list and select the Update Campaign Status - Disable 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 in step #1 in the Report of Datasource name & click Select
5. Review your action setup
6. Save and Schedule your action to run

SQL

Context:

{client_name.example_connected_fb} should correspond to the name of the SQL View/Report you created earlier (in the Connecting Budget to Spend step). Replace this with your client name and the name you gave your report
Ensure that your {client_name.example_connected_fb} datasource contains a campaign_id column for Facebook
We add Percent Spent by dividing total_spend_to_date by budget. If either of these columns have different names in your view, switch out those names
Replace the {#} with whatever threshold you are looking for
- where percent_spend > looks at our newly created columns, and only pulls out rows where the Percent Spend is greater than or equal to the number we pass.
- To adjust that, for example to see campaigns that are overpaced: utilize where percent_spend >= 100. Or to filter to campaigns with greater than or equal to 80%, utilize where percent_spend >= 80
- >= is inclusive, so greater than or equal to our threshold. You can also utilize > to only select campaigns with greater than the threshold you set

CODE

select distinct campaign_id as id from (select *,
        (total_spend_to_date / budget) * 100 as percent_spend 
from {client_name.example_connected_fb}
where percent_spend >= {#})

Live Example with real datasources

CODE

select distinct campaign_id as id 
from 
(select *,
        (total_spend_to_date / budget) * 100 as percent_spend 
from client_name.example_connected_fb
where percent_spend > 100)