Author Topic: Out-of-band membership creation/renewal (Read 1100 times)

sjthespian · February 28, 2011, 09:52:58 am

One of the organizations I work with is sponsoring a conference where we are giving free memberships in our organization to all registrants. We are not running the registration system however, so we will just be getting a copy of the registration list from them, most likely as an excel spreadsheet.

Is there an easy way with CiviCRM to import these users and have them do the "right thing" with their memberships? Some of them will be existing users with no memberships, some with have existing memberships that need to be extended a year, and some will be completely new users with no contact record in CiviCRM.

petednz · February 28, 2011, 11:08:00 am

Step 1
You would need to import the contacts and add to a Group during import (or Tag)

Step 2 if you want to record them as participants at this conference, grab them via the Group you add them to on import, and add them all as Participants of the Event (if you want that recorded)

Step 3
export your Group so you can import their Membership details (start date etc), using the Internal Contact ID which you got from the export.

May have missed a few steps but that is sort of workflow required.

sjthespian · May 03, 2011, 07:11:27 am

The first two steps are straight forward, the problem is step 3. I can't just import membership data as Civi will not necessarily do the "right thing" with it.

Some of these folks will be new memberships, some will be renewals. So for some I need to add a membership and others I need to extend their existing membership by a year. I don't see a way to do this out of the box with the membership import, unless I'm missing something?

I only see "import new" and "update existing" for membership import options, not a way to do both at the same time. Is there any choice other than manually going through the registrants to separate out new and renewals? I also don't see a way to have Civi calculate the start/end dates for me for the renewals, do I have to do this manually as well?

If so, I may be better off writing a script that will parse my CSV, look up the members in the database, and either generate a pair of CSVs for importing or doing the database updates itself.

Thoughts?

qjensen · May 03, 2011, 10:09:18 am

You are right, CiviCRM won't take care of this for you by itself, but you can get the info you need with a couple of quick SQL queries.

Fire up phpMyAdmin and create a query to get a list of contacts in your event group that don't have a membership. Export the results of the query as CSV, touch it up in Excel and import to create the new memberships.

Create a similar query to get a list of existing memberships that you can export to CSV, touch up and import to update those memberships.

If you need help with the SQL, post what you have as a start and I'll be happy to help.

Unfortunately there is no functionality to handle this in the GUI yet.

sjthespian · May 03, 2011, 02:30:04 pm

Thanks Quint.

That's what I was afraid of -- I think I'm going to just write a quick perl script that reads in my CSV, walks the database to find each contact and membership (if one exists), then generates a pair of CSV files that I can import using CiviMember. I can throw that together in an hour or so as at this point I know a lot more about the CiviCRM schema than I wish I did. :-)

The downside of this is that I'm going to have to redefine the column mapping by hand for every conference we work with as I am getting excel spreadsheets of their registrations. I don't have a whole lot of control over how they format the info they send me (for example in this case I have a column that has either an e-mail address, a member ID (the contact ID is our membership number) or nothing -- in which case I have to try and map the e-mail they registered with and/or the name/address info.

sjthespian · May 06, 2011, 07:05:29 am

Just to follow up on this, the above does work -- for the most part. Here is what I ended up doing (and I plan on fine-tuning this in a few months when we have another co-sponsored conference):

Create an event for the conference (we'll do this well in advance next time even though we are using an external conference registration system)
Import contacts to make sure everyone has a contact record and add all of the imported contacts to a group
Run my script that will parse the CSV of registrants and query the database for those with existing memberships. Generate two new CSVs with membership/payment information. Once CSV is for new memberships, one for renewals. If a membership expired more than 6 months ago, consider it a new membership.
Run two membership imports, one with each file
Run two contribution imports, one with each file

Now comes the challenge -- there is nothing in the above to tie the contributions to the membership. I ended up writing yet another script that finds all of the event attendees, their contributions from the above imports, and their most recent membership and links them in the civicrm_membership_payment table.

It isn't the prettiest process in the world, but it works. Can anyone think of any improvements?

CiviCRM Community Forums (archive)

News:

Author Topic: Out-of-band membership creation/renewal (Read 1100 times)

sjthespian

Out-of-band membership creation/renewal

petednz

Re: Out-of-band membership creation/renewal

sjthespian

Re: Out-of-band membership creation/renewal

qjensen

Re: Out-of-band membership creation/renewal

sjthespian

Re: Out-of-band membership creation/renewal

sjthespian

Re: Out-of-band membership creation/renewal