Deal Stage Duration Updater in Zoho CRM
- Jay
- Difficulty: Advanced
- Estimated reading time: 30 mins
Deluge script that calculates the duration of Deals in their respective current Deal Stages.
Core Idea
Zoho Reports has a limitation when it comes to getting the Deal Stage Duration – the system only captures stage duration when a Deal has exited a stage, which means, you can only get the duration of a Deal in its past stages. To get the current Deal stage duration, some Deluge scripting would be necessary. The idea is to get the timestamp of a Deal when it entered its current stage, calculate the duration between the aforementioned timestamp vs today (whenever the function is run), and update the value in a custom field on the Deal record.
Configuration
Before you can use this script with Zoho CRM, you must configure the following:- Create a Zoho CRM Connection with the following scope: ZohoCRM.modules.ALL.
- Create a custom line field in the Deals module called “Current Stage Duration (Days)”.
- Create a schedule for which the custom function will be written in and run. It’s advisable to have this schedule run on a daily basis (before work starts) for up-to-date information.
- Settings > Automation > Schedules > Create New Schedule
Tutorial
Get all Deal Records
To get all Deal records, a Zoho CRM API call is used. Most companies have over 200 Deals in their CRM, but Zoho CRM API has a limit of 200. In order to overcome this, we would simulate awhile loop via pagination. This allows you to get every single Deal record in a list (read more about API pagination here
zohoCrmModule = "Deals";
perPageLimit = 200;
pageIterationList = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}; // This accounts for 5000 Deals (25 x 200). Increase the page number if needed.
allRecords = List();
iterationComplete = false;
for each page in pageIterationList
{
if(iterationComplete == false)
{
response = invokeurl
[
url :"https://www.zohoapis.com/crm/v2/" + zohoCrmModule + "?page=" + page + "&per_page=" + perPageLimit
type :GET
connection:"zohocrm" // Change this to your Connection Name
];
records = response.get("data");
allRecords.addAll(records);
if(records.size() < perPageLimit)
{
iterationComplete = true;
}
}
}
// Check for correctness
info "Number of Records: " + allRecords.size();
{
update2 = zoho.crm.updateRecord("Deals",id,{"Company_Overview":company_overview});
info update2;
}
}
Get the Current Deal Stage, Calculate the Duration, Update the Custom Field
- A
Forloop is used here to iterate through every single Deal record in the list. - To access the Deal Stage information, a Zoho CRM API call is used to get the Stage History (note: “Stage_History” is a related list in Deals).
- To get the current Deal Stage, you must get the first index (
.get(0)) – by default, the latest (current) stage is the first index in the list. - Once the accurate Stage record is accessed,
.get("Last_Modified_Time")will get the time stamp of when the Deal enters its current stage. - Calculate that time stamp vs
todaywith thedays360funciton and voila, you get the duration (in days) of the current Deal Stage. - Update that value in the custom line field you have created.
for each a in allRecords
{
//Get the Deal Stages
response2 = invokeurl
[
url :"https://www.zohoapis.com/crm/v2/Deals/" + a.get("id") + "/Stage_History"
type :GET
connection:"zohocrm"
];
//Calculate the Duration of the Current Deal Stage
duration = days360(response2.get("data").get(0).get("Last_Modified_Time"),today);
//Update field here
update = zoho.crm.updateRecord("Deals",a.get("id"),{"Current_Stage_Duration_Days":duration}); // Change this to your custom field API name if needed.
info update;
}
Use the Custom Field in Zoho Reports
Now, you can use the “Current Stage Duration (Days)” custom field in Zoho Reports for analysis purposes. A good usage of this is for sales people to review dormant Deals.
Click here to copy these scripts. For more Zoho-wizardry, check out our GitHub page.
If you need help implementing Zoho CRM, the Zoho One bundle, or any other Zoho product, we offer free training as well as world-class Zoho consulting services.
If you need help implementing Zoho CRM, the Zoho One bundle, or any other Zoho product, we offer free training as well as world-class Zoho consulting services.
Contact Us!
Book a free 30-minutes consultation with a Zoho expert or send us an email
