Create the tracker spreadsheet

1. Create a Google spreadsheet with the following worksheets:

  • Our stories
  • Slug info
  • News organizations

2. In the “Our stories” worksheet, create the following columns:

  • Slug
  • Date
  • News outlet
  • Headline
  • Link
  • Main
  • Condensed
  • Follow
  • Column/Editorial
  • Sidebar
  • Photo
  • Graphic
  • Other multimedia
  • Multimedia type
  • [Your organization] mentioned?
  • Customized/localized?
  • Print
  • Web
  • Radio
  • TV
  • Comments

Note: Use “1” and “0” instead of “Yes” and “No” to fill in the columns in italics.

3. In the “Slug info” worksheet, create the following columns:

  • Slug
  • Date
  • Hyperlink
  • Location
  • Optional: categories (i.e. environment, education, government)

4. In the “News organizations” worksheet, create the following columns:

  • Name
  • Type
  • Location
  • Affiliation
  • Circulation
  • Readership
  • Audience

5. Once the spreadsheet is set up, begin to enter data from your searches.

6. Start by updating the “Slug info” with your latest stories. Give each one a unique slug and chronological number (for sorting by date grouped by slug), use the date it was first published, include a hyperlink (=hyperlink(“link”,”headline”)), and the city in which the story is based. You can also add a column for each category of coverage, to be filled in with 1s and 0s.

7. Starting with the earliest slug, enter story pickups. Enter the slug and use Vlookup to pull in the corresponding number. Then enter the date of the pickup, the news outlet (be consistent!), the headline used by the news outlet, a link, if available, and then info for analysis. Which version of the story did they use? What story elements were picked up? Did they give you credit? Include comments to keep track of front-page pickups and anything else of note.

8. Finally, fill in the news organizations worksheet by pulling the names of the news organizations from the updated “Our stories” worksheet, and filling in the type, location, affiliation, and circulation/readership/audience numbers, if available.

Note: keep a printed copy of the “Slug info” and “News organizations” next to you when you update the Tracker to ensure consistency.