Using Access to analyse verbatim text

[simoran2]

Hello

I have a large amount of customer feedback which I would like to apply some high level categorisation to.

I would like to set up lookup tables that include words specific to a particular theme. I would then like access to search the customer comment field and if it contained any of the words from the table apply the categorisation.

For example i may set up a 'Financial'table that included words such as bill, cost, expensive etc... A given record in the db may contain a comment from the customer saying "The bills are too expensive. I am looking for a lower cost provider" Access would see the words bill, expensive and cost and categorise this as a Financial reason.

I assume I would just build my lookup tables like any other table (I would probably import some predefined lists of words and categories) but what is the expression to get access to check the contents of the text field and cross reference it with the lookup tables?

Hope this makes sense - thanks in advance

 

[Norie]

This might actually be more suited to Excel.

It certainly has more functionality when it comes to things like 'lookups'.

 

[simoran2]

Thanks for the reply - unfortunately excel isn't the best tool here - purely because of amount of data I need to process

 

[jackd]

I think you'll be looking at some sort of thesaurus --- customized to your categorizations.

You may get some help for set up from locations like thesaurus.com. Here's an example looking for synonyms for Financial.
http://thesaurus.com/browse/financial

This sort of thing isn't trivial, but if you're going to do this on a quasi-regular basis, it can be useful. Especially as you start to add your own words to a category.

I have done something like this for search terms and help when people were dealing with SIC Classifications and NAICS breakdowns.

Good luck.

 

[Norie]

I thought the amount of date might be an issue.

Can you give more details of the data you are working with and what the 'rules' are for deciding the category?

In your example it seemed like the category 'Financial' was decided by there being 3 matches.

Is that the basic rule?

Sorry for all the questions, just trying to get an understanding of the whole thing.

By the way, you probably don't want gave a separate table for each category.

Perhaps a table with at least 2 fields, one for terms and one for category.

PS How many records do you have?

 

[simoran2]

I thought the amount of date might be an issue.

Can you give more details of the data you are working with and what the 'rules' are for deciding the category?

In your example it seemed like the category 'Financial' was decided by there being 3 matches.

Is that the basic rule?

Sorry for all the questions, just trying to get an understanding of the whole thing.

By the way, you probably don't want gave a separate table for each category.

Perhaps a table with at least 2 fields, one for terms and one for category.

PS How many records do you have?

Hi Norie

Don't apologise for the questions - I appreciate the help!

I have around 60k rows of data at the moment but this would be increasing over time.

We ask our customers through an sms survey how we could improve their experience with our business. The response from the customer is a free text output field of various length.

The rules would be very basic to start with i.e.

1. Start by predefining the key words for each category (maybe 15 words in each category). There will be multiple categories i.e. Financial, Device, Agent, Competitor etc...

2. Look at the customers free text response. If the customers comment contains at least one of the words from a category, classify the response with that category.

One of the challenges comes from the fact that a customers response could contain multiple categories i.e.

"My bill is to expensive and my handset is broken".

Bill would be financial whilst handset would be device related. This is the reason why I thought of building each category as a separate table. I would then use a count of how many of each category is mentioned in the free text to help establish primary and secondary drivers of the comment.

So assuming I set up the categories as separate tables, what is the expression needed to look up each word of the verbatim, cross with the category lookup table and apply the associated category?

Hope this gives some context - thanks again for your help with this

 

[simoran2]

I think you'll be looking at some sort of thesaurus --- customized to your categorizations.

You may get some help for set up from locations like thesaurus.com. Here's an example looking for synonyms for Financial.
http://thesaurus.com/browse/financial

This sort of thing isn't trivial, but if you're going to do this on a quasi-regular basis, it can be useful. Especially as you start to add your own words to a category.

I have done something like this for search terms and help when people were dealing with SIC Classifications and NAICS breakdowns.

Good luck.

Hi Jack - thanks, i'll certainly take a look at this to help build the categories

 

[jackd]

Further to my earlier comment, I would agree with Norie. I would NOT have a separate table per category. 1 table should be sufficient in my view.

Good luck

 

[simoran2]

Thanks Jackd

Any thoughts on the expression i need to use to do this? I am thinking its something like instr([customer.comment],[Lookuplist])

 

[Norie]

Is using code for this an option?

The reason I ask is because I think it could be done with queries but it might actually be easier to do with code.