Yesterday was a gigantic victory in this experiment. I can't overstate how BIG it was.
Click a macro. Fix your royalty reports. Import them CLEANLY into Access without any fuss.
Is it really that easy?
Well, click here if you're new here to start reading Part 1. Otherwise, hang onto your hat. We're going to get VERY technical.
I'm going to detail my journey to import all of my 2014 sales into Access. But if you can, take this quick survey as a favor to me.
Round 1: ACX Royalties from 2014
I hit a minor snafu right out of the gate with ACX. The earliest ACX royalty statements didn't work with my macro for some reason. I didn't sell very many audiobooks until around 2018 so I made the decision to just enter these manually until the macro works.
The ACX report is perhaps the most unique of the bunch in terms of formatting—there are a lot of merged columns and conditional formatting that I personally think are over-complicated.
So ACX was a bit of a wash. The macro will work as I advance through my statements, and it works for royalties TODAY, which are substantial, so I still call it a win.
Time Spend: 20 minutes, mostly because I tried to fix my macro but couldn't.
Estimated Time Spend Next Time: Now that I know what I'm doing, it will probably take me about 10 minutes.
Round 2: Barnes & Noble
I screwed up my B&N macro it turns out.
Access doesn't like columns that are formatted as “Percentage” in Excel. Well, Barnes & Noble has two fields on the royalty report that are oddly formatted. For example:
- Royalty % (usually 40%)
- Royalty Per Unit (usually 40%)
But B&N expresses the royalty as a decimal vs. the traditional 40%. So I told my macro to change it. Mistake. I had to leave the report as-is and then change the Access field settings to get the decimal percentage to show.
Also, another quirk about B&N reports is that they don't have a “Marketplace/Territory/Country” column because all the sales are in the US. That will create a problem down the road if I don't “force” a marketplace column, so I created one in Access and defaulted it to US. This way whatever I import into the program will automatically have “US” as the marketplace. Magic!
Once I fixed that, the spreadsheet slid in like butter!
Time Spend: 30 minutes, again due to fiddling with my macro
Time Spend Next Time: 5 minutes
Round 3: CreateSpace
No issues at all with CreateSpace importation. Slid in like butter, with some asparagus on the side (forgive the tortured analogy—I'm having fun over here!)
The Createspace Report matches the format of the KDP Print report (sometimes), and the “Transaction Type” field (which is usually the royalty percentage) is blank.
CreateSpace never published an official royalty rate, but when I calculated it, it was always in the ballpark of 35%, give or take, so I just had Access default 35% into that field, and boom. Done.
Time Spend: 20 minutes
Time Spend Next Time: 5 minutes
Round 4: KDP Old Report
The KDP Old Report slid in like butter. No problems whatsoever, which is a miracle given all the time I put into it. I didn't have to make any modifications.
Time Spend: 5 minutes
Time Spend Next Time: 5 minutes
Round 5: Kobo
Kobo was easy too, though I had to re-do it a couple times because I needed to change a few field names.
One thing I did across the board was name all of the columns that will go on the condensed table with the same names:
- Retailer
- RoyaltyMonth
- RoyaltyYear
- Title
- Marketplace (some reports call this country, location, etc.)
- UnitsSold
- Royalty
I did this to make my “union query” easier later. More on that in a minute.
Time Spend: 20 minutes
Time Spend Next Time: 2 minutes (again, Kobo is really easy)
Round 6: Smashwords
“He's coming around the final lap!”
Smashwords went in like butter too!!!
The Smashwords report is the quirkiest of the bunch, honestly.
By the way, did you know that if someone promotes your book on Smashwords through THEIR affiliate link, their affiliate commission comes out of YOUR book royalty….?
Not sure how I feel about that (not typically how ebook affiliate commissions work….), though I don't sell many books at Smashwords for it to really matter, I suppose.
In any case, it's a fun little report that went in like butter. Thanks Smashwords for making this so easy!
Time Spend: 10 minutes (due to my error)
Time Spend Next Time: 2 minutes
And BAM! First Year of Royalties are Imported!
How Long Did It Take?
Total Time Spend for my First Run: 105 minutes, or 1.75 hours
Total Time Spend Next Time for my 2015 Royalties: 29 minutes, or 0.48 hours
Add in the macros and the minor corrections here and there that will always come up plus new retailer reports that I didn't upload yet (I started selling D2D in 2015 and PublishDrive in 2018 for example), and I'm probably looking at about 45 minutes to import an entire year's worth of royalties into the database.
A task that used to take me AT LEAST 2-4 hours per month now takes me 45 minutes.
That's what I call efficiency, baby!
And a couple reminders:
- Once the data is in the database, it's IN the database. No additional work needs to be done.
- The time spend is roughly the same no matter how many books I sell in a month. If I sell 10 books, it takes the same time to process as if I sold 10,000. That's the biggest problem with doing royalties manually.
- I do very little data entry other than filling in the Retailer, Month, and Year on each royalty report, and then autofill it down the rows. Oh, it's such intense labor!!
Next Step: Create the Condensed Table
As I mentioned in a prior post, I am uploading ALL of my royalty information unadulterated, but I can't run queries on the full tables. They're too diverse and varied, but I have them if I need them for more complicated queries.
Instead, I need to pull the “main” fields from all the tables that they share in common and use those to create a “condensed” table that I can query from.
This can be accomplished in Access using a “Union All” query. Essentially, it ties a bunch of queries together into one as long as all the columns and order of the columns are the same.
It looks like this:
And when I was done, I got this beautiful result (on the first try, at that):
Granted, the table isn't sorted, but it's got ALL RETAILER TRANSACTIONS from 2014 listed. How cool is that?
Oh, also, this table will update and refresh automatically whenever I import new royalties—I don't have to ever touch it again if I don't want to.
And yes, I'm sharing a screenshot of some of my sales because it's chump change. You'll also notice that I have very high amounts of units sold but almost no royalties. That's because the table combines free and paid units together.
I consider a free book as a book sold, but some people may not. That's just a philosophical thing, I guess. Also, the only way to separate this out on the Amazon reports is to filter by Transaction Type, a field that is not common to the other royalty reports. So I said screw it.
With the condensed table built, now it's time to figure out how to link it to my Books form.
Can it be done?
“I'm glad you asked me that question, because yes it can, young man. Yes, it can.”
Showing Sales on the Books Tab
I showed you in very early posts the Book Form where I add my books to the database. I can now link sales under the “Sales” tab, which has been my dream all along.
Below is a rudimentary representation of what this can look like:
Granted, I admit there are a few problems with this layout:
- I think charts are the best way to convey the information quickly, but I'm not a big fan of the Access charts. Excel does it better.
- I hate, hate, HATE pie charts that don't show the label on the chart itself. This is going to look ug-lay with my later books, but again, not much I can do.
- I do wish that I could display the cold hard numbers along with the chart, but if I had to pick, I'd rather see the chart.
You may also notice that the format chart is by retailer and not format. Oversight on my part.
Format is a vital number to know, so I simply added a default column to Access that defaulted the book format to ebook, paperback, or audio, etc depending on the retailer. This will populate the same way every time when I import sales in.
Amazon Ebook will always be “Ebook”, Kobo will always be “Ebook”, ACX will always be “Audiobook”, so I see no reason to manually add this information via the macro. Just let Access handle it, like so:
I simply added “Format” into each retailer on the union query. So easy even Michael La Ronn can do it. Took me 5 minutes!
Now, I can display a better looking and more accurate “Format” breakdown:
How to Be Bad is ebook only, but for future books, the pie chart will show the difference between ebook, paperback, and audio. Cool!
In my opinion, lifetime units sold, lifetime royalties, marketplace breakdown (which includes ALL retailers by the way), and a format /retailer breakdown are more than the “basics” of how your book is performing. Most of my questions about my book sales start here, and then I can jump off into more complex stuff.
Don't forget, I can also run deeper queries, use the Access Reports feature, and even export the data into a spreadsheet to do far more with it. I just need sales in the Book Form for the basics.
It's not like I am restricted from the data. I have more than I'll ever need.
A Recap of Everything I've Done So Far
This series started with a simple question: What if I could build a database that housed ALL the information for my books?
Using Microsoft Access, I built a relational database that has:
- My book metadata (title, price date published, ISBNs, links to all retailers, etc.)
- Updates to my books and WHEN I made them (cover, editing, interiors, book description, etc.)
- ALL of my book sales information
Since Access is a little scary behind the scenes, I built a front-end dashboard that lets me:
- See all the information in a visually-appealing manner, with the use of tabs
And if I NEED to go behind the scenes:
- I can ask the database virtually any question and get answers immediately
- I can ask really unique questions that you can't ask anywhere else (ie. “what were my combined audiobook sales for both Audible AND Findaway Voices in Great Britain last year?”, or “Which books did I update my book descriptions for last quarter?” or “How profitable is my android series, with a year-to-year breakdown?”
If I can dream of a question, I can now use the database to answer it.
No vendor or service currently allows you to have ALL of this information at your fingertips. Not one.
Sure, this isn't perfect by any means, and building it is NOT for the faint of heart.
But what could YOU do with this information?
Maybe you only have a few books today, but what about in 10-20 years when you have as many or more than I currently do?
Dream with Me for a Moment
The answers to all the questions below are an affirmative “Yes, I can”, by the way. But humor me:
TODAY:
Dream #1: What if I could store your tax expenses in the database and export it to your account every year in a single click?
Dream #2: Are you a tradpub author? Well, you can use this too. Talk about complicated royalty statements!
Dream #3: What if I could import all of my Amazon Ad information into the database and use it to glean insights and store information on my keywords and campaigns…?
Dream #4: What if I could connect my AMS campaign information to my books and have my “series” profit & loss update automatically so I could see it under my sales tab?
TOMORROW (ANYTIME BETWEEN NOW AND THE NEXT 10-20 YEARS)
Dream #5: What if a new book retailer or distributor pops up and you need to get all of your books onto the service? What if instead of uploading everything directly, you could reach out to the retailer's sales department and send them a export from your database that has all of the metadata they needed, so that they could plug your data into theirs and bulk upload your books…?
Dream #6: What if Amazon or one of the other book retailers say one day, “We'll allow you to connect to our API to download data about your books, maybe even information about your reader conversion rates…?” And what if you could connect, download the information and then sync it up to your existing sales data? What if Amazon or Kobo or Draft2Digital introduces a new tracking feature in their royalty reports and you want to check it out and compare it to your existing sales data?
Dream #7: In a growing world of artificial intelligence and data mining, wouldn't it be interesting to run your database into an AI to see what kind of insights it can give you? After all, this is a relational database we're talking about, and I'd wager that you and I are only able to see about 1-5% of the true relations. An AI could do it better. The more data points we have over time, the easier it is for AI to find trends and connections we didn't even know existed. (It's a pipe dream right now, but it's coming).
The New Swiss-Army Knife for Authors
This database makes me:
- More organized
- More tech-driven
- More data-driven
- More efficient, with less time spent on royalties and data entry and more time on strategy
- More lean, and less reliant on a hodge-podge of different data services to get my data
And I did all of this with a tool that was already available to me.
I didn't spend anything other than about a week of my time to learn the tool and apply it. I've got a lot to learn still, but this is pretty damn cool for someone who knew nothing about databases until 2 weeks ago.
Sometimes in business, you have to make do with what you have. Paying for insights or services is too expensive, so you have to be creative and work within the confines of your resources.
I can't honestly think of a better example of this in the writing world.
We're just about to the end of the road here. I have some additional things I will build, but this blog series has gone on long enough. I'll probably do a digest version for people who “just want the facts”.
I Need a Favor
I want to know if this is something people would find useful. Take this quick survey to help me gather some data.
And thanks for reading this. I hope it's been interesting and engaging!
