Analytics & Visualization
1. Using SQL, solve the following problem. Given three tables:
Find the total sales by author in October 2016. The result set should have two columns: author’s name and their total sales.
For each author, return the title and published_date of their first book. The result set should have five columns: author’s id, author’s name, book’s id, book’s title, and book’s published_date.
2. You have been tasked with trying to better understand the relationship between calls and emails exchanged with a customer. You have these tables to work with:
We want to gauge the effectiveness of an email campaign in causing a customer to call us. How might we try to join these tables and which metrics could we create to judge the effectiveness of a campaign? What other information would be helpful in trying to gain insights into the relationship between calls and emails?
3. One of the primary metrics for e-commerce business is Conversion Rate. Conversion Rate is ratio of visitors who converted over all visitors. We want to understand how conversion rate fluctuates on a monthly basis between devices and medium.
4. A required step in the conversion process is submitting credit card information. In order to successfully procced with payment, we ask for Email, Credit Card number, Expiration date, CVV and Zip Code. If, at least, one of those fields is invalid – we get an error message (next slide has examples). We would like to understand the incidence of each error by month.
5. What are the most common errors?
6. Are we losing potential converters who tried to submit Credit Card information but got an error message and ultimately were not able to convert? If so, can you size that segment and provide the potential opportunity loss?
Business Analysis & Visualization
Data set overview:
Data is SQL grouped based on device Category, medium, number of errors, error message, date columns.
Visitors and Converted visitors are SUM aggregated.
Comments