Reconcile Excel with your system: billboard locations

If your Excel sheets and your system disagree about where your billboards are, the usual cause is that nothing in either carries a shared site code, so every match is a guess. The first thing to do is find a column that exists in both, and where there is none, record how each match was made. At one outdoor media operator, of 47 boards held in both places, 34 differed, by anything from 4 metres to 3,365 metres.

Here is how we reconciled them.

An outdoor media operator kept two spreadsheets: one with the latitude and longitude of each board, one with the campaigns booked on them. The system held the same boards, and the same campaigns, in its own database. Nobody could say which of the three to believe.

The trouble was that neither spreadsheet carried a site code. There was nothing that said which row was which board, and the two sources had drifted apart over time. A board pinned in the wrong place sends a crew to the wrong street and puts the wrong point on the map a client is shown. A campaign that is in the sheet but not in the system is a booking the system does not know about.

What was actually going on

We audited the two workbooks against the inventory of 101 boards. The coordinates sheet had 107 rows and the campaign sheet 103. With no site code, the only join was the location text against the board’s name, done in three passes, with every match recording how it was made.

The results, for coordinates: of the 47 boards with coordinates in both places, 13 agreed and 34 differed, by anything from 4 metres to 3,365 metres. Twenty-seven boards held no coordinates at all. Twenty-seven sheet rows matched no board, 24 of them with candidates to judge by eye and 3 genuinely new. Twenty-five boards in the system were absent from the sheet. Two cells held text, not numbers.

For bookings: 32 campaigns agreed, 6 showed a different brand, 19 were booked in the sheet with nothing live in the system, and 1 was free in the sheet but booked in the system.

Checking the work caught our own mistakes. A first pass not limited to the client’s own records matched two rows to demonstration boards and would have proposed overwriting them. Date cells were being blanked by a careless flattening step, which emptied a whole availability column. And a branch for uncertain matches could never be reached, so every uncertain match read as certain.

What we changed

The result was one workbook that mirrors the client’s two files row for row, with our columns added to the right: the site code, who is assigned, what the system holds, and the finding. The sites sheet is the authority. The campaigns sheet is context only, and the importer never touches a booking.

The importer is a dry run by default. It applies everything in one transaction, can be run twice without changing anything the second time, and checks that coordinates fall inside India. On the sandbox copy it applied 288 changes: 200 allocations, 61 coordinate fixes and 27 new boards. Boards in the copy went from 101 to 128, and boards with coordinates from 57 to 111.

On 24 September, 44 production boards had no location, 27 of them recoverable from the sheet. We filled those 27 after taking a backup, then 2 more from a text-only match, leaving 15 whose sheet candidates belong to other boards.

What it did not fix

The 34 disagreements were never decided by us. The sheet’s coordinates overwrite the system’s by design, and the log does not say which set was the more accurate.

Fifteen boards in production still have no location, and the 19 bookings held only in the sheet are a question for the client, not something an importer can settle.

How to reconcile your own spreadsheet

1. Find out whether any column exists in both sources that could serve as a key. If there is none, give every board a site code.
2. Match in passes, from the surest to the weakest, and record how each match was made. If there is no key, every reconciliation is a guess.
3. Decide in advance which source wins when they disagree. We made the sites sheet the authority and never let the importer touch a booking.
4. Try the import on a copy first, and ask it to report what it would change before it changes anything.
5. Take a backup before applying it to live data, and check that running it twice changes nothing the second time.
6. Send rows that match nothing to the owner of the data rather than guessing.

Where this ends up

Board locations and bookings belong in one place, and in AdBoard, the system that runs for Gold Sign Media, an outdoor media operator, that place is now the inventory with a recorded site code on every board.