PPC Script Checking Flatlined Accounts

grumpyppc_script_checking_flatlined_accounts_caption

In about two months we’ll all hit that lovely time of the year called “shit, I should have bought the gifts like 2 months ago”. PPC and October-December season is a love&hate relationship. It’s the beautiful 3 months where you can bring your company or client the income totaling the other 9 months of the year combined.

read time ~ 7 minutes

We’re all paranoid here

As any decent PPC-specialist I’m clinically paranoid.

What if I accidentally left a rule that increases bids till eternity?

What if my shopping campaigns stop running due to issue in merchant center?

What if google randomly disapproves all my search ads in one big account? (The last one is sadly true story… ? )

That’s why in Google’s Script Center one can find scripts like “Anomaly detector” because if some shit is happening – WE’D LIKE TO KNOW! When it comes to THE season we especially would like to know because any mild malfunction of campaigns costs so much more these months.

As I work in-house, I’m surrounded by lovely colleagues who are as paranoid as me and have various alerts and reports set up to be notified if something unusual presents. It’s actually quite enough, but since I like digging in occasional scripting why not set up an alert that would help where other methods fails. This is how I came up with a script that would hourly check if my accounts are alive and running.

I’m not going to share the final code of the script, but I will share the steps that will help you set it up for yourself. Try to make a script for yourself from it or just use it as an inspiration how to think about your alerts and notifications.

How to tell an account is offline

Specify the dead man – What makes us know that the account is severely not ok? I don’t mean regularly declining CTR and such. I mean the real deal – client forgot to top up the budget, server issue made all ads disapproved, there was a severe bug in GoogleAds and everything is down, conversions stopped being recorded on the website.

Hence, I came up with 2 main things I should know about:

1. My impressions went to zero.

2. My conversions when to zero.

So impressions are dead…

I love impressions report because it’s so immediate, it’s not like conversions where you need for conversion to be reported with a time lag, no, impressions are just there for you to be downloaded every hour. This part doesn’t limit script in any way. We can download a report every freaking hour.

So conversions are dead…

Conversions are harder. Unless you’re working for a huge retail it’s likely that your conversions are as precious as gold. Conversions don’t often happen non-stop, people have awake-patterns when they register, buy or leave lead on your website. More over there’s a reporting lag and conversions lag that can be very different for various accounts. What I mean is that conversions can’t be checked every freaking hour mostly.

In case of Notino (join our team!) I made a compromise to check only biggest accounts where even 1 hour downtime is big-big money and only in the hours when majority people don’t sleep. Of course including a reporting time lag of 2 hours. So around 9:10 i’ll check conversions between 7 and 8 which are reported as “7” in hours segment.

But how am I supposed to solve it with MY accounts? Well, I’m glad you asked.

If you have small account or big lag in conversion reporting, consider using Automated rules on MCC level.

Select accounts you need, few biggest campaigns and set up email you around 2p.m. if there wasn’t yet a conversion today. Or something similar that works for you.

grumpyppc_automated_rule

Logic of flatlined accounts script

1. Get me a report from my accounts segmented by hour.

This can be done with a simple report baby-code below:

var account_iterator = MccApp.accounts().withCondition('LabelNames CONTAINS "SOME_LABEL"').get();

var sheet = SpreadsheetApp.openByUrl("YOUR_URL_TO_SPEARSHEET")
.getSheetByName('SOME_TAB_NAME'); 
sheet.clearContents();  

while (account_iterator.hasNext()) {
var account = account_iterator.next();
MccApp.select(account)   

var report = AdWordsApp.report("SELECT Impressions, ConversionValue, HourOfDay " +
      "FROM ACCOUNT_PERFORMANCE_REPORT " +
      "DURING TODAY");

report.exportToSheet(sheet);}

2. Check if any account qualifies to be reported.

I did it in the way of formula in spreadsheet. Let’s look at Impressions flatlining part.

=sumproduct((impressions=impressioncell)*(hours=(timenow-1))*(accountname="ACCOUNT_NAME"))

impressions – is column with reported impressions
impressioncell – is benchmark, is this case 0 (zero)
hours – are hours column in report, our segment
timenow – is a google formula that reports current time in hours =TEXT( NOW(), “HH”)
accountname – is column with account names from report

What this formula does is it will give you “1” if all those conditions are matched.

For example:

Are my impressions reported zero? YES.
At what time? Now is 14:10 so at reported segment at 13 there were none, so YES.
Is the name of account in report same that I want to be reported in this cell? YES. Then “1” it is.

3. Filter only accounts that have “1”.

Select only the accounts that qualify to be notified and send there name to the dashboard tab of the spreadsheet.

4. Code in the script checks if there’re accounts reported and if YES send notification.

In the actual script code you can make it check if cells have data and send email only if they do.

var ImpressionsEmaildata = SheetWithData.getRange("RANGE_WHERE_ELIGIBLE_ACCOUNTS_ARE_BEING_REPORTED_TO").getValues();

var ImpressionsEmailrow = 0;
var ImpressionsEmailcol = 0;

      if (ImpressionsEmaildata[ImpressionsEmailrow][ImpressionsEmailcol] === "" || 0) {
        Logger.log("Nothing to send, all good"); }

else  sendEmailImpressions();

5. Schedule script to run hourly.

6. Receive email and get a heart attack.

Some fun technical mentions

1. Time formula from Google doesn’t like to have a format and work as a normal number.

=TEXT( NOW(), "HH")

Which can be solved by doing *1 to it.

2. Also time formula from Google doesn’t refresh itself – you need to check the settings of your speadsheet.

grumpyppc_spreadsheet_settings

3. Some of your accounts are not in the same time zone so you might want to adjust

(hours=(timenow-1)) into (hours=(timenow-2))

for United Kingdom if you’re in Central Europe etc.

4. Well… you need to know when your conversions happen. If normally you for sure get conversions from 11a.m. to 3p.m., but on weekends not so much, you’ll be spammed on weekends… So check your data with hour segments in normal GoogleAds interface.

       

If you enjoyed this article please share it

You can also thank me by buying me a cup of coffee or following me on twitter.
My self-esteem needs more worshipers.

or even join our PPC team!

One Reply to “PPC Script Checking Flatlined Accounts”

Leave a Reply

Your email address will not be published. Required fields are marked *