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 Reports, 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.

sales-funnel-query-table

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).

sales-funnel-report

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

  1. Angela

    Any help?

  2. Angela

    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?

  3. arvindnatarajan

    Fred: Thanks for asking. Here are the steps.1. Open ‘Potentials’ table in Zoho Reports.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.

  4. revisionenergy

    I would love to get this report in my own Zoho Reports, but can’t follow the instructions here to replicate the database in my install. Can you post more thorough instructions?Thanks!
    Fred