Analizzare la Customer Base da Shopify con GSheets e Data Studio
Segment Shopify Customer Base using Google Sheets e Google Data StudioAfter 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…
Quest'articolo è disponibile anche in italiano

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.

A modal window will open, showing some options.

  1. Select the option for All orders.
  2. 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.

Modal – Export orders

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.

Exported excel file of Shopify orders

The file consists of 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.

Delete the unfilled values

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.

Fields to keep
Field Description
Name Transaction ID
Email The customer's email address.
Paid at The date when the payment was captured for the order.
Total The total cost of the order.

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)
}

–– Use only if you're really confident with data formatting

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

Dates Normalization

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.

RFM analysis applied

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.

RFM analysis on Google Data Studio
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.

Data filtering

Here you'll find the Data Studio dashboard, which is a good starting point for building a more functional 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: