Skip to content

1.8.19

⚙️ Patch 1.8.19 — Otto SPA ads sync & sales-stats attribution

🚀 Summary

  • Otto Sponsored Product Ads (SPA) performance is pulled from the Otto Market reporting API and stored on otto_details (total_ad_costs, total_ad_sales).
  • Sync Ads on the Otto Articles tab (and optional step during full Otto sync) creates a product-performance CSV report, polls until ready, then updates matching SKUs.
  • Sales statistics (Otto / Base) show ad costs, ordered ad sales, and adspend/sale; attributed ad spend is deducted from period profit only (gross revenue stays the original sell total).
  • Articles with both ad costs and ad sales > 0 show a small campaign icon next to the SKU.

✨ Details

SPA report & storage

Piece Behaviour
API POST /v1/spa-reporting/product-performance → poll …/product-performance/status → download CSV
Columns SKU, TOTAL_COSTS, TOTAL_ORDERED_SKUS
groupBy SKU (Otto default; KPIs pre-aggregated server-side)
Date range Year start → yesterday, clamped to Otto’s last 90 days
Scope Token must include advertising-services (appended at sync time if missing)
DB otto_details.total_ad_costs = spend € (TOTAL_COSTS ÷ 100; CSV is cents); total_ad_sales = ordered units from ads

Endpoints / UI

Action Route / control
Ads-only sync POST /api/v1/multi/otto/articles/sync_ads_from_otto (+ optional ?sku=)
Full article sync Still runs ads as step 5 after listings
Otto Articles Sync Ads button next to Sync from Otto
Sales dialog KPI cards: Total ad costs, Total ad sales, Adspend/sale, Ad cost applied

Profit attribution (article_stats)

ad_spend_per_sale = total_ad_costs / total_ad_sales   (if total_ad_sales > 0)
  • If total_ad_sales > 0: charge ad_spend_per_sale against the newest Otto Plenty order units in the stats window, up to total_ad_sales (capped by units available in the period).
  • If total_ad_sales = 0 and total_ad_costs > 0: charge the full ad cost on the period.
  • period_profit includes − ad_cost_applied; gross_revenue is unchanged (pre-ad sell total).

🔧 Deploy

  1. Apply SQL (if not already):
-- docker/postgres/updates/add_otto_ad_performance_columns.sql
ALTER TABLE otto_details
    ADD COLUMN IF NOT EXISTS total_ad_costs NUMERIC(12, 2),
    ADD COLUMN IF NOT EXISTS total_ad_sales NUMERIC(12, 2);
  1. Redeploy service-multi-repricer and the frontend.
  2. Ensure Otto credentials can obtain the advertising-services OAuth scope.
  3. Run Sync Ads once to populate rollups, then open Sales statistics on an Otto article.

📚 Docs