Make/Integromat Case Study: Web App to Simplify Management of PPC Shopping Feed

Problem

A client needed to simplify the management of their massive Google Ads shopping feed. The client is running this setup:

  1. They have around 300,000 unique items in their feed.
  2. In reality, there are “only” around 12,000 unique physical products spread over 23 different combinations of e-shop domains and countries.
  3. The client needs to frequently push or pull certain products based on their IDs. The pushes and pulls usually happen across more shops and countries. So say a specific model of a TV needs to be pushed across 5 shops and 2 countries.
  4. The client has assigned their products with different “bidding labels” within the shopping feed. These labels are then used in Google Ads for allocating the products to a correct campaign with a correct bidding strategy. There are 5 different bid strategies based on different performance indicators. Each product can be member only of 1 campaign = 1 bid strategy.

The assignment was to:

  1. Allow client’s inhouse team to quickly move products between the “bidding labels” within the product feed => these moves are then automatically translated as moves between different Google Ads campaigns with appropriate bidding strategies thanks to applied filters in the shopping campaigns.
    1. So for example, if a product moves from label “F” to label “A” in the feed, it will be moved from a campaign with the lowest bids to a campaign with highest bids.
  2. And of course, the process must be simple, quick, and manageable by non-technical people.

Solution

After lots testing and trying, we decided to proceed with 4 tools:

  1. Google Sheets
  2. BigQuery Downloader for Sheets
  3. BigQuery
  4. Make (formerly Integromat)

The feed updates are happening according to these steps:

  1. Every morning, BigQuery Downloader will download the latest feed from BigQuery to the spreadsheet.
  2. A PPC specialist then goes and selectively assigns products with 2 types of scores (SEASONAL, POLITICAL). It does not matter what these scores mean but the important factor is that these scores have power to change products’ bidding label.
  3. The second step for the specialist is to decide to which shops and countries to push the latest changes.
  4. Once the PPC specialist is done with making edits, he pushes the updated feed from the spreadsheet back to BigQuery via a simple web app running on Make. The “web app” means a few Bootstrap pages accessible via browser running via Webhook modules in Make.
  5. Once the updated feed is in BigQuery, all products’ label are recalculated based on the latest inputs coming from the spreadsheet.
  6. Finally, recalculated feed is pushed from BigQuery to Merchant Center by client’s IT team.
  7. Products are then automatically moved between campaigns in Google Ads based on their “bidding label” coming from the feed.

Let’s look at some samples now. This is how the simplified feed looks in Google Sheets (notice the last 2 column where POLITICAL and SEASONAL scores can be entered):

01 - Managing Shopping Feed with Make

This is where the specialist select which countries and shops are affected by the most recent feed change. Once the selections are made, the specialist can initiate the upload process by clicking the link in the first row:

02 - Managing Shopping Feed with Make

The link will initiate the web app running via Make:

03 - Managing Shopping Feed with Make

After selecting the user email, the web app will generate a preview of the changes where the specialist can also add a few notes about the changes. Once notes are entered, the “Upload Now” button can be clicked:

04 - Managing Shopping Feed with Make

Once the upload to BigQuery is finished:

  1. There will be a confirmation screen presented.
  2. A confirmation email will go out to the specialist.
  3. The changes will be logged into the spreadsheet so it’s quickly visible who did what.

At this point, you might be wondering about the whole Make magic which is powering this solution. It’s just ONE scenario:

05 - Managing Shopping Feed with Make

I am not going to describe each module from this scenario but let’s just focus on the main trick. The trick here is to use the Webhooks>Webhook Response module to generate simple HTML pages which the user sees when he or she goes through different stages of the upload process.

So the first module is receiving all the button clicks, then the first Router is routing the user to the right “screen” (=route) based on the incoming parameters while performing certain tasks in the background (such as the upload to BigQuery in the route at the bottom). At the end of each route (=once all the tasks are finished), the already mentioned Webhook Response module is used to generate a HTML page which is returned to the user (the first route = welcome screen, the second = preview screen, the third = confirmation screen after the upload). This HTML content is presented as an ordinary looking web page.

Yes, the scenario looks crazy complicated but the idea is simple.

Once the product feed data is in BigQuery, there are still some steps to be done – mainly reshaping the simplified feed into feed format accepted by Merchant Center via multiple SQL views. Once these transformation are done, a client’s script comes and queries the latest feed with the latest bidding labels and pushes everything to Merchant Center.

Conclusion

This project certainly falls into “special ops” category because it was far from trivial. We did face technical challenges (freezing Google Sheets due to storing too much data) and also business process challenges (how to best enter the push and pulls on scale?).  The key takeaway here is once again the power of Make – not only it can seamlessly move data between thousands of systems but you can build even simple web apps with a little bit of HTML knowledge.

What about the client? Well, client’s PPC staff can now change bidding for specific products across multiple shops and countries within MINUTES. Not hours, not days, not weeks. Minutes. And all that with no dependencies on other teams.

And finally, when you think about the whole idea of “bidding labels”, it’s quite simple too.  The client uses labels for allocating products to campaigns with different bidding strategies and the “only problem” is how to change these labels on scale within the shopping feed with hundreds of thousands of products. And that’s where the solution presented in this article comes in.

Are you facing similar issues?
Or maybe you need to address offline conversion uploads?

HIRE ME

Leave a Reply

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