BI system on DataLens and PostgreSQL for WB Ozon

Building a BI System with PostgreSQL and DataLens for Marketplace Analytics

A development team created a BI system to analyze data from Wildberries and Ozon seller accounts. The system uses PostgreSQL as the primary storage, Yandex DataLens for visualization, and Python scripts for API data extraction. This enabled automation of daily updates, query optimization, and implementation of cost versioning.

The project evolved from manual uploads to Google Sheets into a scalable service with client orchestration via webhooks.

Issues with Existing Services and Project Launch

Standard analytics services failed to provide a unified view of data from multiple sources. Leveraging experience with WB and Ozon APIs, the team decided to build their own solution on DataLens.

Initial dashboards were created using data from Google Sheets. However, connection instability prompted a move to a dedicated server with a database. MySQL was initially used but quickly migrated to PostgreSQL 16 for better performance with large volumes.

Automation of data extraction was implemented via Cron tasks. Scripts parse JSON/REST APIs, normalize data, and save it to the database.

Core Architecture and Scaling

The key element is a modular script core. It runs with a client configuration, allowing centralized updates to logic for all users. Changes in API protocols are automatically applied to everyone.

Variables in scripts (timings, limits) adapt to client data volume, avoiding 429 errors.

Scaling:

• Demo version with anonymized data for testing.
• Website application form with a webhook to the orchestrator.
• Automatic dashboard creation and access distribution.
• Historical data extraction: WB — up to 1 year, Ozon — up to 2 years.

The link with Google Sheets remains for inputting cost price and sales plans — scripts extract them automatically.

Performance Optimization

Initial datasets in DataLens relied on multiple JOINs, slowing dashboards to 15–20 seconds. Optimization:

• Pre-calculation on the server: economics, cost structure, profit are stored in the database.
• Table enrichment during extraction: adding categories by vendor code (vendor_code) without JOINs in DataLens.
• Minimizing JOINs to 2–3 per dashboard.

Result: load time reduced to 5 seconds. Daily pre-calculation scripts take 30 seconds.

Cross-filters on dashboards provide convenient navigation by periods, categories, and vendor codes.

Current Technology Stack

┌──────────────────────────────┐
│ API WB / OZON                │
│ (JSON/REST)                  │
└──────────────┬───────────────┘
               │
               ▼
┌──────────────────────────────┐        ┌──────────────────────────────┐
│ Python scripts               │ <----> │ Google Sheets                │
│ (parsing, normalization)     │        │ (cost price, plan)           │
└──────────────┬───────────────┘        └──────────────────────────────┘
               │
               ▼
┌──────────────────────────────┐
│ PostgreSQL 16                │
│ (primary storage)            │
└──────────────┬───────────────┘
               │
               ▼
┌──────────────────────────────┐
│ Yandex DataLens              │
│ (visualization)              │
└──────────────────────────────┘

Backend: Python, psycopg2, requests, subprocess, python-dotenv, Flask

Database: PostgreSQL 16, SQL

Automation: Cron, orchestrator

BI: DataLens

AI models (Claude Sonnet/Opus) were used to accelerate script development and debugging.

Cost Versioning

Cost price changes over time, requiring adjustments to historical data without distorting analytics. Solution: a start_date column in the table.

Logic:

• Base value without a date.
• New entries with start_date for versions.
• Calculation by priority: actual cost price on the sale date by key start_date + vendor_code.

Insertion example:

INSERT INTO wb_cost_price VALUES
('ART-001', 800.00, '2025-01-01'),
('ART-001', 900.00, '2025-06-01'), -- increased
('ART-001', 850.00, '2026-02-01'); -- decreased

This ensures accurate profit recalculation retroactively.

Key Takeaways

• Modular core allows centralized updates to extraction logic for all clients.
• Server-side pre-calculation reduces dashboard load time by 3–4 times.
• Cost versioning maintains historical accuracy without overwriting data.
• Automated onboarding via webhooks minimizes manual effort.
• PostgreSQL 16 is optimal for analytics with table enrichment.

— Editorial Team