Data Modeling | Data Visualisation | Data Analysis | Machine Learning

Aspiring Data Analyst

Clive Humby

Project Highlights

# **Business Requirements**

| Position   | Request | Purpose|
| ----------- | ----------- |
| Sales Manager      | A dashboard displaying overview of Sales | To get to know top selling products and top purchasing customers |
| Sales Manager   |  A comparison between the target sales and the current sales | To get to know the growth rate |
| Customer Department      | A detailed overview of sales generated from each customer each month | to understand the customers and to know to whom we can sell more|
| Product Department   |   Product Overview and detail of each product sales for each month  and also worst selling and best selling subcategories |To understand which product gives use more and which to stock |


# **Data Extraction and Transformation**

The data was present in the SQL server and based on the Business requirements, the data was extracted from the database. The queries used to  extract and transform the data are shown below.

## **Customers**

![](/images/customers-1fe6aae9.png)

## **Products**

![](/images/products-78107adc.png)

## **Date**

![](/images/date-64ec1ff0.png)

## **Sales**

![](https://preview--diligent-saturn-69933.stackbit.dev/images/sales_final-947bd4b5.png)

## **Data Model**

After some other transformation that were done in power query, such as replacing some null values with others and some remained cleaning. The data was loaded into Power BI and data model was built. The Sales Table is the fact table and Date, Product and Customer represents the dimension Table. Sales is the fact table. Below is the final structure of data model.

![](/images/Model-9741840d.png)

***



The Final dashboard contains 3 Pages. The pages are built as per the requirements. The Sales page, Customer Analysis Page and Product Analysis page.

### **Sales**

The Sales page is focused on the overview of sales. The dashboard is built based on requirements and some additional visuals are added to assist manager to make decisions. The page can be seen below.

![](/images/Sales_Page-1fb99b55.png)

### **Customers Analysis**

The customer analysis page is focused on the customer overview and the detailed data of customer purchase for each customer each month. The page can be seen below.

![](/images/customer_page-02b15a77.png)

### **Product Analysis**

The product analysis page is focused on products overview, quantity sold details of each product each month and some additional data to assist product department. The page can be seen below.

![](/images/product_page-e4e7b67e.png)

The final interactive dashboard is deployed in Power BI Service and can be accessed from [here.](https://app.powerbi.com/view?r=eyJrIjoiNDQyMDllY2QtNzg3ZC00MzdiLWFlYjktOGZiZjlmZjc0Yjg1IiwidCI6Ijg1NGI2MzM2LWRlNjctNDdlNy05NDhmLTUyODZkM2VlY2E0ZSJ9\&pageName=ReportSection)


Final Dashboard

Sales Insight Dashboard For Sales, Customer and Product Analysis

## **Tools Used **

#### Python, Streamlit, Pandas, Plotly, Webscraping, Jupyter Notebook, Pycharm

### **Data Sources :**

*   Overall data was scraped from [FIFA World cup](https://en.wikipedia.org/wiki/FIFA_World_Cup) Wikipedia Page.

*   [Matches, Players and Goals data](https://raw.githubusercontent.com/jfjelstul/worldcup/master/data-csv/goals.csv)

*   [Qualified Team Data](https://raw.githubusercontent.com/jfjelstul/worldcup/master/data-csv/qualified_teams.csv)

### **Goal**

The goal of the Project was to analyze the data of the FIFA World Cup  data from 1930-2018. The First aim was to get the overall information of the World Cup. The Second aim was to  get the information of the world cup for each year. The final goal was to get information on each country which participates in world cup.

### **Data Prepration**

The data collection and cleaning was the first part. The code used for the data collection with the explanation is present [here](https://github.com/sudarshanAw/fifa-wc-analysis-app/blob/main/%20FIFA%20World%20Cup%20Data%20Collection%20and%20Cleaning.ipynb).

### **Data Analysis**

The analysis was done in three parts. First overall analysis was done, then year-wise analysis was done and finally country-wise analysis was done. The differents parts of analysis are explained below.

### **Overall Analysis**

The Overall Analysis was done to answer the following questions.

*   Total World Cup Events from 1930-2018

*   Total Participating countries and their names

*   How many times has a country won the World Cup?

*   How many goals has been scored each year from 1930-2018 ?

*   Which are the top 7 goal scoring countries ?

*   Who are top scoring Players ?

![](/images/overall\_1-11215281.png)

![](/images/overall\_2-8ec318c2.png)

![](/images/overall\_3-7211265f.png)

### **Year-Wise Analysis**

The purpose of year-wise-analysis was to be able to get the statistics of the selected year. The question that are answered in this analysis are :

*   The year of world cup, the coutry hosting the world cup and total attendance of visitors in world cup.

*   Total Participating countries and their names

*   Who won the world cup, was first runner up and second runner up?

*   Which were Top 5 highest scoring countries ?

*   Who were top 5 highest scoring players ?

The stats for the year 1930 are shown below which answers the questions.

![](/images/year-wise\_1.png)

![](/images/year-wise-2.png)

### **Country-Wise Analysis**

The purpose of country-wise analysis was to be able to get the statistics of selected country. The question that were answered in this analysis are:

*   In how many world cups the country has participated ?

*   The years the country has participated in world cups ?

*   Total world cup wins, second positions and third positions ?

*   How many goals the country has scored in each year of participated world cup ?

*   Top scorers from that country in world cup.

The stats for Germany are shown below which answers the above questions

![](/images/country-wise-1-94c72ac6.png)

![](/images/country-wise-2.png)

![](/images/country-wise-3.png)

## **Hosting**

The project was packaged into a web-application using Streamlit and can be found [here](https://sudarshanaw-fifa-wc-analysis-app-app-i440uw.streamlit.app/).

The github code for the project can be found [here.](https://github.com/sudarshanAw/fifa-wc-analysis-app)


Project Description

FIFA World Cup  Analysis (1930 - 2018)

**Tools Used : Sqlite, Jupyter Notebook**

****

The main goal of the project was to analyze the interesting facts about the world and the countries. 

Some of the interesting question that were asked before starting the analysis were:

*   Which is the country with maximum population and minimum population ? 

*   What is the average area of a given country ?

*   Which countries have greater area than average area ? 

*   Which are the countries with more water than land ?

*   Which are the countries with higher death rate than birth rate ?

*   Which country will add most people to the population next year ?

The information about the data, columns, the code written to extract the data and answer the above question with explanation can be found [here](https://github.com/sudarshanAw/cia-factbook-analysis/blob/master/CIA%2BFactbook.ipynb).



Aspiring Data Analyst

“Data is the new oil. It’s valuable, but if unrefined it cannot really be used. It has to be changed into gas, plastic, chemicals, etc to create a valuable entity that drives profitable activity; so must data be broken down, analyzed for it to have value.”

Recent Projects

Sales Insight Dashboard For Sales, Customer and Product Analysis

FIFA World Cup Analysis (1930 - 2018)

Factbook Analysis

Sudarshan Awasthi

Aspiring Data Analyst

“Data is the new oil. It’s valuable, but if unrefined it cannot really be used. It has to be changed into gas, plastic, chemicals, etc to create a valuable entity that drives profitable activity; so must data be broken down, analyzed for it to have value.”

Recent Projects

Sales Insight Dashboard For Sales, Customer and Product Analysis

FIFA World Cup Analysis (1930 - 2018)

Factbook Analysis