BACKGROUND

An e-commerce company from Brazil, has requested a complete analysis of their operating performance. They want a detailed report covering three main parts: general dashboard, delivery performance, and product quality. Based on this analysis report, the company’s management will decide the future development of the business.

The dataset contains information on 100k orders from 2016 to 2018 made at several marketplaces in Brazil. Various order data are recorded: from order status, price, payment, and freight performance to customer location and product attributes. Customer reviews and geolocation dataset relates Brazilian zip codes to latitude/longitude coordinates are also included.

It is actual commercial data that has been made anonymous; references to the companies and partners in the review text were replaced

TASKS

1. Build the overall dashboard. Investigate company’s product volume, sales and customer’s rating for products.
2. Explore delivery performance. Walk through delivery performance and find ways to optimize delivery times. Calculate DAX measures for seller’s late dispatch and carrier’s late delivery.
3. Investigate product quality. Discover product categories that are more prone to customer dissatisfaction. Calculate DAX measure for bad reviews (those less than 3) and build a time series visualization

OBJECTIVE

a. Developed a detailed Power BI report that would help an e-commerce company to decide for the future development of the business.

b. Create measure for business metrics using DAX formula.

METHODOLOGY

A. Data Extraction

1. Extract the datasets from the source. There are nine(9) csv files:

2. Load all the csv files in the Power BI.

B. Data Cleaning and Transformation

1. Using power query editor, check each tables and columns. Removed duplicates.

2. Check and transform each column for the correct data types. For example : Text format for zip code since it would not be used in calculation, currency for prices, etc.

3. Used google translate formula in google sheet to translate the Portuguese in the review_comment_message of the order_review_dataset:

C. Data Modeling

Since we have nine(9) data tables, we need to establish relationship between tables. I used snowflake schema data model in which dimension tables are normalized:

D. Data Analysis and Visualization

1. General Report

a. The four (4) business metrics on the general report (Total Orders, Total Sales, Total Freight, and Review Rating) were just dragged and dropped from the data pane to the visuals.

b. Bar chart. I decided to use total payment instead of total orders on the Top Product Category to see the actual amount distribution of R$16.01M total sale. The health beauty products ranked at number 1 with a total orders of 8,876, total sales of R$1.44M and average of 4.2 star rating.

If we used by total orders, the rank 1 would be Bed Bath Table with a total orders of 9,311 but with lesser total sales of R$1.24M and lesser average star rating of 4. Health beauty products is at ranked 2.

2. Logistic Report

2.1 Added calculated columns that would help on creating new measure for the logistics' business metrics:

a. order_processing_time :

b. ship_to_carrier:

c. ship_carrier_to_customer:

d. dispatch allowance:

e. dispatch status:

f. delivery status:

2.2 The five (5) business metrics on logistic report were obtained using the following new measures:

a. Average Order Processing Time (hour)

b. Average shipping to carrier (day)

c. Average shipping — carrier to customer (day)

d. No. of late dispatch by Seller

e. No. of late deliveries by carrier

3. Quality Report

On the Quality Report, I used bar chart to get the bottom 10 by star ratings and by total orders. Security and Services both ranked no. 1 at the bottom by star ratings and total orders.

E. Recommendation

Olist is actually doing a good operating performance based on the business metrics in general, logistic, and quality reports.

These are the things Olist can still improve:

1. Conduct a thorough investigation on all orders that were not dispatched by the seller and/or undelivered by the carrier. Every bad transaction might affect the reputation and integrity of the company.
2. Verify transactions wherein orders were delivered to the customers without the seller’s approval. This might be a loophole in the system.
3. Address the late dispatch and late delivery issues. Collect more data on what might causes this delay.
4. Replace or discontinue products that are both in the bottom 10 by ratings and by orders.
5. Try to create feedback forms that have questions on how to further improve the company’s products and services.

BACKGROUND

