Help, probably with a pareto

halesowenmum · May 10, 2017

Hi

As suggested by Oldbrewer, I need to put the following into a pareto format as it is likely the best way to show what I need to show (described below my data table):

[TABLE="width: 858"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]AL[/TD]
[TD="align: center"]AM[/TD]
[TD="align: center"]AN[/TD]
[TD="align: center"]AO[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[TD="align: center"]AR[/TD]
[TD="align: center"]AS[/TD]
[TD="align: center"]AT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]This col effectively acts as the control group / benchmark figure to compare against since no contact was made at all by the company[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Contacted successfully by phone and then had service call at home[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]Contacted successfully by telephone only (no service call at home)[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]Not successfully contacted by phone (and no service call)[/TD]
[TD="align: right"] [/TD]
[TD="align: right"]Total[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD][/TD]
[TD]Contact type (Total No.)[/TD]
[TD] [/TD]
[TD="align: center"]17[/TD]
[TD] [/TD]
[TD="align: center"]66[/TD]
[TD="align: center"][/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]105[/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD] [/TD]
[TD]Contact type as % Total No.[/TD]
[TD][/TD]
[TD="align: center"]16%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]63%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]21%[/TD]
[TD="align: center"]100%[/TD]
[/TR]
[TR]
[TD]118[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]119[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]No.[/TD]
[TD="align: right"]%[/TD]
[TD="align: right"]No.[/TD]
[TD="align: right"]%[/TD]
[TD="align: right"]No.[/TD]
[TD="align: right"]%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD] [/TD]
[TD]Only had to call customer the once[/TD]
[TD]

11

[/TD]
[TD]

10%

[/TD]
[TD]

74

[/TD]
[TD]

70%

[/TD]
[TD]

13

[/TD]
[TD]

12%

[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD] [/TD]
[TD]Had to call customer back one further time[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]122[/TD]
[TD] [/TD]
[TD]Had to call customer back more than once[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

What I need to determine is:
Compared to those who we didn't manage to get in touch with which met our goals the best?
- Our goal is firstly that no customer has to call back to our customer care centre
- That if they do call back it's better if they only have to make one call
- That if they do call back it's least favourable that they have to make more than one call back to us.

The assumptions we're testing really are:
-- We might expect that people who had a call and a home service call would be least likely to need to ring us once, twice, or two or more times
-- We might expect that across the board regardless of method of contact that these results would be much better than the ones for the 'control group' who had no contact at all.

Which of the contact methods - successful telephone contact only or successful telephone contact and a service call to the customer's home - was the most effective?

For whatever type of chart is the best to use do I need to set out the data above in a different arrangement; what steps do I need to follow to get going with creating the recommended chart.

Many thanks indeed - fingers crossed I can find the answer as I've got to meet with someone at 9am tomorrow to show them the results!! Obviously, if we find that making proactive calls and sending out engineers to the customer's home where it was felt to be necessary doesn't result in less calls back to the call centre, then the project will not be carried on - it's just I really am not sure (even when looking at pareto templates) which data I need to put where to get the answers that I need.

AhoyNC · May 10, 2017

A pareto chart is usually used to show the type and number of defects that happen within a service or product.
Since you are looking at which contact method is more effective (or if they are the same), I would be more inclined to look at a "two sample test about proportions".
Here are a couple of reference links (you can google for more info).

Hypothesis Test: Difference in Proportions
How to Compare Two Population Proportions - dummies

halesowenmum · May 10, 2017

Thank you. I'll have a look at this at this.

Oh, just had a look.... hmmm...... I couldn't make head nor tail of that sadly...

I just need some idea of what chart to use, to know whether my data is in the right physical layout to enable the chart to work - would a pivot chart do that job?

If necessary I'd accept not returning results for every single variant in one chart - it could be split depending on how many times the customer phoned rather than trying to analyse all the times in the one chart.

AhoyNC · May 10, 2017

Maybe a contingency table. Where you show the nimber of times a customer has to call back (0 times, 1 time, 2 or more times).
The example below just has bogus data, but should give you the idea.
You could then do a bar chart or stacked bar chart of data. In the example below to get a graph just highlight the range A2 to D5 and insert a bar graph.

Excel Workbook