Spreadsheet basics for membership numbers

Your membership software, your payment processor and your email tool all produce reports. Each answers the questions its makers thought of, and none of them talks to the others. Sooner or later you will want a number that no single report gives you, such as churn by plan over the last year, or revenue per member alongside email sign-ups. That is when a plain spreadsheet becomes the most useful tool you own.

You do not need advanced skills. A handful of layout rules, a few formulas and some careful habits will carry you a long way, and the workbook you build will belong to you whatever software you use in future.

Lay out the workbook in three tabs

Most membership workbooks need only three tabs, sometimes called sheets:

• Raw data. Exports from your membership software and payment processor, pasted in exactly as they arrive, each with the date of export. Never edit these by hand.
• Monthly summary. One row per month, with the figures you track. This is the tab you look at.
• Notes. One line per month recording anything unusual: a promotion, a price change, an outage, a newsletter feature. Months later, this tab explains every odd bump in your chart.

Within any table, follow a few rules. Put one header row at the top with a clear name for each column. Use one row per record and one column per piece of information. Keep one fact in each cell, so a plan name and a price go in separate columns. Avoid merged cells and blank rows, which break sorting and formulas. Write dates the same way every time.

A worked example: the monthly summary

Take a hypothetical online club for miniature figure painters, run by an artist named Lucia. The figures are invented to show the method. Her summary tab has these columns: A, month; B, starting members; C, new members; D, cancellations; E, ending members; F, churn; G, recurring revenue; H, revenue per member.

Two rows look like this:

• March: 410 starting, 35 new, 18 cancelled, 427 ending. Churn 18 ÷ 410, about 4.4%. Recurring revenue $6,900, so revenue per member is $6,900 ÷ 427, about $16.16.
• April: 427 starting, 28 new, 21 cancelled, 434 ending. Churn 21 ÷ 427, about 4.9%. Recurring revenue $7,050, so revenue per member is about $16.24.

Lucia types only the new members, cancellations and revenue each month. Everything else is a formula. In the March row, which is row 2:

• Ending members: =B2+C2-D2
• Churn: =D2/B2, formatted as a percentage.
• Revenue per member: =G2/E2

In April's row, starting members is simply =E2, the ending figure from March. Copy the formulas down and every new month fills itself in. Because the starting figure always comes from the month before, a typing mistake shows up immediately as a total that does not match her membership software.

The formulas worth learning

Almost every spreadsheet program shares these, with the same or very similar names:

• SUM and AVERAGE for totals and averages of a column.
• COUNTIF and COUNTIFS to count rows that meet one or more conditions, such as active members on the Premium plan.
• SUMIF and SUMIFS to add up values that meet conditions, such as revenue from annual members.
• A lookup function to pull information from one table into another by matching a member ID, for example adding each member's plan to a list of cancellations.
• A summary table tool, often called a pivot table, which groups and totals rows for you, such as counting members by plan and source in a few clicks.

With COUNTIFS and SUMIFS alone you can calculate churn and revenue for each plan, which is the start of the breakdowns described in breaking your numbers down by plan and source.

Charts that help

For membership figures, a simple line chart with months along the bottom answers most questions. Chart one or two figures at a time, such as ending members, or churn alongside new members. Avoid three-dimensional effects and pie charts with many slices; they look impressive and communicate little. Start the vertical axis at zero for counts, so small wobbles do not look like dramatic swings.

Habits that keep your numbers trustworthy

• Separate inputs from formulas. Shade the cells you type into, and never type a number over a formula.
• Check against the source. Once a month, compare your ending members with the count in your membership software. If they differ, find out why before going further.
• Keep dated copies. Save a copy before any big change, and keep the workbook backed up.
• Collect only what you need. Summary figures rarely need names or email addresses. Where raw exports contain personal details, limit who can open the file and delete old exports on a schedule, in line with how long you keep member data.
• Update on a routine. A workbook updated once a month on the same day is far more useful than one updated in bursts; it fits neatly into a weekly admin routine on the first week of each month.

Build yours in an hour

1. Create three tabs: raw data, monthly summary and notes.
2. Set up the summary columns for starting members, new, cancelled, ending, churn, recurring revenue and revenue per member.
3. Enter the last three to six months from your membership software.
4. Add formulas for ending members, churn and revenue per member, and copy them down.
5. Add a line chart of ending members and one of churn.
6. Write a note for any unusual month, and set a monthly reminder to update the workbook.