I can see the time the sales stage changed in the opportunity change log. I need to create a report that shows the sales stage durations of a given opportunity.
Can I make this report with available fields, or do I need to make custom fields to store the sales stage durations based on change log?
If you have the sales stage included as an audit field, I believe a bit of clever SQL could create that report from the audit tables.
Iâm sure Grok can do it
Hello Helani,
depending on what else youâd like to track / report on, I often build history modules for stuff like this.
If the sales stage changes, a workflow writes a copy of your record into a custom opportunity history module (with the fields that youâre interested in).
In this way, itâs fairly simply to create sales forecasts etc.
If your opportunity stages are fairly static and limited, itâs easy to create custom fields like:
stage 1 date
stage 2 date
âŚ
And then just create workflows to populate them on stage change. ie: stage = stage 2, then stage 2 date = today.
Then if you like you calculate stage 1 days = today - stage 1 date. and so on and so forth.
why not use the built-in audit mechanism?
The issue is being able to report on it in the reporting module. Its easiest if you have fields that contain the data you need.
@pgr
Same here with Paul - with an external BI solution, totally fine to use the audit protocol.
For someone in marketing or sales, the internal reporting should work right away.
A separate module worked well in some projects, where a âsnapshotâ of the data was required (maybe the opp amount changed as well over time).
In those cases, the sql query / reports on the audit is getting more complex.
Thank you @BastianHammer
I defined new Datetime fields per Sales Stage in Opportunity module itself for now. I can use a workflow to update these new fields âon saveâ Opportunity record if the Sales Stage is changed.
But I cannot figure out how I can update already existing Opportunity records with past âChanged onâ dates of each Sales Stage?
@Helani you wonât be able to go back in time. The workflow suggested captures the data at the time the status changes. If you didnât capture it in the past, its not available.
If youâre lucky enough to have sales stage marked as an âauditâ field, there may be some info in the audit log for field changes that you use to re-build a past history. Again, only if sales stage is an âaudit fieldâ will you have any kind of past data.
@pstevens yes, Sales Stage is an âAudit fieldâ, and I can see the Sales Stage changed on date records in the Opportunity âChange logâ.
I see, I cannot set past data using a workflow. Do you know how to get this âChange logâ/audit data programmatically?
You can download the audit table from PHPmyAdmin and parse it based on your needs for the specific field. Youâll have to do a whole bunch of data manipulation to re-construct and reupload a CSV with these fields populated.
Pauls approach is very sound and would be my first bet as well.
If you are looking at more analytics later on with sales forecasts etc. from the history, you might want to look into Metabase for instance.
Then you can probably skip phpMyAdmin + spreadsheets and build your reports as you need them for the import of the historical data.
Thank you @pgr and @BastianHammer. It looks like using Metabase is more efficient to analyse past data.
On the workflow side, I have few issues. I created one workflow per Sales Stage as follows:
This triggers both âold-sales-stageâ workflow and ânew-sales-stageâ workflow, setting the same time in both fields. E.g. If sales stage is changed from âQualificationâ to âEvaluationâ, datetime.now is set in both Qualification Date and Evaluation Date.
Pls explain, have I got the conditions wrong?
Hello Helani,
you basically need only one of the triggers.
Either youâd go with one workflow that always is triggered on âSales Stageâ = Any Change. (usually what Iâd do)
Or youâd go with multiple workflows (only required if youâre setting specific details / calculating sth. depending on the stage) and only use the specific condition: "Sales Stage = âEvaluationâ.
âRepeated Runsâ is a risky option.
Youâd usually not need it, unless your team sometimes switches the sales stage to the next stage, then forgot sth. and switches back AND youâd like to track this as well.
Instead of Modify Record, youâd create a record in your custom modules (e. g. Opportunity History).
Iâve got a detailed video workshop recorded about that, but itâs for members only, if youâre interested:
Well⌠Just want to point out that there are other possibilities. It would be pretty straightforward to create such a report, assuming as you mentioned that status is an audited field, with vReports. Realtime, not based on workflows that may have skipped a beat.
If you are interested:
vReports | SuiteCRM Module
We are thinking about creating a free runtime version. This would mean that you could ask someone with the full vReports version to build such a report for you. May happen before the end of the yearâŚ
