Sales Performance & Growth Insights Analysis
Home // AI-Powered Data Analysis
📂 Table of Content
📋 Project Overview
Workflow
Jupyter to SQL Server Data Integration
- Connect VS Code Jupyter Notebook to SQL Server, transfer DataFrames using Pandas and SQLAlchemy.
SQL Query Development
- Write and optimize SQL queries to extract relevant insights from the database.
Integration with Power BI
- Connect Power BI to the database for seamless data visualization.
Interactive Dashboard Development
- Design and build an interactive Power BI dashboard to present key findings.
Key Insights
- Interpret the results and provide actionable insights based on the analysis.
Tools Use
- Excel
- VS Code
- Microsoft SQL Server
- Power BI
- Power Point
📢 Request
Subject: Dashboard Development: Product Analytics Overview
Dear Data Analyst,
I hope this message finds you well. I would like to request the development of a high-level Product Analytics Dashboard that provides key insights into product performance metrics. This dashboard will support strategic decision-making and enable us to track performance trends effectively.
Dashboard Requirements (1-Page):
- Revenue by Country – Identify top-performing regions along with corresponding revenue figures.
- Revenue by Date and Year – Display comparative revenue trends over time.
- Profit and Unit Sales Year-over-Year (YoY) Change – Provide a high-level summary of year-over-year growth.
- Revenue Breakdown by Discount Band – Illustrate the distribution of revenue across different discount categories.
- Detailed Table View – Present revenue and profit details by country and year for granular analysis.
Please incorporate any additional metrics or visualizations you deem necessary to enhance data-driven decision-making. Let me know if you need any further clarification.
Looking forward to your insights!
Best regards,
Tim
📊 Summary of the Dataset
🚴 Discount Data (discount_data.csv)
- Total Rows: 48
- Total Columns: 3
- Key Columns:
Month– The month associated with the discount.Discount Band– Category of discount applied.Discount– The actual discount percentage.
📦 Product Data (Product_data.csv)
- Total Rows: 6
- Total Columns: 8
- Key Columns:
Product ID– Unique identifier for each product.Product– Name of the product.Category– Product category.Cost Price– Cost of the product.Sale Price– Selling price of the product.Brand– The brand of the product.Description– Product details.Image url– Link to the product image.
📊 Product Sales (product_sales.csv)
- Total Rows: 1,046
- Total Columns: 6
- Key Columns:
Date– Date of the sale.Customer Type– Type of customer (e.g., new or returning).Country– Country where the sale occurred.Product– Product name.Discount Band– Discount category applied.Units Sold– Number of units sold.
🗂️ Data Processing & Integration
Integrating Pandas DataFrames with SQL Server
- Establishing a Database Connection:
# sqlalchemy (for Python database connections)
!pip install sqlalchemy
# pyodbc (common driver for SQL Server)
!pip install pyodbc
# Set Up the Connection String
import sqlalchemy
import urllib
from sqlalchemy import text
# Replace placeholders with my actual server, database, user, and password
driver = '{ODBC Driver 17 for SQL Server}' # ODBC driver for SQL Server
server = 'DESKTOP-LGEEAAQ'
database = 'product'
# username = ''
# password = ''
# Build the connection string
connection_string = (
f"DRIVER={driver};"
f"SERVER={server};"
f"DATABASE={database};"
# f"UID={username};"
# f"PWD={password}"
f"Trusted_Connection=yes;"
)
# Convert to a URL format for SQLAlchemy
connection_url = f"mssql+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
# Create the SQLAlchemy engine
engine = sqlalchemy.create_engine(connection_url)
# Test a query
with engine.connect() as conn:
result = conn.execute(text("SELECT @@VERSION AS version;"))
for row in result:
print(row)
- Importing and Transferring Data to SQL Server:
import pandas as pd
# Read CSV files
df = pd.read_csv(r"D:\roy\roy files\Data\data project\product\discount_data.csv", encoding='ISO-8859-1')
df1 = pd.read_csv(r"D:\roy\roy files\Data\data project\product\Product_data.csv", encoding='ISO-8859-1')
df2 = pd.read_csv(r"D:\roy\roy files\Data\data project\product\product_sales.csv", encoding='ISO-8859-1')
# Write DataFrames to SQL tables
try:
df.to_sql(name='discount', con=engine, if_exists='replace', index=False)
df1.to_sql(name='product', con=engine, if_exists='replace', index=False)
df2.to_sql(name='sales', con=engine, if_exists='replace', index=False)
print("Data successfully written to the database.")
except Exception as e:
print(f"Error writing to database: {e}")
- Run SQL Query
USE product
select * from product
select TOP 10 * from discount
select TOP 10 * from sales
| Product ID | Product | Category | Cost Price | Sale Price | Brand | Description | Image url |
|---|---|---|---|---|---|---|---|
| SR1001 | MV7 | Dynamic Microphone | $174 | $199 | Shure | A versatile USB/XLR dynamic microphone ideal for podcasting, streaming, and vocal recording. It features built-in DSP for sound customization and excellent voice isolation, inspired by the legendary SM7B microphone. | https://absentdata.com/wp-content/uploads/2024/11/m7_gif.gif |
| RR10020 | NT1-A | Condenser Microphone | $160 | $229 | Rode | A popular condenser microphone known for its low self-noise and clear sound quality, perfect for vocal and instrument recordings. It comes with a shock mount and pop filter, making it a great choice for home studios. | https://absentdata.com/wp-content/uploads/2024/11/nt-1_image-removebg-preview.png |
| FR10021 | Scarlett 2i2 | Audio Interface | $118 | $169 | Focusrite | A compact USB audio interface with two inputs, allowing users to connect microphones and instruments for high-quality audio recording. Known for its ease of use and low-latency performance, it?s a staple in beginner and professional setups alike. | https://absentdata.com/wp-content/uploads/2024/11/focusrite-scarlett-2i2-2-removebg-preview.png |
| PS10022 | AudioBox USB 96 Studio | Recording Bundle | $90 | $199 | PreSonus | A complete recording bundle that includes an audio interface, M7 condenser microphone, headphones, and software. This all-in-one package is designed for musicians, podcasters, and streamers looking for an affordable, portable recording setup. | https://absentdata.com/wp-content/uploads/2024/11/AudioBox-USB-96-Studio.png |
| HY1004 | QuadCast S | Streaming Microphone | $97 | $139 | HyperX | A USB condenser microphone featuring customizable RGB lighting, tap-to-mute functionality, and four polar patterns for versatile recording options. Ideal for gaming, streaming, and content creation. | https://absentdata.com/wp-content/uploads/2024/11/hyper_x_color_gif.gif |
| AR1200 | Arctis 7P+ | Wireless Gaming Headset | $104 | $149 | SteelSeries | A wireless gaming headset with excellent sound quality and long battery life, designed specifically for compatibility with PlayStation 5. It features a retractable microphone, comfortable ear cushions, and a durable build. | https://absentdata.com/wp-content/uploads/2024/11/Arc_Headset-removebg-preview.png |
| Month | Discount Band | Discount |
|---|---|---|
| January | none | 0 |
| January | low | 6 |
| January | medium | 10 |
| January | high | 5 |
| February | none | 0 |
| February | low | 6 |
| February | medium | 11 |
| February | high | 5 |
| March | none | 0 |
| March | low | 6 |
| Date | Customer Type | Country | Product | Discount Band | Units Sold |
|---|---|---|---|---|---|
| 1/12/2023 | Government | Germany | SR1001 | None | 15 |
| 1/12/2023 | Education | United States of America | RR10020 | None | 6 |
| 1/12/2023 | Government | Canada | FR10021 | None | 18 |
| 1/12/2023 | Government | Germany | FR10021 | None | 15 |
| 1/12/2023 | Government | Canada | HY1004 | None | 18 |
| 1/12/2023 | Education | United States of America | NULL | None | 6 |
| 1/12/2023 | Government | France | SR1001 | Low | 22 |
| 1/12/2023 | Government | France | FR10021 | Low | 22 |
| 1/12/2023 | Government | Canada | SR1001 | Low | 29 |
| 1/12/2023 | Small Business | Canada | FR10021 | Low | 19 |
📝 SQL Query Development
Data Merging Process
- Merging Three Datasets
- Doing revenue, profit and discounted revenue calculations
- Extract month and year
WITH cte AS (
SELECT
a.Product,
a.Category,
a.Brand,
a.Description,
a.[Cost Price],
a.[Sale Price],
a.[Image url],
b.Date,
b.[Customer Type],
b.[ Discount Band],
b.[Units Sold],
(CAST(REPLACE(a.[Sale Price], '$', '') AS FLOAT) * b.[Units Sold]) AS Revenue,
(CAST(REPLACE(a.[Cost Price], '$', '') AS FLOAT) * b.[Units Sold]) AS Total_Cost,
FORMAT(CONVERT(DATE, b.Date, 103), 'MMMM') AS Month,
FORMAT(CONVERT(DATE, b.Date, 103), 'yyyy') AS Year
FROM product a
JOIN sales b
ON a.[Product ID] = b.[Product]
)
SELECT TOP 10
a.*,
(1 - (b.Discount * 1.0 / 100)) * a.Revenue AS Discounted_Revenue
FROM cte a
JOIN discount b
ON TRIM(a.[ Discount Band]) = TRIM(b.[Discount Band])
AND a.Month = b.Month;
| Product | Category | Brand | Description | Cost Price | Sale Price | Image url | Date | Customer Type | Discount Band | Units Sold | Revenue | Total_Cost | Month | Year | Discounted_Revenue |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| MV7 | Dynamic Microphone | Shure | A versatile USB/XLR dynamic microphone ideal for podcasting, streaming, and vocal recording. It features built-in DSP for sound customization and excellent voice isolation, inspired by the legendary SM7B microphone. | $174 | $199 | https://absentdata.com/wp-content/uploads/2024/11/m7_gif.gif | 1/1/2023 | Government | None | 16 | 3184 | 2784 | January | 2023 | 3184 |
| MV7 | Dynamic Microphone | Shure | A versatile USB/XLR dynamic microphone ideal for podcasting, streaming, and vocal recording. It features built-in DSP for sound customization and excellent voice isolation, inspired by the legendary SM7B microphone. | $174 | $199 | https://absentdata.com/wp-content/uploads/2024/11/m7_gif.gif | 1/1/2023 | Government | None | 13 | 2587 | 2262 | January | 2023 | 2587 |
| AudioBox USB 96 Studio | Recording Bundle | PreSonus | A complete recording bundle that includes an audio interface, M7 condenser microphone, headphones, and software. This all-in-one package is designed for musicians, podcasters, and streamers looking for an affordable, portable recording setup. | $90 | $199 | https://absentdata.com/wp-content/uploads/2024/11/AudioBox-USB-96-Studio.png | 1/1/2023 | Government | None | 15 | 2985 | 1350 | January | 2023 | 2985 |
| Scarlett 2i2 | Audio Interface | Focusrite | A compact USB audio interface with two inputs, allowing users to connect microphones and instruments for high-quality audio recording. Known for its ease of use and low-latency performance, it?s a staple in beginner and professional setups alike. | $118 | $169 | https://absentdata.com/wp-content/uploads/2024/11/focusrite-scarlett-2i2-2-removebg-preview.png | 1/1/2022 | Education | None | 27 | 4563 | 3186 | January | 2022 | 4563 |
| Scarlett 2i2 | Audio Interface | Focusrite | A compact USB audio interface with two inputs, allowing users to connect microphones and instruments for high-quality audio recording. Known for its ease of use and low-latency performance, it?s a staple in beginner and professional setups alike. | $118 | $169 | https://absentdata.com/wp-content/uploads/2024/11/focusrite-scarlett-2i2-2-removebg-preview.png | 1/1/2022 | Small Business | None | 24 | 4056 | 2832 | January | 2022 | 4056 |
| QuadCast S | Streaming Microphone | HyperX | A USB condenser microphone featuring customizable RGB lighting, tap-to-mute functionality, and four polar patterns for versatile recording options. Ideal for gaming, streaming, and content creation. | $97 | $139 | https://absentdata.com/wp-content/uploads/2024/11/hyper_x_color_gif.gif | 1/1/2022 | Government | None | 20 | 2780 | 1940 | January | 2022 | 2780 |
| Scarlett 2i2 | Audio Interface | Focusrite | A compact USB audio interface with two inputs, allowing users to connect microphones and instruments for high-quality audio recording. Known for its ease of use and low-latency performance, it?s a staple in beginner and professional setups alike. | $118 | $169 | https://absentdata.com/wp-content/uploads/2024/11/focusrite-scarlett-2i2-2-removebg-preview.png | 1/1/2023 | Government | Low | 40 | 6760 | 4720 | January | 2023 | 6354.4 |
| Scarlett 2i2 | Audio Interface | Focusrite | A compact USB audio interface with two inputs, allowing users to connect microphones and instruments for high-quality audio recording. Known for its ease of use and low-latency performance, it?s a staple in beginner and professional setups alike. | $118 | $169 | https://absentdata.com/wp-content/uploads/2024/11/focusrite-scarlett-2i2-2-removebg-preview.png | 1/1/2023 | Small Business | Low | 24 | 4056 | 2832 | January | 2023 | 3812.64 |
| QuadCast S | Streaming Microphone | HyperX | A USB condenser microphone featuring customizable RGB lighting, tap-to-mute functionality, and four polar patterns for versatile recording options. Ideal for gaming, streaming, and content creation. | $97 | $139 | https://absentdata.com/wp-content/uploads/2024/11/hyper_x_color_gif.gif | 1/1/2023 | Creator | Low | 25 | 3475 | 2425 | January | 2023 | 3266.5 |
| Scarlett 2i2 | Audio Interface | Focusrite | A compact USB audio interface with two inputs, allowing users to connect microphones and instruments for high-quality audio recording. Known for its ease of use and low-latency performance, it?s a staple in beginner and professional setups alike. | $118 | $169 | https://absentdata.com/wp-content/uploads/2024/11/focusrite-scarlett-2i2-2-removebg-preview.png | 1/1/2023 | Government | Low | 43 | 7267 | 5074 | January | 2023 | 6830.98 |
🔌 Power BI Integration
Connecting SQL Server to Power BI by using the SQL Query
Quality Check
Data Type Transformation in Power Query
| Column Name | Current Data Type (Raw) | Target Data Type (Power Query) | Transformation |
|---|---|---|---|
| Product | Text (String) | Text | Product names should remain text. |
| Category | Text (String) | Text | Categories are categorical values. |
| Brand | Text (String) | Text | Brand names should remain text. |
| Description | Text (String) | Text | Product descriptions remain as text. |
| Cost Price | Text ($ included) | Decimal Number (Currency) | Convert to numeric after removing $. |
| Sale Price | Text ($ included) | Decimal Number (Currency) | Convert to numeric after removing $. |
| Image URL | Text (String) | Text | No transformation needed (URL stays as text). |
| Date | Text (MM/DD/YYYY) | Date | Convert to Date format for analysis. |
| Customer Type | Text (String) | Text | Categorical field |
| Discount Band | Text (String) | Text | Categorical field |
| Units Sold | Whole Number (Integer) | Whole Number | Discrete count of items sold. |
| Revenue | Computed Decimal (Float) | Decimal Number (Currency) | Ensures accurate financial calculations. |
| Total_Cost | Computed Decimal (Float) | Decimal Number (Currency) | Ensures accurate cost calculations. |
| Month | Computed Text (String) | Text | Month names should remain as text. |
| Year | Computed Whole Number | Whole Number | Used for Year-over-Year (YoY) analysis. |
| Discounted Revenue | Computed Decimal (Float) | Decimal Number (Currency) | Needed for discounted revenue calculations. |
🎨 Interactive Dashboard Development
Create Key Metrics (DAX)
DateTable =
ADDCOLUMNS (
CALENDAR (MIN('product'[Date]), MAX('product'[Date])),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Month Year", FORMAT([Date], "MMM YYYY"),
"Quarter", "Q" & FORMAT([Date], "Q"),
"Weekday", FORMAT([Date], "dddd"),
"Weekday Number", WEEKDAY([Date], 1),
"Month Sort", FORMAT([Date], "YYYYMM")
)
---------------------------------------------------------------------------
Profit YoY =
VAR last_year = CALCULATE([Total Profit], DATEADD(DateTable[Date], -1, YEAR))
VAR SelectedYear = SELECTEDVALUE('product'[Year])
RETURN
IF(
NOT(ISBLANK(last_year)) && last_year <> 0 && SelectedYear = 2023,
DIVIDE([Total Profit] - last_year, last_year, BLANK()),
"-"
)
---------------------------------------------------------------------------
Profit Margin % =
VAR Profit = [Total Revenue] - [Total Cost]
RETURN
IF(
NOT(ISBLANK([Total Revenue])) && [Total Revenue] <> 0,
DIVIDE(Profit, [Total Revenue]),
BLANK()
)
---------------------------------------------------------------------------
YoY Growth % =
VAR _PreviousYear = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DateTable[Date]))
RETURN
IF(
NOT(ISBLANK(_PreviousYear)) && (ISINSCOPE(DateTable[Month Name]) || ISINSCOPE(DateTable[Month Year])),
DIVIDE([Total Revenue] - _PreviousYear, _PreviousYear),
BLANK() // Hides total row while keeping year/month values
)
---------------------------------------------------------------------------
MoM Growth =
VAR _PreviousMonth = CALCULATE([Total Revenue], PREVIOUSMONTH(DateTable[Date]))
RETURN
IF(NOT ISBLANK(_PreviousMonth), ([Total Revenue] - _PreviousMonth) / _PreviousMonth, BLANK())
---------------------------------------------------------------------------
Shows YoY Growth at Year and Month level but hides total row.
Build Visualizations
🔍 Key Insights
✅ Revenue & Profit Growth:
- Total Revenue increased from $1.4M (2022) to $1.5M (2023), with a 6.7% YoY profit growth.
✅ Stable Profit Margins:
- 31.8% (2022) → 31.5% (2023), indicating consistent pricing & cost structure.
✅ Less Discount Dependency:
- Full-price sales nearly doubled (3.9% → 9.09%), reducing reliance on discounts.
✅ Regional Performance:
- Strongest growth in France & USA, while Germany saw a decline.
✅ Revenue Seasonality Shift:
- Peak month moved from July (2022) to October (2023); January remains the slowest month.