An image of two hands writing in front of a computer screen with a phone on top of papers on a desk
Blog,  Freelancing,  Journalism

How to easily track pitches in Google Sheets as a freelance journalist

Before I start, I want to give credit to Rachel’s List as I used to use their pitch tracker which is worth checking out.

But I wanted something a bit more tailored to my specific workflow. Because I might also write creative writing work to submit to outlets.

And I also didn’t want to track some of the things that they did.

I try to keep it simple and easy

The spreadsheet in Google Sheets that I used in 2020 had a lot of added quirks as well. I was trying to automate parts of my workflow between Evernote and Asana as well and trying to get them to update a row in the Google Sheet was an interesting but ultimately futile exercise.

So for 2021, I pared down.

I use only the fields that I want to track

So in my tracker, I have the following fields:

  • Pitch number – helps me keep on track to pitch X many stories per month/year
  • Pitch date
  • Follow up date – usually two weeks after the pitch date but can change if the pitch guidelines for an outlet give you a time by which to expect a reply
  • Outlet
  • Contact + email – if it is via a form I just put online portal/submittable in here
  • Pitch headline/nickname – this is more for my purposes so I know what story I am talking about
  • Pitch status – a drop down list of:
    • Ideas & research
    • Pitched
    • Pitch rejected
    • Re-write pitch (in case they are keen but want me to rework it)
  • Story status – a drop down list of:
    • Accepted
    • Writing story
    • Story filed
    • Story published
  • Feedback/notes
  • Pay
  • Words
  • Rate – this has a formula throughout the column of the previous Pay cell info being divided by the Words cell info. I do this here so I can submit rates I collect to a MEAA rate tracker.
  • Link to story

I don’t have a field for tracking whether pay has come in or not because I use an accounts program called Rounded and can just generate an invoice when I file the story for when payment is meant to come in and track it in a more automated fashion that way.

It’s best to work with what is a habit and seems natural for you to do.

I tried to track pitches in Asana but it didn’t work well for me. I also tried a few other methods but again, not that great.

I also wanted to maintain a seperate list of story ideas but again so far this has not worked out for me.

It seems to work out better to list all opportunities to pitch that I have an idea for in the spreadsheet and to then set them to the Ideas and research status.

If I want to track how many pitches I have actually pitched in a certain amount of time, I can set up a formula to show me how many entries have the Pitched status to them.

I made it easy to track the data

Rather than opt for a program with bells and whistles, I googled the formulas and created a second sheet/tab in the Google Sheet file that tracks the data in the first sheet/tab.

So basically, I can always go to this second tab to see what the progress is and how many stories I have pitched in a month, how many were accepted and how much money that was and what my acceptance to pitch rate was.

Here is an example below of how I set this up:

Pitch goal2121

To do this I used COUNTIFS and SUMIFS.

COUNTIFS let me count how many pitches within a given date range were pitched so it looked for the date range and for the Pitched status. And then I wrote another COUNTIFS formula for the Accepted status. Which is why I had to seperate out the pitch and story statuses into two fields so one did not affect the other and therefore the data. I also used a wildcard for the second one because filling out the story status field indicates that the story was accepted. Leaving it blank means it wasn’t.

For counting the stories pitched in a given month:

=COUNTIFS('Sheet1'!B2:B250, ">=01/01/2021", 'Sheet1'!B2:B250, "<=31/01/2021", 'Sheet1'!G2:G250, "Pitched")

For counting the stories accepted in a given month:

=COUNTIFS('Sheet1'!B2:B250, ">=01/01/2021", 'Sheet1'!B2:B250, "<=31/01/2021", 'Sheet1'!H2:H250, "*")

I then had a SUMIFS statement to add up everything in the Pay column if it was also Accepted and within a set date range. That gives me the total amount I have pitched for within a month. There will be some variance with some work coming in AUD and some in USD but it is only going to function as something to keep me on track as I glance at it and not for actual accurate accounting purposes so I am ok with this. Rounded will hold all the actual payment details including all currency conversions etc.

To total what I pitched for successfully in terms of money in a given month:

=SUMIFS('Sheet1'!J2:J250, 'Sheet1'!B2:B250, ">=01/01/2021", 'Sheet1'!B2:B250, "<=31/01/2021", 'Sheet1'!H2:H250, "*")

After that the Acceptance rate info was just a case of telling Google Sheets to divide the accepted count by the pitched count and multiply it by 100.


Then I can just add a total column in the second sheet after the December column to show the totals of each row.


Want to customise the simple, easy to use tracker I made?

I am sure you could build your own with the information I have provided here but if you want to use mine as a starting point, I have created a copy of it for people to take a look at.

Currently it is available via my Patreon so if you subscribe for $5 for a month, you can pay once, access it, download it and then cancel your subscription.

If you choose to stick around on Patreon you can always let me know what other resources for freelancing you would be interested in.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: