Revenue and Profit Analysis: Rickaby Hardware
Conerlious Zechariah Sagandira
2022-03-28
Please check my Portfolio for more projects.
Rickaby hardware is a company which supplies the hardware peripherals to different clients such as nomad stores etc. They have a head office in Mumbai India, and they have different regional offices in different cities of India. Dumisani Sagas is the sales director of this company, and he Is managing the business from the head office. So, what is happening with this company is that sales are declining and Dumisani as a sales director is having a lot of struggle tracking where the business is failing due to the size of the company which is big. When Dumisani calls his reginal mangers asking for information on why the sales are declining, unfortunately, his reginal managers are always giving him fuzzy and unclear information which can help him understand the root cause. In addition to that, they always give him big excel files of all transactions which is very difficult for Dumisani to transform all those excel sheets into insights. However, Dumisani calls for an emergency meeting with the sales director, marketing, customer, data analysis and IT teams.
To come up with a solution, Dumisani and his teams used AIMS grid focusing on four (4) components which are:_
Purpose
- The purpose is to unlock sales insights that are not visible so that the sales team can use them for decision support and automate them to reduced time spent in data gathering.
Stakeholders & Data Warehousing
Stakeholders were chosen based on each team’s impact towards profit and revenue of the company. To map down stakeholder’s impact, i made reference to the company’s data warehousing structure.
Depicted form the
diagram, stakeholders are:
- Sales Director.
- Marketing Team.
- Customer Service Team.
- Data miners and Analysis team.
- IT team (Software Engineers).
Result (what do we aim at the end of this project?)
- An automated dashboard providing quick and latest sales insights to support data driven decision making.
Success criteria (should be able to measure success of the project.)
- Dashboards uncovering sales order insights with latest data available.
- Sales team able to take better decisions and prove 10% cost savings of total amount spent.
- Sales analysts stop data gathering manually to save 20% of their business time and reinvest in value added activity.
Tools to be used
Due to the amount of data available, Dumisani wants his analysis team use the following tools.
SQL
- MySQL was used for ETL, aggregation and Analysis. This is so because it was easier for the team because the datasets contain a huge amounts of data which will be tidious to clean it using excel
Tableau
- To share insights with stakeholders, Tableau was used.
Deliverables
Dumisani asked the data analysis team to come up with the following:
Revenue breakdown by cities.
Revenue breakdown by years and months.
Show top 5 customers by revenue and sales quantity.
Top 5 products by revenue number.
NB) He wants a visualized version of Revenue and Profit Analysis dashboard where he can click on different regions.
To perform the analysis, i used a Dataset from GitHub.This dataset has 5 tables which are:
Customers
Products
Transactions
Markets(Cities)
Date
Data Modeling.
To model the data, i used a star schema whereby transactions table will the fact table referencing the other 4 dimension tables. See the diagram below showing the relationship between all the tables.
Transactions Join with Customer table
# SELECT customer_code From Customers INNER JOIN Transactions ON Cusomers.customer_code = Transactions.customer_codeTransactions Join with Products table
# SELECT product_code From Products INNER JOIN Transactions ON Products.product_code = Transactions.product_codeTransactions Join with Markets table
# SELECT market_code From Markets INNER JOIN Transactions ON Markets.market_code = Transactions.market_codeTransactions Join with Date table
# SELECT order_date From Date INNER JOIN Transactions ON Date.order_date = Transactions.order_dateDate Modeling Results
Revenue breakdown by cities.
Revenue breakdown by years and months.
Show top 5 customers by revenue and sales quantity.
Top 5 products by revenue number.
