Reference manual for the OCC Membership Secretary, providing an overview of the membership types, payments, administration processes and club reporting

OCC Membership Secretary - Chris Baxter
Date – 26 November 2025

This guide outlines the structure, responsibilities, and systems used to manage membership within Otley Cycle Club (OCC), a not-for-profit organization. It ensures consistent administration, secure data handling, and effective communication with members.

Otley Cycle Club operates as a closed membership group. Members are entitled to the following benefits:

• Participation in club cycle rides
• Access to social events
• Subsidised cycling clothing and accessories
• Training opportunities and competitive events

Most OCC members join through subscription-based memberships. The following categories apply:

Note: In some cases, it may be more cost-effective for a single adult and youth to join separately under Individual and Youth memberships rather than Family.

These categories are granted at the discretion of the Club Committee:

The Membership Secretary is responsible for administering club membership and serves as a member of the Club Committee.

• The CMS is the master database for all member records.
• It is integrated with the club website and maintained by a third-party provider.
• The CMS supports card payments only and lacks functionality for:
  • Membership renewal reminders
  • Membership card issuance

• Developed by OCC to complement the CMS.
• Functions include:
  • Scheduling and sending membership reminders
  • Managing card distribution
  • Tracking lapsed memberships
  • Recording payments via card, BACS, and cheque

Stores complete member records, including:

• Full name
• Address
• Date of birth
• Email address
• Membership type
• Membership end date

Contains:

• Subset of member data for all records
• All payment data
• Operational tools for managing:
  • Payment reconciliation
  • Reminder scheduling
  • Card distribution
  • Lapsed membership tracking

Used for:

• Bulk email campaigns
• Membership renewal notifications
• Card issuance alerts
• Lapsed membership communications

Used for reporting and communication:

• Microsoft Excel and Word for documentation
• Google Mail for informal or ad-hoc correspondence

All membership-related files, guides, and administrative tools are stored in the club’s Dropbox system.

• Access is granted to the Membership Secretary by the Club Secretary.
• Dropbox serves as the central repository for membership administration resources.

• The preferred method for renewing memberships.
• Only route for new member registrations.
• Members are prompted to renew after their membership end date.
• Payments are processed via STRIPE, a trusted third-party card payment system.

Members may pay via BACS transfer.

• Club bank details are provided in renewal emails.
• Commonly used for individual payments or multiple memberships in one transaction.

• Members may pay by cheque, with bank details provided in renewal emails.
• Members must include identifying details on the back of the cheque and notify the Club Secretary via email.
• This method is least efficient, due to delays in processing and card issuance.

Key activities include:

• Identifying members due for renewal
• Sending initial and follow-up renewal reminders
• Logging received payments
• Updating CMS renewal dates for BACS and cheque payments
• Updating CMS renewal dates for Family Members (only for Family Lead)
• Issuing updated membership cards
• Reconciling membership data
• Responding to member queries

Key activities include:

• Identifying new members
• Logging received payments
• Issuing new membership cards
• Reconciling membership data
• Responding to member queries

For members who have not renewed within 3 months of their membership expiry:

• Identifying lapsed members
• Notifying members of lapsed status
• Reconciling membership data
• Responding to member queries
• Ensuring compliance with data protection principles

• Issued to new and renewed members
• Includes membership expiry date and emergency contact details
• Enables access to subsidised goods at approved retail outlets

• Membership data is reconciled bi-monthly
• Ensures alignment between CMS and MAS for new, renewed, and lapsed memberships
• Payment records are reconciled with the Club Treasurer to verify:
  • Bank transactions
  • STRIPE card payments
  • Membership activity logs

Provider: Codecraft (third-party cloud-based platform)

Purpose:

Delivers member-facing services including:

• Rides Application & Library
• Events Calendar
• General cycling information

Data Stored:

Primary source of member data:

• Name, address, email, Date of Birth, unique member ID, expiry date
• Other administrative details

Access & Administration:

• Members log in using their email (as username) and a self-managed password
• Membership Secretary has secure admin access (username/password)
• Admin functions include:
• Updating member details (e.g. password, membership end date)
• Resolving login issues
• Downloading data for reconciliation
• All admin actions are logged for audit purposes
• Payment data is handled via Stripe and is not stored in the CMS

See Appendix 1 – CMS Administration Portal

Purpose:

• Managing renewals, membership cards, and lapsed members
• Storing historical payment records (card, BACS, cheque)

Data Stored:

Secondary source of member data:

• Name, email, member ID, expiry date
• Payment history and membership status

Access & Administration:

