From Data to Decisions: Setting Up a Simple Business Intelligence Dashboard for Magento 2

Quick note before we jump in: this post assumes you have access to your Magento 2 database or API credentials and a machine where you can run small services (Docker is perfect). I’ll walk you through a compact, practical BI flow: extract Magento data (especially stock), load it into a small analytics store, visualize with a free BI tool (Metabase/Grafana), and add alerts. I’ll show concrete SQL and scripts so you can reproduce the setup. Think of it as building a simple, useful BI dashboard that helps you turn Magento data into decisions.

Why you want a BI dashboard for Magento 2

Imagine this: your marketing promo drives traffic, but a handful of SKUs are out of stock. Without real-time visibility you risk lost sales and frustrated customers. A lightweight BI dashboard gives you:

• Real-time visibility on stock and availability
• Simple KPIs to track stock health and sales performance
• Automated alerts so you can act before stockouts impact revenue
• An easy way to combine sales, traffic, and inventory for smarter decisions

We’ll focus on practical, low-cost choices: Magento REST/DB extraction, Postgres as a small analytics DB, and Metabase (or Grafana) for visualization and alerts. These tools are open-source and well-suited for small-to-medium stores. Magefine also offers targeted Magento 2 extensions (for example, Force Product Stock Status) that help manage stock data — we’ll explain how to integrate such extensions into the dashboard.

High-level architecture

Simple architecture with minimal moving parts:

• Magento 2 (source) — your store and any inventory extension like Force Product Stock Status
• Extractor — small ETL script (Python) that reads via API or DB
• Analytics DB — Postgres (lightweight and Metabase-friendly)
• BI tool — Metabase (preferred for SQL newbies) or Grafana for richer alerting
• Notification channel — Slack / email for alerts

Step 0 — quick prep

Install Docker if you don’t already have it. Create an API user in Magento if you prefer REST extraction. Make sure you can connect to the Magento DB or have API credentials.

Docker quick stack (optional)

# Start Postgres and Metabase with Docker
# postgres
docker run -d --name mf_postgres -e POSTGRES_PASSWORD=magento -e POSTGRES_USER=magento -e POSTGRES_DB=magento_analytics -p 5432:5432 postgres:14

# metabase
docker run -d --name mf_metabase -p 3000:3000 -e MB_DB_TYPE=postgres -e MB_DB_DBNAME=magento_analytics -e MB_DB_PORT=5432 -e MB_DB_USER=magento -e MB_DB_PASS=magento -e MB_DB_HOST=host.docker.internal metabase/metabase

Note: on Linux you may connect via bridge network instead of host.docker.internal. If you prefer Grafana, swap Metabase for a Grafana container and connect it to Postgres similarly.

Step 1 — Decide: API or direct DB?

There are two common approaches to extract Magento data:

1. Magento REST/GraphQL API — safe, good for hosted environments where DB access is restricted
2. Direct DB queries — faster, gives access to all tables (inventory, orders, extension tables)

If you use an extension like Magefine’s Force Product Stock Status, check its documentation to see where it stores forced status. Often such extensions either set a product attribute or write to a custom table. If it writes a product attribute, you can read it via API or catalog_product_entity tables. If it uses a custom table, DB access is easiest.

Step 2 — Key tables and fields to pull (stock & inventory)

Core Magento stock and inventory tables you’ll typically use when querying the DB:

• catalog_product_entity — product basic data
• cataloginventory_stock_item — qty and manage_stock flag
• cataloginventory_stock_status — stock status (index table)
• sales_order and sales_order_item — orders & order items (sales qty)
• catalog_category_product — product categories

A simple SQL join to get product SKU, name, qty, and stock status:

SELECT
  p.entity_id,
  p.sku,
  COALESCE(nvarchar.value, varchar.value, text.value) AS name,
  si.qty,
  ss.stock_status -- 1 = in stock, 0 = out of stock
