Spreadsheets for the New Year

As you all probably know, I'm a spreadsheet geek.  On the personal side, I have spreadsheets for the books I've acquired and for my weight and activity (where I also keep track of writerly things like word counts and pages edited).  I do spreadsheets for the payjob.  And I have spreadsheets to keep track of things for the writing business.  Those last ones are the ones I want to talk about today.

I have a whomping huge spreadsheet to keep track of my expenses and income.  All six years worth.  That one's particularly ugly this year, so we won't really talk about it much.  When I finish updating it, it will tell me how far in the hole I am and whether I made any progress anywhere.  The closest any of my books is to being out of the hole is Accidental Death.  It's only $102.51 in the negative.

My other two main spreadsheets are Book Sales Data and Sales Totals.  

Book Sales Data has a tab for each month of the year and on each tab, it has sales information for each book - color coded by book on the rows with columns for each possible sales item.  Each potential US price gets a column, other countries get columns (except the EU countries only have one column because they all use the euro), there are columns for KU and print and returns. This sheet is for quantities only.  I used to do quantities and amounts, but I deleted the amounts section several years ago because it wasn't necessary here.  I keep all that over in the Sales Totals spreadsheet.

The Sales Totals spreadsheet is another whomping huge one.  This sucker tells me everything - quantity, amounts, page reads, etc. on a daily, monthly, and yearly basis with comparisons and charts.  I use the Book Sales Data sheet to feed this one.  (I have to, or this one would be even huger and the weight of it would probably topple my computer.)

The first tab of the ST takes the data from the BSD.  It's a lot like the BSD in that there are color-coded lines and columns for each of the possible sales items. Rather than try to explain it, here's a screen capture of what I'm talking about.

Pretty, pretty rainbows.

I couldn't capture the whole sheet, so I just gave you a month and the quantity side.  The earnings side looks the same, except the numbers over there will have dollar signs, when I have numbers to put into it.  The colors are for more than just looks, too.  They help me keep track of things with a glance.  Dying Embers will always be that shade of blue.  If I'd thought this though way back when, series would all be the same color, but I didn't.  And it would've messed up my rainbows anyway.  

If you look beneath the rainbows, you can see the tabs to where all that information works.  The Daily is quantities of everything sold.  The Daily Pgs is for Kindle Unlimited.  Overall keeps track of everything over all the years.  Monthly breaks it down.  Pgs Read does the KU by month and book.  It's all pretty self-explanatory.  

Every time I publish another book, I have to update everything - adding rows and columns, etc. - but for the most part, this works for me all year long.  Sucking in data and presenting it in a format I can easily digest.  

Every month, I have to plug in formulas to make ST pull from BSD, but that's only because I'm too lazy to do it all ahead of time.  I used to populate the thing with formulas at the start of the year.  Then it got to be such a pain, I decided to do it month to month, as things sold.  Why put a formula into a cell if there will never be any information in that cell?  So, I stopped.

Anyway, you probably won't ever want or need anything like this.  It keeps me out of trouble and even when the sales are light, it gives me a clear outlook on where I've been and where I'm going. 

If you have any questions, I'll be happy to answer them as long as they aren't too involved like I'd be building your spreadsheet for you.  I've often thought about offering my services to make spreadsheets like these for other authors, but the sheer weight of these things and the data involved makes it less than ideal.  And the time it takes to create and maintain one would make the process costly for other writers. 

And besides, few writers are as geeky as I am.  I like knowing exactly what's going on with the business at any given time.  Even when the news is bad.  

Speaking of which, I will be doing at least one wrap-up post here sometime next week to talk about 2020 sales and junk.  See ya then.  I won't be here on Friday.

More Marketing Data

Last year, I did something a little different.  I kept track of everything related to marketing.  Duh, I know, but it wasn't something I'd thought about previously.  In the interest of looking at this more as a business, I charted the data to see where I was spending my marketing dollars, how effective each campaign was, and other junk.

Here's what I learned:

I spent $329 on ads last year.  From those ads, I made (to date because that last ad is still bringing in revenue) $491.  That's like a 33% return on investment.  From what I've read, you should be happy with about a 20% ROI on book ads, so I'm pretty pleased with that number.

The best return I saw percentage-wise was from a set of ads I did for the SCIU books with Authors' Billboard.  Three ads for $6 each netted me a 79% ROI. 

Other notable returns were through Bargain Booksy (about 25% ROI), Ereader News Today (about 50% ROI), and Paranormal and Urban Fantasy Bargains (run in conjunction with an ENT ad, so not sure about ROI).

