Executive Summary

Over the last six months, we analyzed BK’s performance on the food delivery platform against
major competitors. While BK held strong in revenue and volume, we saw spikes in incomplete
and refunded orders—especially in September and November. Competitors also outperformed
in delivery speed and customer satisfaction.

Key takeaways: longer online hours tend to boost orders, and market share shifts month to
month, pointing to the need for consistent service.

Next steps:

●​ Fix issues behind failed/refunded orders​

●​ Improve delivery times and completion rates​

●​ Boost customer satisfaction​

●​ Adjust store hours during peak times​

These changes can help BK stay competitive and grow steadily.

Create Refund Detail

Create Restaurant Details

Faced Some error dates so changed the dates from 2024-6-31 to 2024-6-30 etc.

Create Trip Detail

Encountered errors here as well, so the dates were corrected accordingly.

Note: All code snippets are included in this document and are labeled with
corresponding numbers and titles matching the section headings.

A) Brand-Wise Breakdown of Completed Order Percentages for last 6

months
Explanation-
This query calculates the monthly percentage of completed orders for each brand. Tracking this
helps spot fulfillment issues, seasonal trends, or operational gaps. It also allows brands to
benchmark performance, improve reliability, and boost customer satisfaction.
Query-
with cte as
(
​ select brand_name, Trim(TO_CHAR(datestr, 'Month')) as months,
​ round(count(case when is_completed is True then 1 end)*100.0/count(*),2) as
Percent_of_completed_orders
​ from trip_detail
​ where datestr>='2024-07-01' and datestr<='2024-12-31'
​ group by brand_name, Trim(TO_CHAR(datestr, 'Month'))
)
select brand_name, max(case when months='July' then Percent_of_completed_orders end) as
July,
max(case when months='August' then Percent_of_completed_orders end) as August,
​ max(case when months='September' then Percent_of_completed_orders end) as
September,
​ max(case when months='October' then Percent_of_completed_orders end) as
October,
​ max(case when months='November' then Percent_of_completed_orders end) as
November,
​ max(case when months='December' then Percent_of_completed_orders end) as
December
from cte
group by brand_name
order by brand_name;

B) Completed Orders: Monthly On-Time Delivery Percentage Across

Brands
(Assuming if food is delivered in less 30 minutes only then it’s on time)
Explanation- This query calculates the percentage of completed orders delivered within 30
minutes, by brand and month (July–December 2024). Pivoting the results allows easy
comparison across brands and months to track BK’s performance over time and how it
compares to other brands during the same period.

