I’ve spent the last 7 years working with sales ops teams, helping them tell the story of their sales organization with data.

Over that time, I’ve learned first hand that the problem with data analysis is (and always has been) data transformation.

Why?

Because if data is structured the right way:

  • More people can use it. 50% of people in the sales org can run a basic Salesforce report.  Less than 5% of people can wrap their head around how to JOIN data across two objects where there is a one-to-many or many-to-many relationship.  Less than 1% probably know advanced Excel manipulation to run a VLOOKUP.
  • It’s repeatable and timely.  You hit ‘refresh’ on the dashboard/report and you get real-time information on the spot.  Need to know if your connect rate just crashed along with the stock market?  You can know that today instead of two weeks after the end of month when the reports are finally crunched by your analyst.
  • It becomes reliable. People don’t question it (or themselves).  They take action.

 

The challenge with getting data structured this way is that it almost always comes from different sources, so you’re going to have to do some aggregation/processing to get real insights.

An easy example is “tracking engagement against an account.”  If you want to aggregate email clicks between your sales engagement platform and your marketing platform, you’re going to have to have to make sure that 1) a “click” means the same thing in both contexts (eg: is clicking ‘unsubscribe’ a click?)  2) that they can both write to the same field in the same format OR have them write to two fields and then do some aggregation via a Process Builder / Flow.

A more complex example is tracking ‘how many distinct sequences/days it takes to move an account from stage ‘Prospecting’ to ‘Opportunity’.  To do this, you need a way to enrich Account and/or Activity with a notion of a sequence starting and stopping.  And you need to disambiguate which touches are coming from the sales engagement platform and marketing.  And you need to tie a conversion event via opportunities to ensure this is all automated.  Yuck.

So how do you do this data transformation?

Over the years, we tried really hard to make to do these types of data processing through Process Builder and Flows, but we ultimately failed until we used APEX.

 

Here’s why:

 

1. Control at the Transaction Level

In databases, a ‘transaction’ represents a unit of work against the database.  Often, it’s just a single query (eg: “UPDATE record X with these new fields”).  They become much more important when you are updating multiple records at once (eg: “INSERT this Contact and Account at the same time and tie them to each other”), since you may have downstream business logic that doesn’t work if you don’t get the order right.

Process Builder doesn’t let you have this level of control, because you can’t guarantee the order in which the actions on each node are executed.  So you couldn’t say:

  • Action A: update record X THEN
  • Action B take the output of Action A and do something else”.

To do that, you need to get into using flows, which leads us to problem #2.

 

2. Error Handling (Try/Catch)

Screen Shot 2020-07-16 at 2.43.42 AM

With software, things go wrong and you’re not always going to catch everything in sandbox testing.  But CRM is mission critical software – when something goes wrong, jobs and $$$ are on the line – so you need to minimize the risk that your users’ workflow will be interrupted.

(I learned this the hard way)

Unfortunately, Process Builders/Flows don’t give you the ability to fail gracefully when your complex logic is executing, which means that one wrong move can result in a pretty big workflow disruption.  And… the post-mortems are never pretty – “was it really worth adding that rollup field for our reps to go down for two hours?!”

With APEX, you don’t need to make this tradeoff because you have an AFTER handler, that lets your run specific logic after the initial transaction has been committed, and because you have the awesome ability to do error handling on the fly (try/catch blocks to the rescue!)

 

3. Error Reporting

Not all errors are the same in severity and often they need to be sent to different people.

Salesforce is notorious for having a weak error reporting system with limited customizability for organizations out of the box.  It gets exponentially worse if you’re a developer who’s deploying projects via Managed Package.

APEX gives you the ability to build a real error reporting pipeline since it is code.  Imagine integrating Salesforce errors into things like Pagerduty or Datadog or any other developer tool – this is really

 

Conclusion

I resisted learning APEX for the longest time because I hated the notion of having to learn yet another programming language when SFDC had so many other automation functions.  But what I found was that I was able to do this only with small scale deployments, where both the data complexity and the stakes were low.

Adding this tool to my toolkit has completely transformed how we use Salesforce (and saved us literally hundreds of thousands of dollars that would have gone to data analysts, data warehouses, etc).  I highly recommend you check it out!