Order Fullfilment Efficiency Analysis

Order Fulfillment Efficiency

16. What is the average time gap between order_approved_at and order_delivered_carrier_date for each seller? Who are the ‘Bottom 10%’ of sellers who consistently take the longest to hand over packaged orders to the logistics partner?

Solution:

1. Firstly, we calculate the dispatch time per order (seller_lead_times CTE): We join the orders and order_items tables to calculate the exact dispatch_hours for each order by finding the difference between order_approved_at and order_delivered_carrier_date. We use minutes divided by 60.0 to safely prevent integer overflow errors, and filter out ‘canceled’ or ‘unavailable’ orders.

2. Secondly, we aggregate and filter seller performance (seller_performance_summary CTE): Grouping by seller_id, we calculate the avg_dispatch_hours and the total number of orders fulfilled. Crucially, we filter out negative time values (potential data entry errors) and use a HAVING clause to only include sellers with at least 5 orders, ensuring our averages are statistically relevant.

3. Thirdly, we segment sellers into deciles (seller_efficiency_ranks CTE): We use the ntile(10) window function ordered by avg_dispatch_hours desc. This divides the sellers into 10 equal buckets, placing the absolute slowest 10% of sellers into bucket 1.

4. Finally, we isolate the worst offenders (Final SELECT): We filter the results to only include efficiency_decile = 1 (the bottom 10%). We format the output to show both the average dispatch hours and days, highlighting the sellers who consistently take the longest to hand over packages to the logistics partner.

with seller_lead_times as (
    -- step 1: calculate hours between approval and carrier handover
    -- FIX: Changed 'second' to 'minute' to prevent integer overflow from data anomalies
    select 
        oi.seller_id,
        o.order_id,
        dispatch_hours = datediff(minute, o.order_approved_at, o.order_delivered_carrier_date) / 60.0
    from 
        orders o
    inner join 
        order_items oi on o.order_id = oi.order_id
    where 
        o.order_status not in ('canceled', 'unavailable')
        and o.order_approved_at is not null
        and o.order_delivered_carrier_date is not null
),

seller_performance_summary as (
    -- step 2: aggregate averages per seller
    select 
        seller_id,
        count(distinct order_id) as total_orders_fulfilled,
        avg(dispatch_hours) as avg_dispatch_hours
    from 
        seller_lead_times
    where 
        dispatch_hours >= 0 -- filtering out potential data entry errors
    group by 
        seller_id
    having 
        count(distinct order_id) >= 5 -- filtering for sellers with enough history to be statistically relevant
),

seller_efficiency_ranks as (
    -- step 3: segment sellers into deciles based on speed
    -- ntile(10) with order by desc puts the slowest 10% into bucket 1
    select 
        *,
        efficiency_decile = ntile(10) over (order by avg_dispatch_hours desc)
    from 
        seller_performance_summary
)

-- final output: isolating the "bottom 10%" slowest sellers
select 
    seller_id,
    total_orders_fulfilled,
    cast(avg_dispatch_hours as decimal(10,2)) as avg_dispatch_hours,
    cast(avg_dispatch_hours / 24.0 as decimal(10,2)) as avg_dispatch_days
from 
    seller_efficiency_ranks
where 
    efficiency_decile = 1 -- selecting the slowest decile
order by 
    avg_dispatch_hours desc;

Results:

Insight:

• Extreme Outliers: The absolute slowest seller (ecccfa...) takes an average of 25.9 days just to hand over the package to the carrier! However, the worst offenders at the very top of the list mostly have low order volumes (between 5 and 14 orders), suggesting they might be inactive, poorly managed, or drop-shipping from overseas.
• The High-Volume Red Flag: Seller 7c67e144... is a massive problem for the platform. They fulfilled 980 orders but have an incredibly slow average dispatch time of 11.6 days. Because of their high volume, this single seller is likely generating a huge amount of late deliveries and negative customer reviews.
• Unacceptable Baselines: In modern e-commerce, dispatching an item should take 1 to 2 days. The sellers on this “Bottom” list are consistently taking over a week (7+ days) just to get the product out of their warehouse. This indicates a severe supply chain or inventory management issue on the seller’s side.
• Actionable Next Step: Olist needs to enforce strict Service Level Agreements (SLAs). Sellers like 7c67e144... should be penalized or warned, as they are large enough to seriously damage Olist’s brand reputation with their 11-day packing delays.

Pareto Analysis for Sellers

17. Do the top 10% of sellers generate more than 50% of the platform’s total revenue? Furthermore, which product categories are dominated by a single ‘Monopoly Seller’ vs. categories with a healthy, competitive mix of multiple vendors?

Solution:

1. Firstly, we calculate global seller revenue (seller_global_revenue and global_pareto CTEs): We join the order_items, payments, and orders tables to sum up the total revenue for each seller across the entire platform. We then use the ntile(10) window function to rank sellers into deciles, which helps identify the platform-wide revenue concentration.

2. Secondly, we calculate seller market share per category (category_seller_share CTE): We group the sales data by both product_category_name and seller_id to find how much revenue each seller generated in that specific category. We also use a window function sum() over (partition by product_category_name) to calculate the total overall revenue for the entire category without grouping away the seller-level details.

3. Thirdly, we identify the top seller’s dominance (category_dominance CTE): Grouping strictly by category, we count the total number of unique vendors (total_vendors) and find the highest revenue generated by a single seller (max(seller_category_revenue)). By dividing this top seller’s revenue by the total category revenue, we calculate the exact market share percentage held by the leading vendor (leader_market_share_pct).

4. Finally, we classify the competitive health of each market (Final SELECT): We use a CASE statement to evaluate the market conditions. Categories where a single seller holds over 50% market share alongside fewer than 5 total vendors are flagged as ‘Monopoly / Highly Concentrated’, while categories with a leading share under 15% and over 20 vendors are labeled as ‘Healthy / Fragmented Competition’.

with seller_global_revenue as (
    -- step 1: calculate total revenue for every seller across the platform
    select 
        oi.seller_id,
        sum(p.payment_value) as total_seller_revenue
    from 
        order_items oi
    inner join 
        payments p on oi.order_id = p.order_id
    inner join 
        orders o on oi.order_id = o.order_id
    where 
        o.order_status not in ('canceled', 'unavailable')
    group by 
        oi.seller_id
),

global_pareto as (
    -- step 2: rank sellers into deciles to check platform-wide concentration
    select 
        seller_id,
        total_seller_revenue,
        ntile(10) over (order by total_seller_revenue desc) as seller_decile,
        sum(total_seller_revenue) over () as platform_total_revenue
    from 
        seller_global_revenue
),

category_seller_share as (
    -- step 3: calculate revenue share for each seller within their specific category
    select 
        prod.product_category_name,
        oi.seller_id,
        sum(p.payment_value) as seller_category_revenue,
        sum(sum(p.payment_value)) over (partition by prod.product_category_name) as total_category_revenue
    from 
        order_items oi
    inner join 
        products prod on oi.product_id = prod.product_id
    inner join 
        payments p on oi.order_id = p.order_id
    inner join 
        orders o on oi.order_id = o.order_id
    where 
        o.order_status not in ('canceled', 'unavailable')
        and prod.product_category_name is not null
    group by 
        prod.product_category_name, oi.seller_id
),

category_dominance as (
    -- step 4: identify the share of the 'top seller' in each category
    select 
        product_category_name,
        count(distinct seller_id) as total_vendors,
        max(seller_category_revenue) as top_seller_revenue,
        total_category_revenue,
        -- market share of the leading seller
        cast(100.0 * max(seller_category_revenue) / total_category_revenue as decimal(5,2)) as leader_market_share_pct
    from 
        category_seller_share
    group by 
        product_category_name, total_category_revenue
)

-- final output: categorizing product categories by competitive health
select 
    product_category_name,
    total_vendors,
    leader_market_share_pct,
    case 
        when leader_market_share_pct >= 50 and total_vendors < 5 then 'Monopoly / Highly Concentrated'
        when leader_market_share_pct >= 30 then 'Dominant Leader'
        when leader_market_share_pct < 15 and total_vendors > 20 then 'Healthy / Fragmented Competition'
        else 'Moderate Competition'
    end as market_health_status
