Re-member-ships

Part 2 in a mini-series exploring the great, big world of “de-duping” – or, making sure that you don’t repeat the same information twice in a spreadsheet or database.  In this installment: how using a database structure can help you make “linked” spreadsheets instead of having duplicate information on every row.

Dear Spreadsheet Whisperer,

I inherited a membership management system that makes no sense!  Each time someone signs up, the system creates a duplicate version of them.  Plus, I can’t always tell someone’s membership or payment status.  In some cases, neither me nor the members can remember if they are up to date.  This is simply unsustainable!  Can you help??

Signed, Re-member-ships

Dear Re-member-ships,

You are not alone!  Managing membership databases is really tough – so tough that there are entire software platforms out there that do just that.  The range of memberships is really wide, from CSAs to professional organizations to faith communities to magazine subscriptions to museums to your local gym.  Each one has different structures in place for how they handle payments, renewals, and membership benefits – and perhaps most importantly, how they keep track of “who’s in” and “who’s out.”  In a way, this in/out question is a fundamental database feature.  You can do it in a spreadsheet pretty easily (by filtering on a column) but sometimes you need to filter based on more than one criterion – and that’s where databases come in handy.  Yes, I said the DB word!

Bear with me, Re-member-ships – I have some ideas for “best practices” and I also have some ideas for PUNS!  If you structure your data properly, you won’t have to remember when people re-member.  Are you with me?

From single spreadsheet to relational database

Right now, Re-member-ships, you have all of your data stored in one giant spreadsheet with different columns for Membership 2016, Membership 2017, Membership 2018, etc. I can see how this made sense “back in the day,” but we both think it will be unsustainable and unwieldy for the future. Plus, every time someone fills out the membership form, they get added to the sheet and create a duplicate version of themself!  Yikes!  Finally, you don’t have an easy way to track payments (like online versus check in the mail).

In a relational database, you would have lots of spreadsheets that are linked to each other.  So it would look more like:

• Contacts (with their contact info)
• Memberships (with start date and end date)
• Payments (with payment type, amount, and date received, status)

The Contacts would be your data “home base,” but the database would allow you to capture more info at a glance.  Since there would be a “one-to-many” relationship between Contacts and Memberships, you could easily see how one person has had more than one membership over the years, one Membership row for each year!  You could even have “prospective” memberships in there for future years, and then update them to ‘current’ after you seal the deal.  Each Membership would have a payment linked (or monthly payments, however you do it).

You could filter by All Contacts, Contacts that have  Current Membership, All Memberships, All Prospective Memberships, All Overdue Payments, etc by referring to the different spreadsheets in your relational database.

Instead of having the Sign-Up Form create a new Contact, it would create a new Membership and link it to a Contact who is already in the system (usually by email address).  If the Contact isn’t already in there, then the system could add a new one.  No remembering required for renewing!

There are still plenty of questions to figure out when putting a system like this into practice, like whether you are using an online payment processor, whether you want your members to fill out a form each year to renew or whether you want to update a membership record to “current” on your own.  It might take a bit of expert help to get you up and running, but in the long run, I think it would be a good investment!

I think there are simpler ways to handle memberships (for example, we talked about doing an Eventbrite event once a year where everyone signs up and pays) but I think you would lose the ability to track historic info, or you would end up doing a lot of spreadsheet re-configuring on your own.

I want to leave you with a few resources to get you started.  This is such a good topic, thank you for sharing your data quandary with me!

• Membership database functionality from Neon (btw this is purely marketing – but I think they laid out the functions really well here.  I’ve heard good things about Neon but I think any databases could handle this, whether it’s Salesforce, CiviCRM, or others)
• Linking spreadsheets in Air Table – I know you’re already using AT and I think it can handle your situation!
• How to manage your membership – I think this was a good, overarching guide!

I have complete confidence in you, and I’m standing by to help if the going gets tough.

— The Spreadsheet Whisperer