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:

1. Pull relevant product data from BigQuery, including stock level, into a Google Sheet using Google Apps Script + SQL
2. Pull active product ads’ URL slug from our Google Search campaign, using Google Ads Script, and use the URL slugs as the identifiers to look up product data from the table generated in step 1
3. Pause ads with OOS products using Google Ads Script again

Below are the steps in a little bit more details:

1. Pull product data from BigQuery: please visit our awesome Data team’s blog for the super detailed “Ultimate Guide to Integrate A BQ Query Into A GSheet”! Do remember to set up time-based triggers (eg. daily run) so that the sheet will be refreshed automatically with accurate data.
2. Pull active ads’ product URL slugs:

The script is below with a few notes:

• line 9: I already labeled ads that need checking (basically evergreen product ads)
• line 10 + 11: my filter because I want to check only enabled ads that have some traffic (clicks)
• line 21 + 22: “cleaned” the URLs by keeping only the product part & remove the variants behind (ie. I check stock at product level, not variant level)
• line 25: while exporting the URL, I also included the vlookup formula to look up the Inventory sheet (where I already pulled product data from BigQuery in from Step 1)

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 :)