DEV Community: Muhammad abdulhhadi kehinde

Becoming a Data Analyst: Unveiling the Path to Success

Muhammad abdulhhadi kehinde — Mon, 14 Aug 2023 02:49:56 +0000

INTRODUCTION:

Embarking on a journey to become a data analyst can be an exciting and rewarding endeavor. In this article, we'll guide beginners through essential steps, from understanding the data ecosystem to honing programming skills. Whether you're tech-savvy or new to the world of data, this guide will equip you with the knowledge and tools needed to start your data analyst journey.

Course/Step 1: Understanding the Data Ecosystem

Before diving into the world of data analysis, it's crucial to comprehend the role and responsibilities of a data analyst. Data analysts are professionals who gather, process, and analyze data to discover patterns, trends, and insights that can aid in making informed business decisions. They work with various tools and technologies to transform raw data into actionable information, which empowers organizations to optimize processes, identify market opportunities, and address challenges effectively.
By the end of this course, you should have gained an understanding of:

Definition of data related terms
Data sources and data format
Tools used in data ecosystem
The most important skillset to have
Various domains and how they used data
Differentiating between structured and unstructured data
Grasping the concept of databases and data storage
Understanding data collection methods and techniques
Recognizing the importance of data quality and cleaning

Recommended Study Materials:

Key Takeaways:
By the end of this course, you will have a Broad understanding of where you are going to and a solid foundation in data concepts, enabling you to navigate the data landscape with confidence.

Course/Step 2: Spreadsheet Mastery

Spreadsheet skills are a cornerstone of data analysis. Examples of commonly used spreadsheets include Microsoft Excel, Google Sheets, and LibreOffice Calc. Spreadsheets allow data analysts to organize, manipulate, and visualize data efficiently.

Throughout this course, you will learn to:

Utilize functions and formulas for data manipulation
Create charts and graphs for data visualization
Perform basic data analysis tasks using sorting, filtering, and pivot tables

Recommended Materials for Learning Spreadsheets:

Can I Get a Job with Only Excel?
While Excel proficiency is valuable, it's recommended to diversify your skill set to increase job prospects. Excel is a powerful tool but is often used in conjunction with other technologies.

Key Takeaways:
Upon completing this course, you'll have a strong command of spreadsheet tools, empowering you to efficiently manage and analyze data.

Course/Step 3: SQL and Databases

SQL (Structured Query Language) is a vital skill for data analysts. It allows you to retrieve, manipulate, and analyze data stored in databases.

What to Learn:

Basics: Understand SQL syntax, SELECT statements, and data filtering.
Intermediate: Grasp JOIN operations, subqueries, and database normalization.
Advanced: Learn about window functions, stored procedures, and performance optimization.

How to Progress:
Once you have mastered the basics, you can join online forums or registered on database q & a site where you can begin attempt real business question and see different business scenarios and also engage in real-world projects to apply your SQL skills.

Recommended Materials:

Key Takeaways:
Mastering SQL equips you with the ability to extract valuable insights from databases and play a critical role in data-driven decision-making.

Course/Step 4: Statistics

Statistics underpin data analysis, helping you draw meaningful conclusions from data. While deep statistical expertise is beneficial, beginners should focus on foundational concepts such as probability, distributions, and hypothesis testing.

Recommended Materials:

Course/Step 5: Business Intelligence and Visualizations

Business intelligence involves converting raw data into actionable insights. Tools like Power BI enable you to create interactive visualizations and dashboards that communicate findings effectively.

What to Learn:

Understand data visualization principles i.e. data cleaning, measures and calculated column, data preparation merging data set and building models.
Learn Power BI functionalities, M language, and DAX formulas
Develop skills to design compelling dashboards and reports

Recommended Materials:

Key Takeaways:
By mastering business intelligence tools, you'll enhance your ability to present data-driven insights in a visually engaging manner.

Course/Step 6: Computational Thinking and Programming

Computational thinking involves problem-solving and logical reasoning. Programming languages like Python and R are invaluable tools for data analysts.

Note: This course will bridge the gap between non-technical data analyst, and others who want to go into programming. it will lay a solid foundation for you and open you to begin to think problem solving computationally.

Main Courses:

Python: General-purpose language with extensive data analysis libraries (NumPy, pandas).
R: Designed for statistical analysis and data visualization.

What to Learn:

Basics of programming, including variables, loops, and functions
Data manipulation using libraries like pandas (Python) or dplyr (R)

Recommended Materials:

Personal Portfolio Projects:

Practical experience is invaluable for a data analyst. As you learn, work on personal projects or contribute to open-source initiatives. This will not only help you apply your skills but also create a portfolio that showcases your capabilities to potential employers.

Recommendation:

2 Project on Excel/SpreadSheet.
2 Project on SQL.
2 Project on Power-BI/Tableau.
2 Project on python.

Course/Step 7: Applying Data Analysis Skills

Imagine a superstore owner seeking insights. A data analyst would utilize Excel for basic analysis, SQL to extract data from the store's database, and Power BI to create interactive visualizations, ultimately providing a comprehensive analysis and actionable recommendations.

Major Job Titles:

Data Analyst
Business Analyst
Data Scientist
Market Research Analyst
Financial Analyst
Healthcare Analyst
Operations Analyst
Marketing Analyst

Major Certifications:

Non-Technical Skills to Have:

Critical thinking
Communication
Problem-solving
Attention to detail
Domain knowledge
Collaboration
Time management
Business acumen

Course/Step 8: The Final Part of Your Data Analyst Journey

Summary:
Your journey to becoming a data analyst involves mastering data concepts, spreadsheet tools, SQL, statistics, visualization, and programming. With these skills, you can tackle real-world scenarios and contribute meaningfully to data-driven decision-making.

What You Should Be Able to Do:

The ability to solve data related problem.
Extract, transform and load data.
Clean and preprocess data
Analyze data trends and patterns

Note:

When to seek for help:

When given a work or data set to work and you are stuck between the line or you don't know what to do/ how to start? it's always great to seek for help and ask questions.

Apart from building portfolio projects, you start building your career as a data analyst by connecting or networking with other like-minded online(forums or social media).

The journey to becoming a Data analyst is an interesting career path with plenty of rewards but there are challenges along the way. You must become a friend to constant study!

END!

Data-Driven Sales Strategies: The Impact of RFM Analysis and KPI Reports Unveiled

Muhammad abdulhhadi kehinde — Fri, 04 Aug 2023 10:10:15 +0000

Let's take a look at RFM analysis and KPI report of Sales data with SQl and tableau

In this article;

What is Sales Analysis?
What are KPI's?
RFM analysis, and What it is?
Analyze a Sales data of an imaginary store.
RFM analysis on a sales data.
Make Visuals/Dashboard & KPI Report from the analysis above.
Finally, Elaborate about why Sales analysis is import for business's and how it can benefit them.

Target: Business analyst, Data analyst, Business managers.

Note: The code in this article can be found in this GitHub repo: Sales Analysis

In the fast-paced and competitive landscape of business, data-driven decision-making has become a cornerstone for achieving success. Among the plethora of analytical techniques, sales analysis stands as a crucial pillar that empowers organizations to gain valuable insights into their sales performance and customer behavior. Within the realm of sales analysis, two powerful tools emerge as beacons of understanding – RFM analysis and KPI reports.

At its core, sales analysis involves the systematic examination of sales data to extract meaningful information about a company's sales performance, customer interactions, and market trends. Armed with these invaluable insights, businesses can make well-informed decisions, optimize strategies, and strengthen their competitive edge in the market.

The analytics process involves the systematic review of sales-related information, such as revenue, units sold, customer demographics, product performance, and other relevant metrics.

The main objectives of sales analysis are to:

Evaluate Performance.
Identify Trends and Pattern.
Understand Customer Behavior.
Optimize Inventory Management.
Assess Marketing Effectiveness.
Forecast Future Sales.

Sales analysis is an essential aspect of business intelligence, as it empowers companies to make informed decisions, spot opportunities for growth, and identify areas for improvement in their sales processes. It plays a vital role in driving sales strategies, increasing revenue, and overall business success.

RFM Analysis: One of the fundamental methodologies within sales analysis is the RFM analysis, an acronym for Recency, Frequency, and Monetary Value. RFM analysis is a customer segmentation technique that provides a comprehensive understanding of customer behavior based on their transaction history.

KPI Reports: Key Performance Indicators (KPIs) play a vital role in comprehending the overall effectiveness and success of a company's sales operations. KPIs are quantifiable metrics that serve as benchmarks, providing businesses with specific targets to monitor and analyze.

In this article, we will delve into the intricacies of RFM analysis and KPI reports, exploring how they revolutionize sales data interpretation, foster growth, and drive success in the ever-evolving world of business.

PREREQUISITE: Any SQL Tool and Tableau

LOADING THE DATA SET

At first we need to load the dataset we are going to work with. There are several ways to load/import data set into an SQl database, for this guide i am going to use python to import the data set into PostgreSQL.

Note: The data set use in this notebook is sourced from kaggle >

To import data set into SQL using python;

The to_sql() function in Python is used to write records from a Pandas DataFrame to a SQL database. It is a very powerful function that can be used to store data in a variety of database formats.

The syntax for the to_sql() function is as follows:

import pandas as pd
sales_df = pd.read_csv('Superstore data.csv', encoding='ISO 8859-1')

from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:mlyt09@localhost:5432/projectsql')

