Nightmare of segmenting Techcelerate, CapsuleCRM and Mailchimp Databases whilst keeping them cleaned and in sync

How do you segment multiple databases, keep them clean and in sync? If you know an easy way, please do let me know.

Techcelerate maintains 3 vital databases among many for operational management. These being:

1. Techelerate.org (Drupal 5) with 1893 community profiles used predominantly for event registration
2. Mailchimp for weekly newsletter with 1844 profiles. 49 profiles seems to be missing.
3. Capsule for sales conversion and membership renewal management. Unfortunately, Capsule does not easily display total number of profiles held.

Mailchimp automatically updates when a new profile is created on Techcelerate website, which makes life lot easier. Unfortunately, the same level of updating is not available between Techcelerate and Capsule, nor between Mailchimp and Capsule, which means, periodical manual importing of profiles from Techcelerate to Capsule. There is a Drupal module for Capsule CRM, but it does not support our version of Drupal.

First problem in here is, which database should be considered as the master? You may think this is straight forward, but it isn’t.

1. Community can update techcelerate profiles any time. As this data is not used directly for any reason other than for building event attendance lists, the profiles are used less and less directly from an operational point of view.
2. Mailchimp is used just for weekly newsletters and nothing else. Again, there is no need to access profile information on regular basis.
3. Capsule CRM is vital, as it is the key operational tool in tracking when to invoice, and what calls to be made from the sales pipeline.

CapsuleCRM is also integrated with Xero.com, but less attention is paid at present to the data exchange. From an operational management point of view, Xero’s unpaid receivable invoices list is sufficient to expedite debt recovery.

The three databases are vital to:

1. Know who to invite to events or become members
2. Inform them about events and workshops
3. Raise invoices and collect fees and expedite where relevant

But as the databases grow, segementation become even more important. This is where all become even hard to manage. Which databases do I segment? And why do I need segmentation?

Let’s say, I want to find out who to invite to be a Dragon at our next Dragons Lair. If I can pull an up to date list of investors, then I can simply start contacting them. So where do I pull the list from? Techcelerate profile are managed by individuals and therefore may not have accurate data. E.g. an individual might consider himself as an entrepreneur instead of investor, if s/he has dual roles. Segmenting on capsule isn’t straight forward as it’s bit of a mess. Mailchimp, perhaps is easier, but if someone unsubscribe, then the segmented lists might miss some of the investors I like to contact. This means I have no choice but to get the data in Capsule cleaned and segmented.

The overall conclusion is that, I have total control of data on Capsule, but not necessarily on Techcelerate (people can change) or Mailchimp (can lose as a result of unsubscription). This shows how important CRM is from day to day operational management of your business. Therefore, I ought to make a time investment to get the data on Capsule cleaned and segmented.

But I feel I am making my life difficult here. Can I get the community to clean their data, and then automatically update records on Capsule? To do this, I would need to regularly ask the community to keep their profiles up to date (they will only do this, if they see value in doing so) and then upgrade Drupal to version 6 for automatically update profiles on Capsule. I also wish there is an easy way to segregate data on Capsule to people and Companies.

It’s quite interesting to hear how Duncan Stockdill has built Capsule CRM. He is actually making you to think about your data before just uploading, hence the lack of some features. The lists are be filtered, even though they list minimum information. Whatever the problems or lack of features, I need to get Capsule in order, before the 1900 profiles grows to 2,500 and beyond.