Order and Sales Performance Analysis

with monthly_sales as (
    -- step 1: aggregate core metrics by month
    -- we use the calendar table to ensure clean date grouping
    select 
        c.year,
        c.month_number,
        c.month_name,
        count(distinct o.order_id) as total_orders,
        sum(p.payment_value) as total_revenue
    from 
        orders o
    inner join 
        payments p on o.order_id = p.order_id
    inner join 
        Calendar c on o.purchase_datekey = c.datekey
    where 
        o.order_status not in ('canceled', 'unavailable')
    group by 
        c.year, c.month_number, c.month_name
),

growth_metrics as (
    -- step 2: use lag() to fetch values from the previous month and previous year
    select 
        *,
        -- previous month values
        prev_month_orders = lag(total_orders) over (order by year, month_number),
        prev_month_revenue = lag(total_revenue) over (order by year, month_number),
        
        -- previous year values (offset of 12 rows)
        prev_year_orders = lag(total_orders, 12) over (order by year, month_number),
        prev_year_revenue = lag(total_revenue, 12) over (order by year, month_number)
    from 
        monthly_sales
)

-- final output: calculating percentage growth
select 
    year,
    month_name,
    total_orders,
    total_revenue,
    
    -- month-over-month (mom) growth
    mom_revenue_growth_pct = cast(100.0 * (total_revenue - prev_month_revenue) / nullif(prev_month_revenue, 0) as decimal(10,2)),
    
    -- year-over-year (yoy) growth
    yoy_revenue_growth_pct = cast(100.0 * (total_revenue - prev_year_revenue) / nullif(prev_year_revenue, 0) as decimal(10,2)),
    
    -- identifying seasonal spikes
    case 
        when month_number = 11 then 'Black Friday Period'
        when month_number = 12 then 'Holiday Season'
        when month_number = 1 then 'New Year Peak'
        else 'Standard Period'
    end as seasonality_tag
from 
    growth_metrics
order by 
    year, month_number;
go

with category_metrics as  (
    select 
        products.product_category_name,
        count(order_items.order_item_id) as  total_volume,
        sum(order_items.price) as total_revenue       
    from 
        order_items
    inner join 
        products on products.product_id = order_items.product_id
    inner join 
        orders on orders.order_id = order_items.order_id
    where
        orders.order_status not in ('canceled', 'unavailable')
            and products.product_category_name is not null 
    group by 
        products.product_category_name
), 

category_rankings as (
    select
        *,
        percent_rank() over (order by total_volume desc) as volume_percentile,
        percent_rank() over (order by total_revenue desc) as revenue_percentile
    from  
        category_metrics
)

select
    product_category_name,
    total_volume,
    total_revenue,
    cast( total_revenue / total_volume as decimal(10,2)) as avg_unit_price,
    case
        when volume_percentile <= 0.20 and revenue_percentile <= 0.20 then 'High Vol / High Rev'
        when volume_percentile <= 0.20 and revenue_percentile > 0.20 then 'High Vol / Low Rev'
        when volume_percentile > 0.20 and revenue_percentile <= 0.20 then 'Low Vol / High Rev'
        else 'Standard'
    end as category_segmentation
from 
    category_rankings
order by 
    total_revenue desc;

with monthly_revenue as (
    select
        Calendar.year, 
        Calendar.month_number,
        Calendar.month_name,
        sum(payments.payment_value) as raw_monthly_revenue
    from 
        orders 
    inner join 
        payments on payments.order_id = orders.order_id 
    inner join 
        Calendar on Calendar.datekey = orders.purchase_datekey
    where 
        orders.order_status not in ('canceled', 'unavailable')
    group by 
        Calendar.year, calendar.month_number, Calendar.month_name
)

select 
    [year], 
    month_number,
    month_name,
    raw_monthly_revenue,

    -- average the current month and 2 preceding (last) months
    cast (avg(raw_monthly_revenue) over (order by [year], month_number 
            rows between 2 preceding and current row) as decimal(10,3)) as rolling_avg_3m,

    -- calculate deviation from the trend = (current_month - rolling_avg_3m) / current_month * 100.0 = var_dev %
    cast(100.0 * (raw_monthly_revenue - avg(raw_monthly_revenue) over (order by [year], month_number 
            rows between 2 preceding and current row)) / nullif(raw_monthly_revenue,0) as decimal (10,2)) as variance_from_trend
from 
    monthly_revenue
group by 
    [year], 
    month_number, 
    month_name,
    raw_monthly_revenue
order by
    [year], month_number;

Results:

Insights:

