Data analytics for investigations

ACCT-341 taught data analytics for accountants: preparing data, querying ledgers with SQL, journal entry testing, Benford's law and anomaly detection in an audit setting. Investigations use the same tools for a different purpose. An auditor uses analytics to assess risk and gather evidence about whether balances are fairly stated; an investigator uses them to find specific transactions, people and relationships, to measure the full extent of a scheme, and to produce analyses that will be examined line by line by the other side. This lesson applies investigative analytics to the Saltmarsh Industrial Supply Inc. case: profiling the Northfield invoices, finding duplicates, and mapping links among people, companies and accounts. It assumes the data preparation skills from ACCT-341.

Principles of investigative analytics

Several principles distinguish investigative analytics. First, the whole population matters. An investigator who finds 10 fictitious invoices in a sample must still identify all of them to quantify the loss and to answer the question of how long the scheme ran, so investigative tests run on every transaction in the relevant period. Second, data must be validated before analysis: the extract is reconciled to the ledger with control totals, fields are profiled for missing or unusual values, and the extraction process is documented so that it can be repeated. Third, every analysis must be reproducible. The scripts, parameters and data versions are preserved, because the other side's expert may ask to rerun them. Fourth, results are leads, not conclusions. Every flagged item is followed up with documents and, where appropriate, inquiry, and the report distinguishes what the data show from what the investigator inferred.

A practical consequence is that investigators favor transparent methods. A simple rule, such as "same vendor, same amount, invoice numbers that match after removing punctuation", can be explained to a judge in a sentence and tested by anyone. A complex machine learning model may find more anomalies, but its outputs need to be translated into specific, documented transactions before they can be evidence. Machine learning and AI tools have a place in prioritizing review, especially of large volumes of email, but the findings that support a report rest on items a person has examined.

Profiling a suspect vendor

The first analysis in the vendor workstream profiles every payment to Northfield Logistics Services from the vendor's creation in March 2022 to its last invoice in January 2026. The program below reconstructs that profile from a simulated payables extract, together with a duplicate invoice test run across all vendors.

# Analytics on Saltmarsh's accounts payable, April 2022 to January 2026 (simulated data, seed 460).
# 1) Profile one vendor's invoices.  2) Find duplicate and near-duplicate invoices across vendors.
import random, re, statistics as st
from collections import defaultdict
random.seed(460)

inv = []  # (vendor, invoice number, date as yyyy-mm, amount)
n = 0
for y in range(2022, 2027):
    for m in range(1, 13):
        if (y, m) < (2022, 4) or (y, m) > (2026, 1): continue
        for _ in range(random.choice([2, 2, 2, 3])):
            n += 1
            inv.append(("Northfield Logistics Services", f"NLS-{1000 + n}", f"{y}-{m:02d}",
                        round(random.uniform(3600, 4990) / 5) * 5))
other = [("Atlantic Fastener Co.", "INV-10442", "2025-03", 12844.10),
         ("Atlantic Fastener Co.", "INV10442", "2025-04", 12844.10),
         ("Coastal Safety Wholesale", "88213", "2025-06", 7310.00),
         ("Coastal Safety Wholesale", "88213-A", "2025-06", 7310.00),
         ("Bayline Packaging", "B-5521", "2025-07", 2230.40),
         ("Bayline Packaging", "B-5522", "2025-07", 2230.40)]
inv += other

nls = [r for r in inv if r[0].startswith("Northfield")]
amts = [r[3] for r in nls]
print(f"Northfield: {len(nls)} invoices, total {sum(amts):,.2f}, "
      f"median {st.median(amts):,.2f}, all below 5,000: {all(a < 5000 for a in amts)}")
print(f"  share between 4,500 and 4,999: {sum(4500 <= a < 5000 for a in amts) / len(amts):.0%}")
nums = [int(r[1].split('-')[1]) for r in nls]
print(f"  invoice numbers run {min(nums)} to {max(nums)} with no gaps: {sorted(nums) == list(range(min(nums), max(nums) + 1))}")
by_year = defaultdict(float)
for r in nls: by_year[r[2][:4]] += r[3]
print("  paid by year: " + ", ".join(f"{y} {v:,.0f}" for y, v in sorted(by_year.items())))

