Create Your First Project

Start adding your projects to your portfolio. Click on "Manage Projects" to get started

.pro-gallery-wix-wrapper {display: block !important;} .pro-gallery-wix-wrapper .gallery-item-container {opacity: 1 !important; display: block !important;}

Full Analytics Pipeline: From raw data to valuable insights

A self-driven analytics project that explores campaign performance from raw data to final insights — built using SQL, Python, and Tableau.

The Context

This project analyzes 1,000 paid ad campaigns across platforms like Facebook, Google, Instagram, and LinkedIn. Each campaign includes performance metrics, demographic targeting, content format, and calculated KPIs like ROAS and CPA.

The goal was to simulate a real-world PPC (pay-per-click) environment, using structured data to identify trends, optimize spend, and guide smarter marketing decisions.

Key Columns in the Dataset:

Platform — Facebook, Google, Instagram, etc.

Content_Type — Video, Image, Text, or Carousel

Spend, Revenue, Budget — Campaign cost & earnings

Clicks, Impressions, Conversions — Ad funnel metrics

ROAS, CPA, CTR, Conversion_Rate — Core performance indicators

Target_Age, Target_Gender, Region — Demographics

Duration, Date — Campaign timing

Cleaned Dataset Snapshot (Click image to expand):

The Question

Digital marketing spend is growing, but not all campaigns deliver. Using a synthetic dataset modeled after real-world campaigns, this project explores which platforms, content types, and audience segments would drive the highest return on ad spend (ROAS).

The goal: simulate a real analytics workflow that helps teams make smarter, data-backed decisions.

The Process

1. Data Import & Exploration
Started with a raw campaign dataset in CSV format. Previewed structure and checked for missing values.

2. Cleaning in Python
Used Pandas to clean the dataset, handle missing values, remove duplicates, and standardize column names to prepare for SQL analysis.

3. Export & SQL Setup
Saved the cleaned dataset and imported it into MySQL for structured querying.

4. Custom SQL Queries
Wrote 7 queries to explore ROAS, platform performance, content type impact, and demographic trends.

5. Visualization in Tableau

Exported query results and visualized them in Tableau Public. Built 4 dashboards aligned to key business questions, such as platform ROI and audience targeting.

Key Insights From SQL Analysis

These insights were derived from seven custom SQL queries built on cleaned PPC campaign data. Each query was written with intention to simulate real analyst work in a performance marketing environment.

Insight 1: Facebook had the highest average ROAS

Facebook delivered an average ROAS of 13.04, followed closely by LinkedIn and Google. All platforms are significantly above the standard ROAS benchmark of 4.0, indicating strong returns across all platforms.

Recommendation:

Prioritize Facebook for scaling campaigns. All platforms performed exceedingly well - no major changes are required but prioritizing top three platforms could improve returns.

SQL Query 1

Query 1 Output

Insight 2: Video Content Drives the Highest Average Conversion Rate

Video ads led all content types with an average conversion rate of 52.7%, closely followed by carousel ads (52.4%) and image-based ads (50.0%). Text-based ads had the lowest conversion rate at 48.1%.

While all formats performed strongly, video content remains the most effective choice for maximizing conversions.

Recommendation:

Prioritize video content for future campaigns if aiming for higher conversions. Carousel and image content can be used to diversify strategy. Consider limiting the use of text content or testing improvements to boost performance.

SQL Query 2

Query 2 Output

Insight 3: The 25-34 and 55+ age groups offer the strongest returns and cost-efficiency

The 25–34 age group generated the highest average ROAS at 12.39, followed closely by the 55+ and 45–54 groups. The 55+ segment had the lowest average CPA at 39.07, making it one of the most cost-efficient audiences overall. In contrast, the 18–24 group performed the worst, with the lowest ROAS (10.96) and one of the higher CPAs.

Recommendation:

Prioritize the 25–34 and 55+ age groups, both balance strong returns with efficient costs. Consider scaling back spend on the 18–24 segment, suggest running tests to improve efficiency

SQL Query 3

Query 3 Output

Insight 4: North America leads in both ROAS and total revenue

North America outperformed all other regions with the highest ROAS at 12.47 and the highest total revenue at $12.6M. Europe and South America followed closely, each generating ROAS above 12.3 and revenues above $12.4M. Africa had the lowest performance overall, with a ROAS of 9.11 and the lowest total revenue at $9.7M.

Recommendation:

North America, Europe, South America, and Asia are nearly identical in both revenue and ROAS, making them strong candidates for scaling. Africa performed well but may benefit from creative testing or targeting refinement.

SQL Query 4

Query 4 Output

Insight 5: 139 high-spend campaigns underperformed on ROAS

Part 1: Establishing Spend & ROAS Benchmarks:

Before identifying underperformers, key thresholds were established using dataset-wide averages:

Average Spend: $5,956

High Spend Threshold: > $7,500

Average ROAS: 11.69

Low ROAS Threshold: < 4.0

These thresholds were used to flag campaigns with high costs but poor efficiency.

Query Used to Find Benchmarks:

SQL Query 5A

Query 5A Output

Insight 6: Male and Other gender segments show strongest cost-efficiency

The Other gender segment has the highest average ROAS at 12.82, but a mid-range CPA of 40.75. The Male segment has the lowest CPA at 37.40 and a strong ROAS of 11.53, making it the most cost-efficient overall. The Female segment has the lowest ROAS at 10.61 and the highest CPA at 44.98, indicating lower efficiency.

Recommendation:

Prioritize campaigns targeting Male and Other gender segments based on strong performance. Suggest testing new strategies or messaging to improve cost-efficiency for Female segment.

SQL Query 6

Query 6 Output

Insight 7: Facebook + Video delivers the highest ROAS across all platform-content combinations

Facebook + Video content has the highest ROAS at 15.94, making it the top-performing combination overall. YouTube + Carousel (13.64) and LinkedIn + Video (13.29) also perform well above average. Google + Text has one of the lowest ROAS scores at 9.48, and YouTube + Text is the lowest overall at 9.12.

Recommendation:

Prioritize Video content on Facebook, LinkedIn, and Instagram for maximum return. Consider reducing or redesigning Text-based campaigns, especially on YouTube and Google, where they underperform. Explore scaling Carousel formats on high-performing platforms like YouTube, Google, and Facebook.

SQL Query 7

Query 7 Output Sample

The Dashboards

Dashboard 1: Platform & Content Strategy

Insight Summary:

Video consistently outperforms all other content types. The highest ROAS comes from Facebook + Video (15.94), while text formats underperform, especially on YouTube and Google.

View Full Dashboard

Final Reflection

This project was developed to evaluate digital ad performance through a complete, end-to-end analytics workflow. Beginning with raw campaign data, I designed and executed the full process, including data cleaning in Python, structured querying in MySQL, and visual analysis using Tableau dashboards.

The findings provided clear strategic insights:

Facebook campaigns achieved the highest ROAS across platforms
Video-based content consistently outperformed all other formats in conversion rate
The 25–34 and 55+ age groups were the most cost-efficient
Nearly 130 campaigns spent over $7,500 and returned less than 4.0 ROAS, exposing major inefficiencies

Every part of this project, from the data cleaning to the dashboard design to this website, was built independently with lots of care.

My goal with this project was to learn, build, and demonstrate my ability to manage a full analytics pipeline and produce results that are both technically sound and built for real business use.

Explore the Full Project

Want a closer look at the code and files behind this project? I’ve included the original code, queries, dataset, and dashboards for anyone interested in a closer look.

View Full Project Files Here

Create Your First Project

Full Analytics Pipeline: From raw data to valuable insights

The Context

The Question

The Process

Key Insights From SQL Analysis

Insight 1: Facebook had the highest average ROAS

SQL Query 1

Query 1 Output

Insight 2: Video Content Drives the Highest Average Conversion Rate

SQL Query 2

Query 2 Output

Insight 3: The 25-34 and 55+ age groups offer the strongest returns and cost-efficiency

SQL Query 3

Query 3 Output

Insight 4: North America leads in both ROAS and total revenue

SQL Query 4

Query 4 Output

Insight 5: 139 high-spend campaigns underperformed on ROAS

SQL Query 5A

Query 5A Output

Insight 6: Male and Other gender segments show strongest cost-efficiency

SQL Query 6

Query 6 Output

Insight 7: Facebook + Video delivers the highest ROAS across all platform-content combinations

SQL Query 7

Query 7 Output Sample

Insight 5: 139 high-spend campaigns underperformed on ROAS

SQL Query 5b

Query 5b Output Sample

The Dashboards

Dashboard 1: Platform & Content Strategy

Dashboard 2: Audience Insights

Dashboard 3: Regional Performance

Dashboard 4: Campaign ROI & Budget Waste

Final Reflection

Explore the Full Project