The goal of this documentation is to provide you with an overview of the Pipeline Management tools from the Sales Analytics team.
Pipeline is the sum of all open sales opportunities and is one of the key metrics that helps us to consistently hit our targets. It is one of the leading indicators of the health of our business and it informs us on our sales motion dynamics.
Pipeline coverage is a ratio between our pipeline and our business target. We track pipeline coverage at different stages of the sales funnel for current and future quarters. We calculate historical pipeline coverages (to actuals) and use them as a baseline to compare with our current (and future) quarters coverage. That way, we can answer the question "Do we have enough pipeline?" with confidence.
Our standard pipeline coverage calculation is defined as
Open Pipe Net ARR
/ Remainder to Target amount (to date in a given quarter)
and Remainder to Target is defined as
Target Net ARR
- Booked Net ARR (to date in a given quarter)
.
For future quarters, the calculation would be
Open Pipe Net ARR
/ Target Net ARR
as there won’t be any Booked Net ARR yet.
Unless otherwise stated, Pipeline coverage is calculated with the definition above.
We also calculate pipeline coverage against actuals to calculate historical coverage values.
Pipeline coverage against actuals is defined as
Open Pipe Net ARR with Stage X+ at day Y
/ (Total Booked Net ARR
- Historical Booked Net ARR at day Y
)
The inverse of historical pipeline coverage against actuals is the historical win rate at that point in time.
Purpose | Name | Contents | Link |
---|---|---|---|
Provides a high-level overview of quarterly metrics | SS&A - Pipeline Velocity & Generation |
|
|
Provides deep dives and detailed view of selected metrics | SS&A - Pipeline Coverage Deep Dive |
|
SS&A - Pipeline Coverage Deep Dive |
SS&A - Pipeline Generation Deep Dive |
|
SS&A - Pipeline Generation Deep Dive | |
SS&A - Pipeline Movement Tracker |
|
SS&A - Pipeline Movement Tracker | |
Provides operational insights and action items on current and future quarters | Pipeline X-Ray: Current & Next Quarters Overview |
|
GTM Analytics Hub Please find the link under Net ARR/Pipeline section |
SAE Heatmap |
|
GTM Analytics Hub Please find the link under Net ARR/Pipeline section |
To define a baseline of how "Good Enough Pipeline" looks like, we use calculated historical coverage curves of pipeline vs actuals.
Those curves, compare pipeline at that point in time (from the historical date perspective) vs the total bookings of that quarters (instead of the target). That way, we have a historical representation of how much pipeline was needed on that quarter to close its actual bookings. One interesting fact is that the inverse of the historical pipeline coverage vs actuals is our "Win Rate".
Pipeline Coverage against Actual (for historical quarters):
Open Pipe Net ARR with Stage X+ at day Y
/ (Total Booked Net ARR
- Historical Booked Net ARR at day Y
)
Win Rate (inverse of Pipeline Coverage against Actual):
(Total Booked Net ARR
- Historical Booked Net ARR at day Y
) / Open Pipe Net ARR with Stage X+ at day Y
Comparing our current quarter Pipeline Coverage against target vs historical Pipeline Coverage against actuals (at the same day of the quarter) allows us to understand if we do have enough pipeline to close our Net ARR target (based on the assumption that our sales motion and win rate are consistent).
As every past quarter has variability on their win rates, we use the last 4 closed quarters as guidaline.
To reduce quarter-specific variation, a polynomial curve is fitted using the last 4 fiscal quarters of Pipeline Coverage against Actuals data.
Opportunity Snapshot table is used for calculating historical coverages. The fitted curve serves as a benchmark in tools like Pipeline X-Ray: Current & Next Quarters Overview
and others. Maintaining Pipeline Coverage above the fitted line indicates a higher likelihood of hitting the sales target.
Check out Coverage Historical Curves (at the bottom of the Deep Dive dashboard)
CRO (Overview)
, Enterprise
, Commercial per Order Type
etc.3.7
for the current quarter (Stage 3+). And the average coverage for the past 4 quarters was 3.6
2.5
for CQ+1 (Stage 1+). However, the coverage for the past 4 quarters was 3.5
We iterate on the definition of Sales Accepted Opportunities (SAOs), Pipeline Generation, and Created ARRs. If you would like to understand the latest version of them, please reach out to the SS&A team.
How can I have access to the dashboard?
Please refer to Permission Handling section below
Readme
tab in Pipeline X-Ray tool for links for the related SFDC reportsNormalized Close Day of Fiscal Quarter
would be a negative value for those opportunities.
90 - DATEDIFF(DAY, sfdc_opportunity.snapshot_date, close_date_detail.last_day_of_fiscal_quarter) AS close_day_of_fiscal_quarter_normalised
2022-06-09
we look at coverage for "FY23-Q4" and opportunity_a
has
2022-11-24
(FY23-Q4)2023-01-31
.2022-06-09
,Normalized Close Day of Fiscal Quarter
would be: 90 - (difference in day between 2023-01-31 and 2022-06-09) = 90 - 236 = -146
The goal of that calculation is to allow us to compare end of quarter (and days before it) consistently across quarters.
I would like to see actual data models behind the charts. How can I do that? Please refer to Data Models section below.
I would like to request other types of visualizations regarding Pipeline - what should I do? Please create an issue in SS&A project and assign it to @nfiguera and @j_kim.
A set of custom data models were created in the Snowflake sales_workspace
to simplify reporting efforts on Sisense / gSheet. When possible the metrics logic is embedded in the source tables rather than in the report models and looking at the SQL code is the best way to be sure that the metrics are presenting what it is expected from them.
The following list of models (with dbt Docs links) are in use for creating Pipeline Management dashboards:
Model | Summary |
---|---|
restricted_safe_workspace_sales.sfdc_opportunity_xf | Legacy model with extra metrics definitions and helper fields |
Restricted_safe_workspace_sales.sfdc_opportunity_snapshot_history_xf | Legacy model with extra metrics definitions and helper fields |
restricted_safe_workspace_sales.report_pipeline_velocity_quarter_with_targets | Aggregated table containing open, total, target, churned Net ARRs with key segment and other various aggregation keys |
restricted_safe_workspace_sales.report_pipeline_metrics_day_with_targets | Metrics Fiscal Quarter / Day, the model includes targets to simplify the calculation of coverages at different levels |
restricted_safe_workspace_sales.report_pipeline_movement_daily | Model with pipeline related metrics per Opportunity id (daily) |
restricted_safe_workspace_sales.report_pipeline_movement_quarter | Model with pipeline related metrics per Opportunity id (quarterly) |
restricted_safe_workspace_sales.ssa_coverage_fitted_curves (model created from Jupyter Notebook export) |
Model containing historical pipeline coverage (fitted curves) |
workspace_sales.date_details | Helper table containing different date fields |
restricted_safe_workspace_sales.report_agg_demo_sqs_ot_keys | Helper table containing different keys that can be used for aggregation |
(EDM not sales_workspace ) restricted_safe_common_mart_sales.mart_sales_funnel_target |
Model with sales targets |
For some tools, we have introduced another layer of transformation in Python on top of dbt models to overcome limitations from SQL. Sisense dashboards are used for this purpose at the moment and tables are exported to Google Sheet based tools.
Each Google Sheet based dashboard has a source
Google sheet pair which contains data and calculations on top of the presentation layer of the dashboard. Data is exported from Sisense to the source Google sheet, using csv url provided from Sisense.
To comply with the SAFE Framework, please submit an Access Request to the Sisense SAFE Environment. Also for non-Sisense dashboards (e.g. Pipeline X-Ray), access is automatically granted for those to have access to SAFE dashboards in Sisense.