from 
    category_dominance
order by 
    leader_market_share_pct desc;

Results:

Insights:

• True Monopolies in Niche Markets: Very small categories like cds_dvds_musicais and la_cuisine are completely run by 1 or 2 sellers holding 95% to 100% of the market share. This is expected for low-demand or highly specialized items.
• The Illusion of Competition: This is a major finding. Categories like moveis_escritorio (office furniture) look competitive on paper because they have 34 vendors. However, one single seller controls almost 73% of the market. We see the exact same thing in telefonia_fixa (landline phones), where the top seller holds 70% despite having 48 competitors.
• Healthy Core Markets: The best news for Olist is that its top-earning categories are heavily fragmented. Beleza_saude (health & beauty) and esporte_lazer (sports & leisure) each have nearly 500 active vendors, and the top seller only controls about 5% of the sales. This means the platform’s core revenue is highly secure.
• Single Point of Failure (Risk): Any category tagged as “Dominant Leader” carries a huge supply chain risk. If the top seller in pcs (67% share) or moveis_escritorio leaves the platform or starts delivering late, Olist’s revenue and reputation in that entire category will instantly tank.
• The Pareto Principle in Action: While the table focuses on category breakdowns rather than the overall platform revenue, it perfectly illustrates the 80/20 rule. Across dozens of categories, a single vendor is doing the heavy lifting and generating the vast majority of the sales volume.

Seller Churn Analysis

18. What is the churn rate of sellers on the platform? Specifically, how many sellers who made a sale in 2017 became inactive (zero sales) in 2018? Is there a correlation between high ‘Order Cancellation Rates’ and subsequent seller churn?

Solution:

1. Firstly, we aggregate annual activity per seller (seller_activity CTE): We join the orders and order_items tables to calculate each seller’s total_orders and canceled_orders grouped by the specific year they occurred (extracted from the timestamp using the year() function).

2. Secondly, we build the 2017 cohort and identify churn (seller_cohort_2017 CTE): We isolate sellers who were active in 2017 and perform a LEFT JOIN against their own 2018 activity data. If the 2018 record is missing (s18.seller_id is null), we classify them as ‘Churned (Inactive in 2018)’, otherwise they are ‘Retained’. We also compute their individual 2017 cancellation rate percentage here.

3. Finally, we aggregate metrics by churn status (Final SELECT): We group the cohort by their churn_status to count the exact number of sellers that stayed versus left. By averaging the cancellation_rate_2017 and the orders_2017 volume for both groups, we can directly observe the correlation between poor fulfillment performance (high cancellations) and subsequent seller churn on the platform.

with seller_activity as (
    -- step 1: identify activity years and cancellation counts for every seller
    select 
        oi.seller_id,
        year(o.order_purchase_timestamp) as activity_year,
        count(distinct o.order_id) as total_orders,
        sum(case when o.order_status = 'canceled' then 1 else 0 end) as canceled_orders
    from 
        orders o
    inner join 
        order_items oi on o.order_id = oi.order_id
    group by 
        oi.seller_id, year(o.order_purchase_timestamp)
),

seller_cohort_2017 as (
    -- step 2: isolate sellers who were active in 2017 and track their 2018 status
    select 
        s17.seller_id,
        s17.total_orders as orders_2017,
        cast(100.0 * s17.canceled_orders / s17.total_orders as decimal(5,2)) as cancellation_rate_2017,
        case 
            when s18.seller_id is null then 'Churned (Inactive in 2018)'
            else 'Retained (Active in 2018)'
        end as churn_status
    from 
        seller_activity s17
    left join 
        seller_activity s18 on s17.seller_id = s18.seller_id and s18.activity_year = 2018
    where 
        s17.activity_year = 2017
)

