E-Commerce Sales Story

Overview

The purpose of this sales report is to show the executives of a fictional e-commerce company the overall results of their marketing and sales activities without the specifics of how sales represantives reached the company's goals.


Objective

To report on the year-to-date KPIs of the sales in different regions.

Key Questions:
  • PostgreSQL
  • Microsoft Power BI
  • Power Query Editor
  • Visual Studio Code
  • Excel
  • DAX in Power BI
  • PostgreSQL Connector in Power BI
  • Power BI measures
  • Date Table
  • Importing flat files into PostgreSQL
  • Data Modiling
  • Developing KPIs
  • Format: CSV
  • Records: 113,271
  • Information: Sales, order, product, and shipping info, buyers' billing info
  • Data Citation: Sales records
    N.B. The data is already cleaned, originally downloaded from Kaggle.
    Last update: 2023-01-01

Importing Flat file into MySQL / PosgreSQL



BI Connector for PostgreSQL DB



Data Modeling in Power BI

(Based on Star Schema: Fact and dimension tables)



Creating Date Table in Power Query Editor for time intelligence functions



DAX Programming

Example of using variables in Profit measure to show the green Up-arrow and red Down-arrow icons as a trend:

Profit Icon = VAR positive_icon = UNICHAR(9650)
	VAR negative_icon = UNICHAR(9660)
	VAR result = IF([YOY Profit]>0, positive_icon, negative_icon)
	return result

Example of using CALCULATE function with an aggregation expression and a Time intelligence function to create a new measure:

PYTD Sales = CALCULATE(Sum(ecommerce_data[sales_per_order]),DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date])))


Sales Dashboard

Based on KPIs, regions, shipping types, etc. Data is using Segment Slicer and is interactive. Please check the link out to work with this interactive sales dashboard in Power BI.

Recommendation

  • YTD profit is negative for Corporate segment. This is the same for YTD profit margin. It appears that the company needs to enhance their marketing strategies for this target to be able to find more business leads.
  • Same day shipping correspond to only 5 percent of all sales. Cheaper shipping costs or promotions on this shipping type might increase the total number of sales (e.g., if the total sales value is higher than a certain amount, we can offer free same-day shippng).
  • YTD sales is negative across the three segments (Consumer, Corporate, and Home Office). This requires deeper analysis and should be the next logical step of the business analysis to find the underlying reason.

Challenges and Limitations

Importing CSV file into PostgreSQL database encountered errors due to unknown encoding of the file. It took us a couple of tries to find the right encoding type. In addition, it is important to find the escape character used in the CSV file and use the same when importing the CSV into the database.

Deliverables