Dear Spreadsheet Whisperer,
I am a grassroots organizer in WV working on an electoral political uprising (huzzah!) and I am assembling contact lists of voters, donors, and volunteers. Our campaign is still young, so we haven’t invested in an official campaign database yet. We need to figure out how to expand our Master Contact List, including plugging in data from Google Forms. Can you help us organize our data now, and set ourselves up for success later?
Signed,
(our data are) Wild but not Wonderful
Dear Wild,
First of all, I want to commend you! Making these type of contact lists is no small feat! The effort and thoughtfulness that you have put into organizing your info (professionals call it “data architecture”) will serve you well as you build your campaign and scale up your data model. For now, I think you’ve built a solid foundation, and with a lil bit of elbow grease, we can take it to the next level. I always like to give credit where credit is due, and you deserve a round of applause.
You had some really good questions like “how do I link a Google Form to an existing Google spreadsheet?” and “how many columns is too many columns?” I love it when people ask me such concrete questions. Round of Applause #2 coming your way!
“Linking in” Google Sheets and Google Forms
I did some research today about linking and unlinking Google Forms, also known as selecting your “response destination.” It looks like there are a few ways to get to the same goal. You can either navigate from the GoogleSheet you’re in (aka your destination) and click “Tools” > “Create a Form” OR you can create a Form first and then link it to your destination (master contact spreadsheet stored in Google Sheets). Either way should work totally fine! In my demos, I used the first method and it took a few minutes for the responses to show up in the response destination, but eventually they came around and now, new responses are showing up instantaneously. Google has some decent documentation that you can check out if you want to learn more. I also learned that you can create some data validation rules to catch common data problems and help you improve your overall data quality. Finally, I found some fascinating tutorials for moving spreadsheet rows from one tab to another based on criteria you set. You didn’t ask for help with this, but it might come in handy later. Check out documentation here (including example code) and here (video).
Growing, growing, gone!
Your second question was about how many columns is too many. I think you stumbled on a very foundational principle of databases, which is… when do you subdivide your data into multiple related tabs. Woah woah woah, related? What the heck does that mean?
Here’s an example that might help. If you have a contact list, you might add a column for “Phone Outreach” where your team can record if anyone has called this person. Then you might add “Phone Outreach 2” and “Phone Outreach 3” to represent each time you called them. As you can imagine, this could go on forever! Plus, you might need to add complexity like… “Phone Outreach 1 (summary)”, “Phone Outreach 1 (date and time),” “Phone Outreach 1 (follow up notes),” “Phone Outreach 1 (who called?),” and then you’d repeat all of those columns for Phone Outreach 2. And “Donation 1” “Donation 2” etc. What do you do if you call someone 100 times and they make 50 donations? Your simple contact spreadsheet can get way out of control. Where do you even look to find the most recent information? Who’s responsibility is it to add “n+1” columns to the sheet? What about archiving or hiding columns that are no longer useful?
A more sophisticated way to store the information is to have one spreadsheet tab of PEOPLE and one spreadsheet tab of PHONE CALLS and link them together. What do they have in common? The person who is being called, of course!
You could do a similar thing with donations.
And event RSVPs.
And voting history.
Suddenly, you have a list of people … where each person is connected to a whole lot of information! Ta-da, a relational database. That means you have more than one spreadsheet but all of the data are related.
There are lots of relational databases out there that you can purchase and make them into a home for your data. Similar to buying a house, no database is perfect and you’ll have to do a lot of work to furnish it and make repairs over time. Plus, they’re often expensive unless you get a real fixer-upper.
Wild, before you invest in your permanent data home, you might want to check out a system where you can build a small version of a relational database and try it out. The platform I like for that is called AirTable (not linking because I don’t want to get all of their marketing ads). I’ve used it with grassroots groups before as a nice bridge between Google Sheets and a cloud database like Salesforce, NationBuilder or CiviCRM.
I’m excited to get to a point where your data are wild AND wonderful, just like your beloved state. Thanks for giving me a chance to help out – know that I am cheering you on, and standing by if you need some extra data support!
Do YOU need help from the Spreadsheet Whisperer? Feel free to write in through the blog or send me an email at hello@thedataarealright.blog.
7 thoughts on “Dear Spreadsheet Whisperer: (our data are) wild but not wonderful”