Retail Sales Distribution Analysis - A Data Quality Detective Story

The Goal

After successfully cleaning and analyzing the city demographics data, I moved to the core of the business problem: understanding the retail sales distribution patterns across GlobalBev's distribution channels.

My objectives were to:

• Explore the sales transaction data

• Understand which channels are performing best

• Identify patterns that would inform the ML model for channel optimization

• Uncover insights about product-channel relationships

Starting Point: Loading the Sales Data

I began by loading the retail sales dataset and examining its basic structure:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# Load retail sales data
df_sales = pd.read_csv('retailsalesdistribution.csv')

# Display first 10 rows
print(df_sales.head(10))

# Check the shape
print(f"\nDataset shape: {df_sales.shape}")

The dataset contained 334,300 transactions with 6 columns: Date, City_ID, SKU_ID, Channel, Units_Sold, and Sales.

Challenge #1: Understanding SKU References

Looking at the data, I noticed SKU codes like "SKU1002", "SKU1004", etc. I had explored cities in Milestone 1, but now I needed to understand what these product SKUs represented.

I loaded the products dataset:

# Load products data
df_products = pd.read_csv('products.csv')

# Display all products (only 7 SKUs)
print(df_products)

This revealed 7 products in GlobalBev's portfolio - all Minute Maid fruit juices with different flavors and pack sizes.

Learning: What Defines an SKU in the Real World?

I had to pause and understand: What exactly is an SKU? Is it just the flavor? Or does pack size matter?

After examining the products data, I learned that an SKU (Stock Keeping Unit) is uniquely defined by:

• Product + Flavor + Pack Size = Unique SKU

For example:

• Mixed Fruit flavor has 3 different SKUs (1L, 135ml Pouch, 250ml Bottle)

• Orange flavor has 2 different SKUs (1L, 250ml Bottle)

Launch date is just metadata, not part of the SKU definition.

# Check how many SKUs exist per flavor
print(df_products.groupby('Flavor Variant')['SKU Identification Number'].count())

Output confirmed:

• Apple: 1 SKU

• Guava: 1 SKU

• Mixed Fruit: 3 SKUs

• Orange: 2 SKUs

• Total: 7 SKUs

I also noticed SKU1005 was missing from the numbering sequence, which I verified in both datasets:

# Check which SKUs appear in sales data
print("SKUs in sales data:")
print(sorted(df_sales['SKU_ID'].unique()))
print(f"\nTotal unique SKUs in sales: {df_sales['SKU_ID'].nunique()}")

# Compare with products list
print("\nSKUs in products data:")
print(sorted(df_products['SKU Identification Number'].values))

SKU1005 didn't exist in either dataset - likely never created or the numbering skipped it.

Data Quality Check

Before diving into analysis, I checked for basic data quality issues:

# Check data types
print("Data types:")
print(df_sales.dtypes)

print("\n" + "="*50)

# Check for missing values
print("\nMissing values:")
print(df_sales.isnull().sum())

Good news: No missing values across any columns.

However, I noticed the Date column was stored as object (string) instead of datetime. This needed fixing for time-based analysis:

# Convert Date to datetime
df_sales['Date'] = pd.to_datetime(df_sales['Date'])

# Verify conversion
print("After conversion:")
print(df_sales.dtypes)

# Check date range
print(f"\nDate range: {df_sales['Date'].min()} to {df_sales['Date'].max()}")

The data covered 2 years: January 1, 2023 to December 31, 2024.

Exploring Distribution Channels

Now to the core question: What channels exist and how are they performing?

# Check unique channels
print("Unique Channels:")
print(df_sales['Channel'].unique())

print(f"\nTotal unique channels: {df_sales['Channel'].nunique()}")

# Count records per channel
print("\nRecords per channel:")
print(df_sales['Channel'].value_counts())

5 distribution channels emerged:

• General Trade: 101,360 transactions (most)

• E Commerce: 73,100 transactions

• Modern Trade: 72,120 transactions

• HoReCa: 58,480 transactions

• Q Commerce: 29,240 transactions (least)

But transaction count isn't the full story...

The Revenue Reality

I calculated total revenue and units sold per channel:

# Calculate total revenue and units per channel
channel_performance = df_sales.groupby('Channel').agg({
    'Sales': 'sum',
    'Units_Sold': 'sum',
    'Date': 'count'
}).round(2)

channel_performance.columns = ['Total_Revenue', 'Total_Units', 'Transaction_Count']
channel_performance = channel_performance.sort_values('Total_Revenue', ascending=False)

print(channel_performance)

Key Finding: General Trade dominated across all metrics:

• Revenue: ₹1.58 billion

• Units: 92M

• Transactions: 101,360

But something interesting: Q Commerce had fewer transactions than HoReCa (29,240 vs 58,480) yet generated higher revenue (₹236M vs ₹202M).

Challenge #2: The Transaction Value Mystery

This revenue-to-transaction mismatch suggested different channels had different transaction values. I calculated the average:

# Calculate average transaction value per channel
channel_performance['Avg_Revenue_Per_Transaction'] = (
    channel_performance['Total_Revenue'] / channel_performance['Transaction_Count']
).round(2)

print(channel_performance[['Total_Revenue', 'Transaction_Count', 'Avg_Revenue_Per_Transaction']])

Shocking discovery: General Trade's average transaction (₹15,633) was 194x larger than Q Commerce (₹81)!

This suggested fundamentally different business models:

• General Trade: Likely bulk B2B orders to retailers

• Q Commerce: Small individual consumer orders

I verified by checking units per transaction:

# Calculate average units per transaction
channel_performance['Avg_Units_Per_Transaction'] = (
    channel_performance['Total_Units'] / channel_performance['Transaction_Count']
).round(2)

print(channel_performance[['Avg_Revenue_Per_Transaction', 'Avg_Units_Per_Transaction']])

Interesting gap: Units were only 3.4x different (910 vs 269) but revenue was 194x different.

This massive gap meant there had to be huge price differences.

The Pricing Puzzle

I calculated average price per unit:

# Calculate price per unit
channel_performance['Avg_Price_Per_Unit'] = (
    channel_performance['Total_Revenue'] / channel_performance['Total_Units']
).round(2)

print(channel_performance[['Avg_Units_Per_Transaction', 'Avg_Revenue_Per_Transaction', 'Avg_Price_Per_Unit']])

The anomaly: General Trade's price per unit (₹17.19) was 57x higher than Q Commerce (₹0.30).

Wait - this didn't make sense. Q Commerce should charge premium prices for convenience, not have the lowest prices!

Investigation: What's Each Channel Actually Selling?

I suspected different channels might be selling different products. Time to investigate:

# Check SKU distribution across channels
sku_channel = df_sales.groupby(['Channel', 'SKU_ID']).agg({
    'Sales': 'sum',
    'Units_Sold': 'sum'
}).reset_index()

# For each channel, show top SKUs
for channel in df_sales['Channel'].unique():
    print(f"\n{channel}:")
    channel_data = sku_channel[sku_channel['Channel'] == channel].sort_values('Sales', ascending=False)
    print(channel_data)

Critical finding:

• Q Commerce only sells 2 SKUs (SKU1002, SKU1004)

• General Trade sells all 7 SKUs

This product mix difference could explain the pricing gap. I needed to see which pack sizes each channel was selling:

# Merge sales with products to see pack sizes
sales_with_product = df_sales.merge(
    df_products[['SKU Identification Number', 'Pack Size (ml/L)', 'Flavor Variant']], 
    left_on='SKU_ID', 
    right_on='SKU Identification Number',
    how='left'
)

# Check pack sizes sold per channel
print("Pack sizes sold by channel:")
for channel in sales_with_product['Channel'].unique():
    print(f"\n{channel}:")
    pack_dist = sales_with_product[sales_with_product['Channel'] == channel]['Pack Size (ml/L)'].value_counts()
    print(pack_dist)

Another puzzle: Q Commerce only sold 1L bottles, while General Trade sold all sizes including small 135ml pouches.

But this deepened the mystery: If Q Commerce only sells large bottles, why is its price per unit so much lower?

Testing: Same SKU, Different Channels

I needed to isolate the channel effect by looking at the same SKU across channels:

# Check price per unit for SKU1002 (sold in both Q Commerce and General Trade)
sku1002_data = df_sales[df_sales['SKU_ID'] == 'SKU1002'].groupby('Channel').agg({
    'Sales': 'sum',
    'Units_Sold': 'sum'
})

sku1002_data['Price_Per_Unit'] = (sku1002_data['Sales'] / sku1002_data['Units_Sold']).round(2)
print("SKU1002 (same product, different channels):")
print(sku1002_data)

Resolution: All channels sold SKU1002 at the exact same price per unit (₹0.3).

This meant the huge channel-level price differences weren't due to pricing strategies, but due to product mix - which SKUs each channel chose to sell.

Understanding the SKU Price Structure

Now I needed to see how prices varied across all SKUs:

# Calculate price per unit for each SKU
sku_pricing = df_sales.groupby('SKU_ID').agg({
    'Sales': 'sum',
    'Units_Sold': 'sum'
})
sku_pricing['Price_Per_Unit'] = (sku_pricing['Sales'] / sku_pricing['Units_Sold']).round(2)

# Merge with product info
sku_pricing_with_info = sku_pricing.merge(
    df_products[['SKU Identification Number', 'Pack Size (ml/L)', 'Flavor Variant']], 
    left_index=True,
    right_on='SKU Identification Number'
)

print(sku_pricing_with_info[['SKU Identification Number', 'Pack Size (ml/L)', 'Price_Per_Unit']].sort_values('Price_Per_Unit'))

Strange pattern:

• 1L bottles: ₹0.30 per unit

• 135ml pouch: ₹40.50 per unit

• 250ml bottles: ₹75.00 per unit

The small packs appeared to be massively more expensive. But this felt off - was this a unit definition issue?

Challenge #3: The 1000x Pricing Anomaly

To normalize across pack sizes, I calculated price per ml:

# Calculate price per ml for each SKU
def extract_ml(pack_size):
    if 'L' in pack_size and 'ml' not in pack_size:
        return 1000  # 1L = 1000ml
    else:
        return int(''.join(filter(str.isdigit, pack_size)))

sku_pricing_with_info['Volume_ml'] = sku_pricing_with_info['Pack Size (ml/L)'].apply(extract_ml)
sku_pricing_with_info['Price_Per_ml'] = (sku_pricing_with_info['Price_Per_Unit'] / sku_pricing_with_info['Volume_ml']).round(4)

print(sku_pricing_with_info[['SKU Identification Number', 'Pack Size (ml/L)', 'Price_Per_Unit', 'Volume_ml', 'Price_Per_ml']])

Critical data quality issue discovered:

• 1L bottles: ₹0.0003 per ml

• Small packs (135ml, 250ml): ₹0.30 per ml

Small packs were 1000x more expensive per ml than large bottles!

The Reality Check: Does This Make Sense?

I paused to evaluate this against real-world CPG dynamics. While small convenience packs typically have a premium, it's usually:

• Real world: 2-5x more expensive per ml

• My data: 1000x more expensive per ml

This contradicted reality. For context:

• Real example: 250ml Coke ≈ ₹20 (₹0.08/ml) vs 2L bottle ≈ ₹80 (₹0.04/ml) = 2x difference

• My data: 1000x difference

Moreover, my finding that Q-Commerce only sells 1L bottles contradicted actual Q-Commerce business models:

• Blinkit, Zepto, Swiggy Instamart specialize in immediate consumption

• They thrive on small, single-serve packs (250ml, 135ml)

• Customers order when they want a drink NOW, not bulk storage

• Small convenience packs are their highest margin items

# Document findings
print("="*80)
print("DATA QUALITY ISSUE IDENTIFIED")
print("="*80)
print("\nFinding: Small pack pricing shows 1000x premium over large packs")
print("Expected: Real-world CPG pricing typically shows 2-5x premium")
print("\nPossible causes:")
print("1. Unit definition inconsistency across pack sizes")
print("2. Decimal point errors in data entry")  
print("3. Different measurement units (cases vs individual units)")
print("\nImpact: Channel-level price analysis may be misleading")
print("Recommendation: Validate with business stakeholders before model training")

Key Learnings from this experience

Technical Skills:

• Data merging across multiple datasets

• Calculating derived metrics (averages, ratios)

• Multi-level groupby aggregations

• Cross-validation of findings across different cuts of data

Business Understanding:

• SKU definitions in CPG/retail context

• Channel business models (B2B bulk vs B2C individual)

• Product mix impact on performance metrics

• Real-world CPG pricing dynamics

Critical Thinking:

• Identified data quality issues through domain knowledge validation

• Understood when numbers don't match reality

• Chose documentation over data fabrication

• Recognized that finding issues is as valuable as finding insights

Insights Despite Data Quality Issues:

1. General Trade dominates in revenue, volume, and transactions

2. Q Commerce has different economics - fewer, smaller transactions

3. Product mix varies by channel - not all channels sell all SKUs

4. Same products are priced consistently across channels (when sold)

5. Data quality issues exist that need stakeholder validation before ML modeling