3. Macro Performance Analysis

Introduction

In this section, we will answer the 23 business questions that we defined in the previous section using SQL queries and data analysis techniques. We will use the star schema that we created in the previous section to write SQL queries. Each question will be answered with a SQL query that retrieves the relevant data from the database, and we will also provide insights and interpretations of the results to help us understand the implications of our findings for the business.

The questions are designed in a way that they will help us to get business insights on the following domains:

1. Makro Performance Analysis: Here we will analyze the overall performance of the business by looking at metrics such as total sales, profit, and customer count. We will also analyze the performance of different product categories and subcategories to identify trends and patterns in customer behavior. Questions 1-5 fall under this domain.
2. Customer Behavior Analysis: In this domain, we will analyze customer behavior by looking at metrics such as customer demographics, purchasing patterns, and customer loyalty. We will also analyze the impact of different factors such as discounts and promotions on customer behavior. Questions 6-10 fall under this domain.
3. Product Performance Analysis: Here we will analyze the performance of different products and product categories by looking at financial metrics such as Gross Profit, Gross Margin, Product Margin, and Sales Amount. We will also analyze the impact of different factors such as discounts and promotions on product performance. Questions 11-15 fall under this domain.
4. Sales Reason and Customer Preference Analysis: In this domain, we will analyze the reasons behind sales and customer preferences by looking at metrics such as sales reasons, importance of promotional discounts, and preference of product categories. Questions 16-19 fall under this domain.
5. Regional and Logistic Operations Analysis: Here we will analyze the performance of the business in different regions by looking at the Regional Preferences, Tax and Freight Costs, and Regional Bias for specific product categories. SLA and delivery performance will also be analyzed in this domain. Questions 20-23 fall under this domain.

For better clarity and organization, I will give a separate page for each domain and answer the relevant questions in that page. This way, we can focus on specific areas of analysis and gain deeper insights into each domain. As this is the first page of the SQL analysis section, we will start with the first domain, which is Makro Performance Analysis, and answer the first 5 questions related to that domain.

Macro Performance Analysis

Gross Profit and Gross Margin over Time

1. What is the overall Gross Profit and Gross Margin for the B2C channel, and how has it trended year over year?

Solution Method: To answer this question, we will write a SQL query that retrieves the Gross Profit and Gross Margin for the B2C channel from the fact table, and then we will group the results by year to analyze the trend over time. We will also calculate the year-over-year growth rate for both Gross Profit and Gross Margin to understand how they have changed over time. This requires simple select statements grouped by year and filtered by the B2C channel.

select
	c.CalendarYear as [year],
	round(sum(fis.SalesAmount),2) as total_revenue,
	round(sum(fis.TotalProductCost),2) as total_cost,
	round(sum(fis.SalesAmount) - sum(fis.TotalProductCost),2) as gross_profit,
	cast(100.0 * (sum(fis.salesamount) - sum(fis.TotalProductCost)) /
		nullif(sum(fis.SalesAmount), 0) as decimal(5,2)) as gross_margin_pct
from FactInternetSales fis
left join Calendar c
	on c.DateKey = fis.OrderDateKey
where c.CalendarYear not in (2010,2014)
group by c.CalendarYear
order by [year] asc;

Results:

Insights:

The overall Gross Profit for the B2C channel has shown a significant increase from 2011 to 2013, with a growth rate of approximately 138% from 2011 to 2013. The Gross Margin has also improved over the years, increasing from 40.20% in 2011 to 41.37% in 2013. This indicates that the B2C channel has been able to generate more profit from its sales while maintaining a healthy margin, which is a positive sign for the business. The increase in Gross Profit and Gross Margin could be attributed to various factors such as increased sales volume, better pricing strategies, or improved cost management. Further analysis would be needed to identify the specific drivers behind this growth.

Year-over-Year (YoY) Revenue Growth Rate

2. What is the Year-over-Year (YoY) revenue growth rate for each month?

Solution Method: To answer this question, we will write a SQL query that retrieves the revenue for each month from the fact table, and then we will group the results by month and year to calculate the Year-over-Year (YoY) revenue growth rate. We will use the LAG function to compare the revenue of each month with the same month in the previous year to calculate the growth rate. In order to prevent any duplication problem, we should use Common Table Expressions (CTEs) which helps us to organize our query and make it more readable as well as to avoid any potential issues with duplicate data when calculating the YoY growth rate.

