gridpin

Audit an address database before migration

Before a billing or CRM migration someone asks how good the addresses are. One command answers with a page you can attach to the ticket, and no address leaves your machine.

Pro New Clean and compare Command line Utilities Public sector

What you have

A CSV with an address column, here an export of service accounts with the usual mix: one house written twice, a number that is not on the street, a row with no address at all.

accounts.csvfile
account,address
A-100,"233 S Wacker Dr, Chicago, IL 60606"
A-101,"233 South Wacker Drive, Chicago, IL 60606"
A-102,"1600 Pennsylvania Ave NW, Washington, DC 20500"
A-103,"350 Fifth Avenue, New York, NY 10118"
A-104,"99999 Pennsylvania Ave NW, Washington, DC 20500"
A-105,"Unit 4, behind the gas station"
A-106,"1 Infinite Loop, Cupertino, CA 95014"

Steps

1. Write the page:

terminalCLI
$ gridpin-pro audit usa.bin accounts.csv -o audit.html
7 rows, 1 repeat an earlier address, 1 without an answer; ISO 20022: 4 structured, 2 hybrid, 1 none

2. The page is one HTML file with no outside links; these are its sections:

terminalCLI
$ grep -o '<h2>[^<]*</h2>' audit.html | sed 's/<[^>]*>//g'
Summary
Where the answers are
Distributions
Rows that name one address
No answer, by reason
What happened to each field
Not in this country's file
ISO 20022 postal address
Rows to look at

What you get

  • Summary: rows, the share answered at the house, weak answers, rows without one.
  • Distributions: the action for each row (accept, confirm, unmatched), precision, match level and score.
  • Rows that name one address: the same groups as dedupe.
  • No answer, by reason and What happened to each field: a number replaced by a neighbour's, a postcode inferred, a street corrected.
  • ISO 20022 postal address: which rows can go into a payment message as structured, hybrid or not at all.
  • Rows to look at: examples to check by hand.

Why it works

Every row is geocoded on the country file and what happened to it is counted: whether the answer reached the house, which field the file confirmed or replaced, and whether two rows land on one house. The page reports counts for all rows and examples for a sample, so it stays readable for a large table.

Limits

  • It describes the table against the address data of the country; it does not know who lives there or whether mail is delivered (see what GridPin checks).
  • A large table gives counts for every row but examples only for a sample.
  • One country file per run.

All its keys: command reference. The function: audit. Part of gridpin-pro, which comes with a Starter or Pro subscription: Free, Starter and Pro.

More