Skip to content

1.8.3

⚙️ Patch 1.8.3 — Wholesale catalog price, Keepa analyze/margin, push batching

🚀 Summary

  • Catalog price column — lowest in-stock wholesale offer across all suppliers in wholesale_shopping_list (Jadro, Rney, Qogita, …) with seller and stock; backup when live Qogita Price is empty.
  • Target EK aligned with Amazon Revenue Calculator Nettospanne (10% = profit ÷ VK brutto at avg90_new_price_cents).
  • Analyze by EAN re-runs on already-analyzed rows, batches with offset pagination (no infinite loop), ~1s per 500 rows (single DB commit, no per-row API calls).
  • Keepa pipeline — 10k ASIN cap, fetch skip statuses, 429 → token wait; push batches 200 per call.
  • Watchlist cron — catalog flag sync + Discord when rows wrongly marked Not in catalog.
  • Otto repricer — recalculate_articles re-activates paused articles when min/max change.

✨ Improvements

Wholesale catalog price (all suppliers)

  • wholesale_offers.py — lowest price with stock > 0 across all supplier columns.
  • API fields: wholesale_best_price, wholesale_best_stock, wholesale_best_seller.
  • Frontend — Catalog price column (price, seller, stock).
  • Push to Qogita still requires Qogita catalog (qogita_preis / qogita_bestand); other wholesalers are sourcing backup only.

Suppliers: Jadro, Rney, Polarmoda, Parfum Wholesales, Engelsa, B2B Luxury, Nunu, Parfumtrend, Qogita, Novaengel, Naypes.

Target EK — Amazon Nettospanne margin

Problem: Target EK used margin on net revenue (VK ÷ 1.19). Amazon’s calculator shows Nettospanne = profit ÷ VK brutto — so 10% target looked like ~8.4% in Seller Central (e.g. VK €28.69, fees €7.88, EK €13.82 → 8.39% Nettospanne).

Formula (VK = avg90_new_price_cents / 100):

VK_netto = VK_brutto / 1.19
profit_target = VK_brutto × margin%
EK = VK_netto − Amazon fees − profit_target

Example: VK €28.69, fees €7.88, margin 10% → target EK €13.36 (not €13.82). VAT €4.58 is computed correctly; fees unchanged.

  • UI: Margin % (Nettospanne), column Target EK (10% Nettospanne).
  • compute_margin_breakdown() — logs/compare margin_on_gross_vk_pct (Amazon) vs margin_on_net_revenue_pct.

Analyze by EAN — reanalyze, speed, batching

  • reanalyze: true — recomputes EK on rows that already have keepa_scanned_at (needed after margin formula changes).
  • Offset pagination — ORDER BY gtin + offset/limit; API returns reanalyze_total, reanalyze_next_offset, reanalyze_complete (fixes endless 500-row loops from updated_at cursor).
  • Performance — one commit() per batch; catalog EAN keys loaded once; skip DB write when EK/margin/catalog flag unchanged (ek_skipped_write).
  • Logging — batch start/complete only with duration_ms, ek_changed, ek_skipped_write (no per-GTIN lines).
  • Frontend loops until reanalyze_complete (~3 batches for 1384 items at 500/batch).

Push batching

  • Default 200 per call (QOGITA_PUSH_BATCH_SIZE); frontend loops until queue drained.

Watchlist cron — catalog flag correction

  • After Qogita sync: sync_watchlist_catalog_flags() + Discord alert for corrected Not in catalog rows.

Keepa pipeline & query

  • Finder selection update (perPage 50, categories_include, …).
  • 10k ASIN page cap; keepa_fetch_status for skipped no-EAN / no-product / analyze rows.
  • 429 → waiting_tokens, not failed.

Analyze & watchlist UI

  • Status filters/chips for Keepa skipped rows; GTIN / ASIN → amazon.de.
  • Recompute fees when total_fees_eur missing; skipped_analyze for unanalyzable rows.

