• John Clayton

A database for managing a freelance writing career

As a freelance writer, I have dozens of ideas for articles and essays. I also write for numerous publications, and aspire to write for many more. How to track all of that? For the past 20 years, I used a homegrown spreadsheet. But this winter I’ve shifted to a user-friendly lightweight database, hosted free at the cloud app Airtable.com. In hopes of helping other freelancers, I’m now sharing a template of that database. The template is available free at https://airtable.com/universe/expcxfouNUc4HS4P7/freelance-writer-management. In this blog post I’ll explain how it works.


Here’s a screenshot:


In this database, my name is Peter Parker and I’ve got four story ideas. You can see them listed. (Please, no complaints about the feasibility of these ideas! They’re just illustrations!)

  1. The first idea is a news story about Spiderman. My priorities run from one (low) to ten (high), and I’ve put a high priority on this one. (I really like this subject!) I haven’t done any work on it, so I have nothing in the “Done?” field (these explanations use “field” and “column” interchangeably). The news will be stale next week, so the Status is “Deadline,” and I don’t know where I’m going to publish it, so the Status is also “Needs home.”

  2. The second story is about mutants. I’ve written a query to editor Robbie Robertson (we’ll see more of this later), so this fascinating-to-me topic is “Under Consideration.”

  3. Third is a story on superheroes that I’ve already published (“Done”). It was pretty good; I gave it a B+.

  4. Fourth is another news story for which I’ve written a query, but it’s a lower priority for me (“5”) and I haven’t yet sent the query, so it still needs a home.1.


Airtable is very flexible. I use the Grid view but other views also exist. I’ve sorted by Priority and then Status but you can sort on anything. Columns can be of different data-types, and I’ve set up the Status and Done columns with pre-set options including colors. Airtable runs in a browser or on a mobile phone. The company makes money off big databases shared across multiple people; as a solo freelancer I’m able to take advantage of its stripped-down free product. I probably haven’t scratched the surface of @airtable functionality; you can learn more in its Help.


But first let me show you the rest of the table. Depending on how the screenshot fits your screen, you may need to select “View larger version” or open https://airtable.com/universe/expcxfouNUc4HS4P7/freelance-writer-management in a separate window.

  • After the Status column is Magazine: the publication I’m targeting. If you look at the top left, you’ll see that this links to an entire additional table on Magazines. We’ll study that table in a minute.

  • Then comes Tickle Date, one of my favorite features. Many magazines’ submissions guidelines say something like, “If you don’t hear from us in three weeks, assume we’re not interested.” So when I submit, I double-click on this field. Up pops a calendar, and I select three weeks from today.

  • The Overdue field is a formula that compares the Tickle Date to today. It quickly alerts me that I need to pay attention to this story: maybe I can resubmit it to another publication. (Or maybe I need to take another look and see if it’s too badly conceived to be published anywhere…)

  • Subsequent Targets is a freeform field for me to jot notes about which magazine I might target after the next rejection. “Freeform” means that unlike the Magazine field, this field isn’t tied to another table. You could do one or more linked fields here, but I find it helpful to have flexibility of freeform.

  • Prerequisites and Research are additional freeform fields where I can jot notes about what I still need to do to finish the story. In this case Peter needs to do interviews for #1 and #4, and he’s also developing a crush on the scientist’s publicist. (Hey, why not mix work and fun?)

  • Past Published is a record of success, a link to the Magazines table. I don’t track a lot of data here on when it was published (or paid! Or how much it paid, how long it was, etc.); maybe sometime in the future I’ll add columns to do so. But as we’ll see in a minute, this data is actually more valuable in the Magazines table.

  • Past Rejected is a record of failure, important so that I don’t keep resubmitting the same bad idea to the same frustrated editor. It’s also linked and valuable in the Magazines table.

  • Finally, Anthologize is the dream column. Every writer dreams of collecting past work in an anthology. In this case, Peter wants to publish a collection called “The Value of…” and Superheroes would be chapter 1.


Now let’s flip over to the Magazines table. Each record lists a magazine, editor’s name, and any notes on the editor. For example, in this case Peter knew to target Robbie for the Mutant article because he had a note that Robbie liked mutants. The Notes field is also useful for a website or a link to submission guidelines or a favorite article.


In the New Pitches field, the software has automatically generated the link back to the Story table—this is the magic of databases. Furthermore, Airtable also allows you to double-click on a New Pitches record to see a pop-up summary of this particular story right here.


I built in an Attachments column that could theoretically link to a Word document containing the article itself, but in truth I never use this column. The final columns are Past Published and Past Rejected, linked to the Stories table. These allow you to see at a quick glance the types of stories the publication likes, and dislikes. For example, Jonah Jameson rejected Peter’s story on superheroes; Peter can deduce that Jonah doesn’t like superheroes. But Barney Bushkin did publish that story; if Peter comes up with another superhero idea, perusing this table will tell him to target Barney.


What benefits did I gain from developing this database? I’ll admit, I gained some value from simply importing my old spreadsheet and studying each record. Seeing the records in a fresh format allowed me to see them anew. As I tinkered with the database design, I was coming up with new targets; as I made sure my data fit this structure, I got to reconsider each story. Nevertheless, that’s value I would not have gained from continuing to use my old spreadsheet with its fewer features.


Going forward, I expect to—and indeed have already started to—gain value in the following ways:

  1. The Overdue field keeps me on top of submissions. Rather than moving silently from “in some editor’s queue” to “apparently rejected” to “I forgot about it,” the story comes back in my consciousness.

  2. The colored Status can show me at a glance: yes, most of my high-priority or Deadline projects are Under Consideration somewhere, or no, I need to work on X, Y, and Z.

  3. In the Magazines table, the Past Published/Rejected fields help me better understand my publication targets, and which new ideas are best for them.

  4. Sometimes I write an essay without knowing where it’ll go. Seeing Done=Draft and Status=Needs Home gives me more impetus to submit them.

  5. When a story idea gets rejected, seeing “query” or “draft” under Done reminds me: I’ve written something, why not (alter it and) send it somewhere else?

  6. By looking at the Magazines table, I can quickly see that I haven’t pitched the Daily Globe in a while, indeed not since the Superheroes article. Which of my story ideas would be good for Barney?

  7. My next anthology is far in the future. But now I have some mileposts to track my progress!


I’m offering this template and explanation in the spirit of karma. My own career was greatly helped by mentors such as Gary Ferguson (@GaryAFerguson) and Mark Spragg. If this has been helpful to you, I’m glad to be paying it forward. (OK, if you really, really want to help me in return, please consider buying one of my books—and, if you like it, even writing a nice review at wherever you write user reviews). If you’re able to implement improvements to the database, I’d be delighted to hear about them. Thanks for reading the entirety of this super-nerdy post!


  To be notified when new books or articles come out, sign up for my newsletter.

406 446 3843

  • facebook
  • linkedin

©2020 by John Clayton Books. Proudly created with Wix.com