Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 36 ยท Analytics and Cloud

KPIs, Dashboards and Business Reports

Learn operational vs analytical reports, core ERP and CRM KPIs, how to design dashboards and where a data warehouse fits, with Python calculations of real KPI formulas.

ERP and CRM systems collect enormous amounts of data as a by-product of daily work. Business reports turn that data into answers: how much did we sell, which customers pay late, where are deals stuck, how long does an order take from entry to delivery? Good reporting does not mean many reports; it means the right few numbers, trusted by everyone, shown to the people who can act on them.

This lesson covers types of reports, important KPIs with their formulas, dashboard design, and the role of a data warehouse. The products are not available on this site, so the calculations run in Python.

Operational vs analytical reports

  • Operational reports support daily work inside the transaction system: today's open orders, a pick list, an aged receivables list, my open tasks. They need current data, small scope and fast response.
  • Analytical reports look across time and dimensions: revenue by region and product over three years, customer lifetime value, win rate by lead source. They read large volumes and are better run on a separate analytical store so they do not slow down the live system.

Many ERP and CRM products have built-in reports and dashboards for the first type and offer analytics modules or connectors to BI tools for the second.

Data flow to analytics

From system to insight

Source systems ERP, CRM, web shop, spreadsheets.
Warehouse or lakehouse Cleaned, history kept, modeled.
BI tool Dashboards and ad-hoc analysis.

Extract, transform and load (ETL or ELT) moves the data.

1 / 2

A data warehouse stores integrated, historical data organized for analysis, usually in a star schema: a central fact table (sales lines with measures such as quantity and amount) surrounded by dimension tables (customer, product, date, region). Questions then become simple: sum the facts and group by dimensions. The data comes in through scheduled loads, which depend on the quality work from data quality monitoring.

Core KPIs

A KPI (key performance indicator) is a metric tied to a goal. Each needs a precise definition, a data source, an owner and a target. A few important ones:

ERP and finance

  • Gross margin = (revenue - cost of goods sold) / revenue.
  • DSO (days sales outstanding) = accounts receivable / credit sales x days in the period.
  • Inventory turnover = cost of goods sold / average inventory.
  • On-time in-full (OTIF) = orders delivered complete and on time / total orders.
  • Order cycle time = delivery date minus order date.
  • Days to close the month-end books.

CRM and sales

  • Conversion rate = leads that became customers / total leads.
  • Win rate = deals won / deals closed.
  • Average deal size and sales cycle length.
  • Pipeline coverage = open pipeline value / quota.
  • Customer acquisition cost (CAC) = sales and marketing spend / new customers.
  • Customer lifetime value (CLV), churn rate and net revenue retention for subscriptions.
  • First response time and resolution time for service.
Python Compute core finance and supply KPIs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Compute CRM funnel KPIs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that two different "conversion" numbers exist. This is why a metric dictionary matters: write down the exact formula and the filters (are test leads excluded? which date counts?). Many arguments in meetings are really arguments about definitions.

Designing a dashboard

A dashboard is a decision tool, not a decoration.

  1. Start with the audience and the decisions. A warehouse manager needs today's late shipments; a CFO needs cash and margin trends.
  2. Limit to the vital few. Five to nine numbers per view.
  3. Show context: target, previous period, trend. A number alone is hard to judge.
  4. Choose the right chart: lines for trends over time, bars for comparing categories, a single big number for status, a table when users need exact values. Avoid 3D and decoration.
  5. Enable drill-down from the summary to the underlying records.
  6. Use consistent definitions and colours; red and green alone exclude colour-blind users, so add icons or text.
  7. Show data freshness ("updated 06:00") so nobody mistakes stale numbers for live ones.
  8. Set alerts for exceptions (stock-outs, overdue invoices over a threshold).
Python Status against target with trend (text dashboard)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Self-service vs governed analytics

Self-service BI lets business users build their own views, which speeds up answers but risks conflicting numbers. A common compromise: IT or a data team publishes certified datasets and a metric dictionary, and users build freely on top of them. Control access with roles: sales sees only their region; salaries are visible only to HR.

Common mistakes

  • Reports that nobody asked for, and no one retires.
  • Vanity metrics (number of emails sent) instead of outcomes.
  • Different definitions of "revenue" or "active customer" in different reports.
  • Dashboards on dirty data, which destroys trust quickly.
  • Ignoring performance: running heavy analytical queries on the live ERP database during working hours.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is a separate data warehouse often used for analytical reports?

    A warehouse keeps integrated history and takes analytical load away from the transaction system.
  2. 2. Gross margin is calculated as:

    Gross margin is the share of revenue left after cost of goods sold.
  3. 3. What is the purpose of a metric dictionary?

    Without shared definitions, teams quote different numbers for the same name.
  4. 4. Which dashboard practice helps users judge a number?

    Targets and trends show whether a value is good or bad and in which direction it is moving.

Summary

  • Operational reports serve daily work; analytical reports need history and often a warehouse.
  • KPIs need exact formulas, owners and targets; keep a metric dictionary.
  • Finance, supply and CRM each have a small set of core KPIs.
  • Good dashboards are audience-focused, few, contextual, drill-down capable and fresh.
  • Trust depends on data quality and consistent definitions.

Next: Forecasting and Analytics: Pipeline, Segments and Churn