January 15 2017
Datacube: Replacing my sales analytics app with Salesforce
Three years ago, I became VP of Strategy at Retrospect, and I found we didn’t know enough about our product sales. We used Salesforce, but it didn’t provide the level of granular reporting we needed to analyze how Retrospect was selling according to a number of metrics. So, I wrote my own: Datacube. (Retrospect’s icon has always been a cube.)
Building Datacube
Datacube needed to analyze sales in a number of different ways:
- Revenue: Measuring by revenue is always useful.
- Units: Retrospect varies in price per release and per region based on currency exchange rates, so measuring by units is a useful gauge for comparison.
- Product Edition: Our product SKUs change every product release, and we needed release-agnostic insight into how products were selling.
- Product Level: Retrospect has a desktop version and a number of server versions, so we wanted to separate those levels.
- Product Type: We sell a set of products along with add-ons to those products. Differentiating between those sales is significant.
- Purchase Type: Customers can buy Retrospect new, upgrade Retrospect, or renew their subscription to Retrospect. We wanted to see the individual trends for all of these.
- Time Period: Side-by-side comparisons of arbitrary time periods are essential for understanding product launch sales, as our launches are not at the same time each year.
- Market: We sell Retrospect all over the world, so we needed to see how any edition performed in any market.
- Drilldown: We needed to see the individual sales that made up any given number, both to verify its accuracy and understand anomalies.
Datacube handled all of these. It ingested our sales information from Salesforce, analyzed the sales across the above metrics, and let me compare across time periods. For any interesting data point, I could drill down on it to the individual transactions. In the end, Datacube was three thousand lines of Ruby-on-Rails code, and it helped us understand trends in our business and focus our efforts.
However, building a tool has its trade-offs, and Datacube had many limitations. The most significant was buy-in: Sales was never comfortable with it. While Product Management used Datacube, Sales continued to use various Excel templates, leading to two ground truths. Never good. Finally, our VP of Sales pushed me to look at Salesforce again to see if we could somehow use it to replace Datacube and Excel, so that everyone was on the same page.
Replacing Datacube
When I started building Datacube, I had very little experience with Salesforce. I didn’t realize how easy it was to extend objects with custom fields to add missing metrics or how to leverage Reports to analyze those metrics. Salesforce could do everything I built into Datacube with minimal effort.
Extending Salesforce was straight-forward. I worked with Sales and added those original missing attributes (“Product Level”, “Product Edition”, “Product Type”, “Purchase Type”) to the Salesforce Product object, and I helped Sales set up Territories to identify markets around the world. Drilldown is already available in Reports via “Show Details”.
Comparing time periods was a bit more complicated. One quick solution is to open multiple browser tabs, load various time periods into each, and flip back and forth. To compare years in the same report, I created a custom formula for the year of an opportunity to drop in the column grouping. To compare months in the same report, I used “Close Date” > “Group Dates By” > “Calendar Month in Year”, with the year field as a row grouping. Comparing custom time periods took more effort, but it’s similar to the previous two methods.
Datacube helped us understand our business far more than we had before. Incorporating those metrics into Salesforce meant everyone was working off the same data and analysis.
