Data Analysis Project 2 (SQL & Tableau)

Project Details

Problem Statement: The sales of a computer hardware store are declining. They are a peripheral manufacturer to Surge, Nomad, Excel, and Electricalsara Store. The Sales director is having difficulty tracking where exactly the business is failing.

Stakeholders:

Sales Director
Regional Managers
Marketing Team
Customer Service Team
Data Analysis and IT Team

Summary:

The sales director wants to know the following, using an automated dashboard that provides quick and up-to-date sales insights to support data-driven decision-making:

Revenue breakdown by cities
Revenue breakdown by years and months
Top 5 customers by revenue
Top 5 products by revenue

Data Source:

End-to-end Sales Insights Project Using Tableau

Importing the data into MySQL to establish the database and Tableau link

If you don't have MySQL installed on your system, you'll need to install it. Watch this YouTube video to assist with the installation: How to Install MySQL.

Step 1: Import the data into MySQL

After installation click on MySQL workbench and launch it.
Create a new connection called ”DEV_sales_insights” and leave other inputs as default then press “Ok”.
Select the new connection and check your server status to ensure no error message.
Click on the “Server” tab and select “Data Import”.
Select the “Import from self-contained file” option and click “…“ beside the file directory input.
Select the SQL Text file downloaded, “db_dump“.
Navigate to the “Import Progress ” tab and select “Start Import”
Click on the refresh icon in the “Schemas” tab and select the “sales“ file.
Click on “Tables“, right-click on “transactions” and then select “Select Rows Limit-1000”.

Step 2: Quick Insights with SQL queries

Click on the thunderbolt icon to run the queries.

A.) The total number of transactions:

SELECT count(*) 
    FROM sales.transactions;

B.) The total number of customers:

SELECT count(*) 
    FROM sales.customers;

C.) Sales records from only the city of CHENNAI:

SELECT * 
    FROM sales.transactions
        WHERE market_code = "Mark001"

D.) Sales count of transactions from only the city of CHENNAI:

SELECT count(*) 
    FROM sales.transactions
        WHERE market_code = "Mark001"

E.) All transactions done in USD:

SELECT *
    FROM sales.transactions
        WHERE currency = "USD"

F.) Sales transaction records made in 2020:

i.)

An INNER JOIN/JOIN is performed to merge the “dates” and “transactions” tables.

SELECT sales.transactions.*, sales.date.*
    FROM sales.transactions JOIN sales.date
        ON sales.transactions.order_date = sales.date.date

ii.)

Filter the table for the year 2020.

SELECT sales.transactions.*, sales.date.*
    FROM sales.transactions JOIN sales.date
        ON sales.transactions.order_date = sales.date.date
            WHERE sales.date.year = 2020

G.) The total revenue in the year 2020:

SELECT sum(sales.transactions.sales_amount)
    FROM sales.transactions JOIN sales.date
        ON sales.transactions.order_date = sales.date.date
            WHERE sales.date.year = 2020

H.) The total revenue of the city CHENNAI in the year 2020:

SELECT sum(sales.transactions.sales_amount)
    FROM sales.transactions JOIN sales.date
        ON sales.transactions.order_date = sales.date.date
            WHERE sales.date.year = 2020 and sales.transactions.market_code = "Mark001"

I.) The distinct products sold in CHENNAI:

SELECT distinct sales.transactions.product_code
    FROM sales.transactions 
            WHERE market_code = "Mark001"

Step 3: Link the SQL Database to Tableau

If you don’t have Tableau Desktop, you will need to install it on your system. Tableau public doesn’t have the “connect to MySQL database” feature.

After downloading Tableau Desktop open the app and navigate to the “To a Server” section.
Select “MySQL” and enter your details into the input.

(If you don’t have the MySQL server option under the “To a Server” section follow this link to download the driver: Driver Download.

After the download, select the “More…” option, click on “MySQL” and enter your details in the inputs)

Step 4: Create a Data Model

A data model connects different data tables and establishes a relationship between those tables, it is also known as a Star Schema. In this case the transactionsthe table would be the fact table (main) and the other tables would be the dimensions tables.

