Project on Jumia Full Product Sales Insight Using Adidas and some other brands as a Case Study.

I am currently undergoing a very productive and world class bootcamp at side hustle and i dim it fit to build a project that focuses on A full product sales insight of a product used by Nigerians or produced by a Nigerian firm.

I achieved this following four main processes

1. Web scraping
2. Data cleaning
3. Data visualization
4. Database Creation

WEB SCRAPING AND DATA CLEANING USING PYTHON ON VISUAL STUDIO CODE

I used python programming language to scrape jumia website by following the processes below.

I used a text editor called visual studio code, which is a very versatile text editor. Since I aim at writing python codes on it, “I installed a python extension” in order to be able to run python codes on it.

1. I created a file called “Scrape.py”

2. Since I need some python libraries like Beautiful soup, Requests, Pandas and Openpyxl for a successful scrapping, so I created a new terminal using the command prompt to install the libraries by using the format “pip install bs4”, “Pip install Requests”, Pip install pandas, Pip install Openpyxl

3. I then went on to write the codes that will import the installed python libraries. (Please refer to the screenshots to see the codes)

4. I used the requests library to pass in the html source code from the jumia website

5. I also created a variable for our home url, I used “a for loop” to pick data one page at a go.

6. Using beautiful soup and requests let us loop through our urls and use get() from the request library to create a connection and read the url.

7. I used pandas to save the script as a csv format. Please kindly go through the codes in the screenshot, I have used the # attribute to give proper explanation to all our steps for easy understanding.

ANALYSIS ON PYTHON SCRAPED FILE USING MICROSOFT EXCEL

I opened the excel format of the file in Microsoft excel, then I did the following

1. I used ctrl + A to select everything and used ctrl + T to make everything turn into a table format for proper analysis

2. I then discovered that there are very many blank spaces in the data, hence I had to use Ctrl + G to fill the the blank spaces with “0”

3. I also applied some functions like conditional formatting, find and replace all with ctrl +f and then I used the lookup function to look up a particular brand “Adidas” for proper consideration.

4. I ensured the data had some proper level of cleaning in excel before doing more complex cleaning using power Query in power Bi.

DATA MODELLING WITH MICROSOFT EXCEL

I used excel to create some data modelling so that I can have a properly defined and ordered data for use and analysis. I also did this modeling so that I can produce high quality, consistent, structured data that will help the firm I am considering to run good business applications and achieve consistent results.

The steps I followed are below:

1. I used ctrl + shift + right arrow to highlight the data headers and ctrl + c to copy the headers, then I used al+ E + S which a short cut of transposing values to place the headers in a vertical order.

2. I Grouped the columns that are not “Numbers i.e. cannot be calculated” under value and every other one under descriptions.

3. I then group the common columns into one table, identified some columns that ought to stand alone, e.g. date etc. then I later referred to the values as “facts”

4. I then identified the primary keys. Primary keys are parameters that uniquely identifies values or arguments in a record or column or even a table, a primary key must be distinct and cannot be empty.

5. I then Fetched out two tables called “Categories” and “Orders” There is a particular column called “name” which is found in both tables, hence, it becomes the foreign key in the “Orders” table, its function is to help me maintain a good relationship between our tables during the process of analysis

LOADING THE DATA INTO POWER QUERY EDITOR FOR A FINAL CLEANING PROCESS

I opened and “used” the “Get Data” feature in the home tab of power Bi desktop to access my Jumia_Deals data set which was saved in an xlsx format in the documents folder.

after getting this data, i used the “transform” button to load it into the power query editor for cleaning processes.

1. I paid reference to the data modelling I have done in excel and “duplicated” the jumia_deals dataset twice.

2. I renamed the duplicated dataset with regards to my modelling in excel i.e. “name1: Categories table”, “name2: Orders table”

3. I then selected the needed columns in each table and used “remove other column” function in power Bi to remove the unnecessary columns.

4. I also ensured that I treated a brand for proper view and a very clear analysis insight.

5. After all these necessary steps I then used “close and apply all” feature in power query to load the data into “Power Bi Desktop”

CALENDAR TABLE CREATED

Since I have date column in our data, hence, it is very important for me to create a calendar table using dax formulas in Power bi. Hence, I used the function CALENDAR = CALENDARAUTO () TO CREATE A CALENDARS TABLE.

After creating the table, it is very paramount to mark the calendar table as a date table, hence, I used the “mark table” feature under “table tools” to mark it as a date table

Then, I saw it as an important task to extract the month, year and quarter from the calendar table so that my analysis can be date using time intelligence functions.

Note: I used the dax formula “Year=format (tablename,” yyyy”) to extract the year. Month=format (tablename, “mmm”) to extract the month and Quarter= format (tablename, “\QQ”) to extract the quarter.

MEASURES CREATED