-- final output: comparing cancellation rates across churned vs retained sellers
select 
    churn_status,
    count(seller_id) as seller_count,
    -- average cancellation rate to identify the correlation
    cast(avg(cancellation_rate_2017) as decimal(5,2)) as avg_cancellation_rate_pct,
    -- average volume to see if smaller or larger sellers churn more
    cast(avg(orders_2017 * 1.0) as decimal(10,2)) as avg_2017_order_volume
from 
    seller_cohort_2017
group by 
    churn_status;

Results:

Insights:

• Massive Drop-Off Rate: A huge chunk of the seller base did not survive into 2018. Out of 1,784 sellers who made sales in 2017, 685 of them (around 38%) completely stopped operating the following year.
• Low Traction Means High Flight Risk: The sellers who churned never really gained momentum. They averaged only 6.34 orders for the entirety of 2017. In contrast, the retained sellers were moving roughly 37 orders a year. If a vendor doesn’t see quick sales, they abandon the platform.
• The Cancellation Red Flag: There is a massive, direct correlation between failing to fulfill orders and leaving the platform. The churned sellers had an average cancellation rate of 6.06%, which is over 12 times worse than the healthy, retained sellers (who kept cancellations under 0.5%).
• Profile of a Failing Seller: The data paints a very clear picture. The typical seller who leaves Olist is a low-volume vendor who struggles to actually deliver the few orders they get. This poor performance likely leads to account suspension or them simply giving up.

Identifying Underperforming Sellers

Solution:

1. Firstly, we analyze the seller churn correlation (Part 1): We aggregate annual activity and cancellation rates per seller (seller_activity CTE), then isolate the 2017 cohort to track whether they were retained or churned in 2018 (seller_cohort_2017 CTE). The first output aggregates the average cancellation rates and order volumes for churned versus retained sellers.

2. Secondly, we aggregate review statistics per seller (Part 2 - seller_review_stats CTE): To evaluate seller quality, we join the order_items, orders, and order_reviews tables. Grouping strictly by seller_id, we calculate their total_orders, their avg_review_score, and calculate their specific one_star_count.

3. Thirdly, we calculate the platform-wide baseline (Part 2 - platform_total_one_stars CTE): We independently calculate the grand_total_one_stars across the entire database. This acts as the denominator for measuring an individual seller’s negative impact against the whole platform.

4. Finally, we identify the worst offenders and their platform impact (Part 2 Final SELECT): We CROSS JOIN the seller stats with the platform baseline. We filter for high-volume sellers (total_orders > 50) with poor ratings (avg_review_score < 3). By computing pct_of_platform_one_stars, we can pinpoint exactly how much of the platform’s total negative feedback is disproportionately driven by these specific vendors.

with seller_activity as (
    -- step 1: identify activity years and cancellation counts for every seller
    select 
        oi.seller_id,
        year(o.order_purchase_timestamp) as activity_year,
        count(distinct o.order_id) as total_orders,
        sum(case when o.order_status = 'canceled' then 1 else 0 end) as canceled_orders
    from 
        orders o
    inner join 
        order_items oi on o.order_id = oi.order_id
    group by 
        oi.seller_id, year(o.order_purchase_timestamp)
),

seller_cohort_2017 as (
    -- step 2: isolate sellers who were active in 2017 and track their 2018 status
    select 
        s17.seller_id,
        s17.total_orders as orders_2017,
        cast(100.0 * s17.canceled_orders / s17.total_orders as decimal(5,2)) as cancellation_rate_2017,
        case 
            when s18.seller_id is null then 'Churned (Inactive in 2018)'
            else 'Retained (Active in 2018)'
        end as churn_status
    from 
        seller_activity s17
    left join 
        seller_activity s18 on s17.seller_id = s18.seller_id and s18.activity_year = 2018
    where 
        s17.activity_year = 2017
)

-- output 1: comparing cancellation rates across churned vs retained sellers
select 
    churn_status,
    count(seller_id) as seller_count,
    cast(avg(cancellation_rate_2017) as decimal(5,2)) as avg_cancellation_rate_pct,
    cast(avg(orders_2017 * 1.0) as decimal(10,2)) as avg_2017_order_volume
from 
    seller_cohort_2017