• A Clearer Picture of Growth: The 3-month rolling average successfully smooths out the messy monthly jumps. It reveals a very clean, consistent upward curve throughout 2017 before settling into a steady, flat line in 2018.
• Isolating the Black Friday Boom: In November 2017, raw revenue jumped 24.5% higher than the rolling trendline. This confirms that the Black Friday success was a temporary seasonal spike rather than a permanent shift in baseline sales.
• The Post-Holiday Drop: December 2017 shows an 8.2% negative variance from the trend. Because November pulled the 3-month average so high, this negative variance perfectly illustrates the sudden “hangover” effect as customer spending dropped off.
• Highly Predictable 2018: Between February and August 2018, the raw revenue tracks incredibly closely to the 3-month average. The variance is tiny (mostly between -2% and +6%), showing that the business matured into a very stable phase, predictably earning around $1M to$1.15M per month.
• Spotting Bad Data Instantly: The rolling average makes data cutoffs obvious. The massive negative variance in September 2018 immediately flags that our dataset ends abruptly, preventing us from accidentally using that raw number in any serious forecasting.

Revenue Distribution across States and Cities

9. How is revenue distributed across different states (customer_state) and cities? specifically, identifying the top 5 regions with the highest sales density per capita versus regions with high order volume but low total revenue?

Solution:

1. Firstly, we calculate regional baseline metrics (region_performance CTE): We join the orders, customers, and payments tables to aggregate the total_orders and total_revenue for each customer_state and customer_city. We also calculate the avg_order_value (revenue density) by dividing total revenue by the distinct order count, ensuring we exclude ‘canceled’ or ‘unavailable’ orders.

2. Secondly, we rank the regions (region_rankings CTE): We use the rank() window function to assign a revenue_rank and a volume_rank to each city/state combination. By ordering descendingly, the regions with the highest revenue and order counts receive the top ranks (e.g., Rank 1, 2, 3).

3. Finally, we segment the regions based on their rankings (Final SELECT): We use a CASE statement to classify each region’s profile by evaluating its volume and revenue ranks. This allows us to categorize regions into distinct business profiles such as ‘Major Economic Hub’ (top ranks in both volume and revenue) or ‘Affluent Niche’ (lower volume rank but high revenue rank), clearly identifying the sales density and distribution across different geographies.

with region_performance as (
    SELECT
        customers.customer_state,
        customers.customer_city,
        count(distinct orders.order_id) as total_orders,
        sum(payments.payment_value) as total_revenue,
        -- avg order value -> revenue density
        sum(payments.payment_value) / count(distinct orders.order_id) as avg_order_value
    FROM 
        orders
    join   
        customers on customers.customer_id = orders.customer_id
    join 
        payments on payments.order_id = orders.order_id
    where 
        orders.order_status not in ('canceled', 'unavailable')
    group by 
        customers.customer_state,
        customers.customer_city
), 

region_rankings as (
    select 
        *,
        rank() over (order by total_revenue desc) as revenue_rank, 
        rank() over ( order by total_orders desc) as volume_rank
    from 
        region_performance
)

select 
    customer_state,
    customer_city,
    total_orders,
    total_revenue,
    cast(avg_order_value as decimal(10,2)) as avg_order_value,
    case 
        when revenue_rank <= 10 and volume_rank <= 10 then 'Major Economic Hub (High Vol / High Rev)'
        when revenue_rank <= 20 and volume_rank > 50 then 'Affluent Niche (Low Vol / High Rev)'
        when revenue_rank <= 10 and volume_rank <= 10 then 'Mass Market (High Vol / Low Rev)'
        else 'Standard Market'
    end as regional_profile
from 
    region_rankings
order by 
    total_revenue desc;

Results:

Insights:

• The Dominance of São Paulo (SP): São Paulo completely crushes the competition. It acts as our absolute major economic hub, pulling in $2.15M in revenue from over 15,000 orders. This single city brings in almost double the revenue of the second-place city, Rio de Janeiro.
• Top 5 Revenue Centers: The top 5 regions driving the highest total revenue are all major state capitals: São Paulo (SP), Rio de Janeiro (RJ), Belo Horizonte (MG), Brasília (DF), and Curitiba (PR). Together, they represent the core market for Olist.
• State Performance (The “SP” Factor): Even outside of the main capital, the state of São Paulo (SP) is dominant. Cities like Campinas, Guarulhos, and Santos pull in massive volumes, showing that the platform’s logistics and marketing are incredibly effective throughout this entire state.
• High Volume vs. High Revenue Outliers:
  • High Average Order Value (AOV) in Remote Regions: If you look at standard markets like Belém (PA), João Pessoa (PB), or Macapá (AP), their order volumes are lower (under 500 orders), but their Average Order Values are huge ($216,$247, and $253 respectively). Customers in these regions are likely buying heavier, more expensive items because local retail options are limited.
  • Volume Does Not Always Equal Value: Take Carapicuiba (SP) as an example. It generated 324 orders (higher than many standard markets), but only pulled in $37K with a very low AOV of$114.80. This tells us the city generates high engagement but mostly for cheaper items.

Average Order Value and Basket Size Analysis

