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

[Adriane]

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:

1. 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.
2. 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.
3. 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.
4. 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."
5. 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.

[DarrenHepburn]

Superb post - super helpful and beautifully laid out. 😊

[Shadab_Khan]

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

[BérangèreL]

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

[Jnix284]

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!