Analyics - Query to show current and previous CRM deal stages

Analyics - Query to show current and previous CRM deal stages

Create a query table called Stage History Ordered first with the following query:

select row_number() over(partition by "Deal Name" order by "Modified Time" desc) as 'Row',
"Stage History"."Deal Name" as 'Deal Name',
"Stage History"."Stage" as 'Stage',
"Stage History"."Modified Time" as 'Modified Time'
from "Stage History"

Then create another query table with the following query to return the list of deals with the current and previous stages:

SELECT
"Deals"."Id" as 'Deal ID',
"Deals"."Deal Name" as 'Deal Name',
"Deals"."Account Name" as 'Account ID',
"Accounts"."Account Name",
CS."Stage" as 'Current Stage',
date_format(CS."Modified Time", '%Y-%m-%d') as 'Current Stage Change',
PS."Stage" as 'Previous Stage',
date_format(PS."Modified Time", '%Y-%m-%d') as 'Previous Stage Change'
FROM  "Deals"
left outer join "Accounts" on "Deals"."Account Name" = "Accounts"."Id"
LEFT OUTER JOIN( SELECT *
FROM  "Stage History Ordered"
WHERE "Row"  = 1
) CS ON "Deals"."Id"  = CS."Deal Name"
LEFT OUTER JOIN( SELECT *
FROM  "Stage History Ordered"
WHERE "Row"  = 2
) PS ON "Deals"."Id"  = PS."Deal Name"  

    • Related Articles

    • Updating Deal Field from Deal Contact Role

      To get the Your Contact Role Ids, see here Change the Ids where required. deal = zoho.crm.getRecordById("Deals", dealId); dealContactRoles = zoho.crm.getRelatedRecords("Contact_Roles", "Deals", dealId); mpRoles = map(); for each contact in ...

    • Updating a Zoho CRM Subform (Accounts) from another Module (Deals)

      Updating a Zoho CRM Subform This script (courtesy of Zoho) will update existing records in a subform in one module (Accounts) from another Module (Deals). To create a cross-moduel subform entry (at creation for example), please see here ... ...

    • Zoho CRM for Outlook Add-In keeps disabling

      Why does this happen?  - Microsoft has some security measures in place to prevent slow add-ins from running inside Outlook. The issue is however that in many cases add-ins without fault are mistakenly marked as slow and disabled by Outlook, and if ...

    • Mass Deleting Records In Zoho CRM using the API

      Pre-requisites Using Node.js In project folder install Axios (npm install axios) In project folder, install dotenv to use .env .create .env file .env File Structure CLIENT_ID=your_client_id_here CLIENT_SECRET=your_client_secret_here ...

    • Related List to Show Relevant Data from Other Modules

      //Get Account Details accountMap = zoho.crm.getRecordById("Accounts",accountId); //Get account Variables contactList = zoho.crm.getRelatedRecords("Contacts","Accounts",accountId); // rowVal = 0; responseXML = ""; // if(contactList.size() > 1) { ...