ANOVA Test
Done in Excel
To Improve Marketing
The 3 ANOVA functions in Excel can be real power tools for a marketer, if you know how to use them. Let’s take a look at what they can do for you. Below is a video that will show in detail how to use all 3 types of ANOVA that are built into Excel in order to improve your pay-per-click marketing campaigns. In the video, each of the 3 ANOVA types will be applied to the same data set. The video will demonstrate how the data is input, how the ANOVA is run, and how the output is interpretted for each ANOVA type. The article below also discusses the same information. Here is the video:
Step-By-Step Video Showing How To Improve Your Pay-Per-Click Marketing Using All 3 Types of ANOVA Built Into Excel
(Is Your Sound Turned On?)
What Is ANOVA?
Marketers generally use ANOVA to tell them whether changing one element of a marketing campaign actually had an effect on the outcome. ANOVA testing should be applied only when that element being tested has at least three variations. For example, suppose you wanted to test whether a product’s color makes a difference in sales. To answer that question using ANOVA, your product would have to come in at least three colors to run an ANOVA test.
ANOVA, Analysis of Variance, tells the marketer whether or not the test output had enough variance in it to support the claim that varying the tested element actually did make a difference in the output.
ANOVA Output
As is common in most statistical problems, ANOVA’s output does not provide a definitive Yes or No answer but gives the probability that varying an input had an effect on the output. In ANOVA, this probability is called the Degree of Certainty. ANOVA testing requires a Degree of Certainty to be specified up front.
The ANOVA test answers the question of whether varying an element affected the output within a required degree of certainty. The output of an ANOVA might be explained as follows, “Yes, the variance observed in the output was large enough to conclude within 95% certainty that varying the element being tested did affect the output. We can reject the Null Hypothesis which stated that varying the tested element did not have effect on the output.”
The 3 Types of ANOVA Built Into Excel
The first type is called Single-Factor ANOVA. This is used to test only one input element. This is how the data must be arranged on the Excel spread sheet for this type analysis. The yellow-highlighted cells below are those that are selected as the input for Single-Factor ANOVA in Excel.
Excel Data Ready For Input - Single-Factor ANOVA
(Yellow-Highlighted Cells Are the Input Range)
Click On Image To See Enlarged View
Excel Output of Single-Factor ANOVA
Click On Image To See Enlarged View
The second type is called Two-Factor ANOVA Without Replication and tests two elements simultaneously. This is how the data must be arranged on the Excel spread sheet for this type analysis. The yellow-highlighted cells below are those that are selected as the input for Single-Factor ANOVA in Excel.
Excel Data Ready for Input
Two-Factor ANOVA Without Replication
(Yellow-Highlighted Cells Are the Input Range)
Click On Image To See Enlarged View
Excel Output of
Two-Factor ANOVA Without Replication
Two-Factor ANOVA Without Replication
Click On Image To See Enlarged View
The third and final type of built-in ANOVA is called Two-Factor ANOVA With Replication. This ANOVA test simply replicates a two-factor test in at least two places in order to test whether interaction between the two factors also has an affect on the output.
Excel Data Ready for Input
Two-Factor ANOVA With Replication
(Yellow-Highlighted Cells Are the Input Range)
Click On Image To See Enlarged View
Excel Output of
Two-Factor ANOVA With Replication
Click On Image To See Enlarged View
In Excel, we are going to conduct one experiment and run the output of that experiment through all three types of ANOVA so you can observe the differences in how the data is inserted and how the Excel output is interpreted in each case. The above video shows the output of this experiment being run through all 3 ANOVA tests in Excel and how the different ANOVA outputs are interpreted.
The ANOVA Test We Ran
In a nutshell, the test is conducted as follows: we are testing ads in a pay-per-click campaign to determine whether varying the headline, ad text, or interaction between headline and ad text affects the click-through rate. Click-Through Rate (CTR) of an ad equals the number of ad impressions divided by the number of clicks that the ad generated.
We will use Excel to apply all three types of ANOVA testing to the same set of output data so that we can observe the differences in how data needs to be input into Excel for each ANOVA type and how the Excel output is interpreted for each case.
Single-Factor ANOVA will be applied to the output to determine if the varying the headlines affected CTR. Two-Factor ANOVA Without Replication will applied to the output to determine if varying headlines OR varying the ad text affected CTR. Two-Factor ANOVA With Replication will be applied to the output to determine whether variation in headlines OR ad text OR interaction between headlines and ad text affected CTR.
Once again. viewing the embedded video is probably the easiest way to quickly understand how data is input into Excel and how the Excel output is interpreted for each test.
In the video, all three types of Excel ANOVA are applied to the same set of output data.
Here Is a Summary of the Test That Was Run:
Step 1) Create 5 Sets of Ad Text For 1 Ad Group
5 different sets of ad text were created to be used within a single ad group. Ads within a single ad group should be tightly focused on a single theme, product, and landing page. We tried as much as possible for that to be the case here. One key to successful ANOVA testing to remove all other variation from the test except the specific elements being tested.
Step 2) Create 3 Sets of Headlines for That Ad Group
3 sets of headlines were created to be used in this ad group and were then combined with each of the 5 sets of ad text. This created a total of 15 possible combinations of ad text / headline.
Step 3) Run All 15 Ad Text / Headline Combinations In 1 Ad Group
Each of these 15 combinations of ad text / headline were run on both the Google and Yahoo pay-per-click search networks under similar conditions until 1 million ad impressions on each network were reached for each of the 15 ad text / headline combinations.
Step 4) Prepare the Test Results For Excel ANOVA
The output of this test was recorded as shown in the video. The linked video shows how output data must be recorded for insertion into Excel ANOVA. Each type of ANOVA in Excel requires the input data to be formatted slightly differently.
Step 5) Run All 3 Types of ANOVA On Test Results
Single-Factor ANOVA was applied to the output from Google to determine within 95% certainty whether varying the headline affects the output. Two-Factor ANOVA Without Replication was applied to the Google data to determine whether varying headline or ad text affects the output. Finally, Two-Factor ANOVA With Replication was applied to the output from both Google and Yahoo to determine whether varying headlines, ad text, or the interaction between headlines and ad text affect the output.
Step 6) Evaluate the Excel Output For Each ANOVA
The Excel output from each ANOVA test run in Excel was then interpreted. Each element being tested (headlines, ad text, and interaction) will have a separate P Value that will appear in the Excel output. The P Value for each tested element can be interpreted as being the probability of the observed variance in the output was due only to chance and not the result of varying the tested element.
Here is the output for the Single-Factor ANOVA test we ran:
Click On Image To See Enlarged View
Note that the P Value associated with Headlines = 0.001307, less that the Alpha of 0.05. We can therefore reject the Headlines Null Hypothesis and state with 95% certainty that varying the Headlines affected Click-Through Rate.
Here is the output for the Two-Factor ANOVA Without Replication test we ran:
Click On Image To See Enlarged View
Note that the P Value associated with Headlines (in Blue) is 0.00528, which is less that the Alpha of 0.05. However, the P Value associated with Ad Text (in yellow) is 0.627, which is greater than the Alpha of 0.05.
We can therefore reject the Headlines Null Hypothesis and state with 95% certainty that varying the Headlines affected the CTR. We cannot, howver, reject the Ad Text Null Hypothesis and we cannot state with 95% certainty that varying the Ad Text affected CTR.
Here is the output for the Two-Factor ANOVA With Replication test we ran:
Click On Image To See Enlarged View
Note that the P Value associated with Headlines (in Blue) is 1.18 E-06 which is less that the Alpha of 0.05. The P Value associated with Interaction Between Headlines and Ad Text (in Green) is 0.0402 which is less that the Alpha of 0.05. However, the P Value associated with Ad Text (in yellow) is 0.156, which is greater than the Alpha of 0.05.
We can therefore reject the Headlines Null Hypothesis and state with 95% certainty that varying the Headlines affected the CTR. We can also reject the Interaction Null Hypothesis and state with 95% certainty that varying the Interaction Between Headlines and Ad Text affected the CTR. We cannot, howver, reject the Ad Text Null Hypothesis and we cannot state with 95% certainty that varying the Ad Text affected CTR.
Example of How To Interpret the P Value
For example, if the P Value associated with varying the headline has a value of 0.010, it means that there is only a 1% chance that the variance in the output could have occurred entirely by random chance and not by varying the headline. In other words, there is a 99% chance the varying the headline affected the output. If the degree of certainty that you required for this ANOVA test was 95%, then alpha equals 0.05 (1 – 95% = 0.05).
In the case that the P Value associated with headline equals 0.01, which is smaller than the alpha of 0.05, you can state that you are at least 95% certain that you can reject the Null Hypothesis which states that varying the headline had no effect on the output.
The Correct Interpretation of the ANOVA Test
The correct interpretation of an ANOVA is to state whether or not you can reject the Null Hypothesis (varying the element did not affect the output), not its corollary that you can now accept the Alternate Hypothesis (varying the element did affect the output).
The Big "However"....
However, we’re in business and not in statistics class, so if the P Value associated with an element is less than alpha, go ahead and state that varying the tested element did affect output within your required degree of certainty.
Once again, the embedded video above which will show you exactly how to setup, run, and interpret all three Excel ANOVA tests on the same set of pay-per-click ad data.
In a Nutshell, Use ANOVA in Excel To Find Out If Changes You Made To Your Marketing Campaigns Really Made a Difference.
Please feel free to submit any comments you have on this article. Your input is highly valued!
If You Like This, Then Share It...
 |  |  |  |  |  |  |