Here: postgresql:// > the sql_tool, postgres: > the user, ~~mlyt09~~ > the password, @localhost: > the host, 5432 > the port, /projectsql the database.

player_df.to_sql(name='sales', con=engine,index=False if_exists='replace')

Check to confirm the data is in PSQL...

projectsql=# \dt sales

List of relations

Schema	Name	Type	Owner
public	sales	table	postgres

projectsql=# \d sales

Table "public.sales"

Column	Type
row_id	bigint
order_id	text
order_date	date
ship_date	text
ship_mode	text
customer_id	text
customer_name	text
segment	text
country	text
city	text
state	text
postal_code	bigint
region	text
product_id	text
category	text
sub_category	text
product_name	text
sales	double precision
quantity	bigint
discount	double precision
profit	double precision
year	integer
month	integer

Querying the data to answer the questions.

(Qr1) Check to confirm the total number of records.

SELECT COUNT(*) FROM sales;

count
9994

(Qr2) Extracting Year and Month from the 'order_date' column.

ALTER TABLE sales ALTER COLUMN order_date TYPE DATE USING order_date::DATE

ALTER TABLE

SELECT EXTRACT(YEAR FROM order_date) FROM sales

extract
2016
2015
2015
2014

+9990 others

SELECT EXTRACT(MONTH FROM order_date) FROM sales

extract
12
11
11
11

+9990 others

ALTER TABLE sales ADD year INT

ALTER TABLE

ALTER TABLE sales ADD month INT

ALTER TABLE

UPDATE sales SET year = EXTRACT(YEAR FROM order_date)

UPDATE 9994

UPDATE sales SET month = EXTRACT(MONTH FROM order_date)

UPDATE 9994

(Qr3) Checking the Unique values for Some of the data_variables.

SELECT DISTINCT year FROM sales

year
2014
2015
2016
2017

SELECT DISTINCT ship_mode FROM sales

ship_mode
First Class
Second Class
Standard Class
Same day

SELECT DISTINCT segment FROM sales

segment
Consumer
Home office
corporate

SELECT DISTINCT region FROM sales

region
South
West
East
Central

SELECT DISTINCT category FROM sales

Category
Furniture
Office Supplies
Technology

(Qr4) Product Category with the most Sales through 2014-2017.

SELECT sub_category, ROUND(SUM(sales)) AS sales
    FROM sales
    GROUP BY sub_category
    ORDER BY sales DESC

sub_category	sales
Phones	330007
Chairs	328449
Storage	223844
Tables	206966
Binders	203413
Machines	189239
Accessories	167380
Copiers	149528
Bookcases	114880
Appliances	107532
Furnishings	91705
Paper	78479
Supplies	46674
Art	27119
Envelopes	16476
Labels	12486
Fasteners	3024

(Qr5) Product Category with the most profit through 2014-2017.

SELECT sub_category, ROUND(SUM(profit)) AS profit
    FROM sales
    GROUP BY sub_category
    ORDER BY profit DESC;

sub_category	profit
Copiers	55618
Phones	44516
Accessories	41937
Paper	34054
Binders	30222
Chairs	26590
Storage	21279
Appliances	18138
Furnishings	13059
Envelopes	6964
Art	6528
Labels	5546
Machines	3385
Fasteners	950
Supplies	-1189
Bookcases	-3473
Tables	-17725

(17 rows)

(Qr6) Year with the most sales through 2014-2017.

SELECT year, ROUND(SUM(sales)) AS sales
    FROM sales
    GROUP BY year
    ORDER BY sales DESC;

year	sales
2017	733,215
2016	609,206
2014	484,247
2015	470,533

(4 rows)

(Qr7) Trends in Sales and Profit by consumer Segment....

In sales analysis, understanding trends in sales and profit by consumer segment is critical for businesses to make informed decisions and optimize their marketing strategies. Analyzing sales and profit data by consumer segments helps identify patterns, preferences, and behaviors among different customer groups, leading to more targeted and effective sales initiatives.

Sales analysis provides invaluable insights into trends in sales and profit by consumer segment. By understanding consumer behavior, seasonal patterns, and profitability, businesses can refine their sales strategies, optimize marketing efforts, and cater to the specific needs of different customer groups, ultimately driving growth and success.

SELECT segment, COUNT(order_id) AS total_orders,
       ROUND(SUM(SALES)) AS total_sales, ROUND(SUM(profit)) AS total_profit
FROM sales GROUP BY segment ORDER BY total_profit DESC;

segment	total_orders	total_sales	total_profit
Consumer	5191	1,161,401	134,119
Corporate	3020	706,146	91,979
Home Office	1783	429,653	60,299

(3 rows)

(Qr8) Trends in Sales and Profit by region....

Analyzing trends in sales and profit by region is essential for sales analysis in businesses operating in multiple locations. By understanding regional variations in sales and profitability, companies can make data-driven decisions, tailor their marketing efforts, optimize distribution strategies, and identify growth opportunities in specific geographic areas.

SELECT region, COUNT(order_id) AS total_orders,
       ROUND(SUM(SALES)) AS total_sales, ROUND(SUM(profit)) AS total_profit
FROM sales GROUP BY region ORDER BY total_profit DESC;

region	total_orders	total_sales	total_profit
West	3203	725,458	108,418
East	2848	678,781	91,523
South	1620	391,722	46,749
Central	2323	501,240	39,706

(4 rows)

(Qr9) What was the best month for sales in a specific year ?

What's the sales for the month?

In sales analysis, determining the best month in a specific year for sales involves identifying the month with the highest sales revenue or volume. It provides valuable insights into peak periods of customer demand and sales performance. This analysis helps businesses optimize their resources, plan promotions, and capitalize on favorable market conditions.

In addition to finding the best month, it's also essential to assess the sales performance in that month compared to the previous year or previous months of the same year. This year-over-year or month-over-month analysis can provide a deeper understanding of growth patterns and identify areas for improvement.

-- 2014
SELECT month,ROUND(SUM(sales)) AS revenue_for_2014,
       COUNT(order_id) AS total_orders, SUM(quantity) AS total_quantity_order
    FROM sales
    WHERE year = 2014
    GROUP BY month
    ORDER BY revenue_for_2014 DESC;

month	revenue_for_2014	total_orders	total_quantity_order
9	81777	268	1000
11	78629	318	1219
12	69546	278	1079
3	55691	157	585
6	34595	135	521
7	33946	143	550
10	31453	159	573
4	28295	135	536
8	27909	153	609
5	23648	122	466
1	14237	79	284
2	4520	46	159

(12 rows)

-- 2015
SELECT month,ROUND(SUM(sales)) AS sales_for_2015,
       COUNT(order_id) AS total_orders, SUM(quantity) AS total_quantity_order
    FROM sales
    WHERE year = 2015
    GROUP BY month
    ORDER BY sales_for_2015 DESC;

month	sales_for_2015	total_orders	total_quantity_order
11	75973	324	1310
12	74920	316	1203
9	64596	293	1086
3	38726	138	515
8	36898	159	598
4	34195	160	543
10	31405	166	631
5	30132	146	575
7	28765	140	557
6	24797	138	486
1	18174	58	236
2	11951	64	239

(12 rows)

-- 2016
SELECT month,ROUND(SUM(sales)) AS sales_for_2016,
       COUNT(order_id) AS total_orders, SUM(quantity) AS total_quantity_order
    FROM sales
    WHERE year = 2016
    GROUP BY month
    ORDER BY sales_for_2016 DESC

month	sales_for_2015	total_orders	total_quantity_order
11	75973	324	1310
12	74920	316	1203
9	64596	293	1086
3	38726	138	515
8	36898	159	598
4	34195	160	543
10	31405	166	631
5	30132	146	575
7	28765	140	557
6	24797	138	486
1	18174	58	236
2	11951	64	239

(12 rows)

-- 2017
SELECT month,ROUND(SUM(sales)) AS sales_for_2017,
       COUNT(order_id) AS total_orders, SUM(quantity) AS total_quantity_order
    FROM sales
    WHERE year = 2017
    GROUP BY month
    ORDER BY sales_for_2017 DESC;

month	sales_for_2017	total_orders	total_quantity_order
11	118448	459	1840
9	87867	459	1660
12	83829	462	1723
10	77777	298	1133
8	63121	218	884
3	58872	238	885
6	52982	245	931
7	45264	226	840
5	44261	242	887
1	43971	155	597
4	36522	203	733
2	20301	107	363

(12 rows)

In conclusion, sales analysis helps businesses pinpoint the best month for sales in a specific year, enabling them to make data-driven decisions, replicate successful strategies, and leverage market opportunities effectively. Understanding the drivers behind exceptional sales performance during that month empowers businesses to optimize their sales efforts and achieve continued success.

(Qr10) Dive in through the best month to check for the best Product Category...

By conducting a deep dive into the top-selling product category during the best month of a particular year, businesses can gain valuable insights that inform future sales strategies. Understanding customer preferences, market dynamics, and the success of marketing efforts can help replicate successful tactics, optimize product offerings, and drive sustainable growth.

Top 5 best selling product category in the best month of each year.

-- 2014
SELECT month, sub_category, ROUND(SUM(sales)) AS sales_for_2014,
       ROUND(SUM(profit)) AS profit_for_2014, SUM(quantity) total_quantity_orders
       FROM sales
       WHERE year = 2014 and month = 9
       GROUP BY month, sub_category
       ORDER BY sales_for_2014 DESC;

month	sub_category	sales_for_2014	profit_for_2014	total_quantity_orders
9	Machines	22420	-2626	45
9	Chairs	13849	1434	69
9	Binders	12744	5130	123
9	Storage	8810	553	92
9	Tables	4612	-176	34

-- 2015
SELECT month, sub_category, ROUND(SUM(sales)) AS sales_for_2015,
       ROUND(SUM(profit)) AS profit_for_2015,SUM(quantity) total_quantity_orders
       FROM sales
       WHERE year = 2015 and month = 11
       GROUP BY month, sub_category
       ORDER BY sales_for_2015 DESC;

month	sub_category	sales_for_2015	profit_for_2015	total_quantity_orders
11	Chairs	12743	1748	90
11	Bookcases	8377	747	48
11	Phones	8176	1111	92
11	Machines	7850	3454	16
11	Storage	6716	783	112

-- 2016
SELECT month, sub_category, ROUND(SUM(sales)) AS sales_for_2016,
       ROUND(SUM(profit)) AS profit_for_2016,SUM(quantity) total_quantity_orders
       FROM sales
       WHERE year = 2016 and month = 11
       GROUP BY month, sub_category
       ORDER BY sales_for_2016 DESC;

month	sub_category	sales_for_2016	profit_for_2016	total_quantity_orders
11	Chairs	13173	369	99
11	Phones	9103	1574	93
11	Tables	8778	-164	52
11	Machines	8757	-5221	33
11	Accessories	8081	1972	139

-- 2017
SELECT month, sub_category, ROUND(SUM(sales)) AS sales_for_2017,
  ROUND(SUM(profit)) AS profit_for_2017,SUM(quantity) total_quantity_orders
  FROM sales
  WHERE year = 2017 and month = 11
  GROUP BY month, sub_category
  ORDER BY sales_for_2017 DESC;

month	sub_category	sales_for_2017	profit_for_2017	total_quantity_orders
11	Phones	17407	2563	175
11	Chairs	14561	954	87
11	Tables	13659	-1176	81
11	Copiers	12360	5427	8
11	Storage	11911	1384	174

(Qr11) What's the Total Sales, Profit and Quantity Sold.

SELECT ROUND(SUM(sales)) AS total_sales,
       ROUND(SUM(profit)) AS total_profit,
       ROUND(AVG(sales)) AS average_sales,
       Sum(quantity) AS total_quantity_sold
FROM sales;

total_sales	total_profit	average_sales	total_quantity_sold
2,297,201	286,397	230	37,873

(Qr12) RFM Analysis.

RFM analysis is a valuable marketing technique used to analyze customer behavior and segment customers based on their purchasing patterns. The acronym "RFM" stands for Recency, Frequency, and Monetary Value, which are the three key metrics used in this analysis.

Recency (R): This metric measures how recently a customer made a purchase. It indicates the time elapsed since the last transaction, and customers who made recent purchases are considered more engaged and potentially more valuable.
Frequency (F): This metric refers to the number of times a customer made a purchase within a specific timeframe. Customers who make frequent purchases are usually loyal and valuable to the business.
Monetary Value (M): This metric represents the total monetary value of a customer's purchases within a particular period. Customers with higher monetary spending are typically more valuable to the business.

By combining these three metrics, RFM analysis categorizes customers into various segments, such as high-value, medium-value, and low-value customers. This segmentation allows businesses to tailor their marketing strategies, personalized offers, and customer retention efforts to target each customer segment more effectively.

RFM analysis is commonly used in e-commerce, retail, and various other industries to identify their most valuable customers, understand customer behavior, and improve overall marketing efforts and sales performance.

Checking for the recent order_date for each customer compare to recent_date.

-- last order
SELECT
 customer_name,
 ROUND(SUM(sales)) AS order$,
 ROUND(AVG(sales)) AS avgorder$,
 COUNT(order_id)AS  orders,
 SUM(quantity) AS quantity_order,
 MAX(order_date) AS last_order,
 (SELECT MAX(order_date) FROM sales) AS recent_date
 FROM sales
 GROUP BY customer_name
 ORDER BY quantity_order DESC;

customer_name	order$	avgorder$	orders	quantity_order	last_order	recent_date
Jonathan Doherty	7611	238	32	150	2016-12-01	2017-12-30
William Brown	6160	166	37	146	2017-12-10	2017-12-30
John Lee	9800	288	34	143	2017-12-09	2017-12-30
Paul Prost	7253	213	34	138	2017-09-24	2017-12-30
Steven Cartwright	5226	201	26	133	2017-06-26	2017-12-30
Emily Phan	5478	177	31	124	2017-12-18	2017-12-30
Chloris Kastensmidt	3155	99	32	122	2017-11-21	2017-12-30
Cassandra Brandow	6076	234	26	122	2017-11-13	2017-12-30

.....

(Qr13) How many days has it been a customer ordered?

-- last order day
SELECT
 customer_name,
 ROUND(SUM(sales)) AS order$,
 ROUND(AVG(sales)) AS avgorder$,
 COUNT(order_id)AS  orders,
 SUM(quantity) AS quantity_order,
 MAX(order_date) AS last_order,
 (SELECT MAX(order_date) FROM sales) AS recent_date,
 (SELECT MAX(order_date) FROM sales) - MAX(order_date) last_order_day
 FROM sales
 GROUP BY customer_name
 ORDER BY quantity_order DESC;

customer_name	order$	avgorder$	orders	quantity_order	last_order	recent_date	last_order_day
Jonathan Doherty	7611	238	32	150	2016-12-01	2017-12-30	394
William Brown	6160	166	37	146	2017-12-10	2017-12-30	20
John Lee	9800	288	34	143	2017-12-09	2017-12-30	21
Paul Prost	7253	213	34	138	2017-09-24	2017-12-30	97
Steven Cartwright	5226	201	26	133	2017-06-26	2017-12-30	187
Emily Phan	5478	177	31	124	2017-12-18	2017-12-30	12
Chloris Kastensmidt	3155	99	32	122	2017-11-21	2017-12-30	39
Cassandra Brandow	6076	234	26	122	2017-11-13	2017-12-30	47
Edward Hooks	10311	322	32	120	2017-08-17	2017-12-30	135
Matt Abelman	4299	126	34	117	2017-10-27	2017-12-30	64

......

(Qr14) Ranking the Recency, Frequency and Monetary value.

In sales analysis using RFM (Recency, Frequency, Monetary Value) segmentation, customers are ranked from 4 to 1 based on their RFM scores. RFM analysis categorizes customers into different segments to better understand their behavior and value. The RFM scores for each customer are calculated based on three metrics: Recency, Frequency, and Monetary Value. Here's how the ranking works:

RFM Score 4: This represents the highest score and indicates the most valuable customers. Customers with an RFM score of 4 in all three categories (Recency, Frequency, and Monetary Value) are those who have made recent purchases, frequent transactions, and high-value spending. These customers are considered top-tier and represent a significant portion of a company's revenue.
RFM Score 3: Customers with an RFM score of 3 in any two of the three categories are ranked as the second-highest group. This group includes customers who have either made recent and frequent purchases, frequent and high-value transactions, or recent and high-value spending. While they may not be as valuable as RFM 4 customers, they still contribute significantly to a company's success.
RFM Score 2: Customers with an RFM score of 2 in two categories and any score in the third category fall into this group. For example, a customer may have made frequent purchases but not recently, or they may have made recent purchases but with a lower monetary value. This group represents average customers who could potentially be nurtured and encouraged to move up to higher RFM segments.
RFM Score 1: This represents the lowest score, indicating the least valuable customers. Customers with an RFM score of 1 in all three categories (i.e., low recency, low frequency, and low monetary value) are the least engaged and contribute the least to a company's revenue. These customers may require more attention and targeted marketing efforts to increase their engagement and loyalty.

By ranking customers based on their RFM scores, businesses can tailor their marketing strategies and customer retention efforts more effectively. Targeted marketing campaigns can be directed at high RFM customers to encourage repeat purchases and build loyalty, while special promotions can be offered to low RFM customers to entice them to increase their engagement and spending. Understanding customer behavior through RFM analysis helps businesses optimize their sales and marketing efforts to drive revenue and overall business success.

WITH RECURSIVE rfm as(
     SELECT
     customer_name,
     ROUND(SUM(sales)) AS order$,
     ROUND(AVG(sales)) AS avgorder$,
     COUNT(order_id)AS  orders,
     SUM(quantity) AS quantity_order,
     MAX(order_date) AS last_order,
     (SELECT MAX(order_date) FROM sales) AS recent_date,
     (SELECT MAX(order_date) FROM sales) - MAX(order_date) last_order_day
     FROM sales
     GROUP BY customer_name
),
rfm_calc as (
    SELECT r.*,
          NTILE(4) OVER (ORDER BY last_order_day DESC) rfm_recency,
          NTILE(4) OVER (ORDER BY order$) rfm_frequency,
          NTILE(4) OVER (ORDER BY avgorder$) rfm_monetary
    FROM rfm AS r
)
SELECT *
FROM rfm_calc AS C;

customer_name	order$	avgorder$	orders	quantity_order	last_order	recent_date	last_order_day	rfm_recency	rfm_frequency	rfm_monetary
Nicole Brennan	274	137	2	7	2014-10-22	2017-12-30	1165	1	1	2
Georgia Rosenberg	1284	257	5	23	2014-11-21	2017-12-30	1135	1	2	3
Ricardo Emerson	48	48	1	5	2014-12-29	2017-12-30	1097	1	1	1
Craig Molinari	3984	306	13	57	2015-03-01	2017-12-30	1035	1	4	4
Valerie Takahito	1737	193	9	42	2015-04-05	2017-12-30	1000	1	2	3
Pauline Chand	1061	354	3	18	2015-08-01	2017-12-30	882	1	1	4
Andy Gerbode	1455	162	9	33	2015-09-07	2017-12-30	845	1	2	2
Peter Fuller	9063	477	19	75	2015-09-17	2017-12-30	835	1	4	4
David Philippe	1059	265	4	20	2015-10-10	2017-12-30	812	1	1	3
Craig Carroll	2854	238	12	51	2015-10-23	2017-12-30	799	1	3	3

.....

(Qr15) Concatenating the RFM values and storing it in a different table.

-- Let's drop the table if it exist
DROP TABLE rfm

WITH RECURSIVE rfm as(
     SELECT
     customer_name,
     ROUND(SUM(sales)) AS order$,
     ROUND(AVG(sales)) AS avgorder$,
     COUNT(order_id)AS  orders,
     SUM(quantity) AS quantity_order,
     MAX(order_date) AS last_order,
     (SELECT MAX(order_date) FROM sales) AS recent_date,
     (SELECT MAX(order_date) FROM sales) - MAX(order_date) last_order_day
     FROM sales
     GROUP BY customer_name
),
rfm_calc as (
    SELECT rfm.*,
          NTILE(4) OVER (ORDER BY last_order_day DESC) rfm_recency,
          NTILE(4) OVER (ORDER BY order$) rfm_frequency,
          NTILE(4) OVER (ORDER BY avgorder$) rfm_monetary
    FROM rfm
)
 select
     rfm_calc.*, rfm_recency + rfm_frequency + rfm_monetary AS rfm_cell,
     concat (rfm_recency,rfm_frequency,rfm_monetary) AS rfm_cell_string
    into rfm
    from rfm_calc;

SELECT 793

(Qr16) Checking out the new table...

SELECT * FROM rfm;

customer_name	order$	avgorder$	orders	quantity_order	last_order	recent_date	last_order_day	rfm_recency	rfm_frequency	rfm_monetary	rfm_cell	rfm_cell_string
Nicole Brennan	274	137	2	7	2014-10-22	2017-12-30	1165	1	1	2	4	112
Georgia Rosenberg	1284	257	5	23	2014-11-21	2017-12-30	1135	1	2	3	6	123
Ricardo Emerson	48	48	1	5	2014-12-29	2017-12-30	1097	1	1	1	3	111
Craig Molinari	3984	306	13	57	2015-03-01	2017-12-30	1035	1	4	4	9	144
Valerie Takahito	1737	193	9	42	2015-04-05	2017-12-30	1000	1	2	3	6	123
Pauline Chand	1061	354	3	18	2015-08-01	2017-12-30	882	1	1	4	6	114
Andy Gerbode	1455	162	9	33	2015-09-07	2017-12-30	845	1	2	2	5	122
Peter Fuller	9063	477	19	75	2015-09-17	2017-12-30	835	1	4	4	9	144
David Philippe	1059	265	4	20	2015-10-10	2017-12-30	812	1	1	3	5	113
Craig Carroll	2854	238	12	51	2015-10-23	2017-12-30	799	1	3	3	7	133

.....

(Qr17) Using the RFM ranking column to rank the customer.

SELECT customer_name,rfm_recency,rfm_frequency,rfm_monetary,
   CASE
     WHEN rfm_cell_string IN('111','122','121','124','134','113','222','133','114','123','112','212','211') THEN 'lost customers'
     WHEN rfm_cell_string IN('312','322','321','223','234','223','241','143','142','244','313','144','233','213','322') THEN 'Sliping customers'
     WHEN rfm_cell_string IN('411','422','423','421','424','413','412','323','311') THEN 'new customers'
     WHEN rfm_cell_string IN('344','431','343','333','332','433','442','444','432','443','434','334') THEN 'loyal customers'
 END rfm_segment
FROM rfm;

customer_name	rfm_recency	rfm_frequency	rfm_monetary	rfm_segment
Nicole Brennan	1	1	2	lost customers
Georgia Rosenberg	1	2	3	lost customers
Ricardo Emerson	1	1	1	lost customers
Craig Molinari	1	4	4	Sliping customers
Valerie Takahito	1	2	3	lost customers
Pauline Chand	1	1	4	lost customers
Andy Gerbode	1	2	2	lost customers
Peter Fuller	1	4	4	Sliping customers
David Philippe	1	1	3	lost customers
Craig Carroll	1	3	3	lost customers
Sam Craven	1	3	3	lost customers
Lycoris Saunders	1	1	1	lost customers
Duane Huffman	1	1	1	lost customers
Kelly Williams	1	1	1	lost customers

....

(Qr18) What two or more item are often sold together.

In sales analysis, the concept of "two or more products often sold together" refers to products that are frequently purchased in combination by customers. This purchasing behavior is known as product bundling or cross-selling. When customers tend to buy multiple items as a package or in conjunction with each other, it creates opportunities for businesses to capitalize on this trend and optimize their sales strategies.

Identifying which products are often sold together is crucial for several reasons:

Cross-Selling Opportunities: Knowing which products are frequently purchased together allows businesses to cross-sell complementary items. By suggesting related or additional products during the purchase process, companies can increase the average transaction value and boost revenue.
Targeted Marketing: Understanding product combinations that resonate with customers enables targeted marketing campaigns. Companies can tailor promotions, discounts, or product bundles based on customers' buying patterns to drive sales.
Inventory Management: Recognizing products that are often sold together helps businesses manage their inventory effectively. Maintaining sufficient stock of popular product combinations ensures that customers can easily purchase these items without experiencing stockouts.
Customer Satisfaction: Offering product bundles that align with customers' preferences enhances their overall shopping experience and satisfaction. Satisfied customers are more likely to return for future purchases, fostering brand loyalty.

To identify products often sold together, sales analysis involves examining transaction data, customer purchase history, and item associations. By analyzing large datasets, businesses can uncover patterns and relationships between different products, which can then inform sales strategies, marketing efforts, and inventory management decisions.

For example, a retail store may find that customers who purchase smartphones frequently buy phone cases and screen protectors as well. Armed with this knowledge, the store can bundle these items together, offer a discount on the package, and prominently display them as a recommended combination, encouraging customers to make additional purchases.

By leveraging insights from product bundling or cross-selling, businesses can optimize their sales tactics, enhance customer experience, and drive revenue growth. It allows companies to better meet customer needs, increase sales opportunities, and remain competitive in the market.

-- 2 items
SELECT DISTINCT order_id,
(SELECT string_agg(product_id,',')
   FROM sales AS p
   WHERE order_id IN
         ( SELECT order_id FROM
                (SELECT order_id,COUNT(*) AS Num_of_ords FROM sales group by order_id) orid
          WHERE Num_of_ords = 2) AND
 p.order_id = s.order_id) AS product_id

FROM sales AS s order by product_id;

order_id	product_id
CA-2017-161200	FUR-BO-10000468,FUR-FU-10001706
CA-2015-168480	FUR-BO-10000468,OFF-AR-10001044
CA-2016-156251	FUR-BO-10001337,OFF-BI-10003529
CA-2016-152688	FUR-BO-10001337,OFF-BI-10004584
CA-2016-152156	FUR-BO-10001798,FUR-CH-10000454
CA-2017-110198	FUR-BO-10001798,OFF-LA-10004409
US-2017-132059	FUR-BO-10001811,TEC-AC-10003280
CA-2016-123337	FUR-BO-10001918,OFF-AP-10002287
US-2017-121251	FUR-BO-10001918,TEC-PH-10004896
CA-2017-167381	FUR-BO-10001972,OFF-LA-10000134

.....

-- 3 items
SELECT DISTINCT order_id,
(SELECT string_agg(product_id,',')
   FROM sales AS p
   WHERE order_id IN
         ( SELECT order_id FROM
                (SELECT order_id,COUNT(*) AS Num_of_ords FROM sales group by order_id) orid
          WHERE Num_of_ords = 3) AND
 p.order_id = s.order_id) product_id

FROM sales AS s order by 2;

order_id	product_id
CA-2017-140326	FUR-BO-10000112,OFF-PA-10004041,OFF-AR-10001149
CA-2017-125472	FUR-BO-10000330,OFF-BI-10000591,FUR-FU-10001731
CA-2015-130785	FUR-BO-10000330,OFF-BI-10001900,FUR-BO-10003159
CA-2014-156349	FUR-BO-10000362,TEC-PH-10000441,TEC-PH-10002726
CA-2016-157707	FUR-BO-10001567,TEC-PH-10002583,FUR-CH-10004853
CA-2017-159149	FUR-BO-10001601,OFF-AR-10000937,TEC-PH-10000038
CA-2016-123533	FUR-BO-10001619,OFF-PA-10001609,OFF-AP-10002765
CA-2015-156482	FUR-BO-10002598,FUR-CH-10001708,OFF-AR-10001022
CA-2017-143574	FUR-BO-10002598,OFF-SU-10002537,FUR-FU-10003976
CA-2017-143259	FUR-BO-10003441,TEC-PH-10004774,OFF-BI-10003684
CA-2015-105690	FUR-BO-10003965,OFF-LA-10000240,TEC-CO-10001571
CA-2016-123512	FUR-BO-10004218,OFF-LA-10000081,OFF-PA-10001497
CA-2015-147788	FUR-BO-10004357,OFF-LA-10003766,OFF-ST-10000046

