Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Adriane — Wed, 28 Feb 2024 16:08:10 GMT

In the daily service routine, it's common for previously closed tickets to be reopened due to additional customer inquiries.

However, creating report data for such scenarios in HubSpot can be challenging. Besides the standard "Time to Close," which sums up all durations, properties like "Time to Reopen" and "Time to Re-close" are actually necessary.

The good news: With five custom properties and a workflow, you can easily generate this data. This guide will walk you through the process step by step to ensure you get accurate and meaningful report data. It's much easier than it seems at first glance. Dare to try it out!

Step 1: Set Up Custom Ticket Properties

To generate the required data for your reports, start by creating the mentioned five custom ticket properties:

Close Date (Auxiliary Property):
1. Create a custom ticket property of type "Date picker" without any special settings and name it, for example, "Close Date (Auxiliary Property)."
2. This property is needed to secure the current closure date of a ticket because when a ticket is reopened, this date is removed from the original property. However, we need it for our report, and the auxiliary property ensures that.
Reopening Date:
1. Create another custom ticket property of type "Date picker" without any special settings and name it, for example, "Reopening Date."
2. This property stores the date when a previously closed ticket was reopened.
Time to Reopen:
1. Define another custom ticket property, this time of type "Calculation". and name it, e.g. "Time to Reopen."
2. Choose "Time Between" as the property type.
3. Set the start date as "Close Date (Auxiliary Property)" and the end date as "Reopening Date."
4. This property stores the time between closing and reopening a ticket, which you can use for your reports.
Close Date (Auxiliary Property 2):
1. Create another custom ticket property of type "Date picker" without any special settings and name it, for example, "Close Date (Auxiliary Property 2)."
2. This property automatically secures the current closure date needed for calculating "Time to Re-close."
Time to Re-close:
1. Define the last custom ticket property of type "Calculation" and name it e.g. "Time to Re-close."
2. Choose "Time Between" as the property type.
3. Set the start date as "Reopening Date" and the end date as "Close Date (Auxiliary Property 2)."
4. This property stores the time between reopening and re-closing a ticket, which you can use for your reports.

HubSpot custom properties for reporting on reopened tickets

With these custom ticket properties, you are now ready to generate the required data for your reports.

Step 2: Create a Ticket Workflow
An efficient workflow is the key to generating precise report data for reopened tickets in HubSpot. Here's how to set up this workflow for seamless operation:

Define Workflow Type and Re-enrollment Trigger:
- Create a new blank ticket-based workflow.
- Choose the trigger type „When filter criteria is met.“
- Define the trigger by selecting tickets whose "Closure Date" is known, i.e. all closed tickets in HubSpot.
- Finally, activate re-enrollment for this workflow.

HubSpot workflow for generating precise report data for reopened tickets

Secure Close Date:
- Define as the first action in the workflow that the value of the HubSpot standard ticket property "Close Date" is copied into your custom property "Close Date (Auxiliary Property)."
- Pro Tip: By the second run of the workflow, it will be necessary to delete the value of the property "Close Date (Auxiliary Property)" before copying the new date.
- This precaution is not necessary during the first run because your created auxiliary property is naturally still empty. However, from the second, third,... nth entry into the workflow, it is necessary to delete the old value because overwriting the date will not happen. In other words, the data would not be accurate without this deletion action, which is crucial as tickets may be reopened multiple times.

Secure close date in auxiliary property

Delay Until Reopening:
- Set a delay of the type „Event occurrence“ until the HubSpot standard ticket property "Close Date" becomes unknown again, using the event „Property value changed“. This indicates that the ticket has been reopened.
- Ensure that you add a branch to the delay to exclude tickets from the workflow if they are not reopened within 5 years (= 1,825 days). This is the maximum time limit in HubSpot, which should be more than generous for service times.

Add delay until close date is unknown

Set Reopening Date:
- Set the "Reopening Date" to the current date ( = „Date of step“).
- Here, too, you may want to incorporate the precaution mentioned above under 2. to overwrite existing values before setting the actual one.

