5 - Dash Package Report Customization

Make Your Reports Your Own

Once you've determined which new dashboards your organization will use, you'll need to clone and customize the underlying reports. You'll also need to associate those new reports to the correct new dashboards. The documentation below reviews the types of reports available, and clarifies the ways in which you can customize them.

To make this all a little easier, we've identified four types of customizations you can make to your reports:

• Timeframe adjustments
• Funnel adjustments
• Segmenting reports
• Attribution report modifications

It bears repeating: If you're new to Salesforce Reports & Dashboard documentation, it's critical that you review the following documents before getting started with customizations:

https://help.salesforce.com/articleView?id=analytics_overview.htm&type=0&language=en_us

Salesforce Trailhead:
https://trailhead.salesforce.com/trails/force_com_admin_beginner/modules/reports_dashboards


And now, let's start down the path to your brand-new reports!

Timeframe Adjustments

With a timeframe adjustment, you can alter your data in two different ways. You can:

• 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, you'll learn how to create some global timeframe filters that optimize dashboard performance by limiting 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, you can safely 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 reports typically deal with funnel metrics like volume, conversion, and velocity. It all 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.

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 won't 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?"
They do not answer this question: "How many total SALs, SQLs, Pipeline, and Won did I generate in the last 12 months, irrespective of the response date?"

In your 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.

If you wanted to change this component to identify which funnel stages were attained during a specific time period, you would need 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:

Opportunity Reports

You probably won't be shocked to learn that most orgs use the Opportunity Create Date for Opportunity Reports. Some companies, however, 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 for a very long time, it could throw off metrics that rely on averages. In a situation like that, you'd likely need to consider filtering out those opportunities individually.

Global Timeframe exclusions should be based on opportunity create or closed date fields.

Attribution Reports

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.

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 can modify them.

Volume

Volume reports count the number of records that achieved a particular funnel stage. These reports 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'd use to count the volume of MQLs from responses generated in the last 12 months:

Psst!: Here's a 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'd 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 equation is pretty simple: Later funnel stage total volume / earlier funnel stage total volume

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. You may be using different funnel fields or field labels, but here's a generic example:

Scroll down to the funnel stage field and select 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:

CampaignMember.FCRM__FCR_SAR__c:SUM/CampaignMember.FCRM__FCR_QR__c:SUM

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). That formula would look like this:

CampaignMember.FCRM__FCR_SQR__c:SUM/CampaignMember.FCRM__FCR_QR__c:SUM

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

Like conversion rates, velocity is also calculated with report formulas. Here are some things to keep in mind regarding velocity:

• 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 wise 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, you'll find fields for each stage progression velocity, like 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'll need to create a new campaign member field to support the calculation. Check existing fields to find the field formulas to use.

The velocity formula must include the additional criteria that the more progressed funnel stage has been achieved. It does this 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.

Segmenting Reports

Your organization may segment and look at the data differently than the company next door does. Types of segmentation include:

• Campaign segmentation based on Campaign or Campaign Member fields, like:
  • Type
  • Member Status
  • Campaign Sourced By
• Firmographic
  • Account Types
  • Target vs Non-Target
  • Industry
  • Customers vs Prospects
  • Market Segment
• Demographic
  • Job Function
  • Score
  • Geo

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.

Good news: Segmentation is pretty simple! It goes like this:

• Determine whether 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.

Attribution Reports

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

Here's a Campaign Influence Detail record.

Counting Records

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. Be very careful which totals you look at when reporting on this data, particularly when counting opportunities.

In the report below, we have a total of 224 total Opportunities. The report is summarized by campaign name. 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, 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. This means 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. They 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, along with the ways in which you may wish to customize them.

Touch Reports

Several reports and dashboard components 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, you can use the least restrictive model, or you can choose not to 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 view bucket touches relative to the opportunity create date, or look at touches between specific opportunity stages. Here's how:

In the images below, you can 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:

1. Opportunity Name: Opportunity that the campaign member influenced.
2. 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.
3. 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.
4. Opportunity Stage Date: This field will display the date the opportunity stage was set, else the opportunity created date.
5. Next Opportunity Stage: If there is opportunity stage progression, this stage will show the next opportunity stage set.
6. Opportunity Stage Progression Date: This field will show when the next opportunity stage was set.
7. Days Before Opportunity Create: This field calculates the days from the campaign member response date to the opportunity created date.
8. Days Before Opportunity Close: This field calculates the days from the campaign member response date 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

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, timeframe, or geo, even by looking at campaigns that influenced opportunities that weren't sourced by Marketing. To do this, filter out any opportunities where the primary campaign is populated and has been 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.