Report of the Week - Leads to Deals Conversion Cohort

The goal of any business is to sell more. For this to happen, you try to convert more valid leads into potential customers. Lead Conversion Cohort helps you identify how effective your company is at converting leads into customers. It also tells you how healthy your leads are.

This week, we'll see how to create a Leads Conversion Cohort over your CRM data. We've used Advanced Analytics for Zoho CRM data in this solution. You can modify this to suit your data structure.

Lead Conversion Percent Report using Formula Column
Lead Conversion Cohort Analysis using Query Tables

Lead Conversion Percent using Formula Column

Zoho Analytics allows you to create a report on leads conversion percentage using a simple Formula Column.

Create the following formula column over the Leads table. This identifies whether a lead is converted or not and then groups them based on how long it takes to get converted. Please note that this period is assumed based on the time difference between leads created and last modified time.

if("CONVERTED" = 'Yes', CONCAT('Month - ', month_diff("Modified Time", "Created Time")), 'Not Converted')

Now create the lead conversion report as stated below.

Open Pivot Designer over the Leads table.
Add the columns as follows.

Columns: Converted Period formula column with Actual.
Rows: Created Time with Month & Year .
Data: LEADID with Count > % of Row.

The pivot will be generated as shown below. Hide the total.
Apply Theme.
You can apply Conditional Formatting to highlight the conversion trend. Lead conversion rates vary across industries. Specify the conditions as per your industry's standard.

Lead Conversion Percent report is ready.

Lead Conversion Cohort using Query Tables

Using Query Tables you can get a complete Lead Conversion Cohort. This gets you the total leads generated each month and then calculates the percentage of leads converted.

Follow the below steps to create this report.

Query Table: Leads Tracking
Query Table: Leads Count - Month & Year wise
Join Query Tables
Creating Leads Conversion Cohort

Query Table: Leads Tracking

The following query identifies whether a lead is converted or not and then groups them based on how long it takes to get converted. This also groups leads by month and year to calculate the total leads generated for each month.

SELECT

/*Gets all columns form Leads table*/

CONCAT(LEFT(MONTH_NAME("Created Time"), 3), ' ', YEAR("Created Time")) "Month & Year",

/*Gets the month and year of created time as a group to calculate the leads for each month*/

if("CONVERTED" = 'Yes', CONCAT('Month - ', period_diff(date_format("Modified Time", '%Y%m'), date_format("Created Time", '%Y%m'))), 'Not Converted') "Month Diff"

FROM "Leads"

/*Identifies whether a lead is converted or not and groups them based on how long it takes to get converted. This period is assumed based on the time difference between lead created and last modified time. */

Query Table: Leads Count - Month & Year wise

The following query gets total leads created for each month based on the month and year column in the previous query.

SELECT

"Month & Year",

COUNT("LeadId")

FROM "Leads Tracking"

GROUP BY "Month & Year"

Join Query Tables

Now join the Month&Year columns of both the query tables using lookup column to combine data.

Creating Leads Conversion Cohort

You can create this report over any of the above created query tables. Follow the below steps to do so.

Open Pivot Designer.
Add the columns as follows.

Columns: Month Diff from Leads Tracking query table with Actual.
Rows: Created Time from Leads Tracking query table with Month & Year and Count(Lead) from Month & Year wise Lead Count with Actual Dimension.
Data: LEADID from Leads Tracking query table with Count > % of Row.

Generate the report using Click Here to Generate Pivot.
Click Show/Hide and hide the totals.
You can choose to hide the Not Converted Column by clicking Show/Hide > Columns.
Double-click the column header to rename it.
Apply Theme.
Apply Conditional Formatting.