Sales Dashboard &
Revenue Analytics

An Indian product-based company selling electronics accessories — phone cases (budget ₹299, mid-range ₹699, premium ₹1,499), earbuds (₹899), chargers (₹599), and screen protectors (₹199) — across Amazon, Flipkart, and their own website was generating around ₹9.2 Cr annual revenue. On paper, the business was doing okay. But underneath, two specific problems were quietly eating into growth.

Before gathering requirements, I mapped out everyone who had a stake in this project — either because they owned data, made decisions based on sales numbers, or would be a daily user of the new dashboard. Understanding what each person cared about helped me ask the right questions and design a solution that everyone would actually use.

Requirement gathering is about understanding the problem deeply before jumping to a solution. I used multiple techniques because interviews alone don't give the full picture — sometimes you need to sit and watch how people actually work, or look at the raw data yourself.

The “As-Is” process is the current-state process — step by step, how does the team currently get from raw sales data to a business decision? I mapped this by watching the team work and asking them to describe every step. What I found was a process held together entirely by manual effort, with problems at almost every step.

Root Cause Analysis (RCA) is a technique where instead of fixing the first problem you see, you keep asking “but why?” until you find the actual underlying cause. I applied the “5 Whys” method to both main problems separately.

After the As-Is analysis and Root Cause Analysis, I documented every capability the business currently lacks compared to what it needs to make data-driven decisions. Each gap was rated by how critical it is to fix.

The goal of the To-Be design was simple: the Sales Manager should be able to open one dashboard each morning and see everything they need to make decisions — without downloading a single file, copy-pasting anything, or spending hours in Excel.

User stories follow this format: “As a [role], I want [feature] so that [reason].” They help the development team understand who they are building for and why — not just what to build.

I created two BPMN diagrams: one showing the current (AS-IS) process and one showing the future (TO-BE) process. These diagrams were used to clearly show the team what was changing and why — making it easier to get buy-in from people who were used to the old process.

Data mapping means documenting every data source, what data it contains, and how the fields from different systems connect to each other. This is critical because Amazon calls an order “Shipped”, Flipkart calls it “Dispatched”, and the website calls it “Fulfilled” — but they all mean the same thing. Without mapping, the system would count them differently.

-- Which product earns the most for every rupee spent on ads?
-- (ROAS = Return on Ad Spend -- higher is better)
SELECT
  p.product_name,
  p.selling_price,
  COUNT(o.order_id)                    AS total_orders,
  SUM(o.sale_amount - o.refund_amount) AS net_revenue,
  SUM(m.ad_spend)                      AS total_ad_spend,
  ROUND(
    SUM(o.sale_amount - o.refund_amount)
    / NULLIF(SUM(m.ad_spend), 0), 2
  )                                    AS revenue_per_rupee_of_ad_spend
FROM products p
LEFT JOIN orders o      ON p.product_id = o.product_id
LEFT JOIN ad_spend m    ON p.product_id = m.product_id
  AND MONTH(m.spend_date) = MONTH(o.order_date)
WHERE o.order_date >= DATEADD(year, -1, GETDATE())
GROUP BY p.product_id, p.product_name, p.selling_price
ORDER BY revenue_per_rupee_of_ad_spend DESC;

-- Monthly sales trend for the last 24 months
-- Used to identify seasonal patterns (festive peak vs. Jan-Mar slump)
SELECT
  YEAR(order_date)                     AS sale_year,
  MONTH(order_date)                    AS sale_month,
  DATENAME(month, order_date)          AS month_name,
  SUM(sale_amount - refund_amount)     AS net_revenue,
  COUNT(order_id)                      AS total_orders,
  AVG(sale_amount)                     AS avg_order_value
FROM orders
WHERE order_date >= DATEADD(year, -2, GETDATE())
GROUP BY
  YEAR(order_date),
  MONTH(order_date),
  DATENAME(month, order_date)
ORDER BY sale_year, sale_month;

I designed 4 dashboard views in Figma — one for each type of user. Instead of one complex screen with everything on it, each team sees only what they need. The wireframes were reviewed with stakeholders twice before development began.