Find repeated complain

[pdida8]

Hi. I'm new on excel and I'm trying to figure out a formula which would help me doing the following analyses.
I have a list of customers, who complain on our Call Center from different cities.
The list has these data- columns:
1. user (customer)
2. type of complain (ag. damaged equipment),
3. closing activity (eg. changed the equipment),
4. segment type (business or retail),
5. service type (we have 6 type of services, and
6. city
Now, on the column 1, one customer can have more than 1 complain within specified reporting period and with the same type of complains, therefore we want to do the analysis as following:
Report for same customer with the same type of complaints and at the end have:
- Number of same customers with same issue = 1 complain,
- Number of same customers with same issue = 2 repetitive complaints
- Number of same customers with same issue = 3 repetitive complaints
- Number of same customers with same issue = 4 repetitive complaints
- Number of same customers with same issue = 5+ repetitive complaints,

Customer	Complain type	Closure activity	Segment	Service	City
# 1	Wrong price	Credit note	Retail	Service #1	Berlin
# 2	Damaged product	Refunded	Retail	Service #2	Dortmund
# 3
# 4

If any one could help me out to solve this, I would be more than thankful.
Thanks in advance

 

[Herakles]

Do you want to know the numbers (stats) or which customers fall under each of the number of repetitive complaints or both?

How are you defining what a repetitive complaint is?
Segment and City are characteristics of the customer and not the complaint so you are left with
Complaint Type, Closure Activity and Service.

Is it just repetitive complaints from the same customer or repetitive complaints of a particular type from a customer?
Does the type of service that you deploy to rectify the complaint come into it?

 

[pdida8]

Thanks for fast reply.
Repetitive complain is if one customer is filing same complain more than one time, within specified period (example 1 month).
Due to data protection i can't attach data, but let's say I'm customer, I purchased a TV and within 1 month I'm complaining 5 times that TV colors are not accurate and 1 time that TV DHMI port is not working. So this would be,:
Complaints with 5 repetitive tickets = 1 or 83%
Complaints with 1 ticket = 17%
Total tickets 6

And yes, segment and city are characteristics of customer not complaint, but at the end I would like to know for example for customers who had let say 3 same repetitive tickets, how were they closed (activity) and from which cities are complaints coming most.

 

[Herakles]

So how do you record what the complaint is about?

If the complaint is that 'TV colors are not accurate' how would this be recorded?

Is it down to the person taking the complaint as to how the problem is described.

Could the exact same problem be described in different ways?

These are Service Calls rather than complaints.

Are you able to upload some anonymized data?

 

[pdida8]

Hi Herakles,
We have predefined type of opening categories based on type complaints with X service (this is not an issue). It can happen sometimes that for the same problem to be described on different ways (of different ticket opening category), that's why at the end we have closed activity which again are predefined and will be selected based on what was concluded by 2nd level of support.
These are complaints about services and I'm attaching some anonymized. Let me know if this is enough.

Customer	Complain type	Closure activity	Segment	Service	City
James 1	Slow speed	Router re-configuration	Retail	Internet	Berlin
John 2	Browsing problem	General problem	Retail	Internet	Dortmund
Johannes	Channel problem	No intervention needed	Retail	Cable TV	Munich
Anne 0	Invoice complain	Credit Note	Retail	Mobile telephony	Hamburg
James 1	Slow speed	Cable modem changed	Retail	Internet	Berlin
John 2	Browsing problem	LAN problem	Retail	Internet	Dortmund
John 2	VOD problem	Restore factory settings	Retail	Cable TV	Dortmund
Johannes	Cable modem down	General problem	Retail	Internet & Cable TV	München
Sound Studio	Browsing problem	Router re-configuration	Business	Internet	Köln
Sound Studio	Browsing problem	No intervention needed	Business	Internet	Köln
Sound Studio	Browsing problem	LAN problem	Business	Internet	Köln
Sound Studio	Slow speed	Cable modem changed	Business	Internet	Köln
Art Print	Slow speed	Router re-configuration	Business	Internet	Bremen
Art Print	Slow speed	LAN problem	Business	Internet	Bremen
Art Print	Browsing problem	Cable modem changed	Business	Internet	Bremen
Shawn 1	Cable modem down	Change of splitter	Retail	Internet & Cable TV	München
Anthony	CatchUP TV problem	Provisioned for VAS	Retail	Cable TV	Stuttgart
Marta	Disconnections	Connector changed	Retail	Internet	Dresden
Brigite	Wiffi problem	SSID & Password changed	Retail	Internet	Leipzig
Anthony	Browsing problem	General problem	Retail	Internet	Stuttgart
Arthur	Outbound SMS problem	B number absent	Business	Mobile telephony	Duisburg
Arthur	Invoice complain	Credit Note	Business	Mobile telephony	Duisburg
Arthur	VOD problem	Restore factory settings	Business	Cable TV	Duisburg
Arthur	PPV Problem	Provisioned for VAS	Business	Cable TV	Duisburg
Arthur	Slow speed	Cable modem changed	Business	Internet	Duisburg
Arthur	Slow speed	Router re-configuration	Business	Internet	Duisburg

 

[Herakles]

Have a look at this workbook.

It may give you an idea how it can be done.

complaints.xlsx

 

[pdida8]

Yes then I can add count iff in as additional calculation on top. Thank you very much Herakles