Drag and drop the “Transactions” table to the center and select “Update Automatically” so it automatically pulls data.
Drag and drop the “Customers” table to the center and a relationship is established.
Drag and drop the “Date” table to the center and another relationship is established.
Select the “Order Date” under the Transactions tab and select “Date” under the Date tab to create a relationship between the tables.
Drag and drop the “Markets” table to the center to establish another relationship.
Select the “Market Code” under the Transactions tab and select “Markets Code” under the Markets tab to create a relationship between the tables.
Drag and drop the “Products” table to the center to establish another relationship.

This is what you should have after following the steps above:

Step 5: Data Cleaning in Tableau

A.) Remove the sale amounts that are less than 1 Rupee

Navigate to the “Data” tab and click “Edit data source Filters…”.
In the pop-up, click “Add” and select “Sales Amount”.
Select “At least” and enter “1” then click on “OK”.

B.) Remove the market areas (cities) that are not in India:

Navigate to the “Data” tab again and click “Edit data source Filters…”.
In the pop-up, click “Add” and select “Markets Code”.
Click on “All” and deselect select Mark097 and Mark099 (Paris & New York).

C. ) Remove the transactions done in USD by converting them to INR

Click on the dropdown arrow beside the “Currency” column and select “Create Calculated Field…”
Enter “Normalized Amount” as the new column name.

Enter the following code formula for the conversion:

IF [Currency] == "USD" THEN [Sales Amount]*74 ELSE [Sales Amount] END

Step 6: Dashboard’s Component Creation

Row inputs are represented on the Y-axis and Column inputs are represented on the X-axis.

A.) First Component: Total Revenue

Select “Sheet 1” and rename it to “Revenue”.
Drag “Normalized Amount” to “Text” under the Marks card.
Right-click on the “Revenue” title in the worksheet’s center and hide it.
Click “Text” in the Marks card then select “...” beside the “Text:” input.
Edit the text as preferred, type “Total Revenue” at the top, then align to center.

Select the dropdown arrow beside the dragged table in the worksheet and click “Format”.
Under “Default” click on the “Numbers” input and select “Currency (Custom)”.
Change “Display Units” to “Millions (M)” and remove the “$” sign prefix.

B.) Second Component: Quantity

Select “New worksheet” and rename it to “Quantity”.
Drag “Sales Qty” to “Text” under the Marks card.
Right-click on the Quantity title and select “Hide Title” to hide it.
Click “Text” in the Marks card then select “...” beside the “Text:” input.
Edit the text as preferred, type “Sales Quantity” at the top, press “Ok” then align to the center.

C.) Third Component: Revenue by Markets

Ctrl-shift-B increases the chart size; Ctrl-B reduces the chart size.

Select “New worksheet” and rename it to “Revenue by Markets”.
Drag “Normalized Amount” to the “Rows” input & drag “Markets Name” to the “Columns” Input.
To transpose the chart, click on the “Horizontal bars” icon.
Right-click on the “Revenue by Markets” title in the worksheet’s center and hide it, then edit the chart to your preferred colors and size.
To add labels to each bar, drag “Normalized Amount” to “Label” under the Marks card.
Change the displayed amount labels for each graph to Millions(M).
To put each bar label within the bar, click “Label” under the Marks card and then “Alignment” to center. Edit the text to your preferred style.

D.) Fourth Component: Sales Quantity by Markets

Select “New worksheet” and rename it to “Sales Quantity by Markets”.
Drag “Sales Qty” to the “Rows” input & drag “Markets Name” to the “Columns” input.
To transpose the chart click on the “Horizontal bars” icon.
Right-click on the “Sales Quantity by Markets“ title in the worksheet’s center and hide it, then edit the chart to your preferred color and size.
Add labels to each bar and change the displayed amount labels for each graph to Thousands(K).
Put each bar label within the bar and edit the text to your preferred style.
Right-click on the “Markets Name“, y-axis title and hide it.

E.) Fifth Component: Top 5 Customers

Select “New worksheet” and rename it to ‘Top Customers’.
Drag “Normalized Amount” to the “Rows” input & drag “Customer Name” to the “Columns” Input.
To transpose the chart click on the “Horizontal bars” icon.
Right-click on the “Customer Name”, y-axis title and hide it.

