Importing Product Feeds for Shopping Platforms With Checks

Shopping platforms take product data as XML feeds, and they reject items that break their rules, sometimes quietly. If you receive such a feed from a supplier, or build one for a platform, the same checks apply. The expensive way to find the problems is from the platform's diagnostics a day later. The cheap way is a validator that runs when the feed arrives and reports each problem against its product id.

The feed

<item><g:id>P1</g:id><title>Desk lamp</title><link>https://example.test/p1</link><g:image_link>https://example.test/p1.jpg</g:image_link><g:price>19.99 USD</g:price><g:availability>in_stock</g:availability><g:gtin>4006381333931</g:gtin><g:brand>Lumo</g:brand></item>
<item><g:id>P2</g:id><title>Notebook</title><link>https://example.test/p2</link><g:price>4.50 USD</g:price><g:availability>out_of_stock</g:availability></item>
<item><g:id>P3</g:id><title>Pen set</title>... <g:price>19,99 EUR</g:price> ...</item>
<item><g:id>P4</g:id> ... <g:availability>available</g:availability><g:gtin>4006381333932</g:gtin></item>
<item><g:id>P1</g:id><title>Duplicate of lamp</title> ...</item>
<item><g:id>P6</g:id><title>Tom &amp; Jerry mug</title><link>/relative/p6</link> ... <g:price>0 USD</g:price><g:availability>preorder</g:availability></item>

Note the namespaces: id, price, availability, image_link and gtin are in g, while title and link are plain RSS. A reader that asks for every field with the same prefix, or none, misses half of them. The validator reads each field with its right name.

The checks

G = "{http://base.google.com/ns/1.0}"
AVAIL = {"in_stock", "out_of_stock", "preorder", "backorder"}

def gtin_ok(s):
    if not re.fullmatch(r"\d{8}|\d{12,14}", s): return False
    d = [int(c) for c in s[::-1]]
    total = sum(x * (3 if i % 2 == 0 else 1) for i, x in enumerate(d[1:]))
    return (10 - total % 10) % 10 == d[0]

for it in root.iter("item"):
    pid = it.findtext(G + "id")
    for req in ("title", "link", "image_link", "price", "availability"):
        if not (get(req) or "").strip(): problems[pid].append(f"missing {req}")
    m = re.fullmatch(r"(\d+(?:\.\d+)?) ([A-Z]{3})", get("price") or "")
    ...

Seven rules in all: the five required fields are present and not blank; the price is a decimal, a space and a three-letter currency; the price is not zero; the availability is in the platform's list; the link is absolute; the GTIN, if present, has a valid length and check digit; the id is unique. The result on the six items:

6 items, 5 distinct ids, 5 ids with problems
  P1: duplicate id
  P2: missing image_link
  P3: price format '19,99 EUR'
  P4: availability 'available'; gtin check digit 4006381333932
  P6: price is zero; link not absolute

Six items carry five distinct ids, and all five ids have a problem (the lamp, P1, is flagged only because its id appears twice, so a report by id covers both items). Each line has its reason. Notice the form: by id, with the specific reason, one line per product. That is what a supplier can act on, and what a ticket can quote. A report that says "5 errors" or lists line numbers in a 40 MB file is not.

Three of the rules in detail

Price. 19,99 EUR fails the pattern, correctly. A supplier whose export uses a locale with a decimal comma produces it, and the failure you do not want is the quiet one: a parser that reads 19,99 as 1999 or as 19 and drops the rest. The pattern forces the platform's format, and the validator reports the offender. Currency matters as much as amount: a feed that mixes currencies has to be decided on, and the pattern keeps the currency visible. A zero price is almost always a data error, and platforms disapprove items with it.

GTIN. A GTIN (the barcode number, in EAN or UPC form) ends in a check digit: the digits to its left are weighted 3, 1, 3, 1 starting next to the check digit, summed, and the check digit makes the total a multiple of 10. My first version got the weights backwards (it began with 1) and flagged the valid EAN 4006381333931 as bad, while the invalid ...932 would have needed luck to pass. I found it by testing against a real, known-valid number. Test every checksum function with at least one known good value and one known bad value, and run it on real data before trusting its rejections. A wrong validator that rejects good items is worse than none.

Duplicates. Two items with id P1: the platform will keep one, usually the last, without telling you which. The validator reports the id once. Use the parser's sourceline for the position of each if the feed is large.

What to do with the result

A validator has three outputs: clean items, items with problems, and the report. Decide the policy in advance.

1. Load only clean items, and keep the others in a rejects table with their reasons. The default for a feed you depend on.

2. Load everything and flag problems, when losing an item costs more than listing it wrongly. Rare for prices.

3. Reject the whole feed when the rate of problems is above a threshold, which usually means the supplier's export broke: if 5 of 6 items fail, or if more than a few percent do on a feed that was clean yesterday, stop and ask.

The third rule is the one that saves you. Compare each run's problem rate with the previous run, and alert on a jump. A catalog that normally has 0.2 percent bad items and today has 40 percent is not a catalog with many bad items; it is an export with a broken template.

Loading

After validation, the load is the one from loading a product feed in XML into PostgreSQL: xmltable with the namespace, text columns converted in the SELECT, upsert on the product id. Keep the feed file, its checksum and the validation report together. Fields beyond the required ones (brand, color, size, shipping) go in a JSON column or are ignored until you need them. For updates that list only changed products, see incremental XML feed imports by last modified.

Producing a feed for a platform

The same validator is a pre-flight check for your own outgoing feed. Run it on the file you are about to upload, and compare its verdict with the platform's diagnostics for a week. Where they differ, the platform is right and the validator needs a rule; keep adding rules to it from rejections, and keep the file of rejections as a regression test. The platform's specification pages list the required attributes by product type and country, and they change; do not freeze a copy.

Related reading

For the table side see loading a product feed in XML into PostgreSQL; for the cluster overview, working with XML feeds and APIs; for tolerating defects, dealing with XML feeds that break their own schema. GTIN structure is described in the GS1 check digit calculator documentation, and the product data fields in the Google Merchant Center product data specification. Test a path in the XPath tester.

When the platform's own validator is the authority

The platform rejects something your rules accept. The platform wins. Add the rule.

The category has special requirements. Apparel, medicines and adult products have additional attributes and policies that a generic validator does not know.

The feed is generated by a plugin you do not control. Validate its output, report to the vendor, and do not patch the file by hand each day.

The data comes from a supplier with a schema. If a formal XSD exists, validate against it first (validating XML against an XSD schema) and use rules like these for the business checks on top.