Excel Master Series Blog Directory
Statistical Topics and Articles In Each Topic
- Histograms in Excel
- Bar Chart in Excel
- Combinations & Permutations in Excel
- Normal Distribution in Excel
- Overview of the Normal Distribution
- Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
- Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
- Solving Normal Distribution Problems in Excel 2010 and Excel 2013
- Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
- An Important Difference Between the t and Normal Distribution Graphs
- The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
- Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An Easy-To-Understand Way
- t-Distribution in Excel
- Binomial Distribution in Excel
- z-Tests in Excel
- t-Tests in Excel
- Overview of t-Tests: Hypothesis Tests that Use the t-Distribution
- 1-Sample t-Tests in Excel
- Overview of the 1-Sample t-Test in Excel 2010 and Excel 2013
- Excel Normality Testing For the 1-Sample t-Test in Excel 2010 and Excel 2013
- 1-Sample t-Test – Effect Size in Excel 2010 and Excel 2013
- 1-Sample t-Test Power With G*Power Utility
- Wilcoxon Signed-Rank Test As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013
- Sign Test As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013
- 2-Independent-Sample Pooled t-Tests in Excel
- Overview of 2-Independent-Sample Pooled t-Test in Excel 2010 and Excel 2013
- Excel Variance Tests: Levene’s, Brown-Forsythe, and F Test For 2-Sample Pooled t-Test in Excel 2010 and Excel 2013
- Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro Wilk Tests For Two-Sample Pooled t-Test
- Two-Independent-Sample Pooled t-Test - All Excel Calculations
- 2-Sample Pooled t-Test – Effect Size in Excel 2010 and Excel 2013
- 2-Sample Pooled t-Test Power With G*Power Utility
- Mann-Whitney U Test in Excel as 2-Sample Pooled t-Test Nonparametric Alternative in Excel 2010 and Excel 2013
- 2-Sample Pooled t-Test = Single-Factor ANOVA With 2 Sample Groups
- 2-Independent-Sample Unpooled t-Tests in Excel
- 2-Independent-Sample Unpooled t-Test in Excel 2010 and Excel 2013
- Variance Tests: Levene’s Test, Brown-Forsythe Test, and F-Test in Excel For 2-Sample Unpooled t-Test
- Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk For 2-Sample Unpooled t-Test
- 2-Sample Unpooled t-Test Excel Calculations, Formulas, and Tools
- Effect Size for a 2-Independent-Sample Unpooled t-Test in Excel 2010 and Excel 2013
- Test Power of a 2-Independent Sample Unpooled t-Test With G-Power Utility
- Paired (2-Sample Dependent) t-Tests in Excel
- Paired t-Test in Excel 2010 and Excel 2013
- Excel Normality Testing of Paired t-Test Data
- Paired t-Test Excel Calculations, Formulas, and Tools
- Paired t-Test – Effect Size in Excel 2010, and Excel 2013
- Paired t-Test – Test Power With G-Power Utility
- Wilcoxon Signed-Rank Test As a Paired t-Test Alternative
- Sign Test in Excel As A Paired t-Test Alternative
- Hypothesis Tests of Proportion in Excel
- Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
- 1-Sample Hypothesis Test of Proportion in Excel 2010 and Excel 2013
- 2-Sample Pooled Hypothesis Test of Proportion in Excel 2010 and Excel 2013
- How To Build a Much More Useful Split-Tester in Excel Than Google's Website Optimizer
- Chi-Square Independence Tests in Excel
- Chi-Square Goodness-Of-Fit Tests in Excel
- F Tests in Excel
- Correlation in Excel
- Pearson Correlation in Excel
- Spearman Correlation in Excel
- Confidence Intervals in Excel
- Overview of z-Based Confidence Intervals of a Population Mean in Excel 2010 and Excel 2013
- t-Based Confidence Intervals of a Population Mean in Excel 2010 and Excel 2013
- Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
- Confidence Interval of Population Proportion in Excel 2010 and Excel 2013
- Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013
- Simple Linear Regression in Excel
- Overview of Simple Linear Regression in Excel 2010 and Excel 2013
- Simple Linear Regression Example in Excel 2010 and Excel 2013
- Residual Evaluation For Simple Regression in Excel 2010 and Excel 2013
- Residual Normality Tests in Excel – Kolmogorov-Smirnov Test, Anderson-Darling Test, and Shapiro-Wilk Test For Simple Linear Regression
- Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
- All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
- Prediction Interval of Simple Regression in Excel 2010 and Excel 2013
- Multiple Linear Regression in Excel
- Basics of Multiple Regression in Excel 2010 and Excel 2013
- Multiple Linear Regression Example in Excel 2010 and Excel 2013
- Multiple Linear Regression’s Required Residual Assumptions
- Normality Testing of Residuals in Excel 2010 and Excel 2013
- Evaluating the Excel Output of Multiple Regression
- Estimating the Prediction Interval of Multiple Regression in Excel
- Regression - How To Do Conjoint Analysis Using Dummy Variable Regression in Excel
- Logistic Regression in Excel
- Logistic Regression Overview
- Logistic Regression Performed in Excel 2010 and Excel 2013
- R Square For Logistic Regression Overview
- Excel R Square Tests: Nagelkerke, Cox and Snell, and Log-Linear Ratio in Excel 2010 and Excel 2013
- Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
- Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
- Hosmer-Lemeshow Test in Excel – Logistic Regression Goodness-of-Fit Test in Excel 2010 and Excel 2013
- Single-Factor ANOVA in Excel
- Overview of Single-Factor ANOVA
- Single-Factor ANOVA Example in Excel 2010 and Excel 2013
- Shapiro-Wilk Normality Test in Excel For Each Single-Factor ANOVA Sample Group
- Kruskal-Wallis Test Alternative For Single Factor ANOVA in Excel 2010 and Excel 2013
- Levene’s and Brown-Forsythe Tests in Excel For Single-Factor ANOVA Sample Group Variance Comparison
- Single-Factor ANOVA - All Excel Calculations
- Overview of Post-Hoc Testing For Single-Factor ANOVA
- Tukey-Kramer Post-Hoc Test in Excel For Single-Factor ANOVA
- Games-Howell Post-Hoc Test in Excel For Single-Factor ANOVA
- Overview of Effect Size For Single-Factor ANOVA
- ANOVA Effect Size Calculation Eta Squared (?2) in Excel 2010 and Excel 2013
- ANOVA Effect Size Calculation Psi (?) – RMSSE – in Excel 2010 and Excel 2013
- ANOVA Effect Size Calculation Omega Squared (?2) in Excel 2010 and Excel 2013
- Power of Single-Factor ANOVA Test Using Free Utility G*Power
- Welch’s ANOVA Test in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar
- Brown-Forsythe F-Test in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar
- Two-Factor ANOVA With Replication in Excel
- Two-Factor ANOVA With Replication in Excel 2010 and Excel 2013
- Variance Tests: Levene’s and Brown-Forsythe For 2-Factor ANOVA in Excel 2010 and Excel 2013
- Shapiro-Wilk Normality Test in Excel For 2-Factor ANOVA With Replication
- 2-Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
- Excel Post Hoc Tukey’s HSD Test For 2-Factor ANOVA With Replication
- 2-Factor ANOVA With Replication – Test Power With G-Power Utility
- Scheirer-Ray-Hare Test Alternative For 2-Factor ANOVA With Replication
- Two-Factor ANOVA Without Replication in Excel
- Creating Interactive Graphs of Statistical Distributions in Excel
- Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
- Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Chi-Square Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013
- Solving Problems With Other Distributions in Excel
- Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
- Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
- Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
- Solving Beta Distribution Problems in Excel 2010 and Excel 2013
- Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
- Solving Poisson Distribution Problems in Excel 2010 and Excel 2013
- Optimization With Excel Solver
- Maximizing Lead Generation With Excel Solver
- Minimizing Cutting Stock Waste With Excel Solver
- Optimal Investment Selection With Excel Solver
- Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
- Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
- Optimizing a Bond Portfolio With Excel Solver
- Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers
- Chi-Square Population Variance Test in Excel
- Analyzing Data With Pivot Tables
- SEO Functions in Excel
- Time Series Analysis in Excel
Liên hệ Aivivu, đặt vé máy bay tham khảo
ReplyDeletevé máy bay đi Mỹ
chuyến bay từ mỹ về việt nam covid
chuyến bay nhân đạo từ nhật về việt nam
vé máy bay khứ hồi từ đức về việt nam
đăng ký bay từ canada về Việt Nam
vé máy bay từ hàn về Việt Nam
bảng giá khách sạn cách ly tphcm
chuyến bay chuyên gia trung quốc