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.

Time Again for Spreadsheets

I said something yesterday about doing a post about spreadsheets over here today.  Then it occurred to me that I've probably done a similar post in years past.  So, basically, if you've heard it all before, you can skip this post.

If you've been around here long enough, you know that I'm kind of a spreadsheet geek.  (Okay, I'm a geek in other ways, too.)  I make spreadsheets for all sorts of stuff - books I acquire, weight loss, word counts, expenses...  I also do spreadsheets for work, but that's another story. 

I like being able to see the data, track the data, research the data.  Look for patterns, compare  numbers, check for errors, etc.  Whether any of this does me any good is anyone's guess.  It keeps me out of trouble.  Right now, it's the time to start working on the spreadsheets for 2020.

My two main writerly spreadsheets are for tracking book sales.  There's a small one and there's a big one.  The small one - Book Sales Data - feeds the big one - Sales Totals.

Any time I see any sales activity, I plug it into Book Sales Data.  That one has tabs for each month and on each tab, it has each book and each book has a column of dates.  It also has columns for the price it sold at and where (country-wise), pages read, etc.  So, if I sell a book today, I'd type a one in say the Dying Embers section in the $3.99 column.  Or if someone in the UK read 100 pages of Wish in One Hand, I'd put 100 in the KU UK section (which could then feed a formula in another column to give me the percent of book read).

It's all very geeky. 

Anyway, the numbers go into Book Sales Data, which then feeds the behemoth Sales Totals spreadsheet to give me yearly totals and allow me to analyze things like how many books I've sold in any given month, how much money I've made, etc.  And then compares it against previous years, etc. With things color coded by book, so I don't get lost in the data forest.

It's a rainbow! Yep, geeky.

I'm not sure I like all the colors I chose for the books, but it's there now and after all this time, changing colors would totally harsh my groove.  BOAI is brown, dammit, and it will always be brown.

I suppose this all comes from my sales/management/computer tech/data entry background.  The sales force (me) needs to know what's selling and what's not and the management (me) needs to be able to see it all at a glance, so the computer tech (me) need to put it all in a useable format so data entry (me) can input everything easily.  Umm...

I can also take all of this and be able to provide the CFO (Hubs) with answers to any questions he might ask pertaining to book sales.  He doesn't ask, but it's there if he does.  Sometimes, I just blurt out stuff like 'hey, I reached 3000 books sold this morning'.* Or I show him the graphs when compare sales by month or by year.  Because they're pretty.

Oh, to have a sales spike like May of 2015 again...

I have another spreadsheet that tracks expenses pertaining to each book, but let's not talk about that one.  It depresses me and I haven't updated it in months. I should probably do that before year end, though.  Blerg.

Now, I started doing this when I first started selling books in 2015, so it made adding to it each year relatively easy.  If you were going to do one, that's the time to do it.  If you're already years into your book sales, it can still be done, but it would be a bear to recapture all that old data if you haven't already got it.  You could start from here.  You know, if you want to be geeky like me.

And now, if you'll excuse me, I just had a geeky spreadsheet idea to fix a problem I've been having with something I do for the office.

Any questions?  Comments? 

*happened last week, but I forgot to add it to the Sunday Update.

Another Sale and NaNo Updates

First off, Blink of an I and Unequal are on sale this week.  I'm calling it The Semi-Spectacular Dystopian Sale, because I'm a dork like that.  Here's the ad image I posted to FB yesterday:

No sales yet, but I'm not really expecting any.  The last sale I had where I only used FB Groups to market was a dud.  I think it has to do with FB's new rules and algorithms.  :shrug:

Anyway, if you're into dystopian or future suspense or speculative fiction (or whatever people are calling it these days), give it a whirl.

On another note, I'm chugging along with NaNo.  I'm behind about 1200 words, but I'm totally cool with that.  The point here is for me to light a fire under my ass and get writing again.  So far, so good.  Three days in a row.  Woohoo!

Okay, prepare to groan... I made a spreadsheet for this year's NaNo.  I kinda had to because I'm not writing a new book and I'm not necessarily adding words in a linear fashion.  Anyway, it looks like this:

As you can see, if you blow it up, I started with 55876 words, so the end goal would be 106K.  It's a fantasy, so not outside the realm of possibility.  If it ends up being shorter, I won't 'win' NaNo officially, but as long as I write every day, I'll win for me.

And maybe this will lead to other goals being met and stuff and junk.  I'm not making any promises, but there's hope.  =o)

Another Marketing Post

