Thursday, November 25, 2010

Confidence Interval in Excel To Find Customer Preferences

Using the Confidence

Interval in Excel To

Find Customer Preferences



This article will show you exactly how to calculate a 95% Confidence Interval in Excel of the proportion of customers on a commercial web site who prefer to pay with a credit card instead of Paypal. The 95% Confidence Interval is the interval for which we are 95% sure that the true proportion of credit card-preferring customers lies within.


The larger the sample size, the more accurate and smaller the confidence interval will be. We are taking a random sample of 1,000 Internet shoppers on this e-commerce web site.


The advantages of statistical analysis in Excel to solve business statistics problems is that most problems can be solved in just one or two steps and there is no more need to look anything up on Normal Distribution tables.


The 95% Confidence Interval calculated here is a Confidence Interval for Proportion, not Means. A Confidence Interval for Means uses an entirely different set of statistical formulas. Another article in this blog shows exactly how to calculate a Confidence Interval Means. Here is the problem:


Problem: A random sample of 1,000 Internet shoppers on one web site was taken. 70% preferred to pay with a credit card. 30% preferred to pay with PayPal. Determine the 95% Confidence Interval for the proportion of the general population of shoppers on that web site that prefers to pay with a credit card.

Before we begin solving this problem, we need to know about creating Confidence Intervals in Excel. Here is a brief lesson: 


Confidence Interval of a Population Proportion

Creating a Confidence Interval for a population's proportion is very similar to creating a Confidence Interval for a population's mean. The only real difference is how the standard error is calculated. Everything else is the same.



First, the difference between using sampling to estimate a population mean and using sampling to estimate a population proportion will be explained below:


Mean Sampling vs. Proportion Sampling

What determines whether a mean is being estimated or a proportion is being estimated is the number of possible outcomes of each sample taken.


Proportion samples have only two possible outcomes
. For example, if you are comparing the proportion of click-through visitors in two different PPC ads who converted (purchased), each sample has only two possible values; the visitor sampled either converted (purchased) or they didn't.


Mean samples have multiple possible outcomes
. For example, if you are comparing the mean sales of salespeople in two different cities, each salesperson's sales sampled can have numerous values.


Below is a description of how to calculate a Confidence Interval for a population's proportion. Note that everything is almost the same as the calculation of the Confidence Interval for a mean, except sample standard error.



Levels of Confidence and Significance

Level of Significance
, α ("alpha"), equals the maximum allowed percent of error. If the maximum allowed error is 5%, then α = 0.05.


Level of Confidence
is selected by the user. A 95% Level is the most common. A 95% Confidence Level would correspond to a 95% Confidence Interval of the Proportion.


This would state that the actual population Proportion has a 95% probability of lying within the calculated interval. A 95% Confidence Level corresponds to a 5% Level of Significance, or α = 0.05. The Confidence Level therefore equals 1 - α.



Population Proportion vs. Sample Proportion

Population Proportion
= µp = p (This is what we are trying to estimate)


Sample Proportion
= pavg



Standard Deviation and Standard Error

Standard Deviation
is not calculated during the creation of Confidence Interval for a population proportion.


Standard Error
is an estimate of population Standard Deviation from data taken from a sample. Sample Standard Error will be an estimate taken from the sample proportion, pavg, and sample size, n. This is the major difference between calculating a Confidence Interval for a proportion and for a mean. Binomial distribution rules apply to proportions because a proportion sample has only two possible outcomes, just like a binomial variable.



Sample Standard Error of a Proportion
= σpavg = SQRT(p * q / n) ≈ spavg

= spavg = SQRT ( pavg * qavg / n )

p
= Population proportion - This is the unknown that will be estimated with a Confidence Interval


q = 1 - p


n
= Sample size


p
avg = Sample proportion


q
avg = 1 - pavg




Region of Certainty vs. Region of Uncertainty

Region of Certainty
is the area under the Normal curve that corresponds to the required Level of Confidence. If a 95% percent Level of Confidence is required, then the Region of Certainty will contain 95% of the area under the Normal curve. The outer boundaries of the Region of Certainty will be the outer boundaries of the Confidence Interval.


The Region of Certainty, and therefore the Confidence Interval, will be centered about the mean. Half of the Confidence Interval is on one side of the mean and half on the other side.


Region of Uncertainty
is the area under the Normal curve that is outside of the Region of Certainty. Half of the Region of Uncertainty will exist in the right outer tail of the Normal curve and the other half in the left outer tail. This is similar to the concept of the "two-tailed test" that is used in Hypothesis testing in further sections of this course. The concepts of one and two-tailed testing are not used when calculating Confidence Intervals. Just remember that the Region of Certainty, and therefore the Confidence Interval, are always centered about the mean on the Normal curve.





