Calculate a membership renewal rate from an export

A current Wild Apricot member export can give you an estimated renewal outcome rate. It cannot give you a full renewal history. The export has one Renewal date last changed value for each member, so it shows only the latest change. Administrators have asked Wild Apricot for a count of recent renewals on the Member summary. Use the method below and state that the result is an estimate.

Export the right fields

Export your members from Members > Summary. Use CSV or XLS format. Include these fields:

• User ID
• Membership level
• Membership status
• Member since
• Renewal due
• Renewal date last changed

The same values are on the Membership tab of each contact record. For the full steps, read how to export Wild Apricot members.

Use a clear formula

For one month:

renewed members ÷ (renewed members + lapsed members)

Count a member as renewed when all these conditions are true:

• Renewal date last changed falls in the month.
• Membership status is Active, Pending - Renewal or Pending - Level change.
• Member since is before the start of the month. This keeps a first join out of the renewal count.

Count a member as lapsed when Membership status is Lapsed and Renewal due falls in the month. Wild Apricot gives the Lapsed status to a member who lost membership privileges because of unpaid fees. For more about this status, read lapsed, renewal overdue and suspended.

For example, if 42 members renewed and 8 members lapsed in one month, the estimated renewal outcome rate is 42 ÷ (42 + 8) = 84%.

Use completed months

Do not mix a partial current month with completed months. If you export on October 20, report through September 30. The report tool leaves the incomplete export month out of the rate and out of the 12-month totals.

For a 12-month result, add all renewals and all lapses across the last 12 completed months. Then apply the formula one time. Do not calculate the average of the 12 monthly percentages. An average gives a small month the same weight as a large month.

The tool shows the estimated renewal outcome rate for the last 12 completed months only. Its monthly table also shows Renewals and Lapsed for each month, so you can check the arithmetic. See the metric definitions.

State the limits beside the number

Put this note next to the number in a board report:

Estimated from one current member export. The export shows each member’s latest renewal-date change and current status, not a full transaction history.

Two conditions make older months less reliable:

• An administrator can edit a member’s renewal date on the Membership tab. Renewal date last changed records the date of the latest change, so a manual edit can move a member into a different month.
• A member can renew more than one time during a long reporting range. The export keeps only the latest change.

For these reasons, recent completed months give a better estimate than older months.

Keep it separate from retention

Renewal outcome and membership retention answer different questions. The formula above asks what happened to members who reached a renewal outcome in a month. A retention measure usually compares the members at the end of a period with the members at its start, with an adjustment for new members. Write the name of the measure and its formula in every report, so that readers know which question it answers.