Marketing... For indie authors, it's sort of a throw it at the wall and hope it sticks kind of thing for the most part.  I mean, if we had publishers worth their salt, we'd know what marketing worked and aim at the wall better with stickier stuff.  But we don't.  It's all on us to figure this stuff out.

So, this year, I've been trying some things to see what works for me and what doesn't.  And I'm trying to share that with all of you because we're in the same boat and what the hell, right?

One of the things I've done is tracking.  Everything.  Sales - in quantity and dollars, ROI (Return On Investment), page reads in relation to marketing...  I have spreadsheets and graphs.  It's all very pretty.

See?


We already know one of the things I've discovered and talked about ad nauseum.  Ads increase sales... err, during promos.  (Ads without promotional pricing are pretty much duds.)  I paid for ads in February, April, and May.  No paid ads in January or March.  The graph shows how all that worked out, eh?

Another thing we all pretty much know is promos increase sales quantities.  But we also need to remember that they decrease the dollar per book averages.  I know, it's sort of a 'well, duh' moment.  It's all well and good to move a lot of books at 99c (which during a promo nets an average of 65c per), but if you're not selling books at regular prices, the average tanks.  I'm sitting at an avg of $1.02 per book this year and $1.21 per over the past four+ years.

This year, I've also been tracking my book downloads and purchases.  I was doing this mainly so I'd know what books I have, what I've read, and what I didn't finish.  Also, it helps me keep track of my book buying budget.  $31.83 spent this year so far. 

But yesterday, I thought it might help with something else.

I was thinking about where to market books next month.  Should I go with Robin Reads?  Ereader News Today?  Book Adrenaline?  Where would my money best be placed?

Historical data on ROI points to ENT.  Of course.  But when I checked, I discovered my own reading habits also point to ENT.  Of all the places I've discovered books this year, ENT wins hands down.  Then Freebooksy/Bargainbooksy.  Then Reading Deals.  Book Gorilla and Robin Reads are down near the bottom.

Now, this is just anecdotal evidence.  And you have to remember, I'm downloading mostly free books right now.  And with a lot of marketing venues, ads for free books cost more than ads for 99c books.  I don't know about other authors, but I have a tough time paying for an ad for a book that's not going to make me any money.  So I assume there's fewer people who put free books in places like Book Gorilla and Robin Reads, which would lead to a lower download rate from me.  Still, it's something to think about.

Then there's the fact that some venues are harder to get advertising with that others.  I've been using BargainBooksy a lot lately because they have an easy policy and it's clear what dates are available right when you sign up - and you get to choose your date.  No waiting for someone to get back to me with a date or a rejection.  Bing bang boom.  The problem is I think BargainBooksy is almost tapped out for me this year.  I mean, both ads this year paid for themselves, but this recent one paid a lot less than the last.  Of course, that was for OUAD.  Might still be some readers who haven't seen SCIU yet.

It's all a balancing act.  :cue circus music:

Anyway, I hope some of this helps.  As always, if you have any questions, let me know.  If you have anything to add, feel free.  Good luck in your marketing efforts and I hope you sell tons of books!

Update:  I guess I wasn't the only one thinking about this stuff today.  If you're interested in more info go over to Elizabeth Spann Craig's blog where she talks about Amazon's new KDP Beta reports.

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.

Using Amazon Perks More Effectively

Okay, so if you sell books exclusively through Amazon, you know you have these perks you can utilize to help boost sales - chiefly, the Kindle Countdown Deals (KCD) and the free book days. 

Once every 90 days, you can choose to discount your books or have them free for up to 7 days.  With the discount, the days have to be in a row.  With the free, you can sprinkle them around.  Every 90 days you can use one or the other, but not both. 

I've used the KCD and the free promotions, but probably not to the best of their abilities.  And I don't always remember to use them every 90 days. 

The sales I had for Project Hermes, Sleeping Ugly, and Blink of an I were built to use up my KCD before my 90 days ran out. It's a use it or lose it kind of thing.  You get a whole 'nother 7 days when your clock resets, but you can never get back the days you didn't use.  So, I figured, why not use them.

