Hello,
I would like assistance configuring a KPI in Zoho Analytics titled:
Objective of the calculation:
Number of Won Deals
divided by
Total number of assigned prospects (not only converted prospects).
Important clarification:
The denominator must include all assigned prospects, including those that were never converted into opportunities. The goal is to measure a true sales conversion rate from prospect to paying client.
Business definition details:
Won Deals include deals that progressed from free trial to paid stage.
Free trial periods must be included in the overall logic (i.e., prospects who entered a trial but did not convert must still be counted in the denominator).
The metric must remain filterable by month, marketing campaign (UTM), and sales representative.
Tables involved in the calculation:
Prospects table (assigned prospects, status, creation date, owner, etc.)
Opportunities table (stage, won deals, free trial period, paid period)
Relationship: Opportunities → Prospects via the "Converted From" field
Additional related tables used for filtering (e.g., marketing campaigns via UTM parameters)
Issue encountered:
When creating an aggregate formula associated with the Opportunities table, Zoho does not allow me to reference COUNT(DISTINCT "Prospects"."Id"), even though a relationship exists between the tables.
My questions:
What is the recommended way to calculate Won Deals / All Assigned Prospects in Zoho Analytics?
Should this KPI be created from a pivot/report based on the Prospects table instead of Opportunities?
What is the best practice to avoid aggregation errors when calculating metrics across related tables?
I want to ensure that:
All prospects are included (converted and non-converted)
Free trial periods are properly considered
The calculation remains dynamically filter