Summary: 
If your organization is like most businesses, even the most seasoned Sales or Sales Ops professional may not know everything about all the nooks and crannies in your opportunity, contracts, and/or subscription data. For Customer Success professionals? Forget about it. No matter how you filter the data, it can still be tough for CSMs to parse what products/solutions a customer owns, how much ARR is attributed to each line item, and the start/end dates for each one.  

If your Opportunity/Contract/Subscription data is already perfectly streamlined in your CRM, congratulations! Must be nice . If not, please read one to read how we put together a high-level, user-friendly view of customer subscriptions by transforming our complex Salesforce Subscription object data into a more digestible format for CSMs. 

I’m probably stating the obvious, but please keep in mind your data will look different than ours, so hopefully the example I provide here can at least get you started on your journey to provide simplified data for your org. 
 

Data Setup: 

Let’s start with an example customer and then work backwards from there:  

Here is a concrete example of how our Data Design helps our end users. This customer's active subscriptions look like so. I had to redact some information, but notice how the “Active Subscriptions” show many line items with a null ARR and shows multiple line items for a single product code.

Here is what the consolidated list of subscriptions looks like. We put this in a prominent spot on the C360. Note how we sum the ARR for Product Codes and specifically call out revenue tied to support-related subscriptions, which helps CSMs see how much ARR is tied to a customer’s support package.

Both of these reports add up to exactly the same ARR, but the aggregated Data Design gives a much more easy-to-consume, high-level look at the data. 

How we Built the Data Design:

This goes without saying, but deep discovery with the relevant Revenue Ops stakeholders took place before any solutioning or building was done. We needed to make sure we understood all the nuances of the subscription data ourselves so that we could transform that information into the easy-to-consume report displayed above. 

To filter out the numerous null ARR line items in the SFDC source data, we had to do three key things: 

• Filter out most “child” products with a few business-specific exceptions 
• Isolate “support”-related subscriptions and revenue 
• Treat one of our product lines differently 

Without going into the weeds too much, we had to pull three different queries from our subscription object, then do a series of transformations and Unions. We also summed ARR for any products that had the same product code and subscription end date.

Because we're dealing with the same object for all three starting queries (Subscription), this is a perfect use case for using Union to match the data together at various points of this DD. Using a “merge” would not get us what we want because it would duplicate all our key fields (ARR, Subscription End Date, etc.) instead of merging them together as one field. 

The Transformations and Unions you see above are specific to our business and thus are not worth delving into much, but if anyone has specific questions or wants to dive into the weeds, please reach out. 

Final step:

• Query Company object – This is a pretty standard query. We just pull in all active Company records and a handful of relevant fields. We can always easily add more fields if necessary. 
• Merge! - Merge on the SFDC Account ID, and voila – we have our dataset.