• Access restricted to Membership Secretary
• Data stored locally and backed up to OCC Dropbox
• Admin functions include:
  • Sending reminders
  • Issuing membership cards
  • Managing lapsed memberships

See Appendix 2 – MAS Administration

Provider: Intuit (third-party marketing platform)

Purpose:

Used to send email campaigns for:

• Membership renewals
• Membership cards
• Lapsed membership notices

Data Stored:

• Email address
• First name, last name
• Membership end date

Tagging System: Members are tagged for campaign segmentation:

• Renewal (initial) Mmm-yy
• Renewal (ongoing) Mmm-yy
• Card Mmm-yy
• Lapsed Mmm-yy

Campaigns & Templates:

• Campaigns use Tags to target recipients
• Templates include:
  • Membership Renewal Reminder (initial/ongoing)
  • Membership Card
  • Membership Lapsed
• Templates are personalised and include hyperlinks to:
  • Club website
  • Login guide
  • Membership card files

Files Uploaded to MailChimp:

• Membership Cards (mm Membership Card month year.pdf)
• Login Guide (OCC First Login.pdf)
• Club Logo (OCC1.png)

See Appendix 3 - MailChimp Administration Portal

• Members are marked as lapsed after 3 months
• Records are deleted after 6 months to allow for rejoining

• Lapsed members are archived after 6 months

Storage:

• Password-protected files stored in OCC Dropbox, specifically within the folder OCC New System.

Key Spreadsheets:

• OCC_Reconciliation_20nn_20nn: Monthly membership reconciliation for the reporting year (October – September). Includes checklist for administrative processes, membership count reconciliation and New & Lost members.
• OCC_Committee_Analyse: Age/gender demographics
• OCC Secretary Email List yymmdd: Current member email list

Email Address: otleymembership@gmail.com

Uses:

• Ad-hoc member communication
• MailChimp transport email for campaigns

The CMS Administration Portal serves as the central interface for the Membership Secretary to manage member records and related data within the club’s Content Management System (CMS).

Access to CMS Administration Portal is via the official login portal https://www.otleycycleclub.co.uk/siteadmin

To login authorised administrators must use a valid Username/Password combination for verification:

The main membership administration panel is entered via the Website members tab:

The panel lists all members of the club, and individual member data is edited or deleted using the marked items:

An example member data record and fields that may be amended:

The following fields are critical for accurate membership administration:

Membership Expiry Date

• Automatically updated for new and renewal memberships processed via the OCC website
• Must be manually updated by the Membership Secretary for payments made via BACS or cheque
• Must be manually updated for Family Members

Email Address

• Serves as the primary login credential for OCC members
• Must remain unique and accurate, as it functions as the system’s primary key for authentication

OCC Membership Number

• A unique identifier assigned to each member
• Used for referencing members in offline processes and administrative reporting

Emergency Contact Requirement

• When manually updating a member record:
• An emergency contact name and phone number must be present
• If missing (e.g. from initial data migration), placeholder data must be entered
  • Recommended format: a/0

A full MS Excel CSV download of the CMS is obtained using the download tab:

This is used for running a number of offline membership activities outlined in Appendices below.

The Membership Administration System (MAS) is a locally managed tool developed by Otley Cycling Club to support and extend the capabilities of the CMS. While the CMS handles core member data, it does not support functions such as reminders, membership cards, or recording non-card payments. MAS fills this gap by providing a secure, flexible, and platform-independent solution.

Database Platform:

MAS is built on SQLite, a widely used, serverless relational database stored as a single file. This eliminates the need for complex infrastructure and allows the system to run on standard client devices (e.g. laptops).

Management Interface:

The database is administered using Navicat, a proprietary database management tool that supports:

• Table creation and editing
• SQL query development and execution
• Cross-platform compatibility (Windows, macOS, Linux)
• Easy file transfer and replication

Security & Backup:

Although SQLite does not offer enterprise-grade security features, it is encrypted to industry standards.

• MAS runs locally on a secure device
• Regular backups are stored as ZIP files in the OCC Dropbox to ensure data integrity and member privacy

Navicat enables administrators to manage the MAS database through structured queries and table operations. The system supports four key areas of club administration:

1. Membership Data Management

• Synchronise member records between CMS and MAS
• Maintain consistency and accuracy across systems

2. Subscription Payment Management

• Record and validate all payment types (card, BACS, cheque)
• Maintain historical payment records for reconciliation and reporting

3. Member Subscription Management

• Issue renewal reminders and membership cards
• Track lapsed memberships
• Prepare member data for MailChimp campaigns

4. Reporting

