I am member of the Massachusetts state legislature. I represent 40,000 people, 25,000 of whom are registered voters. I run for re-election every two years.

During the non-campaign seasons, my small (one-paid staff plus volunteers) legislative office handles an average of roughly 30 email conversations and roughly 5 new constituents per day. We work on a range of constituent and legislative issues and I run an issues discussion website based on Wordpress at willbrownsberger.com.

Until recently, my office used a constituent relationship management and campaign system offered by a Massachusetts vendor, Sage Systems. Sage is a back office system. I began reassessing my systems earlier this year. I wanted to do two things: Improve my constituent service system used by legislative staff. Improve my campaign system and allow broader online access to support decentralized grassroots campaigning.

I put a small group of volunteers together and we considered a range of options. Fortunately, one of us had run across Civicrm. We are very happy with the progress we have made, but we are still working. The CRM is fully implemented and the public end of the new site is taking form at mywillbrownsberger.com.

I'm grateful to the Civicrm community and post this account of our experience in the hopes that it is helpful to others. Over the next couple of weeks, we will post the routines that we use in batch processing and will certainly contribute any modules that we develop as time goes on.

Over the past few months, we have migrated to Civicrm in the following major stages:

1. In August, we converted our contacts and activity records to Civicrm. Staff immediately felt that the user interface for routine activity tracking (calls, emails, case work) was much smoother. Response time was much better too -- we invested in a pretty high quality virtual private server.
2. In September, we converted our bulk email processing to Civimail from Lyris (which was the product associated with Sage) and we were able to close down the old system. This was a hard technical climb for us, lacking familiarity with email servers, but we were able to get a working configuration going on our virtual private server. The control afforded by the VPS was essential. We are very pleased with the tight integration between civimail and the rest of civicrm which gives great flexibility in list selection.
3. In October, we began refreshing voter lists in the system. This was a major data processing task. We had to match voter lists to the existing set of contacts, which were basically an accretion of old voter lists that Sage had allowed to accumulate. We wanted to preserve contact records to which we had added activity records, ratings (ratings like 'supports Will', does not . . .), etc. over the years, but also to have a fresh accurate voter list that didn't have a lot of long departed people on it. We created a custom table that has all of the fields of the voter list and then in a number of careful passes purged departed voters, matched the voting list table to the contact table, deduped old to old, new to new and old to new. We only used the civicrm import function for the final step of storing new contacts for people on the voter list that didn't match any existing contacts. The rest we did with direct SQL queries. (Import/update is too slow -- it seems to do a huge amount of CPU processing, suggesting some bad joins). Finally, we have in place a contact table that has all of our old value-added data, but no deadwood, with the voter list data hanging off it as a custom field group.
4. Additionally, we have uploaded publicly-available voter history data -- which recent elections they voted in -- in a custom field group.
5. In November, we turned to householding. For efficient campaign and constituent mailing, good householding is essential. We took the following approach.
   • We wrote an address parser to populate the postal fields that are present on the civicrm_address table.
   • We obtained property lists from our local assessors offices and parsed them into the same address format. We stored an intermediate table of housing units, in which each record corresponds to a single housing unit -- this involved splitting records on the property list according to their property class (single family, two family, etc.). We took the approach that the housing unit list should be conservative -- we would not engage in any guesses that would lead us to listing any spurious units.
   • We then stored the housing units in the contact table as records with contact-type household (names like "two-family: 123 main st apt 2")
   • We then linked individual type contacts to household type contacts using the member of household relationship records based on strict address matching (including a unit number in any multi-unit building). The strict address matching we required meant that only about 85% of our individuals were linked to households, but it assured that we weren't creating bogus households comprised of people in different apartments with the same street address -- this was a problem we had experienced in direct address matching using only the voting lists without the housing unit check. We are developing a routine that will update custom greetings appropriate to each household and unmatched individual for mailing purposes.
   • The address parser, the household relationship routine and the salutations routine (which is in progress) will all as nightly batch processes that recreate all relationships. This will support the batch addition of new voter data as time goes on and is, of course, easier to write than a real time update. The first two are fully in production.
6. Over the past few weeks, we have activated the fund-raising system of Civirm and used the profile subsystem to create volunteer forms which are now accessible at mywillbrownsberger.com.
7. Over the months to come (before a re-election campaign heats up in the Spring of 2010), we hope to develop and stabilize a distributed data entry system that will allow supporters to login, search for voters, recruit them and record the results of their canvassing. The system as we have it now is perfectly adequate for a precinct captain to control data entry. We have only 14 precincts, so civicrm/drupal database segmentation would be workable. But we want to allow people we don't know to pitch in and so we need to preserve a full audit trail of activities, rather than allowing users to overwrite a single report of canvassing results.
8. We are very pleased with the geocoding functionality which works like a charm and will be a part of our canvassing system
9. We initially implemented Civicrm as in the Standalone version, but found that there were a number of small security related bugs (ACL caching seemed a little flaky). We bit the bullet and took on the Drupal environment and are very pleased with the front end Drupal has allowed us to get started on at mywillbrownsberger.com.
10. One important issue for us, to comply with campaign finance and ethical rules, was to segregate legislative from campaign functions. Drupal security and theming allows this nicely. Legislative users see a completely different site, showing only the Civicrm backend and only those functions that they should have access to (no fundraising). They log in through a portal on the discussion website to special page-themed login page and are never exposed to the campaign front end.

11. For those who are interested in mind-numbing details, most of our more detailed development notes are visible at mywillbrownsberger.com/forum. If we opening ourselves to some kind of security vulnerability by anything we've posted, we hope you will let us know! We'll brush up and post the batch routines we have developed some time over the next weeks.

    Happy to answer any questions. My email is willbrownsberger@gmail.com.