# 'How to analyse this data' question...

#### halesowenmum

##### Active Member
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:

 AI AO AP AQ AR 114 Had a home service call Had a proactive cust. service call only Were uncontactable 115 No. 17 89 23 106* 116 % 16% =AO115/AR115 84% =AP115/AR115 22% =AQ115/AR115 100% 117 Call breakdown as % of the 105 total 119 Customers who did not have to call us back after the call 13% =AO112/AR115 70% =Z112/AR115 12% =AF112/AR115 120 Customers who had to call back one time 6% =AJ112/AR115 23% =AA112/AR115 7% =AF112/AR115 121 Customers who had to call back two or more times 1% =AO115/AR115 4% =AO115/AR115 2% =AO115/AR115 122 Readmission breakdown by individual contact type (17, 89, 23) 124 Customers who did not have to call us back after our call 82% =AI112/AO115 55% =AE112/AP115 57% =AF112/AQ115 125 Customers who had to call back one time 35% =AJ112/AO115 27% =AA112/AP115 30% =AG112/AQ115 126 Customers who had to call back two or more times 6% =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 !) 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
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
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
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

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.

#### halesowenmum

##### Active Member
Is there any kindly soul who can assist?

#### oldbrewer

##### Well-known Member
 phone calls 179 no reply 13 sample size 166 response summary no problems 143 # broken 2 # noisy 18 # do pareto analysis burning smell 3 # TOTAL 166 PROBLEMS 23 resolved in initial phone call 12 resolved by second phone call 4 # resolved by home visit 5 # do pareto analysis resolved by third phone call 1 # unresolved 1 # ROOT CAUSE ANALYSIS misuse 8 # not following instructions 11 # chart on a bar chart item faulty 4 # TOTAL 23 TIME TO RESOLVE ISSUE less than 1 hour 6 # 1 to 2 hours 8 # pareto 3 to 4 hours 4 # 5 to 24 hours 3 # 25 to 72 hours 1 # > 72 hours 1 # 23

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

Replies
1
Views
211
Replies
15
Views
357
Replies
0
Views
155
Replies
6
Views
117
Replies
1
Views
190

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.

### Which adblocker are you using?

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

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