[SOLVED] Show top 5 tags per month (correctly)



  • Jessie Schutz

    Hey Jude!

    Can you tell me why you think the report is incorrect? It looks like the number of tags is different each month, and it's not unusual for tag usage to be pretty consistent. What do you expect to see?

  • Amy Dee

    Hi Jude! Report filters apply to the report as a whole. The ranking filter in that section won't apply differently for each month; it will apply evenly to the whole range.

    To find the top 5 within each month, you need to use a custom metric. GoodData has some documentation on finding the TOP or BOTTOM(n). In your case, you're looking for the top 5 tags within each month. That metric would look something like this:

    • SELECT # Tickets WHERE TOP(5) IN (SELECT # Tickets BY Ticket Tag) WITHIN (Month/Year (Ticket Created) ALL OTHER)

    I created this metric in a test environment, put Ticket Tag and Month/Year (Ticket Created) under HOW, and filtered for a 6-month period. You can see that there's a lot of overlap, but there are 7 different colors here:

    I hope this helps! Happy reporting!

  • Jude

    Thank you both very much for your help.

    Amy, your solution is exactly what I was after and has worked perfectly! Thanks a bunch.

  • Amy Dee

    Awesome! I'm glad that worked out for you.

    Happy reporting!

  • Jude

    Hi Amy,

    Do you know why the metric would be showing 6 tags here when the metric specifies 5?

  • Amy Dee

    Hi Jude! The most likely cause is a tie for last place. Insights won't pick a "winner" in those cases. If multiple tags have the same count, and at least one of them would be in the top 5 for your filter, Insights will include them all.

    I hope this helps! Happy reporting!

  • Jude

    Perfect, thank you! 

  • Violeta Micu

    Hello everybody,

    I need your help in creating a TOP3 where i want to include custom fields and tags.

    So i defined a custom field called Category and a second category (we call it Subcategory). When i am making the reports in Insights i am using Category + tags (defined for each subcategory) because is easier. You can see a picture from a ticket for a better understanding:

    - "Account" is a Category

    - "Password change" is a Subcategory but i am using the tag "password_change_subcateg" in reports



    Everything works fine but now i need to create 2 graphics

    1. TOP 3 Category 

    2. TOP 3 Subcategory (Tags) for each of the categories from TOP 3 Categories

    If i am selecting 2 filters in the report that contains TOP 3 Category and TOP 3 Tags it shows one  subcategory(tag) that has the most volume in each category:

    - customer_lifecycle is part of Account Category

    - Install/Uninstall is part of Technic

    - Refund is part of Payment/Financial


    I will like to keep the first graph and to create other 3 graphs that will show: TOP3 subcategories (tags) for Rank 1 from TOP 3 Categories. The second graph should contain TOP 3 subcategories (tags) for Rank 2 from TOP 3 Categories and so on.

    I found this article https://support.zendesk.com/hc/en-us/articles/206996687#topic_dxw_gbk_ps i tried to do things like there but in the end is not useful for my goal. The thing is that when the TOP3  Categories is changing the graphs should change as well this is why it needs to be dynamic.

    Does anyone tried to do this kind of report until now? 

    I hope that i explained well for you to understand what i want to reach, if not please let me know.

    Thank you!


  • Amy Dee

    Hi Violeta! It is possible to find the top three values of one field within the top three values of another. To do this, you'd need to rank your subcategories within a metric, then rank your categories on the report.

    It looks like your subcategories may be stored in different ticket fields, but their tags end with "subcateg." That's awesome! It means you can create a dynamic tag filtering metric based on that string. We have details about this type of filtering metric in our recipe for reporting on multi-select fields using ticket tags. (The multi-select part isn't relevant for your report, but finding tags based on a string is.)

    Once you have a metric that finds subcategory tags, you can construct a ranking metric similar to the one we've been discussing here. I built one in my test account that ranks "genre" tags within the "favorite color" field:

    • SELECT Genre Tags WHERE TOP(3) IN (SELECT Genre Tags BY Ticket Tag) WITHIN (Favorite Color ALL OTHER)

    To construct the report, I left this metric under WHAT and selected Ticket Tag and Favorite Color under HOW. For filters, I started with a "List of Values" filter to exclude empty values for the Favorite Color field. Then I added a ranking filter to find the top 3 Favorite Color values by total tickets. 

    For the display, I chose a pie chart, expanded the configuration tools, and made sure Favorite Color was the horizontal axis and Ticket Tag was the series.

    The result is a set of three pie charts showing my top three favorite colors. Within each pie chart, there are three slices for the top three genre tags within that color:


    You should be able to construct something similar to find "subcateg" tags within each category.

    As a quick disclaimer: these examples are provided for demonstration purposes only. I can't guarantee them for all use cases, and I strongly recommend that you audit your results.

    I hope this helps! Happy reporting!

  • Violeta Micu

    Amy thank you so much. Finnaly i have the report i need.

    I changed a little the first metric created for tags. The reason why i did this is because i needed to exclude a tag so the ILIKE didn't helped me in this case. 

    I used : SELECT #Tickets WHERE Ticket Tag IN ( all tags i need to show)

    Tickets = metric

    Ticket Tag = Atribute

    The tags that i need = Atribute Value

    After i double-checked the data everything seems fine.

    Once again thank you very much for your help.



Please sign in to leave a comment.

Powered by Zendesk