Today was all about loose ends and learning. I lost data somehow, so I had to rebuild a couple tables again, which was a pain, but I got it done and still had a very productive day. Fair warning that the second half of this post is a lot of text (and not as many pics), but it's important.
If you're new:
First things first, I forgot to show off my book search field! Ooooh, shiny! The first picture is what it looks like if you click inside the search box.
The next image is what it looks like when I start typing. It automatically filters based on what I type, making finding books a BREEZE.
Imagine how cool this will be once I load ALL my books in? The image below is what happened when I typed in two characters: “Re.” It brought up my book, Reconciled People:
Next, I got my Unique Identifier table working:
This will be VERY important later when I get to sales, because I'll need the unique identifiers so I can tie them to the BookID.
Next, I had an idea to do a marketing/promo tab where I could keep track of marketing/promotions for my books. I haven't done this in the past but I think it could be a useful feature, especially once I tie in sales:
The Make Or Break Moment: Tracking Sales
Now comes the really fun part, and the part that makes me shake in my boots a lot: figuring out how to import my royalties into the database.
Not only how to import them, but how to do accurately, quickly…and AUTOMATED!
Why is this scary?
Every retailer is different. Every retailer offers a different spreadsheet in a different format. Each spreadsheet has different columns.
Is it possible? Can it be done?
First, I said a little prayer and promised the big guy upstairs I'd clean my room every day if I could get this right. 🙂
And then, by amazing luck, I spoke with one of my readers/listeners Tom who is a data analyst who works with Access every day in his job. Just goes to show you that you never know who is in your audience!
Tom and I had a great chat, and he showed me a lot of NINJA tricks that I never would have figured out on my own. First and foremost, he confirmed that I was building my database correctly and that I had a solid foundation.
He also confirmed that not only would it be possible to import royalties into the database, it would be easy, and very easy to automate.
Let me say that again: I canv import my royalty spreadsheets into Access, automate the process, and virtually do nothing.
Imagine with me for a moment how powerful that could be.
Imagine getting your royalty statements, throwing them into a folder on your desktop…and then Access reads that folder regularly, calculates all your royalties, adds them to the database…with only minimal work on your part?
Holy %$%…99% of authors I know are A) using spreadsheets to track royalties and pulling their hair out 2) paying someone to do it and spending a lot of money or 3) not doing it at all because it's a PITA.
This solves a business problem in our community that has existed since the beginning of time.
Huge thanks to Tom for showing me the way here. We had a great conversation and I walked away with a good sense of the direction I needed to go.
It's the Data, Genius
First, since Amazon reports are the ones I check the most and because they are the most complicated, I started with that.
I studied the Amazon royalty spreadsheets closely. If you've ever noticed, Amazon technically has 3 different royalty reports.
The first was pre-2016 (called the “Old Report”) , and it's a dumpster fire of a spreadsheet that groups your books by marketplace. It only includes ebook sales.
Here's my very first “old report”–from my golden month of publishing–January 2014–when I published my first book. Royalty of $5.79!!!
Not only is this hard to read, it's hard to import into anything because the data is so messy. Breaking out “Totals” via spreadsheet rows instead of columns is almost never a good idea. This spreadsheet is a data person's worst nightmare (in my opinion–I'm not a data guy, but hey).
Amazon also has a separate detail report for CreateSpace data prior to the KDP Print transition, and another, more modern spreadsheet that includes ebook, paperback, and KENP royalty information (called the “new report”). The “new report” spreadsheet is actually quite nice, and much easier to work with from a data perspective.
Because I want to build a comprehensive Amazon solution, I also took a look at ACX royalties, and I found something very unique there that I never noticed before.
Every audiobook has another unique ID called a “Royalty Earner.” That makes 2 unique IDs for Audible–one that exists in the hyperlink of any audiobook on the site (useful for other reasons) and the Royalty Earner, which is tied to a book (which means I can tie it to a Book ID).
Audible royalty reports are a special kind of awful due to the fact that customers can buy A La Cart (ALC), with their listener credits (AL), or as part of a subscription but not with a credit (ALOP). Each of these carries a different royalty rate.
The plus side to ACX royalty reports that I honestly never noticed before was that it does give you a breakdown of how each title sells in different marketplaces. That means I can tie ebook, paperback, AND audio sales to marketplaces, giving me more flexible reporting (ie how many books in ebook, paperback and audio did I sell in Germany last year)?
I may need to break each Audible tab into its own unique report, which will take me some extra work.
Standardizing Data
Step #1 is to figure out how the royalty worksheets work, and then get them into Access. Amazon will have 3 tables–one for ebook, paperback, and KU. i may even have to create a separate table for the “old report”. If the old report is too difficult (and it may be), I may just enter my 2014-2016 royalties manually and call it good.
I recall seeing a service on the Internet that calculated royalties for you—all you had to do was send them your spreadsheets, and they surcharged you for every “old report” they had to work on, because it was such a PITA. Naw, dawg—I don't have that kind of time or calories to burn on that report. Nope.
Audible will also have 3 tabs–ALC, AL, and ALOP.
This way I can store the data properly and as close to how they are natively generated as possible.
As I look to the future, APIs and such, that just makes the most sense to try not to butcher the data if possible when importing it.
The only problem is that the spreadsheets themselves are not very clean, even the best of them. But one of the ninja tricks I learned is that if you convert the XLS file to a CSV, it goes into Access 1000% cleaner. Yes!
Once I import a spreadsheet, I need to be able to import the NEXT MONTH'S data into the spreadsheet cleanly without any errors.
Fortunately, that's doable. With Tom's help, I was able to successfully import one of my Amazon royalty statements into Access with just a few clicks. WOW!
(I don't have any screenshots yet but I will with my next update).
Anyway, today was more of a philosophical “can I do it” kind of day. And it's absolutely amazing to know that I can.
This is going to improve the value of the database 100x when I start tying sales into the equation.
Why Sales are Important
Reason #1: They're a major pain to calculate.
Reason #2: It takes a lot of time and effort to calculate them.
Reason #3: Most authors (myself included) don't have 100% accurate sales numbers because of the time it takes. Our time is best spent writing…but we can't neglect our business.
Reason #4: Being able to see your sales and massage the data is just the tip of the iceberg. Want to know what your sales were last year? OK.
What about for a particular series? OK.
What about a marketing promotion you did from 01/01/2020 to 02/01/2020? Was it effective? How many books did you sell, and where? How do you know that the sales you made weren't just organic?
What about a new cover? Are you seeing the same level of sales or more since you updated it? What about that book description?
How profitable is your book? In other words, how much did you pay to create it, and how much money has the book earned? What's your ROI?
These are all questions that can be answered with simple queries in Access. The trick is to get the data in cleanly and accurately.
This next update may take me a while longer because I don't want to show you anything half-baked until I know it's accurate, but rest assured I'm cookin' up something good….
Speaking of cooking, it's time for a midnight snack. I've earned it.
Don't forget to join my Fan Club. It's a pretty happening place these days.