The worst return was any time I tried to pay for ads for Project Hermes.  One of those was also with AB, the other was with Bargain Booksy.  Since I've seen awesome returns of 20% or more from ads with both of those venues for other books, I blame the book and not the venue.  It's probably the blurb.  Might be the cover.  I'll have to do some more research.

Another thing for me to consider is that I was very proactive on Facebook groups for some of my marketing campaigns, but not for others - and the PH sales were part of the 'others' group.  Also, PH is a standalone. 

One of the tanking ad campaigns was for my Dennis Haggarty books, but I did another campaign for those books later in the year that did well.  That might be timing.  I guess.  Maybe.  And, again, I might not have pushed the FB groups much during the first campaign.  Also, one of the venues I chose to advertise with was an unknown and, upon further investigation, they were mostly retired in place at the time of my ad.  (Research, people.  I didn't do mine, so I paid the price.)

The December sale for the SCIU books - which is still earning me some money - was with Bargain Booksy and a new-to-me venue called Book Adrenaline.  Right now, it's running a 25% ROI.

I have a sale set up for Sleeping Ugly this coming week - the 16th thru the 22nd.  An ad should go out in the newsletter for Authors' Billboard on the 18th.  We'll see how that goes.  I haven't advertised SU yet, so it's kind of an unknown.

I'm also going to be setting up a sale and advertising for the Once Upon a Djinn books for next month.  If I can get off my leaden butt.  Those usually do well.  I'm going to try a new-to-me venue again - Book Barbarian (if I can get a slot - they go fast).  That along with maybe something through Author Billboard and PUFB should see some nice returns.  :fingers crossed:

As with everything, your mileage may vary.  These are my experiences alone.  Market at your own risk.  And all that disclaimer, 'don't sue me if you don't get the same results' kind of verbiage.  Hell, your books could do better than mine, for all I know. 

Any questions?  Wednesday I might go over the nitty-gritty of creating advertising for these venues.  It's really not that hard, but worth talking about.

This is a Business

This is a business.

Sometimes I need a gentle reminder.  Sometimes I need a smack upside the head.  So let me say it again...

This is a business.

I tend to forget, in the course of letting my creative side run amok, that this is not about skipping rocks across the publication pond to see how many times they bounce on the surface before they sink.

Or maybe it is.

If you're into rock skipping, you know you have to pick just the right rock or it'll hit the surface with a big kerplop and disappear beneath the glassy water.  It has to be flat.  It has to be smooth.  It needs rounded surfaces to skim across the water.  Otherwise, it's a fail before you even let it go.

You also have to pick just the right day to skip rocks, when the water is kind of glassy.  Can't skip rocks across waves, doncha know.  I tend to suck at this part.  Not for actually skipping rocks, but for this analogy.  I'm really not good at knowing when the best time is to launch my rocks across the publication pond.

I kind of even suck at picking the right rocks sometimes.  DE has been a great rock.  It's still skipping right along there.  (As long as I continue to nudge it.  Helps that it has the most reviews.)  Others?  Kerplop.

Okay, I've beaten that analogy to death.  The point is this is a business.  I've talked about ROI (return on investment) from a marketing standpoint, but never from a publishing standpoint.  If a book will cost x-dollars to publish, will it conceivably make that money back?

To date, not a single one of my books has recouped the initial investment.  Accidental Death has come closest.  I'm within $100 of making a profit on that one.  But paying for marketing makes that kind of a 'one step back, two steps forward' thing.  The worst at recouping so far has been Wish in One Hand - because reasons*.  I'm still in the hole big time on that one.

So, when considering where to go with my writing and my publication schedule, I really should be thinking about how to get out of the hole (or, rather, how to not make the hole even deeper) instead of how to get all the books of my heart published.

Not sure how that will go.  Right now I suspect this writing dry spell is directly related to thinking about the business side of things.  The tiny CFO in my head is at war with the Director of Creative Development.  One wants to audit, the other wants to dance.  And the CEO is beginning to see that the DCD has had far too much leeway for far too long.

DCD:  "Don't make me look at the spreadsheet again.  Please.  It's all too depressing."
CFO:  "Look at it.  Look. At. It."
DCD:  :whimper:

Anyway, it's the start of the year.  Good a time as any to think about things, look at things, and get my mind right.


*Mainly, I paid too much for a shitty cover from a 'real artist' who was supposed to give me something beautiful and gave me, instead, something that looked like it was hacked together from bad video game screen captures.  But I launched with it anyways.  Which sucked.  Then I had to pay for a better cover so my subsequent djinn books' sales wouldn't suck.  Live and learn.

Brief Marketing Update 1

I'm doing some test marketing right now.  This post is as much about me keeping it straight as information for you guys if you're interested in marketing data.  Bear with me.

So yesterday, I had a $10 ad go out for AD.  I sold 5 books and earned just under $4. 

This morning, I posted sales links for AD to various FB groups* for free and, within an hour, sold 2 books. 

Today, two more ads go out at $6 each.  One for AD and one for NC.  :fingers crossed:


The reason the title of this post says '1' is that I'm going to try to post an update later if sales change.  If you don't see an update, there was no change.

* The FB groups posted to were: Amazon Kindle Goodreads, Indie Authors International, eBook World, Self Published Crime Fiction Writers, and Crime, Thriller and Mystery Readers' Cafe.  Since the books I sold were in the UK, I'm going to assume the Indie Authors International group gained me those sales, but I could be wrong.

Marketing Results

I know the sale isn't over yet.  You can still get Wish in One Hand for only 99c/.99p through midnight Pacific time (or Grenwich time for the UK) tonight.  But the initial results for the ENT ad are pretty much in, so I thought I'd share.

A $40 ad for Paranormal/Romance (the best category they had for WIOH) netted me 68 copies sold for WIOH, 4 copies sold of In Deep Wish and Up Wish Creek, and 3 copies sold of Wish Hits the Fan.  Which means the ad paid for itself and put a little money in my pocket.

I'm pleased with the results, but it was nowhere near the results I got when I had ads go out for DE and AD back in 2015.  Not sure if it was the book or the fact that selling books is harder now or whether the readership of ENT leans more toward mystery/suspense than toward paranormal.  Hell, for all I know, an ad on the Thursday before Memorial Day wasn't the best plan.  (They picked the day.  I would've preferred Monday - DE's ad was on Memorial Day - but it wasn't in the cards.)  So much of this marketing thing is guesswork and crossing your fingers. 

Oh, I also got last minute acceptance for an ad with Paranormal & Urban Fantasy Books (PUFB) for yesterday and today ($8 for two days).  Yesterday's newsletter didn't go out until late afternoon, but I saw 4 sales there already.  I hope today's newsletter isn't quite so late so people have time to read it and buy the book before the price goes up tonight.  But it's out of my control.

Let's hope for residual sales, both in page reads and in people reading WIOH then deciding to buy the rest of the series.  :fingers crossed:

Anyway, if you're on the fence about advertising, jump in the pool.  The water's only cold when you first submerge. 

(Administrative Note:  Blogger has decided to stop emailing me comments to approve, so now I have to come back here and refresh my panel to check for new comments.  I learned this early this morning because I had two unapproved comments waiting here this morning that had been posted yesterday.  If you comment and don't see it immediately, don't worry.  It's probably there and I'll get to approving it when I can.)

Keeping Track of Sales

It's that time of the year again - time for the creation of new spreadsheets for the New Year.  Every year, I create two new spreadsheets to keep track of the year's sales. 

The first spreadsheet does the day-to-day stuff.  It looks like this:

Of course, you see the layout for Dying Embers at the top, but beneath that is each book with its own data and color - in the order I published the books in.  (So below DE is Accidental Death and then Wish in One Hand, etc.) Each color here corresponds to the color on the next spreadsheet. 

This, of course, is the big 'Totals' spreadsheet.  This one takes all the numbers from the first spreadsheet and spits them into a form where I can see where sales are at across the board in one place.  If you click on it, you can see there are tabs for different things I want to know - Overall Data, Overall by Month, Monthly Chart, Daily (which is units sold by day each month), Daily Pgs (for KU sales), Pages Read (which totals pages by book), Pages Chart, and then the previous years' info.  (You only see the 2017 tab here, but there are tabs for 2016 and 2015 as well.)

Here you only see January and February, but all the months are done.  I've hidden those rows so I don't have to scroll down to see the totals.  If you look, you'll notice a new brown row in February for the new book I'm publishing.  I add rows as I go along.  (Frankly, this is becoming a behemoth, but what's a gal to do.  Stop publishing more books?  As if.)

I think I'm also going to hide some columns this year, because I don't sell books at $3.99 anymore and I don't sell through Createspace's Expanded Distribution.  I might hide the D2D column, too, because I haven't done that in a while. :shrug: I can always unhide any of these when/if I do need them again.

I'm not really sure how much these spreadsheets are actually helping me, but I'm a geek and I like to see the numbers this way.  I like to think it helps me track whether advertising and discounts or freebies are doing what they're supposed to do.  I'll talk more about that after the first of the year when all the 2017 numbers are in.

Well, I hope all y'all weren't too bored with this post, and that it helps someone somewhere.  If you're a writer, how do you keep track of sales?  Do you bother?  If you're a reader, do you have any questions?  Feel free to drop them in the comments.

KENP Page Count

If you haven't checked your books' number of pages (KENP - Kindle Edition Normalized Page Count) lately, do so.  As of February 1st, they changed some algorithm or something, and some books have changed size significantly.  Although Amazon said the change would average around 5% - and I heard of some that went up a little - mine all dropped precipitously. 

Dying Embers went from 541 KENP to 395.
Accidental Death went from 520 KENP to 380.
BloodFlow went from 715 KENP to 535.

Not sure what the dealie-bob is or why any of them dropped so badly.  Supposedly, it has to do with formatting and unnecessary spaces or font size or some other undefinable thing, and all my books have very simple formatting with no extraneous crap and a normal-sized font.  :shrug:

Not much I can do about it from what I've heard, and I always thought those page counts were way out of line, but it was good while it lasted.  I won't say the fundage hit won't hurt, but then again, my KU page reads have been way down since November, so it's not really effecting me that much yet. 

I do know that Accidental Death will be out of the KU program on 2/12, so I can try the wider distribution thing.  And because of this page count thing, I've chosen to take BloodFlow out, too.  It'll be unavailable through KU on the 22nd - unless something changes.  DE just re-enrolled because I wasn't paying attention earlier this month.  But then again DE is the only book people are reading regularly in KU, so I might've left it there anyway. 

Anyway, these are my experiences.  Your mileage may vary.  Good luck to you all.  :hugs:

Spreadsheets - Keeping Track of Sales

Okay, so there was a request to discuss my use of spreadsheets in this self-publishing endeavor.  For some of you, this might be a total snooze-fest.  Sorry about that.  Check back on Friday and I'll try to be more interesting.  ;o)

First, so we're all on the same page, this is Excel (2010, I think. Might be 2007.)  The files themselves are called workbooks and each individual tab within the workbook is a spreadsheet.

I have been using a separate workbook for each book.  They kinda look like this:

But opening a separate workbook for each book is kind of a pain in the ass, and it clutters the bottom of my screen. (Way worse than what you can see up there.)  Plus, toggling between all those files.  And then think about when I have 8 books out there.  Or twelve.  Ugh.

So, I made a new workbook to encompass all the books in one place.  It's my 2015 Book Sales workbook.  (The one called Sales Totals is what I refer to as the Master Plan. It's very pretty and has multiple charts & graphs.)

The 2015 Book Sales workbook keeps everything in one place. Now, instead of opening 4 separate files (one for each book and the Sales Totals), I will be able to open two files:

Isn't it pretty?

And the 2015 Book Sales will feed the Sales Totals, so I only have one place to plug in data to create pretty graphs like this:

My 'Pages Read' graph

Why bother? (Or as Hubs asked, 'why not just keep track of total book sales instead of individual books?')

Well, I'm hoping to track what works and what doesn't.  Find sales patterns and then find a way to recreate the spikes.  Look for trends and maximize them.  And I do the individual book thing because each book is different - different pricing at different times, different page counts, different genres, different marketing efforts.  :shrug:  Or, it could be that I like playing with data. 

Sales Totals, Daily Sales, color-coded for mktg efforts

As I've said before, some stuff works, other stuff doesn't - but how would I have an inkling of what worked if I didn't track it?  So I track it.  Does it help?  A little.  Down the road - perhaps more.  At least I'll have some idea of what to expect with each new release and with every subsequent year. 

I hope this helps.  I suspect at this point, it probably didn't answer the questions people might've had on exactly 'how' to do it.  That would take hours and I suspect I might not be the awesome software instructor I used to be.  Excel nowadays, though, does a lot of the work for you.  Autosum was a godsend, let me tell ya.  And being able to type the equal sign then click a cell in a whole other spreadsheet?  I could kiss someone for that.  Try typing out '[2015 Book Sales.xlsx]Monthly Totals'!$B$13 in one cell and then in the next cell that but now the B should be a C, or the 13 has to be a 14.  Ugh.  What a pain.

Now, it's not exactly what I would call easy-peasy, but it is a lot easier than it used to be.  Don't be afraid of it.  Play around.  Try some things.  But do what I didn't do and plan ahead.  Don't just think about your needs now, but what your needs might be a couple books or a year down the road.  Or you'll wind up like me - building a new workbook and then going back and populating it from the old workbooks.  It's a lot of work I could've saved myself if I'd planned ahead.  In the end, though, the work I put in now will save me time in the long run.

Any questions?  Do you keep spreadsheets?  What do use yours for?  (I also have one for expenses. I used to have a spreadsheet that listed all the books I owned - titles, authors, pub dates, etc. I still have one I use for quotes.)

Six Months In - A Numbers and Marketing Post

Well, it's been six months since I started self-publishing.  Actually, I started considering self-publishing seriously about a year ago.  And it'll be a year in November when I spent money on the endeavor.  But, for the purposes of this post, it's been just over six months since my first book hit the e-shelves.  Here's how it all breaks down.

Dying Embers - published 2/13/15

Copies sold: 442
Ebooks: 430
Print: 12

Accidental Death - published 5/18/15

Copies sold: 372
Ebooks: 368
Print: 4

Wish in One Hand - published 8/18/15

Copies sold: 12
Ebooks: 11
Print: 1

All books:
Copies sold: 825
Ebooks purchased: 691
KU books borrowed & read*: 112
Other vendor sales: 6
Print copies sold: 17

I was going to put the number of how much money I've gotten, but then I thought better of it. It's gauche to talk money, and it's really not anyone's business.  Suffice it to say, I have not cleared expenses for Dying Embers, let alone the other two books.  I'm hoping to be able to eventually fund this endeavor without dipping into living expenses. I'll be happy when I can pay back what I borrowed from the living expenses budget.  I'll be ecstatic if this ever gets to the point where I have spending money out of it.

The marketing part of this is still a mystery.  I pay for ads that bring me sales. I pay for ads that don't.  I've had free ads that made me money and others that didn't.  Making Dying Embers free didn't really do anything definitive for either sales or reviews, but it did make it available for pirates.  I discovered I moved more books on one pirated site than I've sold to date.  That was the last time I made a book free and I'm not inclined to do it again.

So far, six months in, I'm calling this a win.  Sure, WIOH started out slower than I would've liked, but it'll get there.  I'm aiming for an average of 2 books sold per day per book.  Right now, I'm at an average of just over 4 books a day. (4.07 as of this morning)

Any questions?

*I have no idea how many people actually borrowed thru KU because I don't get data until they read the book.

Self-Publishing Data - Part Two

It occurred to me sometime late Friday afternoon that there was something I wanted to put into the post that I had forgotten.  Numbers. 

First off, let me remind you that I am a total tightwad.  Also, if I didn't have Hubs behind me assuring me that the budget is there, I would be tighter with my publishing budget.

Also, since I don't like talking about money publicly, the numbers I'll be giving here are percentages of the total amount Hubs has budgeted for each book, and percentages of total money spent.

Dying Embers:

Total Spent to Date percentage of total budget - 79%

Of the amount spent:
Editing - 27%
Cover Art -53%
Marketing - 20%

With Accidental Death, I went a little different - just to see what would happen.

The early numbers for Accidental Death break down like this:
Total Spent to Date percentage of total budget - 21%
Total of projected expenditures vs Total Budget -  50%

Of the amount projected:
Editing - 44%
Cover Art - 6%
Marketing - 50%

Lastly, I have totally projected book numbers because I haven't spent a dime on Djinnocide yet.

I expect to spend 100% of the per-book budget on this.

Editing - 25%
Cover Art - 37.5%
Marketing - 37.5%

Obviously, those numbers are flexible.  I'm still not done spending on Dying Embers, for instance.  I'll do some more marketing for that when Accidental Death is closer to coming out.  And I'm not sure if I'll end up spending 50% of the remaining budget money on marketing for Accidental Death (especially since I'm only projecting to spend half of the total budget getting that book to market anyway). 

This first year of self-punishing... I mean, self-publishing... is all about seeing what works and how it works and then adjusting accordingly.  Accidental Death is coming in lower because I did the cover myself, but then again, the subject matter lent itself more easily to a self-done cover.  The cover art for Djinnocide will be going up again because graphic design with Photoshop won't create the cover I want and, while I can draw, I don't have the skill to create what I want. 

It's all a learning curve. 

I hope that helps provide some more insight for those of you out there interested in self-publishing - whether for yourself, a loved one, or for curiosity sake. 

If you have any questions, feel free to ask.  I'll answer what I can.