Reconciling Amazon variation families at scale: a methodology for 1,000+ SKU catalogs.

$What "variation drift" actually means

Amazon's variation system is a parent-children tree. A parent ASIN holds the "family" (the dropdown the customer sees, with options like color and size). Each child ASIN is a specific combination. The parent has a variation_theme attribute that declares which dimensions vary (e.g., SizeName-ColorName or just SizeName). The children have parentage_level=child and a parent_asin reference.

Drift happens when this tree disagrees with your local Odoo state in any of four ways:

• Orphaned children: Odoo thinks a SKU is part of a parent variation; Amazon has the child but doesn't have it linked to the parent. The child becomes invisible to search.
• Phantom children: Amazon has a child ASIN linked to a parent; Odoo doesn't have any record of that SKU. Inventory and price updates silently no-op against it.
• Missing variation theme: Amazon has parent and children correctly linked, but the parent's variation_theme attribute is unset or wrong. The variation dropdown doesn't render correctly on the product page.
• Theme/attribute mismatch: Parent declares variation_theme = SizeName-ColorName but child has only SizeName populated. Amazon rejects the variation as malformed.

Each of those creates a slightly different observable symptom. None are obvious from the Seller Central dashboard.

$Stage 1. Build the reconciliation matrix

For every active SKU in Odoo and every active ASIN on Amazon, classify into one of these states. The query that generates the matrix:

def build_reconciliation_matrix(sp_client, seller_id, marketplace_id, odoo_skus):
    """Returns a dict per SKU classifying it into one of:
    - matched_parent: parent on both sides, correctly linked
    - matched_child: child on both sides, correctly linked
    - amazon_orphan_child: child on Amazon, no parent link
    - amazon_phantom_child: child on Amazon, not in Odoo
    - odoo_orphan: SKU in Odoo, no corresponding ASIN
    - theme_mismatch: parent variation_theme disagrees with children's attributes
    """
    matrix = {}

    # Pull every Amazon listing for the seller.
    # Use /listings/2021-08-01/items?sellerId={seller}&pageSize=100 with cursor.
    amazon_listings = pull_all_amazon_listings(sp_client, seller_id, marketplace_id)

    for sku, listing in amazon_listings.items():
        parent_asin = listing.get("parent_asin")
        parentage = listing.get("parentage_level")
        odoo_match = odoo_skus.get(sku)

        if parentage == "parent":
            # Check variation_theme is set and matches child set.
            theme = listing.get("variation_theme")
            child_skus = get_children_of(sp_client, listing["asin"])
            matrix[sku] = {
                "state": "matched_parent" if theme and child_skus else "theme_mismatch",
                "asin": listing["asin"],
                "theme": theme,
                "children": child_skus,
            }
        elif parentage == "child":
            if parent_asin and odoo_match:
                matrix[sku] = {"state": "matched_child", "parent_asin": parent_asin}
            elif parent_asin and not odoo_match:
                matrix[sku] = {"state": "amazon_phantom_child", "parent_asin": parent_asin}
            elif not parent_asin:
                matrix[sku] = {"state": "amazon_orphan_child"}
        elif parentage is None:
            # Standalone listing, not part of any variation family.
            matrix[sku] = {"state": "standalone"}

    # Sweep Odoo for SKUs without Amazon counterpart.
    for sku in odoo_skus:
        if sku not in matrix:
            matrix[sku] = {"state": "odoo_orphan"}

    return matrix

Run that, count by state. On the 7,000-SKU engagement, the distribution was roughly:

