A map of dots gets looked at. A number for the spot somebody pointed at gets argued with

Customer Intelligence: leadership had only ever seen its customer base as rows in an export, and seeing where customers were or how old they were took hours of spreadsheet work. As the bank’s Credit Analyst & AI Implementation Lead, I led the project and built four browser tools (three maps and a data-preparation tool). With them, both questions could be answered in the meeting that raised them, on a laptop with nothing to install and no backend.

Sponsor: A community bank in the Upper Midwest
Status: In use
Governing constraint: No backend, nothing to install
Reading time: 9 minutes

In use The build is finished, so this page describes it in the past tense. The tools are still open on executives’ laptops, which is the best outcome this Engagement had and the one I had least control over.

What this Engagement shipped

4 tools shipped
Three maps and a data-preparation tool, each a single file that opens with a double-click.
0 servers
No backend, no login and no database, so every figure is computed on the computer that opened the file.
3 silent defects
A filter, a loader and an age check, each giving wrong numbers that looked right.

The Situation

Two questions came up often enough that they needed a real answer. Where are our customers, and what does our age profile look like? The core system exported rows showing an address, a balance and sometimes a date of birth, which are static numbers that say little on their own. Turning them into a useful picture meant hours of manual work in a spreadsheet, for an answer that might not be worth it.

When a question takes hours to answer, it gets asked only when somebody is already willing to spend those hours. Often people stop asking without realizing they should, or without knowing how to ask, and nobody keeps a record of the questions that never got asked. The first thing these tools changed was the cost of asking.

What I Did

The four tools split the work. One map shows where customers are and who they are. The second, the profitability map, shows the net annual income of each location, meaning one customer address and the accounts held there. The third compares two periods. The data-preparation tool turns a raw export into the one format all three maps read.

Each is a single HTML file that opens in a browser, with no server, no login, no database and nothing to install. Because nothing runs on a server, nothing needed approval before someone could try it. The only thing any of them downloads is map imagery, and no customer data leaves the computer. I built them in a deliberate order, starting with a version that only had to prove the idea could work.

From a demo map to a tool you can question

The first version was a map with two layers and dots at hardcoded coordinates, about five hundred lines of code with no real data in it. Its only job was to prove this could be done in a browser.

Because everything about this project was new to me, I met with someone who has years of experience in GIS (mapping and geographic data) to ask what good practice looks like. He pushed back on my format, because points on a map that do nothing say nothing. That led to a map that shades each area by its net annual income when zoomed out and shows individual locations as you zoom in.

The idea that turned the picture into a tool was simple. Select an area and see real numbers for it. Click to drop a circle, drag a slider to size it, and the panel reports what is inside, including the dollars, the record counts and the age distribution. Everything added afterwards applies that idea to something else. It also changed the demos, because people stopped just looking at dots and started arguing over the numbers for a place they had picked.

Hovering a location shows its arithmetic

The profitability map colors each location by its net annual income, which is what its loans earn at their own rates minus what its deposits cost. Its scale runs from red for a loss to green for a gain, and a grid view groups an area into cells the reader can size. A color alone gives the reader nothing to check. So hovering a location shows the subtraction instead, loan income minus deposit expense, and the net that results.

Loan income and deposit expense are the two figures a lender would challenge first, so any disagreement happens over a rate or a balance, where it can be settled.

Exhibit 1 What sits inside the circle, and its net annual income

Click or drag the map, or choose an area, to place the circle, then size it with the slider.

View the data