India Express is a food delivery company operating in India. They cater items that include entrees, sides, drinks, desserts, or grocery items
and are typically delivered in boxes or bags. The company's delivery drivers generally drive a car, but in bigger cities where homes and restaurants are closer together, they may use bikes or motorized scooters.

OBJECTIVES

A. To be able to create a report/dashboard that will reflect how vehicle type, type of order, weather conditions, and road traffic affect the average time taken for delivery.

B. To be able to add map into the dashboard according to the given dataset.

C. To be able to calculate the following measures and add them to the dashboard:

1. The median time that is taken for delivery.
2. Average time for delivery for sunny and stormy weather.
3. Difference between average delivery time on ordinary scooters and motorcycle.

D. As a data analyst, what would you suggest to the delivery company so
they can collect better data and get more precise results?

METHODOLOGY

A. Data extraction

1. Extract the dataset delivery_statistics.csv from the source.

2. Load delivery_statistics.csv into power bi.

B. Data Cleaning

1. Check and correct the data types for each column.
2. Remove errors and Not a Number (NaN) values. Please take note that I only removed NaN because this is just for practice. In real work scenario, the best approach is to verify and ask for guidance from the superior or person who is more knowledgeable on the given dataset. Do not delete what you do not know.

3. Delete columns that are not useful in the analysis.

4. Remove 0,0 coordinates in the latitude and longitude columns.

C. Data Analysis and Visualization

Create new measure to answer the business metrics in the objectives.

1. The median time that is taken for delivery.

2. Average time for delivery for sunny and stormy weather.

Using the average delivery time measure to create a table with weather condition values:

3. Difference between average delivery time on ordinary scooters and motorcycle.

Using the average delivery time measure again to create a table with vehicle type values:

For visualization I created a dashboard template using Microsoft power point and inserted it in the power bi canvas background.

added other KPIs:

Added map using the delivery latitude and longitude:

Added bar and column charts by City type and day of the week to observe the differences in delivery time .

Added some slicers for filters:

RECOMMENDATION

D. As a data analyst, what would you suggest to the delivery company so
they can collect better data and get more precise results?

During the data cleaning process, I noticed that there are many “Not a Number” (NaN) values. I removed all of them, however in real work scenario, without adept knowledge on the given dataset, this could affect the accuracy and integrity of the data that might result into wrong insights.

Assuming that this delivery company already has their delivery apps and working database system on which where they collected all these raw data.

a. I highly recommend to conduct an investigation on why these NaN were generated on their database. It might be their delivery app that is causing these NaN values.

b. Instead of NaN , use more meaningful word such as “No Data”, “Not Applicable”, etc.

c. Lessen the frequency of manual input of the information / details from the customer. It is better if there is already available choices on the app. Include words like “Prefer not to answer”, “Not applicable” , this is better than a blank data.

d. Some information that could be added are service fee, profit margin, profit, distances, etc. in order to have insights on other perspective like financial.

e. Hire a data analyst that would manage the company’s growing data.

INTRODUCTION

For the past few years , Yellevate has been struggling with client disputes. Yellevate defines disputes as clients expressing dissatisfaction with the company’s services and refusing to pay for them.

This has been a huge financial burden for the company: statistically, nearly 20% of the disputes raised against Yellevate resulted in a payment opt-out.

DATA ANALYSIS GOALS

Yellevate’s executives decided that our team need to obtain the following information to identify the circumstances around the dispute problem:

A. The processing time in which invoices are settled.

B. The processing time for the company to settle disputes.

C. Percentage of disputes received by the company that were lost.

D. Percentage of revenue lost from disputes.

E. The country where the company reached the highest losses from lost disputes (in USD).

IMPORTANT ASSUMPTIONS:

1. All the services Yellevate was hired to do were fully completed.
2. The quality of the services provided is not the main driving reason for these invoice disputes.…
3. Yellevate management believes most disputes are the result of contract technicalities or clients thinking they can get away with not paying for the services.

These important assumptions will guide us in developing insights and recommendations.

COMPANY OVERVIEW

