This is part of the “How We Rebuilt All Our Sales Tools Natively In Salesforce” Series.  After 7 years of working in Sales Ops with customers like Square and Wix, we learned two things — first, that sales tools are both the problem and solution to all of life’s problems, and second, that Salesforce do a BETTER job than sales tools if it’s configured right because a system of record has inherent advantages in accuracy/simplicity/cost vs. an external system.

 

Why do this at all?

As we mention in our article Sales Stack Freedom, there are three problems with relying on your Sales Engagement provider to own top of funnel reporting.

First, they limit their analytics focus to just top of funnel (trying to please the SDR leader).  Second, they treat themselves as the system of record, so they often miss other data created in SFDC from Marketing and other sources.  Third, the average organization will change their sales engagement provider every 18-24 months, which means big reporting inconsistencies and data gaps are bound to emerge in your funnel data over time.

All of this leads to analytics pain and complexity for you – the Sales/RevOps leader.

What This Recipe Will Deliver For You:

By following this recipe, you’re going to be able to perform all of the following reports in Salesforce without ever having to do CSV exports, VLookups or Data Cleanups ever again.

  • Capacity – what % of the time reps are spending at each part of the funnel? (prospecting vs. closing, which cadence steps, etc)
  • Conversion Rates – how many touches does it take to create an oppt?
  • Best Practices – which calls / emails / text messages are driving conversions?
  • Segmentation – which account segments are showing the best results?

Moreover, your reporting engine will continue to work regardless of what sales tools you use at any given time, because the system you’re about to build does all data processing using Salesforce as the system of record (as long as activity data is making it into the system, this will work!)

 

Ingredients:

Either:

  • Salesforce (of course)
  • Admin Access
  • Knowledge of Flows or APEX (because you will need SOQL)

OR:

Truly’s free Revenue Process Automation managed package, which will do all of this for you (you can skip to the end of the article)

 

Step 1: Build The Funnel Data Model

In order to make this work, first we’re going to need a way to model the progression of an account through the funnel.  Salesforce doesn’t offer this natively, so we have to create it.

You need to create a picklist on the Account which contains each stage of your customer lifecycle (let’s call this Account Stage).  Our recommendations for picklist fields are:

  • Open (waiting to be prospected)
  • Prospecting (currently being prospected)
  • Opportunity (converted to an opportunity)
  • Customer (closed won)
  • Nurture (closed lost)
  • Do Not Contact (never contact them again)

 

Step 2: Automate Stage Transitions

Your analytics will only be as accurate as our data, and we can’t rely on manual rep data entry for something as precise as funnel analytics (especially SDRs whose schedules are extremely difficult)

So… we automate.

You should consider what the activities/events are that you want to drive the conversion event for each of the account stages.  Here are some examples we recommend:

  • When an Account is created, set the default stage to Open
  • When an Activity is first logged against an Account or related Contact, transition the stage to Prospecting
  • When an Opportunity is created, transition the stage to Opportunity, etc.

You can use Process Builder and Flows or APEX for this.

Step 3: Model Conversion Events

In order to model the funnel, we need to have a data structure that houses information about conversion events.

At a minimum, we need to know 1) when the conversion happened and 2) what things happened since the last conversion event (eg: how many touches, time elapsed, etc) and 3) what drove the conversion event.

There are many approaches to this problem, but we want to do it in a way that’s extensible, meaning that someone can go and change the underlying assumptions (Account Stage, Oppt Stages, Activity schema, etc) and still have this feature work.

Our recommendation is to model these either directly on the Activity OR on a custom object that has a lookup to Activity.  We opted to go with the latter option in our package, just because we didn’t want to be constrainted by Salesforce’s 100 custom field limit.

Once you have this data structure, you’re going to want to create a Flow/APEX that takes a ‘snapshot’ of all Activity (eg: Account.Tier, Account.Stage, Opportunity.Stage) into this data structure.

Denormalizing your data into one place like this has several benefits:

  • No Data Transforms –   If you preprocess all this data, you can run virtually any funnel report off a single report instead of dealing with joins, calculations off of OpportunityHistory, etc.
  • No JOINs – JOINs severely limit the number of people who can use reporting (it’s a pretty technical concept).  By pulling this all into one table, more people are able to access reporting.
  • Faster Queries – even a simple JOIN in SFDC between Accounts/Activities (two columns) yields a 5x penalty in reporting speed.

 

Step 4: Process The Conversion Events (Data Transforms)

One benefit of bringing data into salesforce was the possibility of us creating our own custom metrics that we couldn’t get in our Sales Engagement Platform.

For example, one metric we found interesting to us was how persistent a given rep was in two contexts: 1) the time that elapsed between touches when no engagement had come from the prospect (how “aggressive” we are) and 2) the time it took for a rep to respond to a customer engagement once it happened (our response SLA).

Taking #1 as an example, we needed to do a particular set of calculations in order before storing the result in our new data structure:

  • define what an ‘engagement’ was.  We realized that the definition changes by role and stage of the funnel, so we had to build a ruleset that said “an engagement is when a customer either responds to our Email (not an autoreply) OR when a customer talks to us on an outbound call for at least 90 seconds” and then make sure this ruleset only applied to activities that were related to accounts in the stage of Prospecting.
  • make sure our Who/WhatId relationships were accurate, because we rely so heavily on Account Stage to contextualize the definition of an engagement
  • run queries using the information in the current activity (eg: find the most recent engaged task)
  • run calculations based on query results (eg: current Task.Created_Date – previous engaged Task.Created_Date)
  • run calculations based on related objects’ histories (eg: current Task.Opportunity.Stage vs. previous Task.Opportunity.Stage.  This one is particularly tricky because OpportunityHistory is not the best object to work with.  We ended up having to create our own custom object for capturing object history.

Of course this is a lot of work, but it’s still at least 3x more efficient to do in Salesforce than a one-off analytics project, and you get the benefit of having your reporting run continuously and be up to date in real-time.

Of course this is a lot of work, but it’s still at least 3x more efficient to do in Salesforce than a one-off analytics project, and you get the benefit of having your reporting run continuously and be up to date in real-time. Here’s why — if you were to do these calculations, you would need to work off of snapshot data, which would require data export, creating complex algorithms for inferring order (eg: did the task come before or after the Opportunity Stage changed?  What if it changed twice?).

Step 5: Dashboard (and Enjoy!)

Now that you have a single mega-report that has all of your funnel data, you can create your dashboards in seconds because the reports are simple — every chart you can think of can be built off of just a single mega report, and be filtered on any field (no more need to worry about cross object filter limitations!

 

UGH – Just Reading This Article Made Me Tired

If you’re looking at this and thinking “man is this so much work”, you’re probably right.  What started as a side project that was 100 hours quickly became 1000 hours as we kept going down the rabbit hole of enhancing functionality over time.

If you don’t have the time and resources to implement this recipe, but you really like the concept, there’s an easy solution — take the Truly Revenue Process Automation managed package for a spin.

It works with just a one click install (it’s all APEX so it also uninstalls in one click) and it generates historical reporting, so you’ll see instant time to value, being able to decide if it works for you in under a week.

Interested?  Please get in touch via chat or demo form on our site!