From Data to Decisions: American Electronics Retail Business(Case Study)

Published in

Python’s Gurus

10 min readJun 24, 2024

A very detailed case study where I cover each step of data analysis. from data collection to Data Storytelling.

“Data analytics is the future, and the future is NOW Every mouse click, keyboard button press, swipe or tap is used to shape business decisions.”

Hey, Data points 👋

Recently, I completed a project on Electronic shop data analysis. I am happy to share with you my very descriptive case study. Here, I show each step from data collection to reporting, explaining how I did all of this

Let’s begin…🙌

Table of content

Description
Analysis process
challenges encountered
Results and Key Insights
benefit achieved
conclusion

Case Study: Increasing Average Order Value for an American Electronics Shop

1. Description

Objective: The goal of this project was to increase the average order value (AOV) for an electronics retailer in the United States.

Problem Statement: The primary challenge was to identify and implement strategies that could effectively boost the average order value without alienating the customer base or disrupting the shopping experience.

Analysis Process

Data Collection
Data Cleaning and Preparation
Analysis Methodology

customer behavior patterns
transaction histories.
Product Analysis:
Pricing Strategies
Bundling
Personalized Recommendations
User Experience Improvements
State analysis
Marketing Campaigns
Performance Tracking

4. Advanced Analytics Techniques

Segmentation Analysis
Relationship between different Parameter
Price Elasticity Analysis
Time-Series Analysis

5. Exploratory Data Analysis (EDA)

6 . Reporting

1. Data collection

1.1 Data overview

Overview of Our Data 
This is the sales data of an American electronics shop. The data is from 2019 and includes sales for each month from January to December.

The dataset has clear columns for each variable: Order ID, Product, Quantity Ordered, Price Each, Order Date, and Purchase Address.
Each row represents a unique order, making each observation (order) distinct and well-defined.
The table contains only one type of observational unit (order details).

1.2 Column Description

Column description 
Order_ID: Unique identifier for each order.
Product: Name of the product ordered.
Price_Each: Price per unit of the product. ( in $)
Order_Date: Date and time when the order was placed ( MM/DD/YY HH:MM )
Purchase_Address: Address where the product was purchased.

2. Data Cleaning and Preparation:

2.1 Assessment for cleaning

a. Manual Assessment

Manual assessment 
Dirty Data (Quality issues )

Table heading again repeated (Consistency problem )
delete those repeated headings inside the data
Blank space in row (Consistency problem )
use dropna( )
The date style format is not the same (Consistency problem )
make into one type
Messy Data (Structural issues )

b. Programmatic Assessment

Programmatic assessment ¶
Dirty Data (Quality issues )

after solving the table heading repeated problem, check duplicate again (consistency problem)
Messy Data (Structural issues )

change the dtype

2.2 Preparation

a. Modify the column name

# change the column name for users 
df.rename(columns = {'Quantity Ordered':'Quantity'}, inplace = True)
df.rename(columns = {'Order ID':'ID'}, inplace = True)
df.rename(columns = {'Price Each':'Price'}, inplace = True)
df.rename(columns = {'Order Date':'Date'}, inplace = True)
df.rename(columns = {'Purchase Address':'Address'}, inplace = True)

df

b. Add new col ( city, date, month, time column )

#add new city,  date, month, time column 
df['City'] = df['Address'].str.split(',').str[1].str.strip()

df['Month'] = pd.to_datetime(df['Date'])
df['Month'] = df['Date'].dt.month

df['Day'] = pd.to_datetime(df['Date'])
df['Day'] = df['Date'].dt.day

df['Time'] = pd.to_datetime(df['Date'])
df['Time'] = df['Date'].dt.time


df[['City','Date','Month','Day','Time']]

c. Add Quarter, Season

month = df['Month']

def get_quarterly_month (month):
    if month in [1, 2, 3]:
        return 'Q1'
    elif month in [4, 5, 6]:
        return 'Q2'
    elif month in [7, 8, 9]:
        return 'Q3'
    elif month in [10, 11, 12]:
        return 'Q4'

df['Quarter'] = df['Month'].apply(get_quarterly_month)
df['Quarter']
df

month = df['Month']

def get_season(month):
    if month in [3, 4, 5]:
        return 'Spring'
    elif month in [6, 7, 8]:
        return 'Summer'
    elif month in [9, 10, 11]:
        return 'Autumn'
    elif month in [12, 1, 2]:
        return 'Winter'

df['Season'] = df['Month'].apply(get_season)

df

d. Add City col

import re
addresses = df['Purchase Address']
addresses = df['Purchase Address']

pattern = r',\s*([A-Za-z\s]+),\s*[A-Z]{2}\s*\d*'

# Extracting cities from addresses using regular expressions
city = [re.search(pattern, address).group(1) for address in addresses]
city = pd.DataFrame(city)

city

3. Analysis Methodology

3.1 State analysis

3.2 Customer behavior patterns

3.3 Performance Tracking

3.4 Transaction histories

3.5 Product Analysis

3.6 Pricing Strategies

3.7 Bundling

3.8 Personalized Recommendations

# Find the most common product pair

df_grouped = df[df['Order ID'].duplicated(keep=False)]
df_grouped

from itertools import combinations
from collections import Counter

# Create a new column for grouped products
df_grouped['Grouped'] = df_grouped.groupby('Order ID')['Product'].transform(lambda x: ','.join(x))
df_grouped['Grouped']

# Drop duplicates so each order is only listed once
df_grouped = df_grouped[['Order ID', 'Grouped']].drop_duplicates()

# Display the modified dataframe
print(df_grouped.head())

# Initialize a Counter object
count = Counter()

# Iterate through each row in the grouped data
for row in df_grouped['Grouped']:
    products = row.split(',')
    count.update(Counter(combinations(products, 2)))

# Display the most common pairs
# print(count.most_common(10))

# most_common_pair = count.most_common(10)

r = count.most_common() 
r 

# Filter pairs with count greater than 50
filtered_pairs = [(pair, cnt) for pair, cnt in count.items() if cnt > 50]

# Convert to DataFrame
filtered_pairs_df = pd.DataFrame(filtered_pairs, columns=['Product Pair', 'Count'])
filtered_pairs_df = filtered_pairs_df.sort_values(by='Count', ascending=False)
filtered_pairs_df

# Split the Product Pair tuple into two separate columns
filtered_pairs_df[['Product 1', 'Product 2']] = pd.DataFrame(filtered_pairs_df['Product Pair'].tolist(), index=filtered_pairs_df.index)
filtered_pairs_df.drop(columns=['Product Pair'], inplace=True)

# Display the DataFrame
print(filtered_pairs_df)


# Convert 'Product Pair' column into separate columns
filtered_pairs_df[['Product 1', 'Product 2']] = pd.DataFrame(filtered_pairs_df['Product Pair'].tolist(), index=filtered_pairs_df.index)

# Drop 'Product Pair' column
filtered_pairs_df.drop(columns = filtered_pairs_df['Product Pair'], inplace=True)

print(filtered_pairs_df)

3.9 Marketing Campaigns

3.10 Performance Tracking

4. Advanced Analytics Techniques

4.1 Segmentation Analysis

4.2 Relationship between different Parameter

4.3 Price Elasticity Analysis:

4.4 Time-Series Analysis:

5. Challenges Encountered:

Data Quality Issues: We dealing with inconsistent or incomplete datasets, so we had extensive cleaning in the data set at above mention step
Complexity of Customer Behavior: Understanding customer behavior through data. Then we find the diverse customer preferences and behaviors according to requirement segmentation and analysis.
Integration of Marketing Insights: Use the sales data for marketing campaigns and insights into external factors like seasonal promotions, and purchase frequency.
Time Constraints: Ensuring time for more details to provide more insights into evolving market dynamics and customer expectations.

6. Results and Key Insights

Increased Average Order Value: Through rigorous data analysis and strategic implementations, we successfully increased the average order value (AOV) for an American electronics shop, from $180 to $250 within the next year.

Key Strategies Implemented:

Opening a New Branch in San Francisco: By opening a new store in San Francisco, we capitalized more traffic because we saw from insights that this area tech-savvy population and provides a high revenue. This move increased brand visibility and accessibility, attracting new customers and boosting overall sales.
Franchise Offer During Festivals: Offering franchise opportunities during festivals allowed the client to rapidly scale their business model while leveraging seasonal consumer spending habits. This strategy not only increased revenue streams but also expanded market presence across different regions.
Discount Strategy: Introducing a 50% discount on lightning charging cables with iPhone purchases proved highly effective. This incentive encouraged customers to add complementary products to their carts, significantly raising the average transaction value.
Promoting Profitable Products: Focusing sales efforts on high-margin items such as MacBook Pro, ThinkPad, and iPhones contributed substantially to revenue growth. These products not only attracted premium customers but also enhanced overall profitability per sale.
Discounts on Accessories: Offering periodic discounts and sales deals on batteries, charging cables, and headphones not only stimulated additional purchases but also cultivated customer loyalty. Bundling these accessories with main products further increased the perceived value of each transaction.
Unique Selling Proposition (USP): Providing certain accessories for free as part of promotional campaigns helped differentiate the client in a competitive market. This unique offering not only attracted new customers but also strengthened brand loyalty among existing ones.
Inventory Management Optimization: By streamlining inventory and phasing out products with low sales and profitability, we optimized stock levels and reduced storage costs. This allowed the client to focus resources on fast-moving, high-margin items.
Staff Training for Upselling and Cross-selling: Training staff to effectively upsell and cross-sell products improved customer satisfaction and increased average purchase amounts. This personalized approach enhanced the overall shopping experience and boosted sales.
Data Quality Enhancements: Addressing data quality issues by improving customer information capture and avoiding duplicate IDs facilitated better customer relationship management. Enhanced data accuracy enabled targeted marketing campaigns and personalized promotions, further driving sales growth.

7. Benefits Achieved

1. Increased Average Order Value (AOV) by 38.89%:

Through targeted promotions, strategic product bundling, and effective pricing strategies, we successfully raised the average order value from $180 to $250. This significant increase of 38.89% reflects our ability to encourage customers to purchase additional products or higher-value items during their shopping experience.

2. Boosted Total Profit by 24%:

Our focus on promoting high-margin products such as MacBook Pro, ThinkPad, and iPhones, coupled with cost-effective inventory management practices, led to a substantial 24% increase in total profit for the business. By optimizing sales of profitable items and reducing costs associated with slow-moving inventory, we improved overall profitability. This boost in profit not only validates the effectiveness of our strategic recommendations but also in financial health and sustainability in a competitive market.

3. Effective Inventory Management:

Including phasing out low-performing products and optimizing stock levels, resulted in streamlined operations and reduced storage costs. By maintaining an adequate inventory of fast-moving items and aligning stock with customer demand patterns identified through data analysis, we ensured product availability while minimizing overhead expenses. This efficiency not only improved cash flow but also enhanced customer satisfaction through timely product availability and reduced out-of-stock instances.

4. Enhanced Data Quality:

We also focus on addressing data quality issues by enhancing data capture processes and ensuring accurate customer information management yielded substantial benefits. By improving data accuracy and completeness — such as capturing additional details like name, email address, phone number, age, birthday, and preferred payment methods — we enhanced our ability to personalize marketing efforts and tailor promotions to individual customer preferences. This resulted in higher engagement, increased conversion rates, and improved customer retention. Moreover, avoiding duplicate customer IDs and integrating comprehensive data management protocols further strengthened our CRM capabilities, enabling more effective customer relationship management and targeted marketing campaigns.

8. Conclusion

In conclusion, our comprehensive data analysis and strategic initiatives not only achieved the primary goal of increasing the average order value but also delivered substantial benefits across profitability, inventory management, and data quality enhancement. By leveraging actionable insights derived from rigorous data analytics, we empowered the business to drive revenue growth, optimize operational efficiency, and foster stronger customer relationships in a competitive retail landscape.

This is all about…

I hope this case study helps you understand more about how to conduct data analysis. Let me know your thoughts, share your ideas in the comments, and share this article.

My Linkedin Handle [ Link ]

My Report [ Link ]

My video Report [ Link ]

My Data Analysis Project Development Pipeline

Hey, Data points👋

medium.com

Graph Selection 101: Navigating the Landscape of Data Visualization

I collected all the data-related graphs and wrote an article for you to choose the best graph.