31 Aug 2015
When it comes to winning and losing in business, money is the way to keep score. If you have a lot, you’re winning; if not, you’re losing.
Now, there is a legitimate moral debate about the role money should play in our lives. Recently, Marcus Persson, the Swedish programmer who invented Minecraft and sold it to Microsoft for $2.5 billion (and pocketed $1.3 billion of it), sent a series of messages via Twitter revealing his despair over getting everything he wanted.
In fact, there’s research shows a person’s happiness rises with his income. Once it reaches $50,000 to $90,000 a year, the amount of happiness one feels levels off depending on where you live — and even declines after that (possibly because people at that level are in high-stress occupations such as CEOs, A-list movie stars, and billionaires). That same research also shows that while your perception of happiness rises, your emotional health doesn’t. In other words, getting rich just gives you more room to unleash your craziness.
So, yes, you can’t take it with you, and the best things in life are free, but there’s a big difference between answering the question “How can we afford to eat?” and “Where shall we have lunch?”. Until we get to that level, we have to watch our nickels and dimes.
When I started publishing books, I did it using my word processor. At the beginning of each month, I’d open all my accounts and write down the number of sales each version sold and the royalties I could expect to receive. When I was done, I’d add up the amounts and record it at the bottom of the page.
This was fine when I was publishing one book. By the time I got to number five, the process became complicated. There were sales from the sites that sold the EPUB version, such as Smashwords, Lulu, Nook, and Google Play. But Lulu also sold books to the Nook (yes, I accidentally had two versions of the same book selling there) and Apple’s ibookstore. And the Kindle sold books in 13 markets, and I was receiving reports for my trade paperback versions from CreateSpace US, UK, and EU. Did I want to break out all these figures, or just record the totals?
I didn’t know, and I didn’t know who to ask, until a blog called The Passive Voice came to my rescue. When the site linked to a post by Jessi Gage about her royalties, I left a comment:
If an author wants to write a post that will probably be widely distributed, how about one on how you keep track of sales?
I’m struggling with that now. Currently, I’m using a Word file and listing that month’s income and expenses, and smaller charts breaking down sales and income for that month by book title.
Now I have an Excel spreadsheet that I’m building slowly, but organization’s not my strong suit. I’d love if an author posted their ideas.
Another blogger suggested to the site owner that this might make an interesting post, so he reprinted it.
And Eric Lorenzen stepped in and saved my bacon. But before I get to his solution, let me bring up two popular alternatives recommended by several writers that have the bonus of not requiring you to learn Excel.
If the prospect of using Excel makes your toes curl and your fingers to cramp, there are two alternatives out there that are worth looking into:Trackerbox (StoryBox Software): Written by author Mark Fassett, Trackerbox ($60) runs only on Windows boxes. It promises to import sales reports from 14 sites (including Amazon’s Kindle, CreateSpace and Amazon Vendor Central), lets you run simple to complex reports (e.g., multiple titles, authors, and vendors), export reports in a variety of formats and sort the information just about any way you like. Several commenters at The Passive Voice recommended it highly, and comes with a 45-day free trial. At the end of the period, a purchase gives you a key that unlocks the software. Fassett has released two minor updates to the software in 2015, so it appears for now he’s dedicated to maintaining it.
BookTrakr: This free service has been in beta for a couple years. It promises to not only check your sales daily at Amazon, CreateSpace, Nook, iBooks, Google Play, Smashwords, and Kobo, but also gather your reviews and send you a daily report.
The Excel Solution
Lorenzen recommended using an Excel spreadsheet that he developed, he published it on his website as well. Anyone can download it for free.
It’s a beautiful piece of work.
This is not the place for a tutorial on using Excel. If you don’t know how to use it, don’t download Eric’s spreadsheet and learn along the way, unless you have a lot of time, are very patient, and have something like “Excel for Dummies” on hand (or your Google-Fu is sharp).
But at some point in your indie career, assuming your sales rise, you or someone you know will have to learn how to use Excel or one of its clones. Spreadsheet software is a necessary part of probably every business out there, and once you get into the habit of using one, you’ll see why.
Lorenzen’s spreadsheet breaks down your sales by book and market. Each page is devoted to a single book. On that page you record your monthly sales, royalties, and number of free books given away. The last page automatically totals the sales of all your books, so you can tell how well each book is doing, as well as your overall sales. He even put in a section that totals your sales for each month, both for each book and overall.
Once you understand how it works, tweaking it to meet your needs can be a challenge. It’s important that each cell serves the same function. Cell B2 must always record the number of sales in January for book #1; cell C2 the royalties in January for that book; and D2 the number of free books given away that month. That way, the last sheet (called “SALES TOTAL”) will add up the right data. If you insert a line on one sheet, the same line must be inserted on all of the rest (and the “SALES TOTAL” sheet must record the data for whatever you put there).
Excel can help you along the way, by copying and adjusting the forumulae as you’re inserting or deleting lines and columns. But always double-check what you’re doing.
Since I acquired Eric’s spreadsheet, I’ve changed it to suit my needs. Here’s what I’ve done:
1. I moved the CreateSpace line below Amazon MX, and added lines for the UK and EU markets (titled CreateSpace US, CreateSpace UK, and CreateSpace EU). This put all of trade paperback sales together. (I also included all of my bookstore and direct trade paperback sales here, too).
2. Because I used Lulu for other stores, I broke out sales to the ibookstore and Nook as well.
3. I created a new spreadsheet called Sales By Title, that totaled the print and ebook sales, earnings, and copies given away for each book.
Now, there’s a little trick to doing this right. The SALES TOTAL totals the numbers assigned to each cell, right? It does that through the use of a range. For example, if you published three books, you’d assign an Excel page to the sales for each book. They’d be arranged so that their tabs would read, from left to right, BOOK1, BOOK2, and BOOK3. To the right of the BOOK3 tab would be another tab called SALES-TEMPLATE. This is the blank form that you would use to create a new book (called, for lack of imagination, BOOK4).
(Why wouldn’t you just rename SALES-TEMPLATE as BOOK4? Because what would you do if you publish BOOK5? You’d have to duplicate a used page and tediously zero out all the cells.)
Why do that? Because to get the totals on the SALES TOTAL page to add up, a formula will use a range from left to right. I won’t give you the formula, but in essence it is saying “add the number in Cell B2 from BOOK1 to SALES-TEMPLATE and put it in Cell B2 of SALES TOTAL
This is why you want SALES-TEMPLATE to be the last page before the SALES TOTAL PAGE: It means that you won’t have to rename all the formulas. With each new book, all you’ll have to do is duplicate the SALES TOTAL page (which is blank) and move it to the left. The formulas will still correctly add up your sales and you’ll still have a blank page to copy from for future books.
Now, remember the SALES BY TITLE page? I made that up special, and I placed it to the right of the SALESTOTAL tab. And there it will stay, recording the sales for that year (oh, I should have mentioned that Eric’s spreadsheet will only record the sales for one year. So unless you create a master blank spreadsheet that’s filled with zeroes, I guess you’ll have to resave the current year’s spreadsheet for the new year, and figure out how to zero out all of the cells after all).
A lot of work, right? It was, but the cool thing about it was that, once it was done, I could kiss all my math goodbye. I just had to enter the right number of sales in each box, and I knew at a glance which books were my best-sellers, which formats were popular, and what books were popular in which format. And it looked cool, too.
This spreadsheet also allowed me to travel backwards in time. I created files for the previous two years and entered my sales there, too. So now I have several years of data, including sales totals for each month and each year, and moving forward, I’ll be able to see what my highs and lows in sales were, and whether certain books were growing in popularity or fading.
So whether you’re solution involves a spreadsheet, software, or an abacus, a record-keeping system should be in place to track your book sales.
I’ve had my say, so what do YOU think? Comments and suggestions will be appreciated.