Explore recipe: Leads aging

Zendesk is removing the Sell datasets and prebuilt dashboard from Explore on January 16, 2024. For more information, see Announcing the removal of Sell datasets and dashboards in Explore.

Note: Sell customers who also have a Suite Professional plan or higher may also create this report.

In this Explore recipe, you’ll learn how to report on your leads in Zendesk Sell, specifically their leads age. This will help you identify which leads might’ve gone cold and which to circle back on. You’ll create a table to show each lead, with columns for the owner, creation date, status, and age.

To make it easier to spot which leads are aging most, you’ll apply conditional formatting to color code leads by age from newest (green) to oldest (red).

This article contains the following topics:

• What you’ll need
• Creating the report

What you’ll need

Skill level: Advanced

Time required: 20 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving agents access to Explore)
• Sales data in Zendesk Sell

Creating the report

1. In Explore, click the reports () icon.
2. In the Reports library, click New report.
3. On the Select a dataset page, click Sell > Sell - Sales, then click Start report. The report builder opens.
4. To create a calculated metric for lead aging, go to the Calculations () menu and select Standard calculated metric.
5. Name the metric Lead age.
6. In the Formula field, enter the following:

   DATE_DIFF(today(),[Time - Lead created - Date],"nb_of_days")

   Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
7. In the Metrics panel, click Add.
8. From the list, select Leads and Lead age (the metric you just created), and then click Apply.
9. In the Rows panel, click Add.
10. From the list, select the following attributes, and then click Apply:
    
    • Lead name
    • Lead owner
    • Time - Lead created - Date
    • Lead status
11. To filter out leads with no recorded name, click the Lead name attribute on the left, click the Excluded tab, select NULL, and click Apply.
12. To sort the results by lead age, click the Lead age column header.
13. To add color to the Lead age column based on how old the deal is, click Chart configuration () and select Display format.
14. Next to SUM(Lead age), select Advanced, specify the conditional formatting you want, and click Apply. You can use the following example as a starting point:

IF (SUM(Lead age) > 365) THEN
    {
        "backgroundColor": "#8b0000",
        "precision": 0,
        "scale": 1,
        "prefix": "",
        "decimalSeparator": ".",
        "italic": FALSE,
        "bold": FALSE,
        "suffix": "",
        "fontColor": "#FFFFFF",
        "thousandsSeparator": " "
    }
ELIF (BETWEEN((SUM(Lead age)), 100,365)) THEN
    {
        "backgroundColor": "	#FEFEBE"
    }
   ELIF (SUM(Lead age) <100)THEN
   {
       "backgroundColor": "	#299438"
       }
ELSE
    {
        "fontColor": "#30aabc"
    }
ENDIF

Tip: For help with conditional formatting, see Changing the format of results.