Sales Analytics (Power- BI)

 

✅ 1. Get Your Data

• Open Power BI Desktop.

• Click Home → Get Data → Excel/CSV/SQL Server/Web, depending on your data source.

• Load your sales data file (e.g., Excel file with Orders, Customers, Products tables).

---

✅ 2. Clean and Transform Data (Power Query Editor)

• Click Transform Data to open the Power Query Editor.

• Perform basic data cleaning:

  • Remove nulls or errors.

  • Rename columns for clarity.

  • Change data types (e.g., Date, Currency).

  • Create new columns if needed (e.g., Year, Month, Profit = Sales - Cost).

• Click Close & Apply.

---

✅ 3. Create Data Model

• Use the Model View to build relationships between tables:

  • Orders → Customers (CustomerID)

  • Orders → Products (ProductID)

  • Orders → Regions (RegionID)

• Ensure relationships are one-to-many and directional flow is correct.

---

✅ 4. Build Calculated Columns & Measures (DAX)

Examples:

DAX
Total Sales = SUM(Orders[Sales])
Total Profit = SUM(Orders[Profit])
Profit Margin = DIVIDE([Total Profit], [Total Sales])

You’ll use these measures in your visuals.

---

✅ 5. Design the Report Page

Drag visuals onto the canvas:

• Card for KPIs (Total Sales, Profit, Margin).

• Line Chart for sales trend over time.

• Bar Chart for sales by category/product.

• Map or Filled Map for sales by region.

• Donut/Pie Chart for sales by segment.

---

✅ 6. Add Slicers for Interactivity

• Add Slicers (dropdown filters) for:

  • Date

  • Category

  • Country/Region

This lets users interact with the data.

---

✅ 7. Format the Report

• Use consistent colors (corporate branding or default themes).

• Rename visuals and axes.

• Enable data labels, titles, tooltips.

• Align and size visuals neatly.

---

✅ 8. Publish to Power BI Service

• Save the file (.pbix).

• Click Publish → Power BI Service.

• Choose your workspace.

---

✅ 9. Create Dashboard (Optional in Power BI Service)

• Pin visuals to a new dashboard.

• Share it with your team or manager.

---

✅ 10. Schedule Refresh (Optional)

• In Power BI Service, set up refresh frequency if using cloud-based or dynamic sources.

Customer-Behavior-Analysis (SQL)

 Customer-Behavior-Analysis

This project and the data used was part of a case study which can be found here. It focuses on examining patterns, trends, and factors influencing customer spending in order to gain insights into their preferences, purchasing habits, and potential areas for improvement in menu offerings or marketing strategies in a dining establishment.

Background

Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen. Danny’s Diner is in need of your assistance to help the restaurant stay afloat - the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.

Problem Statement

Danny wants to use the data to answer a few simple questions about his customers, especially about their visiting patterns, how much money they’ve spent and also which menu items are their favorite. Having this deeper connection with his customers will help him deliver a better and more personalized experience for his loyal customers.

He plans on using these insights to help him decide whether he should expand the existing customer loyalty program - additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.

Danny has provided you with a sample of his overall customer data due to privacy issues - but he hopes that these examples are enough for you to write fully functioning SQL queries to help him answer his questions!

Entity Relationship Diagram

Skills Applied

• Window Functions
• CTEs
• Aggregations
• JOINs
• Write scripts to generate basic reports that can be run every period

Questions Explored

1. What is the total amount each customer spent at the restaurant?
2. How many days has each customer visited the restaurant?
3. What was the first item from the menu purchased by each customer?
4. What is the most purchased item on the menu and how many times was it purchased by all customers?
5. Which item was the most popular for each customer?
6. Which item was purchased first by the customer after they became a member?
7. Which item was purchased just before the customer became a member?
8. What is the total items and amount spent for each member before they became a member?
9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?
10. In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi - how many points do customer A and B have at the end of January?

Some interesting queries

Q5 - Which item was the most popular for each customer?

WITH customer_popularity AS (
    SELECT s.customer_id, m.product_name, COUNT(*) AS purchase_count,
        DENSE_RANK() OVER (PARTITION BY s.customer_id ORDER BY COUNT(*) DESC) AS rank
    FROM dbo.sales s
    INNER JOIN dbo.menu m ON s.product_id = m.product_id
    GROUP BY s.customer_id, m.product_name
)
SELECT customer_id, product_name, purchase_count
FROM customer_popularity
WHERE rank = 1;

Q10 - In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi - how many points do customer A and B have at the end of January?

SELECT s.customer_id, SUM(
    CASE 
        WHEN s.order_date BETWEEN mb.join_date AND DATEADD(day, 7, mb.join_date) THEN m.price*20
        WHEN m.product_name = 'sushi' THEN m.price*20 
        ELSE m.price*10 
    END) AS total_points
FROM dbo.sales s
JOIN dbo.menu m ON s.product_id = m.product_id
LEFT JOIN dbo.members mb ON s.customer_id = mb.customer_id
WHERE s.customer_id IN ('A', 'B') AND s.order_date <= '2021-01-31'
--WHERE s.customer_id = mb.customer_id AND s.order_date <= '2021-01-31'
GROUP BY s.customer_id;

Bonus Q2 - Danny also requires further information about the ranking of products. he purposely does not need the ranking of non member purchases so he expects NULL ranking values for customers who are not yet part of the loyalty program.

Explain

WITH customers_data AS (
  SELECT 
    s.customer_id, s.order_date,  m.product_name, m.price,
    CASE
      WHEN s.order_date < mb.join_date THEN 'N'
      WHEN s.order_date >= mb.join_date THEN 'Y'
      ELSE 'N' END AS member
  FROM sales s
  LEFT JOIN members mb
    ON s.customer_id = mb.customer_id
  JOIN menu m
    ON s.product_id = m.product_id
)
SELECT 
  *, 
  CASE
    WHEN member = 'N' THEN NULL
    ELSE RANK () OVER(
      PARTITION BY customer_id, member
      ORDER BY order_date) END AS ranking
FROM customers_data
ORDER BY customer_id, order_date;

Insights

• Customer B is the most frequent visitor with 6 visits in Jan 2021.
• Danny’s Diner’s most popular item is ramen, followed by curry and sushi.
• Customer A loves ramen, Customer C loves only ramen whereas Customer B seems to enjoy sushi, curry and ramen equally.
• The last item ordered by Customers A and B before they became members are sushi and curry. Does it mean both of these items are the deciding factor?