Lucky number seven, baby! I've got a GOOOOOD feeling about this. In this post I'll detail out the steps I followed so that I can keep a log of what I've been doing to track sales and import them into the database. But before we begin:
Part 1 Part 2 Part 3 Part 4 Part 5 Part 6
It's All About the Sales
As I mentioned yesterday, I started with Amazon. KDP has 3 different royalty reports.
I didn't want to, but I started with the “old report”, and it actually wasn't that hard to import in. The downside is that I had to massage Excel a lot first.
Here's that nasty, nasty report again. I had nightmares about it last night:
See all the circles and arrows? Those are problems with the data layout. As I've shown you, Access likes columns. More specifically, it likes clean columns and clean rows. This report disperses all the data using rows, which makes importing a COMPLETE MESS.
The solution? Clean up this spreadsheet like a store in a shopping mall going bankrupt–EVERYTHING MUST GO!
It only took a minute or two, but this is what the spreadsheet looked like when I was done with my very first month of publishing:
You sure do clean up good, “old report!”
Note that I added a column for “Marketplace.” I had to do this because I deleted the marketplace info—the way it's laid out was a big contributor the messiness of the report. I also had to add a column at the very end for currency.
I also had to add a column for month and year, unfortunately.
Now, with the exception of Net Units Sold or Borrowed (a relic of the old days when readers “Borrowed” KDP Select books, before KU), I have standardized the data between both the old and the new report.
Step 2 was to save this as a Comma Separated Version file, and NOT as an Excel workbook (huge thanks to Tom for teaching me that). By changing it to a text file, it made importing so easy it wasn't even funny.
AND…I was even able to save an import style that will recognize the “old report” the next time I upload one.
Here's what it looked like Access when I imported it (30 seconds later):
The next test was to import the next month of sales, to see if I could get them in cleanly and accurately.
My February report was actually perfect because I had sales in the US and the UK, so I got to test adding another marketplace. Took a bit more cleanup, but I was able to slide in February.
There were a couple wrinkles that I had to smooth out, and frankly, it was like looking for a needle in a haystack:
- The Excel column names have to match the Access column names EXACTLY
- The Excel column order has to match the Access column order EXACTLY
- If there's an N/A in any field, the whole thing will error out. I had to replace N/A with a value of 0.
Ridiculous, right? Thank god I could figure this out.
The good thing about this is that I'm doing this intentionally to myself–I don't have to do this work. But I would like the old report columns to match up with the new report columns when I get to those because it will make querying easier. The reports have almost the same columns—so that's the silver lining here.
March is where it gets complicated because I released my second book–ooooh! That means I also have CreateSpace royalties! Gonna be fun…
I forgot what the old CreateSpace reports looked like—they were nice little Excel files if I remember correctly, but turns out that Amazon retooled the old CreateSpace reports to match the “New” ebook reports when they transitioned to KDP Print. YESSSS!!!!!! I can't tell you how happy I was to see this:
All I had to do was delete the “Sales Period” column and then add a column for month and year.
I did notice a problem though. The “Royalty Type” field is empty…CreateSpace probably didn't track this field so there's nothing to put there. In true KDP print sales transactions, you'll usually see 35% listed.
CreateSpace royalties were ALWAYS around 35%, so that's what needs to go in that field 100% of the time. Easy fix.
Also, the ISBN is only listed as “ISBN” and not ISBN-13. And worse, the ISBN isn't correct. Crap.
Fortunately with true KDP Print transactions, the CORRECT ISBN is listed, but it's likely a CreateSpace quirk, and a LOT of data I'll have to manually correct. I don't have to, but I prefer to have clean and accurate data, especially if the probably is only temporary in nature.
Hmmm…let's see what happened:
The import is crystal clean, which is amazing, but the ISBN thing won't do. Won't do at all. At least Access accepted the royalty type as blank.
Once I import all my pre-KDP print data, I'll have a tall glass of alcohol and go through and find/replace for my ISBNs and the Standard field. Not a huge deal as I didn't sell too many paperbacks in my early years. But it's a problem, and something to be aware of if you had a lot of sales during these years.
Roadblock
At the end of every day, I like to run queries to verify if I'm on the right track. If I run queries and 3 out of 3 come back correct, then I'm doing it right.
If I don't get that, then I figure out how to work things until I get a 100% accuracy rate.
I'm not getting accurate query results right now, which is a little disheartening, but I think the main reason is because I need to study up on queries. That will be my goal for the rest of tonight and through tomorrow. My goal is to create queries where I can return the values for both the ebook AND the paperback and get correct calculations.
It's late and my brain is hurting. I made good progress today, so I'm calling it quits. 9 times out of 10, I'll find a solution in the morning.
Don't forget to join my Fan Club. It's a pretty cool place to be these days.