10. AOV and Busket Size Analysis: What is the average order value (AOV) and average basket size (number of items per order) for Olist? How do these metrics vary across year and months?

Solution:

1. Firstly, we isolate order-level totals (order_item_totals and order_payment_totals CTEs): This is crucial to prevent data duplication caused by 1-to-many relationships (multiple items or payment methods per order). We calculate the total number of items (items_in_basket) and the total payment value (total_order_payment) grouped strictly by unique order_id.

2. Secondly, we aggregate these metrics by month (monthly_metrics CTE): We join our safe order-level totals with the orders and Calendar tables, ensuring we filter out ‘canceled’ or ‘unavailable’ orders. This gives us the absolute total_orders, monthly_revenue, and monthly_items_count for each specific year and month.

3. Finally, we calculate the key performance indicators (Final SELECT): By dividing the monthly revenue by total orders, we get the Average Order Value (AOV). Dividing the total items by total orders gives us the Average Basket Size. We also calculate an avg_price_per_item proxy (AOV divided by basket size) to understand the value density of the baskets over time.

with order_item_totals as (
    -- step 1: calculate total items and total price per order
    -- this prevents duplication before joining with payments
    select 
        order_id,
        count(order_item_id) as items_in_basket,
        sum(price) as total_items_price
    from 
        order_items
    group by 
        order_id
),

order_payment_totals as (
    -- step 2: calculate total payment value per order 
    -- (accounts for multi-payment methods/installments)
    select 
        order_id,
        sum(payment_value) as total_order_payment
    from 
        payments
    group by 
        order_id
),

monthly_metrics as (
    -- step 3: aggregate metrics by month
    select 
        c.year,
        c.month_number,
        c.month_name,
        count(o.order_id) as total_orders,
        sum(p.total_order_payment) as monthly_revenue,
        sum(i.items_in_basket) as monthly_items_count
    from 
        orders o
    inner join 
        Calendar c on o.purchase_datekey = c.datekey
    inner join 
        order_item_totals i on o.order_id = i.order_id
    inner join 
        order_payment_totals p on o.order_id = p.order_id
    where 
        o.order_status not in ('canceled', 'unavailable')
    group by 
        c.year, c.month_number, c.month_name
)

-- final output: calculating aov and avg basket size trends
select 
    year,
    month_name,
    total_orders,
    -- aov: total revenue / total orders
    cast(monthly_revenue / total_orders as decimal(10,2)) as aov,
    -- avg basket size: total items / total orders
    cast(monthly_items_count * 1.0 / total_orders as decimal(10,2)) as avg_basket_size,
    -- item value proxy: aov / avg basket size (average price per item)
    cast((monthly_revenue / total_orders) / (monthly_items_count * 1.0 / total_orders) as decimal(10,2)) as avg_price_per_item
from 
    monthly_metrics
order by 
    year, month_number;

Results:

İnsights:

• Single-Item Shoppers: The most striking takeaway is the average basket size. It stays completely flat, hovering between 1.11 and 1.17 items per order across all active months. Olist customers almost exclusively buy just one item at a time.
• Stable Average Order Value (AOV): AOV is remarkably consistent, generally floating between $150 and$170. There are slight dips (like $146.70 in July 2017 or$147.92 in Feb 2018), but it always bounces back into the $160s quickly.
• The Black Friday Shopping Behavior: During the massive sales peak in November 2017, the AOV ($157.98) and basket size (1.16) were completely average. This proves the Black Friday revenue boom came entirely from a massive flood of new orders, not from customers stuffing their carts with multiple discounted items.
• Price Per Item Controls AOV: Because the basket size is practically stuck at 1, the AOV is directly controlled by the avg_price_per_item. When customers buy slightly cheaper goods overall (like in early 2018), the AOV drops right alongside it.

Conclusion

What We Did:

• Analyzed overall order and revenue trends using both raw monthly groupings and a 3-month rolling average to smooth out volatility.
• Segmented product categories to compare raw sales volume against actual revenue generated.
• Mapped out the geographic distribution of sales to find high-performing states and cities.
• Tracked Average Order Value (AOV) and average basket size across the entire timeline.

What We Obtained (Key Findings):

• Growth & Plateau: Olist experienced explosive growth in 2017 and matured into a stable business in 2018, consistently generating between $1M and$1.15M monthly.
• Single-Item Baskets: Customers are overwhelmingly single-item shoppers. The average basket size is practically locked at ~1.14 items across all months.
• Volume Over Cart Size: The massive Black Friday peak in November 2017 was driven entirely by a flood of new orders, not by customers stuffing more items into their carts.
• The São Paulo Anchor: São Paulo (SP) is the undisputed core market for volume and revenue. However, remote regions consistently show a higher AOV, likely due to a lack of local retail alternatives.
• Category Efficiency: Categories like health/beauty and watches are highly efficient (high volume + high revenue). In contrast, electronics are a “trap” category, moving lots of units but yielding relatively low revenue.