We want to gain more insight on how frequently common subjects are covered in our tickets  to help prioritise the development of the product and the support resources.

We've chosen to use a single field (that can contain multiple values) to record all of the different subjects that a single ticket (query and resolution) might cover.

These values have a hierarchy of 9 main categories, with each main category then having one or more sub categories.  Each main category and sub-category is represented by a single unique value.

So, if a ticket concerns both the changing of an email address on an account AND a request for an invoice, the field contains the following values:

'Account' (main category)

'Change Email' (sub-category of Account)

'Billing' (Main category)

'Invoice Request' (Sub category of Billing)

Many of these values are added automatically, either by keyword-based triggers or when a comment macro is used.  Ideally we want to automate this as far as possible to maintain agent response time. 

For the same reason, where an existing trigger or macro does NOT capture a particular issue or request type, we are using composite values following the same hierarchy, for example:

'Account - Password Re-set'

We also have legacy values in the field which need to be incorporated into reports, which can look like this:

account_initial_setup

To incorporate all of these values, I'm trying to create an Explore report that will show me the total amount of tickets that have the keyword 'account' in this field. I want to show this as a proportion of all tickets in a given month.  I also want to include the proportion of tickets that have the keyword 'Billing', those that have 'Integrations' (etc. to cover all 9 main categories) in the same report.

Then, I'd like nine separate reports to break down each set of tickets with a main category keyword into their sub-categories.

I'm basically trying to report on the quantity of instances of a keyword, as opposed to the number of tickets, as one ticket could appear in multiple main categories at the same time. 

I've been referencing articles and using trial and error, but with no joy so far.  What's the best way to do this?