Michael Spangler

Project

SQL

• Covid 2020-2021

(Click button to view SQL file via Dropbox.)

Data was extracted and transformed in BigQuery regarding COVID deaths and vaccinations between 2020 & 2021. The goal was to determine total cases, total deaths vs population, and see how the highest injection and death rates compared to the overall population and global numbers. Another area was determining how many vaccinations were given to the total population. Below is a sample query of the JOIN that was performed. A VIEW was also created so that any visualization could be made at a later date.

Project

SQL

• Nashville Housing

(Click button to view SQL file via Dropbox.)

Data was extracted and transformed in SQL Server. The goal of this project was to clean and transform data into a usable format for future visualization. This required standardizing the date format, populating property address data through JOIN, breaking out addresses into individual columns (Address, City, State), ALTER tables to add columns and field types, updating certain field from 'Y' and 'N' to 'Yes' and 'No', removing duplicates and deleting unused columns.

Project

PowerBI - Pizza Restaurant

For this project, shareholders of a pizza restaurant chain wanted a dashboard to be able to view KPIs of their business.A data model was created for each table with primary and foreign keys identified.

Excel worksheets with the data were then imported into SQL Server and queries were used to join tables. Views were created for export and imported into Power BI for the beginning of the dashboard build-out.

• Drobox Link to SQL Queries

In the interactive dashboard below - the dashboard is arranged in three tabs - Orders, Inventory, and Staff.All tabs can be filtered by date range. The orders tab shows total orders, total sales, total items, and the average order value, charts show the various sales metrics. The inventory tab displays the total ingredient costs, as well as a table with all ingredients, their quantities, costs, and remaining inventory. A table with the cost to make the pizzas is also displayed. The Staff tab displays the total staff costs, total hours worked, and a table displaying each of the staff's metrics.Various measures using DAX within Power BI were created to display certain metrics information based on the sales of orders and stock costs.

Project

PowerBI - Sales Insights

Shareholders of an international firm in India wanted a dashboard to visualize key metrics within their organization.

A star schema was utilized for the data model.

SQL was utilized to JOIN tables.

Within Power BI's Power Query - the Sales Transaction table was cleaned and transformed. USD currencies were converted into INR. Profit margin and cost columns were also created.

Various measures utilizing DAX were created to create the KPI metrics.

As you can see from the interactive dashboard, metrics are broken down into three tabs - Key Insights, Profit Analysis, and Performance Insights. Each tab uses a slicer for users to select by years and months.Key Insights shows the revenue and sales quantity, with charts displaying revenue and sales by market, top customers, top products, and revenue trend.Profit Analysis displays the revenue, sales quantity, and total profit margin. The charts display profit margin by market, profit margin contribution and contribution percentage by market. A table with each customer name with the aforementioned metrics is also available.Performance Insights displays revenue, sales quantity, and total profit margin. A profit target slicer was created to allow users to customize the profit target with the profit margin percentage by zone chart. Revenue trend is displayed with a line and clustered column chart.

Project

PowerBI - Data Analyst Careers

Survey data from a public forum was collected and utilized to create a dashboard showing various responses from the survey participants regarding their data analyst careers.The data was put into an Excel spreadsheet, which was cleaned in preparation for dashboard creation. Cleaning involved utilizing Excel's search and replace. A large amount of respondents for various questions were in the "Other" category with additional information. All additional information was removed to only include "Other". Yearly salary was transformed into an average salary range for better categorical data points.

Once cleaning was completed, the dashboard was created using the data. The interactive dashboard below allows viewers to see and manipulate the data. Various charts were created to display - total survey participants, average age, country of participants, average salary by job role, programming language, difficulty entering the Data Analytics field, happiness with salary, and happiness with work/life balance.

© SpanglerAnalytics. All rights reserved.