How Can We Help?

< Back
You are here:
Print
Table of Contents

Findings 02-Jan-2019 (for the 03-Dec-2018 update)

During the update:

Single.csv

Total Rows 1145 Total number of rows in the imported data.
Total Contacts 1145 Total number of contact records created or modified during the import.
Tagged Imported Contacts : 2 contacts are tagged with this tag. 1143 contacts NOT tagged (already tagged to this tag).

Household.csv

Total Rows 416 Total number of rows in the imported data.
Total Contacts 416 Total number of contact records created or modified during the import.
Tagged Imported Contacts : 416 contacts are tagged with this tag.
Head of Household.csv

Not finishing but has loaded only 392 of the 416 Individuals tagged as Joint with the correct relationship.

Member of Household.csv

Total Rows 416 Total number of rows in the imported data.
Total Contacts 832 Total number of contact records created or modified during the import.
Tagged Imported Contacts : 416 contacts are tagged with this tag. 416 contacts NOT tagged (already tagged to this tag).

Life Member.csv

Total Rows 5 Total number of rows in the imported data.
Total Contacts 5 Total number of contact records created or modified during the import.
Tagged Imported Contacts : 5 contacts are tagged with this tag.

Before the update:

  • The Members Individual Smart Group shows 1,978 (being 1,127 + 5 + 847 – 1 for a person who has both Single and Joint) – see table.
  • The Memberships Individual Smart Group shows 1,556 (being 1,127 + 5 + 424) – see table.
  • The CMS Active Data has 1,556 rows and does agree with the Memberships Individual Smart Group.
Individual Member Counts Contact Type Individual Household
Tag
Single 1,127 1,127 0
Life Member 5 5 0
Joint 1,271 847 424

Findings 03-Dec-2018

  • Mandy from CMS advised that a status of D is entered instead of active/cancelled/left listed address etc when we are made aware that a member is deceased. We then let everyone know – the COTA in question and COTA Insurance if address only PLUS COTA Australia and COTA Holidays, if there is an email on the membership. If a row in the file has D for deceased then everyone on the membership is deceased. If only one of a joint membership were deceased, we would simply remove them and make it a single membership in the other member’s name only.

Findings 01-Dec-2018

  • The Members Individual Smart Group shows 2,333 (being 1,128 + 5 + 1,201 – 1 for a person who has both Single and Joint) – see table.
  • The Memberships Individual Smart Group shows 1,557 (being 1,128 + 5 + 424) – see table.
  • The CMS Active Data has 1,558 rows and should agree with the Memberships Individual Smart Group. One member did not get imported, so all data has been imported correctly.
Individual Member Counts Contact Type Individual Household
Tag
Single 1,128 0
Life Member 5 0
Joint 1,201 424
  • It appears that two people with the same email address throws an error when importing. Removed sterreyj@bigpond.net.au from the female in the household and eainsworth@fastmail.fm from the male to test this assumption. Assumption true see below

 

Matching this contact based on the de-dupe rule would cause an external ID conflict
Either a duplicate to be de-duped
Or the External ID is on the Household and it should be on the Individual (J -> S)
Find Contacts using Last Name
If there is a household and the person is one of them then 2 above
Otherwise if the person shows up twice then 1 above, and merge and go to listing
Keep Dedup on Last Name open
Keep Find Contacts open
……..fast

 

Only remaining error. Can’t work out how to merge…

Reason External Identifier Last Name First Name Individual Prefix Street Address – Home Supplemental Address 1 – Home City – Home Post Code – Home State – Home Country – Home Email – Home Phone – Home – Phone Phone – Home – Mobile Birth Date Membership number – Individual
Matching this contact based on the de-dupe rule would cause an external ID conflict 1019897 Wu Helen 1 Edward St Chadstone 3148 VIC Australia helenwu56@gmail.com 0411 608 879 1019897

