Merging Shopify's customer data with Repeat Customer Insights analysis

Repeat Customer Insights adds some powerful analysis on top of your Shopify customer data, but some customers have asked for how to combine that analysis with data that's still stuck in Shopify.

Shopify has customer data like customer addresses, phone number, and tax status which aren't downloaded or used by Repeat Customer Insights.

The best way to combine that data is using a simple merge function in a spreadsheet.

1. Export your customer data

The first step is to export your customer data from both Shopify and Repeat Customer Insights.

Both exports are in the common CSV format which spreadsheets can use.

2. Create a spreadsheet to hold the data

Next you'll want to create a spreadsheet for the data.

I'm using Google Sheets because it's easy to share but any spreadsheet should work. At the end is an example spreadsheet you can copy.

You'll want to create two sheets for the export data:

1. the Shopify export
2. the Repeat Customer Insights export

You're keeping the export data in separate to make it easy to check the data and to update in the future.

I named mine:

1. RAW Shopify
2. RAW Repeat Customer Insights

3. Copy the exported data into the sheets

Copy the raw data from the two exports into their specific sheets.

Make sure the data comes across as rows and columns and looks "sane". Sometimes a copy and paste will act oddly so if the data doesn't look right, try again.

4. Duplicate the Shopify sheet

With the exported data loaded, now copy the entire Shopify sheet to create a third one. Google Sheets calls this "Duplicate".

You'll modify this duplicated sheet so it'll act as a merged sheet.

5. Magic formula time

Now for the important part.

To merge the two exports, we're going to use the Vertical lookup formula, better known as VLOOKUP. Both Google and Excel use a similar formula.

We're going to start by pulling in the Recency Score from Repeat Customer Insight's data.

Here's the formula in its entirely, I'll break it apart next:

=IFERROR(VLOOKUP($C2,'RAW Repeat Customer Insights'!$C:$P,8,false),"")

5.1. Error handling

The first part is an IFERROR formula which we use to hide any errors.

If VLOOKUP can't find the data, it'll show an ugly N/A error in the cell.

IFERROR can hide that and just show a blank value using the empty string at the end of the full formula. This is needed because Shopify will include records in their customer export of non-customers while Repeat Customer Insights only includes actual paying customers.

5.2. Search by email address

In the Shopify export data, the email address is the C column so that's the first part of the VLOOKUP. We want to "pin" just the column with the dollar sign so when we copy this formula, the spreadsheet doesn't automatically change the column on us.

5.3. Lookup range

VLOOKUP will take that email address and search for it in another range of data. In this case, the data is on another sheet called "RAW Repeat Customer Insights".

The range includes all of the columns from C through P. They are also pinned so the spreadsheet doesn't shift them when we copy the formula.

5.4. Column that has the data we want

Next, we need to tell the formula which column of data to give us when it finds a match. These columns are numbered based on the Lookup range.

Since we want Recency, we start counting in the Repeat Customer Insights sheet until we get to that column.

5.5 Sorted or match type

Finally, the VLOOKUP formula has some final options depending on if you're using Google Sheets or Excel.

In Sheets, the last value controls if the data is sorted or not. It defaults to TRUE which we want to change to FALSE.

In Excel, this is used to set the match type: either exact or approximate match. You'll want to use the default value of TRUE here.

6. Fill the data down for the entire column

Once you have the formula entered, you can fill the formula down the rest of the column for the remaining customers.

There are various ways to do this, I typically highlight the formula cells and then the remaining ones and press Ctrl+D.

7. Check the data

With that formula entered, you should see some data showing up.

If not, double check that the email address is in Repeat Customer Insights. Shopify allows customers to be entered without having an order, while Repeat Customer Insights only includes customers who have placed an order. That means Shopify's export might have customers who aren't analyzed as part of Repeat Customer Insights.

8. Repeat steps 5-7 as needed

At this point, you've merged one column from Repeat Customer Insights into Shopify's data.

Repeat steps 5-7 for each other column you want merged.

Remember to change the column number in the formula as you copy it (Step 5.4).

Updating the data

Later on you might want to update the data.

You'd repeat the export steps above (#1, #2, and #3) but you'll want to delete the existing data before copying it into your main spreadsheet.

Then you'll want to also copy the new Shopify data into the merged sheet (#4). That will save you from having to re-enter the formulas.

But you'll still need to Fill down the formulas (#6) for the newly added customers.

Deeper analysis

This showed you the process to merge Repeat Customer Insight's deep customer analysis with Shopify's data.

To take it one step further, you can start to filter and slice-n-dice the data to define customer segments, geo-target customers, or anything else your heart desires.

For that, the auto-filter and standard filter functions of a spreadsheet will be your friends.

Example sheet

I've gone through this process in an example Google Sheet.

While you can use it as a starting point, it's probably better used as an example of how the formulas and data is organized.

With this you should have everything you need to combine Shopify's customer data with Repeat Customer Insights analysis and segmentation.