AFTER READING THIS ARTICLE, YOU WILL BE ABLE TO: Customize Reports that are part of the Dashboard Package
Once you determine which Dashboards your organization will use, you will need to clone and customize the underlying reports and associate the new reports to the new dashboards. In this section we will review types of reports available and way in which you can customize them.
For the purposes of simplification we have grouped the types of customization into categories:
- Timeframe Adjustments
- Funnel Adjustments
- Conversion Rate, Velocity, Counts
- Adding custom funnel fields (later)
- Segmenting reports
- Attribution report modifications
If you are new to Salesforce dashboards and reports please review the following content:
Salesforce Reports & Dashboard documentation:
Timeframe adjustments on reports can do two things
- Cohort data based on a timeframe
- Exclude older data you don’t want in the report
About excluding older data
In the dashboard customization section below we discuss creating some global timeframe filters in order to optimize the amount of data a dashboard has to render. For example, if you have a decade of database information, but would only report on data from 2016 onwards, it is safe to add add global timeframe filters to the reports to exclude old data. The key is to know which date field to create the filter for. Keep in mind how the timeframe filters impact the data. Reviewing the campaign member date filters below is a good place to start.
Campaign member Reports
These are typically funnel metrics such as volume, conversion and velocity reports. It depends on how you want to view the data on the dashboard as to which field you will use for timeframe filters.
If you filter on the campaign member Response Date field, you are cohorting all of the data as of that date.
For example, let’s say we want to look at the Planning dashboard. This dashboard has volume metrics cohorted to responses generated in the last 12 months. That means that you may see all MQLs generated in the last 12 months, but you will not see all SALs generated in the last 12 months, you will only see the subset of SALs that also had a Response Date within the last 12 months.
These metrics answer the following question: of the responses I generated in the last 12 months, how many progressed to SAL, SQL, Pipeline, or Won?
It does not answer the following question: how many total SALs, SQLs, Pipeline, and Won did I generate in the last 12 months (irrespective of the response date)?
In the dashboard package all of the volume and conversion data is delivered from a single Salesforce report because there is a single timeframe filter that applies to all of the metrics. One of the reports is called Planning: Volume and Conversion.
This single report is referenced in the following 10 components in the Planning dashboard:
If we want to change this component to answer the which funnel stages were attained during a time period you would have to create individual volume and conversion reports for each funnel stage using the following criteria for each:
- MQL: MQL Date = LAST 12 MONTHS & MQL equals True
- SAL: SAL Date = LAST 12 MONTHS & SAL equals True
- SQL: SQL Date = LAST 12 MONTHS & SQL equals True
- Pipeline: Pipeline Date = LAST 12 MONTHS & Pipeline equals True
- Won: Opportunity Closed Date = LAST 12 MONTHS & Won equals True
Below is an example of the SAL report with an SAL date cohort:
Global timeframe exclusions for campaign member reports will either be based on the Response Date or specific funnel dates depending on how your organization wants to view the data.
Typically you would use the Opportunity Create Date. Some companies will filter on the opportunity create date and also include opportunities with a closed date within their timeframe. If you just want metrics on closed dates then using a closed date filter would make sense, but look for any anomalies in the data that may throw off the metrics (for example if you have a very large deal that has been open a very long time, it could throw off metrics that rely on averages) and consider filtering out those opportunities individually.
Global Timeframe exclusions should be based on opportunity create or closed date fields.
Campaign Attribution is calculated on a custom object called ‘Campaign Influence Detail.’ Attribution reports should always include all Campaign Influence Detail records. The timeframe filters should use the opportunity create or closed date similar to the way you would filter opportunity reports.
Global Timeframe exclusions, if not handled by the influence model itself, should be based on opportunity date fields.
Funnel Metric Adjustments
Funnel metrics are set on the campaign member object. Below is a breakdown of how the funnel fields are set and how you would modify them.
Volume reports count the number of records that achieved a particular funnel stage. These reports should should have the following filters:
- Response Status is not Blank or Not a Response
- Funnel stage field is True
- Date filter (either Response Date or Funnel Date cohort)
Below is an example of the report filters you would use to count the volume of MQLs from responses generated in the last 12 months.
Conversion rates are calculated with report formulas. Any single report is limited to five report formulas, so if you have more than five funnel stages you will need multiple reports to render all of the conversion rates. Look at existing conversion reports to copy and modify the conversion formula. The Report ‘Planning: Volume and Conversion’ has an example of looking at multiple formulas.
Here is the Salesforce help topic for working with formulas in reports: https://help.salesforce.com/articleView?id=reports_builder_fields_formulas.htm&type=0&language=en_us&release=206.13
The report for a single funnel stage conversion rate should have the following filters:
- Response Status is not Blank or Not a Response
- Previous funnel stage field is True
- Date filter (either Response Date or Funnel Date cohort)
Below is an example of the report filters you would use to calculate the conversion rate of MQL’s to another funnel stage cohorted from responses generated in the last 12 months.
The report formula references the field API names for funnel stages.
The formula type is percentage.
The math is as follows:
Later funnel stage total volume / earlier funnel stage total volume
You may be using different funnel fields or field labels. Below is a generic example. In building the formula you can select the ‘Sum’ for each funnel field.
The Summary Fields drop down will display all fields that can be summarized.
Scroll down to the funnel stage field and select the Sum. The API name of the field will be inserted into the formula text area.
Let’s look at the MQL to SAL conversion formula using the API Names after selecting them from the UI:
This formula calculates the sum of the campaign member SAL Field (in this case the API name we are using for SAL is FCRM__FCR_SAR__c), divided by the sum of the MQL field (in this case the API name we are using for MQL is FCRM__FCR_QR__c).
If we wanted to look at the conversion rate from MQL to SQL we would build the formula referencing the SQL field (in this case the API name we are using for SQL is FCRM__FCR_SQR__c). The formula would look like this:
Below is an example of single report where MQL is true, using a Response Date cohort, with funnel conversion rates from MQL to the other stages using report formulas.
Velocity is also calculated with report formulas. About the velocity formulas:
- Velocity is based on the average number of days between stages
- Velocity calculations use the campaign member velocity custom fields
- Same day funnel progression will evaluate to 0
- Lack of funnel progression will evaluate to blank
- Because this is an average calculation it’s good to look at the underlying data and consider omitting data outliers
In the planning dashboard a single report called Planning: Velocity is used to render all of the velocity metrics in these five components using the MQL Date cohort.
On the campaign member there will be fields for each stage progression velocity. For example you will have MQL to SAL, or SAL to SQL. These are the fields used in the report to calculate the average velocity. If you want to calculate the average velocity from one stage to another and the velocity field doesn't exist on the campaign member you will need to create a new campaign member field to support the calculation. Look at the existing fields to view the field formulas to use.
The velocity formula must include the additional criteria that the more progressed funnel stage has been achieved. It does so by requiring that the count of the more progressed funnel stage is not blank.
For example if we look at MQL to SAL velocity the formula says:
If there are no SALs in this report the formula result should be blank, if there are SALs, then calculate the sum of MQL to SAL / # total SALs.
In the example below FCRM__FCR_SAR__c is the API name for SAL, and FCRM__FCR_MQR_to_SAR__c is the api name for the ‘MQL to SAL’ field.
IF(CampaignMember.FCRM__FCR_SAR__c:SUM > 0, CampaignMember.FCRM__FCR_MQR_to_SAR__c:SUM /CampaignMember.FCRM__FCR_SAR__c:SUM, NULL)
Use the existing velocity report formulas to clone and create additional reports or velocity fields.
Your organization may have different ways to segment and look at the data. Types of segmentation include:
- Campaign segmentation based on Campaign or Campaign Member fields, such as:
- Member Status
- Campaign Sourced By
- Account Types
- Target vs Non-Target
- Customers vs Prospects
- Market Segment
- Job Function
During your deployment you should have identified the types of segments your organization uses for reporting. Any segmented fields that are reported on historic values should be synchronized to the campaign member via Response Management field synchronization. Field synchronization should be applied to values that will change over time, such as Targeted Accounts, Score, UTM values, or scores. The remaining fields should be those that are more accurate over time, and available on the related objects, such as Geo, Industry, and Campaign Type.
Segmentation is a simple matter.
- Know if your field is on the campaign member or a related object
- Add the field to the report
- Summarize the report by the field
For example, in the previous section we looked at an MQL conversion rate report.
This report is currently summarized by the Response Date.
If we add the Campaign Type as the summary value we can then segment this report by Campaign Type.
Campaign Attribution is calculated on a custom object called ‘Campaign Influence Detail.’ Attribution reports should always show all Campaign Influence Detail records. The timeframe filters should use the opportunity create or closed date similar to the way you would filter opportunity reports. Additional filters should be built into the report itself.
Campaign Influence Detail Object
Response Management supports running three attribution models at a time. A single Campaign Influence Detail record may contain the results of up to three model calculations for a given campaign member-opportunity combination.
Below is a Campaign Influence Detail record.
- The ‘Has Influence’ fields indicate which models the record is influential for. To report on a single model be sure to use the Has Influence Model # field to select records related to that model.
- For each model, the related amount fields will also have the Model# in the field name.
- The Total Op Revenue Model field will have the total influence calculated for the model for open, won and lost opportunities.
- This record was for a lost opportunity. The amount calculations will populate in the field related to the opportunity’s status at the time the calculations were run. Values will be either in the won, open or closed fields.
- Once you have settled on the models you wish to use you can overwrite the field label names by going to Setup > Translation Workbench > Override > and use the settings in the image below. Translation Workbench does NOT have to be enabled. Save each page before pagination.
|Tips||All the Attribution Metrics use the Has Influence Model 1 field as a filter so you are only seeing results from the Model 1 Calculations. You may wish to build out reports and dashboards that display and compare multiple model outputs.
All the Attribution Metrics use the Has Influence Model 1 field as a filter so you are only seeing results from the Model 1 Calculations. You may wish to build out reports and dashboards that display and compare multiple model outputs.
A single campaign member record may influence multiple opportunities, so the count of campaign influence detail records is NOT the same as the number of responses in the system. When reporting on this data be careful which totals you look at, particularly when counting opportunities.
For example: In the report below we are looking at a total of 224 total Opportunities. The report is summarized by campaign name, and for each campaign the number of opportunities influenced is correct. Keep in mind that a single opportunity may be influenced by multiple campaigns, and we see this in the report. If you totaled each sub-column’s opportunity count amount you would get a total of 1264 opportunities. This report’s total grouping is de-duped to the correct opportunity count because the opportunity ‘count’ field is on the opportunity so Salesforce can de-dupe within specific groupings.
Custom Report Type Modifications
A custom report type is used for reporting on the Campaign Influence Detail Object. It is called ‘Campaign Influence Detail w/Related Objects (CRT).'
To learn more about custom report types please review the Salesforce documentation here: https://help.salesforce.com/articleView?id=reports_report_type_setup.htm&type=0&language=en_us&release=206.13
Most of the fields you will want to report on from related records (Campaign, Opportunity, Contact, Account, Lead) can be added by customizing the report type. Campaign Member fields that are synchronized to the campaign member to capture point-in-time data, must be synchronized to the Campaign Influence Detail object during the database rebuild.
A subset of campaign member data is always copied over from the campaign member to the campaign influence detail record:
- Response Status
- Response Date
- Member Status
- Response ID
- Member Type on Create
Attribution Components and Customization
Below are some of the attribution components and ways in which you may wish to customize them.
There are several reports and dashboard components that look at touches relative to an opportunity. The ‘Touches’ are campaign member records that are considered influential for the model, so the count of 'touches' will differ based on the model you run the report against. To see the most touches, use the least restrictive model or don’t filter on model type at all.
In addition to adding global filters, you can modify the date ranges. These are rendered with bucket fields on the report.
Learn more about bucket fields from the Salesforce documentation here: https://help.salesforce.com/articleView?id=reports_bucketing_overview.htm&type=0&language=en_us&release=206.13
You can also bucket touches relative to the opportunity create date, or look at touches between specific opportunity stages. Let’s take a closer look at how to do this.
In the images below you will see the opportunity fields available on the Campaign Influence Detail record. The first record was from a campaign member response generated before the opportunity was created, and the second one is from a campaign member response generated when the opportunity was in the ‘Demo’ stage.
The campaign member response below was generated 32 days before the influenced opportunity was created and 32 days before the opportunity Close date. The next stage progression was Prospecting.
The campaign member response below was generated when the opportunity was in the Demo stage. The next stage progression was Qualified Opp. It came in 47 days before the closed date.
Here is a breakdown of the fields:
- Opportunity Name: Opportunity that the campaign member influenced.
- Opportunity Timeframe: This is a formula field that will resolve to ‘Pre-Opportunity’ if the campaign member response came in before the opportunity was created, or ‘Opportunity’ if the campaign member response was generated after the opportunity existed. You can use this field as a filter.
- Opportunity Stage: This field will indicate the stage the opportunity was in when the campaign member response was generated. Some companies overwrite this field name to be Opportunity Stage (Historic). It will render as ‘Pre-Opportunity’ if the campaign member response was generated before the opportunity was created.
- Opportunity Stage Date: This field will display the date the opportunity stage was set, else the opportunity created date.
- Next Opportunity Stage: If there is opportunity stage progression this stage will show the next opportunity stage set.
- Opportunity Stage Progression Date: This field will show when the next opportunity stage was set.
- Days Before Opportunity Create: This field calculates the days from the campaign member response date prior to the opportunity created date.
- Days Before Opportunity Close: This field calculates the days from the campaign member response date prior to the opportunity closed date. Keep in mind that for open opportunities the closed date is a moving target so you may only want to look at closed opportunities relative to this date.
Example Touch Report Modifications
|Bucket relative to opportunity create date||Update bucket fields to group based on the Days Before Opportunity Create field|
|Bucket only responses created pre-opportunity||Filter campaign influence details where either the Opportunity Stage or Opportunity Timeframe fields have the value of ‘Pre-Opportunity’
|Bucket touches between specific stages||Filter on Campaign Influence Detail records where the Opportunity Stage is the first stage you want to filter off, and the Next Opportunity stage is the second or later stage you want to report on. You should include later stages in case stages are dropped.
You can use the Opportunity Stage Date, and Opportunity Stage Progression Date fields to bucket timeframes before or after a stage is set. For example, look only at responses that came in two weeks before the opportunity progressed to Qualified Opportunity.
Top Campaigns by Attribution
These types of reports are based on the distribution of opportunity amounts across multiple campaign member responses. The output of the data is based on the model configuration. It’s important to be familiar with the influence models you are running so you understand why the revenue was distributed the way it was.
Be sure to think about the following:
- Global filters to apply
- Opportunity Timeframes
You may get more value viewing these reports through segment filters such as opportunity type or timeframe, geo, or even looking at campaigns influential to opportunities that are not sourced by Marketing. To do so, filter out any opportunities where the primary campaign is populated and is sourced by Marketing (you can use the opportunity 'Campaign Attribution' field which will display the opportunity's primary campaign's Campaign Sourced By field value).