FROM catalog_product_entity p
LEFT JOIN cataloginventory_stock_item si ON si.product_id = p.entity_id
LEFT JOIN cataloginventory_stock_status ss ON ss.product_id = p.entity_id
LEFT JOIN catalog_product_entity_varchar varchar ON (varchar.entity_id = p.entity_id AND varchar.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code='name' AND entity_type_id=(SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code='catalog_product'))
LEFT JOIN catalog_product_entity_text text ON (text.entity_id = p.entity_id AND text.attribute_id = /* fallback name attribute id if applicable */)
LEFT JOIN catalog_product_entity_varchar nvarchar ON (nvarchar.entity_id = p.entity_id AND nvarchar.attribute_id = /* store-specific name attr if needed */);

Note: attribute joins vary depending on your Magento installation and store view. When in doubt, use the REST API to fetch product names (safer for multi-store setups).

Step 3 — Including Force Product Stock Status data

Extensions such as Force Product Stock Status often override stock status or expose a manual flag to force a product as in-stock/out-of-stock regardless of qty. There are two common implementations:

• Attribute-based: the extension adds a product attribute (e.g. force_stock_status) — easy to read from eav tables or API.
• Table-based: the extension stores overrides in a custom table (e.g. magefine_force_stock) — requires DB join.

How to detect the implementation quickly:

1. Search for a product attribute: SELECT * FROM eav_attribute WHERE attribute_code LIKE '%force%';
2. List custom tables: SHOW TABLES LIKE '%force%';

Example: reading a hypothetical product attribute named force_product_stock_status via SQL (replace attribute_id accordingly):

SELECT
  p.entity_id,
  p.sku,
  name.value AS name,
  si.qty,
  ss.stock_status,
  fps.value AS forced_status -- 1 = forced in stock, 0 = forced out
FROM catalog_product_entity p
LEFT JOIN cataloginventory_stock_item si ON si.product_id = p.entity_id
LEFT JOIN cataloginventory_stock_status ss ON ss.product_id = p.entity_id
LEFT JOIN catalog_product_entity_varchar name ON (name.entity_id = p.entity_id AND name.attribute_id = 73) -- example attr id for name
LEFT JOIN catalog_product_entity_int fps ON (fps.entity_id = p.entity_id AND fps.attribute_id = 200) -- example attr id for force status
;

If the extension uses a custom table, a join will look like this:

SELECT
  p.entity_id, p.sku, name.value AS name, si.qty, ss.stock_status, fps.override_status
FROM catalog_product_entity p
LEFT JOIN cataloginventory_stock_item si ON si.product_id = p.entity_id
LEFT JOIN cataloginventory_stock_status ss ON ss.product_id = p.entity_id
LEFT JOIN mymodule_force_stock fps ON fps.product_id = p.entity_id
LEFT JOIN catalog_product_entity_varchar name ON (name.entity_id = p.entity_id AND name.attribute_id = 73);

Always check your extension docs for exact table/attribute names — but the approaches above will help you locate the right data to include in the analytics layer.

Step 4 — Extract with a simple Python ETL

Let’s write a short Python script that extracts product stock info and recent sales and loads them into Postgres. This example uses direct DB access via psycopg2 for Postgres and pymysql for Magento MySQL. If you prefer REST API, I include a REST example after.

Requirements

pip install pymysql psycopg2-binary pandas sqlalchemy

Python ETL (DB extraction)

#!/usr/bin/env python3
import pandas as pd
import pymysql
from sqlalchemy import create_engine

# Magento MySQL connection
magento_conn = pymysql.connect(
    host='MAGENTO_DB_HOST',
    user='MAGENTO_DB_USER',
    password='MAGENTO_DB_PASS',
    db='MAGENTO_DB_NAME',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# Postgres analytics connection
pg_engine = create_engine('postgresql://magento:magento@localhost:5432/magento_analytics')

product_sql = """
SELECT
  p.entity_id AS product_id,
  p.sku,
  name.value AS name,
  si.qty,
  ss.stock_status,
  COALESCE(fps.value, NULL) as forced_status
FROM catalog_product_entity p
LEFT JOIN cataloginventory_stock_item si ON si.product_id = p.entity_id
LEFT JOIN cataloginventory_stock_status ss ON ss.product_id = p.entity_id
LEFT JOIN catalog_product_entity_varchar name ON (name.entity_id = p.entity_id AND name.attribute_id = 73)
LEFT JOIN catalog_product_entity_int fps ON (fps.entity_id = p.entity_id AND fps.attribute_id = 200)
"""

with magento_conn.cursor() as cursor:
    cursor.execute(product_sql)
    rows = cursor.fetchall()

df_products = pd.DataFrame(rows)

# push to postgres
df_products.to_sql('bi_products_stock', pg_engine, if_exists='replace', index=False)

# Simple sales extract (last 30 days)
sales_sql = """
SELECT
  i.product_id,
  p.sku,
  SUM(i.qty_ordered) AS qty_sold,
  MIN(o.created_at) as first_order,
  MAX(o.created_at) as last_order
FROM sales_order_item i
JOIN sales_order o ON o.entity_id = i.order_id AND o.state <> 'canceled'
JOIN catalog_product_entity p ON p.entity_id = i.product_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY i.product_id, p.sku;
"""

with magento_conn.cursor() as cursor:
    cursor.execute(sales_sql)
    sales_rows = cursor.fetchall()

df_sales = pd.DataFrame(sales_rows)

# push to postgres
df_sales.to_sql('bi_products_sales_30d', pg_engine, if_exists='replace', index=False)

print('ETL done: products and sales loaded to Postgres')

This loads two simple tables into Postgres you can connect from Metabase. Run this ETL as a cron job (or run every 15–60 minutes depending on your needs).

REST API extraction example (Python)

import requests
import pandas as pd

MAGENTO_BASE = 'https://yourstore.com'
ADMIN_TOKEN = 'your-admin-token'

headers = {'Authorization': f'Bearer {ADMIN_TOKEN}', 'Content-Type': 'application/json'}

# Get products with stock item
page = 1
products = []
while True:
    r = requests.get(f"{MAGENTO_BASE}/rest/V1/products?searchCriteria[currentPage]={page}&searchCriteria[pageSize]=100", headers=headers)
    data = r.json()
    items = data.get('items', [])
    if not items:
        break
    for it in items:
        sku = it['sku']
        name = it['name'] if 'name' in it else (it['custom_attributes'][0]['value'] if it.get('custom_attributes') else '')
        # Hit stock endpoint
        stock = requests.get(f"{MAGENTO_BASE}/rest/V1/stockItems/{sku}", headers=headers).json()
        products.append({
            'sku': sku,
            'name': name,
            'qty': stock.get('qty'),
            'is_in_stock': stock.get('is_in_stock')
        })
    page += 1

pd.DataFrame(products).to_csv('products_stock.csv', index=False)

This uses Magento REST to fetch products and stock item per SKU. Pagination and API throttling are considerations; for medium catalogs you’ll want to implement incremental updates (only changed_at product attributes) to reduce load.

Step 5 — Useful KPIs for stock optimization

Here are practical metrics to implement and visualize. I’ll provide SQL examples you can use in Metabase or directly in Postgres to create charts.

1) Stockouts / Out of stock rate

Definition: percentage of SKUs currently out of stock (or forced out via Force Product Stock Status).

SELECT
  SUM(CASE WHEN (COALESCE(forced_status, ss.stock_status) = 0) THEN 1 ELSE 0 END) AS out_of_stock_count,
  COUNT(*) AS total_products,
  ROUND(100.0 * SUM(CASE WHEN (COALESCE(forced_status, ss.stock_status) = 0) THEN 1 ELSE 0 END) / COUNT(*), 2) AS out_of_stock_pct
