March 18 2017
Tracking Retrospect's Releases with Salesforce
In “Datacube: Replacing my sales analytics app with Salesforce”, I discussed using custom date ranges over multiple years to compare different product releases in one Salesforce Report.
At Retrospect, we wanted a report for tracking the current release with a comparison to previous releases, while hiding other sales data from outside those relative ranges. As our release schedule varies, we couldn’t just track a particular month’s sales compared to the previous years; we needed to compare the March 17, 2015 release, the March 1, 2016 release, and the March 7, 2017 release and only view the first N days of each release. Moreover, we wanted to leverage the rest of the custom attributes we created for deep sales analysis, like product edition, configuration, or fulfillment type.
Let’s walk through exactly how to set up the report. We need to add two Opportunity fields and then use them in a report.
Release Name
The first field is a text formula for the name of the release. Our most recent one was “2017 - Win 12, Mac 14”. This field will be used in header of the report. The formula is structured so that any opportunity that falls within a given date range is assigned the correct release name to group it; any opportunities outside all of the dates is assigned a blank. See below:
Days Since Release
The second attribute is a number formula for the number of days since a release. This field will be used in the filters for the report. The logic is the same as above, but instead of returning text, we return “CloseDate - DATE(YYYY,MM,DD)”. Like above, if the opportunity falls within the date range, the relative date from the release is returned, and otherwise, we return a very large number.
Salesforce Report
Now we put those attributes together. Create an Opportunity Report with a “Matrix” format with no details. Drop “Release Name” into the header, and add “Days Since Release” with a number for the day as a filter. You can then use any standard or custom attribute on the left side to slice up the resulting data. Below is a screenshot of the filters and headers for our report:
