The goal of this project is to perform a wholesome analysis of the effect of COVID-19 on Global trade (Exports) focusing of some countries/continent (United Kingdom, East Asia (excluding china), China, European union (27), United States) from 2015 to 2021.
Data Source
One dataset was provided which are dataset for impact of covid-19 from 2015 to 2021.
The following steps shows how the cleaning process was executed to present the data ready for analysis:
Dropped irrelevant columns
Renamed some columns.
Deleted irrelevant rows.
checked for NULL values
Exploratory Data analysis
I created the Effect of covid-19 database on PostgreSQL to accommodate the cleaned dataset, then I imported the dataset into it.
After importing, I cleaned and pre-processed it to ensure that the dataset ready for analysis.
I further proceeded to answer key questions such as:
Determine the trade revenue for individual years from 2015 - 2021 and percentage change/growth.?
Determine the trade revenue (by country/continent) for individual years from 2015 - 2021 and percentage change.
Determine the trade revenue (by commodity) for individual years from 2015 - 2021 and percentage change.
Data analysis and skill demonstrated
Functions used to Query (Aggregate functions, Union Set Operations, Common Table Expressions, Correlations)
Quick Measures
Filters use in Power BI
KPI in Power BI
Slicers in Power BI
Findings
Between 2015-2019 there was a steady revenue growth rate of 9% however it dipped to -5% from 2019-2021. This was a result of effect of increased trade restrictions amongst countries due to the pandemic.
China was most hit in 2020 where revenue dipped to -1% against 25% revenue growth in 2019, European union (27) trade revenue dipped to 0% against 6% in 2019, East Asia (Excluding china) 4% against 6% in 2019. However United states recorded significant growth of about 16% in 2020 against -1% in 2019, United kingdom recorded slight growth in 2020 of 1% against 0% in 2019. Also revenue dipped in 2021 for all countries except China which recorded 17% growth against figures in 2019.
Trade revenue by commodity all dipped in year 2020 – 2021 with Fish, crustaceans, and molluscs category as the most hit with -24% in 2020 against its 22% growth in 2019.
Commodities in the ‘General and ‘Milk powder, butter, and cheese’ contributed to 72% and 15% percent of total revenue from 2015 to 2021, making them the key performers amongst the commodities. These commodities were less hit by the covid pandemic though having little growth within in 2020 – 2021 period - however positive - compared to other product categories.
China, East Asia (excluding china) and united states contributed to a combined 91% of total revenue , making them key contributors to trade exports revenue.
Q3 and Q4 was across all years was observed to have a higher influence on export trade as that period contributed to 75% of total export trade revenue.
Solutions
Determine the trade revenue for individual years from 2015 - 2021 and percentage change/growth.
SELECT year, SUM (revenue) as Total_revenue FROM data
GROUP BY year
ORDER BY year ASC
Determine the trade revenue (by country/continent) for individual years from 2015 - 2021 and percentage change.
SELECT year, country, SUM (revenue) as revenue_Avg FROM data
GROUP BY year, country
ORDER BY country, year ASC
Determine the trade revenue (by commodity) for individual years from 2015 - 2021 and percentage change.
SELECT year, commodity, SUM (revenue) as revenue_Avg FROM data
GROUP BY year, commodity
ORDER BY commodity, year ASC
Dashboard
Recommendations
Commodities in the ‘General’ and ‘Milk powder, butter, and cheese’ should be focused on as they are key contributors and performers in Global export trade.
Countries like China, others in east Asian countries and United states should be leveraged on to boost exports as they present more opportunities due to their population size and export revenue volumes.
Also Q3 & Q4 presents significant opportunities to boost sales volumes as they are key performing financial quarters.
Project Overview
Data Source
Tools
Data cleaning and Preparation
Exploratory Data analysis
Data analysis and skill demonstrated
Findings
Solutions
Dashboard
Recommendations
Project Overview
Data Source
Tools
Data cleaning and Preparation
The following steps shows how the cleaning process was executed to present the data ready for analysis:
Dropped irrelevant columns
Renamed some columns.
Deleted irrelevant rows.
checked for NULL values
Exploratory Data analysis
Determine the trade revenue for individual years from 2015 - 2021 and percentage change/growth.?
Determine the trade revenue (by country/continent) for individual years from 2015 - 2021 and percentage change.
Determine the trade revenue (by commodity) for individual years from 2015 - 2021 and percentage change.
Data analysis and skill demonstrated
Findings
Between 2015-2019 there was a steady revenue growth rate of 9% however it dipped to -5% from 2019-2021. This was a result of effect of increased trade restrictions amongst countries due to the pandemic.
China was most hit in 2020 where revenue dipped to -1% against 25% revenue growth in 2019, European union (27) trade revenue dipped to 0% against 6% in 2019, East Asia (Excluding china) 4% against 6% in 2019. However United states recorded significant growth of about 16% in 2020 against -1% in 2019, United kingdom recorded slight growth in 2020 of 1% against 0% in 2019. Also revenue dipped in 2021 for all countries except China which recorded 17% growth against figures in 2019.
Trade revenue by commodity all dipped in year 2020 – 2021 with Fish, crustaceans, and molluscs category as the most hit with -24% in 2020 against its 22% growth in 2019.
Commodities in the ‘General and ‘Milk powder, butter, and cheese’ contributed to 72% and 15% percent of total revenue from 2015 to 2021, making them the key performers amongst the commodities. These commodities were less hit by the covid pandemic though having little growth within in 2020 – 2021 period - however positive - compared to other product categories.
China, East Asia (excluding china) and united states contributed to a combined 91% of total revenue , making them key contributors to trade exports revenue.
Q3 and Q4 was across all years was observed to have a higher influence on export trade as that period contributed to 75% of total export trade revenue.
Solutions
SELECT year, SUM (revenue) as Total_revenue FROM data GROUP BY year ORDER BY year ASC
SELECT year, country, SUM (revenue) as revenue_Avg FROM data GROUP BY year, country ORDER BY country, year ASC
SELECT year, commodity, SUM (revenue) as revenue_Avg FROM data GROUP BY year, commodity ORDER BY commodity, year ASC
Dashboard
Recommendations
Commodities in the ‘General’ and ‘Milk powder, butter, and cheese’ should be focused on as they are key contributors and performers in Global export trade.
Countries like China, others in east Asian countries and United states should be leveraged on to boost exports as they present more opportunities due to their population size and export revenue volumes.
Also Q3 & Q4 presents significant opportunities to boost sales volumes as they are key performing financial quarters.