Batch merge automation of big data set

Question

We've faced duplicate records batch merge issue while moving big data set (around 140K Individuals as Organisation members and cca. 400 Organisations accordingly) from proprietary CRM to CiviCRM and would appreciate any advice of how to move forward. We've successfully started from importing small records amount both via Import GUI and API calls; however after bigger import we've found that a large amount of nested Organisations have been created via Relationships, so, we have one "main" Organisation with properly filled address, etc, and several members and, say, 400 "phantom" organisations with just name and one member visible at Relationships tab.

• We have tried "Find and Merge Duplicate Contacts" Individual Rules via GUI, however, after leaving it works for a night we ended up with same total Individuals number and an url like this: civicrm/contact/dedupefind?reset=1&action=map&rgid=7&skipped=XYZ&merged=0 (where XYZ was 8175 just before it was stopped); since no status report has been received, guess "merged=0" part of an url shows number of successfully merges.

• api/explorer (Contacts -> Merge) has almost no parameters to work with, just "$result = civicrm_api3('Contact', 'merge');" and "Other" as choose parameter option, so it's not quite clear how to use it.

• What really works for us is a multi-step GUI workaround, like:

  1. Find all instances of Organisation with same name
  2. Assign search results set to a new group
  3. Apply one of the Find and Merge Organization Rules
  4. Wait (cca. half of an hour, thanks to fine tuned MySQL with 13G innodb_buffer_pool_size and tons of other improvements)
  5. Manually merge several remained records

  It works almost great, however it is quite time-consuming since we will have to repeat a process around 400 times. Hope there is alternative/faster way(s), we've no clue where to start from. Please help!

UPD: Thank you all for a valuable responses, it gives us some inspiration of how to attack an issue from an another angle(s). I'd accept both replies, however Xavier's approach sounds more easy for us at this stage: we've got some positive experience using api/scripting before, even if this method did not work 100% for us previously, we're going to try it again.

Answer 1

For big imports, I wouldn't use the UI and write either a custom script (using the API), using existing drupal modules (civimigrate or use an ETL (amnesty spain has shared a plugin for pentaho for instance)

It would likely be faster than trying to untangle where you are now.

If you can't start, I wouldn't try to merge the contacts, that is a complex operation, but simply write a script to identify the duplicates and manually update the individual to replace the "empty" organisation by the real record, and delete the empty record.

You can use the api from a php script that you'd run from the shell, it will be much faster to run than a "real" dedupe

X+

Answer 2

I can offer two possible approaches.

One is to integrate CiviCRM with Solr for deduplicating. Mark Burdett at the EFF got this working a couple of years ago, so you may want to reach out to him. There's also a Google Summer of Code project to integrate Solr for an interactive/faceted search feature, so you may want to reach out to that student.

Another approach is to use your current approach, but use much faster searches. Parvez from Veda Consulting has customized CiviCRM 4.5+ to do exactly that; this work is expected to make it into core in CiviCRM 4.7+ this fall, but is available now here: http://vedaconsulting.co.uk/civicrm-dedupe-workflow-ui