BikeStores Business Summary: 2016–2018 Sales.
I did a Data Analytics project and I used SQL, Excel, and Tableau all in this project! I found website https://www.sqlservertutorial.net/sql-server-sample-database/ where I have been learning SQL and believe me, it is an amazing resource for learning SQL for any level. The project involves the database of a fictional bike sales company dubbed ‘Bikestores’ with its operations in the USA. This has got to be the most comprehensive Data Analysis project I’ve found so far in my journey. Let’s get straight into it.
The BikeStores database was categorized into 2 schemas ‘Production’ and ‘Sales’. The Production schema was made up of 4 tables products, categories, stocks, and brands; while the Sales schema was made up of 5 tables customers, orders, order_items, staff, and stores. You can see the entity relationships illustrated below which I modelled using QuickDBD.
Business Problem: The fictional business problem in this project is that management wants a summary of all the company’s sales (total units and revenue) between 2016 and 2018, with the customers’ information, the store name and location of stores from which the sales were made, date of sales, what kind of bicycle was sold, and the sales rep who made the sales. Management also wants an interactive dashboard for this report for easy viewing and comprehension.
I have uploaded this project on my GitHub repo with the necessary files and links, but I will be explaining my steps and thought process in more detail on this article. Please walk with me.
Note that the SQL statements to create and load the tables were already written in the Microsoft SQL Server. I simply had to import them into my RDBMS. Being that I use MySQL, I needed to rewrite some of the MS SQL Server syntax to fit into MySQL flavour. Eg, see the objects creation files attached below. The first was the original ‘create’ file written in MS SQL Server, while the second is the MySQL version adjusted by me.
To query the database to successfully return the summary of the results that management has requested, I studied the components of all the 9 tables and noticed that the information needed for this query were distributed across 7 tables. The information required include the following; customer name, city and state — which can be found in the customers table; date of order — which is found in the orders table; the quantity of items ordered, and price per product are found in the order_items table; the product names of the bicycles ordered are found in the products table; the product category is found in the categories table; store name is found in the stores table; and the sales reps can be found in the staff table.
What is abundantly clear from the Entity Relationship Diagram ERD as illustrated above is that these tables have identical columns(fields) which are used to establish relationships between the said tables with identical names.
To establish these relationships between these tables in my query, I used joins to connect the tables.
Upon running the query, I exported the result of the query as a CSV file and imported into MS Excel. The dataset imported into Excel was already clean. The only form of cleaning required was to convert the date format from ‘YMD’ (which is the default SQL date format) to ‘DMY’ in Excel.
I proceeded to further summarize the data to answer the business questions posed by management using pivot tables in a separate sheet named ‘Pivot 1’. To create Pivot tables, go to the ‘Insert’ ribbon in Excel and click on ‘Pivot Table’.
The pivot tables helped me summarize the revenues by the following categories: year, store name, state, product category, customer name and staff. The seventh table (which isn’t really a pivot table) summarizes the revenue by month per year from 2016 to 2018. This sheet will form the basis of our visualization of this analysis on Excel which I did in a third sheet called ‘Dashboard’.
During the visualization, my visualization tools (Excel and Tableau) made my choice of charts easy as they narrowed them down to the most suitable chart forms based on my datatypes that constitute my tables. I simply had to select between 2 or 3 types of charts according to my preferred style of data storytelling.
I proceeded to arrange all the charts in a comprehensible order to form the dashboard.
To make the dashboard interactive, I added slicers and connected them to the relevant charts to tell the story in more granular detail. In this dashboard, I added the ‘Year’ slicer to view the business summary per year at a glance; the ‘state’ slicer to view the summary per state at a glance; the ‘store name’ slicer to view the summary of the store at a glance. These 3 slicers can be aggregated and activated at the same time if need be. To add a slicer, go to the ‘Insert’ ribbon and click on ‘slicer’, then select the fields you want to filter the charts by. To enable the charts to interact with the charts, right-click on each slicer and select ‘report connections’, then select all the charts you want to connect the slicers to.
That’s it! My first ever dashboard on my Data Analytics journey, and boy, am I proud of it! I went a step further to replicate the same dashboard on Tableau just for practice and building my visualization skills on Tableau.
Click this link to view the interactive BikeStores Dashboard on Tableau.