Relationship Between Region of Certainty, Uncertainty, and Alpha

The Region of Uncertainty corresponds to α ("alpha"). If α = 0.05, then that Region of Uncertainty contains 5% of the area under the Normal curve. Half of that area (2.5%) is in each outer tail. The 95% area centered about the mean will be the Region of Certainty. The outer boundaries of this Region of Certainty will be the outer boundaries of the 95% Confidence Interval.


The Level of Confidence is 95% and the Level of Significance, or maximum error allowed, is 5%.



Z Score

Z Score is the number of Standard Errors from the mean to outer right boundary of the Region of Certainty (and therefore to the outer right boundary of the Confidence Interval).


Standard Errors are used and not Standard Deviations because sample data is being used to calculate the Confidence Interval.


Z Score is calculated by the following Excel function:


Z Score(1-α) = NORMSINV (1 - α/2) - This will be discussed shortly.





Excel Functions Used When Calculating Confidence Interval for a Population Proportion

Note that Excel functions STDEV and AVERAGE are not used when working with proportions. The CONFIDENCE function is not used either.


Excel functions that are used are:


COUNT
(Highlighted block of cells) = Sample size = n

This counts the number of cells in highlighted block

NORMSINV
(1 - α/2) = Z Score(1 - α)


Number of Standard Errors from mean to boundary of Confidence Interval. Note that (1 - α/2)

= the entire area in the Normal curve to the left of outer right boundary of the Region of Certainty, or Confidence Interval. This includes the entire Region of Certainty and the half of the Region of Uncertainty that exists in the left tail.


For example:

Level of Confidence = 95% for a 95% Confidence Interval


Level of Significance = 5% (α = 0.05)


1 - α = 0.95 = 95%


Z Score95% = NORMSINV (1 - α/2) = NORMSINV (1 - .05/2) = NORMSINV(1 - 0.025)


Z Score95% = NORMSINV (0.975) = 1.96


The outer right boundary of the 95% Confidence Interval, and the Region of Certainty, is 1.96 Standard Errors from the mean. The left boundary is the same distance from the mean because the Confidence Interval is centered about the mean.



Formula for Calculating Confidence Interval Boundaries from Sample Data for a Population Proportion

Confidence Interval Boundaries = Sample proportion +/- Z Score(1-α) * Sample Standard Error


Confidence Interval Boundaries = pavg +/- Z Score(1-α) * spavg


Sample Proportion = pavg


Z Score(1 - α) = NORMSINV (1 - α/2)


Sample Standard Error of a Proportion = σpavg ≈ spavg = SQRT ( pavg * qavg / n )


Sample size = n = COUNT (Highlighted block of cells containing samples)


Confidence Interval Boundaries = pavg +/- Z Score(1-α) * spavg


***************************************************
Before we use any statistical test that relies upon the underlying population data (from which samples are drawn) to be normally distributed, we must first test the data for normality. The Confidence Interval is an interval in the Normal curve. This requires that the underlying population data be Normally distributed.
***************************************************



Always Test for Normality First

Normality tests should be performed on the independent random sample of Internet shoppers. This blog has numerous articles about how to perform normality testing and nonparametric testing if the data is not normally distributed.


***************************************************

Now we're ready to solve the problem and create a Confidence Interval in Excel.



Problem: Determine Confidence Interval of Internet Shoppers Who Prefer to Pay By Credit Card Based Upon Sample Data


A random sample of 1,000 Internet shoppers on one web site was taken. 70% preferred to pay with a credit card. 30% preferred to pay with PayPal. Determine the 95% Confidence Interval for the proportion of the general population of Internet shoppers on that web site that prefers to pay with a credit card.


Level of Confidence = 95% = 1 - α


Level of Significance = α = 0.05


Sample Size = n = 1,000


Sample Proportion = pavg = 0.70


qavg = 1 - pavg = 0.30


Sample Standard Error of a Proportion = σpavg ≈ spavg = SQRT ( pavg * qavg  / n )


spavg = SQRT ( 0.70 * 0.30 / 1,000 ) = 0.014


Z Score(1 - α) = Z Score95% = NORMSINV (1 - α/2)


= NORMSINV (1 - 0.025) = NORMSINV (0.975) = 1.96


Width of Half the Confidence Interval = Z Score(1-α) * spavg

= 1.96 * 0.014 = 0.0274


