Source material

Rows are not prose, and a question is prose

A spreadsheet is the most tempting thing to upload and the least likely to work as it stands. It is read sheet by sheet as rows, so the data does arrive, and then nothing matches, because a question is a sentence in a customer's words and a row is a code, four numbers and a colour. They share almost no vocabulary. The fix is not a setting; it is a column.

Why this one is harder than it looks

Passages are matched against a question by meaning, and meaning is carried by words. A row reading as an identifier, two dimensions, a material and a figure has almost no words in it. Somebody asking for a small oak side table under a certain price is writing a sentence full of meaning, and there is nothing in that row for it to attach to, so the row is never retrieved even though it is exactly the answer.

The mechanical problems come next and they are all fixable in the file. A header row that is not the first row, because somebody put a title and a date above it, so the real headers are read as data and every column is unlabelled. Merged cells, which express a grouping that does not survive. Several sheets, which are read separately, so a lookup table on sheet two is not attached to the products on sheet one in any way.

Formulas are the quiet one. What is stored is what the file holds, and formulas are not evaluated by the reader, so a column of computed prices or concatenated descriptions can arrive as expressions rather than as values. A column that looks full when you open the file can be empty of anything useful once it is read.

There is one more effect worth knowing about: two documents whose extracted text is identical are stored once. Uploading the same export twice changes nothing, which is convenient. Uploading last month's export alongside this month's is a different matter, because they differ, so both are kept, and both can be retrieved for the same question with no indication of which is current.

What it has to contain

Structure rather than wording. A passage pulled out of this document has to stand on its own, because that is the only form in which it will ever be read.

Structural requirements
A description column that is a real sentenceOne sentence per row, written in the words a customer would use, naming the product, what it is, its key attributes and anything that distinguishes it. This single column does more for retrieval than every other column in the file combined, because it is the only one that shares vocabulary with a question.
The header row as the first row of every sheetNo title above it, no blank spacer row, no merged banner. One header row, one word or short phrase per column, and the same shape on every sheet in the file.
One sheet per kind of thing, named for what it holdsSheets are read separately, so a sheet has to be self-sufficient. If a product sheet refers to codes defined on a lookup sheet, the products sheet needs the meanings written into it rather than the codes alone.
Values rather than formulasEvery computed column converted to its result before the file is uploaded. Prices, totals, concatenated names, anything assembled by a formula. What is stored is what the file holds.
A stable identifier repeated inside the sentenceThe product code or name written into the description column as well as sitting in its own column, so a passage carrying the sentence also carries the identifier somebody may have typed.

The rewrite, in order

Add a sentence column, and write it once

Insert a column and write one sentence per row. It does not need to be elegant. Something on the pattern of a name, a category, the attributes that matter, and the distinguishing feature: this product is a particular kind of thing, in this size and this material, suitable for this use, available in these options.

For a few hundred rows this is a morning's work, and it can be assembled from the columns you already have as long as you convert the result to values before uploading. For a few thousand, write the sentence for the categories that get asked about and accept that the long tail is a lookup rather than a conversation.

The reason this works is worth stating plainly. Retrieval finds passages whose meaning matches the question. A sentence has meaning. A row of values has data, which is not the same thing, and no amount of it will improve a match.

Fix the sheet before you fix the words

Move the header row to row one on every sheet. Delete title rows, spacer rows and merged banners. Unmerge every merged cell and write the value into each cell it used to span, because a merged cell expresses its grouping through layout and layout does not survive.

Then look at the sheet boundaries. Anything a product row depends on that lives on another sheet has to be brought onto the row, since sheets are read separately and nothing joins them back together. A code that means something on sheet three is, on sheet one, just a code.

Replace formulas with values before uploading

Copy the computed columns and paste them back as values, in a copy of the file kept for this purpose. Keep the working file with its formulas; upload the flattened one. Doing it in a copy also means the upload is a deliberate act rather than something that happens whenever somebody saves.

Check the flattened copy by opening it and looking at the columns you care about. It takes a minute and it catches the case where a whole column of prices turned out to be expressions pointing at a sheet you did not include.

Consider exporting the sentences instead of the spreadsheet

The alternative to a description column is to leave the spreadsheet alone and export one written sentence per row into a plain text or Markdown file, then upload that. This gives you a document made entirely of the material that matches well, with none of the columns that match nothing.

It is also easier to keep current, because it can be produced again from the same source whenever the data changes, and a fresh export replaces a stale one rather than sitting beside it. Whichever route you take, upload one version and delete the previous one, because two exports that differ are two competing answers.

What happens if you skip it

A product that is in the file and cannot be found

Somebody describes what they want in ordinary language. The row that answers them is present, correct and complete, and it is never retrieved, because it contains a code, four numbers and two abbreviations and the question contains none of those. Below the match threshold the assistant declines without calling the model at all, using the wording the owner wrote, so what the customer sees is a polite statement that this is not covered.

The cost is invisible, which is what makes it the worst failure in this set. Nothing looks broken. There is no wrong answer to point at, no complaint, no screenshot. There is a catalogue that was uploaded, a business that believes its products are covered, and a stream of visitors being told nothing is known about products that are sitting in the file.

Check it against this

Before you index it

  • Every row has a description column containing a real sentence
  • The identifier appears inside that sentence as well as in its own column
  • The header row is the first row on every sheet
  • No merged cells remain anywhere in the file
  • Nothing on a product row depends on a different sheet
  • Computed columns were converted to values before upload
  • Only one version of the export is indexed at a time

Questions

Can I upload a comma separated or tab separated export instead?
Yes, those are accepted, and they remove the merged cell and multiple sheet problems for free. They do not remove the main one: a row of values still shares no vocabulary with a question, so the description column matters just as much.
How many rows is too many?
There is no hard line, but be aware of what happens as a file grows: more rows that look alike, competing for the same six retrieved passages, with the material given to the model capped. A focused file of the products people actually ask about will out-answer a complete catalogue almost every time.
Should the sheet include stock levels?
Not usefully. A spreadsheet is a snapshot of the moment it was exported, and a stock figure is the fastest thing in your business to go stale. Publish what a product is and let availability be a question that goes to a person or to a live source.

Keep reading

Try it on your own material

Upload a document or point it at your site, paste one line of HTML, then ask it something only your business could answer.

Start freeSee what it does