How-To: Set Up Alli SQR Automation

Automate your Search Query Reporting workflow with a unified interface that automatically pulls data, sends reminder Slack messages, and pushes keyword changes!

Why should you use this?

Search query reports are difficult to manage, cumbersome to work with, and easy to forget. The SQR Automation tool automates the creation of search term reports, allowing your SEM team to quickly review and action on search ad performance. Optionally, the SQR tool will send a reminder message in a Slack channel you choose, linking directly to a Google Sheet containing your data.

After reviewing the consolidated data, use the “Add” and “Negate” sheets to automatically push positive and negative keyword changes directly to the platform.

Installation and Setup

Full setup requires developer access to the Google Ads Account. This requires an ASD Ticket. Which can be made here:

https://agencypmg.atlassian.net/servicedesk/customer/portal/1/group/3/create/12

1. Setup your Google Sheet

Make a copy of the https://docs.google.com/spreadsheets/d/17wg9JHOfHMgoY_P-IFdbV-TSY7P52OE03LH8c_jMhFU/edit?usp=drive_link sheet, making sure to check the box to “Share it with the same people”.

Copy the Alli SQR Automation - TEMPLATE sheet.

Next, it’s best to configure the SETUP and THRESHOLDS tabs in the sheet, which will define the conditions to pull data. The app pull all available data when it is run if these tabs are not filled out.

1a. (optional) Configure the SETUP tab

SETUP defines the campaigns and ad groups to include or exclude in your data.

The Action column indicates whether to Include or Exclude the campaign or ad group that’s matched.
The Match Group indicates whether to look for matching Campaigns or Ad groups.
The Filter indicates which values the Match Group should contain.

Click the dropdown below for an example.

For example...

A sheet that is setup like this:

Action	Match Group	Filter
Include	Campaign Name	_br,test
Exclude	Campaign Name	_travel

will pull all Campaigns that include “_br” AND “test” in the name, but don’t include “_travel”.

In this case, a campaign named

test_campaign_br_x_x_24

will be included in data that’s pulled, but campaigns named

test_campaign_br_travel_x_24

or

live_campaign_br_x_x_24

will not be included.

1b. (optional) Configure the THRESHOLDS tab

THRESHOLDS defines the thresholds for aggregated search term metrics to pull data for.

The Metric column indicates which metric to compare (Impressions, CTR, etc.).
The Condition indicates the type of comparison (>, <, =, etc.).
The Value indicates the value of the comparison.

This is best demonstrated with an example:

For example...

For a THRESHOLDS table setup like this:

Metric	Condition	Value
Impressions	>	1000
CTR	<	5

Only search terms with more than 1000 Impressions AND less than a 5% CTR will be included in the pulled data.

Before moving on, copy the ID of the sheet you setup. This is displayed in the URL between /spreadsheets/d/ and /edit/. For example, this is the TEMPLATE sheet’s ID:

A screenshot of the URL of the Alli SQR Automation - TEMPLATE sheet.

2. Setup your Marketplace App

Install the Alli SQR Automation directly, or navigate to Alli Marketplace and search for the app.

Search Marketplace for the Alli SQR Automation App

Click the ➕ to install it.

Template Variables

Use the available Template Variables to configure where data should be pulled from.

RUN_MODE: Choose between Pull Data Only or Pull Data + Push Changes. This determines whether the app should push changes defined in the Add and Negate tabs. When set to Pull Data Only, the app will entirely ignore the Add and Negate tabs.
ACCOUNT_ID (optional): The ID of the Google Ads account you’d like to pull data for. If not included, this will default to the same value as the PARENT_ACCOUNT_ID.
PARENT_ACCOUNT_ID: The ID of the parent Google Account to to “login” with. This is also called the MCC or Account Login ID.
GOOGLE_SHEET_ID: Paste the ID of the sheet you setup in step 1 here!
SLACK_CHANNEL (optional): The Slack channel to send a notification to when new SQR data is pulled. Make sure the “Alli Insights” bot has been added to the channel, and invite @pmgadmin if the channel is private.
PEOPLE_TO_NOTIFY (optional): The email addresses of people to notify when new data is pulled.
DATE_RANGE: The date range to pull data over. Use the dropdown to select a range between Today and Last 30 Days.

Workflow

Now that the app is setup, you can choose a schedule for it to run on. Each time the app is run, new search term data will be pulled according to your settings in the app and in the sheet. Then, any approved changes in the Add and Negate tabs will be pushed to platform. Here are some quick points that may help your workflow:

Create + reorder columns

Since this runs out of a Google Sheet, feel free to create new columns to take notes or reorder the columns. In the Add and Negate tabs, columns with grey headers will be ignored. You can use this to draft changes that won’t be pushed, or to take notes for others to see.

The **Notes** column will be ignored on this example sheet

Filter + sort data

Data that has been pulled into the sheet can be sorted and filtered using Sheet’s built-in functionality.

For example, to filter for only search terms that aren’t already being added or excluded you can use the filter for the Added/Excluded column:

Troubleshooting

General - Google Ads Permission Errors

The way we pull data from Google Ads requires an Account ID and a Parent Account ID. If these are misconfigured, this can sometimes cause permission issues. Double check these values to make sure they are correct.

For some clients, the Account ID and the Parent Account ID should be the same value. If you’re running into this error and you’re unsure if this could be the cause, try setting both of the values to the value you’re using for the Account ID.

Data Push - Misconfigured Data Error

The app can only push to Campaigns, Ad Groups, or Negative Keyword Lists that already exist in platform. If some keywords cannot be pushed, make sure the destination is accurate.

Getting Help

If all else fails, submit a ticket to the help desk or send a message in #alli-search-automation