Actions for Rob 30-Nov-2018

  • Load up the Contacts fully without loading any Memberships and still have usable data! The Smart Groups don’t use the Memberships, they use the type of the contact and the tags. Excellent, given that the Memberships are problematic.
    • 1. Clear all contacts from Single, Joint and Life Member tags. Done.
    • 2. Recreate all of the .csv files. Done.
    • 3. Upload only the Contact files (ignore the three membership files). Done
    • 4. Fix any deceased contacts. Not done.
    • 5. Create the Smart Groups as required. Done.
    • 6. Test against the uploads. Done.
    • 7. Publish the result using CiviMail. Not done.
  • Need to take “Do not contact” into account in the CMS Importer. Do this manually after the load as there are only 6 people affected and it’s not even clear whether we should take notice of the flags.
  • Install the cleanPhoneNumber macro in the CMS Importer which needs to be applied to all phone numbers. Done.
  • Load all contacts in live and test. Don’t bother with the Memberships Dashboard.

Findings 29-Nov-2018

  • We can load up the Contacts fully without loading any Memberships and still have usable data! The Smart Groups don’t use the Memberships, they use the type of the contact and the tags. Excellent, given that the Memberships are problematic.
  • Need to take “Do not contact” into account and this may involve redoing the .csv files.
  • Implemented a cleanPhoneNumber macro which needs to be applied to all phone numbers.
  • Answered: What about those 7,147 memberships in test (4,385 in live) with null Membership ID that is apparently the primary key. There are 1,297 in test (2,647 in live) non-null. In test, the Memberships with null Membership ID have Contacts that are all Individual contacts. The Contacts are also broken as they have null First Name, Last Name and email. The correct search is null Membership ID and null Display Name. This gives 358 contacts in test. Deleted the contacts which will delete the memberships too.
  • The latest membership prior to this new CMS import is 31-Dec-2017. The earliest Paid_Thru is 31-Aug-2018.
  • Our upload creates New, Current, Grace and Expired memberships. Hmmmm, why expired?
  • Whoops, looks like the old upload of memberships back in 2015 has gone. Finger trouble by Rob. Not an issue as it is in test.
  • Dely Sargeant is on CiviCRM as a Life Member but is in the Inactive file from CMS.
  • Life members appear to have 12 month memberships just like everybody else and they will keep coming through on the CMS files.

Findings 27-Nov-2018

  • The upload of Membership Single reported 1049 uploaded but the resultant memberships were 1048. Why?
  • The Dashboard showed 1013 in Nov and YTD, but 1011 in Current#. Why?
  • Using Search Builder:
    • The Member Status of New was 40 and Current was 933 (sum of 1013).
    • The Member Status of Grace was 40 and Expired 35.
  • We must use “Update” for all imports, thus the CMS Importer has to import a membership on only 1 run per year. Interesting problem. We need a foolproof reconciliation process for this.
  • We haven’t solved the issue of how to put LM memberships in.
  • We can do one run ONLY of the 8 sheets using “Insert” for membership and “Update” contacts and then must not do another run until the CMS Importer ignores 11 out 12 membership “updates”.

Actions after 27-Nov-2018

  • Get answer to each of the question above.
  • Delete contacts with null Membership ID and null Display Name in live.
  • Complete any further testing that is required. This must involve spot checks
  • Load the 8 files into the live environment.
  • Correct all structural issues (Household vs non-Household) that show up in the error files and reconcile against the two membership .csv files.
  • Set up Smart Groups or whatever that are going to make the data more usable.
  • Announce to all staff that the data is now available.
  • (the rest of this list can be spread out over time depending on developer availability)
  • Re-build the CMS Importer to cope with 1 month out of 12 issue.
  • Re-build of the CMS Importer to cope with
  • Productionise the whole show:
    • Set up a versioning of the CMS Importer.
    • Review and re-write doco as required.
  • Consider processing the in-actives as:
    • Extract all Individuals from CiviCRM
    • Do a diff between them and the incoming CMS data
    • Create one uploader for the changes that are required (hopefully small)
  • There should be no interaction between the data month to month except the requirement that the months are processed in sequential order.

Actions for Vidya 26/27-Nov-2018