Visualization (KPI Report)

KPI stands for Key Performance Indicator. It is a quantifiable metric used to evaluate the success or performance of an organization, project, or individual in achieving specific objectives. KPIs are essential in measuring progress toward goals and providing valuable insights into the effectiveness of strategies and processes.

KPIs can vary depending on the industry, department, or goals of the organization. For example, in sales, KPIs may include metrics like revenue, conversion rate, or customer acquisition cost. In customer service, KPIs could involve response time, customer satisfaction ratings, or issue resolution time.

The selection of KPIs is crucial because they act as benchmarks that help organizations make data-driven decisions. KPIs provide a clear understanding of performance trends and can highlight areas that require improvement. By regularly tracking and analyzing KPIs, businesses can identify strengths, weaknesses, and opportunities to optimize their operations and achieve greater success.

Examples:
In this notebook, we use the analysis in the previous chapter to create visuals that are more understandable and readable i.e.

(Viz1) Check for the sales trends through the years (2014-2017).

(Viz2) Check the difference in sales by shipping mode.

(Viz3) Check for the difference in Sales and Profit by consumer segment.

(Viz4) Check for the top selling product category.

(Viz5) The KPI Dashboard.

A KPI dashboard is a visual tool that presents Key Performance Indicators (KPIs) in a concise and easy-to-understand format. It provides a real-time overview of an organization's performance, helping stakeholders monitor progress and make data-driven decisions quickly.

Design and Development:
The above KPI Dashboard was designed using Tableau, a powerful data visualization tool. It incorporates essential sales metrics, such as total revenue, sales growth, conversion rate, average deal size, and customer acquisition cost. The data is extracted from the sales database and refreshed in real-time to ensure the dashboard's accuracy and up-to-date insights.

Dashboard Components:

Sales Overview: This section presents an overview of total sales revenue, total profit, average sales and total quantity sold through the year 2014-2017.
Sales by Region: A geographical representation of sales revenue across different regions, providing insights into regional performance and potential areas for expansion.
Top Performing Products: This component displays the top-selling products based on revenue, helping sales teams identify high-value offerings and strategize product promotions.
Monthly Sales Trend Chart: The chart displays monthly sales data for each year in a multi-line format, allowing for easy comparison and identification of trends. Different colors represent each year's sales performance, enabling quick identification of seasonal patterns.

This article delves into the power of sales analysis through the lens of RFM (Recency, Frequency, Monetary Value) and KPI (Key Performance Indicators). It highlights how RFM analysis allows businesses to understand customer behavior and segmentation, leading to personalized strategies and improved customer retention. The article also emphasizes the significance of KPIs in measuring sales performance, guiding decision-making, and optimizing marketing efforts. Together, RFM and KPI form a dynamic duo, empowering businesses to unlock valuable insights, enhance sales strategies, and achieve data-driven success.

Thanks for coming this long....

If you like what you read, consider subscribing to my newsletter.

Find me on GitHub, Twitter

Exploratory Data Analysis With SQL

Muhammad abdulhhadi kehinde — Sat, 29 Jul 2023 18:10:07 +0000

An Analysis of the FIFA22 Players Dataset

Exploratory Data Analysis(EDA) is a crucial first step in the data analysis process. it invloves the use of various techniques, tools, and visualization to understand the structure, patterns, and characteristics of a data-set. The primary goal of EDA is to gain insights from the data, identify patterns and trends, detect anomalies, and prepare the data for further analysis and modeling.

The FIFA22 dataset is extracted from the popular football video games of FIFA. Every year, there is a new game released with updated characteristics and skills for all players from all the biggest competitions in the world. This means that there are up-to-date characteristics and skills of the players that were transferred between 2015 and 2022. The player characteristics can be found in the variables: player name, age, height in centimeters, weight in kilogram, nationality, player position, preferred foot, international reputation, and contract duration etc.The field player skills are divided into six subcategories, namely passing, defending, physical, dribbling, shooting, and pace.
For goalkeepers, the subcategories are kicking, handling, shooting, reflexes, diving, and speed. The subcategories all have subcategories themselves as well. Also, every player has an overall rating and a potential rating in the game. All ratings are in the range of 1 and 99, where the lowest is 1 and where 99 is the highest possible score.

The information in the datasets is derived from sofifa.com, which is a well-known website with an overview of all FIFA data per year. A copy of the data-set can be downloaded from this kaggle data repositry

The aim of this article is to use EDA technique to dive into the FIFA22 data-set, derive some interesting insights from the data-set and identify anomalies.

The comon question we want to answer include:

Who are the top fastest players in FIFA 2022?
Which Players are paid the most?
Who are the Top tallest players?
Who are the Top strongest players?
Who are the best players with Long passes?
Who are the best defenders?

PREREQUISITE

You need to have a basic knowledge of SQl in order to follow through this guide, also you can install any of the SQL tool which we are going to be using to run the codes i.e. MySQL, MSSQL, PostgreSQL etc.

LOADING THE DATA SET

At first we need to load the dataset we are going to work with. There are several ways to load/import data set into an SQl database, for this guidei am going to use python to import the data set into PostgreSQL.

Note: other SQL work bench allow's to import directly from the workbench eg.MySQL. Since i am using Postgres where importing data set is somehow complecated, i decided to use python to import the data set into the PSQL db which is a more faster and convinience way.

To import data set into SQL using python;

import pandas as pd
player_df = pd.read_csv('player_22')

from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:mlyt09@localhost:5432/projectsql')

Here: postgresql:// > the sql_tool, postgres: > the user, ~~mlyt09~~ > the password, @localhost: > the host, 5432 > the port, /projectsql the database.

player_df.to_sql(name='players', con=engine,index=False if_exists='replace')

player_df is the Pandas DataFrame that you want to write to the database.
name is the name of the table that you want to create in the database.
con is a connection object to the database.
if_exists specifies what to do if the table already exists. The possible values are fail, replace, and append.
index specifies whether to write the index of the DataFrame to the database.
dtype specifies the data types of the columns in the DataFrame.
schema specifies the schema of the database.

Check to confirm the data is in PSQL...

projectsql=# \dt players

List of relations

Schema	Name	Type	Owner
public	players	table	postgres

projectsql=# \d players

Table "public.players"

Column	Type	Collation	Nullable	Default
sofifa_id	bigint
player_url	text
short_name	text
long_name	text
year	bigint
player_positions	text
overall	bigint
potential	bigint
value_eur	double precision
wage_eur	double precision
age	bigint
dob	date
height_cm	bigint
weight_kg	bigint

100+ other columns.....

Let Start by Using SQL Queries to answer some of our quetions.

(Qr1) Check to confirm the total number of records/players in the dataset:

Note: Qr here means query, so Qr1,2,3 > Query1,2,3 etc.

SELECT COUNT(*) AS total_no_of_players FROM players;

total_no_of_players
19239

(Qr2) Top 10 Nationality of Players for 2022:

SELECT DISTINCT nationality_name, COUNT(nationality_name) AS total_no_of_players
    FROM players  GROUP BY nationality_name
    ORDER BY total_no_of_players DESC LIMIT 10;

nationality_name	total_no_of_players
England	1719
Germany	1214
Spain	1086
France	980
Argentina	960
Brazil	897
Japan	546
Netherlands	439
United States	413
Poland	403

As can be noted above, we use the DISTINCT key word to select the unique/distinct nationality of players. With the query above, using the COUNT function we are able to count the total number of players represeting each country,
from the result we could see that england has the highest representative in club-football world @2022.

(Qr3) Countries with the most Football Talented (by pontential) Players:

The potential of a football player is a complex and multifaceted concept. It can be defined as the player's ability to reach their full potential as a footballer. This potential is determined by a number of factors, including the player's physical attributes, technical skills, tactical awareness, mental strength, and psychological makeup.

The potential of a football player is not something that is fixed. It can be developed and improved through training, hard work, and dedication. Players who are willing to put in the hard work and dedication can reach their full potential and become world-class footballers.

SELECT sum(potential) FROM players  WHERE potential > 79;

SELECT DISTINCT nationality_name, ROUND((SUM(potential)/147969)*100,2) potential
    FROM players
    WHERE potential > 79
    GROUP BY nationality_name
    ORDER BY potential DESC LIMIT 10;

nationality_name	potential
Spain	13.03
England	9.29
France	8.85
Brazil	6.13
Argentina	5.95
Germany	5.04
Portugal	4.78
Netherlands	4.30
Italy	3.93
Belgium	2.86
(10 rows)

Age and Performance are use to rate the potential of a player, the more younger and good performance the more highly potentialy rated. These are one of the metrics club-scouts use to scout for new young and talented players to joined their project. As can be seen above, we calculated for the percentage of potiential players each county has, it's no suprise to see that players from european and south american country have the best potential players.

(Qr4) Top 10 Fastest Players:

In the following code, these atrribute listed below will be use to rank the fastest players;

movement_acceleration, movement_sprint_speed, pace,club_position.

The most important player attribute from movement_acceleration, movement_sprint_speed, and pace is movement_acceleration. This is because acceleration is the rate at which a player can change their speed. This is important for players in all positions, but it is especially important for players who play in attacking positions, such as wingers and strikers.

Movement_sprint_speed is also important, but it is not as important as movement_acceleration. This is because sprint_speed is the maximum speed that a player can reach. However, players do not need to reach their maximum speed very often. They need to be able to accelerate quickly in order to get past defenders and create scoring opportunities.

Pace is a combination of movement_acceleration and movement_sprint_speed. It is a good overall measure of a player's speed, but it is not as specific as movement_acceleration or movement_sprint_speed.

SELECT short_name, movement_acceleration,
       movement_sprint_speed, pace,club_position,nationality_name
    FROM players ORDER BY movement_acceleration DESC LIMIT 10;

short_name	movement_acceleration	movement_sprint_speed	pace	club_position	nationality_name
K. Mbapp├⌐	97	97	97	ST	France
Adama Traor├⌐	97	96	96	LW	Spain
M. Diaby	96	92	94	RM	France
D. James	96	95	95	SUB	Wales
A. Davies	96	96	96	LB	Canada
A. Hakimi	95	95	95	RB	Morocco
Vin├¡cius Jr.	95	95	95	SUB	Brazil
R. Sterling	95	88	91	SUB	England
U. Antuna	95	90	92	SUB	Mexico
C. Ejuke	95	93	94	LM	Nigeria

(10 rows)

Acceleration usually defines the rate at which someone/thing moves. together with other varibles concider we could see that the most fastest player is Kylian mbape which is no suprising due to his high attacking game.

BONUS: Code Explanation

Let's break down the code line by line:

SELECT is the keyword that tells the database to select specific columns from a table.
long_name, movement_acceleration, movement_sprint_speed, pace, club_position, and nationality_name are the columns that we want to select.
FROM is the keyword that tells the database which table to select the columns from. In this case, the table is called players.
ORDER BY is the keyword that tells the database to order the results by the movement_acceleration column in descending order.
DESC is the keyword that tells the database to order the results in descending order.
LIMIT is the keyword that limits the results to the first 10 rows.

The code will return the following results:

The long_name, movement_acceleration, movement_sprint_speed, pace, club_position, and nationality_name of the 10 players with the highest movement_acceleration.
The results will be ordered by the movement_acceleration column in descending order.

(Qr5) Top 10 Strongest Players by Stregnth:

SELECT short_name, power_strength, power_stamina,physic, weight_kg, club_name
    FROM players
    ORDER BY power_strength DESC LIMIT 10;

short_name	power_strength	power_stamina	physic	weight_kg	club_name
A. Akinfenwa	97	34	74	110	Wycombe Wanderers
T. Petr├í┼íek	96	57	79	99	Rak├│w Cz─Östochowa
D. Dike	96	59	78	100	Orlando City Soccer Club
R. Lukaku	95	71	83	94	Chelsea
A. Seck	95	73	86	95	Royal Antwerp FC
A. M├⌐ndez	95	81	86	82	Club Nacional de Football
G. Kondogbia	94	82	89	76	Atl├⌐tico de Madrid
S. Coates	94	80	87	92	Sporting CP
C. Luyindama	94	73	85	91	Galatasaray SK
K. Koulibaly	94	70	85	89	Napoli

(10 rows)

The strength and stamina of a players usually determines thier stability, compose and control of the ball. Players with high qualities of the aforemention usually have an edge over oposition players(defend or attack).

(Qr6) Top 10 Strongest Players by stamina:

SELECT long_name, power_strength, power_stamina,physic, weight_kg, club_name
 FROM players
 ORDER BY power_stamina DESC LIMIT 10

long_name	power_strength	power_stamina	physic	weight_kg	club_name
N'Golo Kant├⌐	72	97	83	70	Chelsea
Jewgienij Baszkirow	52	96	67	71	Zag┼é─Öbie Lubin
Rhyan Bert Grant	77	95	82	79	Sydney FC
Vladim├¡r Darida	48	95	63	67	Hertha BSC
Andrew Robertson	65	95	76	64	Liverpool
Nicol├▓ Barella	69	95	78	68	Inter
σÑÑσƒ£ σìÜΣ║«	71	95	73	68	Cerezo Osaka
Pieter Gerkens	58	94	71	72	Royal Antwerp FC
Didier Andr├⌐s Moreno Asprilla	79	94	79	77	Junior FC
Denzel Justus Morris Dumfries	89	94	89	80	Inter

(10 rows)

Best Players FIFA22 (position wise)

Who were the best players(position wise) for the FIFA22 video game, concidering variables that are related to each player position we are going to answer the following questions;

(Qr7) Best Wingers:

Looking at the dataset, we will realize both the lw(Left-Wing) and rw(Right-Wing) column are formatted as text even though they contain numbers, with the below query we could understand why.
Some of the number contain character that are not number, we will have to clean this up and convert the columns into an integer!.

--lw
SELECT lw FROM players WHERE lw LIKE '%-%';

The code above is a SQL SELECT statement. It is used to select all rows from the players table where the lw column contains a hyphen (-).

lw
83-1
83-1
82-1
81-1
81-1

...... 300+ other values

SELECT lw, SUBSTRING(lw,1,POSITION('-' in  lw)-1) AS lw_cleaned
    FROM players
    WHERE lw LIKE '%-%';

The code above is an SQL SELECT statement that will return the lw column from the players table, along with a new column called lw_cleaned. The lw_cleaned column will contain the value of the lw column, but with the hyphen (-) removed.

lw	lw_cleaned
83-1	83
83-1	83
82-1	82
81-1	81
81-1	81

.... 300+ other values

UPDATE players SET lw = SUBSTRING(lw,1,POSITION('-' in  lw)-1) WHERE lw LIKE '%-%'

UPDATE 326

The code above is an SQL UPDATE statement that will update the lw column in the players table. The update will remove the hyphen (-) from the lw column, if it is present.

Finally we need to convert the the column to integer to be able to apply any arithmetic operation.

ALTER TABLE players ALTER COLUMN  lw TYPE Bigint USING lw::bigint

ALTER TABLE

The above is an SQL ALTER TABLE statement that will change the data type of the lw column in the players table to Bigint.

Let's apply the above to rw(right-winger):

-- rw
SELECT rw FROM players WHERE rw LIKE '%-%'

rw
75-1
83-1
82-1
81-1
81-1

.....300+

SELECT rw, SUBSTRING(rw,1,POSITION('-' in  rw)-1) AS rw
    FROM players
    WHERE rw LIKE '%-%'

rw	rw
75-1	75
83-1	83
82-1	82
81-1	81
81-1	81
80-1	80
80-1	80
80-1	80

UPDATE players SET rw = SUBSTRING(rw,1,POSITION('-' in  rw)-1) WHERE rw LIKE '%-%'

UPDATE 326

ALTER TABLE players ALTER COLUMN  rw type Bigint USING rw::bigint

ALTER TABLE

Finally we can add the values for lw & rw for each players to get the highest rating player, playing in the wing role!.

The code below is an SQL SELECT statement that will select specific columns from the players table and order the results by the wingers column in descending order. The LIMIT clause limits the results to the first 10 rows.

The most important attribute of a winger from skill_dribbling, attacking_short_passing, and movement_acceleration is skill_dribbling. This is because dribbling is the ability to control the ball while moving, which is essential for wingers who are tasked with beating defenders and creating scoring opportunities.

Wingers need to be able to dribble past defenders in order to create space for themselves and their teammates. They also need to be able to dribble in tight spaces, such as in the penalty area.

Attacking_short_passing is also important for wingers, as they need to be able to pass the ball accurately and quickly to their teammates in order to create scoring opportunities. However, it is not as important as skill_dribbling, as wingers do not need to pass the ball as often as they need to dribble.

Movement_acceleration is also important for wingers, as they need to be able to accelerate quickly in order to get past defenders. However, it is not as important as skill_dribbling or attacking_short_passing, as wingers do not need to accelerate as often as they need to dribble or pass the ball.

SELECT short_name, rw + lw wingers,skill_dribbling,attacking_short_passing,
       movement_acceleration,overall,age, club_name
       FROM players  ORDER BY wingers DESC LIMIT 10;

short_name	wingers	skill_dribbling	attacking_short_passing	movement_acceleration	overall	age	club_name
L. Messi	184	96	91	91	93	34	Paris Saint-Germain
Neymar Jr	180	95	86	93	91	29	Paris Saint-Germain
K. Mbapp├⌐	180	93	85	97	91	22	Paris Saint-Germain
Cristiano Ronaldo	176	88	80	85	91	36	Manchester United
M. Salah	176	90	84	89	89	29	Liverpool
K. De Bruyne	176	88	94	76	91	30	Manchester City
R. Sterling	174	87	83	95	88	26	Manchester City
H. Son	174	87	84	85	89	28	Tottenham Hotspur
S. Man├⌐	174	90	84	93	89	29	Liverpool
P. Dybala	174	90	87	88	87	27	Juventus

(10 rows)

Here is an explanation of the 'rw + lw' AS 'wingers' part of the code:

The rw + lw expression adds the values of the rw and lw columns.
The AS wingers clause gives the new column the name wingers.
This means that the wingers column will contain the sum of the values of the rw and lw columns. This is useful because it allows us to rank the players by their overall attacking ability, regardless of whether they are primarily right wingers, left wingers, or both.

(Qr8) Best Forwarder (Striker):

In the following code, these atrribute listed below will be use to rank the best forwarders;

shooting, attacking_finishing, attacking_heading_accuracy, skill_dribbling, power_shot_power.

The most important attribute for a striker is shooting. This is because the main job of a striker is to score goals, and the shooting attribute determines how likely they are to do so. A good striker will have a high shooting attribute, and they will be able to score goals from a variety of different positions.

The other attributes listed are also important, but they are not as important as shooting. For example, a striker with a high attacking finishing attribute may be able to score goals from close range, but if they do not have a good shooting attribute, they will not be able to score goals from other positions.

Ultimately, the best way to judge a good striker is to watch them play. However, if you are only able to look at the attributes, then the shooting attribute is the most important one to consider.

SELECT COUNT(*) FROM players WHERE shooting IS NULL;

count
2132

UPDATE players SET shooting = 0 WHERE shooting IS NULL;

UPDATE 2132

SELECT short_name, shooting, attacking_finishing, power_shot_power,
       attacking_heading_accuracy, skill_dribbling, club_name
       FROM players  ORDER BY shooting DESC LIMIT 10;

short_name	shooting	attacking_finishing	power_shot_power	attacking_heading_accuracy	skill_dribbling	club_name
Cristiano Ronaldo	94	95	94	90	88	Manchester United
L. Messi	92	95	86	70	96	Paris Saint-Germain
R. Lewandowski	92	95	90	90	85	FC Bayern M├╝nchen
E. Haaland	91	94	94	69	78	Borussia Dortmund
H. Kane	91	94	91	86	83	Tottenham Hotspur
L. Su├írez	90	93	89	84	83	Atl├⌐tico de Madrid
S. Ag├╝ero	89	93	90	78	86	FC Barcelona
K. Mbapp├⌐	88	93	86	72	93	Paris Saint-Germain
H. Son	87	88	88	68	87	Tottenham Hotspur
M. Salah	87	91	82	59	90	Liverpool

(10 rows)

(Qr9) Best Midfielder:

In the following code, these atrribute listed below will be use to rank the best Midfielders;

mentality_vision, movement_balance, attacking_crossing, mentality_interceptions, defending_marking_awareness

The most important attribute to judge a midfielder is mentality_vision. This is because midfielders need to be able to see the game ahead of them and make good decisions, both offensively and defensively. A midfielder with a high mentality_vision attribute will be able to see where their teammates are and where the space is, and they will be able to make passes and crosses that create scoring opportunities.

The other attributes listed are also important, but they are not as important as mentality_vision. For example, a midfielder with a high attacking_crossing attribute may be able to make good crosses, but if they do not have a good mentality_vision attribute, they will not be able to see where their teammates are and they will not be able to make the right passes.

Ultimately, the best way to judge a midfielder is to watch them play. However, if you are only able to look at the attributes, then the mentality_vision attribute is the most important one to consider.

SELECT short_name, mentality_vision, movement_balance, attacking_crossing,
       mentality_interceptions, defending_marking_awareness, club_name
       FROM players  ORDER BY mentality_vision DESC LIMIT 10;

short_name	mentality_vision	movement_balance	attacking_crossing	mentality_interceptions	defending_marking_awareness	club_name
L. Messi	95	95	85	40	20	Paris Saint-Germain
K. De Bruyne	94	78	94	66	68	Manchester City
Luis Alberto	92	83	69	59	58	Lazio
P. Dybala	91	94	82	42	32	Juventus
T. Kroos	90	71	88	80	71	Real Madrid CF
Bruno Fernandes	90	79	87	66	72	Manchester United
Neymar Jr	90	84	85	37	35	Paris Saint-Germain
L. Modri─ç	90	92	86	80	70	Real Madrid CF
David Silva	90	89	83	50	58	Real Sociedad
Iniesta	90	76	75	59	68	Vissel Kobe

(10 rows)

(Qr10) Best Defender:

In the following code, these atrribute listed below will be use to rank the best Defenders;

movement_balance, mentality_aggression, mentality_interceptions, defending_marking_awareness, defending_sliding_tackle, defending_standing_tackle

The most important attribute to judge a defender is defending_marking_awareness. This is because defenders need to be able to mark their opponents and prevent them from scoring. A defender with a high defending_marking_awareness attribute will be able to stay close to their opponents and prevent them from getting into good scoring positions.

The other attributes listed above are also important, but they are not as important as defending_marking_awareness. For example, a defender with a high mentality_aggression attribute may be able to put in hard tackles, but if they do not have a good defending_marking_awareness attribute, they will not be able to mark their opponents effectively.

SELECT short_name, defending_marking_awareness, defending_sliding_tackle, defending_standing_tackle,
       mentality_interceptions, mentality_aggression, club_name
       FROM players
       ORDER BY defending_marking_awareness DESC LIMIT 10;

short_name	defending_marking_awareness	defending_sliding_tackle	defending_standing_tackle	mentality_interceptions	mentality_aggression	club_name
G. Chiellini	93	88	89	89	88	Juventus
V. van Dijk	92	86	92	90	83	Liverpool
R├║ben Dias	90	85	89	85	92	Manchester City
M. ┼ákriniar	90	82	88	86	84	Inter
K. Koulibaly	90	86	88	85	83	Napoli
S. Savi─ç	90	83	86	87	86	Atl├⌐tico de Madrid
N. Kant├⌐	90	86	93	91	93	Chelsea
M. Hummels	90	86	90	89	71	Borussia Dortmund
S. de Vrij	89	85	87	87	77	Inter
Marquinhos	89	89	89	88	81	Paris Saint-Germain

(10 rows)

(Qr11) Best Goalkeeper:

In the following code, these atrribute listed below will be use to rank the best Goal Keepers;

goalkeeping_reflexes, goalkeeping_handling, goalkeeping_diving, goalkeeping_kicking

The most important attribute to judge a goalkeeper is goalkeeping_reflexes. This is because goalkeepers need to be able to react quickly to save shots. A goalkeeper with a high goalkeeping_reflexes attribute will be able to make saves that most other goalkeepers would miss.

The other attributes listed are also important, but they are not as important as goalkeeping_reflexes. For example, a goalkeeper with a high goalkeeping_handling attribute may be able to catch and hold onto the ball well, but if they do not have good reflexes, they will not be able to save shots that are hit with a lot of power.

SELECT short_name, goalkeeping_reflexes, goalkeeping_handling, goalkeeping_diving,
       goalkeeping_kicking,height_cm, club_name
    FROM players
    ORDER BY goalkeeping_reflexes DESC LIMIT 10;

short_name	goalkeeping_reflexes	goalkeeping_handling	goalkeeping_diving	goalkeeping_kicking	height_cm	club_name
M. ter Stegen	90	85	88	88	187	FC Barcelona
H. Lloris	90	83	88	65	188	Tottenham Hotspur
G. Donnarumma	90	83	91	79	196	Paris Saint-Germain
K. Schmeichel	90	78	84	80	189	Leicester City
J. Oblak	90	92	87	78	188	Atl├⌐tico de Madrid
Alisson	89	86	86	84	191	Liverpool
K. Navas	89	84	89	75	185	Paris Saint-Germain
M. Neuer	88	88	88	91	193	FC Bayern M├╝nchen
Ederson	88	82	87	93	188	Manchester City
T. Courtois	88	89	84	74	199	Real Madrid CF

(10 rows)

(Qr12) Most Paid player(Wage in eur):

Player wages are the salaries that players receive from their clubs. They are typically paid weekly or monthly, and they can vary depending on a number of factors, including the player's age, experience, ability, and the club's financial situation.

Player wages are an important part of the economics of professional football. They can be a significant financial burden for clubs, but they are also necessary to attract and retain the best players.

Player wages can also be affected by other factors, such as the player's contract length, the player's image rights, and the player's performance.

In recent years, player wages have been rising steadily. This is due to a number of factors, including the increasing commercialization of football and the increasing competition for talent.

As a result, player wages have become a major issue for clubs. Some clubs are struggling to afford the wages of their players, and this is leading to financial problems.

SELECT COUNT(*) FROM players WHERE wage_eur IS NULL;

count
61

UPDATE players SET wage_eur = 0 WHERE wage_eur IS NULL;

UPDATE 61

SELECT short_name, wage_eur, value_eur, overall, club_position,nationality_name, club_name
    FROM players
    ORDER BY wage_eur DESC LIMIT 10;

short_name	wage_eur	value_eur	overall	club_position	nationality_name	club_name
K. De Bruyne	350000	125500000	91	RCM	Belgium	Manchester City
K. Benzema	350000	66000000	89	CF	France	Real Madrid CF
L. Messi	320000	78000000	93	RW	Argentina	Paris Saint-Germain
Casemiro	310000	88000000	89	CDM	Brazil	Real Madrid CF
T. Kroos	310000	75000000	88	LCM	Germany	Real Madrid CF
R. Sterling	290000	107500000	88	SUB	England	Manchester City
R. Lewandowski	270000	119500000	92	ST	Poland	FC Bayern M├╝nchen
Cristiano Ronaldo	270000	45000000	91	ST	Portugal	Manchester United
M. Salah	270000	101000000	89	RW	Egypt	Liverpool
Neymar Jr	270000	129000000	91	LW	Brazil	Paris Saint-Germain

(10 rows)

(Qr13) Top Rated Player player(Overall):

The overall rating of a player is a number that represents their overall ability in FIFA. It is calculated based on a number of factors, including their attributes, their position, and their form.

The overall rating of a player can range from 1 to 99, with 99 being the highest possible rating. Players with a high overall rating are considered to be the best players in the world.

