Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

How to get last communication date from customers in an organization

James K_ September 1, 2023

Hello, my team has a need to record last communication - either the most recently opened ticket or most recently responded to ticket - per customer Organization.  I currently have a customization that updates "Last Customer Update" when a customer responds on a ticket.  Ideally, I will be able to query this information using a JQL query so that I can use the Google Sheets Jira integration.  I didn't see an existing report that seems to get the data I want.  Is there a way to get this information using JQL (either creating new automations or existing fields)?

Thanks!

2 answers

1 vote
Esteban Diaz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 4, 2023

Hi @James K_ , 

Maybe you can create a Custom field to hold always the las comment...

And then an automation triggered on comments that makes a copy of the last comment into the Custom Field ... this way you´ll be able to query issues with JQL and get the last comment in a column that you can export to Google Sheets ...

To copy the last comment you can use an smart value like this one in your automation:

{{issue.comment.last.body}}

 

I hope this has been useful to you.
I will be waiting for your comments. 😉

Cheers!


Esteban Diaz
https://www.linkedin.com/in/estebandiaz/ 

James K_ September 14, 2023

Hello Esteban,

Thank you for the the suggestion.  I already have a field "Last Customer Comment Date" that is updated when a ticket is created and when it is responded to.

I don't necessarily need the actual comment but the date across our entire customer base in Jira Service desk.

Assume the following customers and 'Last Customer Comment Date" and Tickets #(Jira-##)

Company A -

   Jira 1 - 12/31/2022;    Jira 5 -9/4/2023

Company B -

   Jira 2 - 2/1/2023;        Jira 3 - 6/1/2023

Company C -

  Jira 4 - 7/1/2023;        Jira 6 - 9/12/2023


My understanding is that I can only query against tickets, and not against organizations.  Is it possible to query against our customer service desk project and only return the tickets with the most recent "Last Customer Comment Date".

Based on the above example, I essentially want a query that would return Jira-5, Jira-3, and Jira-6.

 

Thank you again for the suggestion and have a great day.

Esteban Diaz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 17, 2023

Hi @James K_ , 

Unfortunately is not possible to get this result with a simple JQL ... as we have no clauses like "group by" or "distinct" used in SQL.

You will need to create a different JQL query for each  Company.

Or.. I can recommend you a great add on called EazyBI with which you can build this and many other even more complex reports.

This is the link for EazyBIhttps://eazybi.com/ 

In this link you will find an EazyBI demo with preloaded data for JSM :  https://eazybi.com/accounts/1001/cubes/Issues

and another for Jira Software: https://eazybi.com/accounts/1000/cubes/Issues   

 

Cheers!


Esteban Diaz
https://www.linkedin.com/in/estebandiaz/ 

0 votes
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
October 14, 2023

Hi @James K_

if you're open to solutions from the Atlassian Marketplace, you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of advanced features, including support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

With these, you can build a view like this in just a couple of clicks:

date-of-last-customer-comment-sum-up.gif

(This will work perfectly fine with your Last Customer Update custom field - but JXL would also come with a so-called smart column for the date of last comment by customer, so you could probably also get rid of the automation.)

Any questions just let me know,

Best,

Hannes

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events