Project Overview

Business Objective

How have different advertising platforms performed across major U.S. states in 2023, and how can we adjust marketing strategies to maximize performance in the coming year?

This project analyzes advertising platform performance across key U.S. regions (East, West, North, and South) from January to October 2023. The goal is to uncover trends in product category sales, marketing effectiveness, and regional variations, and use those insights to recommend data-driven marketing strategies for the upcoming year.

Dataset Overview

Data Sources - Sourced advertising platform sales data from Kaggle, and generated synthetic product data using ChatGPT.

Tools Used: Microsoft Excel (Power Query, Pivot Tables, Formulas, Dashboarding)

Dataset Description: The dataset contains sales data with the following fields:

Region: Geographical area (East, West, North, South) State: Location of sales (New York, California, Illinois, Texas) Country: United States Product ID: Unique product identifier Product Category: One of 5 distinct categories Product Price: Cost per product unit Product Sold: Quantity of units sold Sales Date: Transaction date Discount Offered: Applied discount amount Sales Channel: Method of sale (instore or online) Total Sales: Combined revenue from all channels, including company website and advertising platforms Total Sales In-store Website: Revenue from company website Sales by Advertising Channel:

Data Cleaning

Steps Taken:

  1. Formatted Headers – Centered and bolded for clarity
  2. Reorganized Columns – Grouped category + product data together
  3. Removed Duplicates – Excel’s built-in function to avoid skewed aggregates
  4. Blank Cells Removed – CTRL + G → Special → Blanks → Delete
  5. Checked Data Types – Ensured currency consistency
  6. Flagged Outliers – E.g., Discounts greater than price (IF($I2>=$F2,"Yes","No"))
  7. Mapped State to Region – Used INDEX-MATCH logic to auto-populate Region column
  8. Calculated Total Sales (In-Store Only):
IF([@[Sales_Channel]]="In-Store",[@[Total_sales]],[@[Total_sales]]-SUM(P2:T2))