Part of my problem was not keeping good track of when my 90 days were for each of my books.  So, yep, built another spreadsheet.  (It's what I do.) 

As you can see, I've got a line for each of the books (sorted by series), with columns full of dates - when my exclusivity starts, when it ends, and the next one and the next one.  I also mark when I've used my perks by putting a solid box around the date I'm currently in.  So I can plan accordingly. 

The other day, Silver accused me of being organized.  I scoffed.  Traditionally, I have been about the least organized person I could think of.  But, I guess, as I got older and had more time on my hands, I did become organized.  Hence the spreadsheets.  :shrug: 

Being organized has helped a lot with this business part of writing.  (Not so much with the writing part of writing.  That's still an unorganized mess.)   I'm probably still not using all the perks to the best of my abilities - say, getting advertising set up in time for it to be effective - but I'm working on it. 

I hope this helps give you some ideas on how to keep track of this stuff.  Use it if it helps and if it's no use to you, thanks for reading all the way down to here anyway.

Do you use the perks?  Any ideas on how to better utilize them?

Charting Sales, Rankings, Marketing, etc.

This is going to be a boring post.  Unless you're a nerd like me. 

Okay, so I'm doing a sale this week for Accidental Death.  And I thought it would be a good time to try and track the advertising I'm doing along with sales and rankings. 

Yep, I built a spreadsheet.

On one tab, I'm noting when I did a markety thing - date and time, who it was with, and the results as I see them.  On another tab, I'm tracking rankings and how they fluctuate with time and sales.  On the third tab, I'm keeping track of the time of each sale.

This is not scientific.  There's no consistency with when I'm checking sales and rankings, so the times may be off depending on how busy I got doing something else. 

One thing I've learned so far is it takes about 8 hours from the time I get a sale until when it impacts my rankings in the US.  The UK rankings change after about 6-7 hours.  Not nearly quick enough to jump my ranking for boosting sales in the eight hour period, but further sales should help sustain it. 

And it doesn't take long for the rankings to start dropping again if there are no further sales. 

I had one sale Monday morning.  At the time, AD's ranking was around 943,000 in the US.  Eight hours later, the ranking rose to about 159,000.  It dropped throughout the day yesterday with no more sales in the US.  Yesterday, the ranking in the UK was around 920,000.  I got one sale early afternoon here (early evening there) that upped my ranking to 53K.

This morning, the rankings are at around 400K in the US and 81K in the UK. 

Now, I'm not sure how Kindle Unlimited downloads impact rankings and in what timeframe.  I read something the other day that made it seem like it would take 4 days to show up, but I think that might've been for some other bestselling list thing.  I'm unclear.  Fingers crossed I am getting some KU interest that will show up in later days.

Thus far, I have only been doing free advertising - blog, Facebook (groups and pages), and Twitter.  This blog netted me my first sale.  I have no way of knowing which post got me the UK sale, since I had completed three different types of advertising that day before the sale, and five different types the day before. 

Today, a paid ad goes out to the populace.  It's the only advertising I'm doing today, so I can chart it better.  Not sure if the Wednesday before TGD is the best time to put out an ad, but it was all they had open for this week, so I took a chance.  Might be a total waste of $55.  Might be awesome.  Who knows.  Thursday through Sunday I will continue with the free stuff and see what happens.

Poor planning on my part means the sale will end before Cyber Monday.  Derp.

I probably couldn't do this if Accidental Death was selling like hotcakes.  Not at this level anyway. 

Anyway, I hope I haven't bored the hell out of you with my nerdy numbers thing. 

Any questions?

Oh, and have a Happy Thanksgiving tomorrow.  (If you're here in the states.  If not, just have a happy day.)

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.

Business Stuff

It's the 26th of December.  By this time next week, it will be next year.  And you know what that means?  New spreadsheets for 2017! 

Yeah, I'm guessing you're not nearly as excited.  Neither am I.  It's a necessary thing, though.  So, this morning I created a new 2017 Sales Totals spreadsheet and a new 2017 Book Sales Data spreadsheet.

Last year, I totally screwed something up and ended up with a mess of both.  This year I'm hoping to avoid the mess because I made a template instead of creating a copy of the Book Sales Data sheet.  (Creating a copy somehow made all the formulas in the Sales Total Spreadsheet point to the new copy instead of the original, so when I wiped out the 2015 data, it blanked out my original Sales Total sheet.  It was awful.)  Fingers crossed this all goes super smooth this year. 

So, the new spreadsheets are there.  I still have work to do, of course, but they exist - waiting patiently for new sales to fill them up.  (Fingers crossed there, too.)

That's just a screen capture of the top of the sheet.  When I scroll down, all of my books are there in order of publication.  And each has it's own color, which then translates over to pretty lines on the Sales Totals spreadsheet.  It's like a rainbow.  And each month gets its own page in the workbook.  I've only created January and February so far, but I'll get to the others.  I have time.

It's just my totally anal way of keeping track of sales.  The Sales Totals spreadsheet has pretty graphs, too.  I'll probably show a few of those when I do a 2016 wrap up post next week.

If you're a writer, how to you keep track of sales?  If you're not a writer, do you use spreadsheets for other stuff in your life or work? 

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.)