Valerie Comer, an author friend of mine, is part of a four-person novelette book coming out in early 2012. As part of the publicity package, she was given guidelines for creating a spreadsheet to track her review requests. Now Valerie is not much of a spreadsheet person. She was listing her tasks, and just how difficult they would be, with this on the top of the list.
Spreadsheets are my friends. While I prefer to work in a full-fledged database, they have their uses, and they’re very simple, at least for me. So, knowing that someday I will need a spreadsheet much like this one, I offered to make it for her in return for her sharing the information about what should be in it.
The reason I’m telling you all of this is because when I make a spreadsheet, I share it. Therefore, if you wander over to my Writing Tools (http://margaretmcgaffeyfisk.com/writing-tools/), you will find a spreadsheet designed to help you track what reviewers you have contacted, review copies you’ve sent out, the expected date of posting, and a number of other useful items.
This review spreadsheet uses four pages and is in two formats. The pages, or sheets, are: Roundup Chart, Reviews, Reviewers, and Paper Review Copies. The difference between the two formats is because a function is not available in the Excel 2003 version. If you do not have Excel 2007 or 2010, you will have to manually enter the number of review copies you have already promised on the Paper Review Copies page. With the later versions, all the roundups are automatic.
The purpose of each page is described below:
Roundup Chart: this page provides an overview of your review requests. It also has a section to the right that tracks the number of review copies you have versus the number of review copies you promised. Both of these charts are based on the information in the other tables. To see the latest information, you must refresh the table. This can be accomplished by selecting refresh from the right-click menu.
Specifically, the chart shows a breakdown by title, whether it’s a paper or electronic copy that you sent, what type of post was requested, and then some date information. It allows you to look at whether you need to offer an interview, a guest post, or something different like a podcast to the next person willing to profile you on their site. The purpose here is to ensure that each person profiling you has something new to show to their viewers.
As with any pivot chart, you can move the order around to change how it breaks down if you are familiar and comfortable with that process. I would suggest making a backup copy before you do. You will always be able to download a new version from my site, but it won’t have your data in it. I do provide some sample data so you can see how things work.
Reviews: this page is where you track the specific requests. It covers who, what, and when. These are all the unique elements that relate to a specific request. Any information about the reviewers is on a different page because it should not change, or if it does it will change for all reviews. In that case I would suggest putting the old site name in the notes for the reviewer along with a date for the change.
The main Roundup Chart gets its information from this page. It is important that you don’t change the format here without understanding how pivot charts work because you will have to update the chart itself so that it still knows what data to get and how.
The very first line of the spreadsheet offers tips for what to enter in that column. The drop-down list of reviewers is taken from the Reviewers spreadsheet. If the reviewer you are looking for does not exist in that list it would be wise to check to make sure that you enter their information in the other sheet. Depending on how many reviewers you’ve entered, there’s a chance that the list will need to be adjusted, but since I believe I set it for 500 lines, I doubt that’s going to be an issue anytime soon.
The others are just typed in at the top, and you can add to, or change, them as needed. For example, if you found a review type missing, just add it to the list as a way to remind yourself of what possibilities exist.
The other neat feature is that when you enter a date in the “Date Scheduled to Post” column, when that date has passed, the cell turns blue. A quick skim will tell you if you have listed a date posted. If not, you should check to make sure that it did post, and follow-up if it did not.
Reviewers: this page should be self-explanatory. It has no secret formatting or anything beyond what you see. It is specifically designed to track the reviewers so that when you realize one person has been very good for you, you can easily find their preferences or how to contact them.
Paper Review Copies: this page is useful for tracking when you need to order more paper review copies. If you do not have one of the later versions of Excel, you will have to fill in all of the fields yourself except for the Needed column. On the other hand, if you make it part of your process, it shouldn’t be too onerous. If you do have the latest version, you need to fill in the title, and the number of copies you have in each category. The sent and planned columns are automatically calculated based on the reviews page.
And that, I think, is it. I hope some of you find this useful, and if you have any questions, just reply.
Woot! Thanks. I’m gonna feature this on my blog next Friday–and maybe you along with it! You up for an interview? 😀
Depends on what questions you ask ;). Enjoy.
And in true form, I’ve already made one change. I had the second pivot chart below the first, but that caused some issues, so I have moved the Paper Copies to the right of the Roundup, and hidden the default blank entries, to avoid confusion.
Thanks, Valerie and Margaret!
Pingback: Spreadsheet Template for Book Promotion | Valerie Comer
Can you tell me how to refresh? I’ve right-clicked on everything I can find and I don’t see refresh. Please help!
But this looks incredible!
I figured out refresh. 🙂 Now, I have a question…
I tried to rejigger the spreadsheet for Interviews, but I completely messed something up. I’m curious how hard it would be to revamp this so I can keep up with guest blogs posted and guest blogs promised and interviews. I’ve got stuff scattered all over the blogsphere now and I can’t keep track of any of it.
I’ve never worked with (or even heard of pivot pages). Where can I go to learn them really quick? Oh, and unlike Valerie, I actually do like spreadsheets. 😉
Well, I learned Pivot Charts by trial and error, so if you do it my way, you poke around until it works :). If you have Excel 2010 (and maybe 2007) look at the top for a PivotTable Tools after you click on the table (doesn’t show otherwise), you can see a bunch of things to do under options and design.
As far as the rejiggering, I’m not sure what you’re trying to do. You shouldn’t need to change anything for guest blogs (or do you mean on your own site?) If on your own site, just listing yourself as a reviewer and treat it like a review would work. If on others, just type the Review Type into the Review Type column on the Reviews page and you’ll be set.
Oh, and regardless of version, Pivot Charts are an option under Insert. The software does all the heavy lifting and they’re very handy, especially for cleaning up lists were things are listed multiple times but you need a list with no duplicates :).
Thank you for sharing, Margaret! Awesome.
Yr welcome. Hope you find it useful.
Pingback: Tips to Track Those WIPS | MissWrite.net | ROCK your writing! m/w/