This analysis is aimed at addressing the issue of overall wellbeing of the business, extracting meaningful insights of the business at large, such as:

•A) how the sales are doing,

•B) knowing the average quantity purchase per customer,

•C) The most frequently used payment method,

•D) knowing if the business is making profit or not,

•E) knowing the total production cost,

•F) the customer with the highest purchase

The dataset has data on orders placed by customers on a grocery delivery application designed with an assumption that the orders are placed by customers living in the states of United States.

Columns Summary invoice_no: Invoice number associated with each transaction.

customer_id: Identifier for each customer.

gender: Gender of the customer (assumed to be binary: male/female).

age: Age of the customer.

category: Product category associated with the transaction.

quantity: Quantity of products purchased in each transaction.

selling_price_per_unit: Selling price per unit of the product.

cost_price_per_unit: Cost price per unit of the product.

payment_method: Method used for payment in the transaction.

region: Geographic region associated with the transaction.

state: State where the transaction took place.

shopping_mall: shopping mall where the transaction occurred.

OBJECTIVES:

The objectives of this analysis are to give insights into the overall performance of the business to make informed, data driven decisions based on the information derived from this data set through the following;

Key questions to gain meaningful insight:

•Get the total revenue of the different stores

•Get the average quantity, and the categories

•Get the total cost of each product and the product names.

•Find the gross profit.

•What is the total production cost.

•Get the most frequently used payment channel.

•What are the different sales across the regions.

•Retrieve the customer with the highest purchase or highest sales.

Tool used: PostgressSQL

First, i cleaned my data on excel then Safed it as a csv file type uploaded it into postgressSQL and created a table to house the data;

CREATE TABLE Different_stores (invoice_no VARCHAR(20) PRIMARY KEY, invoice_date DATE, customer_id VARCHAR(50), gender VARCHAR(10),age INTEGER,category VARCHAR, quantity INTEGER,selling_price_per_unit DECIMAL, cost_price_per_unit DECIMAL,payment_method VARCHAR(50), region VARCHAR(20,state VARCHAR,shopping_mall VARCHAR(200) );

Uploaded CSV file into the table

COPY Different_stores FROM ‘C:\Program Files\PostgreSQL\15\Different_stores_dataset.csv’ WITH (FORMAT CSV, HEADER true);

— Get the total revenue of the business

SELECT sum(d.selling_price_per_unit*d.quantity) as Totalrevenue FROM Different_stores AS d;

/This is the total revenue of all the different stores across the different rigions, amount $ 251,565,247.98/

— Get the average order quantity, and the categories.

SELECT ROUND(AVG(d.quantity),2),d.category AS Averagequantity FROM Different_stores AS d GROUP BY category;

/This shows the average product category customers purchased more/

— -Get the total cost of each product and the product names.

SELECT d.category, SUM(d.cost_price_per_unit) AS Totalcostpereachpproductcateory FROM Different_stores AS d GROUP BY d.category ;

/These are the various total cost incurred for each of the product categories/

— -Find the gross profit. SELECT SUM(selling_price_per_unit * quantity) — SUM(cost_price_per_unit) AS grossprofit FROM Different_stores;

/* The total revenue which is $251,565,247.98, minus total production cost which is $60,182,808.6060, gives us our Gross profit which is $ 191,382,493.3740*/

— -What is the total production cost.

SELECT SUM(cost_price_per_unit) as totalproductioncost FROM Different_stores;

/it took $ 60,182,808.6060 to produce goods worth $251,565,247.98/

— -Get the most frequently used payment channel.

SELECT d.payment_method, COUNT(payment_method) AS frequency FROM Different_stores AS d GROUP BY payment_method ORDER BY frequency DESC LIMIT 1;

/Cash is the most preferred payment method customers prefer, as we can see the number of times customers purchased goods using the cash method is the highest which is 44447 times./

— -What are the different sales across the regions.

SELECT state,region, SUM(selling_price_per_unit * quantity) AS sales FROM Different_stores GROUP BY state,region;

SELECT state,region, SUM(selling_price_per_unit * quantity) AS sales FROM Different_stores GROUP BY state,region ORDER BY sales DESC LIMIT 1;

/* These are the various sales across the state and region. Going further, we can see the state and region with the highest sales revenue, California state, west region with a total sales revenue of $ 48,014,602.55.*/

— -Retrieve the customer with the highest purchase amount or highest sales.

SELECT customer_id, MAX (quantity * selling_price_per_unit) AS highest_purchaseamount FROM Different_stores GROUP BY customer_id ORDER BY highest_purchaseamount DESC LIMIT 1;

/We can now see the customer with customer Id C189493 with the highest purchase amount of $ 47,250 or as the highest sales per customer/

Conclusion & Recommendation

By the end of my analysis, the objectives of this analysis were achieved. With the key questions above, I was able to achieve insight with the provided data information.

The overall business performance is excellent, comparing the total purchase to the total sales revenue, I will say the business is on the right track. However, i recommend we have product reviews and customer satisfactory response to avoid customer churn, given that every business aims for a large market share to maximize revenue turnover and encourage customer to use other payment channel other than cash to Forster the financial growth of the business.