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.

Open the Stage Modifications query table.
Click New icon and select Pivot View.
Drop columns as follows:

Row - Add the following columns in the Rows shelf.

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.

The report will be generated as follows. Hide the totals.
Sort the data by Rank.
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.

Open the Stage Running Details query table.
Click New icon and select Pivot View.
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.

The report will be generated as follows. Hide the totals.
Custom sort the From Stages, ranked 1 through 5 (or as many Stages you might have).
The final report is created. Apply the Theme you desire.