The overall rating of a player is a dynamic measure, which means that it can change over time. The player's attributes can improve as they get more experience. Their form can also improve or decline, as a result, the overall rating of a player is a good way to track their progress and to see how they compare to other players.

SELECT short_name, overall,age,club_position,club_name
    FROM players
    ORDER BY overall DESC LIMIT 10;

short_name	overall	age	club_position	club_name
L. Messi	93	34	RW	Paris Saint-Germain
R. Lewandowski	92	32	ST	FC Bayern M├╝nchen
Cristiano Ronaldo	91	36	ST	Manchester United
K. De Bruyne	91	30	RCM	Manchester City
J. Oblak	91	28	GK	Atl├⌐tico de Madrid
K. Mbapp├⌐	91	22	ST	Paris Saint-Germain
Neymar Jr	91	29	LW	Paris Saint-Germain
H. Kane	90	27	ST	Tottenham Hotspur
N. Kant├⌐	90	30	RCM	Chelsea
M. Neuer	90	35	GK	FC Bayern M├╝nchen

(10 rows)

(Qr14) Preferred Foot of a Player:

A player's preferred foot is the foot that they use most often to kick the ball. This can have a significant impact on their playing style and their ability to perform certain skills.

For example, a player who is right-footed will typically be better at crossing the ball with their right foot and shooting with their right foot. They may also be better at dribbling with their right foot and passing with their right foot.

On the other hand, a player who is left-footed will typically be better at crossing the ball with their left foot and shooting with their left foot. They may also be better at dribbling with their left foot and passing with their left foot.

Of course, there are some players who are ambidextrous and can use both feet equally well. However, most players have a preferred foot that they use more often.

The preferred foot of a player can be determined by a number of factors, including their genetics, their training, and their playing style.

Finally, a player's playing style can also affect their preferred foot. For example, a player who is a striker will typically need to be good at shooting with both feet, as they will need to be able to score goals from both sides of the goal. However, a player who is a defender may only need to be good at using one foot, as they will typically only need to defend from one side of the field.

SELECT DISTINCT preferred_foot, COUNT(*) AS total
    FROM players
    GROUP BY preferred_foot
    ORDER BY total DESC;

preferred_foot	total
Right	14674
Left	4565

(2 rows)

(Qr15) What is the player age distribution?:

Below, we could note that the player age distribution in soccer is typically bimodal, with a peak in the early 20s and another peak in the late 30s. This is because players tend to reach their peak physical and technical abilities in their early 20s, but they can also maintain a high level of performance into their late 30s if they stay fit and healthy.

There are a few reasons for the bimodal distribution. First, young players are often more physically gifted than older players, and they have more energy. This allows them to run more, tackle harder, and jump higher. Second, young players are often more technically gifted than older players, and they have better ball control and passing skills. This allows them to create more chances and score more goals.

However, young players often lack experience and decision-making skills. This can lead to mistakes, and it can also make them more susceptible to injuries. As players get older, they gain experience and decision-making skills. This allows them to make better decisions on the field, and it also helps them to avoid injuries.

The age distribution of players also varies depending on the position. For example, goalkeepers tend to be older than other players, because they need to have a high level of experience and decision-making skills. Defenders also tend to be older than other players, because they need to be physically strong and have a good understanding of the game.

SELECT DISTINCT age, COUNT(*) total
       FROM players GROUP BY age ORDER BY total DESC;

age	total
21	1547
22	1446
24	1442
25	1394
23	1387
20	1377
27	1200
26	1197
29	1178
28	1129
19	1099
30	901
31	825
18	733
32	634
33	468
34	354
17	271
35	258
36	146
37	105
38	62
39	39
16	20
40	14
41	7
43	3
42	2
54	1

(29 rows)

BONUS

(Qr16) What is the current player age(Now):

ALTER TABLE players ALTER COLUMN dob TYPE DATE USING dob::DATE;

ALTER TABLE

SELECT EXTRACT(YEAR FROM AGE(NOW(), dob)) age_now FROM players LIMIT 6;

age_now
36
34
38
31
32
24

(6 rows)

ALTER TABLE players ADD age_now INT;

ALTER TABLE

UPDATE players SET age_now = EXTRACT(YEAR FROM AGE(NOW(), dob))

UPDATE 19239

SELECT short_name, age AS age_2022, age_now
    FROM players
    ORDER BY overall DESC LIMIT 8;

short_name	age_2022	age_now
L. Messi	34	36
R. Lewandowski	32	34
J. Oblak	28	30
K. De Bruyne	30	32
Neymar Jr	29	31
Cristiano Ronaldo	36	38
K. Mbapp├⌐	22	24
H. Kane	27	30

(8 rows)

HIGHLIGHTS AND CONCLUSION

In this EDA of FIFA data set, we have explored a variety of features, including player age, overall rating, preferred foot, wage, and best position. We have found that there are a number of interesting trends and relationships within the data. For example, we have found that:

Player age distribution of players is bimodal, with a peak in early 20s and another in late 30s.
The best players playing in their role/position often have high technical abilities/qualities and can effect their team overall performance.
The High overall rating of players of result from their form/performance and their atrribute, it can also be from how they effect their team to play better. This is a good way of tracking a player progress.
lastly the wage of players are skyrocketing by year, this as cause major issue for football clubs. Some clubs are struggling to afford the wages of their players, and this is leading to financial problems.

Through this EDA, we have learned a number of things about Exporatory Data Analysis in SQL. For example, we have learned how to:

Use SQL queries to explore data sets.
Identify trends and relationships within data sets.
Test hypotheses about data sets.
We have also learned that EDA is a powerful tool that can be used to gain insights into data sets. By exploring data sets, we can identify trends and relationships that would not be obvious from simply looking at the data. This information can be used to make better decisions about the data, or to build better models.

The next step in this project would be to conduct more in-depth analysis of the data. This could involve using statistical tests to test our hypotheses, or building machine learning models to predict player performance.

Thanks for coming this long....

If you like what you read, consider subscribing to my newsletter

Find me on GitHub, Twitter

DEV Community: Muhammad abdulhhadi kehinde

Becoming a Data Analyst: Unveiling the Path to Success

INTRODUCTION:

Course/Step 1: Understanding the Data Ecosystem

Course/Step 2: Spreadsheet Mastery

Course/Step 3: SQL and Databases

Course/Step 4: Statistics

Course/Step 5: Business Intelligence and Visualizations

Course/Step 6: Computational Thinking and Programming

Course/Step 7: Applying Data Analysis Skills

Course/Step 8: The Final Part of Your Data Analyst Journey

Data-Driven Sales Strategies: The Impact of RFM Analysis and KPI Reports Unveiled

Let's take a look at RFM analysis and KPI report of Sales data with SQl and tableau

LOADING THE DATA SET

To import data set into SQL using python;

Querying the data to answer the questions.

(Qr1) Check to confirm the total number of records.

(Qr2) Extracting Year and Month from the 'order_date' column.

(Qr3) Checking the Unique values for Some of the data_variables.

(Qr4) Product Category with the most Sales through 2014-2017.

(Qr5) Product Category with the most profit through 2014-2017.

(Qr6) Year with the most sales through 2014-2017.

(Qr7) Trends in Sales and Profit by consumer Segment....

(Qr8) Trends in Sales and Profit by region....

(Qr9) What was the best month for sales in a specific year ?

What's the sales for the month?

(Qr10) Dive in through the best month to check for the best Product Category...

Top 5 best selling product category in the best month of each year.

(Qr11) What's the Total Sales, Profit and Quantity Sold.

(Qr12) RFM Analysis.

Checking for the recent order_date for each customer compare to recent_date.

(Qr13) How many days has it been a customer ordered?

(Qr14) Ranking the Recency, Frequency and Monetary value.

(Qr15) Concatenating the RFM values and storing it in a different table.

(Qr16) Checking out the new table...

(Qr17) Using the RFM ranking column to rank the customer.

(Qr18) What two or more item are often sold together.

Visualization (KPI Report)

(Viz1) Check for the sales trends through the years (2014-2017).

(Viz2) Check the difference in sales by shipping mode.

(Viz3) Check for the difference in Sales and Profit by consumer segment.

(Viz4) Check for the top selling product category.

(Viz5) The KPI Dashboard.

Exploratory Data Analysis With SQL

An Analysis of the FIFA22 Players Dataset

LOADING THE DATA SET

To import data set into SQL using python;

Let Start by Using SQL Queries to answer some of our quetions.

(Qr1) Check to confirm the total number of records/players in the dataset:

(Qr2) Top 10 Nationality of Players for 2022:

(Qr3) Countries with the most Football Talented (by pontential) Players:

(Qr4) Top 10 Fastest Players:

BONUS: Code Explanation

(Qr5) Top 10 Strongest Players by Stregnth:

(Qr6) Top 10 Strongest Players by stamina:

Best Players FIFA22 (position wise)

(Qr7) Best Wingers:

Let's apply the above to rw(right-winger):

(Qr8) Best Forwarder (Striker):

(Qr9) Best Midfielder:

(Qr10) Best Defender:

(Qr11) Best Goalkeeper:

(Qr12) Most Paid player(Wage in eur):

(Qr13) Top Rated Player player(Overall):

(Qr14) Preferred Foot of a Player:

(Qr15) What is the player age distribution?:

BONUS

(Qr16) What is the current player age(Now):

HIGHLIGHTS AND CONCLUSION