Table of Contents

Goals

  • To eliminate CA’s unorthodox approach to modeling memberships over time… i.e. creating a new membership record for each period rather than renewing memberships.
  • To allow CA to return to orthodox modeling in the event of some anomaly (e.g., merging of two contacts with existing membership history).
  • To report on the full history of membership events.
  • Future-proof for updates and new features in the CiviMember component by using orthodox membership modeling.
  • Distinguish between direct and indirect (aka conferred or "by association") memberships.

Concepts

Contact and Membership Classes

Chorus America deals with two classes of membership -- individual and organizational -- and two classes of contact -- individual and organization. While the contact classes are formally modeled in the system, the membership classes presently have no formal model.

The rules regarding which class of contacts can hold which class of membership follow:

  • An individual contact should be allowed to have at most one individual membership. Changes between the membership types within that class are treated as upgrades or downgrades.
  • An organizational contact should be allowed to have at most one organizational membership. Changes between the membership types within that class are treated as upgrades or downgrades.
  • An organizational contact should never have an individual membership.
  • An individual may have an unlimited amount of conferred memberships (memberships they are entitled to by virtue of their relationship to a member organization) but should never hold an organizational membership directly.

Membership Events

Chorus America wishes to leverage the data in CiviCRM to report on following membership events. (Some of these are events that CA has not specifically requested but which could be reported if so desired.)

Event Definition
Join The act of becoming a direct member for the first time.
Conferment The act of receiving a conferred membership.
Renewal The act of renewing (i.e, extending the end_date of) a direct membership while the membership is in a non-expired status.
Rejoin The act of becoming a direct member (again) after allowing an existing membership to expire.
Level Change A change in (direct) membership type, usually as a result of a Renewal/Rejoin.
Lapse The act of allowing a direct membership to expire beyond its grace period.
Abandonment The act of intentionally allowing a direct membership to expire beyond its grace period because the contact intends to change the class of membership (e.g., they’ve started a chorus through which they now receive membership, so they no longer need an individual membership).

Approach

The goal of remodeling membership (and associated history) to match the default/core model means that all memberships of the individual class should be squashed into one per contact.

For organization contacts, all organizational memberships should be squashed into a single membership record.

Conferred memberships are essentially mirrors of organizational members. It may suffice to delete child (conferred) membership and membership_log records that reference a parent (organizational) record which is to be deleted/squashed, then take a pass at the database to update all surviving child memberships to match the details of their parents.

The value of membership_log data for conferred memberships is unclear. On the one hand, they follow the events of the parent record exactly, which can be misleading. For example, a just-added employee will have the same “member since” and “status” values as the organization, resulting in the new employee membership having a status of “Current” or “Grace” (or other) based on the parent record’s status at the time the employee was added, or a “member since” value of 1990 despite the employee’s hire date in 2017. On the other hand, we may able to infer the employee’s “join” date by the timestamp of the first membership_log record.

Modeling of Membership Events

We are considering (but not committed to) facilitating reporting with more thorough modeling (making interpolation/inference explicit), while not breaking future-proofing.

For example, Chorus America has expressed interest in creating a "Rejoined" membership status. Such a status has limited utility to Chorus America, as the status will eventually change to “Expired,” “Grace,” etc. (which they understand and accept), but it could be useful in making the model more explicit so that the report has to infer less.

Because of Chorus America’s long history with this database and CiviCRM’s historical inconsistency handling of activity creation for membership events (not to mention numerous related regressions and edge cases), we are loathe to use activities as the basis for our reports.

Inferring Events from the Membership Log

In particular, Chorus America is interested in reporting on the following events. The models below assume that the membership civicrm_membership_log records have already been squashed per above.

Event Model Questions
Join The first civicrm_membership_log record for a membership where the owner_id is NULL.
Conferment The first civicrm_membership_log record for a membership where the owner_id is not NULL. Per discussions with Ed, the owner_id field will distinguish between new members and board members or employees added 18 months later. CA would like to recognize the beginning of a conferred membership (e.g., addition of a new employee to a long-standing membership), but kick off different communications than for their "new member" processes. Note: this is the only event Chorus America cares to recognize for a conferred membership.
Renewal For a direct membership (owner_id is NULL), when viewing the civicrm_membership_log records ordered by modified date, those records which have different end_date values than the previous row should be considered Renewal events, provided the status of the previous row is not “Expired.”
Rejoin For a direct membership (owner_id is NULL), when viewing the civicrm_membership_log records ordered by modified date, those records which have different end_date values than the previous row should be considered Rejoin events, provided the status of the previous row is “Expired.” Should we register Rejoin events for indirect members?
Level Change For a direct membership (owner_id is NULL), when viewing the civicrm_membership_log records ordered by modified date, those records which have different membership_type values than the previous row should be considered Level Change events.
Lapse For a direct membership (owner_id is NULL), when viewing the civicrm_membership_log records ordered by modified date, those records which have a status of “Expired” should be considered Lapse events, provided they have different status values than the previous row. Note: Lapses and Rejoins are not mutually exclusive. That is, if the grace period ends 1/7 for a membership owned by Acme Chorus, then Acme purchases a new membership on 4/1, the event on 1/7 should be recognized as a Lapse, and the event on 4/1 should be recognized as a Rejoin.
Abandonment The first civicrm_membership_log for a membership record with a status of “Abandoned.”