def key(num): return re.sub(r"[^0-9]", "", num).lstrip("0")
print("\nPossible duplicates (same vendor and amount, invoice numbers equal after removing non-digits,")
print("or consecutive numbers with identical amounts):")
seen = defaultdict(list)
for r in other:
    seen[(r[0], r[3])].append(r)
for (vend, amt), rows in seen.items():
    if len(rows) > 1:
        a, b = rows[0][1], rows[1][1]
        why = "same number" if key(a) == key(b) else "consecutive numbers" if abs(int(key(a)) - int(key(b))) == 1 else "number variant"
        print(f"  {vend:<26}{a:>10} / {b:<10}{amt:>11,.2f}  {why}")

Northfield: 106 invoices, total 458,940.00, median 4,340.00, all below 5,000: True
  share between 4,500 and 4,999: 41%
  invoice numbers run 1001 to 1106 with no gaps: True
  paid by year: 2022 84,580, 2023 119,105, 2024 119,060, 2025 127,210, 2026 8,985

Possible duplicates (same vendor and amount, invoice numbers equal after removing non-digits,
or consecutive numbers with identical amounts):
  Atlantic Fastener Co.      INV-10442 / INV10442    12,844.10  same number
  Coastal Safety Wholesale       88213 / 88213-A      7,310.00  same number
  Bayline Packaging             B-5521 / B-5522       2,230.40  consecutive numbers

The profile is striking. Saltmarsh Industrial Supply Inc. paid Northfield 458,940 dollars on 106 invoices, every one below the 5,000 approval limit, with a median of 4,340 and 41 percent between 4,500 and 4,999. The invoice numbers run from 1001 to 1106 without a gap, which means that, if the invoices are genuine, Saltmarsh Industrial Supply Inc. was Northfield's only customer for four years. Payments rose from about 85 thousand in the nine months of 2022 to about 127 thousand in 2025, the typical growth of an undetected scheme, and stopped in January 2026, the month in which Calloway asked a payables clerk to hold Northfield invoices during the year-end audit. Each of these features would be unremarkable alone; together, with the absence of any delivery record, they describe a fictitious vendor.

The duplicate test, run across all vendors, finds three pairs of possible duplicates unrelated to Northfield: two Atlantic Fastener invoices whose numbers differ only by a hyphen, two Coastal Safety Wholesale invoices that differ by a suffix, and two consecutive Bayline Packaging invoices for identical amounts. Follow-up shows that the first two pairs were genuine duplicate payments caused by re-keyed invoices, recovered from the suppliers as credits totaling 20,154.10 dollars; the Bayline pair was two separate legitimate deliveries. Duplicate payments are a common recovery opportunity, and including them in the report shows that the analytics were applied evenly rather than aimed at one person.

Other transaction tests

Investigators draw on a standard library of tests, choosing those that fit the allegation. Threshold tests look for amounts just below approval limits, reporting thresholds or tolerance levels, as in the Northfield profile and the structuring screen in lesson 7. Sequence and gap tests look for missing or out-of-order document numbers, which may indicate removed documents, and for sequential numbers from vendors, which suggest a single customer. Timing tests look for transactions on weekends, holidays or at unusual hours, entries posted just before or after period-end, and approvals made seconds after entry. Round-number tests look for amounts that end in many zeros, which are common in estimates and fabricated figures. Digit tests based on Benford's law, covered in ACCT-341, compare the distribution of leading digits, or the first two digits, with the expected distribution; in investigations they are used to narrow the search to digit combinations that occur too often, such as invoices beginning with 46 or 49 when an approval limit is 5,000.

Fuzzy matching handles the fact that names and addresses are recorded inconsistently. Techniques include normalization (removing punctuation, case and common words), phonetic codes that match names that sound alike, and edit distances that count the number of character changes needed to turn one string into another. Fuzzy matching powers vendor-employee comparisons, duplicate vendor detection and conflict checks like the one in lesson 1. Its thresholds trade false positives against missed matches, and the investigator should report the thresholds used.

Link analysis

Link analysis represents people, companies, addresses, telephone numbers, email addresses and bank accounts as nodes in a network, with edges where records connect them. It reveals relationships that no single file shows, such as a vendor whose director lives at an employee's emergency contact address, or a consultant who shares a telephone number with a buyer. Specialized software visualizes large networks, but the core idea is simple, as the program below shows. It builds a graph from records gathered in the investigation and finds the connected components: groups of entities linked directly or through shared attributes.