Confidence Interval Boundaries = pavg  +/- Z Score(1-α) * spavg

= 0.70 +/- (1.96) * (0.014)


0.70 +/- 0.0274 = 0.6726 to 0.7274 = 67.26% to 72.74%

We can be 95% certain that the percentage of Internet shoppers on this e-commerce web site who prefer to pay with a credit card instead of Paypal is between 67.26% and 72.74%.




Click Image To See Larger Version



*****************************************

Here is a link to this article if you wish to link to it:

Confidence Interval in Excel To Find Customer Preferences


If You Like This, Then Share It...
Confidence Interval in Excel To Find Customer Preferences Confidence Interval in Excel To Find Customer Preferences Confidence Interval in Excel To Find Customer Preferences Confidence Interval in Excel To Find Customer Preferences Confidence Interval in Excel To Find Customer Preferences Confidence Interval in Excel To Find Customer Preferences Confidence Interval in Excel To Find Customer Preferences


Hypothesis Test in Excel to Statistical Test Your Headlines

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Wednesday, November 24, 2010

Hypothesis Test in Excel to Test Headlines

Using the Hypothesis

Test in Excel To Test

Headlines


This article will show exactly how to perform a Hypothesis Test in Excel to test whether one headline performs better than another headline in a pay-per-click Internet marketing campaign. This Hypothesis Test will be testing the Null Hypothesis that both headlines perform at the same level.

Specifically, we will show how to use Excel to perform a One-Tailed test, a Two-Sample test , Unpaired, Hypothesis Test of Proportion to determine whether Headline 2 converts better than Headline 1 when used in the same PPC ad. Different types of Hypothesis Tests such as a Paired test, a Two-tailed test, and a Hypothesis Test of Mean are demonstrated in other articles of this blog.

The advantages of statistical analysis in Excel to solve business statistics problems is that most problems can be solved in just one or two steps and there is no more need to look anything up on Normal Distribution tables.

Here is the problem:


Problem: An Internet marketing manager is testing the effectiveness of two different headlines when used in the same pay-per-click ad. The Internet marketing manager is trying to determine whether Headline 2 is better than Headline 1. Headline 1 was inserted into a PPC ad. Running this Headline 1/ad combination resulted in 80 click-throughs. 52 of those 80 click-through visitors converted (purchased).

Headline 2 was run with the same PPC ad under the same conditions and approximately the same number of ad impressions. Running this combination of Headline 2/ad resulted in 90 click-throughs, of which 63 converted. Determine within an error of 1% whether Headline 2 converted better than Headline 1.

Here are the Conversion Data for Headlines 1 and  2:


Headline 1


p1avg = Sample proportion 1 = 52 / 80 = 0.65 converted


q1avg = 1 - p1avg = 1 - 0.65 = 0.35 not converted


n1 = Sample size 1 = 80



Headline 2

p2avg = Sample proportion 2 = 63 / 90 = 0.70 converted


q2avg = 1 - p2avg = 1 - 0.70 = 0.30 not cconverted


n2 = Sample size 2 = 90



α = 0.01 = alpha = Level of significance, therefore there is a 1% max chance of error.

This results in a 99% Level of Certainty Required



Before we begin solving this problem, we need to know whether we are dealing with normally distributed data. If the data is not normally distributed, we have to use nonparametric statistical tests to solve this problem.



 Always Test for Normality First

Normality tests should be performed on both Headline 1 and Headline 2 conversion data. Both data sets must be normally distributed to perform the well-known hypothesis test that is based upon the underlying data being normally distributed. This blog has numerous articles about how to perform normality testing and nonparametric testing if the data is not normally distributed.





The MOST Important Step

Determine What Type of Hypothesis

Test You Will Perform




1) Hypothesis Test of Mean or Proportion?


We know that this is a test of proportion and not mean because because each individual sample taken has only 2 possible values: the headline/ad combination either converted the click-through visitor or it didn't.




2) One or Two-Tailed Hypothesis Test?


We know that this is a one-tailed test because we are trying to determine if Headline 2 has a higher conversion rate than Headline 1, not whether conversion rates are merely different, which would be a two-tailed test.




3) One or Two-Sample Hypothesis Test?


We know that two samples must to be taken because no data is initially available.




4) Paired or Unpaired Hypothesis Test?

This is unpaired data because groups are sampled independently. Hypothesis tests of proportion cannot be applied to paired data.  


In this case, we are performing a One-Tailed, Two-Sample, Unpaired Hypothesis Test of Proportion to determine whether Headline 2 has a higher conversion rate than Headline 1. We will do this test in Excel. It is extremely important to establish the type of Hypothesis test.

Each type of Hypothesis test uses a slightly (or very) different methodology and set of formulas. The previous 2 articles in this blog with provide examples of Hypothesis Tests of Mean. You will notice that the formulas for Mean Hypothesis Tests are completely different, but the 4-step method always works for any type of Hypothesis Test.



The Four-Step Method That Solves ALL Hypothesis Tests


This problem can be solved using the standard four-step method for Hypothesis testing.


Step 1 - Create the Null and Alternate Hypotheses

The Null Hypothesis normally states that both populations sampled are the same. If the proportions p1avg and p2avg, conversion rates from each headline are the same, then p1avg - p2avg = 0.


The Null Hypothesis states that both headlines the same conversion rates, which is equivalent to:


Null Hypothesis = H0 = p1avg - p2avg = 0


The Alternate Hypothesis states that Headline 2 is better than Headline 1, which is equivalent to:


The Alternate Hypothesis, which states that p2avg is greater than p1avg, is as follows:


Alternate Hypothesis, H1 = p1avg - p2avg is less than 0


****************************


For this one-tailed test, the Alternative Hypothesis states that if the value of the distributed variable (p1avg - p2avg) is less than the value stated by the Null Hypothesis, the Region of Uncertainty will be in the outer left tail.


Note - the Alternative Hypothesis determines whether the Hypothesis test is a one-tailed test or a two-tailed test as follows:


One-tailed test - (Value of variable) is greater than OR is less than (Constant)


Two-tailed test - (Value of variable) does not equal (Constant)




Step 2 - Map the Normal Curve

We now create a Normal curve showing a distribution of the same variable that is used by the Null Hypothesis, which is (p1avg - p2avg). The mean of this Normal curve will occur at the same value of the distributed variable as stated in the Null Hypothesis.


Since the Null Hypothesis states that p1avg - p2avg = 0, the Normal curve will map the distribution of the variable (p1avg - p2avg) with a mean of (p1avg - p2avg) = 0


This Normal curve will have a standard error that is calculated as the standard error of a sampled proportion is normally calculated, as follows:


The standard error of the difference of proportions is:


sp1avg-p2avg =

= SQRT [ pweighted * qweighted ( 1 / n1 + 1 / n2 ) ]



pweighted = (n1*p1avg + n2*p2avg) / (n1 + n2)

= (n1*p1avg + n2*p2avg) / (n1 + n2)


= [ (80 * 0.65) + (90 * 0.70) / (80 + 90) ]


= 0.676



qweighted = 1 - pweighted

= 1 - pweighted


= 1 - 0.676


= 0.324


Standard Error = sp1avg-p2avg =

= SQRT [ pweighted * qweighted ( 1 / n1 + 1 / n2 ) ]


= SQRT [ ( 0.676 * 0.324 ) * ( 1 / 80 + 1 / 90 ) ]


= 0.0719


Hypothesis Test in Excel to Statistical Test Your Headlines


Click On Image To See Larger Version

Step 3 - Map the Region of Certainty




The problem requires a 99% Level of Certainty so the Region of Certainty will contain 99% of the area under the Normal curve.


We know that this problem uses a one-tailed test with the Region of Uncertainty entirely contained in the outer left tail.


The Region of Uncertainty contains 1% of the total area under the Normal curve. The entire 99% Region of Certainty lies to the right of the 1% Region of Uncertainty, which is entirely contained in the outer left tail.


****************************************


We need to find out how far the boundary of the Region of Certainty is from the Normal curve mean. Calculating the number of standard errors from the Normal curve mean to the outer boundary of the Region of Certainty in the right tail for a one-tailed test is done as follows:


z99%,1-tailed = NORMSINV(1 - α) = NORMSINV(0.99) = 2.33


Excel Note - NORMSINV(x) = The number of standard errors from the Normal curve mean to a point right of the Normal curve mean at which x percent of the area under the Normal curve will be to the left of that point.


Additional note - For a one-tailed test, NORMSINV(x) can be used to calculate the number of standard errors from the Normal curve mean to the boundary of the Region of Certainty whether it is in the left or the right tail.


The Region of Certainty extends to the left of the Normal curve mean of (p1avg - p2avg) = 0 by 2.33 standard errors.


One standard error = sp1avg-p2avg = 0.0719, so:
2.33 standard errors = (2.33) * (0.0719) = 0.1675


The outer left boundary of the Region of Certainty has the value
= µ - z99%,1-tailed * s(p1avg-p2avg)