Let’s start with knowing first about the company Yellevate?

Here are some of the facts about Yellevate:

PROBLEM OVERVIEW & ANALYSIS OBJECTIVES

Data Analysis always starts with defining the problem/s and establishing the objective/s. This would help us to frame the problem the right way and to determine which sources of data will best help to solve it.

DATA PREPARATION

Data preparation is the process of gathering, cleaning and transforming raw data prior to processing and analysis. This is often involve a series of tasks in order to eliminate errors, inaccuracies, and other data problems that might affect the quality and integrity of the data.

A. Data Collection

1. Extract the required dataset Yellevate Invoices.csv from the google drive.

2. Create a Yellevate_invoices table in PostgreSQL database with columns same as Yellevate Invoices.csv using the following SQL query:

3. Import the Yellevate Invoices.csv into the table that was created earlier.

Right click the yellevate_invoices table on the left pane and choose Import/Export Data.

B. Data Cleaning

Data cleaning is essential in every process of data analysis. This is to set data for accurate calculations.

1. Check if all the columns were properly imported and with correct column names. Always verify the number of columns and rows for consistency.

1. Remove or modify data that is incorrect, incomplete, irrelevant, duplicated, or improperly formatted.

The following were some of the SQL queries used:

DATA ANALYSIS

In this stage, we need to answer first the needed information by the Yellevate’s executives to identify the circumstances around the dispute problem. SQL or Excel functions can be used to solve this.

A. The processing time in which invoices are settled. (Average # of days rounded to whole 2 numbers).

SQL:

Excel:

Formula: =Average(days_to_settle column) = 26 days

Findings : The processing time is settled 4 days earlier than the due date (30 days) . This means that most of the services rendered by Yellevate were successfully delivered and most customers are satisfied with them.

B. The processing time for the company to settle disputes. (Average # of days rounded to whole 2 numbers).

SQL:

Excel:

Formula: =Average(days_to_settle column filtered to disputed transactions only) = 36 day

Findings : Disputed transactions usually take longer to be settled as both parties are compromised. This means that in case the customer is not satisfied with the service rendered by Yellevate, it cost the company an average of 6 days longer than the due date (30 days) to settle the dispute.

C. Percentage of disputes received by the company that were lost. (Within two decimal places).

SQL:

Excel:

Formula: =101(#lost disputes) / 571(total # of disputes) = 17.69%

Findings: 17.69% were lost disputes received by the company. This is the result of the total number of lost dispute over the total number of disputes received by the company.

D. Percentage of revenue lost from disputes.(Within two decimal places).

SQL:

Excel:

Formula: = 690,167 (total amount lost) / 14,770,318 (total amount of invoices) = 4.67

Findings: 4.67% of lost revenue from disputes. This is computed by getting the total amount paid with those of lost disputes over the total amount paid for all the transactions.

E. The country where the company reached the highest losses from lost disputes (in USD).

SQL:

Excel:

Using pivot table and chart.

Findings: Based on the data given and analysis, France has the highest losses from lost disputes with $526,264.00. French clients won 76 of their 222 disputes accounted for 76.25% ($526,264.00 / $690,167.00) of the revenue loss for Yellevate.

OTHER FINDINGS

While we now know that France is where Yellevate got the highest loss, it also contributed as the 2nd highest revenue, next to China.

Now we need to check if there is something “special” about their French clients. Is there a probability that they discovered some loophole in the service contract with Yellevates?

The team used the IQR method of identifying outliers to set up a “fence” outside of Q1 and Q3. Any values that fall outside of this fence are considered outliers.

The team observed the outlier region based on the data. Statistically, the disputes received by the company are ranging from 1 to 8. As seen from the chart, there are 8 clients (each has more than 30% winning rate against Yellevate) all from France that accounted for more than 60% of the total lost revenue from disputes.

RECOMMENDATIONS

Dispute is a part of consumers right, however it should not be abused in order to take advantage of the other party. The team recommends the following: