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.

Out of box CRM Campaign Chart

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.

Custom Campaign Chart 2.0 in MS Dynamics CRM - custom xml

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.

Chart Designer for Custom Campaign Chart

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.

Campaign Performance Chart for MS CRM wiht new aliases and link entities and filter

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.

Chart before XML is modified - CRM Chart XML Custom Campaign Chart

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.

Target and Cost series for the Campaign Performance Chart xml sample with CustomProperties

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.

Actual and In Progress series for Campaign Performance Chart in MS CRM

Here’s the final chart again.

Custom Campaign Chart for MS Dynamics CRM by CRM Chart Guy

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

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

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

, , , , , , ,

  1. #1 by Trevor Partridge on September 25, 2013 - 6:53 am

    Great post, in fact all of your pages are awesome I use them all the time. I’m having some difficulty mimicking some functionality though. I want to create a chart similar to what you have above but have it grouped by created on day. I want to it to show one bar of all cases created per day, then a stacked chart with 1 bar showing cases open but assigned and the second showing resolved cases all per day. My issue is that I try to add a filter to each attribute. So for status I might have only active cases, or only resolved cases. However the chart always defaults back to 1 filter, whichever filter is the first one in the code all other attributes are filtered by that filter. Do you know what might be causing this?

    Thanks!

  2. #3 by Trevor Partridge on September 26, 2013 - 6:56 am

    Ah ha! I hadn’t been setting my aggregates to “countcolumn”. Thanks a ton for response and help. I’d actually been going back and forth between this post and the one in the link you provided but had missed that one piece. Thanks again!

  3. #4 by Trevor Partridge on September 26, 2013 - 8:25 am

    Sorry, I have one follow up question if you don’t mind. I’m trying to have a filter based off a link-entity to the Queue item whether it’s worked on by or not. Same thing is happening though I inserted the fetchXML code for that filter and it defaults to the filter and only pulls items in the queue for all columns. Is it possible to use a second link-entity for a filter condition? Thanks!

  4. #5 by Jeremy on March 14, 2014 - 2:18 pm

    I would like to do this with just two data sets, not four. The two sets I have are a goal number and the actual. I would like to keep the actual bar solid while showing the goal # slightly transparent (similar to a beaker filling up with liquid). I am a novice so this example kind of confuses me with the four different data sets you included (although it is awesome). Any help would be appreciated!

    • #6 by CRM Chart Guy on March 22, 2014 - 7:05 pm

      Hi Jeremy, thanks for the reading. You can follow the same process, but only create the two series you need. The goal first, and then the actual. You still need to add the DrawSideBySide custom property. It’ll probably be easier to try and follow the steps, rather than trying to remove the additional series from sample xml.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: