Get powerful Etsy sales data for free using pivot tables

Microsoft Excel is probably the most underused office tool gracing the home computers of thousands of Etsy sellers. Even many people in office jobs are unfamiliar with some of its more esoteric features. It’s an astoundingly powerful analysis tool given the right data, and luckily Etsy gives us this data.

This article is a simple introduction to pivot tables, using Etsy sales data. If there’s enough demand, I can do a follow up article to show how to slice and dice your data in different ways to answer different questions.

This is not going to be light reading, sorry. It involves a few complicated steps as I’ll have to partially teach the concept of pivots tables too, but it should be worth it if you stick with it.

  1. Preparation

First you’ll need to get your data out of etsy. Go to Your Shop, Orders and scroll to the bottom to see a link that says ‘Download a CSV’. Click that.

On the CSV page, you’ll see it split into 2 main sections – current listing data (which we’ll ignore for the moment) and Orders. Inside orders, there are 4 options, for now we’re interested in the Order Item CSV which shows individual items sold. Select this, and then your date range. I tend to go for a year at a time so I’ll leave the month blank here and select 2016. Once you hit ‘Download CSV’ Etsy will complete the extract and start downloading the CSV file.

Etsy CSV download screen
Etsy CSV download screen

 

Sidenote: CSV stands for ‘comma separated variable’ and is a standardised format for exacting tabular information into what is effectively a plain text file – it puts a comma between each field (note that if the data itself contains commas, such as your titles, then it uses quotation marks to identify these as being separate). You don’t really need to worry about anything in this paragraph, excel will handle it just fine, but it’s a useful bit of information if you decide to start digging deeper into this field.

 

Security warning!

A note on privacy at this point. I’m going to show you my data in this tutorial, but not my genuine data. I’m going to obfuscate it, which isn’t as rude as it sounds, but will stop you getting any insider information on my own personal shop, and will also hide my customers’ names and addresses which saves me from being prosecuted under the Data Protection Act. Now, in reality I’m not really fussed about my own data, but if you sell something easily copyable then be very careful who share any data with from the point you hit ‘download’ on that CSV file onwards. This is your core business data, guard it appropriately.

In the upcoming extract screenshots you’ll see my product titles replaced with “product#1” through “product#46” and customer names and addresses and listing IDs replaced with ‘Anon’. When you get your own data you’ll see the full titles which can look a bit messy, so you might want to do a find and replace on the whole spreadsheet once you open it in excel to replace the long titles with short titles to make it easier for you to read. We could do clever things with the vlookup function, but I’ll save that for another article.

 

  1. Import to Excel

The file you’ve just downloaded should open in just about any version of Excel released in the last 20 years. If you’re using an earlier version than this, then kudos for the retro tech, but you’ve got bigger problems to deal with.

Most people will be able to simply double click the file just downloaded and it should already be associated with MS Excel (it might already have a mini green X logo in one corner of the file icon). If it doesn’t open, or if you get prompted to choose a program to open it with, you’ll need to tell it to open with Excel. There are lots of youtube videos and articles showing how to do this.

We now come to a somewhat faffy problem, at least for UK sellers, since Etsy’s data is in US date format. This is going to seem a bit complicated if you’re not familiar with Excel, but stick with it.

  1. Select the entire column of dates which are in mm/dd/yy format (US format) by clicking the column header (column A).
  2. Under the DATA menu, select “Text To Columns” (we’re not going to actually split these into separate columns, but excel buried a useful formatting feature in this tool)
  3. Select ‘Delimited’ and click Next
  4. UN-tick all Delimiters (tabs, commas, semi-colons etc) and click Next
  5. Select column format “Date: MDY” and then click Finish (this tells excel what format they’re currently in) and click Finish.
  6. You’ll probably find Excel has now automatically switched this into UK format, but if it hasn’t you can now go into the format menus and switch it from US to UK.

 

Screenshots of tool options
Date Format conversion using the Text to Columns tool

 

The beauty of doing it this way is that it’s preserved the ‘date’ attribute which means we can very easily group it into months and years later on. Trust me, this is invaluable.

It’s worth saving the excel doc at this point so if you mess anything up you can come back to a fresh copy with the date already converted into the correct format. You’ll need to save it as an excel workbook rather than the CSV it started out as, otherwise you won’t be able to save any of the interesting stuff we’re about to do.

 

  1. Your first pivot table

Pivot tables are hard to explain, but easy to show. So let’s get started with a really simple one that counts how many of each product you’ve sold that year.