Synthetic locations, one per row: the area it belongs to, where it sits in miles east and north of the map’s south-west corner, what its loans earn and its deposits cost in a year, in dollars, the net of the two, and its holder’s age and age band. The location the readout opens on is L27.
Location	Area	Miles east	Miles north	Loan income	Deposit expense	Net	Age	Age band
L01	Area A	5.5	9.4	58,000	2,900	55,100	44	35–49
L02	Area A	5.3	12.2	29,000	28,700	300	65	65–79
L03	Area A	6.4	10.3	41,000	38,200	2,800	26	18–34
L04	Area A	2.6	9.5	38,000	27,300	10,700	25	18–34
L05	Area A	4.1	11.9	0	40,000	−40,000	33	18–34
L06	Area A	5.3	11.6	0	24,600	−24,600	18	18–34
L07	Area A	4.5	9.9	0	21,500	−21,500	66	65–79
L08	Area A	7.6	10.9	7,000	41,000	−34,000	31	18–34
L09	Area A	3.9	10.9	11,000	23,900	−12,900	66	65–79
L10	Area A	6.3	11.5	50,000	8,300	41,700	59	50–64
L11	Area A	4.7	11.2	0	2,400	−2,400	89	80+
L12	Area A	6.3	10.4	0	19,700	−19,700	51	50–64
L13	Area A	7.8	11.2	63,000	17,800	45,200	19	18–34
L14	Area A	4.2	10.5	0	25,300	−25,300	87	80+
L15	Area A	4.3	11.2	69,000	20,300	48,700	53	50–64
L16	Area A	6.2	8.0	79,000	25,900	53,100	31	18–34
L17	Area B	14.5	7.5	25,000	25,900	−900	51	50–64
L18	Area B	13.2	4.6	51,000	8,900	42,100	67	65–79
L19	Area B	15.5	6.0	52,000	20,400	31,600	77	65–79
L20	Area B	12.2	3.9	12,000	34,800	−22,800	88	80+
L21	Area B	11.8	5.0	0	24,500	−24,500	18	18–34
L22	Area B	15.2	5.0	70,000	15,400	54,600	59	50–64
L23	Area B	12.4	6.1	64,000	18,500	45,500	80	80+
L24	Area B	12.6	9.7	0	4,700	−4,700	45	35–49
L25	Area B	15.4	6.7	21,000	35,200	−14,200	34	18–34
L26	Area B	14.1	3.8	0	22,500	−22,500	60	50–64
L27	Area B	13.5	6.9	84,200	31,600	52,600	58	50–64
L28	Area B	10.7	5.1	31,000	10,400	20,600	32	18–34
L29	Area B	12.9	3.6	0	30,200	−30,200	43	35–49
L30	Area B	14.2	7.6	0	15,400	−15,400	54	50–64
L31	Area C	5.7	4.9	0	10,300	−10,300	35	35–49
L32	Area C	6.8	2.6	87,000	40,900	46,100	83	80+
L33	Area C	3.1	4.2	82,000	22,700	59,300	64	50–64
L34	Area C	6.3	4.6	56,000	27,100	28,900	51	50–64
L35	Area C	4.8	3.8	0	24,800	−24,800	18	18–34
L36	Area C	4.7	4.5	0	16,400	−16,400	72	65–79
L37	Area C	6.3	5.7	75,000	4,000	71,000	46	35–49
L38	Area C	6.6	3.7	29,000	3,400	25,600	67	65–79
L39	Area C	5.9	5.1	51,000	17,800	33,200	66	65–79
L40	Area C	5.3	3.3	0	17,000	−17,000	56	50–64
L41	Area D	15.0	11.1	83,000	39,500	43,500	59	50–64
L42	Area D	16.2	11.1	0	8,600	−8,600	24	18–34
L43	Area D	15.6	11.4	82,000	24,300	57,700	64	50–64
L44	Area D	18.2	9.9	11,000	1,600	9,400	46	35–49
L45	Area D	15.7	13.5	83,000	9,600	73,400	22	18–34
L46	Area D	16.1	11.7	65,000	36,000	29,000	74	65–79
L47	Area D	15.9	9.8	0	29,300	−29,300	55	50–64
L48	Area D	19.4	15.3	0	14,900	−14,900	60	50–64

