Inline Salesforce Campaign Summary Charts via Google Charts API

The Google Charts API provides an easy way to include inline charts in Salesforce detail pages.   The Google Charts API provides a REST interface that lets you retrieve a static image by requesting a URL with parameters for the data points in the chart.   We can combine this with formula fields within Salesforce to embed charts in record page layouts.

Here’s a simple example of what a Google Charts URL looks like:

http://chart.apis.google.com/chart?
cht=p3&
chs=250×100&
chd=t:60,40&
chl=Hello|World

To get started, create a new formula field on your Campaign object and set the returning value to text.   Then, enter a formula like the one below to provide a campaign member summary:

IF ( Total_Members__c = 0, “”,

IMAGE(

“http://0.chart.apis.google.com/chart?cht=p3&chf=bg,s,65432100&chs=325×100&chma=20,20,20,20&chco=” &

IF(NumberOfLeads – NumberOfConvertedLeads == 0, “”, “DD8107,”)&
IF(NumberOfConvertedLeads == 0, “”, “951DD6,” )&
IF(NumberOfContacts – NumberOfConvertedLeads == 0, “”, “422F7A” ) &

“&chd=t:” &

/* Unconverted leads */
IF( NumberOfLeads – NumberOfConvertedLeads == 0, “”, TEXT( (NumberOfLeads – NumberOfConvertedLeads) / Total_Members__c) & “,”) &

/* Converted Leads */
IF( NumberOfConvertedLeads == 0, “”, TEXT( NumberOfConvertedLeads / Total_Members__c) & “,”)&

/* Contacts */
IF( NumberOfContacts – NumberOfConvertedLeads == 0, “”, TEXT( (NumberOfContacts – NumberOfConvertedLeads) / Total_Members__c) ) &

“&chl=” &

IF(NumberOfLeads – NumberOfConvertedLeads == 0, “”, “Orig. Leads: ” & TEXT( NumberOfLeads – NumberOfConvertedLeads ) & “|” )&
IF(NumberOfConvertedLeads == 0, “”, “Conversions: ” & TEXT( NumberOfConvertedLeads )& “|” )&
IF(NumberOfContacts – NumberOfConvertedLeads == 0, “”, “Orig. Contacts: ” & TEXT( NumberOfContacts – NumberOfConvertedLeads)& “|” ),

“Member Summary Chart”)
)

In making this chart, I took a closer look at the standard Campaign fields.   The Campaign object provides fields that provide: the number of leads, the number of contacts, and the number of converted leads associated with the Campaign.  It turns out that there isn’t an easy way to see the total number of Campaign Members because the sum of these fields is not the right number.     If a Lead is converted,  the converted Lead count and the Contact count go up by one, but the Lead count does not decrease.    This is why I didn’t map these values directly to the pie chart sections above.    It’s a simple fix to create a formula field that sums the three numbers appropriately to give you a member count.

This can be extended to summarize response counts as well as Opportunity win rates.   An added perk is that these fields are available on reports, letting you see a collection of the summary charts for all of your Campaigns at once.

Related Content

1. Keeping Updated On Your Database in Salesforce One of the things that I’ve discovered administering CRMs is...
2. Disable Validation Rules by Record in Salesforce I’m currently working on a Salesforce CRM implementation at work...
3. MailChimp Salesforce Integration Take One I had been planning on writing about the MailChimp Salesforce.com...