Sales and Financial Analysis of Retail Chain

Context

This case was an analysis of retail store sales based on a sales fact database and a financial table. The case originated from a real company, but the data was altered for data governance reasons.

Step 1: Understanding the data source

In this case, the data originated from an SQL database. Below, we can see the two fact tables involved in the analysis.

Table f_financeiro

Columns

ID: The entry id.
DataVenda: The date of sale.
tipo: Entry type. "Receita" means Income and "Despesa" means "Expense" .
valor: Value transaction
grupo: Classification of the entry.
subgrupo: Subgroup of the entry.
conta: Account classified in the chart of accounts.
id_cliente: Customer id.
loja: Store name.
endereco: Customer Address.
idade: Customer Age.
cpf_cnpj: The customer document id.
nacionalidade: Customer Nationality.
email: Customer email.
telefone: Customer phone number.
celular: Customer mobile phone number.
estado_civil: Marital status.
numero_dependentes: Number of dependents.
profissao: Occupation.
historico_compras: Purchase History.
numero_cartao_credito: Credit Card Number.
numero_conta_bancaria: Bank Account Number.
limite_credito: Credit Limit.
numero_identidade: Identity Number.
cidade: Customer City.
cep: Postal Code.
numero_filhos: Number of Children.
tipo_sanguineo: Blood Type.
carteira_motorista: Driver's license number.
tipo_renda: Type of income.
grau_instrucao: Educational Level.
etnia: Ethnicity.
religiao: Customer Religion.
hobbies: Customer hobbies.

Table f_vendas

Columns

ID: The sale id.
DataVenda: The sale date.
Produto: The product name.
Quantidade:Quantity sold.
PrecoUnitario: Unit price.
ValorTotal: Total Amount.
Desconto: Discount.
Frete: Freight Amount.
ID_Cliente: The customer id.
NomeVendedor: The seller id.
NomeLoja: The store name.
NomeCliente: The customer name.
EnderecoCliente: The address id.
TelefoneCliente: The customer phone number.
EmailCliente: The customer email.
StatusCliente: The customer status. Can be "VIP" or "Common".

Step 2: Business Problem

The customer wants to know, in the simplest way possible without complexity, the following points:

Sales:

Which stores generate the highest revenue?
Which stores offer the most discounts?
Which salespeople generate the highest revenue?
Which salespeople offer the most discounts?
Total revenue over time?
How is my revenue compared to the previous year?
Is my revenue increasing or decreasing?
How is the shipping fee collection?
Which products sell the most?

Financial:

How is my Revenue vs. Expenses?
What is the performance of the stores?
Are we increasing or decreasing revenues and expenses?
I want to better understand the sources of revenue and expenses.

Step 3: Answering the business questions.

To answer the business questions, I performed the analysis in SQL by modeling the fact and dimension tables. Below, I provide the code.

Sql Queries f_vendas:

