Consolidating Campaign Members using Open Refine

BACKGROUND
Over time, campaign members build up, and at some point you may want to delete them to lower storage usage. I will demonstrate an approach to ‘consolidating’ multiple historical campaign member records to a single campaign member record.

So it would go from this:

To this:

Allowing you to retain the information, in a single campaign member record.

Note that this approach requires creating a custom long text area field on campaign member, as the standard description field just displays the lead/contact description field and cant be written to. This post assumes you have a custom field called ‘Notes’ (Notes__c) on the campaign member object.

RECIPE
There are four broad steps in this process

1. export data from Salesforce
2. ‘consolidate’ records using OpenRefine
3. load new campaign records w/ consolidated data in notes field

4. delete the existing campaign member records (using the original file from step 1)

5. Export Data From Salesforce
   export a report from salesforce of campaigns with contacts, including the campaigns you want to consolidate. Include the following fields

Contact Id
Campaign Name
Member Status

Optionally, you can concatenate Campaign Name + Member Status in excel, but i’ll show how to do it in Refine.

1. Consolidate Records using OpenRefine

REFINE ORDER OF OPERATIONS

• create project using salesforce data export
• remove the salesforce reports footer (or turn that off!)
• sort by contactId (check the case sensitive option)
• reorder rows permanently
• switch from row to record mode (show as rows -> records)
• blank down contactId (edit cell - blank down)
• create consolidated status column (concatenate using edit column - add column based on this column, using formula below)
• join multi-valued cells with semicolon (edit cells - join multi-valued cells)
• switch back to row mode
• facet Lead/Contact Id column by blank = true (facet - customized facets)
• remove matching rows (all - edit rows)
• optional - replace the semicolon with semicolon + line break using formula below

I will demo this at nonprofit office hours tomorrow, and will post the link to the recording here once available.

USEFUL REFINE FORMULAS

CONCATENATE COLUMNS
cells["Campaign Name"].value + ' : ' + value

REPLACE SEMICOLON W LINE BREAK
value.replace(';', ";\n")

INSTALLING OPENREFINE
go to http://openrefine.org/ and go to the downloads page, follow the instructions for your OS (mac, linux, and windows supported!)

if you have a large file, you may need to increase the memory allocation

TESTING THIS IN A DEV EDITION
Highly recommend testing this in a developer edition.

If you are in a dev org, you will need some campaign members. I wrote up a simple script you can execute via the developer console that will create sample campaign members to test against.

This will create campaign members for all contacts in your instance in all campaigns, so DO NOT DO THIS IN PRODUCTION!

This will fail if you have a large number of contacts or campaigns, but dev orgs only come with a few so you should be fine.

1. load new campaign records w/ consolidated data in notes field

Create a new campaign in salesforce
Export the refine file and open in excel
Add a column called ‘Campaign Id’
Paste the ID of the new campaign in each row
Load to salesforce using your data loading tool of choice
Mapping (salesforce - file)

• contactId = lead/contact Id
• campaign id = Campaign Id
• notes = consolidated status

1. delete the existing campaign member records (using the original file from step 1)

be very careful before doing this!
deleting the existing records is what frees up the space
after this process, you can delete all of the records from your original file.

PROCEED WITH CAUTION
USE AT YOUR OWN RISK!!