In the realm of CRM management, the quest for historic pipeline data is often met with reliance on periodic snapshots meticulously created by seasoned administrators. However, encountering a CRM system devoid of such snapshots presents a unique challenge, as was the case with an acquired company’s CRM system that crossed my path recently.
In the absence of snapshots, I found myself innovating a method to extract historical pipeline insights from the existing CRM data, exclusively relying on closed won or closed lost opportunity records.
This method hinges upon adherence to fundamental sales policies. Firstly, it necessitates a system configuration where users are barred from deleting opportunities, ensuring a perpetual record of each opportunity’s lifecycle. Moreover, it mandates that open opportunities cannot linger with past close dates; they must either be marked closed-lost or have their close dates adjusted to the future. Lastly, every opportunity must be assigned a tangible value.
Operating within these parameters, one can derive past pipelines through judicious filtering of an archive of closed opportunities, considering the opportunity amount, creation date, and closure date. Given the typical sales cycle of 3 to 5 months, it’s common to encounter the same opportunity appearing in multiple quarters.
Here’s a breakdown of the method:
- Filter for all opportunities created before the end date of the desired period.
- Filter for all opportunities with close dates after the start of the period.
- Sum the amounts of qualifying opportunities to ascertain the pipeline for the period.
- Repeat this process for each period of interest.
I used the SUMIFS function in Excel to accomplish steps 1 through 3 with named data ranges for amount, created date and close date. Here is that formula with variables set for the first quarter of 2023:
=SUMIFS(‘SFDC Data.xlsx’!Amount,’SFDC Data.xlsx’!Created_Date,”<4/1/2023”,’SFDC Data.xlsx’!Close_Date,”>=1/1/2023”)
While the results yielded close rates well within the industry norm of 20% to 30% for SaaS companies, it’s imperative to acknowledge the inherent imperfections. Opportunity amounts can fluctuate over time, and the figures considered here are the “final” amounts. Additionally, the final close dates of closed-lost opportunities often experience delays as representatives endeavor to salvage potential success.
The question then arises: Is this method the optimal alternative to traditional snapshot reports for deriving historic pipeline data? Surprisingly, my exploration into existing literature via Google yielded no similar approaches.
I invite you to share your insights. Have you encountered or devised alternative methods for estimating historic pipeline data? Your feedback and experiences could enrich this ongoing conversation in CRM analytics. Let’s delve deeper together.
Cheers!
Bob Bacon