Introduction

The following exercises are a part of a new section (currently under construction) in this website, providing data analysis tasks that require business understanding alongside technical skills.

 

About this Exercise

In the Online Advertising Market, a merchant who wants to advertise a new product, can contact different publishers (such as Google or Facebook) and pay them for placing an ad (such as a banner, or video) in their platform.
In return, the online advertising platform gives the merchant metrics to help him measure his ad performance :

  • Clicks – Click happens when someone sees the ad and clicks on it, going to the website for more information.
  • Conversions – After that someone landed on the website, conversion happens when an action (you have defined as a converting action) takes place, for example: online purchase or a call to the business from a mobile phone

 

ERD General Description

The following database simulates data of an online advertising platform.

  • Within the Ads table you’ll find data regarding the various ads
  • Advertisers table lists the different advertisers
  • Clicks reflects the data regarding the clicks on every ad
  • Conversions table lists the clicked conversions

 

Diagram



 

Download

The database creation script can be downloaded using the following link

 

Exercises

The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

 
Basic Analysis

  1. List the first 10 ads, sort the results by their names in an ascending order
  2. Display all clicks made in Sweden using Chrome browser
  3. List all conversions made in 2017

 
Advanced Analysis

  1. Using Clicks table, what is the most frequently used browser ?
  2. Which ad has the highest amount of clicks ? display the distribution of clicks for each country
  3. Conversion rate is calculated using the following formula : SUM(total_conversions) \ SUM(total_clicks) * 100. Find out the conversion rate for the ad with the highest amount of clicks
  4. Display the top-5 ads, having the highest conversion rate
  5. Is there any conversion rate differance between the browsers?
  6. In average, for each ad, how many days it took for a click to become a conversion?
  7. What is the most frequently used browser in Brazil

 

Solutions