Create custom report for (average) ticket time per ticket stage

[Anonymous]

Hello,

I'd like to create a report to see how long each ticket is in each separate ticket stage from our ticketing pipeline.

I've read online that this is still not possible. Can someone share a workaround so I can create a custom report on this matter? I've seen the post belowbut unfortunately this doesn't work for me: https://community.hubspot.com/t5/HubSpot-Ideas/Time-in-Ticket-Status-reporting-function/idi-p/390372

Many thanks in advance! 

[mollmann]

Hi everybody,

I wanted to share an update on additional functionality about ticket time in status reporting.

Service Hub Professional and Enterprise customers will now have access to new data points about the time in ticket status. Each ticket will now have new properties that can be used in report builders and segmentation alike:

• Date entered ticket status
• Date exited ticket status
• Latest time in ticket status (calculated using the two properties above)
• Cumulative time in ticket status (for tickets that re-enter a ticket status multiple times)

Users will also be able to access out-of-the-box reports with these new data points in the Help Desk Analyze Tab as well as the Service Hub Analytics Suite.

Let us know if you have any questions or feedback.

[Anonymous]

The workflow is currently like this

And the reporting like this: 

Still not sure if this is how you see it 🙂 Looking forward to your reply in the afternoon!

[Anonymous]

I now have the following settings for my reporting:

Based on this workflow: 

Not sure if all should be counted as '1'. Hoping that we can find a workaround to make this work! 

[franksteiner79 · Accepted solution]

Hi @Anonymous 

Did some more thinking, think this should work:

My thinking is this:

1. We stamp in 4h intervals
2. To capture correct times we need some sort of time stamp property.
3. We should be okay using just on time stamp property, because a ticket only ever has one status at any one time.
4. We also need a delay (4h) which then clears the time stamp property
5. This triggers a re-enrolment into the WF
6. This checks if there was a change in status and either adds another 4h to the time in the existing stage or the new one the ticket has moved into

Wanna give that a try?

Frank

[VPradhan24]

Hi Frank,

I tried implementing your workflow, with a few tweaks of my own.

For testing, I have set the delay for 2 mins. 

The problem I am facing, is that the tickets are not enrolling even after the Time Stamp is cleared. When I reached out to the HubSpot team, they said that the possibility of an infinite loop is stopping the ticket from re-enrolling again.

To bypass that, I have added a fail safe, that if a ticket enters the loop more than 5 times, the ticket status changes to On Hold and the ticket is not enrolled again. However, even with this, the ticket is enrolling only once. 

Can you please check and help find where I am going wrong? Waiting for your response on this. 

Thanks in advance.

[Lor_Mas]

Hi @franksteiner79,

I have been going through all the messages with ASchipper, but since I am brand new to HubSpot I have been struggling to replicate the solution you have proposed in your message. So far my understanding is the following:

Step 1: create a Ticket property "Timestamp" that will report the date when the ticket changes status

Step 2: create a Workflow (for each ticket status) that will populate the Ticket property "Timestamp" with the date it has changed status for each ticket status

Step 3: create a Calculation Property that will calculate "Time in" for each Ticket Status by subtracting the time stamp of the old status to the new one

Step 4: once new tickets come in, timestamps and time will start to be populated and therefore the report can be built

In my case I don't need to keep track of time if a ticket goes back in status.

Let me know if my understanding is correct. 

May I ask you to explain more in detail how should I go about each step (provided that they are correct) as I am not sure that I am setting up these properties and workflow in the right way.

Thank you very much in advance

Best,
Lorenzo

[Anonymous]

How should I then display this information in a report? I'm having a hard time figuring this out.

[PamCotton]

Hello, @Anonymous thank you for posting in our Community! I would recommend upvoting and leave your comment on the idea shared, as our product team, who monitors the forum regularly, can read your specific use case and understand why this would be a useful functionality or change. It also helps other customers facing the same issue to advocate for its implementation on your behalf by upvoting on the thread as well.

