Part 1: Connecting SQL to R studio and import Database

library(RODBC)
library(tidyverse)
library(knitr)
library(kableExtra)
library(formattable)
library(sqldf)
library(ggplot2)

After Loading the database I had to make some manipulations on the query from SQL so R will except the columns’ formatting. Here are the outputs visualize in a table.

In the following table I loaded only 15 rows of the dataset which their time period between the Order Date and the Shipped date (TPOSD) is above 8 days just for demonstration. Inside the table I have marked all the orders which their time period between the orders date and day of delivery above 20 marked in yellow and above 27 days marked in red.

Just from random rows in the data it is possible to understand that there are few orders which are inside the delivery limitations zone (8 to 32 days).

Part 2: Getting first glance on the data by diagrams

Now I’m going to use all the dataset and demonstrate in a bar histogram chart and scatter plot diagram to get a better view of the situation, to find where are the most Orders are placed based on the time past till their delivery. I grouped the days till delivery as follow: “0-8”, “9-16”, “17-23”, “24+”.

From the bar histogram chart we can understand that most of the Orders are between 0 to 8 days, which is a good sign. But it is also possible to see that the last group (“24+”) little bit higher than the group next to it. This group is the most concern and need to be tracked and optimize so it would be as less as possible.

The following scatter plots chart represent the same data but in a different way. Each dot represent number of orders which they are in the same TPOSD. The more it goes left and up in the chart the better it is. the most top left dot represent a group of more than 100 orders which their TPOSD is around 5 days. the second highest dot placed between 80 - 100 orders and TPOSD around 5 days.

Part 3: Pareto 80 - 20

In this calculation I’m trying to show how many Orders refer to be the core of all the orders in total. In other words what is the amount of orders which their ratio equal to 80% of all the orders, and their TPOSD is less as possible.

The following table present that 665 Orders are evaluate to 82% of all the orders and they rate between 1 to 10 TPOSD.