https://github.com/yvetteyyuan/Air-france-search-engine-marketing/blob/master/Air%20France%20Case%20Spreadsheet%20Supplement.xls

GROUP (UP TO 3 PEOPLE) WEB ANALYTICS ASSIGNMENT #1 {40 POINTS) DUE DATE: Thursday, 10/6/2022 Each grong only needs to submit one cogy o This assignment will analyze the data (AirFrance.xls), interpret the results, and make recommendation from the Case: Air France Internet Marketing: Optimizing Google, Yahoo!, MSN, and Kayak Sponsored Search (KEL 319). Please read the case rst and follow the instructions in the following very carefully to do this assignment! . Please do the following analyses using the data in the \"DoubleClick\" sheet of the excel file and answer the corresponding questions. Please copyisummarize your key results for each guestion to a word file along with your answers to be well organized to produce the final report. 1. (10 Points) Please rst add four additional metrics (columns) into your data using the formula provided 0 Net Revenue (Amount (total revenue) Total Cost)) 0 Return on Ad $ Spent (ROA1 (Net Revenue / Total Cost) (Note: Set this variable as percentage; if Total Cost is 0, then set ROA as 0 for that observation.) 0 Average Revenue per Booking (Amount fTotal Volume of Bookings) (Note: if Total Volume of Bookings is 0, then set \"NA \" for that observation) 0 Probability of Booking (Engine Click Thru % (CTR) * Trans. Conv. 0/0 (TCR)/ 10000) Please provide descriptive statistics (Count, Max, Min, Mean, and Std.) for variables (CTR TCR, ROA, Net Revenue, Avg. Cost per Click, Average Revenue per Booking, and Probability of Booking. Please ONLY report a summary statistics table and provide short descriptions of your observations and thoughts. 0 Please make a Scatter Plot (with a Trend line), a Histogram (with a density plot, see Tutorial 1.2), and a Box Plot for any of the variables of your own interest in the data. Then report any insights you may be able draw from the charts. u 2. a) (10 points) Use Pivot Tables to summarize metrics for each publisher. Please report the summary table including the variables as shown below. Please report your summary excel table and discuss Key Observations and Takeaways. Total Average Average Sum of Net Sum of Click Sum of Volumn of |Cost Per Average Revenue Per Probability of Publisher Name Revenue Charges Clicks Bookings Click Position Booking ROA Cost/Booking booking Google - Global Google - US Overture - Global Overture - US MSN - Global MSN - US Yahoo - US All Publishers * * *Please Note that the "All Publishers" part may represent "total" for some columns, but "average" for other columns Hint: Always go back to the original formula! use the "sum" function in pivotTable to get the Sum of Net Revenue, Sum of Total Volume of Bookings, Sum of Click Charges, Sum of Clicks, Sum of Amount, and Sum of Impressions, and Etc. Then use those variables to further calculate Average Revenue Per booking and Probability of Booking. Or you can use the calculated fields (more straightforward) as shown in the following tutorial http://www.excel-easy.com/examples/calculated-field-item.html (This is also included in the "Analytics Tutorial Part II"). b) (10 points) Based on your results in a), 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) for search engines with Low probability of booking and Low CPC; (3) for search engines with High probability of booking and High CPC, and (4) for search engines with Low probability of booking and High CPC. Please summarize and report your recommendations for each different type (keep your answers brief).3. (10 points) Based on the one-week summary data provided for Kayak in \"kayak\" sheet of the excel le, please calculate the following metrics and clearly show your calculation process. 1- Kayak Trans. Conv. Rate 0 Average Publisher TCR (the 7 publishers Air France currently using) 0 Kayak CPC - Average Publisher CPC (the 7 publishers Air France currently using) Compare the calculations with what you have derived from Q2, what recommendation would you like to make on marketing in Kayak relative to other publishers