Interactive Pivot Tables for Website Analytics

Why ready-made libraries fall short for big data

Picture an e-commerce site with 10 million orders. A manager needs a quarterly sales report grouped by category and month. A standard Excel export takes 30 minutes and hammers the database. A pivot table with server-side aggregation returns data in 200 ms and lets the user change slices in real time. In practice, off-the-shelf libraries struggle with that load: react-pivottable chokes on 200k+ rows, AG Grid requires an enterprise license for server mode ($2k/year), and Flexmonster doesn't support ad-hoc queries against relational databases. We combine libraries with our own server-side aggregation to fill those gaps. The license savings can exceed $10k/year for a team of 5 developers.

Wikipedia defines a pivot table as a data analysis tool that aggregates data by dimensions. In commercial projects, customization is often needed – something ready-made solutions don't provide.

How we build server-side aggregation for millions of rows

For large data, pivot configuration moves to the server. We use ClickHouse or PostgreSQL with indexes. Each axis change sends a query, the server returns aggregates in 100–500 ms. We cache results by configuration key. Below is an example of generating SQL from config.

// Server – generate SQL from config
function buildPivotQuery(config: PivotConfig, dateRange: [Date, Date]): string {
  const rowsExpr = config.rows.map(r => `"${r}"`).join(', ');
  const colsExpr = config.cols.map(c => `"${c}"`).join(', ');
  const valExpr = config.values[0]; // simplified

  const aggExpr = {
    sum: `SUM("${valExpr}")`,
    count: `COUNT(*)`,
    avg: `AVG("${valExpr}")::numeric(18,2)`,
    min: `MIN("${valExpr}")`,
    max: `MAX("${valExpr}")`,
  }[config.aggFn];

  return `
    SELECT ${rowsExpr}, ${colsExpr}, ${aggExpr} AS value
    FROM events
    WHERE created_at BETWEEN $1 AND $2
    GROUP BY ${rowsExpr}, ${colsExpr}
    ORDER BY ${rowsExpr}, ${colsExpr}
  `;
}

Step-by-step guide

1. Analyze data structure. Identify fields for grouping (rows, columns) and measures (sum, average).
2. Design SQL queries. Generate dynamic queries with GROUP BY.
3. Configure ClickHouse/PostgreSQL. Create indexes, set up result caching.
4. Integrate with the client. Send a query on every config change.

Comparison: client-side vs server-side aggregation

Client-side aggregation is 5× faster on small data, but server-side scales to billions of rows. The choice depends on data volume and interactivity needs.

How to implement client-side aggregation: a step-by-step guide

1. Filter data – keep only records matching the applied filters.
2. Group – derive keys for rows and columns based on selected field values.
3. Aggregate – compute the value for each cell (sum, count, average, min, max).
4. Render table – output an HTML table with totals and a fixed header.

type AggregateFunction = 'sum' | 'count' | 'avg' | 'min' | 'max';

interface PivotConfig {
  rows: string[];
  cols: string[];
  values: string[];
  aggFn: AggregateFunction;
  filters: Record<string, string[]>;
}

interface PivotResult {
  rowKeys: string[][];
  colKeys: string[][];
  data: Map<string, Map<string, number>>;
}

function computePivot(rawData: Record<string, any>[], config: PivotConfig): PivotResult {
  const { rows, cols, values, aggFn, filters } = config;

  const filtered = rawData.filter(row =>
    Object.entries(filters).every(([field, allowed]) =>
      !allowed.length || allowed.includes(String(row[field]))
    )
  );

  const rowKeySet = new Set<string>();
  const colKeySet = new Set<string>();
  const accumulator = new Map<string, Map<string, number[]>>();

  filtered.forEach(row => {
    const rowKey = rows.map(r => String(row[r] ?? '(empty)')).join('||');
    const colKey = cols.map(c => String(row[c] ?? '(empty)')).join('||');

    rowKeySet.add(rowKey);
    colKeySet.add(colKey);

    const numVal = values.reduce((sum, v) => sum + (Number(row[v]) || 0), 0);

    if (!accumulator.has(rowKey)) accumulator.set(rowKey, new Map());
    const colMap = accumulator.get(rowKey)!;
    if (!colMap.has(colKey)) colMap.set(colKey, []);
    colMap.get(colKey)!.push(numVal);
  });

  const aggregated = new Map<string, Map<string, number>>();
  accumulator.forEach((colMap, rowKey) => {
    const row = new Map<string, number>();
    colMap.forEach((vals, colKey) => {
      let result: number;
      switch (aggFn) {
        case 'sum':   result = vals.reduce((a, b) => a + b, 0); break;
        case 'count': result = vals.length; break;
        case 'avg':   result = vals.reduce((a, b) => a + b, 0) / vals.length; break;
        case 'min':   result = Math.min(...vals); break;
        case 'max':   result = Math.max(...vals); break;
      }
      row.set(colKey, result);
    });
    aggregated.set(rowKey, row);
  });

  return {
    rowKeys: Array.from(rowKeySet).sort().map(k => k.split('||')),
    colKeys: Array.from(colKeySet).sort().map(k => k.split('||')),
    data: aggregated,
  };
}

