Reference no: EM133343052
Assignment: Digital Analytics (Google Analytics, Pivot Table/Bubble Chart Analysis: Air France Case, A/B Testing: Rocket Fuel Case, and Web Scraping & Text Analysis of Amazon Reviews) (20 points)
- Google Analytics Exercise
Follow the instruction in the following URL to link your Google analytics account to the Google Analytics Demo account.
Complete the following exercises using data from the Google Demo account (Google Merchandize store: Master view).
- Explore the Google Demo Dashboard and download data for the following table from the relevant section (time period for data: last one month). Once the data is downloaded, plot a pie chart on share of different channels in driving traffic to the website (2 points).
| Default Channel Grouping |
Users |
New Users |
Sessions |
Bounce Rate |
Pages / Session |
Avg. Session Duration |
Ecommerce Conversion Rate |
Transactions |
Revenue |
| Organic Search |
|
|
|
|
|
|
|
|
|
| Direct |
|
|
|
|
|
|
|
|
|
| Referral |
|
|
|
|
|
|
|
|
|
| Paid Search |
|
|
|
|
|
|
|
|
|
| Social |
|
|
|
|
|
|
|
|
|
| Affiliates |
|
|
|
|
|
|
|
|
|
| (Other) |
|
|
|
|
|
|
|
|
|
| Display |
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
- Paid Search ROI Analysis: Air France Case
The objective of this part of the assignment is to analyze the Campaign Strategy for Google US Campaigns that Air France is running. As part of the assignment the following tasks need to be accomplished. Dataset for the case is available in the HBR course pack.
- Use the Pivot Function of the spreadsheet to generate summary statistics related to for the each of the following campaigns for Air France run on Google US network. The variables for summary statistics are given below (2 points).
| Campaigns |
Average of Probability of Booking (TCR x CTR) |
Avg. CPC (=Click Charges/Clicks) |
Total Cost |
Average of Engine Click Thru % (CTR) |
Transaction Conversion % (TCR) |
Total Volume of Bookings |
| Business Class |
|
|
|
|
|
|
| French Destinations |
|
|
|
|
|
|
| Geo Targeted Boston |
|
|
|
|
|
|
| Geo Targeted Chicago |
|
|
|
|
|
|
| Geo Targeted DC |
|
|
|
|
|
|
| Geo Targeted Detroit |
|
|
|
|
|
|
| Geo Targeted Houston |
|
|
|
|
|
|
| Geo Targeted Los Angeles |
|
|
|
|
|
|
| Geo Targeted New York |
|
|
|
|
|
|
| Geo Targeted Philadelphia |
|
|
|
|
|
|
| Geo Targeted San Francisco |
|
|
|
|
|
|
| Geo Targeted Seattle |
|
|
|
|
|
|
| Outside Western Europe |
|
|
|
|
|
|
| Paris & France Terms |
|
|
|
|
|
|
| Western Europe Destinations |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note that you will have to delete the rows "Air France Brand & French Destinations, Air France Branded, Air France Global Campaign, Geo Targeted Miami, Google Yearlong 2006, Unassigned" when copying the table from pivot table to a separate spreadsheet since these are outliers.
- Air France needs to optimize its investment across the above campaigns and needs to know how to reallocate its investment. Therefore, as part of this assignment, analyze the campaigns on basis of their average CPC, average probability of booking and current level of investment using the Bubble scatter plot with avg. CPC as the y-axis, average probability of booking as x-axis and size of the bubbles as the total cost of campaigns. Based on the graphical plot, provide insights on what investment strategy to take with regard to at least four of the campaigns.
- Air France needs to optimize each of the above campaigns. To do so it needs to know which campaigns need improvement in search side copy vs. which ones need improvements in website/landing page site copy. This can be done by creating a bubble scatter plot with Transaction conversion rate on y-axis, click through rate on x-axis and total volume of booking as the size of the bubbles. Based on the graphical plot, provide insights on what search copy vs. landing page copy strategy to take with regard to at least four of the campaigns.
- Online Experiments: Rocket fuel case
Read the Rocket Fuel case and analyze the dataset associated with the case to answer the following questions. The description of variables in the CSV file data set is in Exhibit 2 of the case.
- Was the advertising campaign effective? Did additional consumers convert as a result of the ad campaign? Did the test meet the requirements of randomization across the treatment and control groups? (carry out the relevant analysis, e.g. t-test etc., in R and provide codes for the analysis along with brief comments explaining the rationale for the analysis)
- Was the campaign profitable? In particular, how much more money did TaskaBella make by running the campaign (excluding the advertising costs)? What was the cost of the ad campaign? Calculate the ROI of the campaign and determine its profitability (no analysis on R needed for this question)
- Web Scraping & Text Mining
Scrape the review data for the first three pages of reviews for the following amazon webpage:
Carry out a word cloud analysis for the review text in the review data
Note: web scraping and text analysis for the amazon reviews would be discussed in class and students only need to replicate the analysis for the above case. One can use the R codes that would be provided in class.