Finding the Top Performing Ads with SQL

The problem of finding top-performing ads is a popular SQL question in Data Science interviews. So, if you want to know how to solve this problem, this article is for you. In this article, I’ll take you through how to find top-performing ads using SQL.

Problem Statement:

Imagine you are a digital marketing analyst working for an e-commerce company. Your goal is to identify the top-performing ads based on various performance metrics, such as click-through rate (CTR), conversion rate, and return on ad spend (ROAS). However, you are facing challenges in extracting and analyzing the relevant data from your company’s database using SQL queries. You need to develop SQL queries to find the top-performing ads and overcome these challenges.

Example:

Let’s consider a simplified example where you have access to a database with two tables: “ads” and “ad_metrics”. The “ads” table contains information about the ads, such as ad ID, ad title, and ad campaign. The “ad_metrics” table contains performance metrics for each ad, including the number of clicks and the number of conversions.

Here’s a sample schema for the “ads” and “ad_metrics” tables:

ads table:

ad_metrics table:

To find the top-performing ads based on a specific performance metric, you can use SQL queries. Here are a few examples:

Find the top-performing ads based on click-through rate (CTR):

SELECT a.ad_id, a.ad_title, a.ad_campaign, am.clicks, am.conversions, (am.clicks / am.conversions) AS ctr
FROM ads a
JOIN ad_metrics am ON a.ad_id = am.ad_id
ORDER BY ctr DESC
LIMIT 5;

This query retrieves the ad ID, title, campaign, number of clicks, number of conversions, and calculates the CTR by dividing clicks by conversions. It then orders the results in descending order based on CTR and limits the output to the top 5 ads.

Find the top-performing ads based on conversion rate:

SELECT a.ad_id, a.ad_title, a.ad_campaign, am.clicks, am.conversions, (am.conversions / am.clicks) AS conversion_rate
FROM ads a
JOIN ad_metrics am ON a.ad_id = am.ad_id
ORDER BY conversion_rate DESC
LIMIT 5;

This query retrieves the ad ID, title, campaign, number of clicks, number of conversions, and calculates the conversion rate by dividing conversions by clicks. It then orders the results in descending order based on conversion rate and limits the output to the top 5 ads.

Output

Find the top-performing ads based on return on ad spend (ROAS):

Assuming you have another table called “ad_costs” that contains the cost information for each ad, with columns ad_id and cost, you can calculate ROAS as (conversions / cost). The following query retrieves the top-performing ads based on ROAS:

SELECT a.ad_id, a.ad_title, a.ad_campaign, am.clicks, am.conversions, (am.conversions / ac.cost) AS roas
FROM ads a
JOIN ad_metrics am ON a.ad_id = am.ad_id
JOIN ad_costs ac ON a.ad_id = ac.ad_id
ORDER BY roas DESC
LIMIT 5;

Output

This query joins the “ad_costs” table to fetch the cost for each ad, calculates the ROAS by dividing conversions by cost, and retrieves the top 5 ads based on ROAS.

By leveraging SQL queries and appropriate joins, you can extract relevant data from the database, perform calculations, and identify the top-performing ads based on your desired performance metrics.