function PivotTable({ result, config, format }: {
  result: PivotResult;
  config: PivotConfig;
  format?: (val: number) => string;
}) {
  const fmt = format ?? (v => v.toLocaleString('en-US'));

  const rowTotals = result.rowKeys.map(rk => {
    const rowKey = rk.join('||');
    let total = 0;
    result.colKeys.forEach(ck => {
      total += result.data.get(rowKey)?.get(ck.join('||')) ?? 0;
    });
    return total;
  });

  const grandTotal = rowTotals.reduce((a, b) => a + b, 0);

  return (
    <div className="overflow-auto max-h-[600px]">
      <table className="text-sm border-collapse w-full">
        <thead className="sticky top-0 bg-white z-10">
          <tr>
            {config.rows.map(r => (
              <th key={r} className="border px-3 py-2 text-left bg-gray-50 font-medium">{r}</th>
            ))}
            {result.colKeys.map(ck => (
              <th key={ck.join('/')} className="border px-3 py-2 text-right bg-gray-50 font-medium whitespace-nowrap">
                {ck.join(' / ')}
              </th>
            ))}
            <th className="border px-3 py-2 text-right bg-blue-50 font-semibold">Total</th>
          </tr>
        </thead>
        <tbody>
          {result.rowKeys.map((rk, ri) => {
            const rowKey = rk.join('||');
            return (
              <tr key={rowKey} className="hover:bg-gray-50">
                {rk.map((label, i) => (
                  <td key={i} className="border px-3 py-1.5 font-medium">{label}</td>
                ))}
                {result.colKeys.map(ck => {
                  const val = result.data.get(rowKey)?.get(ck.join('||'));
                  return (
                    <td key={ck.join('/')} className="border px-3 py-1.5 text-right tabular-nums">
                      {val != null ? fmt(val) : '—'}
                    </td>
                  );
                })}
                <td className="border px-3 py-1.5 text-right tabular-nums font-medium bg-blue-50">
                  {fmt(rowTotals[ri])}
                </td>
              </tr>
            );
          })}
        </tbody>
        <tfoot>
          <tr className="font-semibold bg-gray-100">
            <td colSpan={config.rows.length} className="border px-3 py-2">Total</td>
            {result.colKeys.map(ck => {
              const colTotal = result.rowKeys.reduce((sum, rk) => {
                return sum + (result.data.get(rk.join('||'))?.get(ck.join('||')) ?? 0);
              }, 0);
              return (
                <td key={ck.join('/')} className="border px-3 py-2 text-right tabular-nums">{fmt(colTotal)}</td>
              );
            })}
            <td className="border px-3 py-2 text-right tabular-nums bg-blue-100">{fmt(grandTotal)}</td>
          </tr>
        </tfoot>
      </table>
    </div>
  );
}

Export to Excel

We use exceljs to generate .xlsx on the client:

import ExcelJS from 'exceljs';

async function exportToExcel(result: PivotResult, config: PivotConfig) {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet('Pivot Table');

  const headers = [...config.rows, ...result.colKeys.map(k => k.join(' / ')), 'Total'];
  ws.addRow(headers).font = { bold: true };

  result.rowKeys.forEach(rk => {
    const rowKey = rk.join('||');
    const row = [...rk];
    result.colKeys.forEach(ck => {
      row.push(String(result.data.get(rowKey)?.get(ck.join('||')) ?? ''));
    });
    ws.addRow(row);
  });

  const buffer = await wb.xlsx.writeBuffer();
  const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
  const url = URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url;
  a.download = 'pivot.xlsx';
  a.click();
}

How to choose between client-side and server-side aggregation?

If your data is up to 200k rows and doesn't need complex filtering, client-side is faster and cheaper. For millions of rows and ad-hoc queries, server-side aggregation is mandatory. We help clients pick the optimal architecture. With 7+ years of experience and 50+ successful pivot projects, we guarantee a custom solution that meets your performance needs.

Development stages and estimated timeline

Client-side pivot for up to 50k rows with drag-and-drop configurator and Excel export: 2–3 weeks. Server mode with ClickHouse or PostgreSQL, result caching, and support for multiple values: additional 1–2 weeks. A turnkey solution including all stages costs from $5k and is delivered in 4–6 weeks.

Scope of work (included)

• Data analysis and aggregation schema design
• Client UI development with drag-and-drop (React, TypeScript, Tailwind)
• Server-side aggregation on ClickHouse/PostgreSQL with caching
• Export to Excel/CSV, printing
• Load testing up to 10M rows
• Documentation and source code handover
• 3 months of post-launch support with guaranteed response time

We'll evaluate your project within one day. Contact us to discuss your data, metrics, and find the best solution. Get a free consultation and a custom quote today. We also offer a satisfaction guarantee – if the solution doesn't meet agreed performance benchmarks, we iterate until it does.

Setup Web Analytics: GA4, GTM, Yandex.Metrica, and Amplitude

We often see: conversion rate 1.2%, traffic grows, but conversion stays flat. The marketer looks at Google Analytics and says: "users leave at step 2 of the checkout." The developer opens the same step — no errors, Sentry is silent. So it's not a JS bug, but a UX issue or skewed data from analytics. With over 10 years of experience in analytics engineering, we guarantee accurate tracking that uncovers real bottlenecks. Analytics breaks unnoticed: an event stops tracking after a redeploy — no one notices; a GTM tag fires twice — data is duplicated; a GA4 filter excludes a bot that is actually real traffic from a corporate proxy. An audit of your current tags will find the cause within a week.

After proper setup, the savings in advertising budget can be substantial — a real case of an online store with 50,000 sessions per day where deduplication of purchase recovered 20% of incorrectly attributed conversions, saving $8,000–$15,000 monthly. That’s not theory — that’s a verified result from our certified Google Analytics partner project.

Why do GA4 events duplicate and how to fix it?

Universal Analytics is gone, replaced by GA4's event-based model. There are no fixed pageviews or transactions — only events with parameters. This is more flexible but requires proper event design. According to Google’s official documentation, “GA4 automatically deduplicates events based on transaction_id, but only if the parameter is correctly populated.” Many implementations miss this.

Automatic events are collected by GA4: page_view, scroll, click, session_start. Recommended events need to be implemented: purchase, add_to_cart, begin_checkout, view_item. Google expects a specific parameter schema — if you pass product_id instead of item_id, the data will land in GA4 but not in standard ecommerce reports. Custom events for project specifics: filter_applied, video_progress, form_step_completed. Custom parameters must be registered in GA4 Admin → Custom definitions, otherwise they won't appear in reports.

A common mistake is the purchase event being duplicated. Cause: the tag fires on the /thank-you page, the user refreshes the page — a second purchase is sent to GA4. Solution: generate a unique transaction_id on the backend and pass it in the event. In our experience, 80% of e-commerce stores have this issue. GA4 deduplicates based on it (in theory — verify with DebugView). Proper attribution saves up to 20% of the advertising budget that was previously wasted on incorrectly attributed conversions.

How to set up the data layer to avoid data loss?

GTM is a tool for managing tags without code deployment. But "no code" doesn't mean "no architecture." The data layer is the foundation. We pass data from the application to GTM via dataLayer.push(). Structure: event + contextual data. For e-commerce: before opening a product page — push with product data. GTM tag reads from the data layer, not from the DOM.

window.dataLayer = window.dataLayer || [];
dataLayer.push({
  event: 'view_item',
  ecommerce: {
    items: [{
      item_id: 'SKU-12345',
      item_name: 'Product name',
      price: 1990.00,
      currency: 'USD'
    }]
  }
});

Bad practice: GTM tag parses the DOM — looks for the price in span.price, the name in h1. This breaks with any layout change. Good practice: always use the data layer. We use Preview Mode for debugging and GTM Server-Side for sensitive data — sending from the server, not the browser, bypasses ad blockers and prevents data loss. A properly implemented data layer reduces tracking errors by 95%.

How does Yandex.Metrica complement web analytics?

For a Russian audience, Metrica is a must — especially Webvisor. Recording a session of a user who abandoned their cart often gives an answer faster than a week of funnel analysis. Goals in Metrica: event-based (via ym(COUNTER_ID, 'reachGoal', 'GOAL_NAME')) or automatic (button click, page visit). Integration with CRM via Metrica Plus — passing offline conversions. Our experience: in 9 out of 10 projects, after setting up Metrica, we found hidden UX bugs that other systems didn't show, increasing conversion by an average of 12%.

What does product analytics give in Amplitude?

Amplitude is a product tool, unlike marketing-oriented GA4 and Metrica. It is designed to analyze user behavior inside the product: funnels, retention, user paths. Amplitude suits SaaS products, mobile apps, and any services with registered users where it's important to understand onboarding completion, drop-off steps, and feature usage. Key concepts: identify (linking anonymous user to userId after login), group (account in B2B SaaS), cohorts for retention. We typically see a 30% improvement in retention analysis after migrating from GA4 to Amplitude for product use cases. Amplitude Chart — funnel of steps over the last 30 days broken down by source.

Monitoring Data Quality

Analytics without monitoring is a black box. We set up:

• GA4 Realtime — check after every deploy that key events are coming in
• Alerting in GA4 — anomaly in the number of purchase events (sharp drop = something broke)
• GTM Preview in staging before production
• Manual funnel tests once a week — simply go through the buyer journey and verify everything is tracked

What the work includes

Process and timeline

1. Audit of existing tags and data (2 days)
2. Event schema design (2 days)
3. Data layer development and tag setup (3–5 days)
4. QA in Preview Mode and staging (2 days)
5. Deploy and dashboard setup (1 day)

Cost is calculated individually. Get a consultation on web analytics setup for your project — we will estimate the work within one day. Contact us to get started with a free audit of your current tracking.