Query-
with cte as
(
​ select brand_name, Trim(TO_CHAR(datestr, 'Month')) as months,
​ round(count(case when delivery_time<=30 then 1 end)*100.0/count(*),2) as
Percent_of_on_time_delivery
​ from trip_detail
​ where datestr>='2024-07-01' and datestr<='2024-12-31'
​ and is_completed is True
​ group by brand_name, Trim(TO_CHAR(datestr, 'Month'))
)
select brand_name, max(case when months='July' then Percent_of_on_time_delivery end) as
July,
max(case when months='August' then Percent_of_on_time_delivery end) as August,
​ max(case when months='September' then Percent_of_on_time_delivery end) as
September,
​ max(case when months='October' then Percent_of_on_time_delivery end) as October,
​ max(case when months='November' then Percent_of_on_time_delivery end) as
November,
​ max(case when months='December' then Percent_of_on_time_delivery end) as
December
from cte
group by brand_name
order by brand_name;
C) Brand-wise Total Opening Hours and Total Orders
Explanation- This analysis helps understand the relationship between total operating hours and
order volume. It shows whether longer restaurant availability results in more customer orders
across different brands.
Query-
with cte as
(
​ select a.brand_name, a.restaurant_id, [Link], b.online_hours
​ from trip_detail as a
​ join restaurant_details as b
​ on a.restaurant_id=b.restaurant_id and [Link]=[Link]
),
cte2 as
(
​ select brand_name, restaurant_id, datestr, online_hours, count(*) as
total_orders_on_given_day
​ from cte
​ group by brand_name, restaurant_id, datestr, online_hours
),
cte3 as
(
​ select brand_name, restaurant_id, sum(online_hours) as total_hours_restaurant,
​ sum(total_orders_on_given_day) as total_restaurant_orders
​ from cte2
​ group by brand_name, restaurant_id
)
select brand_name, count(distinct restaurant_id) as num_restaurant,
sum(total_hours_restaurant) as total_opening_hours,
​ sum(total_restaurant_orders) as total_orders_brand
from cte3
group by brand_name;
D) Monthly Refund Orders by Brand
Explanation- This query calculates the total number of refund orders for each brand, broken
down by month.
Tracking the number of refund orders per brand each month helps identify potential service or
quality issues. A high or increasing refund trend may signal customer dissatisfaction,
operational inefficiencies, or delivery problems.
For example, we can analyze this and get an idea why our brand BK is experiencing high
returns specifically in September and November. By monitoring this metric monthly, brands can
take timely corrective actions, benchmark against competitors, and improve overall customer
experience.
Query-
with cte as
(
​ select distinct brand_name, restaurant_id
​ from trip_detail
),
cte2 as
(
​ select b.brand_name,a.restaurant_id, Trim(To_char([Link], 'Month')) as months,
​ [Link]
​ from refund_detail as a
​ join cte as b
​ on a.restaurant_id=b.restaurant_id
),
cte3 as
(
​ select brand_name, months, sum(refunds) as total_no_of_refunds
​ from cte2
​ group by brand_name, months
)
select brand_name, coalesce(max(case when months='July' then total_no_of_refunds end),0)
as July,
coalesce(max(case when months='August' then total_no_of_refunds end),0) as August,
​ coalesce(max(case when months='September' then total_no_of_refunds end),0) as
September,
​ coalesce(max(case when months='October' then total_no_of_refunds end),0) as
October,
​ coalesce(max(case when months='November' then total_no_of_refunds end),0) as
November,
​ coalesce(max(case when months='December' then total_no_of_refunds end),0) as
December
from cte3
group by brand_name
order by brand_name;
E) Total Monthly Revenue for Each Brand
Explanation-
This analysis helps us understand how much revenue each brand is generating
month-over-month. It gives clear visibility into seasonal trends, brand performance, and sales
momentum. For example, if Brand BK shows lower revenue in August compared to July, this
could prompt a review of marketing strategies, operational issues, or customer behavior during
that period.

Query-
with cte as
(
​ select brand_name, Trim(To_Char(datestr, 'Month')) as months,
sum(Cast(Replace(fare_usd, '$', '') AS INTEGER)) AS total_earnings
​ from trip_detail
​ where is_completed is True
​ group by brand_name, months
)
select brand_name, coalesce(max(case when months='July' then total_earnings end),0) as July,
coalesce(max(case when months='August' then total_earnings end),0) as August,
​ coalesce(max(case when months='September' then total_earnings end),0) as
September,
​ coalesce(max(case when months='October' then total_earnings end),0) as October,
​ coalesce(max(case when months='November' then total_earnings end),0) as
November,
​ coalesce(max(case when months='December' then total_earnings end),0) as
December
from cte
group by brand_name
order by brand_name;
F) Monthly Market Share (%) by Brand
Explanation-
This query calculates each brand's monthly market share based on completed order revenue
from July to December. It enables performance comparison by showing percentage
contributions that sum to 100% each month.