FROM catalog_product_entity p
LEFT JOIN cataloginventory_stock_status ss ON ss.product_id = p.entity_id
LEFT JOIN catalog_product_entity_int fps ON (fps.entity_id = p.entity_id AND fps.attribute_id = 200);

Visualize: a single KPI card (out_of_stock_pct) + a table of the top 50 SKUs currently out of stock.

2) Stock rotation / Inventory turnover (simple proxy)

Inventory turnover can be approximated by dividing units sold over a period by average stock. If COGS is not available, use qty sold as a proxy.

-- Approx turnover over 30 days per product
WITH sales_30 AS (
  SELECT product_id, SUM(qty_ordered) AS qty_sold
  FROM sales_order_item i
  JOIN sales_order o ON o.entity_id = i.order_id AND o.state <> 'canceled'
  WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY product_id
),
current_stock AS (
  SELECT product_id, qty
  FROM cataloginventory_stock_item
)
SELECT
  p.sku,
  COALESCE(s.qty_sold, 0) AS qty_sold_30d,
  COALESCE(c.qty, 0) AS current_stock,
  CASE WHEN COALESCE(c.qty,0) = 0 THEN NULL ELSE ROUND(COALESCE(s.qty_sold,0) / c.qty, 2) END AS turnover_proxy
FROM catalog_product_entity p
LEFT JOIN sales_30 s ON s.product_id = p.entity_id
LEFT JOIN current_stock c ON c.product_id = p.entity_id
ORDER BY turnover_proxy DESC NULLS LAST
LIMIT 100;

Visualize: bar chart for top movers by turnover_proxy; trend line for qty_sold_30d vs current_stock to spot potential stockouts.

3) Products most sold (volume and revenue)

SELECT
  i.product_id,
  p.sku,
  SUM(i.qty_ordered) AS qty_sold,
  SUM(i.row_total) AS revenue
FROM sales_order_item i
JOIN sales_order o ON o.entity_id = i.order_id AND o.state <> 'canceled'
JOIN catalog_product_entity p ON p.entity_id = i.product_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY i.product_id, p.sku
ORDER BY qty_sold DESC
LIMIT 50;

Visualize: top 50 by qty/revenue combo chart (bar + line).

4) Days of stock remaining (simple)

Estimate days remaining = current stock / average daily sales (over the last N days).

WITH daily_sales AS (
  SELECT product_id, DATE(o.created_at) AS date, SUM(i.qty_ordered) AS qty
  FROM sales_order_item i
  JOIN sales_order o ON o.entity_id = i.order_id AND o.state <> 'canceled'
  WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  GROUP BY product_id, DATE(o.created_at)
), avg_daily AS (
  SELECT product_id, AVG(qty) AS avg_daily_sales
  FROM daily_sales
  GROUP BY product_id
), stock AS (
  SELECT product_id, qty AS current_stock FROM cataloginventory_stock_item
)
SELECT p.sku, s.current_stock, a.avg_daily_sales,
  CASE WHEN a.avg_daily_sales IS NULL OR a.avg_daily_sales = 0 THEN NULL ELSE ROUND(s.current_stock / a.avg_daily_sales, 1) END AS days_remaining
FROM catalog_product_entity p
LEFT JOIN stock s ON s.product_id = p.entity_id
LEFT JOIN avg_daily a ON a.product_id = p.entity_id
ORDER BY days_remaining ASC NULLS LAST
LIMIT 100;

Visualize: risk matrix (days_remaining vs qty) or sorted table for products with days_remaining under threshold.

Step 6 — Combine traffic, sales and stock

Combining traffic (Google Analytics or server logs), sales and stock gives you a powerful context: which high-traffic pages are driving sales and are those SKUs available?

How to add traffic data

Options:

• Export GA4 events to BigQuery and query them, then load aggregated page/product traffic into Postgres.
• Use server logs / Cloud provider analytics to extract page-level hits and join by product SKU (if your product pages include SKU in URL or structured data).
• Use a simple CSV export from Google Analytics containing page path, product SKU and sessions, and import into Postgres.