Source: synthetic geography and synthetic accounts, invented for this site, because the real tool’s figures are the Sponsor’s business. Each location carries its dollar figures directly rather than the balances and rates the real tool multiplies. Dot color runs from rose (loss) to blue (gain), where the real tool uses red and green.

This is the original layout, the version I first presented to the bank’s executives and board. It shows where customers are and the net annual income of each area, which gave leadership a clearer view of its customer base than any export could.

How the data-preparation tool handles a broken export

Real exports arrive broken in small ways that affect the outputs, such as a title row above the headers, an address with no coordinates or an age column holding dates. The data-preparation tool handles all of them by one standing rule.

It corrects a problem only when the correction is unambiguous. A date of birth becomes an age, and a missing coordinate is filled in from a matching address in a file that was already cleaned. Otherwise the cell is left empty. Every correction is flagged where the person using the output cannot miss it. The cell is highlighted, the reason shows on hover, and a count of corrections sits above the preview. A tool that quietly improves its data looks exactly like one that quietly damages it.

Exhibit 2 Four corrections, each flagged, and two cells left empty

Choose Prepared, then hover or focus a flagged cell to read why.

View the data

One synthetic export, line by line: each cell as it arrived, what the preparation tool made of the cells it checks, and why. Line 2 is the export’s header row.
Line	Account	Address	Coordinates	Age	Coordinates, prepared	Age, prepared	Why
1	Customer extract, run 2026-09-01	Blank	Blank	Blank	Line removed	Line removed	A title row above the headers, which is not a record.
3	A-1001	Address A	45.12, −93.40	47	Unchanged	Unchanged	None
4	A-1002	Address B	Blank	62	45.10, −93.44	Unchanged	No coordinates, so filled from the same address in a previously cleaned file.
5	A-1003	Address C	45.15, −93.37	1958-03-14	Unchanged	68	A date of birth in the age column, converted to the age on the day the export was run.
6	A-1004	Address D	Blank	39	Left empty	Unchanged	No coordinates and no matching address in a previously cleaned file, so left empty rather than placed at a guess.
7	A-1005	Address E	45.11, −93.42	3/4/24	Unchanged	Left empty	A date with a two-digit year: born in 1924 or in 2024, and nothing in the row says which.
8	A-1006	Address F	45.13, −93.39	1947-11-02	Unchanged	78	A date of birth in the age column, converted to the age on the day the export was run.

Source: synthetic, an invented export of six accounts whose addresses are placeholders and whose coordinates describe no one. No real export is shown, because every row of one is a customer of the Sponsor.

I also built a prototype that puts an assistant on top of the same data. It runs a trained language model, Llama, inside the browser. A user can ask a question in plain words and go back and forth until it is the right question, or use the map as before. Either way, the customer data never leaves the computer.

What Changed

The map plots every loan and deposit account the bank holds and filters them by geography, customer age and loan type. A question about where customers are or how old they are used to take hours of spreadsheet work, and the map now answers it directly. The bank’s CEO, CFO, senior lending staff and board use it to decide where to focus outreach and to manage risk.

The map also turned up something nobody had seen before. The bank’s deposits are concentrated in its oldest customers, enough that it belongs in a conversation about funding rather than demographics. If those deposits leave, the bank has to replace that funding, usually at a higher cost. The finding is described here without figures because it is the Sponsor’s business, and only my own work product gets numbers on this site.

The outcome I am most confident in came as a byproduct of the work. This Engagement is why the bank offered me an ongoing role after it ended. The quality of these tools is why I have kept building more there, including the Credit Process Rebuild (Engagement 02).

What I Learned

That a check which returns a plausible answer on bad input is worse than one that refuses.

An age column sometimes arrives holding date serials, which are dates of birth stored as a count of days and never formatted as dates. The map recognized these and converted them, but only above a floor of twenty thousand days, which lands in October 1954.

