Leke John Oluwagbemiga

Evaluating Marketing Campaign Success Using SQL Queries By LEKE, John Oluwagbemiga Introduction Effective marketing requires data-driven decision-making to identify which campaigns perform best. The primary objectives of this report are to use SQL to analyze a marketing campaign dataset, focusing on key metrics such as impressions, ROI, CTR, engagement scores, and cost-effectiveness to assess campaign performance. The analysis helps businesses optimize budgets, refine targeting strategies, and improve overall marketing effectiveness. By importing, cleaning, and querying the dataset, valuable insights are extracted to guide smarter marketing decisions. Objectives The primary objective of this analysis is to evaluate the performance of marketing campaigns based on key metrics such as impressions, ROI, engagement, CTR, and cost-effectiveness. The specific queries and expected outputs are as follows: 1. Calculate Total Impressions for Each Campaign Output: A table with campaign_id and total_impressions. 2. Identify the Campaign with the Highest ROI Output: A single row with campaign_id, company, and roi. 3. Find the Top 3 Locations with the Most Impressions Output: A table with location and total_impressions. 4. Calculate Average Engagement Score by Target Audience Output: A table with target_audience and avg_engagement_score. 5. Calculate the Overall CTR (Click-Through Rate) Output: A single value for the overall overall_ctr. 6. Find the Most Cost-Effective Campaign Output: A table with campaign_id, company, and cost_per_conversion. 7. Find Campaigns with CTR Above a Threshold Output: A table with campaign_id, company, and ctr. 8. Rank Channels by Total Conversions Output: A table with channel_used and total_conversions. Dataset Overview The dataset contains 200,005 entries with fields such as campaign ID, company name, campaign type, target audience, duration, channel used, conversion rate, acquisition cost, ROI, location, date, clicks, impressions, engagement score, and customer segment. Methodology Creating database A database was created, and within it, a table was created named "campaigndata" was established using this SQL syntax. Data Preparation The dataset saved in a CSV format was imported into pgAdmin 4 using the “Import/Export Data” tool. Checking our data structure The dataset was reviewed for inconsistencies and data types. The total number of rows and columns was counted, confirming 200,005 rows and 15 columns. Queries and Findings Various SQL queries were executed to extract insights, focusing on campaign performance metrics. 1. Calculate Total Impressions for Each Campaign The query below was used to calculate the total impressions for each campaign by summing up the impressions across all records associated with the same Campaign_ID and renamed the sum column as Total_Impression,. It is essential for understanding the reach of each marketing campaign. By calculating total impressions, we can evaluate campaign visibility and compare the effectiveness of different campaigns in capturing audience attention. Query: Result: 2. Identify the Campaign with the Highest ROI We used the query to identify the campaign with the highest Return on Investment (ROI) by sorting all campaigns in descending order based on ROI and selecting only the top one. It helps identify the most successful campaign based on ROI. Query: Output: 3. Find the Top 3 Locations with the Most Impressions We used the query to identify the top 3 locations with the highest number of ad impressions helping in understanding where campaigns performed best. Query: Output: 4. Calculate Average Engagement Score by Target Audience We used this syntax to calculate the average engagement score for each target audience. To improve campaign performance, marketing efforts should be adjusted based on audience engagement trends. Query: Output: 5. Calculate the Overall CTR (Click-Through Rate) This query calculates the Overall Click-Through Rate (CTR) for all marketing campaigns in the and this CTR metric helps in evaluating campaign performance and optimizing ad strategies to increase engagement and conversions. Query: Output: 6. Find the Most Cost-Effective Campaign I identify the most cost-effective campaign by calculating the cost per conversion and selecting the campaign with the lowest value. Future campaigns should analyze cost effectiveness and focus on strategies that improve conversion rates while keeping acquisition costs low. Query and Output: 7. Find Campaigns with CTR Above a Threshold This query retrieves campaigns that have a Click-Through Rate (CTR) higher than 5% and it helps to optimize marketing efforts, focusing on successful campaign while improving weaker ones. Query and Output: 8. Rank Channels by Total Conversions This query analyzes campaign performance by marketing channel and identifies which channel generated the highest total conversions. The marketing budget and resources should be allocated more towards social media and google ads, while email and Tv ads may need optimization. Query and Output: Key Insights • Top Performing Campaign: Campaigns 43755 ,60573, 26806 are the 3 top performing campaigns making it the most successful campaign in terms of profitability. • Top Locations by impressions: New York (221,359,756), Miami (221,3,47,726), and Chicago (219,999,352) generated the highest impressions, indicating these locations are key markets for future campaigns. • Engagement by Audience: The target audience "Men 18-24 Years" had the highest average engagement score of 5.51, suggesting this audience is highly responsive to the campaigns. • Cost-Effectiveness: Campaign 118451 by Alpha Innovation Company was the most cost-effective, with a cost per conversion of $-, making it a model for budget optimization. • Channel Performance: Email drove the most conversions (2697.38), highlighting its effectiveness in reaching and engaging the target audience. • CTR Performance: The overall CTR across all campaigns was 9.98%, indicating an average engagement level from audience. Recommendations 1. 2. 3. 4. 5. Increase Budget for High-Performing Channels: social media and Google Ads yielded the highest conversions, indicating that allocating more resources to these channels could further boost ROI. Optimize Campaigns with High Impressions but Low Conversions: Some campaigns had high impressions but did not translate into conversions. These should be analyzed for better targeting and message optimization. Enhance Engagement Strategies: Campaigns with low engagement scores should be adjusted by refining ad creatives, personalization, and interactive content to increase audience interaction. Focus on Cost-Effective Campaigns: Campaigns with the lowest cost per conversion should serve as a benchmark for future marketing efforts, ensuring better ROI with controlled expenses. Improve Underperforming Channels: Email and TV ads showed lower conversion rates; adjustments such as A/B testing and better audience segmentation may improve their effectiveness. Conclusion The analysis provided valuable insights into campaign effectiveness by examining impressions, ROI, CTR, and engagement scores. Key findings include: • • • Social media and Google Ads were the top-performing channels, suggesting they should receive increased investment. Some campaigns had high impressions but low conversions, indicating the need for improved targeting. Cost-effectiveness is a crucial factor, and focusing on campaigns with lower cost per conversion can enhance overall efficiency. By implementing the recommended strategies, businesses can refine their marketing approach, maximize returns, and improve engagement with their target audience. Next Steps 1. 2. 3. 4. 5. Conduct Deeper Analysis: Use segmentation analysis to understand audience behaviour across different demographics and Implement A/B testing to determine which ad variations perform best. Refine Targeting Strategies: Adjust audience targeting parameters to improve conversion rates and Use retargeting strategies for audiences that engaged but did not convert. Monitor Performance Continuously: Set up real-time dashboards to track key performance indicators (KPIs) and regularly update campaigns based on engagement and conversion trends. Test Alternative Channels: Explore emerging marketing channels and compare their effectiveness with existing ones. Enhance Data-Driven Decision-Making: Implement machine learning models to predict campaign performance and optimize ad spend. Appendix • • • SQL Queries Used: https://drive.google.com/file/d/1lQXKC4NBdACP5keFoJFDSrrLDQRWW2DH/view?usp=drive_link Dataset Summary: Total Records: 200,005 rows, Columns: 15, Imported Using: pgAdmin 4 (CSV format), Key Fields: Campaign ID, Company, Audience, Channel, ROI, Impressions, Clicks, Conversions, Engagement Score, Data Preparation: Checked for inconsistencies, verified structure, and ensured accuracy before analysis. Key Metrics Analyzed: Impressions, ROI, Engagement, CTR, CostEffectiveness, and Channel Performance. Key Metrics Definitions: Return of Investment (ROI), Click Through Rate (CTR) and cost per conversion (CPC).