How to I create a report on average time to close deals

[IOsten6]

I'm trying to create a report to show the average time it takes to close won deals over three different pipelines. I've had a play around and i'm sure there is a simple answer but struggling to find it!

[karstenkoehler · Accepted solution]

Hi @IOsten6,

Are you interested in just the average time to close, just that one number? If so, here's how you would do it:

1. Create a custom report with primary source = deal: https://knowledge.hubspot.com/reports/create-reports-with-the-custom-report-builder
2. Choose a KPI visualiation (orange circle with a green number 1 within)
3. Once in the report builder, drag and drop 'Days to close' into values
4. Use the pencil icon to change aggregation from Sum into Average (see below)
5. Apply filters to meet your pipeline requirements or additional date filters in the Filter tab

Let me know if you have any follow-up questions!

[RPopovich]

Step-by-Step Guide to Calculate the Average Number of Days Between Deal Create Date and Close Date

To calculate the average number of days between the Create Date and Close Date for deals in HubSpot, you need to create two custom properties:

1. Property 1: Calculate the time difference between the Deal Create Date and Close Date (in milliseconds). When you subtract Create Date - Close Date in a calcualted property, the output will be in Milliseconds which will need to convert to Days within the Second Calculated Property. 
2. Property 2: Convert the milliseconds from the first property into days.

---

Step 1: Create Property 1 – Time Between Create Date and Close Date (Milliseconds)

1. Access HubSpot Property Settings

1. Log in to your HubSpot account.
2. Click the Settings icon in the top navigation bar.
3. In the left sidebar menu, go to Properties.

2. Select the Object

• Use the Select an Object dropdown to choose Deal properties.

3. Create a New Property

1. Click Create property.
2. Fill in the details:
   • Label: Time Between Create Date and Close Date (Milliseconds)
   • Group: Deal Information (or your preferred group).
   • Description: Calculates the time in milliseconds between the Deal Create Date and Close Date.

4. Choose the Calculation Type

1. Under the Field Type tab, select Calculation.
2. Choose Number as the calculation type.

5. Configure the Time Between Calculation

This will provide you the number of Milliseconds between the Close Date and Create Date. We will then move onto the Second property to Convert the Milliseconds into Days. 

6. Save the Property

• Click Create to save the property.

---

Step 2: Create Property 2 – Convert Milliseconds to Days

1. Create a Second Deal Property

1. Go to Properties and select Deal properties again.
2. Click Create property.
3. Fill in the details:
   • Label: Days Between Create Date and Close Date
   • Group: Deal Information (or your preferred group).
   • Description: Converts the time in milliseconds into days.

2. Choose the Calculation Type

1. Under the Field Type tab, select Calculation.
2. Choose Number as the calculation type.

3. Write the Custom Formula

1. In the formula editor, input the following:

   text

   Copy code

   [properties.time_between_create_date_and_close_date_milliseconds] / (1000 * 60 * 60 * 24)
   • Replace [properties.time_between_create_date_and_close_date_milliseconds] with the internal name of the first property you created. You can click "Insert" > "Properties" to grab the property you created above. 

4. Test the Formula

1. Click Test Formula and input a sample value to confirm accuracy.
   • Example: 1,555,200,000 milliseconds should return 18 days.

5. Save the Property

• Click Create to save the property.

---

Step 3: Calculate the Average Number of Days

If your client wants to calculate the average number of days across multiple deals, follow these steps:

1. Use a HubSpot Report

1. Go to Reports > Create Report.
2. Select the Custom Report Builder.
3. Add the Days Between Create Date and Close Date property to the report.
4. Use the Average function to calculate the mean value of this property across all deals.

2. Optional: Display in Dashboards

• Add the report to a Dashboard for easy access and ongoing monitoring.

---

Key Notes

• Ensure all deals have both Create Date and Close Date populated. Deals without these dates will not contribute to the calculation.
• If they need automation, workflows can populate these properties based on triggers.
• The average days calculation is dynamic and will update as new deals are added or closed.

Let me know if you’d like help with building the reports or configuring these properties!

[karstenkoehler]

@RPopovich what would be the benefit of this over the default 'Days to close' deal property (see my other reply in this thread)?