eCommerce KPI Trends

BACKGROUND

Leverage data analytics to solve data silos problem by aggregating data from disparate sources (PostgreSQL, Salesforce and ERP system) into a centralized platform (analytics dashboard solution) that will serve as a source of truth for tracking & monitoring KPI metrics.

GOAL

Execute an end-to-end data analytics project by performing ETL process, building a data model, applying statistical methods and building an interactive dashboard solution that will serve as a centralized platform (analytics dashboard solution) to efficiently track and monitor KPI metrics towards the business goals and help them derive real-time insights for informed decision making to help drive growth.

METHODOLOGY

I first utilized SQL to extract the raw data needed for the analysis and uploaded the tables to Power Bi. Using DAX function, I created more primary and secondary metrics necessary for more thorough analysis.

Using appropriate charts and graphs to represent the data points and developed an interactive dashboard solution with 5 views for these various categories:

1. Summary View
2. Products Analysis View
3. Customer Analysis View
4. Order Analysis View
5. Sales Forecast View

KEY INSIGHTS

Performance of Top Products -Judging by sales and profit, the canon image CLASS 2200 Advanced Copier raked in the most revenue. However, three of the top-performing products by sales have negative profit

Most Purchased Product -The top ten best-selling products are staples, Easy staple paper and staple envelope all above 100. The trend also shows that the Technology category generated the most sales by a slight margin. The phones had the most sales with the copiers with the most profit

Revenue by Age Group -The age group above 50 raked in the most sales and profits(almost double the others) while those below 20 performed the least. The trend is the same for age groups 20 to 50 years

Sale Geographical Distribution -The trend show the top best selling states to be California, New York, Texas, Pennsylvania, Washington, all above $100,000 in sales. The western region raked in the highest sales

Ship Trend vs Ship Mode -More orders are made through the fast shipments of below or equal to 4 days. Significantly more orders are placed through the standard class ship mode

Sales Forecast -The trend shows lowest sales in January but progressively increases each year. The highest sales are in December which also increases progressively each year. The sales trend is also similar each year

RECOMMENDATIONS

1. Investing more in advertisement in December will likely yield to higher sales
2. Adverts tailored to above-50 age group will impact positively on sales
3. Targeted campaigns in the top 5 states of California, New York, Texas, Pennsylvania, and Washington will increase sales and profits
4. As technological goods make more sales, they should always be in stock and marketed more aggressively
5. For more orders and consequently more sales, shipments should be made less than and before 4 days