Set reopening date

Delay Until Re-close:
- Add another delay until the HubSpot standard ticket property "Close Date" becomes known again. This indicates that the ticket has been closed again.
- Ensure again that you add a branch to the delay to exclude tickets from the workflow if they are not reopened within 1,825 days.

Add delay until re-close

Copy Close Date Again:
- Copy the HubSpot standard ticket property "Close Date" again, but this time into the property "Close Date (Auxiliary Property 2)."
- Here, too, you may want to incorporate the precaution mentioned above under 2. to overwrite existing values before copying new values.

Copy next closedate

Add another Delay Until Reopening:
- Set a last delay of the type „Event occurrence“ until the HubSpot standard ticket property "Close Date" becomes unknown again.
- Ensure again that you add a branch to the delay to exclude tickets from the workflow if they are not reopened within 1,825 days.
Set a new Reopening Date:
- Set the "Reopening Date" to the current date ( = „Date of step“).
- Here, too, you may want to incorporate the precaution mentioned above under 2. to overwrite existing values before setting the actual one.

...and: you're done! That's how your workflow may look now:

Full HubSpot workflow for generating precise report data for reopened tickets

The workflow ensures that tickets can be reopened and closed indefinitely while retaining the necessary information for your report on reopened tickets.

There is a small downside to this, but there is already a post in the ideas forum, so we hope for a solution.

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

BérangèreL — Thu, 29 Feb 2024 10:05:29 GMT

Hi @Adriane, I hope that you are well!

Thanks a lot for sharing with the Community, this really complete guide to have reports on reopened tickets!

Hi @Josh, @Jnix284, @Bryantworks, @louischausse, @BukunmiOdetayo, @beeginman, @OPerek-Clark, @danmoyle, @GRajput and @Shadab_Khan this might be of interest for you and/or your customers.

Have a great day!

Best,
Bérangère

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Shadab_Khan — Thu, 29 Feb 2024 10:21:48 GMT

@BérangèreL Thank you - you are the BEST!

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Jnix284 — Thu, 29 Feb 2024 12:46:50 GMT

Thanks for the tag @BérangèreL, this is a really great way to manage reopened tickets @Adriane, thank you for sharing your step-by-step process!

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

DarrenHepburn — Tue, 10 Sep 2024 14:45:09 GMT

Superb post - super helpful and beautifully laid out. 😊

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

SDiplock — Wed, 15 Jan 2025 08:45:13 GMT

This is super helpful, how do I now visualize this data @Adriane ?

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Adriane — Mon, 17 Feb 2025 09:51:03 GMT

Hi there,

you may create the following custom reports:

"Average Time to Reopen" (line or bar chart)
- Calculates the average "Time to Reopen" for tickets within a specific time period (e.g., this month, this quarter)
- To identify trends in why tickets are being reopened
"Average Time to Re-close" (line or bar chart)
- Tracks the average time it takes to resolve reopened tickets ("Time to Re-close")
- To understand the efficiency of handling reopened issues
"Reopen Rate" (pie chart or a simple data table)
- Calculates the percentage of closed tickets that are reopened within a given timeframe
- Gives an idea of the quality of initial resolutions
"Number of Reopened Tickets" (bar chart)
- Shows the total number of tickets reopened in a specific period
- Monitors the volume of recurring issues
"Reasons for Reopening" (pie chart or a bar chart)
- You need to create an additional custom property to create this report
- Then you can capture the reason for reopening and identify common issues that lead to reopenings
"Reopen Rate by Agent" (bar chart)
- Shows which agents have the highest ticket reopen rates
- Highlights areas where additional training or process improvement is needed
"Time to Re-close by Agent" (bar chart)
- Analyzes how quickly different agents resolve reopened tickets

Let me know if you have still questions.

Best regards

Adriane

topic Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot in Tips, Tricks & Best Practices

Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot

Re: Step-by-Step Guide: Generate Precise Report Data for Reopened Tickets in HubSpot