How to 'hack' the writing process with spreadsheets

This post is part of a v. occasional series in which I try to open up about the journalistic process. If you have questions about local news/a specific story/the great city of Buffalo (I take all comers), send it in and I’ll address it later. 

I don’t advertise this clichéd facet of my neurosis, but I am a person who likes — nay, loves — personal “productivity hacks.” Batch-cooking, morning routines, 52-minute block schedules … hell, I’m developing a work uniform so I no longer have to spend time getting dressed.

But I’ve never had a ton of tricks for speeding up the writing process. For me, a long story is just a dumb, brute slog. I spend days or weeks reporting and then attempt to outline and organize and “write through” reams of notes until they resemble a coherent article.

Lately, however, I’m trying a new approach — and it involves Google Sheets, of all things. For my last story, a profile of a big tech start-up here, I organized my notes in a 290-row spreadsheet.

ACV spreadsheet   Google Sheets.png

Each column is pretty intuitive: You’ve got your quote or factoid or other discrete piece of information, plus the person or organization it came from; a few thematic filters to help group similar information; and special columns to denote must-use scenes or quotes and flag items for follow-up.

As I reported out this story — conducting interviews, reading studies, watching pitch videos and generally hanging out around ACV — I dumped my research in this spreadsheet and tagged each quote, scene, statistic and observation with a corresponding theme. LaunchNY’s Marnie Lavigne laying out local investment stats fell under “WNY startup scene” / sub-topic “current state.”  The findings of a study on tech earnings in Pittsburgh became “economic impact” / “startups as development theory.” 

Once I’d finished reporting, I used Sheets’ filter function to identify the most important threads and surface the info I needed as I needed it. When writing the section about ACV’s history, for instance, I could pull up just those notes and work off them; when a long narrative section needed a scene to break it up, I could filter for “scene” and see what fit. (Plz disregard the bad writing and excessive details in these spreadsheet scenes, which were not intended for public consumption.)

ACV spreadsheet   Google Sheets2.png

Added bonus: My research never got separated from its sources — a hazard, when you’re working off a long outline — so I never had to go back to individual transcripts to find who said what or when. And creating a column for high-priority scenes and quotes meant I couldn’t lose them in successive rounds of edits.

I don’t know if this method saved me a mountain of time, per se. But it did make the writing process run more smoothly, with far fewer frustrating fits and starts. So for that reason, at least, I’m adding this hack to my list of regulars. In fact, I’m already 315 rows (eek) into the next one.

H/T to my project-manager husband, who uses a similar method to organize his projects and suggested it might work for stories, too. And if you have zany process ideas, I’d love to hear from you.

‘Til next time,

Caitlin

How I wrote it: “In Buffalo’s ‘digital deserts,’ more than half of households lack Internet”

A story I wrote for last Sunday’s paper relied on a pretty shocking statistics: In a neighborhood only minutes from the Buffalo News, fewer than two in five households have home Internet service.

Poynter was kind enough to include the story in its morning media newsletter Tuesday, where Tom Jones called it “the type of important story that news organizations in every major U.S. market could and should do” (yay!). Since then, I’ve gotten several emails from local reporters looking to unearth this data for their city.

Fortunately, it’s not at all hard to do. You just have to know where to look. So below, find my abbreviated, three-step guide to reporting on the digital divide near you.

Step 1: Find neighborhood-level Internet data

This story pulled from the Census Bureau’s 2013-2017 American Community Survey, the first to report Internet access rates down to the tract level. A Census tract is the bureau’s second-smallest geographical unit, consisting of 1,200 to 8,000 people.

There are 297 Census tracts in Erie and Niagara counties. I downloaded data for all of them using factfinder2.census.gov. You can do the same by advance-searching your local city/county, selecting “Census tracts” as the geography filter, and choosing “B28011 — Internet Subscriptions in Household — 2017 ACS 5-year estimates” as your document title.

Step 2: Match Internet data and demographics

Access rates are half the story, though. I also wanted to know why some neighborhoods were better-connected than others. Studies have pointed to a range of reasons some people don’t have Internet, from age and disability to poverty and issues with rural access.

So to help narrow that field, I also added to my growing Excel sheet tract-level ACS data on demographic factors including age, poverty levels, English fluency and disability status. I also coded each tract as “urban” or “rural” based on Census categorizations.

When Excel’s CORREL function revealed a strong correlation between access, poverty and educational attainment, I knew I had enough data to start pursuing an angle. I contacted public schools and community groups in the worst-connected urban neighborhoods to find out if access among low-income folks was on their radar.

Step 3: … But wait, there’s another spreadsheet!

As I began interviewing, I heard two complaints over and over again: that a single Internet provider held a “monopoly” in Buffalo, and that the best and fastest Internet speeds weren’t available to city residents.

To verify this, I turned to the Federal Communications Commission’s broadband deployment records, also called Form 477. Internet companies are required to file twice-yearly reports with the FCC declaring where they offer service and at what speeds.

By sampling this data for representative Census tracts in Buffalo and its suburbs, I was able to confirm that (a) city residents essentially have only one terrestrial broadband option and (b) that option runs more slowly in Buffalo than it does in many suburbs. (Fwiw, I think this is a prime area for follow-up once I figure out a way to stop the full file from crashing my computer.) You can find a guide to Form 477 here.

That’s all the heavy lifting, though! After that, it was all phone calls and interviews and frantic consultations with a News developer who helpfully explained to me how the actual mechanics of the Internet work. And two weeks later, I had a story that — as one source told me post-publication — may “really move the conversation forward.”

If you have further questions about this data, I’m happy to answer them. And if you end up doing a story in your market on the digital divide, please holler! I’d love to read it.

(As an aside, I plan to do regular blog posts of this sort. I have NO IDEA if anyone will read them, but I’m on a mission to show more of my work.)

New year, new blog

I’m forever telling myself I’ll start a blog and forever not getting around to it. OR forever writing tortured, self-conscious first posts and later consigning them to oblivion.

But this is the year, I think. I started a job in local news. And as I work deeper into the particulars and nuances of that job, I find myself wanting a platform to share more with readers.

This is partly inspired by things I’ve read about Hearken’s “Open Notebook,” a nascent (?) project designed to build reader trust. It’s also borne out of my frustration with Twitter and Facebook both as companies and platforms for substantive discussion.

But it mostly draws from my earnest belief that moving to a local paper affords me a unique chance to engage readers in my community. And maybe to demystify — for just a few people!! — a profession that many dislike and distrust lately.

More tk, as they say. This is all an experiment! But man, it feels good to write a first post and actually publish it…