WEB ANALYTICS ASSIGNMENT
In this assignment, you will analyze the data (AirFrance.xls), interpret the results, and make recommendation based on the case: "Air France Internet Marketing: Optimizing Google, Yahoo!, MSN, and Kayak Sponsored Search" (KEL 319). Please read the case first and follow the instructions below very carefully to complete this assignment.
- Please conduct the following analyses using the data in the "DoubleClick" sheet of the excel file and answer all questions. Covert the Excel file into .txt file for R data importing, if necessary.
- Your submission will be a single Word document. Make sure you include your key analysis results (graphs, tables, figure, estimation outputs, etc.) that you use to support your answers.
1. (1a) Please add four additional metrics into your data using the formula below.
-Net Revenue: [Amount (i.e., total revenue) - Total Cost]
-Return on Ad $ Spent (ROA): [Net Revenue / Total Cost]
Note: Set this variable as %; if Total Cost is 0, set ROA as 0 for that observation.
-Average Revenue per Booking: [Amount / Total Volume of Bookings]
Note: If Total Volume of Bookings is 0, then set "NA" for that observation)
-Probability of Booking [Engine Click Thru Rate (CTR) * Trans. Conv. Rate (TCR)]
Note: Set this variable as %. Make sure the units make sense to you.
(1b) Please provide descriptive statistics (Count, Max, Min, Mean, and Std.) for variables (CTR, TCR, Net Revenue, Avg. Cost per Click, ROA, Average Revenue per Booking, and Probability of Booking). Report a summary statistics table and provide short descriptions of your observations and thoughts.
(1c) Please make a Scatter Plot (with trend line) and a Histogram for any of the variables of your interests in the data. Report any insight you may be able draw from the charts.
2. (2a) Use pivotTable to summarize metrics for each publisher. Please report the summary table including the variables as shown below. The answer for Google-global is already provided, so please fill-in others. Please report your Excel tables and discuss key observations and takeaways.
(2b) Based on your results in (2a), graph publishers on a bubble chart using the following dimensions: X=Probability of Booking, Y=Avg. Cost Per Click, Bubble Size=Total Costs or Total Funding (Sum of Click Charges).
Base on your bubble chart, can you categorize different publishers into four different types:
1) For search engines with High probability of booking and Low CPC; (2) Low probability of booking and Low CPC; (3) High probability of booking and High CPC, and (4) Low probability of booking and High CPC? Please summarize and report your recommendations for each different type (keep your answers brief).
3. Use the following pivot tables to study tactics of campaigns with a high ROA for Google U.S. Based on this table, please answer what the characteristics of best campaigns are, such as campaign category, keyword combination, match type, bid strategy, etc. Make your own recommendations.
4. Please conduct regression analysis to study what factors influence the Total Cost. Basically, Total Cost will be your dependent variable (Y) and your task is to determine what the important independent variables are. Please:
(a) Build and estimate at least 3 linear regression models. Report results from all 3 models;
(b) Indicate the best model among the three. Report final set of independent variables you have chosen and why you have chosen them;
(c) Report the estimated regression equation with simple interpretations for each estimated coefficient (β's).
5. Based on the one-week summary data provided for Kayak in "kayak" sheet of the excel file, please calculate the following metrics and clearly show your calculation process.
- Kayak Trans. Conv. Rate
- Average Publisher TCR
- Kayak CPC
- Average Publisher CPC
Compare the calculations with what you have derived from Q2, what recommendation would you would like to make about marketing in Kayak relative to other publishers?
Attachment:- Assignment.rar