Go to Insert > Pivot Table and then just hit Ok to the next prompt and it will open a new sheet and bring up a strange looking pop up menu (which may be anchored to the right hand side of the screen) with 4 boxes underneath a list of all your column headings from the data you imported.

The four boxes are Filters (which we’ll ignore for now), Columns, Rows and Values.

Let’s ease into this gently. From the list of fields at the top, find ‘Item name’ and drag it into the ‘Rows’ box. You should see the left side of your screen suddenly become a list of item names.

Now drag the same Item name from the top into the ‘Values’ field. See how it’s just added a second column into the bit on the left? You’ll also notice that when you dragged it into ‘Value’ it changed its name slightly and become ‘Count of item name’ which, as the name suggests, means it did a simple tally. Later we can change this to percentage, or average, or several other variants, but let’s keep it simple for now.

Screenshot of Excel spreadsheet with pivot table
Simple Item Count

Up to this point we’ve only used a simple one dimensional pivot table. But let’s add a second dimension in. Go back to your list of columns, and scroll to find ‘Country’ (if you only sell to one country, then use ‘City’ here instead). Drag this to the ‘Columns’ box and watch the magic happen – see it pop out a load of new columns, one for each country (or city) and tally the quantity of each item sold by country.

Excel screenshot of a pivot table
Two dimensional pivot showing item name totals split by country

Mind. Blown. Right?

Now let’s get to the good stuff.

Clear the row, column and values box (by dragging the items back up into the top list), and then find Sale Date and drag it down into Rows again.

Uh oh. We have a ton of rows here, especially if you’re a busy shop. And we have one for each order, meaning that if we had several sales per day, we’ll see that date several times.

But this is why we faffed about with MDY date formats, because now I’m going to introduce you to Excels Grouping tool. Can you contain your excitement?

Right click on one of the dates, and look for ‘Grouping’ (Windows) or ‘Group and Outline > Group’ (Mac). You’ll now get an option to group the dates by Days, Months, Quarters and Years. Let’s go with Months for now. You should see the dates roll up into calendar months.

Now we’re going to do something different, and switch months to being columns, by dragging sale date from Rows to Columns.

Screenshot of excel pivot table dialogue
Moving months up to columns after grouping

Why didn’t I just drag Sale Date to columns?Because Excel won’t let me put it there straight away, as it has a maximum* number of columns (but not rows) so we have to put them into rows first, then group into months, and then move to columns. I know, I know. Welcome to Excel. But I’m not going to teach you SQL server or BI analytics so we have to work with what we’ve got.

(*to be honest, if you only have a couple hundred orders, it will probably fit into columns straight off, but I’m used to working with 100k or more rows of data and got into the habit of doing it in rows first).

Now drag item name into Rows again, and drag the same Item Name into values et voila – a report on how many of each item sold by month.

Excel screenshot showing 2d pivot table
Sales per item per month

By now you should be able to start to see the kinds of reports Excel Pivot tables can give us from the downloadable data.

 

  1. Getting really deep into pivot tables

I’m only going to touch on this briefly, but we’ve only covered two dimensional tables so far. You can also nest fields in both rows and columns. For example if I wanted to list quantities of item but subdivide by variation and also plot this by month, I’d keep the example above, but then find ‘Variations’ in the field list at the top and drag this too under ‘rows’, underneath Item Name (if you don’t have any listing variations, then you can see a similar effect by dragging Country or City here instead).

Note – in this next example I’ve also applied a filter to remove products that don’t have variants. I won’t explain it here, but there’s tons of info on how to apply a filter to pivot tables.

excel screenshot of pivot table
3d pivot showing items per month by listing variant

Now you can see items and their variants, by month. If you have the same variants across multiple products (eg. you offer the same selection of envelopes across multiple cards, or the same selection of chains and straps across multiple necklaces) then try swapping the order of fields inside the Rows box. It will now order everything by variant first, then list the item name underneath.
This is the beauty of pivot tables, it allows you to rapidly summarise and combine your Etsy sales data to get meaningful information from it. Have a play, and see what you come up with.

Leave comments / suggestions and correction below, and if you’re in the market for a new Travellers Notebook refill, my wife’s shop has just started listing these!

You can find her shop at: https://www.etsy.com/uk/shop/notesandclips

product shot of foiled travellers notebook refills
Foiled travellers notebooks available at NotesAndClips on Etsy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s