Custom fields, Apex triggers, VLOOKUPS, Oh my! How to make short work of cross-sell campaigns in Salesforce.
A version of this originally appeared on the BizOps Blog.
by Greg Keshian, COO
Imagine putting the best cross-sell target accounts into the hands of your reps with just a few natural-language queries of your account data in Salesforce, Marketo, HubSpot or wherever it resides. Now picture automatically feeding that data into a Salesforce campaign so your sales reps know where to focus.
It really is as simple as that with the Rekener Account Control Center. Here’s why and how it’s so much simpler than doing this from within Salesforce.
Yikes! You need to create another custom Salesforce report
If you’ve ever tried to create a Salesforce report of accounts that purchased only one of your products, you know it’s a big hassle. Having this report is important because it helps your sales team focus on the best cross-sell target accounts. We love Salesforce, but the way Salesforce stores its data makes it impossible to generate it in a scalable way.
Let’s break down the whole painful process:
This StackExchange post describes at a high level how you can try to do this in Salesforce. Here’s a more detailed explanation:
1. Create a custom field on your OpportunityLineItem object. You need to do this to store the name of the product associated with that line item. Your sales reps will need to type in the name of the product being sold, per line item, or you’ll need to build a workflow that carries the product name down and stamps it on the line item.
2. Create rollup summary fields on the Opportunity object. This is necessary to count all the line items on that opportunity containing a product. Note: you’ll need to create a rollup summary field for every unique product that you track in Salesforce. So, if you have a lot of products, you’ll need to create a lot of fields on your opportunities — one to track each product.
3. Create rollup summary fields on the Account. This is similar to the step above but you do this to track the number of line items associated with each sold product. Again, you’ll need to create a rollup summary field for every single product that you sell. Obviously that could be quite an undertaking if you offer many products. You’ll also want to put criteria on this rollup summary field that says the Opportunity Stage = Closed Won.
4. Build an Apex trigger to periodically modify all Opportunities. The rollup summary on the Account object isn’t going to fire unless the Opportunity object underneath it is modified. You’ll need to run the Apex trigger once a day or so to make sure your accounts calculate properly.
Once you’ve set up and tested all of the above, you’ll be able to run a very basic report along the lines of “Purchases: Product A > 0” AND “Purchases: Product B = 0”.
As if it’s not enough that you had to jump through hoops just to create and run this report, there are several drawbacks with this approach:
- If your business offers lots of products, you’ll need to add another rollup summary field on both the opportunity and the account every time you add a new product. You’re limited to 25 rollup summary fields per object in Salesforce, so if you have more than 25 products, you’re out of luck.
- It would be useful to track not just the number of purchases by product, but also the bookings volume of that product on the account. But you’d need to create another field on both the opportunity and the account in order to track this second Bookings by Product field. And you’d need to create that extra field for every single product.
But the pain doesn’t end there
Let’s step back a bit. Before you can even take a stab at this tear-out-your-hair process, you need to know which accounts are targets for cross-sells. The best way to identify them is by finding the accounts with active annual recurring revenue (ARR) or monthly recurring revenue (MRR) for one product but not for any other product. You’ll want to be sure to capture “active” ARR or MRR in order to exclude those customers who may have purchased the product long ago and no longer have an active subscription.
Unfortunately, Salesforce isn’t very cooperative when it comes to this. When you’re creating the rollup summary field on the account to track opportunities that are Closed Won for a particular product, you want to be able to say something like “Subscription End Date > TODAY”. But Salesforce doesn’t let you use dynamic date ranges in these rollup summary fields. Instead, you’d need to hardcode a date to substitute for TODAY. Obviously that’s not a scalable or practical way to identify cross-sell opportunities as your business grows.
Wait…what about Excel?
Companies do try to attack this problem using Excel, but you’ll need to decide for yourself if that’s truly a better option. Here’s what it takes:
1. In Salesforce, stamp the product name on each line item with a workflow.
2. Run an Opportunities with Products report in Salesforce representing the following fields: Account Name, Account ID, Product Name and Total Price. Be sure to filter your report to just those opportunities that are Closed Won.
3. Export this Salesforce report to a CSV file. Note that since we set the criteria to Closed Won = True this report will only show accounts that have made a purchase in the past. Even if you remove that criteria, you’d still only see accounts that have had an opportunity associated with them, and only for an opportunity with at least one line item on it.
4. Run an All Accounts report, exporting only the Account Name and Account ID. This report fills in the blanks from the report you exported in the previous step, showing accounts that never bought anything or never had an opportunity opened.
5. Format the first report and join the two reports. When formatting the first report, organize the data so that all information about each account is in one row.
The easiest way to do this is using a pivot table. When you create your pivot table, put the account ID into the rows and your product names in as columns. Then as values, take the SUM of the total price, and also the COUNT of the total price.
Now that your data is formatted properly, you can join the two reports by doing a VLOOKUP. On your All Accounts report, create more columns so there are two columns for each product: one for the SUM of total price, and one for the COUNT of total price. Next do a VLOOKUP on Account ID, over to the Opportunities with Products table, and grab the relevant column for each product amount or count. Carry that formula down for all of your accounts. Don’t forget your cell anchors, and don’t forget to manually change the column reference when you drag to the right: it doesn’t change automatically.
Phew! Now you have an account report displaying every account as well as its total amount purchased by product and total number of sales by product. At last you can see the accounts with one product but not the other(s) by using data filters in Excel.
The drawbacks of the Excel approach
If the pain of that process wasn’t enough, here are the flaws with this approach:
- This report doesn’t include any contacts, so if you want to deliver contacts inside targeted accounts to the sales team for cross-sell, you’ll need to run yet another report from Salesforce (Accounts with Contacts). Then you’ll need to run more VLOOKUPs to find contacts at the accounts you care about. Next, you need to upload those contacts back into Salesforce with a flag (like a Campaign, or some other specific field), to indicate these are contacts you’d like your sales team to target for cross-sell.
- Just like the Salesforce process, this one isn’t scalable. You’ll need to go through this whole process every time you want an updated look at your accounts by product. You’ll need to run both reports, export them to CSV, pivot by account and product, and do the VLOOKUP from one report to the next. And then you’ll need to generate another report and VLOOKUP if you want to grab contacts at those accounts.
Clearly that is a tedious, time-consuming, error-prone task. How can you possibly go through all those steps every time you want to refresh your cross-sell campaign?
Put all that pain behind you with Rekener
The good news is that you don’t need to suffer through all that pain. The Rekener Account Control Center gives you visibility into all of your accounts using your Salesforce data, and lets you summarize product-level information up to the Account. In a snap, you can answer basic questions like, “Which of my accounts bought Product A but not Product B?” You can also answer much more interesting questions, like:
- Which product has the best close rate for new business?
- Which product has the best close rate for cross-sell?
- Which product has the highest ASP when sold to the manufacturing industry?
The Rekener Account Control Center can answer many other questions to help you create an effective cross-sell strategy. And in addition to making quick work of your Salesforce data, it seamlessly integrates with marketing automation tools like Marketo and HubSpot. That means you can zoom in on the accounts that haven’t yet purchased a specific product but have hit pages on your website about that product. Not only do you get a list of the accounts that don’t yet have that product, you can also see which ones are showing an interest in it. You can instantly add all contacts at those accounts that you’d want to target and push them directly into a campaign in Salesforce.
Easily find your best, most interested accounts
Identifying which accounts are your best targets for cross-sell and then putting them into the hands of the marketing and sales teams should be a simple exercise. You shouldn’t need to create custom fields, modify workflows or set Apex triggers in Salesforce, export data from Salesforce to Excel, use pivot tables or run VLOOKUPS. The Rekener Account Control Center lets you leverage the account lifecycle data you already have in Salesforce to find the best accounts. Plus, it lets you use data in your marketing automation systems like Marketo and Hubspot to see which of those accounts are showing interest. You can easily take all the contact data related to those accounts and push it into a Salesforce campaign, ensuring your sales team is focused on the accounts with the greatest potential for cross-sell.
Now, what are you going to do with all your free time?