Report Date Filters

[NStones]

I want to track how many deals hit each stage in the last month. What is best practice, filter by "created date" or "last activity"?

Each time I run it the numbers are obviously different, but what is best practice in sales?

Also, for quarterly reports, do sales teams like to look at everything "created" that quarter, or had "activity" that quarter.

[karstenkoehler · Accepted solution]

Hi @NStones,

HubSpot's not very good at answering the question of how many deals entered a certain stage in a given month. What is much easier to answer is how deals created in a month are distributed across deal stages in this very moment. You would simply navigate to Menu > Reports > Reports > Report library > search for "Deal stage funnel with deal totals and conversion rates" > Click Edit in builder > Click Filters on the upper left of the screen > Adjust timeframe to your liking.

If that works for you, you could ignore the rest of my answer below.

If however you really need to now how many deals entered a certain stage in a month, we would have to get technical. This data is not explicitly available in a HubSpot property so we would have to get it there first.

Let's look at existing properties first: Create date would not be sufficient. It would only document the deal entering the first stage, but not keep track of the dates when the deal enters any of the following stages. Last activity includes notes, chat conversations, calls, emails, meetings – so there are a lot of things which could update this. We would have to create date properties for when each deal enters a certain stage.

This is unfortunately a bit more complicated than it should be. For contacts and Lifecycle stage, there is a date property called Became a [lifecycle stage] date which is automatically stamped by HubSpot. For deals, this does not exist and has to be created and maintained manually (Free and Starter versions of HubSpot) or automatically (Professional and Enterprise versions of HubSpot.)

In both cases, you would first create a custom date property for each stage. Similar to the Became a [lifecycle stage] date properties for contacts, I usually name these Entered [deal stage] date. For each stage, create a custom date property.

For the manual solution, you'd ideally place all of these new properties in the left deal sidebar so that sales reps can update this date when they update the deal stage. https://knowledge.hubspot.com/crm-setup/customize-record-sidebars This additional step is far from perfect but it is what it is.

A workflow solution that automatically does this could look like this:

You would have to repeat this process for each stage, either in multiple workflows like this (less prone to error) or one combined one:

Optionally, you could create yet another custom property (Last deal stage update date) which gets a time stamp at the end of each of the branches.

When switching this live, do not choose to enroll existing records that match the criteria. Otherwise all old records will be stamped with the current date (today's date).

(For historic values, things get even more complicated. You would have to export the property history for Deal stage, following the instructions here. This is the only way to access the information when a deal moved to another stage. In step 2 of the instruction linked above, your goal is not get the latest value of the Deal stage property. You are looking for a final Excel file that includes a column for the email address, followed by columns of all of the custom date properties created above.)

You would now have date properties that you could use in your custom deal reports.

Before you launch into this endeavor, again, I'd recommend checking out the report library (Menu > Reports > Reports > Report library). You might find something that you weren't looking for but which is equally informative.

Let me know if this answers your question!

[shaikhzeba23]

In tracking deals for the last month, it's generally considered best practice to filter by "last activity" rather than "created date" if your goal is to capture the current state of your sales pipeline. "Last activity" provides a more dynamic view, focusing on deals that have had recent engagement or progression. This approach is especially valuable for assessing the real-time status of active deals.

[NStones]

Super helpful, I ended up going this route, but selecting "any", because our deals don't always go in a linear fashion. Appreciate your help!

[webdew · Accepted solution]

Hi @NStones ,

Create date will only bring in deals on the date they were created in the portal and last activity pulls in deals with emails, notes, calls etc... not based on stage updates.

So, what we can can do is create a funnel report to see how many deals have gone through each deal stage in a specific pipeline like this one here:

For example- 2 deals have gone through all stages until TL ROUND (interviews) for the Interviews deal pipeline (see screenshot)

You can see more on funnel reports here: https://knowledge.hubspot.com/reports/create-custom-funnel-reports

Hope this helps!

If we were able to answer your query, kindly help the community by marking it as a solution.

Thanks and Regards.

[karstenkoehler · Accepted solution]

Hi @NStones,

HubSpot's not very good at answering the question of how many deals entered a certain stage in a given month. What is much easier to answer is how deals created in a month are distributed across deal stages in this very moment. You would simply navigate to Menu > Reports > Reports > Report library > search for "Deal stage funnel with deal totals and conversion rates" > Click Edit in builder > Click Filters on the upper left of the screen > Adjust timeframe to your liking.

If that works for you, you could ignore the rest of my answer below.

If however you really need to now how many deals entered a certain stage in a month, we would have to get technical. This data is not explicitly available in a HubSpot property so we would have to get it there first.

Let's look at existing properties first: Create date would not be sufficient. It would only document the deal entering the first stage, but not keep track of the dates when the deal enters any of the following stages. Last activity includes notes, chat conversations, calls, emails, meetings – so there are a lot of things which could update this. We would have to create date properties for when each deal enters a certain stage.

This is unfortunately a bit more complicated than it should be. For contacts and Lifecycle stage, there is a date property called Became a [lifecycle stage] date which is automatically stamped by HubSpot. For deals, this does not exist and has to be created and maintained manually (Free and Starter versions of HubSpot) or automatically (Professional and Enterprise versions of HubSpot.)

In both cases, you would first create a custom date property for each stage. Similar to the Became a [lifecycle stage] date properties for contacts, I usually name these Entered [deal stage] date. For each stage, create a custom date property.

For the manual solution, you'd ideally place all of these new properties in the left deal sidebar so that sales reps can update this date when they update the deal stage. https://knowledge.hubspot.com/crm-setup/customize-record-sidebars This additional step is far from perfect but it is what it is.

A workflow solution that automatically does this could look like this:

You would have to repeat this process for each stage, either in multiple workflows like this (less prone to error) or one combined one:

Optionally, you could create yet another custom property (Last deal stage update date) which gets a time stamp at the end of each of the branches.

When switching this live, do not choose to enroll existing records that match the criteria. Otherwise all old records will be stamped with the current date (today's date).

(For historic values, things get even more complicated. You would have to export the property history for Deal stage, following the instructions here. This is the only way to access the information when a deal moved to another stage. In step 2 of the instruction linked above, your goal is not get the latest value of the Deal stage property. You are looking for a final Excel file that includes a column for the email address, followed by columns of all of the custom date properties created above.)

You would now have date properties that you could use in your custom deal reports.

Before you launch into this endeavor, again, I'd recommend checking out the report library (Menu > Reports > Reports > Report library). You might find something that you weren't looking for but which is equally informative.

Let me know if this answers your question!

[NStones]

Thank you so much, extremely thorough and gave me lots of good ideas. Appreciate your help.