All of the following must be only in the test environment. Make sure that you don’t keep the production environment open except for this page! The 8th import does not yet exist. These are the Life Members and we need to discuss them. There are only 6 so no biggie there.

  1. Delete all Memberships that started after 31-Dec-2017. This should be all the ones that have been added over the last few days as the earliest Paid to Date is 31-Dec-2018 and the memberships are for 12 months. We aren’t deleting or changing the Contacts as most/many were there beforehand. It is only the newly created Membership records that have to go.
  2. Count everything using as many paths as you can. The Membership Dashboard is one. An Advanced Search may be another. Keep a record. The Membership Dashboard should probably show zeroes everywhere.
  3. Do the 7 imports as given in the Processing section below. Create new Mappings as you go with the new names. This may be a bit tedious and you may have to pretend to use an old mapping if you are unsure of “what goes with what”. Save every error log and record the number of records added/updated.
  4. Count everything using as many paths as you can. The Membership Dashboard is one. An Advanced Search may be another. Keep a record. The Membership Dashboard should agree with your counts in step 3 (or at least be able to be explained).
  5. Delete all Memberships just as you did in step 1.
  6. Do the 7 imports as given in the Processing section below. Use the new Mappings that you created back in step 3.
  7. Count everything using as many paths as you can. The Membership Dashboard is one. An Advanced Search may be another. Keep a record. The Membership Dashboard should agree with your counts in step 6 (or at least be able to be explained).
  8. Explain, and document, why some of memberships end up as New, some as Current and possibly some as Grace. Almost certainly to do with the dates in each.
  9. It occurs to me that if we now repeat step 6 without preceding it with step 5 and then do step 7 again we will establish that the update of both Contacts and Memberships works in a way that doesn’t introduce new records but only updates those in situ. We will need this behaviour, month after month, especially for Memberships. We would need a membership to not change for each of the 12 months and then a new record be created on the 13th month being the start of a new membership period. This is a potentially tricky area.

I have deleted the “Joint Household” tag already. The only tags are now: “Single”, “Joint” and “Life Member”. These are in the hierarchy of tags under “Memberships Individual”.

There are two goals:

  • Prove that our Processing procedure is correct for the importation.
  • Ensure that our Processing procedure and this whole Knowledge Base entry is complete, correct and understandable.

Cheers,

Rob.

(Tomorrow the task will be World Peace!)

Overview

All Australian COTA organisations have outsourced the management of their individual memberships to COTA Membership Services (CMS) in South Australia.

Every month, CMS sends COTA Victoria (and every other COTA) two Excel workbooks:

  • a full list of active memberships (about 1,500 records) and
  • a full list of inactive memberships (about 13,000 records).

Structure of the Excel files that arrive from CMS each month

The two files each have the same 35 columns (i.e., fields). Most columns are obvious, but the fields that need interpretation are:

  • Main_ID is the unique identifier assigned by SA to the record. This is the member number that is visible to the member.
  • Member_Type is: “A” is Associate (probably obsolete), “CMS” means that it was sourced by SA (obsolete), “J” is joint (Household in CiviCRM), “LM” is life member, “S” is single and “P” is potential member (probably obsolete).
  • Status is: “A” is active, “C” is cancelled, “D” is deceased, “I” is inactive, “L” is ‘left listed address’ as indicated by returned snail mail to SA.
  • Paid_Thru is the date one day before the current membership expires or expired.
  • Birth_Date is a date but the SA system’s data entry allows a date like “10/04/50” to be entered which ends up as “10-Apr-2050” and not “10-Apr-1950”.
  • Owner is to be ignored.
  • Organisation is to be ignored.
  • Source could be brought into CiviCRM with some notation like “SA source notes are: <source>”.
  • Exclude_Mail is used by SA to mean that the member doesn’t want to receive the magazine.
  • Preferred_Mail is to be ignored.
  • Preferred_Bill is to be ignored.

Processing implications into CiviCRM

  • We should capture the Main_ID and put it into our External ID and then match on it in subsequent months and to do the linkage within CiviCRM as required. This would guarantee that we were updating exactly the correct person(s). It turns out that there was (in the dim, dark past) at least one upload of the SA data as these Main_IDs are listed as the External ID of many of the existing CiviCRM contacts. This Main_ID is also the membership number as visible to the member.
  • Where there are two people in an input record we should produce or update three Contacts in Civi (two person in the same household).
  • Cancelled and Deceased imply that we should mark as “do not contact” in the various GDPR fields.
  • Exclude_Mail does not mean that we shouldn’t send snail mail to the member ourselves.
  • Left Listed Address implies that we shouldn’t send snail mail, but what if we have a better address than SA?
  • It appears that the vast majority, if not all, of the SA records have a correct Birth Date and these can be used to simply overwrite our bad Birth Dates. Testing has confirmed this to be true.
  • The data in the CMS should have its data preferred over CiviCRM, but fields not present in the CMS should be preserved in CiviCRM.
  • The Member_Types of “A” and “CMS” should be converted to “J” or “S” according to whether there are one or two people on the input record. Thus, these two input member types never enter CiviCRM. This applies only to the inactive data.
  • Attach the “Single” tag to the single members.
  • Attach the “Joint” tag to each of the dual members and to the household that contains these dual members.
  • Attach the “Life Member” tag to the life members. Life members are always individuals and never a household.
  • A member may go from Single to Joint (or Joint to Single) and therefore migrate from an Individual in CiviCRM to a Household, and vice versa.
  • A member may go from Active to Inactive (or Inactive to Active) and therefore move from one input file to the other. This is an unresolved issue.