-- 1 Which stores generate the most revenue?
                                SELECT 
                                    fv.StoreName,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS TotalRevenue
                                FROM dbo.sales_table fv
                                GROUP BY fv.StoreName ORDER BY TotalRevenue DESC
                                
                                -- 2 Which stores offer the most discounts?
                                SELECT
                                    fv.StoreName,
                                    FORMAT(SUM(fv.Discount), 'N', 'en-us') AS TotalDiscount
                                FROM dbo.sales_table fv
                                GROUP BY fv.StoreName ORDER BY TotalDiscount DESC
                                
                                -- 3 Which salespeople generate the most revenue?
                                SELECT 
                                    fv.SalespersonName,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS SalespersonRevenue
                                FROM dbo.sales_table fv
                                GROUP BY fv.SalespersonName ORDER BY SalespersonRevenue DESC
                                
                                -- 4 Which salespeople offer the most discounts?
                                SELECT
                                    fv.SalespersonName,
                                    FORMAT(SUM(fv.Discount), 'N', 'en-us')  AS SalespersonDiscount
                                FROM dbo.sales_table fv
                                GROUP BY fv.SalespersonName ORDER BY SalespersonDiscount DESC
                                
                                -- 5 Total revenue over time by Year
                                SELECT 
                                    YEAR(fv.SaleDate) AS Year,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS TotalRevenue
                                FROM dbo.sales_table fv
                                GROUP BY YEAR(fv.SaleDate) ORDER BY Year ASC
                                
                                -- 5.1 Total revenue over time by Month
                                SELECT 
                                    MONTH(fv.SaleDate) AS Month,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS TotalRevenue
                                FROM dbo.sales_table fv
                                GROUP BY MONTH(fv.SaleDate) ORDER BY Month ASC
                                
                                -- 5.2 Total revenue over time by Year and Month
                                SELECT 
                                    YEAR(fv.SaleDate) AS Year,
                                    MONTH(fv.SaleDate) AS Month,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS TotalRevenue
                                FROM dbo.sales_table fv
                                GROUP BY YEAR(fv.SaleDate), MONTH(fv.SaleDate) ORDER BY Year ASC, Month ASC
                                
                                -- 6 Revenue compared to the previous year
                                SELECT 
                                    YEAR(fv.SaleDate) AS Year,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS TotalRevenue,
                                    FORMAT(LAG(SUM(fv.TotalValue)) OVER(ORDER BY YEAR(fv.SaleDate)), 'N', 'en-us') AS PreviousYear,
                                    ((LAG(SUM(fv.TotalValue)) OVER(ORDER BY YEAR(fv.SaleDate))) / SUM(fv.TotalValue)) - 1 AS YearOverYearChange
                                FROM dbo.sales_table fv
                                GROUP BY YEAR(fv.SaleDate) ORDER BY Year ASC
                                
                                -- 7 Shipping fee collection by Store
                                SELECT  
                                    fv.StoreName,
                                    SUM(fv.ShippingFee) AS ShippingFee
                                FROM dbo.sales_table fv
                                GROUP BY fv.StoreName ORDER BY ShippingFee DESC
                                
                                -- 8 Top selling products
                                SELECT TOP 10
                                    fv.Product,
                                    FORMAT(SUM(fv.TotalValue), 'N', 'en-us') AS ProductRevenue
                                FROM dbo.sales_table fv
                                GROUP BY fv.Product ORDER BY ProductRevenue DESC;

Sql Queries f_financeiro:

SELECT TOP 10 *
                                FROM dbo.f_financeiro

-- Creating a view
                                CREATE VIEW tb_f_financeiro
                                AS
                                SELECT 
                                    ff.id,
                                    ff.date,
                                    ff.type,
                                    ff.value,
                                    ff.group,
                                    ff.subgroup,
                                    ff.store
                                FROM DBO.f_financeiro ff

-- 1 How is revenue versus expenses?
                                SELECT
                                    ff.type,
                                    CASE WHEN
                                        ff.type = 'Expense' THEN FORMAT( SUM(ff.value) * -1, 'N', 'en-us')
                                        ELSE
                                        FORMAT( SUM(ff.value) , 'N', 'en-us')
                                    END
                                FROM DBO.f_financeiro ff
                                GROUP BY ff.type

-- 2 How is the result of the stores?
                                SELECT
                                    ff.store,
                                    SUM( ff.value ) 
                                FROM DBO.f_financeiro ff
                                GROUP BY ff.store

-- 3 Analysis of expenses and revenue over time
                                SELECT
                                    YEAR( ff.date ) AS Year,
                                    MONTH( ff.date ) AS Month,
                                    ff.type, 
                                    SUM( ff.value ) AS Value 
                                FROM dbo.f_financeiro ff
                                GROUP BY YEAR( ff.date ), MONTH( ff.date ), ff.type ORDER BY Year ASC, Month ASC

-- 4 Analysis of revenue and expenses sources
                                SELECT
                                    ff.type,
                                    ff.group,
                                    FORMAT( SUM( ff.value ), 'N', 'en-us' ) AS Total_Value
                                FROM dbo.f_financeiro ff
                                GROUP BY ff.type, ff.group ORDER BY ff.type DESC

Step 4: Data Modeling

The modeling for this dashboard was quite simple; I just broke down the fact table into dimensions, thus forming a star schema modeling.

Step 5: Data Visualization

In the final stage, the type of visualization was combined with the end client who wanted a simple and intuitive project

Pane 1

Pane 2

DAX Measures