group by 
    churn_status;


/* PART 2: Seller Review Performance & 1-Star Concentration
    Objective: 
        1. Identify high-volume sellers (>50 orders) with poor ratings (<3 stars).
        2. Calculate their contribution to the platform's total 1-star reviews.
*/

with seller_review_stats as (
    -- step 1: aggregate review scores and total 1-stars per seller
    select 
        oi.seller_id,
        count(distinct o.order_id) as total_orders,
        avg(orv.review_score * 1.0) as avg_review_score,
        sum(case when orv.review_score = 1 then 1 else 0 end) as one_star_count
    from 
        order_items oi
    inner join 
        orders o on oi.order_id = o.order_id
    inner join 
        order_reviews orv on o.order_id = orv.order_id
    group by 
        oi.seller_id
),

platform_total_one_stars as (
    -- step 2: get the total number of 1-star reviews across the whole platform
    select sum(case when review_score = 1 then 1 else 0 end) as grand_total_one_stars
    from order_reviews
)

-- output 2: flagging high-risk sellers based on volume and negative sentiment
select 
    s.seller_id,
    s.total_orders,
    cast(s.avg_review_score as decimal(3,2)) as avg_review_score,
    s.one_star_count,
    -- concentration: what % of the entire platform's 1-star reviews come from this one seller?
    cast(100.0 * s.one_star_count / p.grand_total_one_stars as decimal(5,2)) as pct_of_platform_one_stars
from 
    seller_review_stats s
cross join 
    platform_total_one_stars p
where 
    s.total_orders > 50 
    and s.avg_review_score < 3
order by 
    s.one_star_count desc;

Results 1:

Results 2:

Insights:

• The Worst Offenders: We isolated five specific sellers who process a solid volume of orders (between 74 and 198) but consistently fail their customers, keeping their average rating below 3 stars.
• Disproportionate Damage: The top two sellers on this list (2eb702... and 1ca707...) are incredibly toxic to the platform. Together, these two vendors are responsible for 1.41% of all 1-star reviews generated across the entire Olist marketplace.
• High Volume, High Failure Rate: Seller 1ca7077d... is the most concerning data point. They fulfilled 114 orders and racked up 80 one-star reviews. This suggests that a massive majority of their customers had a terrible experience, dragging their average score down to an abysmal 2.20.
• Reputation Risk at Scale: Sellers hovering around the 2.9 score (a499..., 5496..., 972d...) might not look as disastrous at first glance, but because they process 70 to 100 orders, they are consistently delivering bad experiences to a large group of people.
• Actionable Next Step: These vendors are actively hurting the Olist brand. The platform should immediately suspend or heavily penalize these top 5 sellers to protect future buyers.

Conclusion

What We Did:

• Calculated the average dispatch times for sellers to identify the worst bottlenecks in the fulfillment process.
• Performed a Pareto analysis on market share to see which categories are dominated by monopolies versus those with healthy competition.
• Analyzed seller churn rates between 2017 and 2018, comparing the performance metrics of retained versus inactive vendors.
• Isolated high-volume, low-rated sellers to measure their specific impact on the platform’s overall 1-star review count.

What We Obtained (Key Findings):

• Severe Dispatch Delays: While some sellers operate efficiently, the bottom tier is completely failing. We identified high-volume vendors taking over 11 days just to pack an item, which creates a massive bottleneck.
• The “Dominant Leader” Risk: Many categories look competitive but are actually carried by a single vendor holding up to 70% of the market. This creates a huge supply chain risk if that seller leaves. Luckily, our biggest revenue categories (like health & beauty) are highly fragmented and safe.
• Failure Drives Churn: About 38% of 2017 sellers quit in 2018. The data shows they didn’t just leave; they failed out. Churned sellers averaged only 6 orders a year and had a cancellation rate 12 times higher than successful, active sellers.
• A Few Bad Apples: We pinpointed a tiny group of sellers who are actively damaging the platform’s reputation. Just two specific vendors generated over 1.4% of all 1-star reviews on Olist, proving that strict seller quality enforcement is urgently needed.