Opportunities, Marketing Touches and The Power of One

July 13, 2016 Vivek Ravisankar

2893549851_ef5121f78b_z

There’s a lot of value in building reports in native Salesforce with an object and its child objects.

And one of the key values you look for in this kind of report is how many unique counts exist. One key use case is from a lookup relationship on the child of a parent object. For example, finding the number of unique Opportunities that are being touched by a Marketing Campaign. You would think that this would be easy to show. But in reality, this is it not straightforward in Salesforce.

The problem is you end with a misleading count of records at the bottom of each report that can leave you scratching your head and wondering: what does that number mean?

But don’t worry. I’m here to help. Here is a neat Salesforce analytics trick that will make your life much easier. It’s quick, simple and even has a cool name: The Power of One. In fact, it’s sometimes called the Greatest Formula Ever Written. And here are two ways you can use it to ensure that you’re getting the most from your reports.

Case Study 1

This will allow you to produce a report showing all the Campaigns and the accurate number of the unique Opportunities that they have influenced. So when I generate a Report with Campaign as the grouping, I can get an accurate measure of the Opportunities influenced even if a campaign has multiple touches onto that Opportunity.

Here are the steps:

  1. Create a ‘One__c’ formula field on the ‘Opportunity’ object
    • The formula should have the value ‘1’
  2. Add the new field to your report type by clicking ‘Edit Layout’ and add the new ‘One__c’ field onto the layout
    • Note : This field will be under the ‘Opportunity’ lookup fields
  3. Create a report and summarize by ‘Campaign’
  4. Add the ‘Opportunity:One__c’ field to the report
  5. Click ‘Summarize’ to sum the ‘One__c’ field
  6. Now, you should now see the unique number of Opportunities a Campaign has influenced

Image1

Case Study 2

So how can I get an accurate report of all of my Opportunities – with or without Marketing Touches? Salesforce automatically puts in ‘1 record’ for Opportunities even though they do not have any touches. Here’s a trick that will give you an accurate sum of touches in a report.

Here are the steps:

  1. Create a ‘One__c’ formula field on the ‘LeanData__Marketing_Touch__c’ object
    • The formula should have the value ‘1’
  2.  Clone our LeanData Account-Based Report Type
  3. Change ‘Marketing Touches’ to allow ‘with or without related records’
  4. Click ‘Edit Layout’ and add the new ‘One__c’ field onto the layout
  5. Create a report and summarize by ‘Opportunity’
  6. Add the ‘One__c’ field to the report
  7. Click ‘Summarize’ to sum the ‘One__c’ field

IMage2

By completing these steps, you can quickly view a summary of the unique records attached to a particular parent/child object relationship.

Previous Article
Ensuring Data Quality with Validation Rules
Ensuring Data Quality with Validation Rules

Let’s just say that having a messy room was not an option when I was growing up. I was raised in a house wh...

Next Article
Managing Your Salesforce Guests
Managing Your Salesforce Guests

You just moved into a new apartment. And now you’re asking yourself: Who else should have access to my plac...