Felo's Portfolio - Strategics Insights

Strategic Insights Generation for an e-commerce Company

This case was made it during the course "My First Experience as a Data Analyst" and the idea was to extract useful information from scratch without know nothing about data. The target site for analysis is an e-commerce site that sells organic products.

Area: Data Analysis

Applied Technologies:

SQL, SQLite.

Key Concepts:

The Business Analysis Framework goes from the general to the specific in analyzing a company's data. It begins with an overview of total sales, then analyzes sales trends, then drills down to individual products, and finally examines customer and order behavior.

The Business, Question, Query Framework consists of attacking items by first understanding what the scope of the business is, that is, the specific aspect of the business that we are going to analyze at that time, then a business question that we want to answer is raised and finally a SQL query is built that gives us the answer to that question (insight).

Pyramid Framework: The key concept of this framework is to reverse the order in which the results of our work are presented. When we begin our work, we start with the data, that is, the base of the pyramid. From this data, we obtain conclusions that we call "evidence." This represents a second step in the pyramid. Then, by converting this "evidence," we obtain insights, which represent the peak of the pyramid.

Lesson Learned:

Use of technologies like SQL and SQLite.
Understanding and use of frameworks to extract evidence and insights directly from data. Business Analysis Framework and "Business, Question, Query" Framework.
Understanding and use of a framework and a document to display results: Pyramid Framework and Strategic Business Analysis Document.

Details of the Case:

To simulate a company's database client-server, the SQLite ODBC driver was used through its online version.
A tickets table contain all the information about sales, including these fields: customer_id, order_id, date, time, department_id, product_id, section_id, product_name, unit_price, quantity, total_price.
Analyzing the data at first glance with this simple query:

SELECT * FROM tickets LIMIT 100;

I noticed that the table is not organized by customer_id but by order_id and product_id. I realized this because the same customer appears in more than one row/sale.

Used Methodology:

As a lookup methodology, I used the Business Analysis Framework and the Business, Question, Query Framework.

To present the results to the management or marketing people I completed the Strategic Business Analysis Document using the Pyramid Framework.

Results:

The Strategic Business Analysis document was completed.

Strategic Business Analysis Document.pdf

Detailed Work:

1) Sales and Trends

a) Business Aspect: Get an overview of total sales.

Question: What is the total revenue generated by the business?

Query: SELECT SUM(total_price) AS total_revenue FROM tickets;

Results:

b) Business Aspect: Sales trends over time.

Question: What has been the monthly revenue trend?

Query: SELECT strftime('%Y-%m', date) AS month,

SUM(total_price) AS monthly_revenue

FROM tickets

GROUP BY month

ORDER BY month;

Results:

2) Products and Sections

a) Business Aspect: Analysis by department.

Question: What is the sales performance of each department?

Query: SELECT department_id, SUM(total_price) AS department_sales

FROM tickets

GROUP BY department_id

ORDER BY department_sales DESC;

Results:

b) Business Aspect: Analysis by section.

Question: How are sales distributed among the different sections?

Query: SELECT section_id, SUM(total_price) AS section_sales

FROM tickets

GROUP BY section_id

ORDER BY section_sales DESC;

Results:

c) Business Aspect: Product analysis.

Question: What are the 10 best-selling products by quantity?

Query: SELECT product_name, SUM(quantity) AS total_sold

FROM tickets

GROUP BY product_name

ORDER BY total_sold DESC

LIMIT 10;

Results:

d) Business Aspect: Product analysis.

Question: Which 10 products generate the most revenue?

Query: SELECT product_name, SUM(total_price) AS product_revenue

FROM tickets

GROUP BY product_name

ORDER BY product_revenue DESC

LIMIT 10;

Results:

3) Customer and Orders

a) Business Aspect: Customer behavior.

Question: Who are the 20 customers who buy the most in terms of revenue?

Query: SELECT customer_id, SUM(total_price) AS customer_revenue

FROM tickets

GROUP BY customer_id

ORDER BY customer_revenue DESC

LIMIT 20;

Results:

b) Business Aspect: Customer behavior.

Question: What is the average purchase per customer?

Query: SELECT AVG(total_customer_purchase) AS

average_purchase_per_customer

FROM ( SELECT customer_id, SUM(total_price) AS

total_customer_purchase

FROM tickets

GROUP BY customer_id ) subquery; *

* I took all the purchases from each client with their total spending and then I made an average of all of them.

Results:

c) Business Aspect: Order analysis.

Question: How many total orders have been placed?

Query: SELECT COUNT(DISTINCT order_id) AS total_orders

FROM tickets;

Results:

d) Business Aspect: Order analysis.

Question: What is the average value (spending) per order?

Query: SELECT AVG(total_order) AS average_order_value

FROM ( SELECT order_id, SUM(total_price) AS total_order

FROM tickets

GROUP BY order_id ) subquery; *

* The sales for each order are calculated in an intermediate table (total_price) and then the average for all orders is calculated.

Results: