Tickets & Conversations

James-Preseem
Colaborador(a)

Time in current status calculation

I am sure if this is the right location for this question, please let me know if there is a more appropriate board.

 

I am trying to determine the length of time that a ticket has been in the current pipeline stage (status).   I suspect that I can find a way to make this work using a customer calculated property....however I know that this information is already an attribute of the ticket within HubSpot.   In late 2020, there was a short week of time in october, where the export of all ticket properties was not restricted and showed there were actually almost 270 properties available.

 

Specifically there were 

 

Date entered 'stagename (Pipeline Name)'

Time in 'stagename (Pipeline Name)' (HH:mm:ss)

Date exited 'stagename (Pipeline Name)'

 

For each stage / pipeline that we have  as well as for the current stage pipeline as a separate set of properties.

Unfortunately, these were only temporarily available and were no longer included in the export of all properties after a few days of public exposure.  Further unfortunate is the fact that these have never been exposed to then reporting or workflow parts of the product.

Which brings me to my question.   Are any of these properties exposed to the API as properties?  Is there an hs_pipeline_stage_time_in_stage_current  or something similar that I can access that will give me the current duration that a ticket has been in the current stage (status)?

 

UPDATE:

Since posting this message I have personally researched the issue further and have figured out how to expose this information.

 

In simple terms, each ticket has a series of properties for each of the statuses that are available for Tickets.  These statuses are hidden by default but can be made visible via the API.

The naming of the Properties via the API use the naming format

  • hs_date_entered_1
  • hs_date_exited_1
  • hs_time_in_1

...where 1 is the internal status ID for the specific status.  In this case "1" is the default status "New"

 

Looking at the details for the property hs_time_in_1, you can see that there is a "Hidden" attribute for this property.

 

{
"updatedAt": "2022-08-18T12:58:04.208Z",
"createdAt": "2020-02-11T18:45:00.484Z",
"name": "hs_time_in_1",
"label": "Time in 'New (Support Pipeline)'",
"type": "number",
"fieldType": "calculation_read_time",
"description": "The total time in seconds spent by the ticket in the 'New' stage, 'Support Pipeline' pipeline",
"groupName": "ticket_activity",
"options": [],
"displayOrder": -1,
"calculated": false,
"externalOptions": false,
"hasUniqueValue": false,
"hidden": true,
"hubspotDefined": true,
"modificationMetadata": {
"archivable": true,
"readOnlyDefinition": true,
"readOnlyValue": true
},
"formField": false
},

 

Using the API, you can update the hidden attribute and expose this value within the UI and to the workflow process.

 

 

 

 

 

8 Respostas 8
James-Preseem
Colaborador(a)

Time in current status calculation

@Vlad09  @LDeClercq 

 

The update that we applied did use the `Patch` action as you described.   The one thing that I did different in my case was that I had changed a number of property for a series of statuses that were not default statuses that were created by HubSpot.  
In my support pipeline, I have a series of statuses, "waiting for client", waiting for escalation reply", etc...

My interest was with the time that the ticket was in these specific states and not how long it was in "New".  In the original example of the post I used the "New" status (hs_time_in_1) as the the example, but upon review I don't think we actually changed that specific property.   The error that you received back from also leads me to believe that this might not be possible on default (i.e. HubSpot created) default properties.  

 

"hs_date_entered_1 is a hidden default property, so it cannot be unhidden"

 

I am going to attempt to make this change myself, and see if I get the same response.  I suspect that the hs_data_entered_#### that correspond with each of the custom statuses have been added can be unhidden, and maybe this is not possible on the default properties.

I would suggest attempting to unhide this property for one of the custom statuses and see if you get a different result.

 

 

 

 

LDeClercq
Membro

Time in current status calculation

@James-Preseem @Vlad09 
I already managed to unhide the property exactly as @Vlad09 described.
It was indeed a custom status.

I can now make lists who filter on tickets that have been longer than X days in a certain stage.
The only weird thing is, I wanted to make some reports on this so I could measure average time in a stage and see the distribution of time in a stage, but the reports all see the value hs_time_in_'stage' as unknown.
I have conctacted Hubspot support for this.

James-Preseem
Colaborador(a)