Technical Requirements

Audit Log

For the purposes of debugging, quality assurance, and (hopefully we don’t need this) reconciling old records with the rewritten history, an audit log is desired.

It will suffice to take a snapshot of the pre-transformation tables and to create a custom activity “Membership Merge” for each merged membership. The ID of the surviving membership will be stored in the Activity’s source_record_id field (the same field that is referenced when other membership events are logged as activities), while the deleted membership ID will be stored in a custom field. Using a custom activity rather than an ad hoc database table provides some visibility to administrators and as well as a logging mechanism for ad hoc merges that may occur in the future.

Reconfiguration of Membership Types

Based on user testing and research (resulting in proposed updates to the product documentation), it is clear that at least one dummy organization ought to be created so that the Membership Organization does not overlap classes of membership. For example, the organizational membership types could continue to use the Chorus America contact if the individual membership types were updated to specify a dummy organization Chorus America Individual Memberships. This should be done whether or not membership history is rewritten. Research findings and rationale:

  • For CiviCRM’s membership up-sell feature to work -- that is, for a user to upgrade from one membership type to another -- both types must specify the same organization contact.
  • Fundraising concerns aside, the up-sell feature provides desirable behavior: an upgraded membership record is updated to reflect the new type and dates. (Note that the history of changes to the membership record is available in civicrm_membership_log). On the other hand, when a non-upgrade membership purchase is made (i.e., the user chooses a membership type which grants membership in a different organization), a second membership is created, which dramatically increases the difficulty of reporting on certain membership events.
  • In practice, Chorus America allows upgrades for both Individuals (e.g., student upgrades to a “basic” membership) and organizations (e.g., increase in organizational budget pushes it to the next tier).
  • Conferred memberships and the up-sell feature conflict unless the direct and conferred membership types specify different organizations. An individual with both a personal and a conferred organizational membership can, in some cases, inadvertently change the type and end date of the conferred membership (which should change only when the organization’s membership record changes) when upgrading the individual membership.
  • There is no scenario in which it is desirable for a contact to have two concurrent individual memberships.

ETL Process

To achieve Chorus America’s membership modeling goals (and thus satisfy its reporting needs), we must develop an ETL (extract, transform, load) tool.

The tool will extract a list of IDs for contacts with more than one non-conferred membership using SQL. Each contact ID will be passed to a custom CiviCRM API membership.merge, which will handle transformation and load. The rationale for this approach is that it allows later ad hoc membership merging, to correct for anomalies arising from contact merges, data entry errors, etc.

The custom API will:

  • Accept a contact ID as its only argument.
  • Ignore conferred memberships unless a merge is being performed on the conferring membership.
  • Get a list of non-conferred memberships that reference the same membership organization (as configured in the membership type), and choose the surviving membership record based on latest expiry date. The surviving membership will be updated to include the earliest “member since” date. The start_date will need to be calculated based on lapses.
  • For each membership to be merged:

    • Update the associated membership_log records to refer to the surviving membership ID, deleting overlapping history per the diagram below:img
    • Delete any membership Activity which references the membership ID (field: source_record_id) during the overlapping periods per above.
    • Update any remaining membership Activity which references the membership ID (field: source_record_id) to reference the surviving membership instead.
    • If the membership is of a different type than the previous one in the chain of memberships to be merged, create a (core) “Change Membership Type” activity to record the type change, dated to match the first preserved civicrm_membership_log record.
    • Update any civicrm_membership_payment record which references the membership ID.
    • Delete the membership record (unless it is the surviving record).
    • Write to audit log.
    • If the membership is conferring (i.e., has children):

      • If it is not the surviving membership, delete the children.
      • If it is the surviving membership, then for each child membership:
      • Capture the date of the first membership log record (X), then delete all civicrm_membership_log records associated with the child. (Note that the earliest log record is likely to belong to a child membership slated for deletion -- as opposed to the surviving membership record).
      • Create a new history for the child by copying the parent history starting with first record dated X. (Starting from X instead of the beginning of parent history preserves the ability to register a Join event for the conferred membership.)

      • TODO: Membership Activities are created automatically for conferred memberships (i.e., a Renewal Activity is created for Joe Employee when Acme Chorus renews); these will need to be reconciled as well as the member_log history.

    Miscellaneous Questions:

  • What happens to conferred membership records when the relationship expires or is deleted/deactivated? (i.e., are they affected by whatever problems we vaguely remember in #1?)

    When a conferring relationship is deactivated or deleted, the conferred membership records (and all associated membership log records) and immediately and permanently deleted.

  • Verify whether or not we can basically ignore recurring contributions (auto-renewals) because they are encapsulated with the contribution records. (i.e. so long as the contribution record points to the correct membership record, we are good.)

    The civicrm_contribution_recur table does not reference the membership ID in any way.

  • Do we have to do anything with line item or transaction records?

    No:

    • The trxn tables contain no references to the membership ID.
      • The civicrm_line_item table refers to some other entities which can be traced to a membership type id, but not a membership ID.

Updated: 3-21-2019