If the last post was lucky number seven, this post is unlucky number eight. WOW–just WOW am I amazed at how truly complicated royalty statements are. The deeper I got into this, the more I realized why no one has ever tackled this. It's time-consuming, goes nowhere in a hurry, and extremely frustrating.
In fact, words cannot describe how difficult this truly is—at least for someone like me who doesn't know code.
But before we begin, click here to go to the first post in the series if you're new here.
Here's the lesson I learned: Building a database is something just about anyone can do—getting it to calculate royalties on the other hand, is not for the faint of heart.
I spent the entirety of the last two days wrestling with Access's query function, and what I ultimately settled on (for now) is that because the way the royalty statements are set up, Access's main and most basic query function is little to no help when you want to start doing more complex searches.
For example, if I store all my Amazon ebook royalties on one table (which is a database best practice by the way), it's very easy to run a query on that table and return virtually any question I want, such as “How much did my short story collections sell last year?” or “How much did my space opera series make?” Dead simple. You can run queries that will be correct 100% of the time, even if the table is huge.
Where it gets more difficult is when I want to combine Amazon ebook AND paperback royalties into one query. And furthermore, when I want to start adding additional retailers into the mix…
Let me show you what I mean.
Why This is Complicated
The query below is a simple one: how many ebooks did my short story collection, Reconciled People ,sell in March 2014?
This is correct, and about as simple as a query as you can make.
Now, Reconciled People also happened to sell 2 paperbacks in March too (big money, big money). But if I ask the query to add paperbacks into the mix, here's where problems start.
In the query, I am pulling from two royalty tables–Amazon ebook and CreateSpace/KDP Print. However, the tables are not (and cannot reasonably be) linked in such a way that Access understands, without modifying them to the point where I lose efficiency.
So even if I form a correct query like this (which, based on my understanding, is what it should look like):
I get a result that looks like this:
The table thinks that I sold 4 copies of the ebook and paperback editions, and it keeps wanting to join ebook sales with paperback sales, which is wrong. Even if I link the tables and change the join types, I can't get the query correct.
“Excuse me waiter, can I get a tall glass of NOPE?”
That won't work.
What (I think) is happening is that Access wants to join rows together because that's how it thinks. It wants to group everything together because the data is similar.
The problem is that royalty statements are aggregations of rows that are quite similar. If you sell one copy of the same book on each of Amazon's different marketplaces, many of the data columns will have the same values–the only thing that will be different is the royalty, currency, and marketplace. Therefore, Access has problems with that.
If you look at a table of ALL your royalties over time with one retailer, a LOT of the rows will be exactly the same (1 copy of x book in the US at X royalty). Access doesn't like that.
Imagine how much of a train wreck this would have been if I loaded all my royalties in. That's why I'm working with very small data sets right now.
The Cure (for Now)
Tom whipped up a nice macro that combined both the ebook and paperback query into one, which worked very nicely.
Another way I found was to create a “Union All” query that basically did the same thing, which involved some basic SQL coding (very easy).
I also added a field for the retailer type which should make running queries a little easier in the future.
This screenshot is for all my book sales in 2014, but you get the picture.
In order to solve the problem, I had to start thinking about the “Royalty” column as a representation of ALL royalties for a given book and not for one format (ie. ebook royalty, paperback, royalty, etc.). That may be problematic down the road if I want breakdowns, but that's what the Access Reports are for…and there's always Excel.
Here's the architecture I ultimately have to build in order to get everything to work:
- I have to clean up the royalty statements from each retailer to import them into the database. Each retailer has its own table. Amazon, for example has 4 (ebook, paperback, KENP, and Audible).
- I have to then take the most important fields off the individual retailer tables and spin them into a separate query. The only drop-dead fields I need from each statement are month/year, book title, units sold (net) marketplace, royalty, and currency. This condensed table will hold sales for ALL RETAILERS.
- Whenever I want insights for my sales, I have to query from the condensed table, not the individual retailer tables, unless I need something very specific.
- The condensed table is my first port of call for any query I need to make.
Here's a visual of what it will look like.
Yeah, it's a little complicated…but not hard now that I know what needs to be done.
The Next Hurdle: Conversion Rates
For those of you who are eagle-eyed, you'll notice in the last query screenshot that I still have a major problem I need to solve—conversion rates. One of my sales transactions was in GBP.
Amazon stores the conversion rate in a separate report that details when you get paid…
That begs some other complicated questions:
- What's the best way to link the royalty table to the conversion rate without doing it manually every time?
- How do other retailers handle conversion rates?
- How can I ensure that when I run reports, I'm looking at the most accurate conversion rate as possible?
Amazon applies the conversion rate at the MARKETPLACE LEVEL and not the book level, so even if I apply the correct exchange rate to a book's royalty amount, due to rounding I'll never ever be looking at the 100% true royalty amount. But it'll be close.
That my friends, is the battle I'll be fighting next. Wish me luck.
