Time Series Analysis¶
MoM and Seasonality Analysis¶
29. What is the Month-over-Month (MoM) growth rate for total revenue across the entire dataset? Can we identify specific months where the business experienced 'Hyper-Growth' (>20% increase) versus months of stagnation or contraction?
Solution:
-
To calculate the month-over-month growth rate, the core metrics must first be aggregated by month. This should be done in the first CTE by joining the orders, payments, and calendar tables. The total number of orders and the total revenue must be calculated for each specific year and month. Canceled or unavailable orders must also be filtered out to ensure the revenue figures are perfectly accurate.
-
Next, the historical data must be retrieved to allow for comparison. The lag window function should be used here to fetch the revenue and order volume from both the previous month and the previous year. The data must be ordered chronologically by year and month number to ensure the lag function pulls the correct prior periods for every single row.
-
Once the current and previous metrics are aligned, the actual percentage growth must be calculated. The mathematical formula for percentage change will be applied to both month-over-month and year-over-year revenue. A nullif function must be included during the division step to prevent any mathematical errors just in case the previous month had zero recorded revenue.
-
Finally, the calculated growth rates must be categorized to clearly highlight business performance. A case statement will be used in the main query to assign a growth performance tag, easily identifying periods of hyper-growth above twenty percent, standard positive growth, or stagnation. An additional case statement should also be applied to flag key seasonal periods like Black Friday and the holiday season to provide necessary business context for any major revenue fluctuations.
Show SQL Code
/* growth and seasonality analysis
objective: calculate mom and yoy growth rates for revenue and order volume
logic:
1. aggregate revenue and order counts by month.
2. use lag() window function to compare current month vs prior month and prior year.
3. calculate percentage growth rates and classify performance tiers.
granularity: year_month
*/
with monthly_sales as (
-- step 1: aggregate core metrics by month
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_calculations as (
-- step 3: calculate percentage growth
select
*,
mom_revenue_growth_pct = cast(100.0 * (total_revenue - prev_month_revenue) / nullif(prev_month_revenue, 0) as decimal(10,2)),
yoy_revenue_growth_pct = cast(100.0 * (total_revenue - prev_year_revenue) / nullif(prev_year_revenue, 0) as decimal(10,2))
from
growth_metrics
)
-- final output: categorizing growth performance levels and seasonality
select
year,
month_name,
total_orders,
total_revenue,
mom_revenue_growth_pct,
-- identifying growth tiers as requested
case
when mom_revenue_growth_pct > 20 then 'Hyper-Growth (>20%)'
when mom_revenue_growth_pct > 0 then 'Positive Growth'
when mom_revenue_growth_pct <= 0 then 'Stagnation / Contraction'
else 'Baseline (No Prior Month)'
end as growth_performance_tag,
-- 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
final_calculations
order by
year, month_number;
Results:
| year | month_name | total_orders | total_revenue | mom_revenue_growth_pct | growth_performance_tag | seasonality_tag |
|---|---|---|---|---|---|---|
| 2016 | September | 1 | 136.22999572753906 | Baseline (No Prior Month) | Standard Period | |
| 2016 | October | 293 | 51657.52999472618 | 37819.35 | Hyper-Growth (>20%) | Standard Period |
| 2016 | December | 1 | 19.6200008392334 | -99.96 | Stagnation / Contraction | Holiday Season |
| 2017 | January | 787 | 137006.76003867388 | 698201.50 | Hyper-Growth (>20%) | New Year Peak |
| 2017 | February | 1718 | 283621.93943831325 | 107.01 | Hyper-Growth (>20%) | Standard Period |
| 2017 | March | 2617 | 425656.3995541632 | 50.08 | Hyper-Growth (>20%) | Standard Period |
| 2017 | April | 2377 | 405988.3795110285 | -4.62 | Stagnation / Contraction | Standard Period |
| 2017 | May | 3640 | 582926.160167003 | 43.58 | Hyper-Growth (>20%) | Standard Period |
| 2017 | June | 3205 | 499827.47035080194 | -14.26 | Stagnation / Contraction | Standard Period |
| 2017 | July | 3946 | 578858.5795211494 | 15.81 | Positive Growth | Standard Period |
| 2017 | August | 4272 | 662071.7698084246 | 14.38 | Positive Growth | Standard Period |
| 2017 | September | 4227 | 717195.2203616947 | 8.33 | Positive Growth | Standard Period |
| 2017 | October | 4547 | 764785.2796836384 | 6.64 | Positive Growth | Standard Period |
| 2017 | November | 7423 | 1172639.2297811043 | 53.33 | Hyper-Growth (>20%) | Black Friday Period |
| 2017 | December | 5620 | 861914.4599985629 | -26.50 | Stagnation / Contraction | Holiday Season |
| 2018 | January | 7187 | 1102639.4091129452 | 27.93 | Hyper-Growth (>20%) | New Year Peak |
| 2018 | February | 6625 | 979966.2302345932 | -11.13 | Stagnation / Contraction | Standard Period |
| 2018 | March | 7168 | 1152736.7396906763 | 17.63 | Positive Growth | Standard Period |
| 2018 | April | 6919 | 1156303.9107048158 | 0.31 | Positive Growth | Standard Period |
| 2018 | May | 6833 | 1145748.6310662013 | -0.91 | Stagnation / Contraction | Standard Period |
| 2018 | June | 6145 | 1020494.2907593064 | -10.93 | Stagnation / Contraction | Standard Period |
| 2018 | July | 6233 | 1039880.1590387616 | 1.90 | Positive Growth | Standard Period |
| 2018 | August | 6421 | 996896.1512343884 | -4.13 | Stagnation / Contraction | Standard Period |
| 2018 | September | 1 | 166.4600067138672 | -99.98 | Stagnation / Contraction | Standard Period |
Insights:
- The 2017 Startup Boom: The first half of 2017 is defined by aggressive "Hyper-Growth." Months like February (+107%), March (+50%), and May (+43%) show huge revenue jumps, proving this was the phase where Olist rapidly acquired its early market share.
- The Black Friday Rollercoaster: November 2017 was an absolute game-changer. Revenue spiked by 53.3% due to Black Friday sales. However, this created a predictable hangover in December, resulting in a 26.5% contraction as the holiday shopping rush ended.
- The 2018 Plateau: After recovering with a strong January 2018 (+27.9%), the platform's growth essentially flattened out. The rest of 2018 is filled with slight contractions (like -11% in February and -10% in June) and very mild growth (like +0.3% in April). The business stopped doubling its size and stabilized at around $1M to $1.15M per month.
- Ignoring the Noise: As analysts, we need to ignore the massive percentages at the very start (late 2016) and very end (Sept 2018) of the table. These months only have 1 recorded order, meaning they represent the setup phase and the cutoff date of our dataset, not actual business performance.
Seasonality Effect¶
30. How does the revenue of specific months compare across different years (e.g., Jan 2017 vs. Jan 2018)? Can we observe a consistent 'Seasonality Effect' where certain months (like November) consistently outperform others regardless of the year?
Solution:
-
To analyze the seasonality effect, revenue must first be aggregated by year and month. This should be done in the first CTE by joining the orders, payments, and calendar tables. The total revenue for each month of every year must be calculated while filtering out canceled or unavailable orders to ensure accuracy.
-
Next, the lag window function should be used to compare the revenue of the same month across different years. This will allow for a direct year-over-year comparison for each month, such as January 2017 vs January 2018. Additionally, the average monthly revenue for each year should be calculated to serve as a benchmark for identifying seasonal trends.
-
Once the current month revenue, previous year same month revenue, and yearly average are all aligned, a seasonality index can be calculated. This index will be derived by dividing the current month's revenue by the yearly average. An index greater than 1.0 will indicate that the month outperformed the year's average, suggesting a strong seasonal effect.
-
Finally, the seasonality index should be categorized to clearly identify which months are peak performers, standard performers, or low season periods. A case statement will be used to classify months with a seasonality index above 1.2 as 'Peak Months', those between 0.9 and 1.2 as 'Standard Performance', and those below 0.9 as 'Low Season / Slack Period'. This final output will help determine if certain months consistently outperform others across different years, confirming the presence of a seasonality effect.
Show SQL Code
/* growth and seasonality analysis
objective:
1. compare specific months across years (e.g., Jan 2017 vs Jan 2018).
2. calculate a 'seasonality index' to identify recurring monthly trends.
logic:
- aggregate revenue by year and month.
- use window functions to compare same-month performance across years.
- calculate the deviation of each month from its year's average to find the 'seasonal push'.
*/
with monthly_revenue_base as (
-- step 1: aggregate total revenue by year and month
select
c.year,
c.month_number,
c.month_name,
sum(p.payment_value) as monthly_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
),
seasonal_indexing as (
-- step 2: calculate year-over-year same-month comparisons and yearly averages
select
*,
-- direct comparison: revenue of the same month in the previous year
prev_year_same_month_rev = lag(monthly_revenue) over (
partition by month_number
order by year
),
-- average monthly revenue for that specific year (benchmark)
yearly_avg_monthly_rev = avg(monthly_revenue) over (
partition by year
)
from
monthly_revenue_base
)
-- final output: quantifying the seasonality effect
select
year,
month_name,
cast(monthly_revenue as decimal(15,2)) as current_month_revenue,
-- yoy comparison for the specific month
cast(100.0 * (monthly_revenue - prev_year_same_month_rev) / nullif(prev_year_same_month_rev, 0) as decimal(10,2)) as month_specific_yoy_growth_pct,
-- seasonality index: revenue / yearly average
-- index > 1.0 means the month outperforms the year's average
cast(monthly_revenue / nullif(yearly_avg_monthly_rev, 0) as decimal(10,2)) as seasonality_index,
-- classification of the month's role in the business cycle
case
when (monthly_revenue / yearly_avg_monthly_rev) >= 1.2 then 'Peak Month (Strong Seasonality)'
when (monthly_revenue / yearly_avg_monthly_rev) between 0.9 and 1.2 then 'Standard Performance'
when (monthly_revenue / yearly_avg_monthly_rev) < 0.9 then 'Low Season / Slack Period'
else 'Baseline'
end as seasonal_classification
from
seasonal_indexing
order by
year, month_number;
|year|month_name|current_month_revenue|month_specific_yoy_growth_pct|seasonality_index|seasonal_classification|
|----|----------|---------------------|-----------------------------|-----------------|-----------------------|
|2016|September|136.23||0.01|Low Season / Slack Period|
|2016|October|51657.53||2.99|Peak Month (Strong Seasonality)|
|2016|December|19.62||0.00|Low Season / Slack Period|
|2017|January|137006.76||0.23|Low Season / Slack Period|
|2017|February|283621.94||0.48|Low Season / Slack Period|
|2017|March|425656.40||0.72|Low Season / Slack Period|
|2017|April|405988.38||0.69|Low Season / Slack Period|
|2017|May|582926.16||0.99|Standard Performance|
|2017|June|499827.47||0.85|Low Season / Slack Period|
|2017|July|578858.58||0.98|Standard Performance|
|2017|August|662071.77||1.12|Standard Performance|
|2017|September|717195.22|526359.11|1.21|Peak Month (Strong Seasonality)|
|2017|October|764785.28|1380.49|1.29|Peak Month (Strong Seasonality)|
|2017|November|1172639.23||1.98|Peak Month (Strong Seasonality)|
|2017|December|861914.46|4392939.87|1.46|Peak Month (Strong Seasonality)|
|2018|January|1102639.41|704.81|1.15|Standard Performance|
|2018|February|979966.23|245.52|1.03|Standard Performance|
|2018|March|1152736.74|170.81|1.21|Peak Month (Strong Seasonality)|
|2018|April|1156303.91|184.81|1.21|Peak Month (Strong Seasonality)|
|2018|May|1145748.63|96.55|1.20|Standard Performance|
|2018|June|1020494.29|104.17|1.07|Standard Performance|
|2018|July|1039880.16|79.64|1.09|Standard Performance|
|2018|August|996896.15|50.57|1.04|Standard Performance|
|2018|September|166.46|-99.98|0.00|Low Season / Slack Period|
**Insights:**
* **Massive Scaling:** Comparing the same months across years shows the true scale of growth. January 2018 revenue was 704% higher than January 2017. Every month in 2018 outperformed the previous year by a huge margin.
* **November is the Undisputed King:** With a Seasonality Index of 1.98, November performs nearly twice as well as the average month. This confirms that Black Friday is the single most important event for the platform's annual revenue.
* **Shift to Year-Round Demand:** In 2017, the first half of the year was considered "Low Season." By 2018, months like March and April became "Peak Months," showing that Olist successfully transitioned into a year-round shopping destination.
* **Predictable Maturity:** The Seasonality Index for 2018 stayed very stable (between 1.03 and 1.21). This indicates that the business became much more predictable as it matured, with less chaotic fluctuations than the previous year.
* **The High-Revenue Window:** A clear "Peak" cluster exists from September to January. This five-month window is the most critical period for the business, likely combining Black Friday, Christmas, and New Year sales.
## Distribution of Order Volume by Day and Hour
31. What is the distribution of order volume across different **Days of the Week** and **Hours of the Day**? Specifically, do we see a 'Lunchtime Spike' (12 PM - 2 PM) or an 'Evening Spike' (8 PM - 10 PM)?
**Solution:**
- To analyze the distribution of order volume across different days of the week and hours of the day, the order data must first be aggregated by extracting the day of the week and hour from the order purchase timestamp. This should be done in a CTE by using date functions to derive these time components. The total number of orders for each combination of day and hour must be calculated while filtering out canceled or unavailable orders.
- Then we can analyze the aggregated data to identify specific patterns. A case statement should be used to classify hours between 12 PM and 2 PM as 'Lunchtime Spike Window' and hours between 8 PM and 10 PM as 'Evening Spike Window'. All other hours can be classified as 'Standard Time'.
- Finally, the results should be ordered by the day of the week and hour to clearly visualize the distribution of order volume throughout the week and identify any consistent spikes during lunchtime or evening hours. This analysis will help determine if there are specific times when customer engagement is higher, which can inform marketing strategies and operational planning.
Show SQL Code
/* growth and seasonality analysis
objective:
1. compare specific months across years (e.g., Jan 2017 vs Jan 2018).
2. calculate a 'seasonality index' to identify recurring monthly trends.
3. analyze order distribution by Day of the Week and Hour of Day.
logic:
- aggregate revenue by year and month for high-level trends.
- use window functions for year-over-year same-month comparisons.
- extract time-of-day and day-of-week metrics to find peak engagement windows.
*/
with monthly_revenue_base as (
-- step 1: aggregate total revenue by year and month
select
c.year,
c.month_number,
c.month_name,
sum(p.payment_value) as monthly_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
),
seasonal_indexing as (
-- step 2: calculate year-over-year same-month comparisons and yearly averages
select
*,
-- direct comparison: revenue of the same month in the previous year
prev_year_same_month_rev = lag(monthly_revenue) over (
partition by month_number
order by year
),
-- average monthly revenue for that specific year (benchmark)
yearly_avg_monthly_rev = avg(monthly_revenue) over (
partition by year
)
from
monthly_revenue_base
),
-- step 3: analyzing hourly and weekly volume distribution
time_of_day_stats as (
select
datename(dw, order_purchase_timestamp) as day_of_week,
datepart(dw, order_purchase_timestamp) as day_num, -- used for sorting
datepart(hour, order_purchase_timestamp) as hour_of_day,
count(order_id) as total_orders
from
orders
where
order_status not in ('canceled', 'unavailable')
group by
datename(dw, order_purchase_timestamp),
datepart(dw, order_purchase_timestamp),
datepart(hour, order_purchase_timestamp)
)
-- final output: combining the original seasonality analysis with the new time-of-day insights
-- part 1: monthly seasonality (existing)
select
year,
month_name,
cast(monthly_revenue as decimal(15,2)) as current_month_revenue,
-- yoy comparison for the specific month
cast(100.0 * (monthly_revenue - prev_year_same_month_rev) / nullif(prev_year_same_month_rev, 0) as decimal(10,2)) as month_specific_yoy_growth_pct,
-- seasonality index
cast(monthly_revenue / nullif(yearly_avg_monthly_rev, 0) as decimal(10,2)) as seasonality_index,
case
when (monthly_revenue / yearly_avg_monthly_rev) >= 1.2 then 'Peak Month (Strong Seasonality)'
when (monthly_revenue / yearly_avg_monthly_rev) between 0.9 and 1.2 then 'Standard Performance'
when (monthly_revenue / yearly_avg_monthly_rev) < 0.9 then 'Low Season / Slack Period'
else 'Baseline'
end as seasonal_classification
from
seasonal_indexing
union all
-- part 2: placeholder/separator row for clear data interpretation if exported
select
null, '--- INTRA-DAY ANALYSIS ---', null, null, null, null
union all
-- part 3: day/hour distribution
select
day_num as year, -- repurposing columns for the union's schema
concat(day_of_week, ' at ', hour_of_day, ':00') as month_name,
total_orders as current_month_revenue,
null as month_specific_yoy_growth_pct,
null as seasonality_index,
case
when hour_of_day between 12 and 14 then 'Lunchtime Spike Window'
when hour_of_day between 20 and 22 then 'Evening Spike Window'
else 'Standard Time'
end as seasonal_classification
from
time_of_day_stats
order by
year, month_name;
|year|month_name|current_month_revenue|month_specific_yoy_growth_pct|seasonality_index|seasonal_classification|
|----|----------|---------------------|-----------------------------|-----------------|-----------------------|
||--- INTRA-DAY ANALYSIS ---|||||
|1|Sunday at 0:00|260.00|||Standard Time|
|1|Sunday at 1:00|140.00|||Standard Time|
|1|Sunday at 10:00|524.00|||Standard Time|
|1|Sunday at 11:00|715.00|||Standard Time|
|1|Sunday at 12:00|669.00|||Lunchtime Spike Window|
|1|Sunday at 13:00|714.00|||Lunchtime Spike Window|
|1|Sunday at 14:00|676.00|||Lunchtime Spike Window|
|1|Sunday at 15:00|707.00|||Standard Time|
|1|Sunday at 16:00|706.00|||Standard Time|
|1|Sunday at 17:00|792.00|||Standard Time|
|1|Sunday at 18:00|888.00|||Standard Time|
|1|Sunday at 19:00|892.00|||Standard Time|
|1|Sunday at 2:00|68.00|||Standard Time|
|1|Sunday at 20:00|956.00|||Evening Spike Window|
|1|Sunday at 21:00|876.00|||Evening Spike Window|
|1|Sunday at 22:00|864.00|||Evening Spike Window|
|1|Sunday at 23:00|603.00|||Standard Time|
|1|Sunday at 3:00|43.00|||Standard Time|
|1|Sunday at 4:00|27.00|||Standard Time|
|1|Sunday at 5:00|27.00|||Standard Time|
|1|Sunday at 6:00|33.00|||Standard Time|
|1|Sunday at 7:00|105.00|||Standard Time|
|1|Sunday at 8:00|203.00|||Standard Time|
|1|Sunday at 9:00|343.00|||Standard Time|
|2|Monday at 0:00|322.00|||Standard Time|
|2|Monday at 1:00|134.00|||Standard Time|
|2|Monday at 10:00|1020.00|||Standard Time|
|2|Monday at 11:00|1068.00|||Standard Time|
|2|Monday at 12:00|961.00|||Lunchtime Spike Window|
|2|Monday at 13:00|1013.00|||Lunchtime Spike Window|
|2|Monday at 14:00|1082.00|||Lunchtime Spike Window|
|2|Monday at 15:00|1065.00|||Standard Time|
|2|Monday at 16:00|1076.00|||Standard Time|
|2|Monday at 17:00|974.00|||Standard Time|
|2|Monday at 18:00|915.00|||Standard Time|
|2|Monday at 19:00|933.00|||Standard Time|
|2|Monday at 2:00|64.00|||Standard Time|
|2|Monday at 20:00|1016.00|||Evening Spike Window|
|2|Monday at 21:00|1103.00|||Evening Spike Window|
|2|Monday at 22:00|982.00|||Evening Spike Window|
|2|Monday at 23:00|708.00|||Standard Time|
|2|Monday at 3:00|36.00|||Standard Time|
|2|Monday at 4:00|21.00|||Standard Time|
|2|Monday at 5:00|22.00|||Standard Time|
|2|Monday at 6:00|65.00|||Standard Time|
|2|Monday at 7:00|159.00|||Standard Time|
|2|Monday at 8:00|475.00|||Standard Time|
|2|Monday at 9:00|765.00|||Standard Time|
|3|Tuesday at 0:00|304.00|||Standard Time|
|3|Tuesday at 1:00|157.00|||Standard Time|
|3|Tuesday at 10:00|950.00|||Standard Time|
|3|Tuesday at 11:00|1040.00|||Standard Time|
|3|Tuesday at 12:00|889.00|||Lunchtime Spike Window|
|3|Tuesday at 13:00|1036.00|||Lunchtime Spike Window|
|3|Tuesday at 14:00|1106.00|||Lunchtime Spike Window|
|3|Tuesday at 15:00|1026.00|||Standard Time|
|3|Tuesday at 16:00|1074.00|||Standard Time|
|3|Tuesday at 17:00|956.00|||Standard Time|
|3|Tuesday at 18:00|868.00|||Standard Time|
|3|Tuesday at 19:00|909.00|||Standard Time|
|3|Tuesday at 2:00|80.00|||Standard Time|
|3|Tuesday at 20:00|970.00|||Evening Spike Window|
|3|Tuesday at 21:00|1017.00|||Evening Spike Window|
|3|Tuesday at 22:00|955.00|||Evening Spike Window|
|3|Tuesday at 23:00|685.00|||Standard Time|
|3|Tuesday at 3:00|28.00|||Standard Time|
|3|Tuesday at 4:00|28.00|||Standard Time|
|3|Tuesday at 5:00|24.00|||Standard Time|
|3|Tuesday at 6:00|71.00|||Standard Time|
|3|Tuesday at 7:00|219.00|||Standard Time|
|3|Tuesday at 8:00|515.00|||Standard Time|
|3|Tuesday at 9:00|853.00|||Standard Time|
|4|Wednesday at 0:00|392.00|||Standard Time|
|4|Wednesday at 1:00|175.00|||Standard Time|
|4|Wednesday at 10:00|1029.00|||Standard Time|
|4|Wednesday at 11:00|1046.00|||Standard Time|
|4|Wednesday at 12:00|912.00|||Lunchtime Spike Window|
|4|Wednesday at 13:00|1009.00|||Lunchtime Spike Window|
|4|Wednesday at 14:00|1039.00|||Lunchtime Spike Window|
|4|Wednesday at 15:00|971.00|||Standard Time|
|4|Wednesday at 16:00|1024.00|||Standard Time|
|4|Wednesday at 17:00|958.00|||Standard Time|
|4|Wednesday at 18:00|837.00|||Standard Time|
|4|Wednesday at 19:00|833.00|||Standard Time|
|4|Wednesday at 2:00|81.00|||Standard Time|
|4|Wednesday at 20:00|892.00|||Evening Spike Window|
|4|Wednesday at 21:00|953.00|||Evening Spike Window|
|4|Wednesday at 22:00|872.00|||Evening Spike Window|
|4|Wednesday at 23:00|612.00|||Standard Time|
|4|Wednesday at 3:00|31.00|||Standard Time|
|4|Wednesday at 4:00|33.00|||Standard Time|
|4|Wednesday at 5:00|26.00|||Standard Time|
|4|Wednesday at 6:00|93.00|||Standard Time|
|4|Wednesday at 7:00|210.00|||Standard Time|
|4|Wednesday at 8:00|508.00|||Standard Time|
|4|Wednesday at 9:00|817.00|||Standard Time|
|5|Thursday at 0:00|354.00|||Standard Time|
|5|Thursday at 1:00|167.00|||Standard Time|
|5|Thursday at 10:00|970.00|||Standard Time|
|5|Thursday at 11:00|945.00|||Standard Time|
|5|Thursday at 12:00|963.00|||Lunchtime Spike Window|
|5|Thursday at 13:00|968.00|||Lunchtime Spike Window|
|5|Thursday at 14:00|968.00|||Lunchtime Spike Window|
|5|Thursday at 15:00|916.00|||Standard Time|
|5|Thursday at 16:00|1066.00|||Standard Time|
|5|Thursday at 17:00|893.00|||Standard Time|
|5|Thursday at 18:00|776.00|||Standard Time|
|5|Thursday at 19:00|814.00|||Standard Time|
|5|Thursday at 2:00|74.00|||Standard Time|
|5|Thursday at 20:00|829.00|||Evening Spike Window|
|5|Thursday at 21:00|832.00|||Evening Spike Window|
|5|Thursday at 22:00|850.00|||Evening Spike Window|
|5|Thursday at 23:00|541.00|||Standard Time|
|5|Thursday at 3:00|39.00|||Standard Time|
|5|Thursday at 4:00|31.00|||Standard Time|
|5|Thursday at 5:00|28.00|||Standard Time|
|5|Thursday at 6:00|83.00|||Standard Time|
|5|Thursday at 7:00|217.00|||Standard Time|
|5|Thursday at 8:00|497.00|||Standard Time|
|5|Thursday at 9:00|750.00|||Standard Time|
|6|Friday at 0:00|418.00|||Standard Time|
|6|Friday at 1:00|210.00|||Standard Time|
|6|Friday at 10:00|942.00|||Standard Time|
|6|Friday at 11:00|983.00|||Standard Time|
|6|Friday at 12:00|843.00|||Lunchtime Spike Window|
|6|Friday at 13:00|989.00|||Lunchtime Spike Window|
|6|Friday at 14:00|950.00|||Lunchtime Spike Window|
|6|Friday at 15:00|969.00|||Standard Time|
|6|Friday at 16:00|960.00|||Standard Time|
|6|Friday at 17:00|807.00|||Standard Time|
|6|Friday at 18:00|716.00|||Standard Time|
|6|Friday at 19:00|772.00|||Standard Time|
|6|Friday at 2:00|70.00|||Standard Time|
|6|Friday at 20:00|730.00|||Evening Spike Window|
|6|Friday at 21:00|714.00|||Evening Spike Window|
|6|Friday at 22:00|695.00|||Evening Spike Window|
|6|Friday at 23:00|507.00|||Standard Time|
|6|Friday at 3:00|48.00|||Standard Time|
|6|Friday at 4:00|40.00|||Standard Time|
|6|Friday at 5:00|35.00|||Standard Time|
|6|Friday at 6:00|96.00|||Standard Time|
|6|Friday at 7:00|204.00|||Standard Time|
|6|Friday at 8:00|493.00|||Standard Time|
|6|Friday at 9:00|758.00|||Standard Time|
|7|Saturday at 0:00|312.00|||Standard Time|
|7|Saturday at 1:00|171.00|||Standard Time|
|7|Saturday at 10:00|648.00|||Standard Time|
|7|Saturday at 11:00|701.00|||Standard Time|
|7|Saturday at 12:00|681.00|||Lunchtime Spike Window|
|7|Saturday at 13:00|702.00|||Lunchtime Spike Window|
|7|Saturday at 14:00|670.00|||Lunchtime Spike Window|
|7|Saturday at 15:00|716.00|||Standard Time|
|7|Saturday at 16:00|688.00|||Standard Time|
|7|Saturday at 17:00|694.00|||Standard Time|
|7|Saturday at 18:00|707.00|||Standard Time|
|7|Saturday at 19:00|746.00|||Standard Time|
|7|Saturday at 2:00|66.00|||Standard Time|
|7|Saturday at 20:00|723.00|||Evening Spike Window|
|7|Saturday at 21:00|651.00|||Evening Spike Window|
|7|Saturday at 22:00|543.00|||Evening Spike Window|
|7|Saturday at 23:00|425.00|||Standard Time|
|7|Saturday at 3:00|42.00|||Standard Time|
|7|Saturday at 4:00|25.00|||Standard Time|
|7|Saturday at 5:00|23.00|||Standard Time|
|7|Saturday at 6:00|52.00|||Standard Time|
|7|Saturday at 7:00|104.00|||Standard Time|
|7|Saturday at 8:00|248.00|||Standard Time|
|7|Saturday at 9:00|426.00|||Standard Time|
|2016|December|19.62||0.00|Low Season / Slack Period|
|2016|October|51657.53||2.99|Peak Month (Strong Seasonality)|
|2016|September|136.23||0.01|Low Season / Slack Period|
|2017|April|405988.38||0.69|Low Season / Slack Period|
|2017|August|662071.77||1.12|Standard Performance|
|2017|December|861914.46|4392939.87|1.46|Peak Month (Strong Seasonality)|
|2017|February|283621.94||0.48|Low Season / Slack Period|
|2017|January|137006.76||0.23|Low Season / Slack Period|
|2017|July|578858.58||0.98|Standard Performance|
|2017|June|499827.47||0.85|Low Season / Slack Period|
|2017|March|425656.40||0.72|Low Season / Slack Period|
|2017|May|582926.16||0.99|Standard Performance|
|2017|November|1172639.23||1.98|Peak Month (Strong Seasonality)|
|2017|October|764785.28|1380.49|1.29|Peak Month (Strong Seasonality)|
|2017|September|717195.22|526359.11|1.21|Peak Month (Strong Seasonality)|
|2018|April|1156303.91|184.81|1.21|Peak Month (Strong Seasonality)|
|2018|August|996896.15|50.57|1.04|Standard Performance|
|2018|February|979966.23|245.52|1.03|Standard Performance|
|2018|January|1102639.41|704.81|1.15|Standard Performance|
|2018|July|1039880.16|79.64|1.09|Standard Performance|
|2018|June|1020494.29|104.17|1.07|Standard Performance|
|2018|March|1152736.74|170.81|1.21|Peak Month (Strong Seasonality)|
|2018|May|1145748.63|96.55|1.20|Standard Performance|
|2018|September|166.46|-99.98|0.00|Low Season / Slack Period|
**Insights:**
* **Monday is the Main Event:** Monday is the busiest day of the week for Olist. It hits peak volumes at 2:00 PM and 9:00 PM. Overall, sales are significantly higher during the workweek (Monday–Friday) than on weekends, showing that customers prefer shopping during work hours or their commute.
* **The Lunchtime Surge:** There is a very consistent "Lunchtime Spike" every single weekday. Between 12:00 PM and 2:00 PM, order counts surge as people shop on their breaks. On Tuesdays, for example, volume jumps from 853 at 9:00 AM to a peak of 1,106 by 2:00 PM.
* **The Evening Peak:** Regardless of the day, the highest activity happens during the "Evening Spike" between 8:00 PM and 10:00 PM. Monday at 9:00 PM is the busiest single hour of the entire week with 1,103 orders.
* **Weekend Drop-off:** Shopping activity cools down on Saturdays and Sundays. While the evening spike still exists on Sunday (956 orders at 8:00 PM), the overall baseline is much lower. Saturday is the quietest day on the platform across almost every hour.
* **Late-Night Dormancy:** Across all days, shopping almost completely stops between 2:00 AM and 6:00 AM, with most hours dropping below 40 orders. This is the ideal window for system maintenance since traffic is at its lowest.
## Cumulative Revenue and Growth Trajectory
32. What is the cumulative revenue generated over time since the inception of the platform? By plotting the running total of sales, can we visually confirm the business's transition from 'Linear Growth' to 'Exponential Growth' (The Hockey Stick Curve)?
**Solution:**
- To track cumulative revenue and observe the hockey stick growth curve, the total revenue must first be aggregated by year and month. This should be done in the first CTE by joining the orders, payments, and calendar tables, ensuring that canceled or unavailable orders are strictly filtered out.
- Next, a running total of the monthly revenue must be calculated. Window functions should be applied here to continuously sum the monthly revenue over time, ordered chronologically by year and month. This cumulative revenue metric will clearly show the overall growth trajectory of the business from its inception. In the exact same step, historical year-over-year comparisons and yearly averages will also be established to maintain the baseline seasonality analysis.
- To find the peak customer engagement windows, the order distribution by day of the week and hour of the day must be analyzed. A separate CTE should be created to extract the specific day name and hour from the order purchase timestamps. The total number of orders must then be counted for each specific hour of every day to find intraday shopping patterns.
- Finally, these distinct sets of insights must be combined into a single final output. The monthly seasonality and cumulative growth metrics should be selected first. Then, a union all statement must be used to append the day and hour distribution below the monthly data. A case statement will be applied here to tag specific times as lunchtime or evening spike windows. This unified approach will allow the business to view both high-level exponential growth and granular daily shopping habits in one comprehensive report.
Show SQL Code
/* growth and seasonality analysis
objective:
1. compare specific months across years (e.g., Jan 2017 vs Jan 2018).
2. calculate a 'seasonality index' to identify recurring monthly trends.
3. analyze order distribution by Day of the Week and Hour of Day.
4. calculate cumulative revenue to track "The Hockey Stick" growth curve.
logic:
- aggregate revenue by year and month for high-level trends.
- use window functions for year-over-year and running total calculations.
- extract time-of-day and day-of-week metrics to find peak engagement windows.
*/
with monthly_revenue_base as (
-- step 1: aggregate total revenue by year and month
select
c.year,
c.month_number,
c.month_name,
sum(p.payment_value) as monthly_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
),
seasonal_indexing as (
-- step 2: calculate YoY comparisons, yearly averages, and cumulative running total
select
*,
-- direct comparison: revenue of the same month in the previous year
prev_year_same_month_rev = lag(monthly_revenue) over (
partition by month_number
order by year
),
-- average monthly revenue for that specific year (benchmark)
yearly_avg_monthly_rev = avg(monthly_revenue) over (
partition by year
),
-- cumulative revenue: running total since the platform inception
running_total_revenue = sum(monthly_revenue) over (
order by year, month_number
)
from
monthly_revenue_base
),
-- step 3: analyzing hourly and weekly volume distribution
time_of_day_stats as (
select
datename(dw, order_purchase_timestamp) as day_of_week,
datepart(dw, order_purchase_timestamp) as day_num, -- used for sorting
datepart(hour, order_purchase_timestamp) as hour_of_day,
count(order_id) as total_orders
from
orders
where
order_status not in ('canceled', 'unavailable')
group by
datename(dw, order_purchase_timestamp),
datepart(dw, order_purchase_timestamp),
datepart(hour, order_purchase_timestamp)
)
-- final output: combining the original seasonality analysis with the new running total and time-of-day insights
-- part 1: monthly seasonality and cumulative growth
select
year,
month_name,
cast(monthly_revenue as decimal(15,2)) as current_month_revenue,
cast(running_total_revenue as decimal(15,2)) as cumulative_revenue,
-- yoy comparison for the specific month
cast(100.0 * (monthly_revenue - prev_year_same_month_rev) / nullif(prev_year_same_month_rev, 0) as decimal(10,2)) as month_specific_yoy_growth_pct,
-- seasonality index
cast(monthly_revenue / nullif(yearly_avg_monthly_rev, 0) as decimal(10,2)) as seasonality_index,
case
when (monthly_revenue / yearly_avg_monthly_rev) >= 1.2 then 'Peak Month (Strong Seasonality)'
when (monthly_revenue / yearly_avg_monthly_rev) between 0.9 and 1.2 then 'Standard Performance'
when (monthly_revenue / yearly_avg_monthly_rev) < 0.9 then 'Low Season / Slack Period'
else 'Baseline'
end as seasonal_classification
from
seasonal_indexing
union all
-- part 2: placeholder/separator row
select
null, '--- INTRA-DAY ANALYSIS ---', null, null, null, null, null
union all
-- part 3: day/hour distribution
select
day_num as year,
concat(day_of_week, ' at ', hour_of_day, ':00') as month_name,
total_orders as current_month_revenue,
null as cumulative_revenue,
null as month_specific_yoy_growth_pct,
null as seasonality_index,
case
when hour_of_day between 12 and 14 then 'Lunchtime Spike Window'
when hour_of_day between 20 and 22 then 'Evening Spike Window'
else 'Standard Time'
end as seasonal_classification
from
time_of_day_stats
order by
year, month_name;
|year|month_name|current_month_revenue|cumulative_revenue|month_specific_yoy_growth_pct|seasonality_index|seasonal_classification|
|----|----------|---------------------|------------------|-----------------------------|-----------------|-----------------------|
||--- INTRA-DAY ANALYSIS ---||||||
|1|Sunday at 0:00|260.00||||Standard Time|
|1|Sunday at 1:00|140.00||||Standard Time|
|1|Sunday at 10:00|524.00||||Standard Time|
|1|Sunday at 11:00|715.00||||Standard Time|
|1|Sunday at 12:00|669.00||||Lunchtime Spike Window|
|1|Sunday at 13:00|714.00||||Lunchtime Spike Window|
|1|Sunday at 14:00|676.00||||Lunchtime Spike Window|
|1|Sunday at 15:00|707.00||||Standard Time|
|1|Sunday at 16:00|706.00||||Standard Time|
|1|Sunday at 17:00|792.00||||Standard Time|
|1|Sunday at 18:00|888.00||||Standard Time|
|1|Sunday at 19:00|892.00||||Standard Time|
|1|Sunday at 2:00|68.00||||Standard Time|
|1|Sunday at 20:00|956.00||||Evening Spike Window|
|1|Sunday at 21:00|876.00||||Evening Spike Window|
|1|Sunday at 22:00|864.00||||Evening Spike Window|
|1|Sunday at 23:00|603.00||||Standard Time|
|1|Sunday at 3:00|43.00||||Standard Time|
|1|Sunday at 4:00|27.00||||Standard Time|
|1|Sunday at 5:00|27.00||||Standard Time|
|1|Sunday at 6:00|33.00||||Standard Time|
|1|Sunday at 7:00|105.00||||Standard Time|
|1|Sunday at 8:00|203.00||||Standard Time|
|1|Sunday at 9:00|343.00||||Standard Time|
|2|Monday at 0:00|322.00||||Standard Time|
|2|Monday at 1:00|134.00||||Standard Time|
|2|Monday at 10:00|1020.00||||Standard Time|
|2|Monday at 11:00|1068.00||||Standard Time|
|2|Monday at 12:00|961.00||||Lunchtime Spike Window|
|2|Monday at 13:00|1013.00||||Lunchtime Spike Window|
|2|Monday at 14:00|1082.00||||Lunchtime Spike Window|
|2|Monday at 15:00|1065.00||||Standard Time|
|2|Monday at 16:00|1076.00||||Standard Time|
|2|Monday at 17:00|974.00||||Standard Time|
|2|Monday at 18:00|915.00||||Standard Time|
|2|Monday at 19:00|933.00||||Standard Time|
|2|Monday at 2:00|64.00||||Standard Time|
|2|Monday at 20:00|1016.00||||Evening Spike Window|
|2|Monday at 21:00|1103.00||||Evening Spike Window|
|2|Monday at 22:00|982.00||||Evening Spike Window|
|2|Monday at 23:00|708.00||||Standard Time|
|2|Monday at 3:00|36.00||||Standard Time|
|2|Monday at 4:00|21.00||||Standard Time|
|2|Monday at 5:00|22.00||||Standard Time|
|2|Monday at 6:00|65.00||||Standard Time|
|2|Monday at 7:00|159.00||||Standard Time|
|2|Monday at 8:00|475.00||||Standard Time|
|2|Monday at 9:00|765.00||||Standard Time|
|3|Tuesday at 0:00|304.00||||Standard Time|
|3|Tuesday at 1:00|157.00||||Standard Time|
|3|Tuesday at 10:00|950.00||||Standard Time|
|3|Tuesday at 11:00|1040.00||||Standard Time|
|3|Tuesday at 12:00|889.00||||Lunchtime Spike Window|
|3|Tuesday at 13:00|1036.00||||Lunchtime Spike Window|
|3|Tuesday at 14:00|1106.00||||Lunchtime Spike Window|
|3|Tuesday at 15:00|1026.00||||Standard Time|
|3|Tuesday at 16:00|1074.00||||Standard Time|
|3|Tuesday at 17:00|956.00||||Standard Time|
|3|Tuesday at 18:00|868.00||||Standard Time|
|3|Tuesday at 19:00|909.00||||Standard Time|
|3|Tuesday at 2:00|80.00||||Standard Time|
|3|Tuesday at 20:00|970.00||||Evening Spike Window|
|3|Tuesday at 21:00|1017.00||||Evening Spike Window|
|3|Tuesday at 22:00|955.00||||Evening Spike Window|
|3|Tuesday at 23:00|685.00||||Standard Time|
|3|Tuesday at 3:00|28.00||||Standard Time|
|3|Tuesday at 4:00|28.00||||Standard Time|
|3|Tuesday at 5:00|24.00||||Standard Time|
|3|Tuesday at 6:00|71.00||||Standard Time|
|3|Tuesday at 7:00|219.00||||Standard Time|
|3|Tuesday at 8:00|515.00||||Standard Time|
|3|Tuesday at 9:00|853.00||||Standard Time|
|4|Wednesday at 0:00|392.00||||Standard Time|
|4|Wednesday at 1:00|175.00||||Standard Time|
|4|Wednesday at 10:00|1029.00||||Standard Time|
|4|Wednesday at 11:00|1046.00||||Standard Time|
|4|Wednesday at 12:00|912.00||||Lunchtime Spike Window|
|4|Wednesday at 13:00|1009.00||||Lunchtime Spike Window|
|4|Wednesday at 14:00|1039.00||||Lunchtime Spike Window|
|4|Wednesday at 15:00|971.00||||Standard Time|
|4|Wednesday at 16:00|1024.00||||Standard Time|
|4|Wednesday at 17:00|958.00||||Standard Time|
|4|Wednesday at 18:00|837.00||||Standard Time|
|4|Wednesday at 19:00|833.00||||Standard Time|
|4|Wednesday at 2:00|81.00||||Standard Time|
|4|Wednesday at 20:00|892.00||||Evening Spike Window|
|4|Wednesday at 21:00|953.00||||Evening Spike Window|
|4|Wednesday at 22:00|872.00||||Evening Spike Window|
|4|Wednesday at 23:00|612.00||||Standard Time|
|4|Wednesday at 3:00|31.00||||Standard Time|
|4|Wednesday at 4:00|33.00||||Standard Time|
|4|Wednesday at 5:00|26.00||||Standard Time|
|4|Wednesday at 6:00|93.00||||Standard Time|
|4|Wednesday at 7:00|210.00||||Standard Time|
|4|Wednesday at 8:00|508.00||||Standard Time|
|4|Wednesday at 9:00|817.00||||Standard Time|
|5|Thursday at 0:00|354.00||||Standard Time|
|5|Thursday at 1:00|167.00||||Standard Time|
|5|Thursday at 10:00|970.00||||Standard Time|
|5|Thursday at 11:00|945.00||||Standard Time|
|5|Thursday at 12:00|963.00||||Lunchtime Spike Window|
|5|Thursday at 13:00|968.00||||Lunchtime Spike Window|
|5|Thursday at 14:00|968.00||||Lunchtime Spike Window|
|5|Thursday at 15:00|916.00||||Standard Time|
|5|Thursday at 16:00|1066.00||||Standard Time|
|5|Thursday at 17:00|893.00||||Standard Time|
|5|Thursday at 18:00|776.00||||Standard Time|
|5|Thursday at 19:00|814.00||||Standard Time|
|5|Thursday at 2:00|74.00||||Standard Time|
|5|Thursday at 20:00|829.00||||Evening Spike Window|
|5|Thursday at 21:00|832.00||||Evening Spike Window|
|5|Thursday at 22:00|850.00||||Evening Spike Window|
|5|Thursday at 23:00|541.00||||Standard Time|
|5|Thursday at 3:00|39.00||||Standard Time|
|5|Thursday at 4:00|31.00||||Standard Time|
|5|Thursday at 5:00|28.00||||Standard Time|
|5|Thursday at 6:00|83.00||||Standard Time|
|5|Thursday at 7:00|217.00||||Standard Time|
|5|Thursday at 8:00|497.00||||Standard Time|
|5|Thursday at 9:00|750.00||||Standard Time|
|6|Friday at 0:00|418.00||||Standard Time|
|6|Friday at 1:00|210.00||||Standard Time|
|6|Friday at 10:00|942.00||||Standard Time|
|6|Friday at 11:00|983.00||||Standard Time|
|6|Friday at 12:00|843.00||||Lunchtime Spike Window|
|6|Friday at 13:00|989.00||||Lunchtime Spike Window|
|6|Friday at 14:00|950.00||||Lunchtime Spike Window|
|6|Friday at 15:00|969.00||||Standard Time|
|6|Friday at 16:00|960.00||||Standard Time|
|6|Friday at 17:00|807.00||||Standard Time|
|6|Friday at 18:00|716.00||||Standard Time|
|6|Friday at 19:00|772.00||||Standard Time|
|6|Friday at 2:00|70.00||||Standard Time|
|6|Friday at 20:00|730.00||||Evening Spike Window|
|6|Friday at 21:00|714.00||||Evening Spike Window|
|6|Friday at 22:00|695.00||||Evening Spike Window|
|6|Friday at 23:00|507.00||||Standard Time|
|6|Friday at 3:00|48.00||||Standard Time|
|6|Friday at 4:00|40.00||||Standard Time|
|6|Friday at 5:00|35.00||||Standard Time|
|6|Friday at 6:00|96.00||||Standard Time|
|6|Friday at 7:00|204.00||||Standard Time|
|6|Friday at 8:00|493.00||||Standard Time|
|6|Friday at 9:00|758.00||||Standard Time|
|7|Saturday at 0:00|312.00||||Standard Time|
|7|Saturday at 1:00|171.00||||Standard Time|
|7|Saturday at 10:00|648.00||||Standard Time|
|7|Saturday at 11:00|701.00||||Standard Time|
|7|Saturday at 12:00|681.00||||Lunchtime Spike Window|
|7|Saturday at 13:00|702.00||||Lunchtime Spike Window|
|7|Saturday at 14:00|670.00||||Lunchtime Spike Window|
|7|Saturday at 15:00|716.00||||Standard Time|
|7|Saturday at 16:00|688.00||||Standard Time|
|7|Saturday at 17:00|694.00||||Standard Time|
|7|Saturday at 18:00|707.00||||Standard Time|
|7|Saturday at 19:00|746.00||||Standard Time|
|7|Saturday at 2:00|66.00||||Standard Time|
|7|Saturday at 20:00|723.00||||Evening Spike Window|
|7|Saturday at 21:00|651.00||||Evening Spike Window|
|7|Saturday at 22:00|543.00||||Evening Spike Window|
|7|Saturday at 23:00|425.00||||Standard Time|
|7|Saturday at 3:00|42.00||||Standard Time|
|7|Saturday at 4:00|25.00||||Standard Time|
|7|Saturday at 5:00|23.00||||Standard Time|
|7|Saturday at 6:00|52.00||||Standard Time|
|7|Saturday at 7:00|104.00||||Standard Time|
|7|Saturday at 8:00|248.00||||Standard Time|
|7|Saturday at 9:00|426.00||||Standard Time|
|2016|December|19.62|51813.38||0.00|Low Season / Slack Period|
|2016|October|51657.53|51793.76||2.99|Peak Month (Strong Seasonality)|
|2016|September|136.23|136.23||0.01|Low Season / Slack Period|
|2017|April|405988.38|1304086.86||0.69|Low Season / Slack Period|
|2017|August|662071.77|3627770.84||1.12|Standard Performance|
|2017|December|861914.46|7144305.03|4392939.87|1.46|Peak Month (Strong Seasonality)|
|2017|February|283621.94|472442.08||0.48|Low Season / Slack Period|
|2017|January|137006.76|188820.14||0.23|Low Season / Slack Period|
|2017|July|578858.58|2965699.07||0.98|Standard Performance|
|2017|June|499827.47|2386840.49||0.85|Low Season / Slack Period|
|2017|March|425656.40|898098.48||0.72|Low Season / Slack Period|
|2017|May|582926.16|1887013.02||0.99|Standard Performance|
|2017|November|1172639.23|6282390.57||1.98|Peak Month (Strong Seasonality)|
|2017|October|764785.28|5109751.34|1380.49|1.29|Peak Month (Strong Seasonality)|
|2017|September|717195.22|4344966.06|526359.11|1.21|Peak Month (Strong Seasonality)|
|2018|April|1156303.91|11535951.32|184.81|1.21|Peak Month (Strong Seasonality)|
|2018|August|996896.15|15738970.55|50.57|1.04|Standard Performance|
|2018|February|979966.23|9226910.67|245.52|1.03|Standard Performance|
|2018|January|1102639.41|8246944.44|704.81|1.15|Standard Performance|
|2018|July|1039880.16|14742074.40|79.64|1.09|Standard Performance|
|2018|June|1020494.29|13702194.24|104.17|1.07|Standard Performance|
|2018|March|1152736.74|10379647.41|170.81|1.21|Peak Month (Strong Seasonality)|
|2018|May|1145748.63|12681699.95|96.55|1.20|Standard Performance|
|2018|September|166.46|15739137.01|-99.98|0.00|Low Season / Slack Period|
**Insights:**
* **Hockey Stick Growth Confirmed:** The cumulative revenue curve shows a classic "hockey stick" shape. The platform started extremely slowly in 2016, but the slope turned sharply upward in mid-2017. It took 7 months to reach the first $1M in total sales, but by 2018, the business was hitting that same $1M milestone every single month.
* **Rapid Scaling Milestones:** The business reached a $5M cumulative revenue mark in October 2017. Incredibly, it only took another 5 months to double that total to $10M by March 2018. This acceleration proves that Olist successfully transitioned from a struggling startup phase into a high-growth scale-up.
* **The November 2017 Catalyst:** November 2017 wasn't just a good month; it was a permanent game-changer. The massive revenue spike during that period permanently steepened the cumulative growth line. After this point, the business never returned to its lower 2017 baselines, maintaining a much higher trajectory throughout 2018.
* **Final Revenue Snapshot:** By the end of the recorded data in August 2018, the platform had generated over $15.7M in total revenue. The trajectory remained strong and steep right until the data cutoff, suggesting that the business was still in a healthy expansion phase.
## Conclusion
**What We Did:**
* Analyzed **Month-over-Month (MoM)** revenue growth to pinpoint hyper-growth phases versus periods of stabilization.
* Calculated a **Seasonality Index** to determine the baseline importance of each month in the yearly cycle.
* Performed a **Year-over-Year (YoY)** comparison to measure the scale of expansion between 2017 and 2018.
* Conducted an **Intra-day Analysis** of order volumes by hour and day to identify peak shopping windows.
* Tracked **Cumulative Revenue** to visualize the business’s overall growth trajectory.
**What We Obtained (Key Findings):**
* **The "Hockey Stick" Effect:** Olist successfully moved from a slow startup phase in 2016 to a high-speed scale-up in 2017. The cumulative revenue trajectory turned sharply exponential in late 2017, reaching a total of over $15.7M by the end of the study period.
* **Maturity & Stability:** While 2017 was a year of chaotic growth, 2018 showed a mature plateau. Revenue stabilized at a predictable baseline of ~$1M per month, indicating that the platform successfully retained its market share.
* **November is the Revenue Engine:** November is the undisputed peak of the year. With a seasonality index of 1.98, it brings in nearly double the revenue of an average month, confirming that Black Friday is the most critical event for Olist’s financial health.
* **The Weekday Workflow:** Shopping is a "workweek" activity. Monday is the highest-volume day, and sales consistently dip during the weekends.
* **Dual-Spike Daily Behavior:** Customers follow a predictable daily routine across all states, with a primary **Lunchtime Spike (12 PM - 2 PM)** and a secondary **Evening Spike (8 PM - 10 PM)**. Monday at 9 PM represents the absolute peak of customer activity.