# Link analysis: people, companies, addresses, phone numbers and bank accounts as a graph.
# Connected components show which entities are linked, directly or through shared attributes.
from collections import defaultdict, deque

links = [  # (entity, attribute) pairs drawn from the vendor master, HR files and corporate registry (invented)
    ("Northfield Logistics Services", "addr:14 Ferncliff Drive"),
    ("Northfield Logistics Services", "acct:00812-6634"),
    ("Northfield Logistics Services", "director:G. Pellerin"),
    ("R. Calloway (employee E117)", "addr:14 Ferncliff Drive"),       # emergency contact address
    ("G. Pellerin", "director:G. Pellerin"),
    ("G. Pellerin", "addr:14 Ferncliff Drive"),
    ("Keel and Rudder Consulting", "phone:555-0147"),
    ("Keel and Rudder Consulting", "acct:00977-3012"),
    ("Bayline Packaging", "addr:5 Dockside Road"),
    ("Bayline Packaging", "acct:00641-2207"),
    ("S. Fraser (employee E161)", "phone:555-0147"),
    ("Atlantic Fastener Co.", "acct:00907-5521"),
]
g = defaultdict(set)
for a, b in links:
    g[a].add(b); g[b].add(a)
seen, comps = set(), []
for node in g:
    if node in seen: continue
    comp, q = [], deque([node]); seen.add(node)
    while q:
        x = q.popleft(); comp.append(x)
        for y in g[x]:
            if y not in seen: seen.add(y); q.append(y)
    comps.append(comp)
for comp in sorted(comps, key=len, reverse=True):
    ents = sorted(c for c in comp if ":" not in c)
    attrs = sorted(c for c in comp if ":" in c)
    tag = "LINKED" if len(ents) > 1 else "single"
    print(f"[{tag}] {'; '.join(ents)}")
    print(f"         shared or held: {', '.join(attrs)}")

[LINKED] G. Pellerin; Northfield Logistics Services; R. Calloway (employee E117)
         shared or held: acct:00812-6634, addr:14 Ferncliff Drive, director:G. Pellerin
[LINKED] Keel and Rudder Consulting; S. Fraser (employee E161)
         shared or held: acct:00977-3012, phone:555-0147
[single] Bayline Packaging
         shared or held: acct:00641-2207, addr:5 Dockside Road
[single] Atlantic Fastener Co.
         shared or held: acct:00907-5521

Two components contain more than one entity. The first links Northfield, its director G. Pellerin and R. Calloway through the Ferncliff Drive address and the directorship; the bank account belongs to the same component because it is held by Northfield. The second links Keel and Rudder Consulting with S. Fraser, an employee in purchasing, through a shared telephone number; this is the same vendor that scored in the review category in lesson 5 and the same employee whose split purchases remained open in lesson 4. The component does not prove misconduct, but it gives the team a specific relationship to investigate, and counsel adds it to the scope as a related matter. Bayline Packaging and Atlantic Fastener stand alone.

Text analytics and email review

Much of the evidence of intent lies in communications. Email and messaging review in investigations uses keyword searches, conversation threading, date filters and analysis of who communicated with whom and when. In large matters, technology-assisted review uses machine learning trained on reviewers' decisions to rank documents by likely relevance, a technique courts in both countries have accepted in litigation when properly validated. Keyword lists should be designed carefully, tested for false positives and negatives, and documented. In the Saltmarsh Industrial Supply Inc. revenue workstream, a search of the sales team's email for terms such as "side letter", "return", "Q4" and the distributors' names, limited to December 2025 and January 2026, identified the side letters and an email in which C. Varga asked a sales manager to send the letters "directly, not through contracts". The review team examined every hit; the search terms and the hit counts are documented in the working papers.

Presenting analytics as evidence

Analyses become evidence only if they are understandable and verifiable. Good practice includes describing each test in plain language, stating the data sources and periods, reporting the number of items flagged and the outcome of follow-up for each (confirmed, explained, unresolved), including the scripts or queries in the working papers, and preparing summary schedules that tie to the underlying records. Charts should be honest, with axes that start at zero where appropriate and clear labels, following the visualization principles in ACCT-341. When an analysis depends on judgment, such as a matching threshold or a scoring weight, the report should say so and, where useful, show the effect of alternative choices.