Pipeline Insights - Historical Snapshots for Trend Analytics

[rachfahey]

Author:

Rachael Fahey

Role:

Analytics Lead

Company:

Juniper Networks, Inc.

Collaborators:

Brian Calvanese, Shshank Chawathay, Amanda Baranauskas, Leonard Bickoff

Overview of Use Case

After adopting Alteryx in 2016, Juniper created direct connections and a semantic layer to leverage Certified Data Sources to enable more advanced analytics.  We automated historical SFDC snapshots needed to provide executives and sales leaders more insight into our sales pipeline.  These historical snapshots were easily blended with our other certified data sets to create analytics and lay the foundation for predictive analytics in the near future.

Describe the business challenge or problem you needed to solve

Sales is the lifeline for any company.  Juniper needed a way to tap into historical CRM data to gauge the health of our business.

In 2016, we began using Alteryx and the SFDC connector tool to automagically snapshot detailed opportunity data from Salesforce.com every week.  This allowed us to start building the historical database required for year-over-year and trend analytics.

Key business questions we needed to answer:

• Do we have enough pipe to deliver forecast and plan?
• How is future pipeline building? Is that better or worse than previous quarters?
• Overall, is our pipeline growing or declining?

Like most companies, Juniper redefines our Sales Territories every year, making apples-to-apples comparisons using any sort of offline data a real challenge.

Describe your working solution

After adopting Alteryx in 2016, our team created direct connections and a semantic layer to leverage Certified Data Sources to enable more advanced analytics.  For this pipeline project we used the direct connector from Alteryx to SFDC to automate the historical snapshots needed to provide executives and sales leaders more insight into our sales pipeline.  These historical snapshots were easily blended with our other certified data sets to create views and analytics around trends and coverage multiples that were never available at Juniper. 

• Weekly Snapshots from SFDC
• We used the Direct Connect Salesforce Input tool to combine multiple tables within SFDC to create our own Historical SFDC Certified Data Source.
• We found the info tool and the dynamic rename tool invaluable to easily parse through metadata and translate machine names into common reporting names.
• Joins were performed between SFDC tables to enrich opportunity header-level info with transactional detail and additional dimensions.
• Alteryx’ ability to pass SOQL through to the Salesforce.com platform allowed us to pre-process filters in-database to more effectively extract data.

Direct Connect SFDC, SOQL, Info, Dynamic Rename to join multiple SFDC tables:

• Historical Transaction Reprocessing
• Every year, sales territories are redefined according to new organizational structures, rendering comparisons between current data and offline snapshots all but useless.
• To re-process our offline snapshots, we used Alteryx to connect directly to Anaplan to extract thousands of sales representatives’ assignment rules.
• Using a nested macro, we applied all the rules to every snapshot to re-assign all territories and sales organization’s so that we could have apple-to-apple comparisons (effectively creating a conditional cross-join tool to make over 20 million comparisons across 6 different criteria!!).
• This effort also had the side benefit of recreating an offline version of our SAP CRM assignment engine, which is now being used to test new territory rules before they are implemented into production.

• Data Blending
• To compare the current pipeline data to the historical snapshots, Plan and Forecast data from our Hyperion financial system was unioined into the historical pipeline snapshots to calculate normalized coverage ratios.
• The same coverage ratios were developed for historical periods by comparing prior snapshots against actual results, which were also unioined into the data sets.
• All of these data sources are processed from their raw connections through our ETL layer to normalize all naming convention (field names and values) to make further analysis possible.

Blending it all together: SAP, SFDC, Hyperion

• Tableau
  • All of the prepared data above is output to a TDE and the Tableau Workbook is automatically refreshed every week.

Describe the benefits you have achieved

Data coming from Salesforce.com, SAP, Hyperion and Anaplan come together using Alteryx to create a TDE for Tableau Server.  The Tableau workbook houses metrics that answer the key business questions for executives.  These metrics are now used by the Executive Operating Committee, CEO Staff and Sales Leadership.

• Do we have enough pipe to deliver forecast and plan?
  • Current Quarter Coverage multiples show how our current sales and pipeline are trending compared to historical quarters, to give us more confidence during the sales forecasting process.
    
    Sample data and visualization for illustrative purposes only.

• How is future pipeline building? Is that better or worse than previous quarters?
  • Next Quarter coverage multiples show how future quarters are building compared to historical quarters as well. This has expanded our forecasting process beyond the current quarter to give us insight deeper into the future.

 
Sample data and visualization for illustrative purposes only.

Overall, is our pipeline growing or declining?

• Pipeline Trending views show us whether longer term views of our sales funnel are growing or declining, and whether or not our mix of sales stages is healthy compared to history.

Sample data and visualization for illustrative purposes only.

This mostly hands off workflow of capturing a current Salesforce.com snapshot, adding the snapshot to the snapshot history, being able to reprocess history and then add in plan, forecast and actual results, has never been done before at Juniper.  This historical trend data will also be used as the foundation for predictive analytics in the very near future.