• Generate monthly and ad-hoc reports for club oversight
• Support committee reviews and operational planning

This appendix outlines the technical and procedural framework for MAS administration. It describes the tools and workflows to ensure consistent and secure management of OCC membership data.

1. IMPORTANT – Create a backup using the Pre-Update Archive.
2. Do not rename the CMS CSV data extract – cms-export (see Membership Download).
3. The ‘cms-export’ table will be ‘cleaned’ by removing CodeCraft test members, re-formatting dates, and assigning Family Member status (not set in the CMS data extract).
4. Address exceptions in the ‘exceptions’ table.
   1. CMS Member Exists – likely a new member not yet added to the ‘MEMBERSHIP’ table.
   2. Expiry Dates Differ - Stripe subscription may have been processed, not yet reflected in the MEMBERSHIP table.
   3. CodeCraft Expiry Dates – Minor discrepancies between CMS and MAS expiry dates due to Stripe sync issues. These can be ignored for now; CodeCraft is investigating.
   4. Family Leads Differ – occasionally mismatch when a family member becomes an individual member, CMS may retain the original Family Lead name. CodeCraft is reviewing.
   5. Other Exceptions: Require manual investigation and resolution.

1. Stripe payments:
   1. Do not rename the STRIPE CSV data extract – unified-payments.
   2. The ‘unified-payments ’ table of Stripe payments will be cleaned of unnecessary data generated by Stripe.
2. Bank payments:
   1. Use the relevant banking app or website to identify payments. Match payment details with member supplied transaction references or emails sent to the Membership Secretary (typically in response to reminder emails).
   2. Manually input payment details into the ‘TempBANK’ table. Enter separate records for multiple subscriptions (e.g. partnerships). Do not manually enter name or expiry date, these will auto populate if the member number is found. Adjust expiry date is necessary. Ensure the actual payment matches the bank transaction and subscription payments reflects the number of individuals covered. Validate all entries before committing.
3. Before COMMITTING updates, confirm whether Stripe and/or Bank payments are included, type YES for each applicable method, otherwise NO.
4. IMPORTANT - Create a backup using the Post_Update_Archive to preserve the final state.

1. Membership cards:

Populate the ‘mailchimp’ table with membership card data (see Allocating Members to Tags).

1. Reminders and Lapses:

Populate the ‘mailchimp’ table with reminder or lapse data for the relevant month, use the date parameter to select the appropriate membership cycle. Exclude reminders/lapses relating to Family Members (managed by the Lead Member) and Invalid records (see Allocating Members to Tags).

Generate monthly reports and maintain accurate membership records.

1. Monthly reconciliation tasks:
   1. Generate a list of New / lost members to update the Reconciliation spreadsheet.
   2. Generate the current membership count (input a date parameter representing the start of the current month) to update the Reconciliation spreadsheet.
   3. Generate a members list (input date parameters representing the start/end of the selected period) to populate the Committee Data Analysis spreadsheet (by age and gender demographics).
   4. Generate a member email list (input a date parameter representing the start of the current month) to populate the Secretary Email List spreadsheet.

MailChimp is a third-party Campaign Management System used by Otley Cycling Club to manage email communications related to:

• Membership renewals
• Membership cards
• Lapsed membership notifications

Access to MailChimp is via the official login portal https://login.mailchimp.com

To login authorised administrators must use a valid Username/Password combination plus SMS based 2-factor authentication for verification:

The main Home Screen will be displayed:

The Audience / All Contacts section will contain access to all OCC club members, in particular:

• Email address
• First Name
• Last Name
• Membership End Date

The Audience / Tags section will contain all Tags which OCC club members will be assigned to, in particular:

• Initial Membership Renewal: Renewal (initial) mmm-yy e.g. Renewal (initial) Jul-24
• Follow-up Membership Renewal: Renewal (ongoing) mmm-yy e.g. Renewal (ongoing) Jul-24
• Membership Cards: Card mmm-yy e.g. Card Jul-24
• Membership Lapsed: Lapsed mmm-yy e.g. Lapsed Jul-24

To prepare member data for MailChimp campaigns (renewals, cards, lapses), administrators must import contacts into the MailChimp Audience using the following process:

Importing Member Data

1. Navigate to the Audience > All Contacts section in MailChimp
2. Select Add Contacts from the dropdown menu
3. Choose Import Contacts, then select the Copy and Paste option
4. Click Continue to open the import interface

Data Preparation

• The import box must be populated with member data formatted specifically for the MailChimp audience
  • Use the template file MailChimp_import_template.xlsx (located in: OCC New System / MailChimp Files) to ensure correct formatting

