Commercial Analytics Exercise

Introduction

Social graph technology allows us to identify and track relationships between individuals. By leveraging connections people have with each other, it can help companies perform effective targeted messaging. For this exercise, you will be utilizing the social graph for a commercial application to analyze target universes as well as formulate a business plan. A target universe is a list of people we generate who are related to an initial group of people our client provides us. This list provides new leads the client can reach out to. For example, if a client gives us a list of people who bought one of their products recently, we could generate a target universe consisting of coworkers of people in that list.

Background

Salamander is a small but ambitious car insurance company based in the Midwest region that is looking to expand its business. As a pilot program, Salamander gave us a list of recent customers in the Ohio area, from which we provided them two target universes as lead generation:

1. One target universe contains first-degree connections of the recent customers: people who, based on our social graph, are friends, coworkers, colocated (household members), or classmates of the recent customers.

2. The second target universe contains a randomly generated list of people.

Using these lists, Salamander launched an online ad campaign, sending online advertisements to people in both lists, and measured who clicked on the ads and who ended up buying one of their products. The results have come back and we would like to see an analysis of the results from the first round as well as a proposal for improving future iterations of campaigns.

The Project

Please send back to us the following deliverables:

- first_round_analysis.pdf: An analysis and comparison of the results from the one degree target universe and random target universe:
? Which one yielded better results? What metrics did you use to evaluate which results were better?
? What demographics are most strongly correlated with better results?
? How do the results differ across different types of first-degree connections?
? What issues did you identify with the data that Salamander sent you?
? If you had more time to work on this project, what further analysis would you conduct?

- business_strategy.pdf: A business memo (around 3 pages) intended for internal distribution to us. This memo should contain detailed and organized action items for us to improve its follow up experiments, including:
? Suggesting additional data Salamander should collect in its campaigns that would help them better measure success
? Coming up with new Key Performance Indicators (KPIs) based on this additional data, explaining how each KPI is computed and why it is beneficial for Salamander
? Coming up with an additional set of KPIs for us to internally track how well its targeting strategies are working and how well the graph is performing
? Suggesting how we can improve the design and workflow of future campaigns so that it can more systematically track their findings and are capable of capturing the new KPIs you created. Consider marketing strategies, experimental design, platforms, and pipelines that would provide more streamlined and quantitative analysis
? Any additional questions you would ask Salamander and/or us that would help you make your recommendations

- sql_commands: A list of the SQL commands you used to conduct the first round analysis (make sure they are in an easily accessible format, like a notebook, pdf, or word document)

Suggestions

- It is encouraged that you do not work on this project for more than 6 hours.
Spend around 1-2 hours understanding and ingesting the data, 2 hours analyzing the first round results, and 2 hours writing a memo suggesting improvements to the follow up experiments

- Consider all aspects of the consumer experience, from impressions to retention, in your business strategy memo. Be creative and detailed!

- Although the size of this dataset is small enough that it could be analyzed in Excel, the datasets that you will typically deal with are much larger and cannot be handled by Excel. As such, if you need to manipulate the data at all, you will need to do so using SQL (or at least let us know which SQL query you would use. If you want to use Excel to create graphics, that is fine.

- In order to complete the project, you will need to host the datasets that we send you. WeusesDatabricks as its primary platform for SQL queries, so we highly recommend using Databricks for this exercise!

Attachment:- Commercial Data Analytics Using SQL.zip