Our Blog

We can't shut up.

You are here: Home Our Blog A Better Way to Track Email in Salesforce
May 18 2010

A Better Way to Track Email in Salesforce

We believe that significant improvements in email broadcasting data storage efficiency are easily achievable, by using Campaign Member objects as the point of integration, rather than a custom object. Here's how email service providers can implement this solution.

THE PROBLEM

Salesforce includes 100MB of data record storage per user license. The Salesforce Foundation grants nonprofits 10 user licenses, which means that most of our Salesforce consulting clients have a 1GB limit on data record storage. Using an integrated email broadcasting platform that reports data back to Salesforce can very quickly consume this limited data storage space.

The main reason for this is that most integrated email broadcasting solutions use their own custom object to track email results on a per-contact basis. This is a relatively inefficient storage strategy.  For each email sent, this approach uses 3KB of Salesforce storage per email recipient to track the email send results (campaign member is 1KB, plus 2KB for the custom object).

This doesn't sound like a lot, but given the number of records that email broadcasting generates, the storage load quickly mushrooms out of control.  Consider the following example:

  • An organization's Salesforce database contains 25,000 contact records
  • The organization wants to send a weekly email broadcast to all 25,000 contacts and track the results per contact
  • For each email sent, the current integrations are creating 3kB of data. 
  • 25,000 sends X 3KB data storage per send = 73MB of data per email blast
  • At this rate, an organization will exhaust its data storage limits in approximately 13 weeks!
  • Organizations can purchase additional storage space from Salesforce, but it's quite expensive -- approximately $1200/yr per 500MB.  So, storing the data associated with a weekly blast to 25,000 recipients would cost nearly $9000/year!

While most of our clients are not burning through data storage quite that fast, since they're not emailing their entire list each week, several have shown trajectories to reach their storage limit in less than a year from when they begin integrated email communication activities.

Barring changes in Salesforce's data storage policies, Groundwire believes that the current approach most email broadcasting providers are using for data storage in their Salesforce integrations results in unsustainable costs.

OUR RECOMMENDATIONS

We believe that significant improvements in email broadcasting data storage efficiency are easily achievable, by using Campaign Member objects as the point of integration, rather than a custom object. Groundwire recommends that email broadcasting providers who integrate with Salesforce adopt this strategy for reporting data back to Salesforce. 

Here's the outline, followed by a proposed schema:

  • The best place to store email broadcasting results data is in the Campaign Member object, not in a third-party custom object such as VerticalResponse's "Email History" object or ExactTarget’s "Individual Email Result" object
  • Email broadcasting platforms should add several custom fields to the Campaign Member object to track email results
  • Custom objects which track aggregate data such as ExactTarget’s "Email Results" object may still be used as only one object is generated per email campaign, and this does not contribute significantly to data storage bloat

Using the Campaign Member Object

  • Create new checkbox fields: Opened, Clicked, Bounced, Unsubscribed
  • Create new number field: Number of Clicks
  • Create a Text field for the Campaign ID
  • Create a Text Area (Rich ) field called Clicked Links - This will be the field which captures the actual URL of a clicked link (or links)

Proposed Schema for New Fields

Campaign Member Field Name
Possible Values
Field Type
Campaign ID
Unique per campaign
 Text (string)
Opened  TRUE, FALSE
 Checkbox (boolean)  
Clicked  TRUE, FALSE
 Checkbox (boolean)
Unsubscribed  TRUE, FALSE
 Checkbox (boolean)
Bounced  TRUE, FALSE
 Checkbox (boolean)
Number of Clicks
Unique per campaign  
 Numeric (integer)
Clicked Links
Unique per campaign
 Text area (rich lines field) 

Note that Email Opt Out on the Contact record should be checked automatically as part of the integration when there is an unsubscribe.

 

data-storage-graph.gif

 

Figure 1. This chart shows a hypothetical data usage scenario. The x-axis is months, and the y-axis is in MB. Both lines start with a baseline of 200MB or roughly 20% of data storage used for records like Contacts, Accounts, Opportunities, etc. After 15 months of sending 20,000 emails per month, the non-mitigated example would run out of storage while the other would only use about half of the available storage in that time.

This is such a well-put explanation of a significant problem and a solution. In the end, third-party providers SHOULD be using standard objects as much as possible. In the meantime, we need a workaround. This might help:
Use an Apex trigger to copy data from these custom objects (such as VerticalResponse's email history objects) to a CampaignMember record. VR already updates the CM Status field, and includes all the required fields for copying the appropriate data to CM on its email history objects. The final step in the trigger would be to delete the email history record itself.
The downside is that once a VR Email History record is deleted, it is not recreated by VR - even when the campaign statistics are updated. I'm not sure why. But the campaign member information is updated.
So this isn't a full solution, but it might be something to consider when developing a viable workaround.
Thanks for the suggestion David! We thought of a similar approach but were disappointed to find out that the VR Email History object does not contain a lookup to campaign. Instead, VerticalResponse includes a hash of Campaign Id that is only useful for VR. I couldn't figure out a way to tie a VR Email History record back to a campaign, and in turn a particular campaign member record. Other ideas are welcome.

- Dave
Impressive documentation on the problem and a recommended solution. Good work!

We (Right On Interactive) have solved this problem by updating the Salesforce Campaign Member Status based on how the subscriber responds to the email (ExactTarget specifically or other ESPs) using the following campaign member status' (Sent, Opened, Clicked, Bounced, Unsubscribed). This process is automated using our AppExchange solution, 5Buckets.

Additionally, we provide weights(Sent=0, Opened=1, Clicked=5, Bounced =(-10), Unsubscribed =(-100)on a custom campaign member field. Or, similar weights.

Then, we update a custom field on the lead or contact record (Engagement Score) and provide an overall engagement score (email and all other marketing tactics). Dashboards, Views, and Reports can be created from here to show the engagement score of all leads/contacts.
Troy, that's not a bad solution that you have there. I like the engagement score idea too. We looked at suggesting using campaign member status, but the problem is that campaign members can only have one status at a time. It makes it harder to show the total number of opens AND total number of clicks.

Now that the campaign member object can be customized, we feel that tracking all email metrics in custom fields is the way to go. This also frees up campaign member status for other types of tracking such as stages of a campaign.

AppExchange products aside, what we're really trying to do is encourage vendors to use standard Salesforce objects when they create mass email integrations.
That's alright for tracking statistics over an entire campaign, but what if you want to track statistics for a single message?
Good question Dave, in our estimation a campaign can be either a single email blast or a longer campaign like a year-long engagement outcome. We often use campaign hierarchy to model single outreach efforts under a larger campaign.
Great solution! Once you set up the new fields, what is the next step in setting up VR so that it uses the new fields?
Thanks
Tim, it turns out that VR doesn't include a lookup to the campaign record from within the VR Email History Contact record so there isn't a way to map the data into the campaign member record. However, if you set up your campaigns to use the custom campaign member records that VR know how to write to (Opened, Clicked, Bounced, Unsubscribed, and Sent) you can at least track that much info about each email campaign.

One issue though, is that each campaign member can only have one status at a time so it would be harder to calculate cumulative open rates (because a clickthrough only shows as a click, not an open). Another issue is that the actual list of URLs that were clicked on cannot be recorded on the campaign member record.

The bottom line is that you can get leaner data storage when using VR, but not exactly the same depth of data that you get from their custom record. With other email integrations you can get the best of both worlds (ExactTarget being one).

Once you start using the campaign member record exclusively you could delete all your old VR Email History Contact records to free up space. It's also possible to write a trigger which would do this automatically.

Hope this helps,
Sam
Commenting has been disabled.