Example: join sales, stock, and traffic

SELECT
  sku,
  SUM(qty_sold) AS qty_sold_30d,
  MAX(current_stock) AS current_stock,
  SUM(sessions) as sessions_30d
FROM (
  SELECT p.sku, COALESCE(s.qty_sold,0) AS qty_sold, COALESCE(st.qty,0) AS current_stock, 0 AS sessions
  FROM catalog_product_entity p
  LEFT JOIN (
    SELECT product_id, SUM(qty_ordered) AS qty_sold
    FROM sales_order_item i
    JOIN sales_order o ON o.entity_id = i.order_id AND o.state <> 'canceled'
    WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY product_id
  ) s ON s.product_id = p.entity_id
  LEFT JOIN cataloginventory_stock_item st ON st.product_id = p.entity_id
  UNION ALL
  SELECT sku, 0 AS qty_sold, 0 AS current_stock, SUM(sessions) as sessions
  FROM product_traffic_30d -- a table you import from GA export
  GROUP BY sku
) x
GROUP BY sku
ORDER BY qty_sold_30d DESC LIMIT 200;

Visualize: scatter plot or table with columns (sku, sessions, qty_sold_30d, current_stock). This makes it easy to spot high-traffic SKUs with low stock.

Step 7 — Automate alerts and reports

Two common ways to automate alerts:

1. Let your BI tool send alerts (Metabase pulse/email, Grafana alerting to Slack/email).
2. Run a small monitoring script (Python) that checks thresholds and sends Slack/email when triggered.

Metabase: pulses and alerts

Metabase supports email and Slack pulses. Create a SQL question that returns products under threshold and configure a pulse to send it every X hours when it’s non-empty. It’s a very friendly way to get reports to your inbox or Slack.

Grafana: alerting and notification channels

Grafana can run alerts on time series data and notify Slack, PagerDuty, or email. Grafana is stronger for time-based thresholds (e.g., projected days_remaining crosses below 7).

Example: simple Python alert script

#!/usr/bin/env python3
import psycopg2
import smtplib
from email.message import EmailMessage

conn = psycopg2.connect('host=localhost dbname=magento_analytics user=magento password=magento')
cur = conn.cursor()

# Check for low stock items
cur.execute("""
SELECT sku, name, qty, COALESCE(forced_status, stock_status) AS effective_stock_status
FROM bi_products_stock
WHERE qty <= 5 -- threshold
ORDER BY qty ASC;
""")
rows = cur.fetchall()
if rows:
    body = 'Low stock items:\n' + '\n'.join([f"{r[0]} - {r[1]} - qty={r[2]}" for r in rows])

    msg = EmailMessage()
    msg['Subject'] = 'Low stock alert'
    msg['From'] = 'alerts@yourdomain.com'
    msg['To'] = 'ops@yourdomain.com'
    msg.set_content(body)

    s = smtplib.SMTP('localhost')
    s.send_message(msg)
    s.quit()

cur.close()
conn.close()

This script is intentionally small: use a managed SMTP, or swap to Slack webhook (requests.post) for Slack alerts.

Step 8 — Dashboards to build (practical list)

Start with 4 dashboard panels and expand from there:

1. Stock Health: out_of_stock_pct, count of forced statuses, list of top 50 out-of-stock.
2. Top Movers: top 50 sold in last 30/90 days + current stock + days_remaining.
3. Demand vs Supply: traffic (sessions) vs sales vs stock for top landing pages.
4. Reorder & Alerts: items below reorder point, projected days_remaining, last purchase lead time (if you have PO data).

Each panel should include a link to the product admin page so a merchandiser can act quickly.

Step 9 — Tools and extensions recommended

Free / open-source tools:

• Metabase — easy dashboards, SQL questions, pulses (great for non-technical users)
• Grafana — stronger alerting, time-series capabilities
• Postgres — reliable analytics DB for small stacks
• Apache Superset — another open BI option (SQL-first)