which equals 0 - (2.33) * (0.0719) = - 0.1675

This point (-0.1675) is 2.33 standard errors to the left of the Normal curve mean of (p1avg - p2avg) = 0

This point (-0.1675) is the left boundary of the 99% Region of Certainty on the Normal curve.


Hypothesis Test in Excel to Statistical Test Your Headlines
Click Image To See Larger Version


Step 4 - Perform Critical Value and p-Value Tests


a) Critical Value Test


The Critical Value Test is the final test to determine whether to reject or not reject the Null Hypothesis. The p Value Test, described below, is an equivalent alternative to the Critical Value Test.


The Critical Value test tells whether the value of the actual variable, p1avg - p2avg, falls inside or outside of the Critical Value, which is the boundary between the Region of Certainty and the Region of Uncertainty.


If the actual value of the distributed variable, p1avg - p2avg, falls within the Region of Certainty, the Null Hypothesis is not rejected.


If the actual value of the distributed variable, p1avg - p2avg, falls outside of the Region of Certainty and, therefore, into the Region of Uncertainty, the Null Hypothesis is rejected and the Alternate Hypothesis is accepted.


In this case, the actual value of the variable, p1avg - p2avg =
= 0.65 - 070 = -0.05


The actual value of the variable (p1avg - p2avg) = -0.05 and is therefore inside the Critical Value (-0.1675), which is the boundary between the Regions of Certainty and Uncertainty.


The actual value of the variable (p1avg - p2avg) is inside the Region of Certainty and therefore inside the Critical Value.


We therefore do not reject the Null Hypothesis and state that the Null Hypothesis is not disproven that both headlines have the same conversion rates..


Hypothesis Test in Excel to Statistical Test Your Headlines

Click On Image To See Larger Version


b) p Value Test

The p Value Test is an equivalent alternative to the Critical Value Test and also tells whether to reject or not reject the Null Hypothesis.

The p Value equals the percentage of area under the Normal curve that is in the tail outside of the actual value of the variable (p1avg - p2avg).

For a one-tailed test, if the p Value is larger than α, the Null Hypothesis is not rejected.

For a two-tailed test, if the p Value is larger than α/2, the Null Hypothesis is not rejected.

For a one-tailed test, the Region of Uncertainty is contained entirely in one tail. Therefore the curve area contained by the Region of Uncertainty in that tail equals α.

For a two-tailed test, the Region of Uncertainty is split between both tails. Therefore the curve area contained by the Region of Uncertainty in that tail equals α/2.

The p Value for the actual value of the distributed variable, which in this case is greater than the mean (falls to the left of the mean in the left tail), is:


p Value(p1avg-p2avg) =
= NORMSDIST( [ (p1avg - p2avg) - µ ] / s(p1avg - p2avg) )


**************************************

Excel note - NORMSDIST(x) calculates the total area under the Normal curve to the LEFT of the point that is x standard errors to the right of the Normal curve mean. If we are calculating the area to the RIGHT of this point, we would use 1 - NORMSDIST. This would be as follows:

p Value(p1avg-p2avg) =


= 1 - NORMSDIST( [ (p1avg - p2avg) - µ ] / s(p1avg - p2avg) )


******************************************************

Since we are calculating the area to the left of this point, we use:

p Value(p1avg-p2avg) =


= NORMSDIST( [ (p1avg - p2avg) - µ ] / s(p1avg - p2avg) )


= NORMSDIST((-0.05 - 0 ) / 0.0719) = NORMSDIST(-0.05/0.0719) = 0.24


The p Value (0.24) is greater than α (0.01), so the Null Hypothesis is not rejected.


For a one-tailed test- When the p Value is greater than α, the actual value of the distributed variable falls inside the Region of Certainty and the Null Hypothesis is not rejected.


This is the case here.

Hypothesis Test in Excel to Statistical Test Your Headlines

Click Image To See Larger Version



*****************************************

Here is a link to this article if you wish to link to it:

Using the Hypothesis Test in Excel to Find Which Headline Works Best In Your PPC Ads


If You Like This, Then Share It...
Hypothesis Test in Excel to Statistical Test Your Headlines Hypothesis Test in Excel to Statistical Test Your Headlines Hypothesis Test in Excel to Statistical Test Your Headlines Hypothesis Test in Excel to Statistical Test Your Headlines Hypothesis Test in Excel to Statistical Test Your Headlines Hypothesis Test in Excel to Statistical Test Your Headlines Hypothesis Test in Excel to Statistical Test Your Headlines


Hypothesis Test in Excel to Statistical Test Your Headlines

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic