13/06/2025, 11:33 Business_Analysis.

ipynb - Colab

Instructor : Col Jai govind(Retd), Associate Professor CHRIST University, Kengeri, Bangalore

Complete Python notebook using a simple sample dataset to demonstrate Business Analysis with pandas library of Python

This complete notebook demonstrates:

1. Dataset creation with realistic business data

2. Core financial metric calculations
3. Product/marketing/customer analysis
4. Strategic segmentation
5. Time-based trend analysis
6. ROI calculations
7. Visual reporting
8. Actionable business insights
9. Pivot Tables

[Link] Context: Creating Sample Sales Data

We'll generate a small dataset representing:

100 transactions from Jan-Mar 2025

Products: Electronics, Clothing, Furniture

Marketing channels: Email, Social, Search engines

import pandas as pd
import numpy as np

[Link](42)
data = {
'transaction_id': range(1001, 1101),
'date': pd.date_range('2023-01-01', periods=100).tolist(),
'product_category': [Link](['Electronics', 'Clothing', 'Furniture'], 100),
'unit_price': [Link]([Link](50, 500, 100), 2),
'quantity': [Link](1, 10, 100),
'cost': [Link]([Link](30, 300, 100), 2),
'marketing_channel': [Link](['Email', 'Social', 'Search'], 100),
'customer_id': [Link](['C100', 'C101', 'C102', 'C103', 'C104'], 100)
}

df = [Link](data)
print("Sample dataset created with 100 transactions")
[Link](3)

Sample dataset created with 100 transactions

transaction_id date product_category unit_price quantity cost marketing_channel customer_id

0 1001 2023-01-01 Furniture 139.42 3 115.57 Search C101

1 1002 2023-01-02 Electronics 52.48 3 75.76 Social C101

2 1003 2023-01-03 Furniture 416 96 3 180 34 Email C104

Next steps: Generate code with df toggle_off View recommended plots New interactive sheet

Business Context: Revenue & Profit Calculation

# - Revenue = Quantity × Unit Price

# - Profit = Revenue - (Quantity × Cost)
df['revenue'] = df['quantity'] * df['unit_price']
df['profit'] = df['revenue'] - (df['quantity'] * df['cost'])
df['profit_margin'] = (df['profit'] / df['revenue']) * 100

# Show transaction-level profitability

print("\nAdded business metrics:")
df[['transaction_id', 'revenue', 'profit', 'profit_margin']].head(3)

[Link] 1/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab

Added business metrics:

transaction_id revenue profit profit_margin

0 1001 418.26 71.55 17.106584

1 1002 157.44 -69.84 -44.359756

2 1003 1250 88 709 86 56 748849

keyboard_arrow_down Business Context: Overall Performance Dashboard

# Calculate company-wide KPIs:
total_revenue = df['revenue'].sum()
total_profit = df['profit'].sum()
avg_margin = df['profit_margin'].mean()
print(f" Overall Performance:")
print(f"• Total Revenue: ₹{total_revenue:,.2f}")
print(f"• Total Profit: ₹{total_profit:,.2f}")
print(f"• Avg Profit Margin: {avg_margin:.1f}%")

Overall Performance:
• Total Revenue: ₹114,689.94
• Total Profit: ₹37,281.25
• Avg Profit Margin: 11.9%

Business Context: Product Performance Analysis

# Identify best/worst performing product categories

product_performance = [Link]('product_category').agg(
total_revenue=('revenue', 'sum'),
total_profit=('profit', 'sum'),
avg_margin=('profit_margin', 'mean'),
units_sold=('quantity', 'sum')
).sort_values('total_profit', ascending=False)

print(" Product Performance Ranking:")

product_performance

Product Performance Ranking:

total_revenue total_profit avg_margin units_sold

product_category

Clothing 45674.91 16116.51 23.322232 172

Electronics 38064.61 13433.73 12.690804 142

Furniture 30950 42 7731 01 -2 349563 149

Next steps: Generate code with product_performance toggle_off View recommended plots New interactive sheet

Business Context: Profitability Segmentation

# Classify products into strategic tiers:

product_performance['tier'] = [Link](
product_performance['avg_margin'],
bins=[0, 15, 30, 100],
labels=['Low', 'Medium', 'High']
)

print(" Product Profitability Tiers:")

product_performance[['avg_margin', 'tier']]

[Link] 2/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab

Product Profitability Tiers:

avg_margin tier

product_category

Clothing 23.322232 Medium

Electronics 12.690804 Low

Furniture -2 349563 NaN

Business Context: Sales Trend Analysis

# Track monthly revenue and profit

monthly_trends = df.set_index('date').resample('M').agg(
monthly_revenue=('revenue', 'sum'),
monthly_profit=('profit', 'sum')
)

print(" Monthly Sales Trends:")

monthly_trends

Monthly Sales Trends:

<ipython-input-10-460062894>:2: FutureWarning: 'M' is deprecated and will be removed in a future version, please use 'ME' instead.
monthly_trends = df.set_index('date').resample('M').agg(
monthly_revenue monthly_profit

date

2023-01-31 35426.11 5060.74

2023-02-28 34510.69 16140.05

2023-03-31 34739.78 13026.89

2023-04-30 10013 36 3053 57

Next steps: Generate code with monthly_trends toggle_off View recommended plots New interactive sheet

Business Context: Marketing Efficiency Analysis

# Calculate ROI for marketing channels

marketing_spend = [Link]({
'marketing_channel': ['Email', 'Social', 'Search'],
'spend': [500, 1500, 1200]
})

channel_performance = [Link]('marketing_channel').agg(
channel_revenue=('revenue','sum'),
channel_profit=('profit','sum')
).reset_index()

roi_data = [Link](channel_performance, marketing_spend)

roi_data['roi'] = (roi_data['channel_profit'] - roi_data['spend']) / roi_data['spend']

print(" Marketing Channel ROI:")

roi_data[['marketing_channel', 'spend', 'channel_profit', 'roi']]

Marketing Channel ROI:

marketing_channel spend channel_profit roi

0 Email 500 10267.72 19.535440

1 Search 1200 16562.60 12.802167

2 Social 1500 10450 93 5 967287

Business Context: Customer Value Segmentation

# Identify high-value customers for loyalty programs

customer_value = [Link]('customer_id').agg(
total_spend=('revenue', 'sum'),
purchase_count=('transaction_id', 'count'),
avg_profit_margin=('profit_margin', 'mean')

[Link] 3/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab
).sort_values('total_spend', ascending=False)

# Classify top 30% customers as VIP

customer_value['segment'] = [Link](customer_value['total_spend'],
q=[0, 0.7, 1],
labels=['Standard', 'VIP'])

print(" Customer Segmentation:")

customer_value

Customer Segmentation:
total_spend purchase_count avg_profit_margin segment

customer_id

C103 31466.86 24 3.249060 VIP

C104 28895.15 24 34.502138 VIP

C101 20746.03 20 11.445495 Standard

C102 18755.78 15 8.547579 Standard

C100 14826 12 17 -4 564231 Standard

Next steps: Generate code with customer_value toggle_off View recommended plots New interactive sheet

Business Context: Executive Summary Visualization

# Visualize key findings for stakeholders

import [Link] as plt

# 1. Monthly Performance Trends

[Link](figsize=(12, 5))
[Link](1, 2, 1)
monthly_trends['monthly_revenue'].plot(kind='bar', color='skyblue')
[Link]('Monthly Revenue')
[Link]('₹')

[Link](1, 2, 2)
monthly_trends['monthly_profit'].plot(kind='bar', color='lightgreen')
[Link]('Monthly Profit')
plt.tight_layout()
[Link]()

# 2. Product Profitability
[Link](figsize=(8, 5))
product_performance['total_profit'].plot(kind='pie', autopct='%1.1f%%')
[Link]('Profit Distribution by Product Category')

[Link] 4/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab
[Link]('')
[Link]()

# 3. Marketing ROI Comparison

[Link](figsize=(7, 4))
roi_data.set_index('marketing_channel')['roi'].plot(kind='barh')
[Link]('Marketing Channel ROI')
[Link]('Return on Investment')
[Link]()

Key Business Insights from Sample Data

# Business Context: Actionable Insights

# 1. Product Strategy Recommendation
print(" Product Strategy Insight:")
print(f"Discontinue {product_performance[product_performance['tier']=='Low'].index[0]} due to low margins")

Product Strategy Insight:

Discontinue Electronics due to low margins

# 2. Marketing Optimization
best_channel = roi_data.loc[roi_data['roi'].idxmax(), 'marketing_channel']
print(f" Increase budget for '{best_channel}' channel (highest ROI)")

Increase budget for 'Email' channel (highest ROI)

[Link] 5/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab
# 3. Customer Retention Focus
vip_contribution = customer_value[customer_value['segment']=='VIP']['total_spend'].sum() / customer_value['total_spend'].sum()
print(f" VIP customers ({customer_value[customer_value['segment']=='VIP'].shape[0]}) contribute {vip_contribution:.1%} of revenue")

VIP customers (2) contribute 52.6% of revenue

# 4. Seasonal Planning
best_month = monthly_trends['monthly_profit'].idxmax().strftime('%B')
print(f" Peak profitability in {best_month} - plan inventory accordingly")

Peak profitability in February - plan inventory accordingly

Sample Output Insights Overall Performance: • Total Revenue: 24, 519.50 ∙ T otalP rof it :9,568.89 • Avg Profit Margin: 38.3%

Product Strategy Insight: Discontinue Furniture due to low margins

Increase budget for 'Email' channel (highest ROI)

VIP customers (2) contribute 42.0% of revenue

Peak profitability in March - plan inventory accordingly

PIVOT TABLE

Pivot Table Business Applications:

Pivot Table Business Application

Monthly Revenue by Category Seasonal planning and inventory forecasting

Profit by Region/Category Resource allocation to high-margin combinations

Sales Volume by Month/Region Supply chain optimization and logistics planning

Multi-metric Analysis Comprehensive performance dashboards for regional managers

Why Pivot Tables are Powerful for Business Analysis:

1. Multi-dimensional Analysis: Compare metrics across different business dimensions (time, region, product)
2. Data Summarization: Transform detailed transaction data into actionable summaries
3. Pattern Identification: Quickly spot trends, outliers, and opportunities
4. Interactive Exploration: Easily rearrange dimensions to answer different questions
5. Visualization Ready: Structured output perfect for charts and heatmaps

This example shows how pivot tables transform raw sales data into strategic insights for:

Targeted marketing campaigns

Regional performance optimization
Seasonal inventory planning
Product portfolio management
Resource allocation decisions

The pivot tables serve as the analytical engine that converts transactional data into business intelligence.

[Link](42)
data = {
'date': pd.date_range('2023-01-01', periods=100),
'product_category': [Link](['Electronics', 'Clothing', 'Furniture'], 100),
'region': [Link](['North', 'South', 'East', 'West'], 100),
'unit_price': [Link]([Link](50, 500, 100), 2),
'quantity': [Link](1, 10, 100),
'cost': [Link]([Link](30, 300, 100), 2),
}

df = [Link](data)
df['revenue'] = df['quantity'] * df['unit_price']
df['profit'] = df['revenue'] - (df['quantity'] * df['cost'])
df['month'] = df['date'].dt.month_name()

print("Sample dataset created with 100 transactions")

df[['date', 'product_category', 'region', 'quantity', 'revenue']].head()

[Link] 6/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab

Sample dataset created with 100 transactions

date product_category region quantity revenue

0 2023-01-01 Furniture West 1 453.24

1 2023-01-02 Electronics West 3 579.30

2 2023-01-03 Furniture West 6 597.12

3 2023-01-04 Furniture East 7 1067.99

4 2023-01-05 Electronics East 6 1453 20

# Business Context: Pivot Table 1 - Monthly Revenue by Product Category

# Business Question: How does revenue for each product category vary month-to-month?
# Why: Identify seasonal trends and product performance patterns

pivot_monthly_category = pd.pivot_table(
df,
values='revenue',
index='month',
columns='product_category',
aggfunc='sum',
fill_value=0
)

print("Monthly Revenue by Product Category:")

pivot_monthly_category

Monthly Revenue by Product Category:

product_category Clothing Electronics Furniture

month

April 0.00 11817.07 4675.49

February 8399.26 12127.19 16105.89

January 7004.34 11256.14 10968.66

March 29303 51 4525 54 9029 60

Next steps: Generate code with pivot_monthly_category toggle_off View recommended plots New interactive sheet

# Business Context: Pivot Table 2 - Profit Margins by Region and Category

# Business Question: Which region-category combinations have the highest profitability?
# Why: Optimize regional product assortments and marketing focus

pivot_profit_margin = pd.pivot_table(
df,
values='profit',
index='region',
columns='product_category',
aggfunc='mean', # Average profit per transaction
fill_value=0,
margins=True, # Add grand totals
margins_name='All Regions'
)

print("Average Profit per Transaction by Region and Category:")

pivot_profit_margin

Average Profit per Transaction by Region and Category:

product_category Clothing Electronics Furniture All Regions

region

East 487.550769 1069.278889 133.106667 516.439706

North 364.996250 -223.645000 1048.948750 453.167727

South 379.100000 269.272222 -149.740000 243.575263

West 932.781250 219.102222 270.271250 463.853600

All Regions 538 168889 384 151818 377 478065 437 529100

[Link] 7/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab

Next steps: Generate code with pivot_profit_margin toggle_off View recommended plots New interactive sheet

# Business Context: Pivot Table 3 - Sales Volume Analysis

# Business Question: How do sales quantities vary across regions and months?
# Why: Understand regional demand patterns and inventory distribution needs

pivot_sales_volume = pd.pivot_table(
df,
values='quantity',
index='month',
columns='region',
aggfunc='sum',
fill_value=0,
margins=True,
margins_name='Total'
)

print("Total Units Sold by Month and Region:")

pivot_sales_volume

Total Units Sold by Month and Region:

region East North South West Total

month

April 14 0 17 19 50

February 64 39 24 21 148

January 57 24 12 35 128

March 39 43 30 40 152

Total 174 106 83 115 478

Next steps: Generate code with pivot_sales_volume toggle_off View recommended plots New interactive sheet

# Business Context: Pivot Table 4 - Profitability Analysis with Multiple Metrics

# Business Question: What's the complete performance picture by region and category?
# Why: Comprehensive view for regional managers to identify strengths/weaknesses

pivot_full_analysis = pd.pivot_table(
df,
values=['revenue', 'profit', 'quantity'],
index='region',
columns='product_category',
aggfunc={
'revenue': 'sum',
'profit': 'sum',
'quantity': 'count' # Number of transactions
},
fill_value=0
)

print("Comprehensive Performance Analysis by Region and Category:")

pivot_full_analysis

Comprehensive Performance Analysis by Region and Category:

profit quantity revenue

product_category Clothing Electronics Furniture Clothing Electronics Furniture Clothing Electronics Furniture

region

East 6338.16 9623.51 1597.28 13 9 12 16317.03 17414.93 13024.46

North 2919.97 -1341.87 8391.59 8 6 8 8876.15 3126.35 15585.50

South 2653.70 2423.45 -449.22 7 9 3 6448.31 12108.17 1279.46

West 7462 25 1971 92 2162 17 8 9 8 13065 62 7076 49 10890 22

Next steps: Generate code with pivot_full_analysis toggle_off View recommended plots New interactive sheet

# Business Context: Visualizing Pivot Table Insights

[Link] 8/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab
# Why: Make trends and patterns easily understandable for stakeholders
import [Link] as plt

# 1. Monthly Revenue Heatmap

[Link](figsize=(10, 6))
[Link](pivot_monthly_category, cmap='YlGnBu')
[Link](label='Revenue (₹)')
[Link](range(len(pivot_monthly_category.columns)), pivot_monthly_category.columns)
[Link](range(len(pivot_monthly_category.index)), pivot_monthly_category.index)
[Link]('Monthly Revenue Heatmap by Category')
[Link]()

# 2. Regional Profitability Comparison

pivot_profit_margin.drop('All Regions').plot(kind='bar', stacked=True)
[Link]('Profit Distribution by Region and Category')
[Link]('Average Profit per Transaction (₹)')
[Link]()

# 3. Sales Volume Patterns

pivot_sales_volume.drop('Total').plot(kind='line', marker='o'
[Link]('Monthly Sales Volume by Region')

[Link] 9/10
13/06/2025, 11:33 Business_Analysis.ipynb - Colab
[Link]('Units Sold')
[Link](True)
[Link]()

# Business Context: Actionable Insights from Pivot Analysis

# 1. Best-performing product/region combinations
max_profit_region = pivot_profit_margin.drop('All Regions')['All Regions'].idxmax()
max_profit_category = pivot_profit_margin.loc['All Regions'].drop('All Regions').idxmax()
print(f" Highest profit region: {max_profit_region}")
print(f" Most profitable category: {max_profit_category}")

Highest profit region: East

Most profitable category: Clothing

# 2. Seasonal opportunities
best_month_electronics = pivot_monthly_category['Electronics'].idxmax()
print(f" Peak electronics sales in {best_month_electronics} - increase marketing spend")

Peak electronics sales in February - increase marketing spend

# 3. Regional inventory adjustments

west_sales = pivot_sales_volume.loc['March']['West']
east_sales = pivot_sales_volume.loc['March']['East']
print(f" March regional disparity: West ({west_sales} units) vs East ({east_sales} units) - rebalance stock")

March regional disparity: West (40 units) vs East (39 units) - rebalance stock

# 4. High-potential underperformers
furniture_profit = pivot_full_analysis[('profit', 'Furniture')].mean()
electronics_profit = pivot_full_analysis[('profit', 'Electronics')].mean()
if furniture_profit < electronics_profit * 0.5:
print(" Furniture significantly underperforming - consider promotions or product refresh")

Views are welcome. Col Jai Govind

[Link] 10/10