DB sync

  • Preset qogita syncs wholesale_shopping_list + qogita_watchlist_items.
.\scripts\db-sync\sync-from-prod.ps1 -Preset qogita

Otto repricer (service_multi-repricer)

  • Min/max recalc → status=active, unpause_date=null.

🐛 Bug Fixes

Analyze did not update existing rows

  • analyze_products only queued keepa_scanned_at IS NULL; margin changes had no effect until reanalyze mode.

Analyze infinite / slow batches

  • ~16s/500 rows — per-row commit() + per-GTIN catalog SQL (no Keepa API).
  • Infinite loop — updated_at rotation re-selected the same 500 rows; fixed with offset + reanalyze_complete.

Stale “Not in catalog”

  • Analyze uses DB catalog lookup; cron + list API enrich from wholesale_shopping_list.

Keepa fetch token waste

  • skipped_no_ean / skipped_no_product — no repeat fetch on stub ASINs.

✨ Tests

  • test_wholesale_offers.py — supplier selection.
  • test_keepa_pricing.py — Amazon Nettospanne EK (€28.69 → €13.36), margin breakdown.
  • test_keepa_pipeline.py, test_keepa_scanner_mocked.py
  • test_recalculate_channels.py — Otto min/max → active.
cd backend/service_omni
pytest app/tests/test_wholesale_offers.py app/tests/test_keepa_pricing.py app/tests/test_keepa_pipeline.py -v

cd backend/service_multi-repricer
pytest app/tests/test_recalculate_channels.py -v -k "min_max_change"

📁 Files changed

Backend (service_omni)

  • app/services/qogita/wholesale_offers.py, watchlist_present.py, constants.py
  • app/crud/qogita_catalog.py, qogita.py
  • app/services/keepa/scanner.py, pipeline_runner.py, constants.py, pricing.py
  • app/services/price_alerts/watchlist_cron.py, discord_notifications.py
  • app/api/v1/endpoints/qogita.py, keepa.py
  • app/models/qogita.py, keepa.py
  • app/tests/test_wholesale_offers.py, test_keepa_pricing.py, test_keepa_pipeline.py, test_keepa_scanner_mocked.py

Backend (service_multi-repricer)

  • app/api/v1/endpoints/otto_articles.py
  • app/tests/test_recalculate_channels.py

Frontend

  • src/pages/Statistics/Tabs/QogitaWatchlistTab.jsx, qogitaWatchlistPrefs.js

Postgres

  • docker/postgres/migrations/add_keepa_fetch_status.sql
  • docker/postgres/tables/qogita_watchlist_items.sql

Scripts

  • scripts/db-sync/tables.json

🔧 Database & deploy

\i docker/postgres/migrations/add_keepa_fetch_status.sql

Restart service-omni, service_multi-repricer. Rebuild frontend.

QOGITA_PUSH_BATCH_SIZE=200

Usage notes

Catalog price vs Qogita price

Column Source When populated
Catalog price Best wholesale offer with stock Any supplier in wholesale_shopping_list
Qogita Price Qogita watchlist API After push + sync (qid set)

Target EK vs Amazon calculator

  1. Set Margin % (Nettospanne) (e.g. 10).
  2. Analyze by EAN — runs full reanalyze pass once (~3×500 for ~1400 items).
  3. Spot-check in Seller Central: COGS = Target EK, VK = avg90 price; Nettospanne should match margin %.

Analyze logs (expected)

Keepa analyze batch start: offset=0 requested=500 reanalyze_total=1384
Keepa analyze batch complete: reanalyze_next_offset=500 reanalyze_complete=false duration_ms=~1000
…
Keepa analyze batch complete: reanalyze_next_offset=1384 reanalyze_complete=true

After margin or formula deploy: run Analyze by EAN once so all calculated_ek_price / target_price values refresh.

Local prod data

.\scripts\db-sync\sync-from-prod.ps1 -Preset qogita