Civi CRM and Membership
BACKGROUND
Civi CRM is a Customer Relationship Management system with less of a focus on sales, pipelines, call booking and the like. In the past we’ve had a look at Sugar CRM, another open-source CRM offering, but found it too sales-orientated.
Our client is a national membership organisation with a campaigning, quasi-political role. We have used Civi CRM along with Civi Member to run the membership system. By integrating Civi with the Joomla CMS and phpBB forum software we can ensure that only paid-up, current members get access to members-only content. Civi CRM handles new members, membership renewals, emailing the membership and producing magazine labels for posting.
This article documents a few things we learnt along the way in the hope it will be useful to others and provides a membership number generator with absolutely no warranty or guarantee of any kind.
CIVI IMPORT
We had over 3000 member records to import and the first thing to realise about Civi is that you have to do at least two imports:
- Import contacts – this is all the demographic stuff, name, address, email, etc. This import is for single individuals – not households.
- Import membership data, e.g date joined, renewal date, membership type
You may have to do a further import if you have households. In our case a household could consist of 2 people but only receive one copy of the club magazine. More on households later.
Before you can begin the actual import you need to:
- setup your membership types
- define any custom fields particular to your organisation
- decide on whether your membership is for a fixed or variable period. Ours was for a fixed period, i.e. January to December, with those joining later in the year receiving a discount. The club considers those people who join in November to have membrship for all of the following year (they pay a bit more). Civi solves this situation nicely with its Fixed Period Rollover Day. i.e. membership signups after this date cover the following calendar year as well.
Contact Import
Once thats done you start by doing the (single) contact import. Here are a few gotchas we came across:
- We received data from the old system in an Excel spreadsheet. Excel would occasionally strip out leadings zeros when saving to .csv. For example a membership number of 0121345 would become 121345.
- civiCRM insists on calling (UK) counties State/Province, its County field is for US style counties (Think Dukes of Hazard County!) and it has its own peculiar TLA for counties in the search results, e.g. GLS for Gloucestershire, although viewing, editing and mailing a contact all use the proper word.
- Civi is very picky about imported County names, e.g. the import failed on Tyne & Wear but was ok when amended to Tyne and Wear. However, typical UK shortenings of county names such as Beds, Bucks, Oxon, which were all present in our data failed
- Civi doesn’t know about at least 1 UK county – Durham
- Country is a required field that wasn’t present in our data, easy to add in the data and then update CiviCRM > Administer CiviCRM > Global Settings > Localization with your country
Its important to use your current unique identifier during the import as the “External Identifier (match to contact)” field because this ties the membership data to the contact data when doing the member import.
Member Import
This was more straightforward with less custom data to import and get right. We found that Civi Member will calculate an expiry date for your LIFE members if you inadvertently include an end date in your data.
Household Import
The club has “Joint” members, 2 or more people who live at the same address and get discounted membership because they only get one club magazine. We only want to hold one instance of their address and then link each member of the household to that address. The import allows this with a Contact Type of “Household”.N.B. Household name is a required field for this import. Household name was created via an Excel formula, e.g.: =D40&”,”&C40&” & “&E40&” Household”
where:
Dnn is the Surname column
Cnn is the First name column
Enn is the Joint name column
It should then be possible to import each person in the household and have Civi link them all up. We found this to be extremely buggy. In the end we imported the main person without their address and manually linked thm using the Civi contatc edit screen. We could do this because we only had a few joint type memberships. If you have too many to do manually watchout this area needs some work.
MEMBERSHIP NUMBER
Civi Member doesn’t generate new membership numbers or codes and this was a requirement for our client. By searching the Civi forums it became clear we would have to write our own because no alternative existed. We provide a simplified version of our number generator for you to download and use absolutely no warranty or guarantee of any kind.
You can see the membership number generator in this screenshot from the contact edit screen:
The new field Membership ID (marked by the red asterisk) has a link under it “Generate next membership ID”. Clicking on the link opens the little pop-up window showing the member ID. Clicking the copy button inserts the new membership nbr and closes the pop-up.
To implement:
1. Create a table in the database:
DROP TABLE IF EXISTS akZZZmembid;
CREATE TABLE akZZZmembid
(mid_key CHAR (10) NOT NULL PRIMARY KEY,
mid_current_year YEAR (2) UNSIGNED NOT NULL,
mid_seq_no INT (8) UNSIGNED NOT NULL,
mid_username CHAR (100),
mid_dt_tm TIMESTAMP
);
where ZZZ is a unique identifier for the mysql table name, chosen by you.
2. Create a PHP script to generate membership IDs:
This one uses a format of 2 letters, followed by a year, followed by a sequence, followed by two letters. You can modify it to suit. The alphabet array allows you to choose which characters will be included, this allows you to limit it (e.g. some people cannot tell a 1 and an l apart, or use non-ascii or meta-characters.
require_once ‘db_common.inc’;
connect ();
$alphabet = array (‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’,’k’,’l’,‘m’,’n’,’o’,’p’,’q’,’r’,’s’,’t’,’u’,’v’,’w’,’x’,’y’,’z’);
#do {
### Do first alphas
$rnd_nbr = rand(0, 25);
$first_alpha = $alphabet[$rnd_nbr];$rnd_nbr = rand(0, 25);
$second_alpha = $alphabet[$rnd_nbr];### Get year
$YY = date(“y”, time());$seq_nbr= get_seq_nbr ($YY);### Do last alphas
$rnd_nbr = rand(0, 25);
$third_alpha = $alphabet[$rnd_nbr];
#print “Number: $rnd_nbr Letter: $alphabet[$rnd_nbr]”;
$rnd_nbr = rand(0, 25);
$fourth_alpha = $alphabet[$rnd_nbr];
### Build up the memb id
$memb_id=$first_alpha.$second_alpha.$YY.substr($seq_nbr, -4).$third_alpha.$fourth_alpha;
print “Memb ID is $memb_id
“;
?>
$Table = ‘akZZZmembid’; # Name of the table holding tokens
### Update the DB
$query = “insert into $Table values (‘$memb_id’,$YY,$seq_nbr,’website’,NOW())”;
$result = mysql_query($query);
$nbrRows = mysql_affected_rows();
$mysql_errno=mysql_errno();
$mysql_err=mysql_error();function get_seq_nbr ($YY) {
### Declare some local variables
$Table = ‘aktrfmembid’; # Name of the table holding tokens
$debug = 0;
$mysql_errno = 0;
$a_row;
$error =0;
$seq_no =0;$query = “SELECT mid_current_year, mid_seq_no FROM
$Table where mid_seq_no = (select max(mid_seq_no) from $Table)”;
if ($debug) {
print “Query is “.$query;
}
$result = mysql_query($query);
$nbrRows = mysql_affected_rows();
if ($debug) {
$mysql_errno=mysql_errno();
$mysql_err=mysql_error();
print “Msg/errno “.$mysql_err.” / “.$mysql_errno.”rows :”.$nbrRows.”
“;
}
if ($nbrRows >0 ) {
$error=0;
$a_row = mysql_fetch_row($result);
$mid_current_year = $a_row[0]; # worked
$mid_seq_no = $a_row[1]; # worked
}
else {
$error=3; # failed
return 9999;
}
if ($debug) {
print “DB YY $mid_current_year “;
print “Curr YY $YY “;
print “Seq $mid_seq_no “;
}
### Has there been a year roll?
if ($YY != $mid_current_year) {
if ($debug) {
print “Year roll from $mid_current_year to $YY”;
}
$seq_no=’20’.$YY.’0001′;
}
else {
$seq_no=($mid_seq_no+1);
}
if ($debug) {
print “seq no is $seq_no
“;
}
return ($seq_no);
}?>
3. Insert the link and Javascript to create the pop-up window. In the Civi back-end navigate to
CiviCRM » Administer CiviCRM » Custom Data
where your custom membership field is defined and insert: