Digital Marketing Metrics & KPIs to Measure (SQL)

Introduction

This project entails a comprehensive analysis of marketing spending data to understand the effectiveness of various advertising campaigns. It aims to identify key performance metrics such as Click-Through Rate (CTR), Conversion Rates (Conv_1 and Conv_2), Average Order Value (AOV), Cost Per Click (CPC), Customer Acquisition Cost (CAC), and Return on Marketing Investment (ROMI) across different campaigns and time frames.

Project Setup

1. Data Importation: Begun by importing necessary Python libraries (Pandas) and loading the marketing dataset into a DataFrame for manipulation and analysis.

Analysis Tasks

Task 1: Metrics Calculation

Calculated essential marketing metrics to evaluate campaign performance:

• CTR (Click-Through Rate): The percentage of impressions that led to a click.
• Conv_1 (Conversion Rate 1): The ratio of leads generated from clicks.
• Conv_2 (Conversion Rate 2): The ratio of orders generated from leads.
• AOV (Average Order Value): The average revenue per order.
• CPC (Cost Per Click): The average cost incurred for each click.
• CAC (Customer Acquisition Cost): The cost of acquiring a new customer through a specific campaign.
• ROMI (Return On Marketing Investment): The return generated for every dollar spent on marketing.

Address specific questions related to these metrics for different campaigns and dates.

Task 2: Overall ROMI Calculation

Calculated the overall ROMI for all campaigns combined, providing a high-level view of marketing efficiency.

Task 3: Campaign-specific ROMI

Calculated ROMI for the 'instagram_blogger' campaign within a specific date range, offering insights into the campaign's targeted performance.

Task 4: Weekend vs. Weekday Revenue Analysis

Analyzed average revenue generated on weekends (Saturday and Sunday) compared to weekdays, identifying patterns in consumer spending.

Task 5: Identifying Worst-performing Campaign

Determined the campaign that experienced the worst loss in a single day, indicating areas that require immediate attention.

Task 6: Facebook Campaign Analysis

Assessed the total money spent on Facebook campaigns with a negative ROMI, highlighting potential areas for budget reallocation or strategy adjustment.

Conclusion

The analysis concludes with actionable insights derived from calculating various metrics, comparing performance across campaigns, and identifying areas of loss. These findings aim to guide future marketing strategies, budget allocation, and campaign optimization to enhance overall marketing effectiveness.