Calculate ARR by Product with Salesforce
Ever wanted to create a Salesforce report of accounts that haven’t yet purchased a certain product, only to realize how hard it is? The benefit of running this report in Salesforce would be to help the sales team focus on the best cross-sell target accounts. These accounts have previously purchased only one of your products, but haven’t yet purchased others. But because of the way Salesforce stores its data, you really can’t run this report in a scalable way.
This StackExchange post has a pretty good description of the desired outcome, and also describes how you could try to cobble this together in Salesforce.
To do it in Salesforce would require the following:
The creation of a custom field on your OpportunityLineItem object to store the name of the Product associated with that Line Item. You'll need to either have sales reps type in the name of the Product being sold, per Line Item, or build a Workflow that carries the Product name down and stamps it on the Line Item.
The creation of rollup summary fields on the Opportunity object to count all of the Line Items on that Opportunity with each Product on it. Note that here 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.
The creation of rollup summary fields on the Account, similar to the ones built on Opportunities, to track the number of Line Items where you sold each Product. Again, you'll need to create a rollup summary field for every single Product that you sell, which could be an issue if you have many Products. You'll also want to put a criteria on this rollup summary field that says the Opportunity Stage = Closed Won.
You'll also need to build an Apex trigger to periodically modify all of your Opportunities. The rollup summary on the Account object isn’t going to fire unless the Opportunity object underneath it is modified. Run the Apex trigger once a day or so to make sure your Accounts will calculate properly with this field.
Now that 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”. There are several flaws in this approach though. For instance, it’s not easily scalable if your business has lots of products, because you’ll need to add another rollup summary field on both the Opportunity and the Account every time you add a new product. Also, 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. Similarly, 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. This leads to even more scale difficulties because you’d need to create another field on both the Opportunity and the Account in order to track this second “Bookings by Product” field — for every single Product you have.
Further complicating all of this is how to attack the issue at hand. The best way to attack the problem of knowing which accounts are targets for cross-sell is to find the accounts with active annual recurring revenue (ARR) or monthly recurring revenue (MRR) of one product but not another product. Maybe the customer purchased the first product long ago and no longer has an active subscription. The difficulty here though, is that when you're creating the rollup summary field on the Account to track Opportunities that are Closed Won and that have a particular Product, you want to be able to say something like “Subscription End Date > TODAY”. But in these rollup summary fields, Salesforce doesn’t let you use dynamic date ranges. Instead, you’d need to hardcode a date in there to substitute for TODAY, which clearly doesn’t scale as time goes on.
Export to Excel
The other common way to attack this problem is to do it in Excel, which requires the following:
In Salesforce, stamp the Product name on each Line Item with a workflow.
Run an Opportunities with Products report in Salesforce where you bring in the following fields: Account Name, Account ID, Product Name and Total Price. You'll want to filter your report to just those Opportunities that are Closed Won.
Export this Salesforce report to a CSV file. Note that this report will only show Accounts that have made a purchase in the past, since we set the criteria to Closed Won = True. Even if you remove that criteria, you'd still only be seeing Accounts that have had an Opportunity associated with them, and that Opportunity also has at least one Line Item on it.
For that reason, we also need to run an Accounts report, and we'll only export the Account Name and Account ID. This report fills in the blanks from the prior report, showing Accounts that never bought anything or never had an Opportunity opened.
Now we will format the first report so that we can join the two together with VLOOKUPS. Specifically, we will organize the data in the first report so that all of the 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 the data in the Opportunities with Products report is formatted in a way where you can join it to the report of all your Accounts by doing a VLOOKUP. On your All Accounts report, create more columns. There will be two columns for each Product: one for the SUM of Total Price, and one for the COUNT of Total Price. Then 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.
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 that have one product but not the other by using data filters in Excel.
Unfortunately, there are issues with this approach, too. For one, we don’t have any Contacts on this report, and so if we want to find Accounts with or without certain Products for the purpose of delivering Contacts inside those Accounts over to the sales team for cross-sell, we'd have to run yet another report from Salesforce, which has Accounts with Contacts, and then run more VLOOKUPs to find Contacts at the Accounts we care about. Then, we’d have to upload those Contacts back into Salesforce with a flag (like a Campaign, or some other specific field), to indicate that they are Contacts we'd like our sales team to try to cross-sell to.
Another major issue here is scalability. You'll need to go through this whole process every time you’d want an updated look at your Accounts by Product. This will require running both reports, exporting them to CSV, pivoting by Account and Product, and doing the VLOOKUP from one report to the next. And then another report and VLOOKUP if you want to grab Contacts at those accounts. That is a tedious, time-consuming task to undertake, and is both error-prone and not scalable.
The Rekener Approach
Is there a better solution? Yes. 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. So you can answer basic questions like, “Which of my Accounts bought Product A but not Product B?” in a snap. But 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 put together an effective cross-sell strategy. And because it seamlessly integrates with marketing automation tools like Marketo and HubSpot, you can zoom in on the Accounts that haven’t yet purchased a specific Product and, in addition, find those that have hit pages on your website about that product. Now, you not only have a list of the Accounts that don’t yet have that Product, but 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 with a sales or marketing campaign, and push them directly into a campaign in Salesforce.
Identifying which accounts are your best targets for cross-sell and then putting them into the hands of the marketing and sales teams shouldn’t be complicated. No need to create custom fields, modify workflows or set Apex triggers in Salesforce, and no need to export data from Salesforce to Excel or 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, but also 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 that the team is focused on the accounts with the greatest potential for cross-sell.
What are you going to do with all your free time?
Add new comment
Your comment will appear soon!
Greg is COO and Co-Founder at Rekener. Greg’s entire career has been focused on using BizOps to grow recurring revenue businesses. Before joining Rekener, he served as VP of Operations at ZeroTurnaround, where he built its BizOps practice and team. He did the same for the AVOKE call center analytics business, a SaaS company within BBN Technologies. He got his start in BizOps for recurring revenue businesses while at AppNeta.
Sales Rep Scorecards
Rekener Sales Rep Scorecards is the quickest and easiest way to get all of your sales rep reporting done...period.Learn More
Get the best BizOps content delivered to your inbox twice a month.