with calculations as (
select
	c.CalendarYear as [year], 
	c.MonthNumberOfYear as month_number,
	c.MonthName as month_name,
	round(sum(fis.SalesAmount),2) as curr_total_revenue,
	lag(sum(fis.SalesAmount), 1) over (partition by c.MonthNumberOfYear 
		order by c.CalendarYear asc) as prev_total_revenue
from FactInternetSales fis 
join Calendar c 
	on c.DateKey = fis.OrderDateKey
	and c.CalendarYear not in (2010,2014)
group by 
	c.CalendarYear, 
	c.MonthNumberOfYear,
	c.MonthName
)

select
	[year], 
	month_number, 
	round(curr_total_revenue,2), 
	round(prev_total_revenue,2), 
	cast(100.0 * (curr_total_revenue - prev_total_revenue) / 
		nullif(prev_total_revenue, 0) as decimal(7,2)) as revenue_growth_pct
from calculations cal
order by [year], month_number;

Results:

Insights: The Year-over-Year (YoY) revenue growth rate for each month shows significant fluctuations over the years. In 2012, there was a decline in revenue growth for most months compared to 2011, with some months experiencing a negative growth rate. However, in 2013, there was a substantial increase in revenue growth for all months compared to 2012, with some months experiencing a growth rate of over 200%. This indicates that the business experienced a significant recovery and growth in revenue in 2013 after a challenging year in 2012. The fluctuations in revenue growth could be attributed to various factors such as changes in market conditions, customer behavior, or the effectiveness of marketing and sales strategies. Or maybe the company launched better and became a more competitive player in the market in 2013, which led to a significant increase in revenue growth.

Seasonality of Sales and Quarterly Revenue Drivers

3. What is the monthly seasonality of our sales, and which quarter drives the most revenue historically?

Solution Method: To answer this question, I have written a SQL query that calculates the monthly revenue for each month and quarter, and then identifies the quarter that drives the most revenue historically. The query uses Common Table Expressions (CTEs) to organize the calculations and make it easier to read. The first CTE calculates the monthly revenue for each month and quarter, while the second CTE calculates the total revenue for each quarter. Finally, we calculate the growth rate of each quarter compared to the previous quarter to identify trends in seasonality. To prevent any duplication and wrong calculations, we have used CTEs in a sequence in which the financial metrics are derived first, then grouped by quarter, and then grouped with window functions get the previous quarter’s revenue for growth rate calculation.

with revenue_calculations as (
	select
		c.CalendarQuarter as [Quarter], 
		c.MonthNumberOfYear as month_number,
		c.MonthName as [Month],
		coalesce(sum(fis.SalesAmount),0) as revenue
	from FactInternetSales fis
	join Calendar c 
		on c.DateKey = fis.OrderDateKey
		and c.CalendarYear not in (2010, 2014)
	group by 
		c.CalendarQuarter, 
		c.MonthNumberOfYear,
		c.MonthName
),
curr_quarter_revenue_calc as (
	select
		[Quarter], 
		month_number,
		[Month], 
		revenue as monthly_revenue, 
		sum(revenue) over (partition by [Quarter]) as curr_quarter_revenue
	from revenue_calculations
),
growth_calc as (
	select
			[Quarter], 
			month_number,
			[Month], 
			monthly_revenue, 
			curr_quarter_revenue,
			lag(curr_quarter_revenue,3) over (order by [Quarter], month_number) as prev_quarter_revenue
	from curr_quarter_revenue_calc 
)

select 
	*,
	cast ((curr_quarter_revenue - prev_quarter_revenue) / nullif(prev_quarter_revenue,0) as decimal(10,2))
		as Q_revenue_growth_pct
from growth_calc
order by 
	[Quarter], 
	month_number;

Results:

Insights: The monthly seasonality of our sales shows that the revenue tends to increase in the second and fourth quarters of the year, with the fourth quarter driving the most revenue historically. The growth rate of the second quarter compared to the first quarter is approximately 29%, while the growth rate of the fourth quarter compared to the third quarter is approximately 19%. This indicates that there is a significant increase in sales during these quarters, which could be attributed to various factors such as seasonal demand, holidays, or promotional activities. Understanding this seasonality can help us to plan our inventory, marketing campaigns, and staffing levels more effectively to capitalize on these peak periods and maximize our revenue.

Average Order Value (AOV) vs Total Order Volume (TOV)

4. What is the Average Order Value (AOV) for the B2C channel, and how has it trended over time?

Solution Method: This question is quite straitforward. We should calculate the Average Order Value (AOV) for the B2C channel by dividing the total sales amount by the number of distinct orders. We will group the results by year and month to analyze the trend over time. The query uses a simple aggregation to calculate the total sales amount and count of distinct orders, and then divides these two metrics to get the AOV for each month and year. I did not include the years - 2010 and 2014 in the analysis as they are incomplete years in our dataset, which could skew the results and provide an inaccurate representation of the AOV trend over time. By excluding these years, we can ensure that our analysis is based on complete data and provides a more accurate picture of the AOV trend for the B2C channel.

