Vehicle Sales Analysis - Excel
Vehicle Sales Data Analysis using Excel / Power Pivot on the data provided by the SQL for Data Analytics book by Upom Malik, Matt Goldwasser, and Benjamin Johnson (Packt Publishing).
This dataset contains sales, products (automobiles & scooters), and other information. The sales and product tables were exported from PostgreSQL as CSV files and loaded into Excel. The sales data starts in 2010 and runs through May 2019. The project's goal was to review the overall sales (revenue & count) and determine if the company is on track for the remainder of 2019.
Excel Functions and formulas used
-
SUMIFS
-
COUNTIFS
-
XLOOKUP
-
Charts (Bar, Stacked Bar, Line, Combo-Bar & Line)
-
YEAR extraction from dates
-
FORECAST.LINEAR
-
Tables
CRM Sales Opportunities Analysis - Power BI
For this project, an analysis was performed on sales pipeline data for a fictional company selling computer hardware. The were 4 separate files were importing into Power BI using Power Query. Some additional columns were created, and a variety of measures were calculated. Some example measures were the following: win count, loss count, win percentage, average close value, average days to close, and total revenue. In addition, similar measures were created that would ignore slicers and filters to compare those values to those for certain Sales Managers or Sales Agents.
This data is available from the Maven Analytics Data Playground at: https://mavenanalytics.io/data-playground.
The goal of this project was to visualize the sales opportunities in a Power BI dashboard to show an overview of how well the sales have been going and for the Sales Managers to understand how their teams have been performing.
A snapshot of the data model in Power BI is shown below:
Below is one page of the Sales Pipeline dashboard. The items on the left are slicers where the user can select which Sales Manager's performance results are shown. The other slicer is to select which quarter's data should be shown.
Below are two examples of measures created for this project. "Avg Close Value" calculates the average sales value for each opportunity that was won by the company. "Win Count" counts the number of opportunities won.
Avg Close Value = CALCULATE(AVERAGE(sales_pipeline[close_value]), sales_pipeline[deal_stage] = “won”)
Win Count = COUNTAX(FILTER(sales_pipeline,sales_pipeline[deal_stage]=”Won”),[deal_stage])
Vehicle Sales Analysis - PostgreSQL
Vehicle Sales Data Analysis using PostgreSQL on the data provided by the SQL for Data Analytics book by Upom Malik, Matt Goldwasser, and Benjamin Johnson (Packt Publishing).
This dataset contained sales, products (automobiles & scooters), dealerships, and other information. The sales data starts in 2010 and runs through May 2019. This project explored the database to learn more about it and tried to answer a few questions on how vehicle sales had changed over time.
SQL functions & techniques used:
-
GROUP BY & aggregate functions (COUNT, SUM, etc.)
-
ORDER BY
-
HAVING
-
CASE WHEN
-
JOIN
-
EXTRACT
-
Subqueries & Common Table Expressions
Global Shark Attacks - Python
The Shark Research Institute (SRI) maintains a log of shark attacks worldwide, the Global Shark Attack File (GSAF). The purpose of the GSAF is to "use forensic analysis to demonstrate and emphasize the likelihood of shark/human interactions in comparison to the myriad dangers that we face in our daily lives", per the SRI/GSAF website:
https://www.sharks.org/global-shark-attack-file.
The goal of this project was to gain an understanding of the data, clean the data, and then analyze shark incidents worldwide. Some of the areas of interest in this project are the type of incident (provoked or unprovoked), fatalities, the shark species involved, and the activity of the human.
