How to prepare a Dataset to report on Sales Cycle Efficiency (using Advanced Analytics features)

Let’s say we want to understand how long it takes for a lead to convert into a closed-won deal—so we can:

• Optimize our sales cycle efficiency

• Spot reps or campaigns with faster conversion times

• Set goals to reduce time-to-close

This is difficult to get from the CRM itself, especially when contact and deal-related data live in separate places. However, we can use Datasets and create a table of raw data from both of these objects, prepare it, and blend it.

Create the Contacts Datasets

We’ll start by creating a dataset from our Contacts object. 

1. Go to the Datasets page and create a new Dataset from your CRM. We'll use HubSpot CRM for this example.

2. Select the Contacts table and choose the following columns: Create_date, Email, and HubSpot_Owner

3. Preview the data and save the Dataset. We recommend renaming the Dataset so it can be easily referenced in other steps. Rename it to "Contacts Dataset".

Create the Deals Dataset

1. Go to the Datasets page and create a new Dataset using the Deals table.

2. Select the Contacts table and choose the following columns: Close_date, Deal_stage, Email.

3. Preview the data and save the Dataset. Rename it to "Deals Dataset".

Filter the Contacts Dataset

It's a good practice to clean up the Dataset before using it in further steps. For example, if you don't want to look at gmail contacts, you can filter them out in the dataset. You can also apply filters later but this removes any complexity in further steps.

1. Edit the Contacts Dataset.

2. Click on the arrow icon in the Email column and click on Filter.

3. Set the filter to Does Not Contain @gmail.
   

   

4. Save the changes.

Merge the Contacts Dataset with the Deals Datasets

1. Go to the Merged Datasets page and click on Merge Datasets.

2. First, select the Contacts Dataset. Select to include all columns.

3. Click "+" to add a new Dataset and select the Deals Dataset.

4. Next you'll will be prompted to select the Join Operator.

   1. Select the Inner join.

   2. Under Join Conditions, select "Email" from the Contacts Dataset, and "Email" from the Deals Dataset.

   3. This will ensure that only rows where the email matches between both the Contacts and the Deals Datasets will be stored in the new Dataset.
      

      

You now have a new merged Dataset:

Create a new Calculated Column in the Merged Dataset

1. Go to the Merged Datasets page, click View Data, and Edit Data.

2. On the right-hand side, click on the "+" column to get started with a new calculated column.

3. In the Fx field, paste the following formula: DATEDIFF (deals,closed_date, contacts. created_at) and click on Run.

4. This new column will show exactly how many days it took each lead to become a customer.

5. Rename the column to "Time to Close" and save the dataset.

Use Metric Builder to create Custom Metrics

As the last step, we can either:

1. Export the dataset as a CSV to keep a historical snapshot, share it with the team, or run deeper analysis in tools like Google Sheets or Excel.

2. Or, create custom metrics from it so we can track key business metrics on our dashboards like time to close or average days for a lead to become a customer.

If you need any help, feel free reach out to our support team via chat.