select
	c.CalendarYear calendar_year, 
	c.MonthNumberOfYear as month_number,
	count(distinct fis.SalesOrderNumber) as num_distinct_orders, 
	sum(fis.OrderQuantity) as num_items, 
	cast(sum(fis.SalesAmount) / nullif(count (distinct fis.SalesOrderNumber),0) as float)
		as avg_order_value
from FactInternetSales fis
join Calendar c
	on c.DateKey = fis.OrderDateKey
	and c.CalendarYear not in (2010, 2014)
group by 
	c.CalendarYear,
	c.MonthNumberOfYear
order by 
	calendar_year,
	month_number;

Results:

Insights: The Average Order Value (AOV) has shown a significant decline from 2011 to 2013. In 2011, the AOV was consistently above $3000, while in 2012, it dropped to around$1700-$1900, and in 2013, it further declined to around$500-$800. This indicates that customers in the B2C channel are spending less per order over time. The decline in AOV could be attributed to various factors such as changes in customer behavior, increased competition, or the effectiveness of marketing and sales strategies. It is important for the business to analyze the underlying reasons for this decline and take appropriate actions to improve the AOV, such as offering promotions or bundling products to encourage customers to spend more per order. Conversely, the increase in the number of distinct orders and items sold over the years suggests that while customers are spending less per order, they are making more purchases overall, which could indicate a shift in customer behavior towards more frequent but smaller purchases. Toward the end of 2013, customers tend to buy more items per order, but spend less on average per order, which could indicate a shift in customer behavior towards more frequent but smaller purchases. Overall, that compensates for the decline in AOV and could be a positive sign for the business as it indicates that customers are still engaged and making purchases, even if they are spending less per order. However, it is important to monitor this trend closely and take appropriate actions to ensure that the business continues to grow and remain competitive in the market.

Year-to-Date (YTD) Revenue and Profitability

5. What is the Year-to-Date (YTD) revenue and profitability for the current year compared to the previous year?

Solution Method: I solved this question by using window function sum with order by day_number to calculate the cumulative revenue for each day of the year. The financial metrics are calculated in a CTE on a daily basis and then for the cumulative ytd calculations, sum window function is used.

with revenue_calculation as (
	select
		c.CalendarYear as calendar_year,
		c.MonthNumberOfYear as month_number,
		c.DayNumberOfYear as day_number,
		count(distinct fis.SalesOrderNumber) as num_orders,
		coalesce(sum(fis.salesamount), 0) as daily_revenue
	from FactInternetSales fis 
	right join Calendar c 
		on c.DateKey = fis.OrderDateKey
	where c.CalendarYear = 2013
	group by 
		c.CalendarYear,
		c.MonthNumberOfYear,
		c.DayNumberOfYear		
)

select
	*,
	sum(daily_revenue) over (order by day_number) as cumulative_revenue
from revenue_calculation r
order by 
	calendar_year,
	month_number,
	day_number

Results:

Insights: The Year-to-Date (YTD) revenue for the current year (2013) shows a steady increase over time, with cumulative revenue reaching approximately $7.47 million by the end of July. The profitability trend can be analyzed by comparing the daily revenue and cumulative revenue, which indicates that the business is generating consistent revenue growth throughout the year. The significant increase in revenue in June and July could be attributed to seasonal factors, promotional activities, or increased customer demand during these months. It is important for the business to continue monitoring the YTD revenue and profitability trends to make informed decisions about inventory management, marketing strategies, and resource allocation to sustain growth and maximize profitability.

Conclusion

The first domain is focused on analyzing the revenue growth, seasonality of sales, average order value, and year-to-date revenue and profitability for the B2C channel. According to the results of the SQL queries we derived following insights:

• The Year-over-Year (YoY) revenue growth rate for each month shows significant fluctuations over the years, with a substantial increase in revenue growth in 2013 compared to 2012.
• The monthly seasonality of sales indicates that the second and fourth quarters of the year drive the most revenue historically, with significant growth rates compared to the previous quarters.
• The Average Order Value (AOV) for the B2C channel has shown a significant decline from 2011 to 2013, while the number of distinct orders and items sold has increased, indicating a shift in customer behavior towards more frequent but smaller purchases.
• The Year-to-Date (YTD) revenue for the current year (2013) shows a steady increase over time, with significant growth in June and July, which could be attributed to seasonal factors or increased customer demand during these months. Overall, these insights provide valuable information for the business to make informed decisions about inventory management, marketing strategies, and resource allocation to sustain growth and maximize profitability in the B2C channel. It is important for the business to continue monitoring these trends and take appropriate actions to ensure continued growth and competitiveness in the market.