Yesterday, Getty has uploaded the first sales report for iStock contributors into their ESP platform. As I explained in a blog post, it is the most terrible way to present data I can imagine. The PDF you can download contains all sales but it was probably designed in a time when the best stock photographers would get 100 sales a month, each paying $50-100, it doesn’t really make sense to go through dozens of pages for hundreds of subscription downloads. The TXT file you can download contains all the details, so there is a lot of information in there but about 80-90% of the spread sheet is unusable information, the formatting isn’t what I would call industry standards, so it isn’t really easy to get a good overview of what you sold. I tried to turn it into readable information for myself to get a better overview of what I sold and for how much.

I first used OpenOffice locally which also is free, and you can certainly do the same with Excel if you have it. A blog reader also pointed me to the site TodayIs20.com which has been created by Getty photographers for other Getty photographers to read out the sales report and transform them into more useful stats. They have taken some effort to implement changes necessary for the iStock sales. Personally, I will rely on StockPerformer to read in the data properly in a few days, so I’ll have it along with my other microstock sites – they published a first version yesterday but it seems to not be perfect just yet.

However, if you are willing to invest some time yourself, you can make something out of it. I’ll try to show you how I approached this with the help of Google Drive which is available online and free for everyone. I hope you’ll find this useful.

How to import the ESP sales data into Google Spreadsheets

1. Export the sales report as a TXT file from ESP as shown in the screen shot above
2. Go to Google Drive Spreadsheets
3. Open a new Blank spread sheet
4. In the spread sheet, go through the File – Import menu
5. Select the txt file you downloaded from ESP
6. Tell Google Spreadsheets to “replace” the current empty one with the imported data

How the spread sheet looks like when the data is imported and formatted automatically

As I mentioned at the start, the spread sheet contains a lot of data overload, so in a next step I made this more readable. For this I have started to hide all the columns that contain redundant or useless information, like the first six columns which always contain my name, contract, an invoice number that doesn’t provide any value for me.

To hide information, select the columns you want to hide, right click on the column headers and choose “Hide columns” from the context menu.

My final spread sheet

I hid all the columns AD to the end of the spread sheet and some more columns in the middle. Personally, I don’t see any value in seeing the customer name or the usage terms when trying to find out how much I sold. Certainly details that you can find some value in but for an overview I skipped those columns and just kept a total of 12 columns as you can see below:

Finally I renamed the spreadsheet on the top of it, so I can find it again next month.

Now let’s get to the nerdy stuff: Pivot tables

Once you have the data in a proper spread sheet, there is some maths magic you can make the software do. One of the great things in spread sheet software is called “Pivot Tables”. Those sort and sum up data from long lists for you, so you can easily look at the sums and breakdowns. To create a Pivot table, select the whole spread sheet (by clicking on the empty space in the top left corner, above the “1” row) and go through the Data menu to Pivot table.

Google Spreadsheets (or OpenOffice or Excel) will now automatically add a new sheet to your file, showing an empty table. Click into the table and you will see a list on the right side, allowing you to add fields to Rows, Columns and Values. When you choose a Row, the data will be sorted and summed up by the different type of values in that particular chosen column. For example, I chose “Purchase from Site” as the first element in Rows. This will automatically sort the data by the sites that were used to buy my images, Getty, iStock, Partner Portal and Thinkstock.

Add fields to the Pivot TableAt this stage, you won’t see any numbers. For this, you have to choose data in the “Values” section. It makes sense to choose “Gross Royalty in USD” as the main Value to show. Once chosen, you will see – almost by magic – the money you have made through the different sales sites. If you look again, you can also choose how those “Values” are being treated. Be default they are Summarized as SUM which makes sense for numbers. I have however added a second Value – again “Gross Royalty in USD” but you could basically choose any column there – and set this to Summarize by COUNT. This will count how many rows are being shown. Translated into our data, you will now see not just how much money you have made but also how many sales you had on each of the sales channels.

Here is a quick overview how I chose to sort data further by adding Collection and Product Type to the Rows section:

In a final tweak to this, I wanted to also show sales from different months separately since I noticed there were quite a few December sales included in the January report. I don’t know if this will be necessary for the future but at least for this time, I wanted to add the month into separate columns.  Unfortunately the sales report only contains a “Royalty Month” column (the now hidden column E) and a “Sales Date” which includes the day and would not look proper. So I have added a new column to the spread sheet right of the Sales Date column. I have called this “Sales Month”, read out the month and year from the column before but set it all to the first of that particular months. I copied this formula to all lines below, so I ended up with only two different values for December 2016 and January 2017. Now I could use this column in my Pivot Table to also add a month component to the final table:

I know this all might sound very nerdy to some of you, and it probably is. I am amazed that a company does not provide any information in a legible and usable format right away these days. But at least, the data is there and if you want to get the best out of it, I think this is a good way to go. Let me know if you have any problems with any of the steps. 🙂

Hint for non-US contributors: Your Google Drive might be set to your local language and settings. This can screw up the numbers during the import of data. Make sure to go through the File – Spreadsheet Settings menu first and turn the location to United States before you import your TXT files.