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.