Reconcile Security Tags Against Sales in Under 60 Lines of Python

If you have ever built software for a shop, you know the bugs that cost real money are rarely in the code. They live in the gap between what the system says happened and what happened on the floor. Here is a small, practical example from retail operations: checking whether the hard security tags removed at the register actually match the tagged items that were sold.

The problem

Many clothing and accessory stores attach reusable hard tags to selected products. When an item is sold, the cashier removes the tag and drops it into a bin under the counter. At closing, someone counts the bin.

That count is useful data. If a register sold five tagged items but only four tags are in the bin, one of three things happened: a tag left the store still attached to a customer's purchase, a tag was dropped or pocketed, or the count was wrong. If the bin holds more tags than tagged sales, an item may have been rung up as a different SKU, or tags from returns ended up in the wrong place. Either way, a manager wants to know the next morning, not at the annual inventory.

The POS already knows what was sold. We just need to join three small CSV files.

The input files

tagged_skus.csv lists which products carry a hard tag:

sku,tag_type
JKT-104,hard_tag
JNS-220,hard_tag
SCF-031,hard_tag

sales.csv is a simple line-item export from the POS:

date,register,receipt,sku,qty
2026-11-02,R1,1001,JKT-104,1
2026-11-02,R1,1002,TEE-010,2
2026-11-02,R1,1003,JNS-220,2
2026-11-02,R2,2001,SCF-031,3
2026-11-03,R1,1010,JKT-104,1
2026-11-03,R2,2010,JNS-220,1

tag_counts.csv is what staff record at closing:

date,register,tags_in_bin
2026-11-02,R1,3
2026-11-02,R2,2
2026-11-03,R1,2

The script

Only the standard library is needed.

import csv
from collections import defaultdict

def load_tagged_skus(path):
    with open(path, newline="") as f:
        return {row["sku"]: row["tag_type"] for row in csv.DictReader(f)}

def expected_tags(sales_path, tagged):
    expected = defaultdict(int)  # (date, register) -> tags that should be in the bin
    with open(sales_path, newline="") as f:
        for row in csv.DictReader(f):
            if row["sku"] in tagged:
                key = (row["date"], row["register"])
                expected[key] += int(row["qty"])
    return expected

def counted_tags(counts_path):
    counted = {}
    with open(counts_path, newline="") as f:
        for row in csv.DictReader(f):
            counted[(row["date"], row["register"])] = int(row["tags_in_bin"])
    return counted

def reconcile(expected, counted, tolerance=0):
    keys = sorted(set(expected) | set(counted))
    report = []
    for key in keys:
        exp = expected.get(key, 0)
        got = counted.get(key)
        if got is None:
            status, diff = "NO COUNT", None
        else:
            diff = got - exp
            if diff < -tolerance:
                status = "SHORT"   # tags may have left the store on items
            elif diff > tolerance:
                status = "OVER"    # tags from unscanned items or returns
            else:
                status = "OK"
        report.append((*key, exp, got, diff, status))
    return report

if __name__ == "__main__":
    tagged = load_tagged_skus("tagged_skus.csv")
    report = reconcile(expected_tags("sales.csv", tagged),
                       counted_tags("tag_counts.csv"))
    print(f"{'date':<11}{'reg':<5}{'expect':>7}{'counted':>8}{'diff':>6}  status")
    for date, reg, exp, got, diff, status in report:
        got_s = "-" if got is None else str(got)
        diff_s = "-" if diff is None else f"{diff:+d}"
        print(f"{date:<11}{reg:<5}{exp:>7}{got_s:>8}{diff_s:>6}  {status}")
    with open("tag_reconcile_report.csv", "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["date", "register", "expected", "counted", "diff", "status"])
        w.writerows(report)

Run it in the folder with the three CSVs:

python3 tag_reconcile.py

Output for the sample data:

date       reg   expect counted  diff  status
2026-11-02 R1         3       3    +0  OK
2026-11-02 R2         3       2    -1  SHORT
2026-11-03 R1         1       2    +1  OVER
2026-11-03 R2         1       -     -  NO COUNT

The script also writes tag_reconcile_report.csv, which opens cleanly in any spreadsheet.

How to read the statuses

• OK: the bin matches tagged sales. Nothing to do.
• SHORT: fewer tags than expected. Check the receipt list for that register and day. If a customer later returns with a tag still attached, this is where it shows up first.
• OVER: more tags than expected. Often a cashier scanned a similar untagged SKU, or a tag from a return or a fitting room landed in the bin.
• NO COUNT: nobody recorded the bin. That is a process gap, and it is worth tracking on its own.

Design notes

A few choices keep this script honest and easy to maintain:

1. Keys are (date, register). Counting per register points a manager to a specific shift instead of a whole store-wide number.
2. A tolerance parameter. Some stores accept a difference of one on busy days. Pass tolerance=1 to reconcile() rather than hiding mismatches in the data.
3. Missing counts are reported, not treated as zero. Treating a missing count as zero would flag a false shortage and train people to ignore the report.
4. No database. Three CSVs and one file keep the barrier low. Schedule it with cron or Task Scheduler after the nightly POS export.

Ideas to extend it

• Add a tags_per_unit column for products that carry two tags, such as paired items, and multiply in expected_tags().
• Group SHORT results by SKU over a month to see which products lose tags most often.
• Email the report with smtplib, or push it to a shared sheet.
• Read returns from the POS and subtract re-tagged units so they do not inflate the OVER count.

Small tools like this work best when the floor process is simple and consistent: one bin per register, counted at the same time every day. If you want the operations side of the picture, such as tag handling, staff routines and counter setup, the store blog from Qova Security covers those topics in plain language: Qova Security.

The code is short on purpose. Fork it, rename the columns to match your POS export, and you will have a nightly check running before lunch.

Published by Qova Security.