Using mathematics to solve problems with real-world data excites me.

Background

Every month, a data pipeline generates an Excel file containing information on ads shown for various TV networks. Each row in the table represents data on a particular ad airing in a specific time slot, consisting of:

• Spend: amount spent on the ad
• Lift: number of distinct visitors to the product's website within a 30 minute period following the ad
• Acquisitions: number of distinct visitors who ultimately purchased the product

My Python script cleans the data, performs joins, and calculates important metrics, then generates and exports three summary reports as PDF files to produce recommendations for the TV networks where ad spending should be increased or decreased.

The full project can be found on my GitHub.

Insights

Using data from September and October, I've calculated cost per visitor, cost per acquisition, and conversion rate for $221,436 in spending from 6K records over 28 marketing channels and assigned rankings based on average performance in each metric.

Definitions: Key Performance Indicators (KPIs)

• Cost Per Visitor (CPV) = Total Spend / Total Lift
  • Average CPV = $10.81
• Cost Per Acquisition (CPA) = Total Spend / Total Number of Purchases Made
  • Average CPA = $1,265.35
• Conversion Rate = Total Number of Purchases Made / Total Lift * 100
  • Average Conversion Rate = 0.9%

Several channels ranked in the top five for at least two KPIs. The biggest winner was CNBC, who had the best CPA ($432/acquisition) and the highest conversion rate (3.7%). In close second and third place were MSNBC and CNN. MSNBC had the 4th best CPV ($10.09/visitor) and the 3rd best CPA ($708.13/acquisition), while CNN had the 4th best CPA ($787.58/acquisition) and the 4th highest conversion rate (2.1%).

Despite not ranking in the top five for any metric, The History Channel and Turner Network Television had above-average performance in multiple KPIs and are worth mentioning. Both channels had below-average CPAs and above-average conversion rates, indicating they are cost-efficient, and consumers are more likely to purchase the product being advertised.

On the other hand, several channels ranked in the bottom five for at least two KPIs. The Weather Channel was in the bottom five for all three and was the biggest loser by far, having the 3rd worst CPV ($36.58/visitor) with 0 purchases, leading to a 0% conversion rate. Similarly, CNBC World had 0 purchases and a 0% conversion rate. ZeeTV had the worst CPA ($5,120.85/acquisition) and the 4th worst conversion rate (0.3%), while Star Plus had the 4th worst CPA ($2,945.14/acquisition) and the 3rd worst conversion rate (0.2%).

An Important Remark

Acquisiton data comes from exit surveys following a purchase, where a consumer will be asked to indicate the television channel on which they saw the ad that led them to make a purchase.

Forty-four of the survey's responses indicated purchasing something based on an ad that aired on a channel without ad spending. This discrepancy could mean an issue with data quality where purchases got attributed to the wrong channel. It could also mean that some of the survey's respondents are incorrectly remembering which channel ultimately led them to make a purchase. Either way, the data quality should be investigated, and future survey questions should be adjusted so respondents cannot select channels with no ad spending in their responses.