Time in current status calculation

I don't know this for sure, but I suspect that HubSpot will only populate
hs_time_in_stage after it has exited that stage. i.e. hs_date_exited_state
- hs_date_exited = hs_time_in


- hs_date_entered_1
- hs_date_exited_1
- hs_time_in_1


0 Avaliação positiva
LDeClercq
Membro

Time in current status calculation

I can give you the answer to that 🙂
Hubspot does already populate it. I currently have a list which tracks tickets that are in stage X and have hs_time_in_X greater than 7 days.

So everything works in lists, it jsut doesn't in reports.

Vlad09
Participante

Time in current status calculation

Hi @James-Preseem,

 

Your message inspired me to try to solve my own problem! I am trying to find a way to show in reports the number of tickets in a specific status for a particular period of time. I'm not a developer, but I decided to give it a try to evaluate the feasibility of solving my problem.

 

I registered as a developer and then created a test account in Hubspot to obtain an accessibility token for the HubSpot API. To send requests, I registered with Postman.

 

In Postman, I made a GET request to https://api.hubapi.com/crm/v3/properties/tickets/hs_date_entered_1

 

I received the following reply:


{
"updatedAt": "2022-08-18T12:56:53.211Z",
"createdAt": "2020-02-11T18:44:49.289Z",
"name": "hs_date_entered_1",
"label": "Date entered 'New (Support Pipeline)'",
"type": "datetime",
"fieldType": "calculation_read_time",
"description": "The date and time when the ticket entered the 'New' stage, 'Support Pipeline' pipeline",
"groupName": "ticketinformation",
"options": [],
"displayOrder": -1,
"calculated": false,
"externalOptions": false,
"hasUniqueValue": false,
"hidden": true,
"hubspotDefined": true,
"modificationMetadata": {
"archivable": true,
"readOnlyDefinition": true,
"readOnlyValue": true
},
"formField": false
}

 

Next, I tried to change the "hidden" attribute with a PATCH request, using the following headers:

 

Key: Authorization, Value: Bearer {{token}}
Key: Content-Type, Value: application/json

 

I included the following raw JSON in the request body:


{
"hidden": false
}

 

However, I received the following reply:


{
"status": "error",
"message": "hs_date_entered_1 is a hidden default property, so it cannot be unhidden",
"correlationId": "71e117fd-074b-4734-83b9-025657186494",
"propertiesErrorCode": "PROPERTY_INVALID"
}

HubSpot_API_reply.png

 

Additionally, when I tried using a PUT request and pasted the entire property with the changed hidden attribute, I received the status: 405 Method Not Allowed.

HubSpot_API_Status405.png

 

Could you please advise me on what I might be doing wrong? 

 

Thank you.

Vlad

 

James-Preseem
Colaborador(a)

Time in current status calculation

@MSnider   I noticed that you upvoted, gave kudos to this post.  Just a quick update on the subject matter.  What I have since learned about this issue is that the properties I am referring can be exposed to the UI.  It is necessary to update the "hidden" attribute of the property via the API.   Once that is done, the property can be made visible in the UI and used within Workflows.

 

James-Preseem
Colaborador(a)

Time in current status calculation

@MiaSrebrnjak 

Is there any indication from Product Management that they might expose the existing properties that track this information already.  I have created properties previously that mirror exiting properties that are not exposed already.  It seems to me that making users create a property, then build a workflow that monitors tens of thousands of tickets in order to provide information that is already available but not yet exposed is redundant and frankly a bit irritating.

MiaSrebrnjak
Gerente da Comunidade
Gerente da Comunidade

Time in current status calculation

Hi @James-Preseem,

 

Thank you for reaching out to the Community!

You are right - there currently isn't a default Ticket property that would record the date when a ticket has entered a certain stage. 

 

You could, however, create a custom date picker property by following the steps in @karstenkoehler's solution to this Community article. I hope this helps!

 

Cheers
Mia, Community Team 

 


Wusstest du, dass es auch eine DACH-Community gibt?
Nimm an regionalen Unterhaltungen teil, indem du deine Spracheinstellungen änderst


Did you know that the Community is available in other languages?
Join regional conversations by
changing your language settings