'How to analyse this data' question...

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Hi all

The data I need help with is to analyse the data around a trial of a new way of running customer services where two initiatives were trialled where customers were given a telephone call proactively by our company to see if they had any issues that needed resolving with the aim of resolving them over the phone if possible (all the customers called or not called successfully = 106, contacted successfully by telephone only (89) OR contacted successfully by telephone and also given a free home service call (17) - and there were 23 who the customer service officers were unable to get in contact with when they called:

AIAOAP
AQAR
114Had a home service callHad a proactive cust. service call onlyWere uncontactable
115No.178923106*
116%16%
=AO115/AR115
84%
=AP115/AR115
22%
=AQ115/AR115
100%
117Call breakdown as % of the 105 total
119Customers who did not have to call us back after the call13%
=AO112/AR115
70%
=Z112/AR115
12%
=AF112/AR115
120Customers who had to call back one time6%
=AJ112/AR115
23%
=AA112/AR115
7%
=AF112/AR115
121Customers who had to call back two or more times1%
=AO115/AR115
4%
=AO115/AR115
2%
=AO115/AR115
122Readmission breakdown by individual contact type (17, 89, 23)
124Customers who did not have to call us back after our call82%
=AI112/AO115
55%
=AE112/AP115
57%
=AF112/AQ115
125Customers who had to call back one time35%
=AJ112/AO115
27%
=AA112/AP115
30%
=AG112/AQ115
126Customers who had to call back two or more times6%
=AK112/AO115
4%
=AB112/AP115
9%
=AH112/AQ115

<tbody>
</tbody>

106 is the 89 and the 17 added together which delivers the 106 total - the number for those who were called but no one was in is essentially for information only (I think) - although they kind of act as a small control group ie they didn't have a contact so it would show how likely they were to need to call back if no one proactively phones them. The 106 is therefore the number of customers who were successfully contacted 89 of which had just a phone call and 17 had a phone call and a home service call.

All these refer to totalled cells elsewhere where I've counted up the different types of contact as a % of the 106. All these refer to totalled cells elsewhere where I've counted up the different types of contact as a % of the 17, 89 or 23.

The questions I want to answer (and where my mind is going a bit blank :confused:!) is around how to identify which method of contact was the most and least successful to meet this aim: for all customers contacted proactively by us to be able to compare whether not being contacted at all (Not successfully contacted(?)) or contacted by telephone only (Contacted by telephone only) or had a call and a home visit (Had home visit) which was the most and least successful method in preventing the customer from having to call back any more (Customers who did not have to call us back after our call).

As above but for those who had to call us back once and those who had to call us back two or more times what were the results based on the three different contact methods - did customers call back less, the same or more under the different contact methods.

The company wants to figure out if asking staff to follow this methodology had a significant enough effect on preventing customers having to call back that it's wise to continue it as an ongoing operational model, or if the effect wasn't much different to customers who weren't contacted at all - does it reduce customers having to call back or is it not much better than just letting them call themselves if they need to? Was the most important thing to give them a home service call (was just a phone call ineffective?).

Crikey I hope that's clear (and not as mud!) and any help really appreciated.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
do you phone the customers at random, and of those who have an issue do you - say - set up a home visit for a quarter of them, ???

what may be significant is the % needing second and third calls to resolve the situation.

maybe you could do Pareto analysis on the reasons for dissatisfaction - excel is very good for that....
 

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Hi Oldbrewer.

The absolute crux is not having them needing to come back to us at all - our telephone call on its own or our telephone call and home service call together are meant to achieve that aim.

We select customers based on the ones we hope will not need to come back to us the most - so in this case they happen to be the more elderly/vulnerable customers and that's the basis for selection.

For ALL customers successfully contacted on the phone if the phone call doesn't resolve the issue then for all those where the issue wasn't resolved, all of them would then get a home service call.

I'm not sure if what I've done so far is correct and usable to answer what I need to answer.

I'm not sure I'd know how to set out the information in order for it to be Paretoable?
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
still trying to understand the basic question - are all the customers who are phoned people who "have a problem" and have reported it to you ? And you are phoning them BACK to resolve the issue ?
 

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No. They are phoned because they fall within the group 'elderly customers' who we want to take special care of. So no there is no guarantee whether they will have an issue or won't have an issue. If they do have an issue they may not have reported it so we only learn about it when we call. We only conduct the call once.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
phone calls179
no reply13
sample size166
response summary
no problems143#
broken2#
noisy18#do pareto analysis
burning smell3#
TOTAL166
PROBLEMS23
resolved in initial phone call12
resolved by second phone call4#
resolved by home visit5#do pareto analysis
resolved by third phone call1#
unresolved1#
ROOT CAUSE ANALYSIS
misuse8#
not following instructions11#chart on a bar chart
item faulty4#
TOTAL23
TIME TO RESOLVE ISSUE
less than 1 hour6#
1 to 2 hours8#pareto
3 to 4 hours4#
5 to 24 hours3#
25 to 72 hours1#
> 72 hours1#
23

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>
 

Forum statistics

Threads
1,148,145
Messages
5,745,049
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top