Correlation Matrix: Which Measures Move Together

Someone asks whether the discounts are eating into margin, and the honest way to check is a spreadsheet: sort the orders by discount, scroll the amount column next to it, and look for a pattern. That just about works for two columns. Bring cost into the question too, because margin should track cost before it tracks anything else, and the eyeballing stops working: nobody holds three columns of scatter in their head at once, and whoever checked it yesterday has to check it again today the moment someone filters the orders to one region.

A derived grid turns that squint into a lookup. Point a second grid at the orders grid, name the measures to compare, and it returns one row for every pair: amount against cost, amount against discount, cost against discount, each carrying Pearson’s r. Filter the orders grid to a region, a rep or a date range, and all three coefficients recompute off whatever is left, because they are read from the rows on screen rather than sorted and squinted at by hand.

That is the difference between running a correlation every time someone asks and having one that is already running. Three numbers that used to live in whoever last checked them now live in a row each of a grid, next to a count of how many orders each one covered, so a coefficient over eleven filtered orders is never mistaken for the same claim as one over all twenty.

Below: amount and cost move almost in lockstep, and both run the opposite way from discount, which is the actual finding. The orders getting discounted are the smaller ones, not the ones eating into margin.

Plain JavaScript

import { createGrid } from '@toclocoinc/lattice-grid';

const orders = [
  { id: 1,  amount: 420, cost: 260, discount: 0 },
  { id: 2,  amount: 180, cost: 140, discount: 10 },
  { id: 3,  amount: 960, cost: 540, discount: 0 },
  { id: 4,  amount: 310, cost: 210, discount: 15 },
  { id: 5,  amount: 540, cost: 330, discount: 0 },
  { id: 6,  amount: 275, cost: 205, discount: 20 },
  { id: 7,  amount: 890, cost: 510, discount: 0 },
  { id: 8,  amount: 150, cost: 130, discount: 25 },
  { id: 9,  amount: 410, cost: 250, discount: 0 },
  { id: 10, amount: 220, cost: 175, discount: 10 },
  { id: 11, amount: 730, cost: 410, discount: 0 },
  { id: 12, amount: 95,  cost: 90,  discount: 5 },
  { id: 13, amount: 605, cost: 360, discount: 0 },
  { id: 14, amount: 340, cost: 230, discount: 30 },
  { id: 15, amount: 515, cost: 300, discount: 0 },
  { id: 16, amount: 175, cost: 150, discount: 15 },
  { id: 17, amount: 820, cost: 470, discount: 0 },
  { id: 18, amount: 260, cost: 195, discount: 20 },
  { id: 19, amount: 450, cost: 275, discount: 0 },
  { id: 20, amount: 390, cost: 240, discount: 10 },
];

const ordersGrid = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: [
    { field: 'id',       title: 'Order' },
    { field: 'amount',   title: 'Amount',   type: 'number' },
    { field: 'cost',     title: 'Cost',     type: 'number' },
    { field: 'discount', title: 'Discount', type: 'number', filter: { type: 'set' } },
  ],
  rows: orders,
});

// A second grid, one row per pair of measures.
const pairs = createGrid(document.querySelector('#pairs'), {
  rowKey: '__key',
  source: {
    mode: 'derived',
    from: ordersGrid,          // the grid to read
    follow: 'filtered',       // read whatever the filters leave
    statistics: { fn: 'correlation', columns: ['amount', 'cost', 'discount'] },
  },
  columns: [
    { field: 'a',           title: 'Measure' },
    { field: 'b',           title: 'Measure' },
    { field: 'coefficient', title: 'Correlation (r)', type: 'number' },
    { field: 'n',           title: 'Orders', type: 'number' },
  ],
});

Filter the orders grid to the rows with no discount, and pairs drops to three rows computed over that narrower set, each with its own n, because every coefficient is read off the orders grid on screen rather than off a correlation run once and pasted into a doc.

React

import React from 'react';
import { createGrid } from '@toclocoinc/lattice-grid';
import '@toclocoinc/lattice-grid/css';
import { createLatticeGrid } from '@toclocoinc/lattice-grid/modules/react';

// Build the component once, at module scope, not on every render.
const LatticeGrid = createLatticeGrid({ React, createGrid });

function CorrelationMatrix({ orders }) {
  const [ordersInstance, setOrdersInstance] = React.useState(null);

  const orderColumns = [
    { field: 'id',       title: 'Order' },
    { field: 'amount',   title: 'Amount',   type: 'number' },
    { field: 'cost',     title: 'Cost',     type: 'number' },
    { field: 'discount', title: 'Discount', type: 'number', filter: { type: 'set' } },
  ];

  const pairColumns = [
    { field: 'a',           title: 'Measure' },
    { field: 'b',           title: 'Measure' },
    { field: 'coefficient', title: 'Correlation (r)', type: 'number' },
    { field: 'n',           title: 'Orders', type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setOrdersInstance}
        style={{ height: 320 }}
      />
      {ordersInstance && (
        <LatticeGrid
          rowKey="__key"
          columns={pairColumns}
          source={{
            mode: 'derived',
            from: ordersInstance,
            follow: 'filtered',
            statistics: { fn: 'correlation', columns: ['amount', 'cost', 'discount'] },
          }}
          style={{ height: 160 }}
        />
      )}
    </>
  );
}

onGridReady hands the orders instance back as soon as it mounts, and the pairs panel waits for it before it renders, because statistics.from needs a real grid the same way groupBy does anywhere else. Filtering the orders grid, from its own header or a quick filter, moves the pairs panel the same way the plain example does, because both read a live grid rather than a coefficient computed once and handed down as a prop.

The options behind a correlation matrix in JavaScript

What the example actually produced

Running the plain JavaScript example above with createHeadlessGrid from the published package, and printing pairs’s rows, gives:

Three pairs out of three columns, each over all twenty orders: amount and cost track each other almost exactly, and discount runs against both of them, confirming that the orders being discounted are the cheaper ones rather than the ones doing the most damage to margin.

The derived and chained grids guide covers statistics alongside grouping, joins, column profiles and chaining a derived grid from a derived grid in full. For another look at two measures on the same orders, grouped rather than compared, see units and revenue by SKU from your orders grid.