Session: How Spreadsheets Can Boost Sales: An Introduction to Author Data
In this session Adam Croft shows you why understanding your author data is so important. You’ll learn:
- How you can use data to boost your book sales
- A walkthrough of the spreadsheet Adam used to track the ad progress of his most successful book
- An introduction to using data to find your mailing list’s most lucrative subscribers
- A quick decoding of Acronym H.E.L.L.
Audience: All Levels
This post is part of London Book Fair Self-Publishing Advice Conference (#SelfPubCon2019), an online author conference that showcases the best self-publishing advice and education for authors across the world — harnessing the global reach of the Alliance of Independent Authors’ network. Our self-publishing conference features well-known indie authors and advisors, for 24 sessions over 24-hours, in a one-day extravaganza of self-publishing expertise straight to your email inbox. We hope you enjoy this session. Let us know if you have any questions or input on this self-publishing topic. Visit our Facebook Group and join in the conversation there, or leave your questions and feedback in the comments section below.
Hello and welcome to this Introduction to Author Data for ALLi’s spring conference. My name’s Adam Croft and I’m going to be giving you a very quick brief overview of the sorts of things you should be tracking in your author career. Now, there’s no way I can make this comprehensive inside the limitations of a short online presentation, but I will do my best. If you’re left with more questions than answers, please do feel free to email me through my website, indieauthormindset.com or find me in the Indie Author Mindset Facebook group.
Data has been absolutely crucial to my success. I’ve spoken a number of times and you may have heard me speak on this subject before about the success I’ve had advertising, a couple of my standalone psychological thrillers, the first one was Her Last Tomorrow, and it was a bit of an interesting story behind that.
I started Facebook advertising at a very low level five, ten pounds a day. And I could see from the data that I was getting back, that every time I spent 10 pounds, for example, what I was getting an increased 30 pounds in sales. And of course if you’re tripling your money rather than tripling 10 pounds, it makes a lot more sense to triple a hundred pounds or a thousand pounds, quite naturally. So the only way of really doing that was to have clean, reliable data and to know that I wasn’t getting my math wrong because I ended up increasing the ad spend up to, getting on for about 1500 pounds a day. I could not have done that without having the data, without having the confidence in that data. And numbers don’t lie. The data doesn’t lie. If you get clean data from the proper sources and feed that into your spreadsheets, perform some calculations, you will then have facts right in front of your fingertips that you just can’t argue with.
The numbers never lie. I have to have faith in that data. I had to know that the information that I had generated from it was accurate and was correct because otherwise I stood to lose an awful lot of money. It’s, you know, spending for a good couple of months at 1500 pounds a day just isn’t sustainable unless you are making that money back at least, you know, in order to turn a profit. So I had to have complete confidence in the data and I did. And frankly that is what enabled my career to take off, I could look at the numbers, I could see what was being spent, I could see what was coming in. I could perform calculations through spreadsheets and say, “Okay, this is working. Every time I do this, this happens here, it is in black and white with the figures.”
And I could then with confidence move forward, I could up my advertising budgets and end up generating a huge number of book sales, huge amount of profit and propelling myself to the point where I’m now spending my time doing presentations like this and showing other people how to do it. It’s something I’ve done a few times. It is, quite frankly, it’s what spurred my interest in data. I didn’t really start tracking things beforehand, but, once I started spending money on advertising, I’m a bit of a skinflint, I’ll be honest with you. I’m not good at spending money. So when I started putting even five, 10 pounds a day, that was a lot of money to me. Then when I was putting that into Facebook ads, I needed to know where it was going. I needed to know what was coming back.
That was the only possible way that I could do this. I had to see it for myself. So I started tracking, I started putting those figures into spreadsheets and that is when I started to become slightly obsessed with what I could do with spreadsheets, what I could track, the kind of calculations I could perform and the kind of information that I could take out of that in order to inform my future publishing and business decisions.
So it’s only really having played around with Facebook ads, having played around with the data that actually spurred my interest in it and when I could see the money starting to roll in because of the decisions I was making based on the data I was seeing that quite naturally increased my love of data even further. And I now track all sorts of things, not just Facebook ads and not just advertising in general, but all sorts of different calculations and interpretations of data that I use and that you can use too in order to take your author career on to the next level.
Now I’m going to skip over the “What is data” bit that would normally come with a presentation like this at the front of the presentation. I’m assuming everybody knows the dictionary definition of data, but to be a little more specific, what we’re actually looking at here is information. Crucially, information is what we get when we interpret and manipulate data and perform calculations with it. The data itself is already there. It’s provided to us in enormous depth quite frankly by Amazon, by Kobo, Barnes and Noble, Apple, Google Play, Audible, Facebook, Bookbub, just about everyone else you deal with in your day to day life as an author gives you some kind of reporting, provides some kind of data to you and you can use that to your own advantage to get information that you need. But why is data so important? Why do we need it?
It’s important for us to know if we’re succeeding. Manipulating and interpreting data will allow us to gauge the success of our advertising, marketing, social media campaigns, our book descriptions, pricing strategies, just about every other decision you make as an author. Likewise, It’ll tell us what doesn’t work and how and where we could improve. It will help us to inform our future decisions based on whether the data tells us things are working or not. And best of all, it’s free and it could revolutionize your business.
So to summarize, every time you do something in your author career, it’ll have an effect. That’s affect will be visible in the data that’s returned to you, which you can then use to gauge whether that move was a success or not. And you can then use that result to decide your next move and the system continues to feed itself.
Now I’ll give you some warning. There are acronyms coming up, but they needn’t be scary. I’m just going to introduce you to a few of the measures that are used and other people use, what they are and how you can calculate them. These are ones which will be quite important to you. So ROI is return on investment. This is telling you, expressed as a percentage, how much of your ad spend, for example, you’re getting back in profits.
So in this example here, and we can see that we spent 50 pounds and we’ve got 100 pounds in profit, not revenue. So, for example, the 50 pounds spend might’ve resulted in 150 pounds worth of sales. You take the spend off of that, so you’re left with 100 pounds profit, divide that back again by the spend and a hundred there divided by 50 is two, which expressed as a percentage is 200% because those of you who paid attention in maths at school and I’m not one of them, one represents a 100% and 1.5 would be 50%.
Another acronym for you, CPC, which is cost per click. Let’s say that you spend 50 pounds and you get 200 clicks out of it, 50 divided by 200 clicks is 0.25. That is expressed in pounds in money because we’re basing it on our 50 pounds spends. So that is 25p per click would be the answer there. If I spent 50 pounds and got 200 clicks.
There’s also click through rates, which is the percentage of people who see your ads and click it. So, so you’ll get a thousand impressions, a thousands different people see my ads or the ad’s seen 1000 times to be more accurate. It is then clicked 200 times. So 1000 impressions divided by 200 and is five or 5%. So 5% of people who saw the ad then went and clicked on it.
Conversion rate, you can do, CR, I’ve used BR before for buy rate. There isn’t, doesn’t really seem to be a standard acronym for this one, but there’s one for you anyway. CvR with the little v because for some reason all the acronyms usually seem to be three letters and it looks a little bit weird just paying the CR on so I put a little V in there. So let’s say I’ve got 200 clicks off of an ad that I run and I got 10 sales off of the back of that. So those 10 sales divided by the two hundred clicks, my calculator gives me 0.05, which as we said earlier, expressed as a percentage is equal to 5%. So that is saying that 5% of people who hit the sales page or Amazon or Kobo or wherever it is, will actually then go on and buy the book based on that calculation there. Now these are the ones that I start to introduce in my spreadsheet.
So cost per sale, CPS, let’s say I spend $200 on an advertising campaign and I get 150 sales as a result of that dividing, 200 divided by 150 gives you a result of $1.33, which is in dollars because it’s based on the $200 that I spent. So that then tells me that each sale cost me $1.33 to generate, which if the royalty for that sale is higher than $1.33 means I’m getting a profit.
We’re going to have a look now at a spreadsheet I compiled in early 2018 when I launched my book, Tell Me I’m Wrong. It’s designed to capture data on the money I spend each day on advertising that book and the sales those ads generated each day. Now, I also entered the number of clicks the ads generated that day, and the number of sales of that book achieved on each day. Everything else is worked out automatically using the spreadsheets own formula calculations.
So here is one of my many, many spreadsheets. Don’t be daunted by all of the figures. It’s, you know, I’d been writing for quite a long time by this point. And all of this data is actually very, very simple. So really you only need to actually answer a couple of things here. I’ll run you through what I do. So I’ve got, this basically tracks my ad spend and return. I was getting on it at this particular point in time about, just over a year ago. So I’ve got the date here called which day of the week it was, which is a simple formula. It just pulls out the, the day of the week based on the date. I’ve got my spend here, which I get straight from Facebook. If you log into your Facebook ads account and go to yesterday, it will tell you exactly what you spent.
So I’d put that in there. I get my sales figures from Book Report which you can get at bookreport.com. This is just my KDP Amazon spreadsheet. Profit. Very, very simple. It’s just the sales minus the spend. So the money that I had coming in minus what I spent cause my profit and the ROI is basically just a percentage, an expression of the profit compared to what I spent. So it’s gross profit divided by spend. So all of these here are just calculations that are done based on the money that I’ve spent on Facebook ads, which is data I get from Facebook and the money I’ll have coming in from sales which I get from Amazon KDP for the purposes of this spreadsheet, clicks and number of clicks that I had on those ads, I get again from Facebook, Facebook will tell you exactly where those clicks came from.
I’ll show you in a minute number of sales as well. That’s something that you get from book reports. This is all data that I enter myself. Now the cost per click, you can get that from Facebook. But I found a much more accurate way and a nice way of doing it within the spreadsheet is to take your spend and then divide that by the number of clicks that you got, which will tell you on average how much it costs me per click through I was getting. This is the buying rate. So this is a percentage expression of the percentage of people that clicked onto my sales page from the ad and went on to buy the book. So as you can see 151 is 41.4% of those clicks. Now in the early days of running an ad that is going to be a ridiculously high number and it’s not just because of the ad, it’s because, as I’ve written here, that was a launch day and I can’t attribute all of that to the ads.
So the first few days it’s a bit wonky. So by the time you get down here you’re looking at, you know, kind of 11, 12, 13, 14%. these are more accurate buying rates down here. This next column, I got rid of, is my cost per sale. Now that essentially tells me how much I have to spend in ad spend in order to get a sale. And that’s quite a crucial thing to understand because each book, of course, has a royalty so a book that I’ve got up at about, say 2 pounds 99 which this one was at this point will net me about 1 pound 70 something I think it is in royalties if I manage to sell a copy. So it’s crucial that this doesn’t go above that because as long as it’s below one pound 70, so here on the 3rd of February, I was spending one pound 16 for every book that I sold at one pound 70.
So that is again, a calculation done very, very simply. It’s the money that you’ve spent divided by the number of sales that you got that can then be used to give me quite an interesting figure, which is this column here, this is the max cost per click. This is an advisory statistic for me, which tells me what I should be bidding on, what I should be bidding, sorry, on Facebook ads and whether or not those ads aren’t going to be working, knowing which ones to keep going, which ones not to. So the way that is done is using the buying rate and multiplying that by the 1 pound 76 royalty I would get for each copy sold. So it’s basically saying to me that if I sell a copy of the book, I get one pound 76. So only 12.8% of people who click onto the page, we’ll go ahead and buy that book.
So 12.8% of 1 pound 76 is 23 cents, which is the maximum cost per click that I could spend and not lose money. So effectively this is my break even point on each of those days. And if you look as we get further on in time, that starts to creak down, which tells me the ads were starting to get more expensive. And you can see here the ROI starts to fall a little bit as well as the happens, which then gives you an indicator that you need to spice things up with your ads, adjust your spend, maybe adjust the ads that are going out there and see what happens as a result of that. But just to be clear, the only places I enter data are in the green columns here and all of that is very, very simply obtained from my Facebook ads dashboard or from Book Reports.
Everything in the red cells is fantastic, golden information calculated by the spreadsheet according to those bits of data I enter. Now, here’s where only get all that data from Facebook.
So this is for the same period of time. That’s a month in February last year, shortly after Tell Me I’m Wrong came out. So if each day I would go into the day or perhaps, say, yesterday, if it was the next day that I’m doing it and here’s where I got all the data. So I can see that on the 1st of February, 2018 this is what I spent on Facebook ads that day. So that will go into the spend. So, the number of clicks that I got is here, the five, seven, six, five and that really is the only two pieces of data that you need to take from Facebook ads.
So that will go into your spend column and that will go into my clicks column. And now this is my book reports reporting page for the same date, February 1st, 2018. So we’re assuming that I’m putting these stats in on February the second. So in terms of sales, this is where I get that number from, that it’s the total amount of books I sold that day in terms of pounds, so that will go into my sales column and then down here, number of sales that I got, that’s the number that goes in there. The 766 is how many books were sold that day for that amount of money.
So just to confirm that 1st of February, that’s where I would put the money that was spent that Facebook told me about. That’s where I’d put the number of clicks Facebook told me I had, that’s where I put the amount of money book report told me I’d made. And that is where I put the number of books Book Report told me I’d sold. Now bear in mind that, as it’s over a year ago now, some of those figures have changed slightly within a few days, in a month or two, you sometimes get a little bit of variation creeps in on the reports when you go back and look at them again because of currency conversion rates and things like that. But it’s as near as done it. It’s about as good as we’re going to get, frankly. Now, bear in mind, you don’t have to track all of these things that I track.
If you’re interested in data and spreadsheets and you think you can do this, then great, please do. But so this is literally just to show you what I do and how simple it is to put together. So I also, I’m on top of looking at the KDP sales and the profit that the KDP books have made. I then add on the effects at the other vendors at that point in time. So example, on these days here, I would put in on the 1st of February, for example, also the money that Kobo told me I’ve made, the money that Apple told me I’d made and the money that Nook told me I’d made, which would then give me this figure here, which is literally just a sum of all of those put together. So you tell me how much money I’d made on that day in profit.
Now what I also set up here is some conditional highlighting, which again is a bit more of an advanced feature spreadsheets, but all it does is look at the numbers in that column in that cell and it will turn it a color based on whether it is above or below a certain number. So it’s a nice way of me looking at it at a glance. And if it’s green, I know that I’ve reached or exceeded my target for profit that day. And, over here, again, this is just something that I programmed in to tell me which day I had my highest KDP sales. This is a formula here. It looks at the KDP tracking spreadsheet we looked at earlier, and finds the max value in the sales column, that there tells me which day that was, it’s got a complicated formula there, which tells me on which day I made that amount of money.
So I can just, at a glance, see what, what the best date was for this particular campaign and exactly the same here, but looking at the profit columns that would tell me which day I made the most KDP profit, that was the 22nd of March, made 950 pounds on KDP alone in profit, and the highest overall profit once you include all of the vendors, what was 1092 pounds there on the 24th of February last year for that particular campaign. But it’s not just about ads and money, that’s not all that’s there to be tracked. For example, I always say one of the biggest marketing tools you have for your author career is your mailing list. I’ll use MailChimp for mine. And here’s a little bit of analysis I did based on my MailChimp subscribers. What I was trying to find out is what is the most successful way of getting subscribers onto my list.
Now there are a number of ways of measuring that. You could look at pure numbers, the number of people that are being added on. That’s quite an easy way to find out. You can also look at the number of people who become five star engaged subscribers and become super fans, which I also did look at, or you can look at what I did here, which is the attrition rates of those particular ways of getting people on. So what I have here is down this side, these are just name tags that I’ve given to the different ways that I got certain members onto my VIP club mailing list, which is what I call our mailing list and the VIP club. I tagged users as they come onto the mailing list depending on where they’ve come from. That’s all done in the back end of MailChimp.
So MailChimp could tell me by me looking in, this is how many people in total had signed up through these various methods. This here VlP Club is through my website page, which they might gone to from the back of a book, Start A Library was a Facebook campaign that I ran for quite a while, a couple of years ago. And so on, all these different ways that people signed up onto my mailing list, and this is how many people had signed up over that period of time that I was looking at. This here shows how many of those people still existed on the list at the time I looked at it. So of those 37,000 people, 30,000 of them were still on the list. This was how many of them had gone, had unsubscribed, had left the list and, and gone elsewhere.
So just over 7,000 of them. And what I was also looking at here was the percentage of those people who’d signed up, who had gone? So people who signed up for my website, 18% had gone by the time I’d done this analysis. People who signed up through your Facebook ads, 21% had gone. So I could see from here that percentage of people effectively that were staying on the list or that had left the list. So I could see that audiobook boom, for example, wasn’t the most successful in terms of number of people that went. There’s a thriller bundle that I went into, nearly 30% of the people left after that. So that wasn’t the most successful email marketing and list building method I used, but some of them were very successful.
This one is obviously the best because nearly over 92% of people have stayed on the list after signing up through this method. But there were only ever 26 anyway. So statistically that’s not something I can use that’s significant. So the best one for me, um, with statistical significance was this one here because I’ve got 4,000 subscribers and three and a half thousand of them stayed in the list. And that’s the year Ryan Zee booksweeps promos.
Now, as I mentioned earlier, I wanted to see where my best quality subscribers are coming from on my mailing list. So I set up this little spreadsheet here. MailChimp does a very handy little thing really. It’s rates all of your subscribers from five stars right the way down to one star or I think even zero stars. I think one might be the lowest, based on whether they open your emails, whether they engage, reply, click on links and things. So you’re five stars subscribers are the ones that you want to cultivate.
So this is the one I started because as I say, all of my subscribers are segmented and I know where they’ve come from, at the point in that I did this particular analysis, this is how many people were on my mailing list in total, 19,001 and 100% of course, 19,001 is 100% of 19,001, so this is how many people were signed up through the website, through the Start A Library ad, and this is a percentage of the 19,001 that those are just so I can see at a glance what percentage of people signed up to my list from which particular places.
Now this is how many of those 9,999 subscribers Mailchimp says it had rated it five stars, 1,444 of those 9,999 subscribers were rated five star, which is 14.4% of the VIP club sign up subscribers there. People do sign up through Start A Library 6,298 of them, 942 of those who rated five stars, which is 15%. So I can have a quick glance down here and the highest figures here are the ones that essentially give me the best quality subscribers.
So I can also see, for example, that let’s have a look at those Ryan Zee booksweeps promos again, so 13% of people who signed up through there went on to be gold five star subscribers and superfans, 26% of them went on to be four stars. A 16.3% were three stars. And generally speaking, a lot of them become two star subscribers anyway because emails fall into spam traps or don’t get opened or what have you.
So these are the guys that we want to not worry about too much. The three stars are the people that could be swayed. The four stars are fans of yours and the five stars are super fans. So I can see at a glance here which methods of getting people on my mailing list, which is these, I can have a look then and I can see how many five star subscribers I’ve got from those, how many four star and what percentage of those subscribers that came from that source, for each of those different categories. As I keep saying, I’m not going to go into a huge, huge detail about how I put these spreadsheets together, but just to show you a few of the formulas there and so you can see the kind of things that I track because you know, the maths behind it is fairly simple really.
It’s just showing you what I track, what I use that data and the information for and the sorts of things that I can extrapolate from that data in order to make business decisions. So for example, we looked at the mailing list, we could see where quality subscribers were coming from, which sources of mailing list building were giving me the highest rates of subscriber attrition and I can use that. So you say, “Okay, well in that case I need to perhaps dial back on certain methods of list building because the people who sign up aren’t staying very long. They’re becoming one and two star subscribers and they’re disappearing off my list fairly quickly and unsubscribing. There’s not much point in putting the effort into getting those people on my list.” In fact, it could harm it.
Whereas there are other methods of this building, you can see from my stats that are good. They give me five star subscribers, they give me four star subscribers, they give me low unsubscribe rates. And just by looking there at that data, I can say, “Okay, these are the methods of list building that I need to do more of because they’re getting me five stars subscribers, they’re getting me high open rates, they’re getting me high click rates and these are the things that are doing well with my business” because otherwise it’s very easy to just say, okay, I’ve got x number of people on my mailing list. I’m trying to get as many people as possible from as many sources as possible, which isn’t a great idea. Knowing where the good, highly engaged readers and subscribers are coming from is what you want. So a simple spreadsheet like the one I just showed you can go a long way to doing that for you.
I hope you found this session on data useful and I haven’t just confused you further. It’s a very, very difficult subject to broach in the course of half an hour and to get across in an online presentation. But I’m hopefully if you perhaps watch again, you might find something useful in there. If you are stuck, then please do feel free to head over to my website, indieauthormindset.com and fire me an email. I’d be more than happy to help you. No problem at all. Or head over to the Indie Author Mindset Facebook group, which is completely free and has a whole bunch of people in there who will be more than willing to help you. I hope you enjoyed the presentation. Thank you very much for sticking with it.
Register for #SelfPubCon2019
Our next online conference for authors runs in association with the Digital Book World, September 2019.
Register now and we'll send details of our speakers, sessions, sponsors and competitions closer to the time.
Hosted by the non-profit Alliance of Independent Authors. Always free.