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:
campaignHistory.png

To this:
consolidatedCampaignMember.png

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

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)

  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!!

 
3
Kudos
 
3
Kudos

Now read this

Infinite Flow

UPDATE - 7-7-15: This will eventually trigger an error, and is a known issue w expected behavior ‘TBD’. See updated post - Infinite Flow Refined. for more details on the error and the infinite flow model It is possible to set up a... Continue →