Revenue by Customer Segment: Join a Grid, Then Group It

Segment is not a column on the orders table. It lives with the customer, on a list that somebody else owns, so answering “how much of this came from Enterprise versus SMB” means exporting the orders, looking up each customer’s segment in that second list, and pivoting the result by hand. Whoever asked gets a number for today. Whoever asks next month gets the exercise repeated, because nothing kept the lookup after the spreadsheet closed. Join the two tables in the JavaScript grid instead, and the lookup stays.

A derived grid does the join as configuration instead of as an export-lookup-pivot routine run from scratch each time. Point a second grid at the orders grid, join it to the customers grid on the field they share, bring segment across, and group on it. The join runs once, inside the grid, and re-runs itself: filter the orders to a date range or a single customer and the segment totals follow, because they are read off the orders grid rather than off a copy of it that somebody built in a spreadsheet an hour ago.

That turns the segment cut from a recurring export into a grid that is already open. Nobody re-runs the lookup to check it is still right, because it was never copied out far enough to go stale. The numbers below come from the published package, not from the prose.

Plain JavaScript

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

const customers = [
  { id: 'C1', customer: 'Northwind', segment: 'Enterprise', country: 'UK' },
  { id: 'C2', customer: 'Acme',      segment: 'Mid-market', country: 'US' },
  { id: 'C3', customer: 'Globex',    segment: 'Enterprise', country: 'DE' },
  { id: 'C4', customer: 'Initech',   segment: 'SMB',        country: 'US' },
  { id: 'C5', customer: 'Umbrella',  segment: 'Mid-market', country: 'FR' },
  { id: 'C6', customer: 'Soylent',   segment: 'SMB',        country: 'UK' },
];

const orders = [
  { id: 1,  customer: 'Northwind', amount: 420 },
  { id: 2,  customer: 'Acme',      amount: 180 },
  { id: 3,  customer: 'Globex',    amount: 960 },
  { id: 4,  customer: 'Northwind', amount: 310 },
  { id: 5,  customer: 'Initech',   amount: 540 },
  { id: 6,  customer: 'Acme',      amount: 275 },
  { id: 7,  customer: 'Globex',    amount: 890 },
  { id: 8,  customer: 'Umbrella',  amount: 150 },
  { id: 9,  customer: 'Soylent',   amount: 410 },
  { id: 10, customer: 'Northwind', amount: 220 },
  { id: 11, customer: 'Initech',   amount: 730 },
  { id: 12, customer: 'Acme',      amount: 95 },
  { id: 13, customer: 'Globex',    amount: 605 },
  { id: 14, customer: 'Umbrella',  amount: 340 },
  { id: 15, customer: 'Soylent',   amount: 515 },
  { id: 16, customer: 'Northwind', amount: 175 },
  { id: 17, customer: 'Initech',   amount: 820 },
  { id: 18, customer: 'Acme',      amount: 260 },
  { id: 19, customer: 'Globex',    amount: 450 },
  { id: 20, customer: 'Umbrella',  amount: 390 },
];

const customersGrid = createGrid(document.querySelector('#customers'), {
  rowKey: 'id',
  columns: [
    { field: 'customer', title: 'Customer' },
    { field: 'segment',  title: 'Segment', filter: { type: 'set' } },
    { field: 'country',  title: 'Country' },
  ],
  rows: customers,
});

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

// A second grid, joined to the customers grid on the field both share.
const bySegment = createGrid(document.querySelector('#by-segment'), {
  source: {
    mode: 'derived',
    from: ordersGrid,
    join: {
      with: customersGrid, on: 'customer', select: ['segment', 'country'],
      follow: 'filtered', type: 'inner',   // follow the customers grid's filters too
    },
    groupBy: 'segment',
    select: {
      revenue: { of: 'amount', fn: 'sum' },
      orders:  { fn: 'count' },
    },
    sort: [{ col: 'revenue', dir: 'desc' }],
  },
  columns: [
    { field: 'segment', title: 'Segment' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
    { field: 'orders',  title: 'Orders',  type: 'number' },
  ],
});

Filter the orders grid to a single customer, or the customers grid to one segment, and bySegment moves with it, because every number it shows is read off the two grids on screen rather than off a sheet built from them earlier. Country rode across in the same join: not grouped on here, but there for the next cut without opening the lookup again.

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 RevenueBySegment({ customers, orders }) {
  const [customersInstance, setCustomersInstance] = React.useState(null);
  const [ordersInstance, setOrdersInstance] = React.useState(null);

  const customerColumns = [
    { field: 'customer', title: 'Customer' },
    { field: 'segment',  title: 'Segment', filter: { type: 'set' } },
    { field: 'country',  title: 'Country' },
  ];

  const orderColumns = [
    { field: 'id',       title: 'Order' },
    { field: 'customer', title: 'Customer', filter: { type: 'set' } },
    { field: 'amount',   title: 'Amount', type: 'number' },
  ];

  const segmentColumns = [
    { field: 'segment', title: 'Segment' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
    { field: 'orders',  title: 'Orders',  type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={customerColumns}
        rows={customers}
        onGridReady={setCustomersInstance}
        style={{ height: 240 }}
      />
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setOrdersInstance}
        style={{ height: 320 }}
      />
      {customersInstance && ordersInstance && (
        <LatticeGrid
          columns={segmentColumns}
          source={{
            mode: 'derived',
            from: ordersInstance,
            join: {
              with: customersInstance, on: 'customer', select: ['segment', 'country'],
              follow: 'filtered', type: 'inner',
            },
            groupBy: 'segment',
            select: {
              revenue: { of: 'amount', fn: 'sum' },
              orders:  { fn: 'count' },
            },
            sort: [{ col: 'revenue', dir: 'desc' }],
          }}
          style={{ height: 160 }}
        />
      )}
    </>
  );
}

onGridReady hands each grid instance back to its own piece of state as soon as it mounts, and the segment panel waits for both before it renders, because join.with needs a real instance the same way from does. Filtering either grid, from its own header or a quick filter, moves the segment panel the same way the plain example does, for the same reason: both read a live grid, not a snapshot of one.

The options used to join two tables

What the example actually produced

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

Eight Enterprise orders across Northwind and Globex, five SMB orders across Initech and Soylent, seven Mid-market orders across Acme and Umbrella: twenty orders, three segments, and not one of those totals was looked up by hand.

The derived and chained grids guide covers this join in full, alongside unnesting, chaining a derived grid from a derived grid, and cross-filtering back into the source. For the same technique run the other way, grouping by a field the source grid already carries rather than one joined in, see revenue by region from your orders grid.