This analysis helps assess how much of the market each brand controls month-by-month, which
is essential for competitive benchmarking. Tracking market share trends can reveal gains or
losses in brand dominance, highlight successful campaigns, or signal potential issues.
Query-
with cte as
(
​ select brand_name, Trim(To_Char(datestr, 'Month')) as months,
sum(Cast(Replace(fare_usd, '$', '') AS INTEGER)) AS total_earnings
​ from trip_detail
​ where is_completed is True
​ group by brand_name, months
),
cte2 as
(
​ select months, sum(total_earnings) as months_total
​ from cte
​ group by months
),
cte3 as
(
​ select a.brand_name, [Link], round(a.total_earnings*100.0/b.months_total,2) as
market_share
​ from cte as a
​ join cte2 as b
​ on [Link]=[Link]
)
select brand_name, coalesce(max(case when months='July' then market_share end),0) as July,
coalesce(max(case when months='August' then market_share end),0) as August,
​ coalesce(max(case when months='September' then market_share end),0) as
September,
​ coalesce(max(case when months='October' then market_share end),0) as October,
​ coalesce(max(case when months='November' then market_share end),0) as
November,
​ coalesce(max(case when months='December' then market_share end),0) as
December
from cte3
group by brand_name;
G) Restaurant-Wise Breakdown of Incomplete Orders for BK Brand
Explanation-
This query identifies BK restaurants with the highest number of incomplete orders.
This analysis is useful for diagnosing operational inefficiencies at a granular level. By pinpointing
restaurants with high failure rates, BK can investigate potential root causes—such as staffing
issues, inventory shortages, technical problems, or delivery delays.
This metric is particularly valuable for retrospective analysis to address why certain restaurants
are consistently underperforming.
Query-
with cte as
(
​ select restaurant_id, count(case when is_completed is False then 1 end) as
incomplete_order
​ from trip_detail
​ where brand_name='BK'
​ group by restaurant_id
)
select restaurant_id, incomplete_order
from cte
order by incomplete_order desc;

H) Average Online Daily Hours for restaurants of each brand for each
month
Explanation- This query calculates the average online hours of restaurants for each brand,
broken down by month. It helps compare how long different brands keep their restaurants open
on average, giving insight into competitor activity across months.
Query-
with cte as
(
​ select a.brand_name, a.restaurant_id, b.online_hours, Trim(TO_CHAR([Link],
'Month')) as months
​ from trip_detail as a
​ join restaurant_details as b
​ on a.restaurant_id=b.restaurant_id and [Link]=[Link]
),
cte2 as
(
​ select brand_name, months, round(sum(online_hours)*1.0/count(*),2) as
avg_online_hours
​ from cte
​ group by brand_name, months
)
select brand_name,max(case when months='July' then avg_online_hours end) as July,
max(case when months='August' then avg_online_hours end) as August,
​ max(case when months='September' then avg_online_hours end) as September,
​ max(case when months='October' then avg_online_hours end) as October,
​ max(case when months='November' then avg_online_hours end) as November,
​ max(case when months='December' then avg_online_hours end) as December
from cte2
group by brand_name
order by brand_name;

I) Brand-wise % of Incomplete and Refunded Orders

Explanation- This metric helps evaluate service quality and customer satisfaction by showing
what percentage of a brand’s total orders were either incomplete or refunded. High percentages
may indicate operational issues or customer dissatisfaction, warranting further investigation.
Query-
with cte as
(
​ select distinct brand_name, restaurant_id
​ from trip_detail
),
cte2 as
(
​ select b.brand_name, sum([Link]) as total_refunds
​ from refund_detail as a
​ left join cte as b
​ on a.restaurant_id=b.restaurant_id
​ group by b.brand_name
),
cte3 as
(
​ select brand_name, count(*) as total_orders, count(case when is_completed is False
then 1 end) as total_incomplete_orders
​ from trip_detail
​ group by brand_name
)
select a.brand_name, round(a.total_incomplete_orders*100.0/a.total_orders,2) as
percent_incomplete_orders,
round(b.total_refunds*100.0/a.total_orders,2) as percent_refunded_orders
from cte3 as a
join cte2 as b
on a.brand_name=b.brand_name;