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:
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.)
|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).|
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.
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.
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.
|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.”|
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.
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:
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.
For each membership to be merged:
If the membership is conferring (i.e., has children):
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.)
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?