Report of the Week - Stage Analysis

Report of the Week - Stage Analysis

Potential stage history analysis helps you track the stage movement of each potential. Tracking the wins and fails helps you avoid repeating the mistakes of the past and define better processes to convert more potentials into wins. 

 

This week, let's see how to track the stage movement of each potential. You can create this over your CRM data with Potential/Opportunity stage history data.  Advanced Analytics for Zoho CRM supports Potential/Opportunity stage history. 


Let's see how to create Stage Analysis reports

  • Create Query Table - Rank 
  • Create Query Table - Stage Modifications
  • Create Query Table -  Stage Running Details
  • Create Stage Analysis Reports
    • Stage Analysis Report
    • Stage Timeline Report

Query Table - Rank


The below query will rank the stages based on what stages each potential moves into, and when.


SELECT
a."Deal Id" "POTENTIALID",
a."Modified Time" "Modified Time",
a."Modified By" "Modified By",
a."Stage" "Stage",
a."Amount" "Amount",
count (b."Deal Id") "Rank"
FROM "Potentials" p
LEFT JOIN "Stage History" a ON p."Id" = a."Deal Id"
JOIN "Stage History" b ON a."Deal Id" = b."Deal Id" AND a."Modified Time" >= b."Modified Time"
GROUP BY 1,
2,
3,
4,
5

Query Table - Stage Modifications

The below query fetches from which stage the potentials move to which stage and the date they move (From and To).  It also calculates the duration of a potential in each stage.

SELECT
a."POTENTIALID" "POTENTIALID",
a."Rank" "Rank",
a."Stage" "From Stage",
b."Stage" "To Stage",
a."Amount" "Amount",
a."Modified Time" "From Time",
if(b."Modified Time" is null , now(), b. "Modified Time" ) as "To Time",
datediff(if(b."Modified Time" is null , now(), b."Modified Time" ), a."Modified Time" ) "Duration" ,
u."Last Name" "Owner Name"
FROM "Rank" a
LEFT JOIN "Rank" b ON a."Rank" = b."Rank" - 1 AND a.POTENTIALID = b."POTENTIALID"

LEFT JOIN "Users" u ON u."Id" = b."Modified By"  

 

Query Table - Stage Running Details


Create a table with an Auto number column as TimeKey. Combine this with the Stage Modifications query table to fetch the dates a potential stays in each stage.


SELECT
a.*,
adddate(a. "From Time" , b."TimeKey" ) as 'Stage Date',
adddate(a. "From Time" , (b."TimeKey" + 1)) as 'Stage Date + 1'
FROM "Stage Modifications" a
JOIN "Auto number" b ON adddate(a."From Time" , b."TimeKey" ) < a."To Time"

Creating Reports

You can create various reports tracking the stage movement of the Potentials based on these query tables. Here, let's see the following reports.

  • Stage Analysis Report
  • Stage Timeline Report

Stage Analysis

Stage Analysis gives you the stage movement of the Potentials and the amount in each stage. Follow the below steps to create this report.

  1. Open the Stage Modifications query table. 
  2. Click New icon and select Pivot View
  3. Drop columns as follows:
    • Row - Add the following columns in the Row .
      • Potential Name with Actual
      • Rank with Actual(D)
      • From Stage with Actual
      • To Stage with Actual
    • Data - Add the Amount   and Duration with Sum in the Data shelf. 



  4. The report will be generated as follows. Hide the totals. 



  5. Sort the data by Rank. 



  6. The final report is created. Apply the Theme you desire.
                  


Stage Timeline Report

The Stage Timeline Report allows you to track the number of potentials in each stage across fixed time periods (week, month, quarter etc). Follow the below steps to create this report. 

  1. Open the Stage Running Details query table. 
  2. Click New icon and select Pivot View.  
  3. Drop columns as follow:
    • Column - Add the Stage Date with Full Date.                                           
    • Row - Add the From Stage with Actual.
    • Data - Add the POTENTIALID with Count.



  4. The report will be generated as follows. Hide the totals. 



  5. Custom sort the From Stages, ranked 1 through 5 (or as many Stages you might have).



  6. The final report is created. Apply the Theme you desire.



Copy the sample workspace from the below url to learn more on this. 





          Zoho Marketing Automation
                  • Sticky Posts

                  • Zoho Analytics | Quarterly Newsletter | Q3 2022

                    Hello Everyone! We hope all of you are safe and well! Zoho Analytics - Q3 Quarterly Newsletter is out! Check out the newsletter (attachment) to know all about our new features, recognitions, webinars, blogs, and many more exciting things.  Do let us know

                  Zoho Pagesense Resources

                    Zoho SalesIQ Resources



                          Zoho TeamInbox Resources

                            Zoho DataPrep Resources



                              Zoho CRM Plus Resources

                                Zoho Books Resources


                                  Zoho Subscriptions Resources

                                    Zoho Desk Resources

                                      Zoho Projects Resources


                                        Zoho Sprints Resources


                                          Qntrl Resources


                                            Zoho Creator Resources


                                              Zoho WorkDrive Resources





                                                  Zoho Campaigns Resources

                                                    Zoho CRM Resources

                                                          Design. Discuss. Deliver.

                                                          Create visually engaging stories with Zoho Show.

                                                          Get Started Now