Analytics & Optimization Analysis

This Excel workbook has three other tabs: 1) AB Test 2) OrderDetails: a report on all the orders placed that were assisted by chat 3) TranscriptReport: a report with details on each chat interaction between a customer and an agent Part A: The AB Test worksheet contains instructions on analyzing an A/B test. Present your findings and recommendations in a client facing PowerPoint presentation. Please take this opportunity to demonstrate your data visualization skills. Also show your work in an Excel file. Part B: The tabs 'OrderDetails' and 'TranscriptReport' contain data for the same client for the same date range. Create a PowerPoint file (1-5 slides) to share your 5 most meaningful insights and any recommendations based off those insights. As above, this should result in a presentation that could be presented to a client in a real-life scenario, so please take this opportunity to demonstrate your data visualization skills. Also show your work in an Excel file. Here are some notes about the OrderDetails report: - Note that the “Product” column, column C, actually contains 3 columns worth of data all separated by a tilde “~” character. You should separate the data in this column into multiple columns. - Also note that the “Conversions” column only contains a 1 or 0.  A conversion represents a completed sale, which may consist of 1 or more products.  Each row represents a different product within an order.  Therefore, in rows 3-5, which are all part of the same order (which is apparent through columns A and B), only row 3 contains a “1” in the Conversions column, whereas rows 4 and 5 contain 0’s.  This means when you add up column E, it will give you the correct number of total orders (not products sold). Please note that we also track a conversion as 'chat assisted' when it occurs up to 30 days after the chat interaction ended. - Column J is the Business Rule column.  A Business Rule describes how chat is displayed on a client's website (eg: one business rule could be to show a yellow click to chat button on the shopping cart page, another one could be to show a chat window when a visitor has spent 90 seconds on the shopping cart page).  You don’t need to understand the naming conventions for the business rules at this point. - Column K is the Page column.  These represent different pages (or groups of URLs) on the client’s website. Here are some notes about the TranscriptReport report: - This worksheet lists every chat interaction that took place for the client, both those that resulted in an order and those that didn’t.  The only additional columns are the Disposition Category and the Disposition Reason (a.k.a., sub-category).  Dispositions are how chat agents categorize a chat interaction after it concludes.