Deal Stage Duration Updater in Zoho CRM

Difficulty: Beginner
Estimated reading time: 15 mins
Estimated implementation time: 1 hour

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 a while 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;
	}
}
				
			

Want to learn even more? Sign up for one of our Zoho courses!

Get the Current Deal Stage, Calculate the Duration, Update the Custom Field

  • For loop 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 today with the days360 funciton 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.

More Free Resources

Deluge Tips – Null Check

When scripting, ask yourself this question – will this value ever be null? If it’s a yes, that’s a place for a null check! Here are 3 tips and best practices to help you kickstart the habit.

Read More »