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.
WHAT YOU’LL LEARN:
How to use Google Sheets to backfill Back in Stock subscribers to Klaviyo as part of an onboarding or migration project.
WHY IT MATTERS:
Back in Stock subscribers have explicitly allowed you to contact them when a product that they want to purchase is available to buy. You do not want to lose these leads when migrating to Klaviyo. This guide will outline how easy it is to migrate the data.
LEVEL OF SOPHISTICATION: Moderate
There is some level of scripting required, but this guide will provide you with the basics.
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:
- 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:
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!
