Cleaning and profiling a national register

The problem

Grants teams, funders and peak bodies often need a trustworthy list of eligible organisations, for example charities based in the ACT, sorted by size and purpose.

The national register is large and, like most real-world data, has blanks, inconsistent values and possible duplicates. Using it as-is risks wrong counts and missed or doubled-up organisations.

Our approach

• Downloaded the published register file (66,623 records, 67 columns) and kept the raw file with a SHA-256 fingerprint.
• Validated every ABN against the official ATO check-digit rule, and checked postcodes against state and territory ranges, dates, websites and country values.
• Standardised names, towns, states and website addresses, then flagged possible duplicates (same normalised name, or a shared website) for human review rather than deleting them.
• Profiled ACT-based charities by ACNC size category and stated charitable purpose.

The result

• Every one of the 66,010 ABNs present passed the check-digit test except 2; 613 records had no ABN in the public file, and 304 of those have names indicating Private Ancillary Funds.
• Flagged for review: 1,066 records sharing a normalised legal name with another charity, 6,312 sharing a website, 792 with an establishment date later than their registration date, and 201 postcodes outside their state's range.
• Profile: 1,374 charities are based in the ACT (821 small, 227 medium, 169 large, the rest unclassified) and 3,158 charities say they operate in the ACT. Education, religion and social welfare are the most common stated purposes.
• Outputs: a cleaned extract, a duplicate review list, a quality report and a summary chart.

Quality checks

What we would do differently for a client

• Flags are not errors. Two charities can legitimately share a name or a website, so the review list goes to a person who knows the domain.
• Postcode checks used simplified state ranges. A production job would validate against a full postcode reference file.