Automate Search ads based on inventory using BigQuery & Google Apps Script

Everybody knows the holy grail of Search ads: the more relevant the better.

However, even though we all want to lead people to specific product pages for specific product queries, none of us like to monitor hundreds of SKUs landing pages daily to check for out-of-stock (OOS) products & pause the ads. To solve that problem, Brainlabs has a great script that works by scanning the landing pages’ source code for phrases signaling issues, like “out of stock” or “currently unavailable”. However, as we tried using that script, we found out that our web page structure keeps changing, and as of now straight up we don’t have such phrases in the source code for the script to scan & find!

After going back & forth the 3rd time trying to make this work, Anaïs Ghelfi asked me, “why don’t you just check stock level directly from the database itself instead of the web page?” Turns out, that’s possible (❤)

The Steps -

So I start writing my own scripts with the following steps:

Below are the steps in a little bit more details:

The script is below with a few notes:

3. Pause ads with OOS products using Google Ads Script:

Now that I already have the list of active URLs + the lookup result against product inventory, the next script will scan through that list and pause ads that have “bad” URLs (URLs that don’t belong to any available products)

How I would improve the scripts further

This is one of my first Google Ads Script project ever, so at that point I haven’t learnt how to use functions properly.

Hence, the most obvious improvement to this process would be to combine an Out-of-stock check with a Back-in-stock check into 1 script, using 2 functions: one function to export active product URLs into 1 sheet and another function to export paused product URLs into another sheet. Similarly, we can also pause Out-of-stock ads and reactivate Back-in-stock ads in 1 script using 2 functions.

Further thoughts

Being able to access Big Query and pull queries’ result directly into Google Sheet proves to be a huge game changer (thanks Anaïs Ghelfi!). With the rich info from BigQuery, the potential is not only limited to inventory-based ad management, but we can also build out ad customizers based on other business data (eg. sales, prices, reviews etc.). It also allows us to fully control, edit & customize our product feed for both Google & Facebook dynamic ads without the need to bother our tech team.

Furthermore, beyond just Google or Facebook ads, using Google Apps Script to query data into GSheet has opened up endless possibilities. Whether it is to create an easy and automated way to share simple data tables with the general users (eg. a table of best selling products in the past 30 days), or to help automating our A/B testing process (here), we simply can’t remember how life was before Apps Script :)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store