When we published the Gain More Insight Into Sales With 3 Types of CRM Reports post, CRM user Wendy Wright wanted to know, “What % of leads were closed/won in the last month?” (i.e., how to know the total number of leads got, to those that finally got converted to sales)
This is where the Advanced Analytics Add-on for Zoho CRM, powered by Zoho Analytics, comes in. It helps CRM users to slice and dice data, and get actionable insights.
Coming to Wendy’s question, what we first do is create an SQL query table, which combines the Leads and Potentials modules data. What the below query table does is it gets all the records from the Leads table, uses the UNIONALL SQL function to combine it first with total potentials, followed by won potentials.
And then we create the Sales Funnel Report, by drag-and-drop of the appropriate columns.
The above Sales Funnel Data query table and the Sales Funnel Report are part of the CRM demo database (they are under the Sales Reports folder).
You too can create a sales funnel report or any other ad hoc report you may need, using advanced analytics. Trying the Zoho CRM Advanced Analytics Add-on is simple. Login to Zoho CRM, and visit any page under the Reports or Dashboards tabs. Click the Configure Advanced CRM Analytics link at the top-right and go from there (note that Advanced Analytics can be configured by any CRM ‘Administrator’).
Related Links
Can anyone help me, How to remove the percentage(%) of data from the funnel.
The Query should be like below Select “LEADID”, “Created Time”, ‘Leads’ as “Type” From “Leads” UNION ALL Select “LEADID”, “Created Time”, ‘Potentials’ as “Type” From “Potentials” where “LEADID” IS NOT NULL UNION ALL Select “LEADID”, “Created Time”, ‘Potentials Won’ as “Type” From “Potentials” where “Stage” = ‘Closed Won’
Any help?
What if I want to include "Lead Owner", "Project Owner" and Lead "Status" as filterable fields? How would I add that to the SQL code?
Fred: Thanks for asking. Here are the steps.1. Open 'Potentials' table in Zoho Analytics.2. Create 'New Query Table' and give it a name like 'Sales Funnel Data'. The query is as follows - Select "LEADID", "Created Time", 'Leads' as "Type" From "Leads"
UNION ALL
Select "LEADID", "Created Time", 'Potentials' as "Potentials" From "Potentials" where "LEADID" IS NOT NULL
UNION ALL
Select "LEADID", "Created Time", 'Potentials Won' as "Potentials Won" From "Potentials" where "Stage" = 'Closed Won'3. Create 'New Chart View' on top of the query table and save it as 'Sales Funnel Report'. (Drop 'Type' in X-Axis and 'Lead ID' (Count) in Y-Axis. Change the chart type to Funnel. Add 'User Filter' according to your needs (Last Month, This Month, Last Quarter, This Quarter etc)Hope this helps. Let us know how this goes.
I would love to get this report in my own Zoho Analytics, but can't follow the instructions here to replicate the database in my install. Can you post more thorough instructions?Thanks!
Fred