• ~5,800 matched_child (90% of catalog, working correctly)
• ~360 matched_parent (working correctly)
• ~520 amazon_orphan_child (invisible to search)
• ~140 amazon_phantom_child (no Odoo record)
• ~180 theme_mismatch (parent variation theme wrong)
• ~60 odoo_orphan (Odoo has SKU, Amazon doesn't)

The "703 variation families" number is the count of distinct PARENT ASINs that had at least one of these problems. Each parent ASIN's family needed reconciliation work, even if most of its children were correct.

$Stage 2. Resolve in dependency order

The order matters. Don't try to fix children before fixing parents, because a child needs a parent that has the correct variation_theme before the link operation will succeed.

Dependency order:

1. Fix theme_mismatch first. For each parent with a wrong variation_theme, GET the children, infer the correct theme from the attributes that vary, PATCH the parent to set the correct theme.
2. Link amazon_orphan_child to parents. For each orphaned child, look up which parent it should belong to (Odoo's variation model has this; usually the product.template ID maps to the parent ASIN). PATCH the child with parentage_level=child and the correct parent_asin.
3. Decide on amazon_phantom_child. These are SKUs Amazon has but Odoo doesn't. Two options: import them into Odoo (preserving Amazon's catalog state) or delete from Amazon (preserving Odoo's state). On the engagement: ~95% of phantoms were imports, products that had been added directly on Amazon and never back-synced to Odoo.
4. Push odoo_orphan to Amazon. For each SKU in Odoo without an Amazon counterpart, create the listing on Amazon. This is the standard publish flow; it just needs to happen explicitly rather than waiting for the next regular publish trigger.

Each step gets verified before moving to the next. After fixing theme_mismatch, GET each parent and confirm variation_theme is set correctly before starting child-link work. The whole reconciliation can run in parallel batches once the per-stage gates pass.

$Stage 3. Submit, monitor processing reports

Each fix is a PUT /listings/2021-08-01/items/{seller}/{sku} submission. Amazon returns a submissionId. The actual processing is async and reports come back via GET /listings/2021-08-01/items/{seller}/{sku}/submission/{submissionId} minutes to hours later.

The reconciliation submissions on the engagement totaled 7,644 across all 703 families. The monitoring loop:

def submit_and_track(sp_client, seller_id, sku, payload):
    response = sp_client.put(
        f"/listings/2021-08-01/items/{seller_id}/{sku}",
        json=payload,
    )
    submission_id = response.json()["submissionId"]
    log_submission(sku, submission_id, payload)
    return submission_id

def poll_submissions(sp_client, seller_id, pending_ids):
    """Check each pending submission. Move accepted ones out of pending,
    flag rejected ones for retry or manual review."""
    for sku, sub_id in list(pending_ids.items()):
        r = sp_client.get(
            f"/listings/2021-08-01/items/{seller_id}/{sku}/submission/{sub_id}"
        )
        status = r.json()["status"]
        if status == "ACCEPTED":
            mark_complete(sku, sub_id)
            del pending_ids[sku]
        elif status == "REJECTED":
            reasons = r.json().get("issues", [])
            flag_for_retry(sku, reasons)
            del pending_ids[sku]
        # Else still processing, leave in pending.

The reconciliation log on the engagement: 7,644 submissions, 7,644 ACCEPTED. No rejections needed follow-up. The reason zero rejections: the reconciliation matrix in Stage 1 caught the structural problems first; by the time we submitted, every payload matched a state Amazon's validator would accept.

The shortcut that doesn't work: just re-publishing everything as if it were new. Amazon's validator catches the structural problems anyway, and you get a mountain of rejection-issue codes to debug instead of a clean matrix.

$Stage 4. Verify the end state

Re-run the reconciliation matrix. Every SKU should be in matched_parent, matched_child, or standalone. Any remaining orphans / phantoms / theme_mismatches indicate either:

• A submission was accepted but didn't actually propagate (rare; usually means Amazon's async indexing hasn't completed; wait 24 hours and re-check).
• A submission was for a parent whose children weren't yet linked when we ran the matrix. The verification pass catches these.
• A new drift occurred during the reconciliation window (a child got created or modified by a different code path while we were working).

The end-state verification should produce a matrix with 0 entries in the "drift" states. That's the 703/703 result. If you don't hit zero, the remaining cases need manual triage.

$Why this matters commercially

Variation drift is the single largest silent revenue leak in Amazon catalogs at scale. Orphaned children are invisible to search; customers literally can't find them. Phantom children consume your inventory accounting capacity but don't sell. Theme-mismatched parents render badly on the product page, depressing conversion. None of these show up as red banners in Seller Central; they just quietly underperform.

The 703/703 reconciliation on the engagement closed a leak that had been growing for at least 18 months before I arrived. Post-reconciliation, the catalog's Buy Box win rate, search impressions, and conversion-on-PDP all moved up. I never quantified the dollar impact precisely (Amazon doesn't make pre-vs-post-reconciliation A/B easy), but the order-volume delta in the 60 days after reconciliation closed was material.

This is the kind of pattern an audit surfaces. The reconciliation matrix is the diagnostic; the four-stage fix is the remediation. Without the methodology, "fix variation drift" sounds like a vague consulting deliverable. With it, the work is finite, falsifiable, and complete-when-done.