For the past six years (2010-2016), Tea Chai Té had been using Volusion to run it’s ecommerce site. But with the move to Shopify, it meant we’d lose all our customer history – data that I’ll be using grow our company. So before we shut down Volusion, I exported all of the data I could get my hands on without hiring someone to run SQL queries for me.

One of the Volusion reports was Order History. This export shows the details of every order: Who ordered, when they ordered, how much (in dollars) they ordered, the shipping cost, and how they paid. What it doesn’t show is what they ordered, but that is for another post.

But this report is valuable because it allows me to see how many orders a customer made and how much they spent. A great way to see who our top customers, how often they order, and their average order size.

But the problem is that all of this data is spread out.

So I started with one goal: Sort the list so that I can see what customers have placed the most orders.

To do this, I opened up my spreadsheet (I’m using Google Sheets), found the “Customer ID” column and sorted.

Looking at the green “Cust ID” column, you’ll see that customer #1 made two purchases. Customer #6 made two purchases. Customer #7 made seven purchases. Customer #8 made three purchases.

 

I didn’t want to manually count and enter the number of purchases made for every customer ID, that would just take too long.

With the sheet sorted by the “Cust ID” column from smallest to largest, I added a column to the right and named it “No of Purchases.”

In that column I added the following formula:

=COUNTIF(I2:I1994,I2)

This formula tells us how many times that customer ID displays in a range of cells, in this case, from cell I2 through I1994. The second “I2” is what customer ID to look for. I pasted that formula in cell J2 through J1994.

 

As you can see, if we look in column “I” we see there are two customer IDs of “1,” which is printed out in column “J.” For customer ID of 7, we see there are seven purchases that have been made. And for customer ID of 8, we see there are 4 purchases.

At this point, there are still a lot of numbers to look at, but we are going to ignore it, because we can now see who are biggest customers are.

To do this, I sorted column “J” from largest to smallest (in Google Sheets it is “Sort Sheet Z –> A”) and I am left with the following:

 

We can now see that Cust ID 898 has made 36 purchases (there are 35 other rows above this one, but I scrolled down so you could see the formula at work), Cust ID 887 has made 20 purchases, and customer 124 has made 18 purchases.

 

And again, as I scroll to the bottom, I am able to see who only made one, two, or three purchases and target them in a different way.