I want to invite our top experts to this conversation to share their thoughts @jolle @danmoyle @Jnix284 any recommendations for @Anonymous?

Thank you,

Pam

[Anonymous]

Thank you Pam!

[franksteiner79]

Hi @Anonymous 

I would have suggested a similar approach to the one described in the community post that you have shared. You essentially need to time stamp when a ticket enters a stage and moves to the next. The main issue I see with building this yourself is, how sequential is your ticket process. 

For things like deal stages or live cycle stages where the basic assumption is to only go forward and never back, this works well. But with tickets it isn't unusual for a ticket to circle once or twice through "waiting for customer" and "in progress".

Assuming tickets only move one way, I would do the following:

1. Create a time stamp property for each ticket stage, that gets set once whenever a ticket enters that stage.
2. Create a calculated properties, keep in mind the limit on those properties.

Hope that helps.

Frank

[Anonymous]

Hi Frank, thank you for your solution! Indeed, the tickets do move backwards into the ticket pipeline sometimes. Would it be wise to still implement your suggestion, to get at least a grasp of the average time, taken with a grain of salt? Or wouldn't this be wise. 

[franksteiner79]

Hi @Anonymous 

Then it gets a bit tricky and my solution won't work. From the top of my hat, the following could work but would give whole days.

1. Create a number property for each ticket stage - time in stage x
2. Create a ticket workflow that runs on a daily schedule - either beginning or end of the day
3. The create a "value equals branch" to check the status/stage of a ticket and increase the "time in x stage" by 1. Which would look something like that.

Depending on the level of accuracy you need, you could start the WF at 8am, do the value equals branch, then then check the status again 4h later, if it hasn't changed add 0.5 or 0.25 (depending if you use working days or full 24h), then check again 4h later, and so on.

It is a somewhat crude approach but should work and should also catch and keep adding the respective time to the stages if tickets move back and fort.

Frank

 

[Anonymous]

Hi Frank,

Many thanks! This could work as a workaround solution for the time being. 

If I understand you correctly, '1' means one day in your example? And with the change added of 0.25, 0,5 etc. as the ticket moves to other stages? Not sure what you mean by that 🙂

Or should I just number every stage 1 to 5 i.e. as seperate number per ticket pipeline stage. So the 'time in stage x'  would be 'time in stage 1 through 5' i.e? 

[franksteiner79]

Hi @Anonymous 

Yes, sorry if that wasn't clear from my side. 1 means 1 day, the fractions were referring to a fraction of a day. Theoretically you could be that granular, question is if you need/want to add that complexity. 

If we check a ticket's status (i.e. new) we but 4h later it is being worked by a support rep, the status would have changed. So either we say time in new was 1 = 1 day, or we say it was 0.5 days (assuming we define a 1 day as 8 working hours).

Each ticket pipeline stage needs its own "time in stage" property for this to work, so you have "time in stage 1", "time in stage 2", etc. You then increase the count of these properties either by full or fractional days via a workflow.

[Anonymous]

Like this: 

[Anonymous]

We do not have operations professional, so I've set the triggers to trigger every time when a record (ticket) is created.

[franksteiner79]

Ohh my bad, seems I have an Ops Hub Trial in my portal 🙄

I would change this to "ticket status is known", we want and need to capture changes in the ticket status in order to get the "time in stage x" to increase. If we can't schedule this daily, I am not 100% sure how we can make sure we add the right amount of time. Let me mull this over later today when I have no meetings.

For the reporting, I would create a ticket report, select the appropriate ticket create timescale (maybe this year so far), select the "summary" chart type, display the "time in status x" property and select avg.

 

[Anonymous]

The workflow is currently like this: 

And the report like this: 

Wondering what I'd have to change in how to make this work ... 🙂 Looking forward to your reply in the afternoon! 

[Anonymous]

Thanks Frank, I'll kindly await your reply in the afternoon.