Advanced Campaign Performance Chart in MS CRM

The following out-of-the-box chart for viewing campaign performance in MS CRM is quite inadequate compared to the possibilities.

It seems fine at first, but it is only showing the revenue generated by campaigns. However, campaigns in CRM are a lot more advanced and really lends itself to more advanced charts, which bring insight to campaign performance.

The campaign record includes the following fields of interest for campaign performance:

• Estimated Revenue (the goal for the campaign)
• Total Cost of Campaign

From the related Opportunities we get:

• Won Revenue (or Actual Revenue)
• In Progress Revenue (Est. Revenue from open Opportunities)

All put together in one chart, and using the same data records as in the OOB chart shown, it could look like this.

Similar to the standard goal chart, it has the familiar green and grey bars for “Actual Revenue” and “In Progress” values. Campaign cost and target revenue are shown behind it. Instantly the user can see, if the campaign has generated more revenue than its cost, how it is stacking up against its target, and how much more revenue might come. This should give the user a clear and instant view of campaign performance.

Note: In this blog post I am only using the bar chart and stacked bar chart types. Had I used column charts, we could have made something very similar to the standard goal charts. However, bar charts often utilize space better and are a lot more restrictive to work with, which means we have to get a little creative to get everything on one chart.

The chart contains four series; two Stacked Bar Charts and two regular Bar Charts.

To get started, I’ll create a View for the Opportunities and make sure to include the fields:

• Total Cost of Campaign (from Source Campaign)
• Estimated Revenue (from Source Campaign)
• Name (from Source Campaign)

Save the View, select it, and open the chart editor.

Add the four series needed as Legend Entries and the name of the campaign as the Category.

Notice the values on the fields coming from the Source Campaign are averages and the fields from the Opportunity records are sum. The same Source Campaign record will appear multiple times, and using the average will ensure that we only get the value intended.

Time to save the chart and export the chart xml.

As with any multiple series chart, first check if CRM added any references to a secondary Y axis. If it did, remove these first.

NOTE: Full sample chart xml is available at the bottom of this post.

My first step is to rename all the aliases to something more sensible and easier to work with.

Second, I’ll add a filter on Actual and Est. Revenue to ensure that only Won Opportunities are counted towards the actual value, and only Open Opportunities count towards the Est. Revenue.

New aliases are highlighted in the chart xml and the sections indicated are:

1. Link to the campaign record with the cost and est. revenue fields
2. Link to Opportunities and filtered to only include open opportunities
3. Link to Opportunities and filtered to only include won opportunities

Now I have all the needed data in the chart, but it still looks like this.

Now the real fun begins with editing the bars to match our needs.

I’ll make the following changes to the first two series for target and cost. These are the two bars I want in the background to indicate if the progress on sales is good or bad.

• ChartType=”Bar”
• IsValueShownAsLabel=”False” – values here would clutter the chart
• LegendText=”Campaign Target” and “Campaign Cost” – so the legend is shorter and easier to read
• Color, BackGradientStyle, BackSecondaryColor – to create the gradient on the bars
• CustomProperties=”DrawSideBySide=False

The last item under the CustomProperties is important. It is the setting that draws the bars on top of each other, instead of next to one another. It may look like a Stacked Bar chart, however, it is not since the values do not build on each other as they do on a stacked bar. For example, if Cost is 20k and Target 100k, a stacked bar would be 120k high. Two bars with SideBySide=False would still only be 100k high.

For cosmetic reasons, I also decreased the PointWidth a little.

Here’s the xml for the series for target and cost.

The Target is the first series, and the Cost is second. The bars are drawn in order, and since target is always higher, I want this one drawn first, and then Cost on top of that. Had it been in reverse, the Target bar would completely cover the Cost. I’ll make the safe assumption that the cost of the campaign is always lower than the revenue target.

The next two series, for actual revenue and in-progress revenue, I am more or less just copying the color settings found in the goal charts. Consistency in charts is important and the green and grey color shades are easily identifiable to the user.

In addition to some color and gradient settings, custom LegendText and some label formatting, I also made the following changes:

• Set chart type to StackedBar   –  because here we do want the values to build upon one another
• Reduced PointWidth and MaxPixelPointWidth under CustomProperties

By reducing the PointWidth and MaxPixelPointWidth I create a stacked bar that is thinner than the other two bars behind it. That enables me to have the cost and goal in the background, but wider, so you can still see them even if the revenue bar has surpassed the two.

Here’s the xml for the last two series. Actual Revenue and In-Progress Revenue.

Here’s the final chart again.

Couple of ideas for changes or additions:

• Add lost revenue on top of In-Progress, but make it even more transparent.
• Gradually shade the grey In-Progress bar according to the stage in the sales process

Resources and associated posts