I created some measures that will help me have an insightful analysis and conclusions that will affect the firm’s decisions positively

Some of the measure I created are as follows:

Total Categories using the dax formula =Countrows(table name)

Total Orders using the dax formula = Countrows(table name)

Total Sales Price using the dax formula = Countrows(table name)

PRE_MTH = CALCULATE([TOTAL_ORDERS], PREVIOUSMONTH('CALENDAR'[Date])) CENTRAL_ORDERS =

CALCULATE(ORDERS[TOTAL_ORDERS],ORDERS[gender]="Female")

ORDERS VS CENTRAL = [TOTAL_ORDERS] - [CENTRAL_ORDERS]

% Male = DIVIDE([Male],[TOTAL_ORDERS],0)

MODEL VIEW OF DATA TABLES PROPERLY FIXED

I looked into my Model view which is just below the data view at the extreme left of power bi desktop. I discovered that i needed to ensure that the tables are related or else it will affect my analysis on the visual view.

So, I connected the Orders Table to the Categories table by dragging “name column" from orders table to categories table. It is the Foreign key based on the modelling i did earlier on in excel.

I also dragged the date column from the Orders table to the Calendar table, hence, they are now all related.

With this properly done, i did not have any relationship issue in our Analysis

POWERBI DASHBOARD CREATED

This is a Jumia Full product sales insight dashboard Using Adidas as a case study

Firstly, I would like to say that I was very careful about not using too many colors, irrelevant colors, irrelevant charts or any how fonts, I was very specific and careful about crossing our t’s and dotting my I’s.

ANALYSIS :

1. I was able to show the Total Orders that was made in year 2022 and the month of march by Jumia Company.

1. I was able to also show the total Sales price of of the same year and month so that insightful recommendations can be made after analysis

2. I was able to show the total promotions, total categories (I got this by using a dax formula which is a measure)

3. I Was able to show The Sales Price count of date by category and by their distinct id’s using a “Scatter Chat” where the play axis is the id. On clicking on this play axis, it will begin to display in an orderly manner.

4. I was able to show Using a Matrix table, Sales Price by month and by the previous months. (This previous month was gotten using a dax formula in the measures as explained above under step 6)

5. I was able to also show the Sales Price by brand (Adidas) and Month using a Stacked bar chart.

6. I showed the Count of Price by Date using a KPI

7. Lastly I showed Sales Price by Gender(Male) using a donut chart.

RECOMMENDATIONS:

1. This analysis shows that not up to an average of male order Adidas product from Jumia, so i recommend that jumia reduces their price on a monthly basis to an affordable rate.

2. I recommend that Jumia should try to be consistent with their prices on a monthly basis, because looking at the price of the previous month compared to that of march there is a big difference and this can discourage customers.

3. I also recommend that jumia should increase their promotions as they reduce their price to a very affordable rate.

EXCEL DASHBOARD

This is a Jumia Full product sales insight dashboard Using Some brands as a case study

Firstly, I would like to say that I was very careful about not using too many colors, irrelevant colors, irrelevant charts or any how fonts, I was very specific and careful about crossing my t’s and dotting my I’s.

In order to build the Above, excel dashboard i took the following steps

1. I selected my dataset with ctrl + A and used ctrl + T to make it a table then i used alt + N + V to create a pivot table in a new worksheet

2. After creating this pivot table, I then put the needed fields into either values, rows or columns

3. Then I selected a pivot chart for each of the charts.

4. For each of the charts i created, I had to just copy and paste the first pivot table, change the fields, values and columns and choose a suitable chart.

ANALYSIS:

1. I was able to show the sales price by several categories and use a date slicer that is connected to only this chart.

2. I was able to show Sales price by Gender and promotion

3. I was also able to show the Sales Price before by only 5 brands that are commonly used by Nigerians

4. I was also able to show the sales price by the top 4 categories.

RECOMMENDATIONS:

1. I recommend that Jumia should try to be consistent with their prices on a monthly basis, because looking at the price of the previous month compared to that of march there is a big difference and this can discourage customers

I CREATED A DATABASE IN POSTGRESQL SO THAT WE CAN KEEP OUR DATA SAFE AND WRITE SOME NECESSARY QUERIES FOR FUTURE PURPOSES

Lastly I created a database(warehouse) using Postgres by writing some sql programming languages.

The procedures i followed :

1. I created a database in PostgreSQL using the sql statement “Create database database name” note: the database name is sidehustle

2. I then created a schema so that my projects can be properly arranged by using the sql statement “Create Schema project1”

3. I then created a table using sql statement (please look at the screenshot for this) note: the table name is team3

4. After that I went ahead to save my excel file in a csv format so that I can import it into sql.

5. Lastly I then right click on my already created table, clicked on import to bring in my table.

6. Finally, I wrote an sql statement that will show my imported table Select * from project1.team3

Note: * means all in sql.