To filter for just the top 5 customers, click on the drop-down icon beside “Customer Name” in the “Columns” input and select “Filter…“.
In the pop-up, navigate to the “Top” tab and select the “By field:” check box.
Type “5” into the second input, press “OK“, then edit the chart to your preferred color and size.
Add labels to each bar and change the displayed amount labels for each graph to Millions(M).
Put each bar label within the bar and edit the text to your preferred style.

F.) Sixth Component: Top 5 Products

Select “New worksheet” and rename it to ‘Top 5 Products’.
Drag “Normalized Amount” to the “Columns” input & drag “Product Code” to the “Rows” Input.
Filter for the top 5 products and edit the chart to your preferred color and size.
Add labels to each bar and change the displayed amount labels for each graph to Millions (M).
Put each bar label within the bar and edit the text to your preferred style.
Right-click on the “Product code” y-axis title and hide it.

G.) Seventh Component: Revenue by Year

Select “New worksheet” and rename it to ‘Revenue by Year’.
Drag “Normalized Amount” to the “Rows” input & drag “Cy Date” to the “Columns” Input. “Cy Date“shows the monthly revenue.
Expand the x-axis with the plus icon to see the quarterly revenue.
Click on the plus icon twice and remove “Quarters ” from the “Column” input.

Edit the chart to your preferred color, abbreviate the x-axis labels, and change the dimension to “up”.
Add labels to each point and change the displayed amount labels for each point to Millions (M).
Drag “Normalized Amount” into “Color” in the Marks card, to edit the line graph color.
Edit your preferred color choice on the legend for a gradient effect.
Right-click on the “Cy Date” y-axis title and hide it.

H.) Eighth Component: Year

Select “New worksheet” and rename it to ‘Year’.
Drag “Cy Date” to the “Columns” input and not “Year” to get a breakdown.

A field for row entries needs to be created and made empty because the system identifies the information in rows and columns.

Click on the dropdown icon in the “Data” tab and select “Create Calculated Field…”.

In the pop-up, name it “Blank” and type “ “ and click “OK”.
Drag “Blank” to “Text” in the Marks card.
Hide the “Cy Date” title, edit, and style it as preferred.

I.) Nineth Component: Month

Select “New worksheet” and rename it to ‘Month’.
Drag “Cy Date” to the “Columns” input and drag “Blank” to “Text” in the Marks card.
Click on the drop-down icon beside the “Cy Date” table in the “Columns” input and select “Month”.
Format the dates/month into abbreviations and style as preferred.
Hide the “Cy Date” title.

Step 7: Tableau Dashboard Build

Select “New Dashboard” and increase the canvas size to, Width: 1500px X Height: 800px.
Click on the “Floating” tab.
Drag each sheet (Year, Month, Revenue by Markets, Total Revenue, Sales Quantity by Markets, Quantity, Top 5 Customers & Top 5 Products) to the canvas, edit, and style as preferred.

To make the dashboard dynamic and interactive:

Click on the “Year” tile on the canvas and select “Use as filter” or the filter icon.
Click on the filter icon on each dashboard tile to display specific visualizations when individual items are selected.

After following all the steps this is what your dashboard should look like:

Step 8: Publish Dashboard on Tableau Public

If you haven't already, sign into Tableau Public directly from the Tableau Desktop app. This will ensure a seamless connection and simplify the publishing process.

Go to the “Server” tab and select “Publish Workbook…”.
Enter your preferred workbook name and click on “Save”.

All done!!

Recommendations

My recommendations can be found in this Data presentation - Sales Insights.

My Portfolio

datascienceportfol.io/estheribom

I hope this documentation was helpful and you’ve learned one or more SQL & Tableau concepts.

Other references are highlighted below. Feel free to add your twist along the way and share more knowledge in the comments!

REFERENCES:

Code Basics - Sales Insights Project

Data Analysis Project 2 (SQL & Tableau) - Company Sales Insights

SQL Database, Tableau Data Cleaning, and Tableau Dashboard Publishing (Tableau Public)

Table of contents