How To Segment Shopify Customer Base with Google Sheets and Google Data Studio

After defining what the RFM analysis is standing for, and how you can apply it to your Customer Base, I want to show you how to apply it on Shopify orders data.

A brief introduction to anticipate some doubts, such as:

Why should we use RFM analysis?What kind of information can be obtained by using it?How do I increase the Conversion Rate or AOV (Average Order Value) using customer segmentation and RFM analysis?

I answered to some other questions, in an article that you can find it out by clicking on this link.

Let's export orders from Shopify

The data collected by Shopify are a pretty good starting point, but they need a little normalization to make them available for Recency, Frequency and Monetary analysis on excel.

In order to extract all the orders, we need to go in the backend and click on the "Orders" tab, from the side menu, and follow some steps, described below.

Use "Order" and "Filter" buttons, to filter all by Payment Status, Paid and Fulfilment status, Fulfilled (you can find a detailed instructions on Shopify website).In the Order page, click on the Export button, in the top left side.

A modal window will open, showing some options.

Select the option for All orders.In the Export as section, leave the default selection.

Now you can click on Export orders button, to export the orders we have just filtered.

Side note
The bigger the export, the more time it takes to do it. For example, an export with less than 100,000 items could be completed in less than one hour, while an export with 400,000 items could take about 4 hours. At the end of such a long export, Shopify will notify us by email, sending us a link to download the file.

Now that we have our file, let's import it into our Google Drive account, which will automatically turn it into a Sheet file.

The file is made up by informations that Shopify keeps for our store; a lot of data is superfluous for our calculation and we'll go forward, in the next step, to clean them.

Exported the data already filtered by Financial Status, Paid and Fulfillment Status, Fulfilled we'll need to delete the superfluous columns.

First, we check that all rows are correctly filled by "paid" and "fulfilled", otherwise we may have inaccurate results.

Proceed sorting from A --> Z on the column "Fulfillment Status", and delete the rows that have a value other than "fulfilled".

We delete all the columns, keeping only the fields shown in the following table, making sure that the currency field has the correct currency format.

The Email field will be our Customer ID, allowing us to display in Google Data Studio, the related emails when we'll going to filter on a specific customer segmentation.

TIPS - The selected segments exported can be used in Active Campaign to create targeted communications using email marketing automation.

Normalizing values

At this point, we need to normalize all values in the "Paid at" column, as the format exported by Shopify also handles the time zone. For the purpose of this example, one day variation can be allowed too, but if we wanted to apply the right conversion, we should apply a custom function in the Google Sheets scripts module, in order to convert correctly the date.

### Timezone conversion in a Google spreadsheet
```
/**
 * Converts a datetime string to a datetime string in a targe timezone.
 *
 *@param {"October 29, 2016 1:00 PM CDT"} datetimeString Date, time and timezone.
 *@param {"GMT"} timeZone Target timezone
 *@param {"YYYY-MM-dd hh:mm a z"} Datetime format
 *@customfunction
 */
function myFunction(datetimeString,timeZone,format) {
  var moment = new Date(datetimeString);
  return Utilities.formatDate(moment, timeZone, format)
}
```

*I suggest to use this function only for skilled.*

Going forward to the next step, we can apply the =SPLIT function, as shown below:

Apply the RFM matrix calculation, as shown in this article, where you can review all the singles steps, or if you prefer, download the ready-made Google Sheet file.

Showing data in Google Data Studio

Open Google Data Studio and create a new Report, as shown below:

When creating a new report, you will be asked to associate a data source; select the sheet on which we have worked so far. When creating a new Report, it will be shown some different data source integrations; select the sheet on which we have worked so far.

It'll create a simple table, which we'll update as following: 

The settings will allow us to view all email and RFM value for each our customers. Now, add a tree graph, that can help us to properly show how and how many clusters make up our customer database.

Add the "Tree Map" graph and set its configuration as follows.

At this point, several rectangles will be shown, representing our Customer Base clustered and sorted by size: the color becomes darker based on the sum of the emails.

The 343, 344, 545 values, represent a very specific information of our customers, as you can read from the association table, that I described in the final section of this article.

These graphs are a basic representation of our data, as you can see in the image below. Now, we can filter clicking by on these graphs, consequently updating in real time, the right side table with the related emails of the customers belonging to the clicked segment.

PRO TIP - When we normalize the orders, there were other data too that we cleaned (e.g. products number for each order, cost per product and many other data). These columns could be interesting to keep and shown in Google Data Studio, while your filtering the cluster.

In Google Data Studio, you can create a custom dashboard more suited at your specific needs.

Here you'll find Datastudio Dashboard: it's a good starting point for building a more customized one.

Articles Series

I published an article series based on RFM matrix to explain different applicable actionable tactics to grow your business.

In case you missed something, the series includes the following articles:

– Applying RFM Customer Segmentation to Your Business right now

— [ Step by Step Guide] Map Customer Segmentation using Google Analytics and Google Sheets

– Segment Shopify Customer Base using Google Sheets e Google Data Studio