Finalise Import

• After pasting the formatted data, click Continue to Organize
• Proceed with assigning appropriate tags based on the campaign type (e.g. Renewal, Card, Lapsed) and month

Ensure Update any existing contacts is checked, and Continue to tag.

Select the appropriate tag previously created from the dropdown (an example tag is shown), then Continue to match.

The membership details will be matched against the MailChimp Audience (ensure the members identified by the Membership Secretary are included). Then Continue to subscribe.

Finally complete the import. Members will now be allocated to a specific Tag within the MailChimp Audience. The Tag will be used to run a Campaign mailshot to specified members, e.g. membership cards for a specific month.

MailChimp Campaigns are used to send targeted email communications to Otley Cycle Club members. These include:

• Membership renewal reminders
• Membership card notifications
• Lapsed membership alerts

Each Campaign targets a specific group of members using a combination of:

• A predefined email template relevant to the message type (e.g. renewal, card, lapse)
• A Tag applied to the relevant members within the MailChimp Audience

Campaign Naming Conventions

To ensure consistency and traceability, all membership-related Campaigns follow a standard naming format:

All Campaigns are stored in monthly folders named using the format:

• Mship mmm-yy → e.g. Mship Jul-24

Creating a Campaign

Campaigns can be created in two ways:

• From scratch
• By duplicating and editing an existing Campaign

To create a new Campaign:

1. Navigate to Campaigns > All Campaigns
2. Click the Create button
3. Select Regular Email via the Design Email option
4. Choose the appropriate template and audience tag
5. Finalise campaign settings and schedule or send as required

Once the Campaign template is displayed, complete the following fields to prepare the mailshot:

Campaign Setup

• Campaign Name
  • Default name is Untitled, rename using the standard format, e.g. Renewal (initial) Jul-24
• Recipients (To Field)
  • Set the Send to field to the appropriate Tag. Refer to Allocating Members to Tags for guidance
• Email Subject Line
  Choose one of the following, depending on the campaign type:
  • OCC membership renewal reminder
  • OCC Membership Card
  • Otley Cycle Club recently lapsed membership

Email Content

• Click Design Email to open the template selection screen
• Select Saved Templates
• From the Folder dropdown, choose Membership Templates
• Select the appropriate template:
  • Renewal Reminder (initial or ongoing)
  • Membership Card
  • Lapsed Membership
• The selected template will open for editing (if required)
• Click Continue to return to the Campaign setup screen

Editing the Card Template (if applicable)

• The Membership Card template requires a date-specific update:
  • Click the edit block icon (pen symbol) to open the content pane
  • Locate the hyperlink in the phrase: “Your new membership card can be downloaded here”
  • Click within the word “here” to open the URL popup
  • Replace the existing URL with the correct Membership Card link
  • Refer to the Content Management section below for guidance on selecting the correct URL

Finalising and Sending

• Click Finish Later to exit setup and return to the Campaigns section
• The new Campaign will now appear in the list
• Move the Campaign into the appropriate monthly folder, e.g. Mship Jul-24
• Alternatively, duplicate a previous successful Campaign and amend as needed
• To send the Campaign:
  • Open the newly created Campaign
  • Click SEND to distribute the email to members associated with the selected Tag

This section outlines how to manage Membership Card files and email templates within MailChimp for use in club campaigns.

Membership Card Files

Membership Cards are maintained as PDF documents by the Membership Secretary. Each card reflects the membership expiry date for a given month and year.

File Creation and Storage

• Cards are created and stored locally in:
  • OCC New System / Membership Cards / Memb Cards yyyy
• One card is produced per month, per year

Uploading to MailChimp

To upload a Membership Card to MailChimp:

1. Navigate to Content > My Files
2. Select the Membership Cards folder from the Folder dropdown
3. Click Upload and choose the relevant PDF file
4. The file will be placed in the selected folder

Retrieving the File URL

To obtain the URL for a specific Membership Card:

1. Tick the checkbox next to the uploaded file
2. Click View Details
3. Select Copy URL
4. Use this URL to update the “here” hyperlink in the Membership Card email template
   • Refer to Campaign Mailshots for hyperlink editing instructions

Membership Email Templates

Email templates are maintained directly within MailChimp by the Membership Secretary.

Accessing Templates

• Navigate to Content > Email Templates
• Select the Membership Templates folder from the Folder dropdown

Available Templates

The following templates are available and may be edited as needed:

• Renewal Reminder (initial)
• Renewal Reminder (ongoing)
• Membership Card
• Lapsed Membership

Refer to MailChimp’s standard editing tools for modifying template content and layout.