• Chart Types http://msdn.microsoft.com/en-us/library/dd489233.aspx
• Stacked Bar Chart and CustomProperties http://msdn.microsoft.com/en-us/library/dd456771.aspx
• Bar Chart and CustomProperties http://msdn.microsoft.com/en-us/library/dd456715.aspx
• Colors https://crmchartguy.wordpress.com/2012/08/23/palette-custom-colors-in-charts/
• Advanced Goal Chart https://crmchartguy.wordpress.com/2013/02/07/add-insight-to-your-pipeline-with-advanced-goal-charts/
• Link and filter entities https://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/

Hope you liked the post. Please sign up for the email newsletter or follow me on Twitter Follow @crmchartguy

Sample Chart XML for the custom Campaign Chart

Here’s the full XML and it should work with a current CRM trial and sample data. Just add some data to the Estimated Revenue fields on the Campaign Records.

<visualization>
 <visualizationid>{A9B55234-37FD-E211-9BBB-B4B52F67E642}</visualizationid>
 <name>Campaign Performance Chart by CRM Chart Guy</name>
 <primaryentitytypecode>opportunity</primaryentitytypecode>
 <datadescription>
 <datadefinition>
 <fetchcollection>
 <fetch mapping="logical" aggregate="true">
 <entity name="opportunity">
 <link-entity name="campaign" from="campaignid" to="campaignid" link-type="outer">
 <attribute name="name" groupby="true" alias="campaign" />
 <attribute name="totalactualcost" aggregate="avg" alias="totalcost" />
 <attribute name="expectedrevenue_base" aggregate="avg" alias="goal" />
 </link-entity>
 <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
 <attribute alias="open" name="estimatedvalue" aggregate="sum"></attribute>
 <filter>
 <condition attribute="statecode" operator="eq" value="0" />
 </filter>
 </link-entity>
 <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
 <attribute alias="won" name="actualvalue" aggregate="sum"></attribute>
 <filter>
 <condition attribute="statecode" operator="eq" value="1" />
 </filter>
 </link-entity>
 </entity>
 </fetch>
 </fetchcollection>
 <categorycollection>
 <category alias="campaign">
 <measurecollection>
 <measure alias="goal" />
 </measurecollection>
 <measurecollection>
 <measure alias="totalcost" />
 </measurecollection>
 <measurecollection>
 <measure alias="won" />
 </measurecollection>
 <measurecollection>
 <measure alias="open" />
 </measurecollection>
 </category>
 </categorycollection>
 </datadefinition>
 </datadescription>
 <presentationdescription>
 <Chart Palette="None" PaletteCustomColors="149,189,66; 197,56,52; 55,118,193; 117,82,160; 49,171,204; 255,136,35; 168,203,104; 209,98,96; 97,142,206; 142,116,178; 93,186,215; 255,155,83">
 <Series>
 <Series ChartType="Bar"
 IsValueShownAsLabel="False"
 LegendText="Campaign Target"
 Color="0,25,25,112" BackGradientStyle="LeftRight" BackSecondaryColor="60,25,25,112"
 Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="DrawSideBySide=False, PointWidth=0.65, MaxPixelPointWidth=40">
 <SmartLabelStyle Enabled="True" />
 </Series>
 <Series ChartType="Bar"
 IsValueShownAsLabel="False"
 LegendText="Campaign Cost"
 Color="0,255,165,0" BackGradientStyle="LeftRight" BackSecondaryColor="120,255,165,0"
 Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="DrawSideBySide=False, PointWidth=0.65, MaxPixelPointWidth=40">
 <SmartLabelStyle Enabled="True" />
 </Series>
 <Series ChartType="StackedBar"
 IsValueShownAsLabel="True"
 LegendText="Actual Revenue"
 Color="112, 142, 50" BackGradientStyle="LeftRight" BackSecondaryColor="149, 189, 66"
 LabelFormat="$#,0,.##K" Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="PointWidth=0.35, MaxPixelPointWidth=20">
 <SmartLabelStyle Enabled="True" />
 </Series>
 <Series ChartType="StackedBar"
 IsValueShownAsLabel="True"
 LegendText="In Progress"
 Color="170, 100, 100, 100" BackGradientStyle="LeftRight" BackSecondaryColor="110, 100, 100, 100"
 LabelFormat="$#,0,.##K" Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="PointWidth=0.35, MaxPixelPointWidth=20">
 <SmartLabelStyle Enabled="True" />
 </Series>
 </Series>
 <ChartAreas>
 <ChartArea BorderColor="White" BorderDashStyle="Solid">
 <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
 <MajorGrid LineColor="239, 242, 246" />
 <MajorTickMark LineColor="165, 172, 181" />
 <LabelStyle Font="{0}, 10.5px" Format="$#,0,K" ForeColor="59, 59, 59" />
 </AxisY>
 <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
 <MajorTickMark LineColor="165, 172, 181" />
 <MajorGrid LineColor="Transparent" />
 <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
 </AxisX>
 </ChartArea>
 </ChartAreas>
 <Titles>
 <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />
 </Titles>
 <Legends>
 <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
 </Legends>
 </Chart>
 </presentationdescription>
 <isdefault>false</isdefault>
</visualization>