Processing

Overview

The processing script in the following section is to be run (executed) for each row in this table that has a Sequence Number. The executions must occur in the sequence specified. After the executions, edit every life member and ensure that the membership status is permanently “Current”.

 

A

B

C
Sequence Number Filename.csv for importation

also known as

Saved Mapping in Import Contact or Import Membership

also known as

Name of sheet in CSM Importer

[plus outcome in square brackets]

CMS Import
One Person

(Membership type: “S” or “LM”)

Two People

(Membership type: “J”)

1 Single

[creates Contact (Individual)]

Create/update with first import person, member number as External ID and member number as the custom field Membership number – Individual. Tag with “Single”. No action.
2 Membership Single

[creates Membership (Individual)]

Create/update with back reference to External ID. No action.
3 Household

[creates Contact (Household)]

No action. Create/update with member number as External ID. Tag with “Joint”.
4 Head of Household

[creates Contact (Individual) linked to Household]

No action. Create/update with first import person, with back reference to External ID and member number as the custom field Membership number – Individual. Tag with “Joint”.
5 Member of Household

[creates Contact (Individual) linked to Household]

No action. Create/update with second import person, with back reference to External ID and member number as the custom field Membership number – Individual. Tag with “Joint”.
6 Membership Joint

[creates Membership (Household)]

No action. Create/update with back reference to External ID.
7 Life Member

[creates Contact (Individual)]

Create/update with first import person, member number as External ID and member number as the custom field Membership number – Individual. Tag with “Life Member”. No action.
8 Membership Life Member

[creates Membership (Individual)]

Create/update with back reference to External ID. No action.

Processing Script

  • Select either:
    • Contacts > Import Contacts if the word after “creates” in Column B is “Contact”
    • or
    • Memberships > Import Memberships if the word after “creates” in Column B is “Membership”
  • (wait until the “Choose file” button appears)
  • Choose the file that has the filename give in column B above.
  • Tick First row contains column headers.
  • Select the Contact Type that is given in parentheses in Column B above.
  • Choose “Update” for Duplicate Contacts or “Insert Existing Memberships for Import Mode (as appropriate)
  • Don’t select a Dedupe Rule (i.e., leave it as “- select -“).
  • Don’t change the Field Separator from being a comma.
  • Don’t change the Date Format from that which indicates yyyymmdd.
  • Select a Saved Field Mapping that is the word “CMS ” followed by the Saved Mapping name given in Column B above (e.g. “CMS Head of Household”).
  • Press Continue.

(new page appears – make no changes)

  • Press Continue.

(new page appears – you should see that there are 1,129 total rows)

  • Select/openTag imported records and tick the tag or tags as indicated in Column C above.
  • Press Import Now.

Resolution of Structural imbalance between CMS data and CiviCRM

CiviCRM outcomes (in italics) A Single Individual member exists OK Will fail as the member number will be used as the External ID during the import on a household rather than an individual – remedy is to create the household and the second individual and put the member number on the household.
Single Individual member exists but member of a household OK, and the structure of the household will remain unchanged. Will fail as the member number will be used as the External ID on a household rather than an individual – remedy is to check that the household has the correct people and put the member number on the household.
Joint Membership exists as a Household Will fail as the member number will be used as the External ID on an individual during the import rather than a household – remedy is to move the member number to the individual but the household can be retained if there is still such a household. OK, albeit that the first and/or the second import person may be a new person in the household.