Zoho CRM Solution Queries

This page contains queries that are used in Solution for Zoho CRM Advanced Analytics

Customer Retention

Customer Retention reports let you know how better you are engaging with your existing customers and make then continue buying products or services. The following query will help you in creating Customer Retention report. 

Customer Start Date

This Query Table obtains information on the date of subscription for each customer.

SELECT
"Customer ID",
STR_TO_DATE(min("Date of Subscription"), '%Y-%m-%d') "Date" /* fetches the start date from subscription created time */
FROM  "Invoice" 
GROUP BY  "Customer ID" 

Invoice and Recurring Month

This Query Table obtains information on the month and Year of the customer's subscription, along with Invoice details.

SELECT
INV."Invoice ID" "Invoice ID",
INV."Date of Subscription" "Date",
INV."Customer ID" "Customer ID",
INV."Invoice Owner" "Invoice Owner",
STR_TO_DATE(CUS."Date", '%Y-%m-%d') 'Min Date',
date_format(CUS."Date", '%Y%m') "Month and Year", /*Extracts the year and month */
period_diff(date_format(INV."Date of Subscription", '%Y%m'), date_format(CUS."Date", '%Y%m')) 'Month'  /*Finds the duration in months */
FROM  "Customer Start Date" CUS
INNER JOIN "Invoice" INV ON INV."Customer ID"  = CUS."Customer ID"  

Customer Count by Month

This Query Table obtains the month-wise Customer Count. 

SELECT
date_format("Date", '%Y%m') "Month and Year",
count("Customer ID") "Customer Count"
FROM  "Customer Start Date" 
GROUP BY  "Month and Year" 

Teams' Performance

Teams' Performance report depicts the performance of each Sales Representative. The following queries will help you to create Teams' Performance report. 

Customer Start Date

This Query Table obtains information on the date of subscription for each customer.

SELECT
"Customer ID",
STR_TO_DATE(min("Date of Subscription"), '%Y-%m-%d') "Date"
FROM  "Invoice" 
GROUP BY  "Customer ID" 

Invoice and Recurring Month

This Query Table obtains information on the month and Year of the customer's subscription, along with Invoice details.

SELECT
INV."Invoice ID" "Invoice ID",
INV."Date of Subscription" "Date",
INV."Customer ID" "Customer ID",
INV."Invoice Owner" "Invoice Owner",
STR_TO_DATE(CUS."Date", '%Y-%m-%d') 'Min Date',
date_format(CUS."Date", '%Y%m') "Month and Year", /*Extracts the year and month */
period_diff(date_format(INV."Date of Subscription", '%Y%m'), date_format(CUS."Date", '%Y%m')) 'Month' /*Finds the duration in months */
FROM  "Customer Start Date" CUS
INNER JOIN "Invoice" INV ON INV."Customer ID" = CUS."Customer ID"  

Invoice Count by Owner

This Query Table obtains information on the number of customers assigned to each Sales Representative.

SELECT
"Invoice Owner",
COUNT("Invoice ID") "Invoice Count"
FROM  "Invoice" 
GROUP BY  "Invoice Owner" 

Leads Conversion

Lead tracking

This Query Table obtains information on all details needed for tracking the leads such as created time, nature of conversion, etc.,

SELECT
*,
CONCAT(LEFT("month_Created Time1", 3), ' ', YEAR("Created Time")) "Month & Year",
CONCAT('Month - ', period_diff(date_format("Last Modified Time", '%Y%m'), date_format("Created Time", '%Y%m'))) "Month Diff"
FROM  "Leads" 

Lead Count - Month & Year wise

This Query Table obtains information on the number of leads generated for each of the months.

SELECT
"Month & Year",
COUNT("LEADID")
FROM  "Lead tracking" 
GROUP BY  "Month & Year" 

Potential Conversion

Potential Count by Month

This Query Table obtains information on the number of potentials generated for each of the months. 

SELECT
date_format("Created Time", '%Y%m') "Month and Year",
COUNT("POTENTIALID") "Total Potential"
FROM  "Potentials" 
GROUP BY  1 

Potential Conversion by Month

This Query Table obtains information on month wise the conversion of the potentials.

SELECT
p."POTENTIALID" "POTENTIALID",
date_format(p."Created Time", '%Y%m') "Month and Year",
period_diff(date_format(pst."Modified Time", '%Y%m'), date_format(p."Created Time", '%Y%m')) "Month"
FROM  "Potentials" p
LEFT JOIN( SELECT
"POTENTIALID",
max("Modified Time") "Modified Time"
FROM  "Potential Stage History" 
GROUP BY  1 
) pst ON p."POTENTIALID"  = pst."POTENTIALID"  
WHERE p."Stage"  = 'Closed Won'