Power BI Project: Food Delivery Case

Problem Statement

I am a food delivery man. I have data of food delivery log to investigate which delivery area I should be in when to maximize my pay. When should I work harder in a day to get more tips? Let’s see how it goes through data wrangling with Power BI.

Data sources

This is a flat table data. Let’s separate the table into fact and dimention tables to create a star-schema data model.

Dimention Table

• ASAP
• Date
• Delivery Area
• Drivery Name
• Delivery Lead Time (furnished)
• Delivery Timings (furnished)
• Customer
• Restaurant

Data modeling

• Change date data to integer format

1
2
3
4
5
6

NewCol = 
Text.Combine({
Date.ToText([date_id], "yyyy"),
Date.ToText([date_id], "MM"),
Date.ToText([date_id], "dd")
})

• Create dimention tables and its index and merge to the fact table.
• In the fact table, I added few custom conditional columns for “Delivery Timings” and “Delivery LeadTime Classification”.

1
2
3
4
5
6
7
8

orderplaced_delivered_class = if [sec_orderplaced_delivered] < 180 then -1 // 3 minutes
else if [sec_orderplaced_delivered] >= 180 and [sec_orderplaced_delivered] < 600 then 1
else if [sec_orderplaced_delivered] >= 600 and [sec_orderplaced_delivered] < 1200 then 2
else if [sec_orderplaced_delivered] >= 1200 and [sec_orderplaced_delivered] < 2400 then 3
else if [sec_orderplaced_delivered] >= 2400 and [sec_orderplaced_delivered] < 3600 then 4
else if [sec_orderplaced_delivered] >= 3600 and [sec_orderplaced_delivered] < 7200 then 5
else if [sec_orderplaced_delivered] >= 7200 and [sec_orderplaced_delivered] < 14400 then 6
else 7

1
2
3
4
5
6
7
8
9

orderplaced_class = if [time_customer_order_placed] >= #time(0,0,0) and [time_customer_order_placed] <  #time(3,0,0) then 1
else if [time_customer_order_placed] >= #time(3,0,0) and [time_customer_order_placed] <  #time(6,0,0) then 2
else if [time_customer_order_placed] >= #time(6,0,0) and [time_customer_order_placed] <  #time(9,0,0) then 3
else if [time_customer_order_placed] >= #time(9,0,0) and [time_customer_order_placed] <  #time(12,0,0) then 4
else if [time_customer_order_placed] >= #time(12,0,0) and [time_customer_order_placed] <  #time(15,0,0) then 5
else if [time_customer_order_placed] >= #time(15,0,0) and [time_customer_order_placed] <  #time(18,0,0) then 6
else if [time_customer_order_placed] >= #time(18,0,0) and [time_customer_order_placed] <  #time(21,0,0) then 7
else if [time_customer_order_placed] >= #time(21,0,0) and [time_customer_order_placed] <  #time(24,0,0) then 8
else -1

Measures

• count of deliveries
• sum of tip
• sum of delivery fee
• mean of delivery duration

Visualizations

Key takeaways

• Is “ASAP” delivery worth to take? - Yes, it would be more chance to get more tip than Not ASAP deliveries.
• Which area is the most frequent and higher paid? - The data is equally separated into area. Hence filtering by area does not give us any insights unfortunately.
• Which area is the most competitive? - As above, no effects.
• When should I work in a day? Any specific timing? - Late Night and Early Morning would be the best timing to work in terms of higher opportunities plus more tip. Also, it is worth to mention that there would be few differences in amount of tips excerpt Evening when its average tip amount is USD6.22 which is relatively 30% lower than other timings.
• Would a delivery timing affect an amount of tip received? Based on the filtered data, basically there would be no correlation. However, ensuring delivery leadtime within 1 hour should be a key as 50% of deliveries between “Late Night” and “Early Morning” are completed within the leadtime.
• Is there any timing that I should hit to differentiate from other competitors? - Lunch would be my opportunity as 25% drivers are less avaialble.