Any customer born before then, roughly anyone seventy-one or older, failed the serial test. Their value then failed every other test and came back as zero. Zero is a real age band in the tool, not an error value, so those customers were neither dropped nor flagged. They were filed with the infants, and the band they were taken from was the very band the demographic finding was about.

It was the third defect of this kind, where a tool gave wrong numbers that looked right. An age filter counted records it had been told to exclude. A loader read every dollar value as zero. Together they taught me to check a tool’s inputs as carefully as its outputs, and to keep a record of every change made to the data.

Exhibit 3 Born before October 1954, filed as age zero

Slide the date of birth across the floor and watch the computed age switch between zero and the true age.

View the data

Dates of birth, oldest first: each one’s day serial, whether it passes the map’s serial test by being above the floor, the age the map computed from it, its true age on 2026-09-01, the date the synthetic export in Exhibit 2 carries, and the ten-year band that age belongs in.
Date of birth	Day serial	Serial test, floor 20,000	Age the map computed	Age on 2026-09-01	Band it belongs in
1930-05-15	11,093	Fails	0	96	90–99
1935-05-15	12,919	Fails	0	91	90–99
1940-05-15	14,746	Fails	0	86	80–89
1945-05-15	16,572	Fails	0	81	80–89
1950-05-15	18,398	Fails	0	76	70–79
1954-10-02	19,999	Fails	0	71	70–79
1954-10-04	20,001	Passes	71	71	70–79
1955-05-15	20,224	Passes	71	71	70–79
1960-05-15	22,051	Passes	66	66	60–69
1965-05-15	23,877	Passes	61	61	60–69
1970-05-15	25,703	Passes	56	56	50–59
1975-05-15	27,529	Passes	51	51	50–59
1980-05-15	29,356	Passes	46	46	40–49
1985-05-15	31,182	Passes	41	41	40–49
1990-05-15	33,008	Passes	36	36	30–39
1995-05-15	34,834	Passes	31	31	30–39
2000-05-15	36,661	Passes	26	26	20–29
2005-05-15	38,487	Passes	21	21	20–29
2010-05-15	40,313	Passes	16	16	10–19
2015-05-15	42,139	Passes	11	11	10–19
2020-05-15	43,966	Passes	6	6	0–9

Source: illustrative. The dates of birth are points on a calendar, one every five years and one a day either side of the floor, and belong to no one. The floor of twenty thousand days and the month it lands in are the ones stated above, and serials count days the way a spreadsheet does. The ten-year bands are drawn for this Exhibit, not taken from the tool, and no customer’s age or count is shown, because each is the Sponsor’s.

How it surfaced, and how it was fixed

I did not find it by reading the code. I was testing something else with a test file built from the shape of a real export. Two of its ten ages came back as zero when I knew what they should have been. The same check had been written twice a few weeks apart, and the two copies had drifted. The data-preparation tool had the range right the whole time.

The fix checks for a plausible age first and a date serial second, and any value that fits neither is now flagged instead of being filed at zero. Because the fix moved ages on screen in a tool executives were already reading, I told them before it went in. Numbers changing without warning is its own problem, even when they change from wrong to right.

Test data I invent agrees with my code by design, because I build it on the same assumptions the code was written under. A test file catches what the code does not expect only when it copies a real file, as this one copied a real export’s shape. Every edge case the data-preparation tool handles came from a real file that broke it first.

Where Else This Applies

Lowering the cost of a question. Any organization where an ordinary question costs hours of somebody’s time has a set of questions it has stopped asking, and no record of which ones.

Making a visual show its arithmetic. Heat maps, scores, ratings, risk tiers and red-amber-green dashboards all hand the reader a conclusion with the working removed, and each is believed until the first person checks one.

Showing every correction. Any process that cleans data on its way into a decision has to choose between staying silent about what it changed and showing it. That covers claims intake, survey responses, meter reads and system migrations alike.

All six Engagements Ask me about this one