How to Build a Custom "Advanced Reporting" Dashboard for Marketing ROI

If you want a clean, usable view of how every marketing penny turns into revenue, building a custom "Advanced Reporting" dashboard for Marketing ROI is a perfect project. In this post I’ll walk you through a practical, hands-on approach—architecture, data sources, code examples, visualizations, automation, and SEO & sharing tips—so you can ship something useful for your team and integrate it with Magento 2 stores (including stock modules like Force Product Stock Status).

Why build a custom dashboard?

Off-the-shelf analytics tools are great, but they often miss the context of your store: custom product attributes, stock extensions, promotions, and how Magento stores campaign metadata. A custom dashboard lets you:

  • Create metrics tailored to your campaigns (ROAS, CAC, incremental revenue).
  • Combine sales + stock + marketing data (e.g., Force Product Stock Status) for smarter decisions.
  • Provide near real-time visualization for quick optimization.
  • Automate scheduled reports for leadership or ad platforms.
  • Make the dashboard SEO-friendly and shareable for internal and external stakeholders.

Overview: architecture and components

At a high level you’ll want 4 layers:

  1. Data sources: Magento 2 database + extensions (orders, order items, product stock, Force Product Stock Status, Google Ads/UA/GA4 export, email platform).
  2. ETL/aggregation: lightweight service to extract, normalize and store optimized metrics (a small analytics DB like Postgres or ClickHouse if you have high volume).
  3. API layer: endpoints to serve aggregated metrics to the UI (JSON, with filters and pagination).
  4. UI: interactive dashboard (Chart.js / D3 + DataTables) with filters, date ranges and real-time updates.

Step 1 — Identify the key data sources in Magento 2

For Marketing ROI you typically need:

  • Orders and order lines (sales_order and sales_order_item)
  • Customer and acquisition metadata (if you capture UTM params into the order or a custom table)
  • Catalog inventory for stock level (cataloginventory_stock_item)
  • Any custom stock or availability attributes from modules like Force Product Stock Status

Common Magento tables and fields to use (examples):

  • sales_order.entity_id, sales_order.increment_id, sales_order.created_at, sales_order.grand_total, sales_order.customer_id
  • sales_order_item.order_id, sales_order_item.sku, sales_order_item.name, sales_order_item.qty_ordered, sales_order_item.price
  • cataloginventory_stock_item.product_id, cataloginventory_stock_item.qty, cataloginventory_stock_item.is_in_stock

If you capture UTM parameters at checkout, they might be saved as order attributes (e.g. sales_order table columns like marketing_source, marketing_campaign). If not, plan to start capturing them (I'll show a small example later).

Step 2 — Designing the metrics

Before coding, define the metrics you need. Here are the high-impact ones for marketing ROI:

  • Revenue by campaign, channel, and UTM source
  • Orders and conversion rate by campaign
  • Average Order Value (AOV) per campaign
  • Customer Acquisition Cost (CAC) per channel (requires ad spend import)
  • Return on Ad Spend (ROAS) = revenue / ad spend
  • Incremental revenue: revenue from campaign cohorts vs baseline
  • Stock-aware metrics: % of orders impacted by out-of-stock or forced stock status (Force Product Stock Status)

Example metric definitions (precise):

  • ROAS (channel) = SUM(revenue WHERE channel) / SUM(ad_spend WHERE channel)
  • CAC (channel) = SUM(ad_spend WHERE channel) / COUNT(new_customers_acquired_by_channel)
  • Stock Risk = COUNT(order_items WHERE product_force_status = 'out_of_stock' OR is_in_stock = 0) / COUNT(order_items)

Step 3 — Data model and aggregation queries

Keep the analytics queries simple and pre-aggregate where possible (daily rollups, campaign-level rollups). Here are useful SQL examples against Magento tables. These assume you either run them on a replica DB or copy the data into a separate analytics database.

Get daily revenue by campaign

-- Example SQL (Postgres / MySQL compatible)
SELECT
  DATE(o.created_at) AS day,
  COALESCE(o.marketing_campaign, 'unknown') AS campaign,
  SUM(o.grand_total) AS revenue,
  COUNT(DISTINCT o.entity_id) AS orders
FROM sales_order o
WHERE o.state = 'complete'
  AND o.created_at BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY day, campaign
ORDER BY day, campaign;

If marketing_campaign is not a column in sales_order, you should add it (see the next section on capturing UTM params).

Get product stock status per order item using Force Product Stock Status if available

Force Product Stock Status modules can store a product attribute or a separate table. A robust approach is to check for a product attribute first and then fallback to stock item.

-- Join product attribute (if stored as an EAV attribute) and fallback to cataloginventory
-- This is simplified pseudo-SQL using Magento conventions
SELECT
  oi.order_id,
  oi.product_id,
  oi.sku,
  COALESCE(fp.value, si.is_in_stock) AS final_stock_status
FROM sales_order_item oi
LEFT JOIN cataloginventory_stock_item si ON si.product_id = oi.product_id
LEFT JOIN (
  -- hypothetical table where extension stores force status
  SELECT product_id, value FROM force_product_stock_status
) fp ON fp.product_id = oi.product_id
LIMIT 100;

Adjust the fp table name or join to the EAV tables (catalog_product_entity_int) depending on how the extension stores the status.

Step 4 — Capturing UTM & campaign metadata in Magento

If you haven’t been saving UTMs, add a small piece of code to store UTM params into quote & order. Two pragmatic approaches:

  • Frontend: capture UTMs with JavaScript into a cookie, then write to quote during checkout
  • Backend: use an observer to copy quote custom attributes into order on order_place_after

Example JavaScript to capture UTMs into a cookie:

<script>
(function(){
  function getParam(name){
    var m = window.location.search.match(new RegExp('(?:\\?|&)' + name + '=([^&]+)'));
    return m ? decodeURIComponent(m[1]) : null;
  }
  var utm_source = getParam('utm_source');
  var utm_medium = getParam('utm_medium');
  var utm_campaign = getParam('utm_campaign');
  if (utm_source || utm_medium || utm_campaign){
    var data = {
      s: utm_source || '',
      m: utm_medium || '',
      c: utm_campaign || ''
    };
    document.cookie = 'mf_utm=' + btoa(JSON.stringify(data)) + '; path=/; max-age=' + (60*60*24*30);
  }
})();
</script>

Server-side (Magento) you can read that cookie during checkout and save it to the quote and then to the order. Here is a conceptual PHP snippet for a Magento 2 observer:

// In your Observer for checkout_onepage_controller_success_action or quote to order transfer
public function execute(
  
  \Magento\Framework\Event\Observer $observer
) {
  $order = $observer->getEvent()->getOrder();
  $request = $this->request;
  $utmCookie = $request->getCookie('mf_utm');
  if ($utmCookie) {
    $data = json_decode(base64_decode($utmCookie), true);
    if (!empty($data['c'])) {
      $order->setData('marketing_campaign', $data['c']);
    }
    if (!empty($data['s'])) {
      $order->setData('marketing_source', $data['s']);
    }
    if (!empty($data['m'])) {
      $order->setData('marketing_medium', $data['m']);
    }
    $order->save();
  }
}

After you have campaign data captured on orders, the SQL rollups become straightforward.

Step 5 — Building a simple ETL pipeline

For clarity I’ll show a minimal pipeline using Node.js to pull orders via Magento REST API and write daily aggregations into Postgres. This is easy to run as a cron job or in a container.

Minimal Node ETL (concept)

// package.json dependencies: node-fetch, pg
const fetch = require('node-fetch');
const { Client } = require('pg');

const MAGENTO_BASE = process.env.MAGENTO_BASE; // e.g. https://store.example.com
const MAGENTO_TOKEN = process.env.MAGENTO_TOKEN; // integration token
const PG_CONN = process.env.PG_CONN; // postgres connection

async function fetchOrders(updatedAfter){
  const url = `${MAGENTO_BASE}/rest/V1/orders?searchCriteria[filter_groups][0][filters][0][field]=updated_at&searchCriteria[filter_groups][0][filters][0][value]=${encodeURIComponent(updatedAfter)}&searchCriteria[filter_groups][0][filters][0][condition_type]=gt`;
  const res = await fetch(url, { headers: { 'Authorization': `Bearer ${MAGENTO_TOKEN}` } });
  const body = await res.json();
  return body.items || [];
}

async function upsertDailyAggregates(orders){
  const client = new Client({ connectionString: PG_CONN });
  await client.connect();
  for (const o of orders){
    const day = o.created_at.split('T')[0];
    const campaign = o.marketing_campaign || 'unknown';
    const revenue = parseFloat(o.grand_total) || 0;
    await client.query(`
      INSERT INTO analytics.daily_campaign (day, campaign, revenue, orders)
      VALUES ($1,$2,$3,1)
      ON CONFLICT (day,campaign) DO UPDATE
      SET revenue = analytics.daily_campaign.revenue + EXCLUDED.revenue,
          orders = analytics.daily_campaign.orders + 1;
    `, [day, campaign, revenue]);
  }
  await client.end();
}

(async ()=>{
  const orders = await fetchOrders('2025-09-01 00:00:00');
  await upsertDailyAggregates(orders);
})();

This illustrates the idea: pull new/updated orders, map to your campaign field, and update an analytics table.

Step 6 — API endpoints for the dashboard

Build a couple of optimized endpoints to serve the UI: e.g., /api/metrics/daily?start=...&end=...&campaign=... and /api/metrics/summary?period=30d

A simple Express endpoint example that returns daily revenue by campaign from Postgres:

app.get('/api/metrics/daily', async (req,res)=>{
  const { start, end, campaign } = req.query;
  const where = [];
  const params = [];
  if (start) { params.push(start); where.push(`day >= $${params.length}`); }
  if (end) { params.push(end); where.push(`day <= $${params.length}`); }
  if (campaign) { params.push(campaign); where.push(`campaign = $${params.length}`); }
  const q = `SELECT day, campaign, revenue, orders FROM analytics.daily_campaign ${where.length ? 'WHERE ' + where.join(' AND ') : ''} ORDER BY day`;
  const { rows } = await pg.query(q, params);
  res.json(rows);
});

Step 7 — Frontend: real-time interactive visualization

For an interactive dashboard you can use Chart.js for charts and DataTables for tabular drill-downs. Provide date pickers and campaign selectors. For near real-time updates, either use short polling (every 10-30s) or WebSockets if you need push updates.

Minimal HTML + JS example (Chart.js + fetch API)

<div id="controls">
  <label>Start<input type="date" id="start" /></label>
  <label>End<input type="date" id="end" /></label>
  <select id="campaignSel"><option value="">All</option></select>
  <button id="refresh">Refresh</button>
</div>
<canvas id="revenueChart" width="1000" height="400"></canvas>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
async function loadData(){
  const start = document.getElementById('start').value;
  const end = document.getElementById('end').value;
  const campaign = document.getElementById('campaignSel').value;
  const params = new URLSearchParams({ start, end, campaign });
  const res = await fetch('/api/metrics/daily?' + params.toString());
  const rows = await res.json();
  const groups = {};
  rows.forEach(r => {
    if (!groups[r.campaign]) groups[r.campaign] = { labels: [], data: [] };
    groups[r.campaign].labels.push(r.day);
    groups[r.campaign].data.push(parseFloat(r.revenue));
  });
  const datasets = Object.keys(groups).map((k, idx) => ({
    label: k,
    data: groups[k].data,
    fill: false,
    borderColor: `hsl(${(idx*60)%360},60%,50%)`
  }));
  const labels = rows.filter(r=>true).map(r=>r.day).filter((v,i,a)=>a.indexOf(v)===i);
  if (window.revChart) { window.revChart.data.labels = labels; window.revChart.data.datasets = datasets; window.revChart.update(); }
  else {
    const ctx = document.getElementById('revenueChart').getContext('2d');
    window.revChart = new Chart(ctx, { type: 'line', data: { labels, datasets }, options: { responsive: true } });
  }
}

document.getElementById('refresh').addEventListener('click', loadData);
loadData();
</script>

This basic interface gives you multiple campaigns on one chart. Add tooltips and on-click drill-downs to fetch order-level data for any day/campaign.

Step 8 — Real-time considerations

If you need true real-time: stream events when orders are placed into a message queue (e.g., Kafka, Redis Streams) and have a small consumer update an in-memory store (Redis) and push via WebSockets. For many shops, polling or a 1-minute aggregation is plenty.

Step 9 — Periodic reports and automation

Leadership usually wants a scheduled PDF or CSV summary every morning. Options:

  • Server-side generator: produce a PDF (wkhtmltopdf, Puppeteer) or CSV from aggregated data and send via email.
  • Magento cron: implement a console command that runs daily and sends reports.
  • Use a scheduler in your container (cron or Kubernetes CronJob) to run ETL & report generator.

Example: simple Linux cron entry that runs a Node script every day at 6am

0 6 * * * /usr/bin/node /opt/analytics/scripts/daily_report.js >> /var/log/mf/daily_report.log 2>&1

And a minimal Node script that creates a CSV and sends via SMTP:

const fs = require('fs');
const { Client } = require('pg');
const nodemailer = require('nodemailer');
(async ()=>{
  const client = new Client({ connectionString: process.env.PG_CONN });
  await client.connect();
  const { rows } = await client.query(`SELECT day, campaign, revenue, orders FROM analytics.daily_campaign WHERE day = current_date - 1`);
  const csv = ['day,campaign,revenue,orders', ...rows.map(r => `${r.day},"${r.campaign}",${r.revenue},${r.orders}`)].join('\n');
  fs.writeFileSync('/tmp/daily_report.csv', csv);
  const transporter = nodemailer.createTransport({ host: 'smtp.example.com', port: 587, auth: { user: 'noreply@example.com', pass: 'password' } });
  await transporter.sendMail({ from: 'noreply@example.com', to: 'leadership@example.com', subject: 'Daily Marketing Report', text: 'Attached', attachments:[{ path: '/tmp/daily_report.csv'}] });
  await client.end();
})();

If you prefer PDFs, use Puppeteer to render an HTML dashboard page and save a PDF snapshot to attach to your email.

Step 10 — Visualization tips for impact

Keep the UI focused:

  • Top row: summary KPIs (Revenue, ROAS, CAC, Conversion rate)
  • Middle: time-series charts (revenue by campaign, orders)
  • Bottom: detailed table with quick actions (export, create ticket for stock issues)

Make stock-aware visual cues: highlight campaigns where a significant portion of revenue comes from products with forced out-of-stock statuses. Add a column for forced_stock_flag or is_in_stock and color rows red/orange when stock is an issue.

Step 11 — SEO & sharing optimization for the dashboard

Even internal dashboards benefit from SEO-like optimizations because they make the page easier to share and discover inside the company, and often you’ll want to provide public snapshots or embedded reports for partners.

Here’s a practical checklist to make your dashboard shareable and SEO-friendly:

  • Friendly URL: use a clean slug (e.g. /analytics/marketing-roi or /articles/advanced-reporting-dashboard-marketing-roi for a public writeup).
  • Descriptive meta title and meta description for each dashboard page (server-side render these with the date range or campaign name in the title).
  • OpenGraph tags so shared links on Slack/Teams/LinkedIn show a useful preview (title, description, thumbnail).
  • Canonical URLs for saved snapshots to avoid duplication.
  • Structured data (JSON-LD) if you publish a public case study or report page so search engines understand it.
  • Sitemap entries for public reports or snapshot pages so they’re indexed if intended.
  • Allow per-report permalinks (share a URL that encodes the filters like ?start=...&end=...&campaign=...)

Example meta tags (server-render):

<meta name="title" content="Advanced Reporting Dashboard for Marketing ROI | Magefine" />
<meta name="description" content="Build a custom Magento 2 advanced reporting dashboard for Marketing ROI. Integrate Force Product Stock Status, visualise in real time, automate reports and optimise SEO for sharing." />
<meta property="og:title" content="Advanced Reporting Dashboard for Marketing ROI | Magefine" />
<meta property="og:description" content="Custom dashboard for Marketing ROI: integrate Magento sales and stock modules, create custom metrics, visualise in real time, and automate reporting." />
<meta property="og:url" content="https://magefine.com/articles/advanced-reporting-dashboard-marketing-roi" />
<link rel="canonical" href="https://magefine.com/articles/advanced-reporting-dashboard-marketing-roi" />

Step 12 — Performance & hosting considerations (Magento context)

Analytics pages can be heavy. Keep these points in mind for Magento hosting:

  • Serve the dashboard from a separate service or subdomain (analytics.magefine.com) or use a lightweight app server to avoid overloading Magento storefront servers.
  • Use caching for public snapshots and pre-computed aggregates for charts.
  • Use Redis for caching and session store, and ElasticSearch for fast product-level searches if you need them inside the dashboard.
  • If you host on Magefine, choose hosting with enough CPU and RAM for data processing or use a managed DB for your analytics store.

Step 13 — Integrating Force Product Stock Status specifically

Force Product Stock Status modules typically let merchants set the displayed stock status independently from real inventory. For ROI decisions this matters: you may be promoting products that show as "In Stock" while the inventory provider shows 0 available.

How to incorporate:

  1. Discover how the module stores values: check the extension docs or lookup the product attribute or table created by the extension.
  2. If stored as a product attribute (common), join via EAV tables (catalog_product_entity_int / varchar) using the attribute_id for the extension attribute.
  3. If the extension uses a custom table like force_product_stock_status(product_id, status), join that table.
  4. Define a resolved_stock_status = CASE WHEN force_status IS NOT NULL THEN force_status ELSE cataloginventory_stock_item.is_in_stock END
  5. Expose this as a column in your order line rollups so you can compute stock-aware revenue and highlight risky campaigns.

Example SQL snippet to compute resolved stock status:

SELECT
  oi.order_id,
  oi.product_id,
  COALESCE(fp.status, si.is_in_stock) AS resolved_stock_status,
  oi.qty_ordered,
  oi.price
FROM sales_order_item oi
LEFT JOIN cataloginventory_stock_item si ON si.product_id = oi.product_id
LEFT JOIN force_product_stock_status fp ON fp.product_id = oi.product_id;

Now compute revenue coming from items where resolved_stock_status = 0 or 'out_of_stock' and report it by campaign to flag promotions that may cause fulfillment problems.

Step 14 — Security, roles and collaboration

Dashboards often expose sensitive info. Follow these rules:

  • Authenticate and authorize: integrate with SSO (SAML / OAuth) if possible and restrict who can view/export raw data.
  • Audit: log exports and scheduled report sends.
  • Mask PII when exporting to external partners.
  • Use HTTPS everywhere and secure DB credentials in environment variables or a vault.

Step 15 — Putting it all together: an implementation checklist

  1. Capture UTMs into orders (start today).
  2. Decide analytics DB and ETL cadence (near real-time vs hourly).
  3. Implement ETL to daily_campaign and order_item_rollups (include resolved_stock_status).
  4. Build optimized API endpoints for charts and tables.
  5. Implement frontend with Chart.js and DataTables, add filters and permalinks.
  6. Schedule daily automated reports with CSV/PDF attachments and keep logs of sends.
  7. Secure the dashboard and add role-based access and SSO.
  8. Optimize SEO/open graph for public snapshots and share-permalinks.

Quick troubleshooting and tips

  • Slow queries? Pre-aggregate. Use materialized views or daily rollups and refresh them regularly.
  • Missing campaign data? Add the cookie capture code and run a backfill where you can match referrers and timestamp heuristics.
  • Discrepancies with GA? Understand attribution windows and refunds: your revenue is from orders, GA may attribute differently.
  • Stock mismatch? Use resolved_stock_status and include a small reconciliation job that compares forced status vs inventory quantity weekly.

Final notes — ship iteratively

Start with the core: revenue by campaign and ROAS. Add stock-awareness next (Force Product Stock Status integration). Then iterate toward automation and real-time. Keep UI simple: a few meaningful filters and a clear export button will make your dashboard useful from day one.

If you’d like, I can help produce the exact migration scripts (schema for analytics tables), provide a ready-to-deploy Node/Express app that fetches data from Magento, or a reference frontend with Chart.js and server-side rendering that’s SEO-ready for Magefine hosting. Tell me which part you want first and I’ll produce the code and configuration you can run this week.

Good luck and happy reporting—this is the sort of feature that quickly pays for itself when your marketing team can see which campaigns actually generate profitable orders and which ones cause stock headaches.

Author: Magefine Insights — practical tips for Magento 2 sellers and agencies.