My Story Submission Tracker Google Spreadsheet is now Freely Available

While at Launchpad, there was a discussion about various ways writers track their submissions. Some writers use a service like Duotropes, others use a simple spreadsheet, others still write them down in a notebook. I’ve always done mine in a spreadsheet, one that eventually evolved into a Google Spreadsheet and, as is my wont, became highly automated. It works well for me and my needs. Several of my Launchpad friends asked if I’d share the spreadsheet with them. I went ahead and generalized it, populated it with sample data, and now the spreadsheet is available for anyone who wants to use it.

Access my Submission Tracker spreadsheet here.

Some basic instructions for using the spreadsheet follow. Keep in mind that the spreadsheet was designed for the way I work and so it may not be optimal. That said, I’m always open to ideas and suggestions, but I have very little time to make changes or provide support. Beyond the instructions below, you are on your own.

Overview

The spreadsheet consists of 8 tabs:

  1. Markets: the source for listing the markets to which you submit your work. Submissions are summarized by market on this spreadsheet.
  2. Stories: the source for listing the stories/articles/novels which you submit to markets. Submissions are summarized by stories on this spreadsheet.
  3. Submissions: the main submission log. This is where you list your submissions. Each submission consists of one story and one market and some other information. Data about the submission (like how long the story has been out) is summarized on this spreadsheet.
  4. Publications: the source for listing your publications and payments.
  5. Submission Summary: a pivot table that summarizes your submission counts by year and by market.
  6. Payment Summary: a pivot table that summarizes your payments by year and by market.
  7. Configuration Lists: the source for some of the “static” pulldown lists in the spreadsheets.
  8. Instructions. A not-quite-as-detailed version of these instructions.

Accessing the spreadsheet

The spreadsheet is shared on Google Drive and I believe this means it required a Google account to access1. Here is the best way of accessing the spreadsheet and making it your own:

  1. Go to the shared version of the spreadsheet (this version is read-only).
  2. From the File menu, select Make a Copy…
  3. Give your copy a name. It will be stored in your Google Drive repository.
  4. You should now be able to access and make changes to your own copy.

Some basic conventions

Here are some basic conventions that will be useful when beginning to use the spreadsheet:

  • Many fields are “validated” by a pulldown list. This means you can only enter data that appears on the pulldown list for that field. If there is something you are not seeing on the pulldown list, it is because it hasn’t been entered in the appropriate sheet yet. For example, if you are creating a submission for a new story, and you don’t see the story on the “Story” pulldown list on the Submission spreadsheet, it is because you have not yet entered the story on the Story spreadsheet.
  • Green shaded fields are fields that are automatically calculated. Do not overwrite the green shaded fields, or you may get results you don’t expect.
  • White fields are the data entry fields. These are the places where you type something in or select it from a pulldown list.

Getting started

The spreadsheet is populated with some sample data so you can see what it looks like in action.

If you are looking for a way to get started, or transferring submissions from an old list, here is how I would go about doing it:

Step 1: Add your markets to the Markets spreadsheet

Markets List

Each market you add requires only two fields, the name and the type. The type is something I use to indicate whether the market is a “professional” market (e.g. SFWA, pays pro rates, etc.), a semi-pro market, or if the market is closed.

You can add your list in any order you like and sort the list by selecting anywhere in the Market column, clicking the Data menu and selecting Sort Sheet by Column A.

You can also filter your market list using the filters in the first row. If, for example, you wanted to see just SFWA markets, you can click on the pull-down next to the Type field and unselect everything but SFWA.

As you create new submissions, the columns shaded green on the Markets spreadsheet will update automatically.

Step 2: Add your stories

Stories List

I call this a “stories” list, but it really can be anything, novels, stories, articles, etc. There are three pieces of information to provide for each story:

  1. Your full title.
  2. An abbreviated title. This is what will show up on the pulldown lists on the Submissions spreadsheet.
  3. Words (optional) a rough word count for the piece.

Add your stories to the list. Just like submissions, you can sort them however you like. The items shaded green on the Stories spreadsheet will update automatically as you create and update your submission list.

Step 3: Add your submissions

Submission List

Once you have added at least one market and one story, you can begin to add submissions to your submissions list.

A new submission requires 4 pieces of information:

  1. The date on which the story was submitted
  2. The story being submitted (selected from the pulldown list)
  3. The market to which the story was submitted (selected from the pulldown list)
  4. The status. For new submission, I set my status to “Submitted”

You can use the Notes column to add additional notes if necessary.

When you learn of the disposition of a story (Accepted, Rejected, Hiatus, or Withdrawn) you do the following:

  1. Change the status to the appropriate disposition
  2. Update the “Last Date” for the date of the current disposition (the date on which a story was accepted, rejected, etc.)

The Contract column is there to provide a link to a contract to the story. I scan my contracts into Evernote and provide a link to the note in this field.

A word about how the Days Out is calculated:

  • If there is no value in the Last Date field, the Days Out is the number of days between the day you submitted the story and today (the day you are looking at the spreadsheet).
  • If there is a value in the Last Date field, the Days Out is the number of dates between the submit date and the last date.

Step 4: Add your publications

Submission List

The publication sheet is where you add your publication and sales information. Adding a publication requires that your story be on the Story spreadsheet and your Market be on the Market spreadsheet. However, you do not need a submission record to add a publication. I did this because there are some stories and articles I’ve sold that aren’t really submitted, they are commissioned. This allows me to capture the sales without the need for a submission record (you can still add one of you like).

Here are the fields for adding a publication:

  1. Pub Date: the date on which the story is published.
  2. Story: the story being published.
  3. Market: the market in which the story was published.
  4. Issue Date: this is the date on the cover of the magazine (which is sometimes different than the publication date), or the date on the copyright page of the book or anthology. It is a text field as opposed to a date field so that you can enter things like “July/August 2013″ or “Fall 2013″, etc.
  5. Type: this is a very high-level summary of the rights you’ve sold. I basically track: Original, Reprint, Audio to keep things simple.
  6. Payment: your payment amount in dollars.
  7. Payment Date: the date on which you received your payment
  8. Payment Type: currently, Check or Paypal2.
  9. Issue Link: if the publication is available electronically, I include a link here to the publication. If it is a print version only, I might include the Amazon link.

Summary

Keeping the first four tabs up-to-date will automatically update all of the green shaded fields on the spreadsheet. It will also update the pivot tables on the Submission Summary and Payment Summary spreadsheets.

Support, etc.

Like I said, I’m happy to share this, but I just don’t have time to support it, so you are on your own. That said, this spreadsheet has worked for me for years.

And please feel free to make changes and adjustments to your own copies as best suits you. If you know what you are doing with spreadsheets in general, you’ll find that it isn’t to hard to tweak things.

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

Notes

  1. But I am not certain.
  2. Thanks to Andy Romine for suggesting this feature.