Magento-specific extensions and recommendations:

• Force Product Stock Status (Magefine) — useful if you need to force product availability for marketing reasons. Make sure your ETL understands whether the extension uses attributes or a custom table. We used a generic pattern above to include forced_status in analytics.
• Inventory extensions that expose clearer stock data and PO/lead time features — these make turnover and reorder logic more accurate when included in your analytics.
• Magefine hosting and Magento support — if you use Magefine hosting or extensions, they can help with connector recommendations and ensuring your analytics reads correct stock values (especially when extensions override stock logic).

Avoid installing heavy full-suite platforms if your needs are small: a well-designed Postgres + Metabase + ETL flow is fast to implement and very powerful.

Step 10 — Practical tips & pitfalls

• Stock indexes: watch Magento indexers. The cataloginventory_stock_status table is an index — if you update inventory externally, reindex or read direct stock_item.qty to get raw numbers.
• Extensions override behavior: some extensions (like Force Product Stock Status) can cause stock values and “effective” availability to diverge — be explicit about which column you display (raw qty vs effective availability).
• Performance: avoid heavy full-table joins in real-time dashboards. Use incremental ETL to push rolling aggregates to your analytics DB.
• Testing: start with a small subset of SKUs (top 500) to validate your logic before loading full catalog.
• Security: if you expose APIs or DB access, use read-only users and firewall rules. Never put admin DB credentials into public scripts.

Advanced ideas to evolve your dashboard

Once you have the basics, consider:

• Predictive replenishment: use simple linear regression on recent sales to forecast demand and recommend reorder quantities.
• Lead-time aware reorder suggestions: combine supplier lead time (from PO system) with days_remaining to generate reorder triggers.
• Product-level margins and COGS: pump COGS into the analytics DB to compute real inventory value and profitability by SKU.
• A/B test visibility: show the impact of marketing campaigns on SKU-level availability and conversions.

Putting it all together: example workflow for a merchandiser

1. Every hour, ETL updates bi_products_stock and bi_products_sales_30d in Postgres.
2. Metabase dashboard shows stock health. A pulse runs every morning to list SKUs with qty < 10.
3. Alert script posts to Slack when days_remaining < 7 for top-sellers.
4. Merchandiser clicks product link, updates stock (or uses Force Product Stock Status to temporarily mark as in stock), and the dashboard reflects the change after the next ETL run.

Quick troubleshooting checklist

• No data in Metabase? Check Postgres table permissions and connection string.
• Numbers don’t match Magento admin? Verify you’re reading the correct source (stock_item.qty vs stock_status vs forced flags).
• Dashboards slow? Add incremental pre-aggregations: daily sales table instead of re-aggregating hours of orders in real-time.
• Alerts missing items? Check timezone differences in date filters and sales windows.

Summary and final tips

Building a simple BI dashboard for Magento 2 doesn’t require heavy investment. With a small ETL, a Postgres analytics store, and an open-source BI tool (Metabase or Grafana), you can:

• Get real-time visibility on stock and forced availability (e.g. from Force Product Stock Status)
• Track key KPIs: stockouts, turnover proxy, top sellers and days of stock remaining
• Combine traffic, sales and stock for actionable insights
• Automate alerts via Pulses (Metabase), Grafana or simple scripts

Want to go further? If you use Magefine extensions and hosting, they can help you map the extension-specific data into the analytics layer and recommend best practices for index handling and integrations. Start small: pick the 5 SKUs or the 5 KPIs that matter most, and iterate from there.

If you want, I can:

• Generate a tailored SQL set for your Magento DB schema (I’d need a list of your product attribute IDs and whether you use multi-store names).
• Create a sample Metabase dashboard JSON export ready to import with the panels described above.
• Turn the Python ETL into a Dockerized service and provide a docker-compose template.

Tell me which one you’d like and I’ll prepare the next step.