Solution Recipe 14: How to backfill Back in Stock with Google Sheets

Solution Recipes are tutorials to achieve specific objectives in Klaviyo. They can also help you master Klaviyo, learn new third-party technologies, and come up with creative ideas. They are written mainly for developers & technically-advanced users.

Note: We do our best to make sure any code and API references are accurate and current when this is published, but you might need to update code and it’s always a best practice to leverage our latest API versions. If you have questions, feel free to hop over to our Developer Community.

Introduction

Back in Stock notifications are hugely important in today’s retail environment as they signify that your product is in such demand that people want to be notified as soon as they are able to purchase it.

Due to the importance of these subscriber lists, it is imperative that we handle them with care. During any transformation project, be it migration to a new platform, or introduction of new markets or changes to the data model, data consistency is key.

Here we will look at how to create a Google Script to push data from a Google Sheet of Back in Stock subscribers into Klaviyo by utilising the Back in Stock API.

Background

In order to import BiS (Back in Stock) into Klaviyo, it is important to understand how BiS works and its components:

Custom catalog

Within Klaviyo, custom catalog items are stored as either ‘products’ or ‘variants’, depending on your integration method. The catalog becomes ‘inventory aware’ once inventory data is added to the catalog feed.

Back in Stock settings

When the inventory level changes for a the subscribed item, a trigger is fired, and the flow will be initiated for that item. This in turn will then follow the rules defined in the Back in Stock settings for your Klaviyo account.

Subscribing to Back in Stock via API

Typically, BiS subscribers are subscribed to the variant item, as this is the physical item that will maintain inventory. The subscription event occurs through an API call:

curl 'https://a.klaviyo.com/api/v1/catalog/subscribe'
--data 'a=AccountID&email=test@klaviyo.com&variant=5000149172256&platform=api'

Scenarios

Scenario 1 — The migration to Klaviyo

The business is looking to migrate to Klaviyo and has amassed a list of Back in Stock subscribers for their products. The business wants to bring these Back in Stock subscribers into Klaviyo to utilise Klaviyo’s powerful BiS flow based upon stock changes.

Scenario 2 — Product restructuring

The business has recently undertaken a product restructuring exercise, and as a result, many product and variant SKUs have changed. Given that the existing list of BiS subscribers are subscribed to now ‘defunct’ SKUs, the business would like to migrate them to the new SKU structure so they can ensure uninterrupted delivery.

Ingredients

• A catalog synced to Klaviyo with an inventory field
• Google Sheets
• Google Scripts

Instructions

Step 1 — Organise your subscriber data

As mentioned above, profiles are typically subscribed to the physical variant item, the one that maintains the inventory levels.

Within your Google Sheet, the following columns will be required:

• email
• variant
• synced

Now, you will need to rename the Sheet tab from Sheet 1 to BackInStock.

Finally, you’ll need to add all your subscribers.

Step 2 — Prepare your catalog feed

Assuming that you are using a custom catalog feed, the variant items will need to ensure that they are inventory-aware.

You will need to ensure that there is an inventory quantity, and an inventory policy flag set for each item.

The inventory quantity will contain the stock level

The inventory policy flag determines how Klaviyo will handle out of stock items. A 1 means that the item will not appear until the product is back in stock, even when out of stock, whereas a 2 means that Klaviyo can still use the item in product blocks and recommendations.

Step 3 — Map the catalog data within Klaviyo

Now that you’ve added these new attributes, you will need to adjust your catalog feed mapping within Klaviyo.

It’s important that these new fields are mapped to the correct Klaviyo attributes so that they can be utilised for BiS.

To do this, you will need to navigate to Products -> Custom Catalog Sources, then locate your feed, and select Update.

One the subsequent mapping screen, you will need to map your newly created attributes with the attributes $inventory_quantity & $inventory_policy.

Once updated, hit save and re-sync the feed.

Step 4 — Configure BiS trigger and flow rate

Klaviyo’s Back in Stock Settings allows you to adjust under what level of stock the BiS will trigger, and the flow rate at which people should be notified.

This can configured within Settings -> BiS settings.

Step 5 — Add a Google Script to push the subscriber data to Klaviyo

In your BackInStock sheet, select Extensions -> Apps Script

Now paste the following script:

//Pull environment variables
const public_key = "PUBLIC_KEY";

function seedBackInStock(){
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BackInStock');
    const data = sheet.getDataRange().getValues();  
    const headers = data[0];

    const emailIndex = headers.indexOf("email");
    const variantIndex = headers.indexOf("variant");
    const syncIndex = headers.indexOf("synced");

    //Throttle that can be lowered if you're getting errors with large volume of subscribers.
    const reqsPerSecond = 50;
    
    //Restricting to 20,000 URL Requests/day as per https://developers.google.com/apps-script/guides/services/quotas. If you have a Workspace account, this can be increased to 100,000.
    const maxHTTPrequests = 20000;
    const numRows = data.length-1
    if(numRows>maxHTTPrequests){
      alertMessage('Too many rows. This script supports up to 20,000 rows');
    }else{
      data.slice(1).forEach(function (row, index) {
        if(row[syncIndex]!="synced"){
          Utilities.sleep(1000/reqsPerSecond);
          subscribeBis(row[emailIndex], row[variantIndex]);
          let cell = columnToLetter(syncIndex+1)+(index+2);
          sheet.getRange(cell).setValue('synced');
        }
      });
    }
}
function subscribeBis(email, variant){  
    const options = {
      'method': 'POST',
      'payload': {
        'a':public_key,
        'email':email,
        'variant':variant,
        'platform':'api'
      }
    }
    UrlFetchApp.fetch('https://a.klaviyo.com/api/v1/catalog/subscribe', options)
}

function columnToLetter(column){
  let temp, letter = '';
  while (column > 0){
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function alertMessage(message) {
  SpreadsheetApp.getUi().alert(message);
}

Replacing PUBLIC_KEY with your accounts Public Key

Give it a name, and save it.

Step 6 — Run the script

Ensure that the seedBackInStock function is selected, then hit Run.

If this is your first time running this script, you will be asked to provide the Script permission to access your Google Sheet.

Select Review permissions and login to the correct account and grant access.

After this, the script will complete.

When you return to the Google Sheet, you should see ‘synced’ status appear in the ‘synced’ column:

That’s it!

BONUS — Make a button!

To make this a bit more re-usable, you can create a button to run the script.

To do this, click Insert -> Drawing

Then create a Button shaped object, and add some text:

Next, assign the seedBackInStock script to this button by tapping on the three dots when hovering over the button image:

And enter seedBackInStock

That’s it. You can now tap this button, and it will perform the sync!

Learn more

If you’re interested in learning more about Klaviyo’s developer experience and capabilities, check out developers.klaviyo.com!