📊 Sales Performance Dashboard: Analyzing the Impact of Media Advertising on Sales

Project Overview 🎯

This project involves the creation of an interactive Sales Performance Dashboard using SQL and Tableau. The primary objective of this dashboard is to analyze and visualize the relationship between advertising spend across different media channels (TV, Radio, and Newspaper) and sales performance. This dashboard allows stakeholders to track sales trends, understand media spend efficiency, and make data-driven decisions for future marketing campaigns.

Dataset 📈

Overview

The dataset used in this project is from Kaggle: AdvertisingCSV by Mehmet Isik. It includes the following key variables:

• TV 📺: Investment in TV advertising campaigns (in thousands of dollars).
• Radio 📻: Investment in radio advertising campaigns (in thousands of dollars).
• Newspaper 📰: Investment in newspaper advertising campaigns (in thousands of dollars).
• Sales 💸: Revenue generated from sales campaigns (in thousands of dollars).

This dataset offers a detailed breakdown of how different types of advertising investments impact sales performance over time. It is a valuable resource for marketing analysts, business strategists, and data scientists looking to analyze the efficiency of advertising spend.

Sample Data

TV	Radio	Newspaper	Sales
230.1	37.8	69.2	22.1
44.5	39.3	45.1	10.4
17.2	45.9	69.3	9.3
151.5	41.3	58.5	18.5
180.8	10.8	58.4	12.9
...	...	...	...

Dataset Features:

• TV, Radio, Newspaper: Expenditure in advertising campaigns across different channels.
• Sales: Revenue generated from sales campaigns influenced by these advertising investments.

Usage Recommendations 👨🏿‍💼

This dataset is particularly useful for:

• Economic research on advertising spend effectiveness 📊.
• Marketing analysis and campaign optimization 📈.
• Predictive modeling for future advertising ROI (Return on Investment) 📉.

Limitations ⚠️

• The dataset is based on historical data, and advertising effectiveness may change in the future.
• Assumptions regarding the relationship between media spend and sales are based on past trends, and external factors may influence future results.

Tools Used 🛠️

• SQL: Used to clean, preprocess, and aggregate data for analysis.
• Tableau: Used for creating the interactive dashboard and visualizations.

Project Structure 🗂️

Data

• Sales Performance - advertising.csv: Contains data about media spend and sales performance.

SQL Analysis

• SQL queries are written to clean, aggregate, and summarize the data, preparing it for visualization in Tableau.

Tableau Visualizations

• A set of Tableau dashboards were created to analyze and visualize key metrics like:
  • Media spend efficiency across TV, Radio, and Newspaper channels 📺📻📰.
  • Correlations between advertising spend and sales revenue 💵.
  • Time trends and seasonal effects in advertising campaigns 🕒.

Features of the Dashboard 📍

• Advertising Spend Analysis: Compare how spend across TV, Radio, and Newspaper correlates with sales performance 📊.
• Sales Forecasting: Trendlines and projections based on historical advertising spend and sales 📈.
• ROI by Channel: A bar chart showing the return on investment for each advertising channel (TV, Radio, and Newspaper) 💰.

[Sales] / ([Tv] + [Radio] + [Newspaper])

• Spend vs. Sales: Scatter plot to explore the relationship between the advertising spend and the corresponding sales generated 🔍.

• Sales Performance of Media Advertisement: Dashboard overview of all charts that shows the fluctuations in sales 📊.

Step-by-Step Python Script for Data Processing with SQL Integration

This script will:

• Load the CSV data into a pandas DataFrame.
• Create a SQLite database to store the data.
• Insert the data into a database.
• Run SQL queries to analyze the data.
• Output results to the console and optionally save results to a new CSV file.

# Import required libraries
import sqlite3  # SQLite library to interact with the database
import pandas as pd  # Pandas to read and handle the CSV file

# 1. Load the CSV data into a DataFrame
csv_file = 'Sales Performance - advertising.csv'  # Path to the CSV file
data = pd.read_csv(csv_file)  # Read CSV into a pandas DataFrame

# Normalize column names to lowercase for consistency
data.columns = data.columns.str.lower()

# Display first few rows to check data
print("Loaded Data:")
print(data.head())  # Display first 5 rows to confirm

# 2. Create and Connect to a SQLite Database
conn = sqlite3.connect('sales_performance.db')
cursor = conn.cursor()  # Create a cursor to execute SQL queries

# 3. Create the 'advertising_data' table in the SQLite database
cursor.execute('''
CREATE TABLE IF NOT EXISTS advertising_data (
    TV REAL,
    Radio REAL,
    Newspaper REAL,
    Sales REAL
);
''')
conn.commit()  # Commit the transaction to save the changes

# 4. Insert data from the pandas DataFrame into the SQLite database
for index, row in data.iterrows():
    cursor.execute('''
    INSERT INTO advertising_data (TV, Radio, Newspaper, Sales)
    VALUES (?, ?, ?, ?)
    ''', (row['tv'], row['radio'], row['newspaper'], row['sales']))

conn.commit()  # Commit to save the inserted rows into the database

# 5. Perform SQL Queries for Data Analysis

# Example 1: Get the total advertising spend and total sales
cursor.execute('''
SELECT 
    SUM(TV) AS total_tv_spend,
    SUM(Radio) AS total_radio_spend,
    SUM(Newspaper) AS total_newspaper_spend,
    SUM(Sales) AS total_sales
FROM advertising_data;
''')
total_spend_sales = cursor.fetchone()  # Fetch the result
print("\nTotal Advertising Spend and Sales:")
print(f"Total TV Spend: {total_spend_sales[0]:.2f} thousand")
print(f"Total Radio Spend: {total_spend_sales[1]:.2f} thousand")
print(f"Total Newspaper Spend: {total_spend_sales[2]:.2f} thousand")
print(f"Total Sales: {total_spend_sales[3]:.2f} thousand")

# Example 2: Calculate average spend for each medium and average sales
cursor.execute('''
SELECT 
    AVG(TV) AS avg_tv_spend,
    AVG(Radio) AS avg_radio_spend,
    AVG(Newspaper) AS avg_newspaper_spend,
    AVG(Sales) AS avg_sales
FROM advertising_data;
''')
avg_spend_sales = cursor.fetchone()  # Fetch the result
print("\nAverage Spend and Sales:")
print(f"Average TV Spend: {avg_spend_sales[0]:.2f} thousand")
print(f"Average Radio Spend: {avg_spend_sales[1]:.2f} thousand")
print(f"Average Newspaper Spend: {avg_spend_sales[2]:.2f} thousand")
print(f"Average Sales: {avg_spend_sales[3]:.2f} thousand")

# Example 3: Analyze sales and media spending relationship (TV vs Sales)
cursor.execute('''
SELECT TV, Sales 
FROM advertising_data
ORDER BY TV;
''')
tv_sales_data = cursor.fetchall()  # Get all rows from the query

print("\nTV Spend vs. Sales (Top 5 Entries):")
for row in tv_sales_data[:5]:
    print(f"TV Spend: {row[0]:.2f} | Sales: {row[1]:.2f}")

# Example 4: ROI Calculation (Return on Investment) for each medium
cursor.execute('''
SELECT 
    TV, Sales, 
    CASE WHEN TV != 0 THEN Sales / TV ELSE 0 END AS tv_roi,
    Radio, 
    CASE WHEN Radio != 0 THEN Sales / Radio ELSE 0 END AS radio_roi,
    Newspaper, 
    CASE WHEN Newspaper != 0 THEN Sales / Newspaper ELSE 0 END AS newspaper_roi
FROM advertising_data;
''')
roi_data = cursor.fetchall()  # Get all ROI data

print("\nSample ROI Data (TV, Radio, Newspaper vs Sales):")
for row in roi_data[:5]:  # Display first 5 results
    print(f"TV ROI: {row[2]:.2f}, Radio ROI: {row[4]:.2f}, Newspaper ROI: {row[6]:.2f} | Sales: {row[1]:.2f}")

# 6. Close the connection to the database
conn.close()

# 7. Optionally, save processed data to a new CSV file
output_csv_file = "processed_sales_data.csv"
data.to_csv(output_csv_file, index=False)
print(f"\nProcessed data saved to {output_csv_file}")

Explanation of Each Step

1. Loading Data: We read the CSV file into a pandas DataFrame:

data = pd.read_csv(csv_file)

This allows you to easily manipulate the data and pass it into the SQLite database.

2. Creating the SQLite Database: We connect to an SQLite database, creating it if it doesn't already exist:

conn = sqlite3.connect('sales_performance.db')

A table advertising_data is created in the database to hold the CSV data.

3. Inserting Data into the Database: The script loops through the DataFrame and inserts each row into the advertising_data table in the database:

for index, row in data.iterrows():
    cursor.execute('INSERT INTO advertising_data ...', (row['TV'], row['Radio'], row['Newspaper'], row['Sales']))

4. Running SQL Queries: The script performs SQL queries to aggregate and analyze the data:

• Total Spend and Sales: Using SUM(), we get the total advertising spend across TV, Radio, Newspaper, and total sales.
• Average Spend and Sales: Using AVG(), we get the average spend and sales across all data.
• TV Spend vs. Sales: We analyze the relationship between TV spending and sales.
• ROI (Return on Investment): For each medium, we calculate ROI as the ratio of sales to advertising spend.

5. Saving Processed Data: After analysis, the script saves the original data to a new CSV file processed_sales_data.csv:

data.to_csv(output_csv_file, index=False)

6. Closing the Connection: Finally, we close the SQLite database connection to free up resources:

conn.close()

How to Run the Script

1. Install Required Libraries: If you don’t have pandas installed, run:

pip install pandas

sqlite3 is included with Python by default, so you don’t need to install it separately.

2. Run the Script: Save the script to a .py file (e.g., process_advertising_data.py) and execute it:

python process_advertising_data.py

Output The script will output:

• Total and average spend on TV, Radio, and Newspaper.
• A snapshot of the relationship between TV spend and sales.
• ROI values for each medium.
• A new processed_sales_data.csv file with the original data.

Instructions for Use 📝

1. Clone this repository:

   git clone https://github.com/Willie-Conway/sales-performance-dashboard.git

Instructions for Use 📝

1. Prepare Data:

Ensure the dataset Sales Performance - advertising.csv is in the correct folder.

2. SQL Queries:

• Open the sql/queries.sql file and run the queries to process the data.
• Make sure your SQL database is set up and populated with the dataset.

3. Open Tableau Dashboard:

• Open the Sales Performance Dashboard_v2024.2.twbx Tableau file.
• Make sure the processed data is connected to the Tableau workbook.

Key Visualizations in the Dashboard 📊

• Media Spend Breakdown: A pie chart that displays the distribution of advertising spend across the three media channels 📺📻📰.
• Sales Performance by Channel: Line charts that visualize how sales correlate with media spending over time 📈.
• ROI by Channel: A bar chart showing the return on investment for each advertising channel (TV, Radio, and Newspaper) 💸.
• Spend vs. Sales: Scatter plot to explore the relationship between the advertising spend and the corresponding sales 💡.

Contributing 🫱🏿‍🫲🏿

Contributions are welcome! If you have any suggestions or improvements, feel free to fork this project and create a pull request.

License 📄

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments 🙏🏿

• Data source: AdvertisingCSV by Mehmet Isik on Kaggle
• Tableau: For creating the powerful visualizations that bring insights to life 🖥️.
• SQL: For preprocessing and aggregating the data efficiently 🔢.

Contact Information 📬

For questions or feedback, please reach out to:

• Email: hire.willie.conway@gmail.com
• LinkedIn: linkedin.com/in/willieconway