Custom BI Dashboard Development (Business Intelligence)

Custom BI Dashboard (Business Intelligence)

Companies often face the problem: data is scattered across CRM, ERP, marketplaces, and reports are prepared manually in Excel. This leads to delays in decision-making, errors due to human factors, and inefficient use of resources. A BI dashboard solves all these problems by unifying data into a single repository and allowing business users to build reports in real time independently. Our experience — over 20 completed projects, 7 years on the market. Certified specialists in ClickHouse and PostgreSQL. Quality guarantee at all development stages.

Why Data Warehouse Is the Foundation of Any BI Solution?

Data Warehouse is a centralized storage optimized for analytical queries, not for transactions. Unlike simple connections to sources, a DWH avoids N+1 queries and ensures data consistency. The standard approach is the dimensional model (star schema):

          dim_customers
               |
dim_products — fact_orders — dim_dates
               |
          dim_locations

fact_orders — facts (transactions), contains numeric metrics (amount, quantity) and keys to dimensions. dim_* — dimensions (descriptive data). Queries like "sales by region for Q3" work quickly thanks to this structure. A proper data model can save up to 40% of analysts' time.

How ClickHouse Accelerates Analytical Queries?

ClickHouse is a columnar DBMS with enormous aggregation speed. In practice: a query COUNT(*) + SUM(revenue) on a table with 1 billion rows executes in 0.5 seconds, while on PostgreSQL it takes 15 minutes. ClickHouse is 10–100 times faster than PostgreSQL for typical BI queries.

-- ClickHouse: sales by category for the last 30 days
SELECT
    category,
    sum(revenue) AS total_revenue,
    uniqExact(customer_id) AS unique_customers,
    count() AS orders
FROM orders_mv
WHERE toDate(created_at) >= today() - 30
GROUP BY category
ORDER BY total_revenue DESC;

ETL from PostgreSQL to ClickHouse: via clickhouse-local + scheduled job or Apache Airflow. Materialized views can also be used for precomputed aggregates.

How We Build an ETL Pipeline for BI?

1. Data source audit — identify all relevant systems (CRM, ERP, marketplaces), assess volumes and update frequency.
2. DWH design — develop a star schema considering future reports.
3. ETL development — use Apache Airflow for orchestration, dbt for transformations. Example DAG: source_extract → load_to_staging → transform_to_dwh → load_to_clickhouse.
4. ClickHouse optimization — configure partitioning, primary key, materialized views.
5. Dashboard creation — 10–20 typical reports with filters, drill-down, parameterized queries.

What Is Self-Service BI and How to Embed It?

Self-service means an analyst or manager can build the required report themselves. Components:

• Query builder UI — drag & drop fields, select aggregations, filters without SQL.
• Dashboard builder — add widget, choose chart type, configure axes.
• Parameterized reports — template with variables, user input values.

Comparison of tools for embedding BI:

We help choose the optimal option for your task. Contact us for a consultation to discuss details.

OLAP and Slice & Dice

OLAP allows "slicing" data across multiple dimensions:

• Drill-down: year → quarter → month → day
• Slice: only one region from all
• Dice: region × category × period
• Pivot: rows and columns swap places

In a BI dashboard, this is implemented through hierarchical filters and pivot tables.

Cohort Analysis

Cohort analysis groups users by the period of first action and tracks metrics over time:

SQL for cohort retention:

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
),
activity AS (
  SELECT user_id, DATE_TRUNC('month', event_at) AS activity_month
  FROM user_events WHERE event_type = 'purchase'
)
SELECT
  cohort_month,
  EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS period,
  COUNT(DISTINCT a.user_id)::FLOAT / COUNT(DISTINCT c.user_id) AS retention_rate
FROM cohorts c
LEFT JOIN activity a USING (user_id)
GROUP BY 1, 2;

Access and Security

• Row-level security: each manager sees only their own clients/region.
• Policies at the dataset level: who can create reports, who can only view.
• Query audit: who viewed which reports and when.

What Is Included in BI Dashboard Development

• Data source audit and modeling.
• ETL pipeline development (ClickHouse, Airflow).
• Dashboard creation (10–20 reports).
• Architecture documentation.
• Access rights and security setup.
• Training for the analytics team.
• Technical support after launch.

Timeline

MVP BI dashboard (ClickHouse/PostgreSQL, 10–15 reports, basic filters, user roles): 3–4 months. Full BI platform with self-service builder, cohorts, ETL, and embedded SDK: 5–9 months. Order BI dashboard development and get an engineer consultation. Contact us to discuss the details of your project.

Setup Web Analytics: GA4, GTM, Yandex.Metrica, and Amplitude

We often see: conversion rate 1.2%, traffic grows, but conversion stays flat. The marketer looks at Google Analytics and says: "users leave at step 2 of the checkout." The developer opens the same step — no errors, Sentry is silent. So it's not a JS bug, but a UX issue or skewed data from analytics. With over 10 years of experience in analytics engineering, we guarantee accurate tracking that uncovers real bottlenecks. Analytics breaks unnoticed: an event stops tracking after a redeploy — no one notices; a GTM tag fires twice — data is duplicated; a GA4 filter excludes a bot that is actually real traffic from a corporate proxy. An audit of your current tags will find the cause within a week.

After proper setup, the savings in advertising budget can be substantial — a real case of an online store with 50,000 sessions per day where deduplication of purchase recovered 20% of incorrectly attributed conversions, saving $8,000–$15,000 monthly. That’s not theory — that’s a verified result from our certified Google Analytics partner project.

Why do GA4 events duplicate and how to fix it?

Universal Analytics is gone, replaced by GA4's event-based model. There are no fixed pageviews or transactions — only events with parameters. This is more flexible but requires proper event design. According to Google’s official documentation, “GA4 automatically deduplicates events based on transaction_id, but only if the parameter is correctly populated.” Many implementations miss this.

Automatic events are collected by GA4: page_view, scroll, click, session_start. Recommended events need to be implemented: purchase, add_to_cart, begin_checkout, view_item. Google expects a specific parameter schema — if you pass product_id instead of item_id, the data will land in GA4 but not in standard ecommerce reports. Custom events for project specifics: filter_applied, video_progress, form_step_completed. Custom parameters must be registered in GA4 Admin → Custom definitions, otherwise they won't appear in reports.

A common mistake is the purchase event being duplicated. Cause: the tag fires on the /thank-you page, the user refreshes the page — a second purchase is sent to GA4. Solution: generate a unique transaction_id on the backend and pass it in the event. In our experience, 80% of e-commerce stores have this issue. GA4 deduplicates based on it (in theory — verify with DebugView). Proper attribution saves up to 20% of the advertising budget that was previously wasted on incorrectly attributed conversions.

How to set up the data layer to avoid data loss?

GTM is a tool for managing tags without code deployment. But "no code" doesn't mean "no architecture." The data layer is the foundation. We pass data from the application to GTM via dataLayer.push(). Structure: event + contextual data. For e-commerce: before opening a product page — push with product data. GTM tag reads from the data layer, not from the DOM.

window.dataLayer = window.dataLayer || [];
dataLayer.push({
  event: 'view_item',
  ecommerce: {
    items: [{
      item_id: 'SKU-12345',
      item_name: 'Product name',
      price: 1990.00,
      currency: 'USD'
    }]
  }
});

Bad practice: GTM tag parses the DOM — looks for the price in span.price, the name in h1. This breaks with any layout change. Good practice: always use the data layer. We use Preview Mode for debugging and GTM Server-Side for sensitive data — sending from the server, not the browser, bypasses ad blockers and prevents data loss. A properly implemented data layer reduces tracking errors by 95%.

How does Yandex.Metrica complement web analytics?

For a Russian audience, Metrica is a must — especially Webvisor. Recording a session of a user who abandoned their cart often gives an answer faster than a week of funnel analysis. Goals in Metrica: event-based (via ym(COUNTER_ID, 'reachGoal', 'GOAL_NAME')) or automatic (button click, page visit). Integration with CRM via Metrica Plus — passing offline conversions. Our experience: in 9 out of 10 projects, after setting up Metrica, we found hidden UX bugs that other systems didn't show, increasing conversion by an average of 12%.

What does product analytics give in Amplitude?

Amplitude is a product tool, unlike marketing-oriented GA4 and Metrica. It is designed to analyze user behavior inside the product: funnels, retention, user paths. Amplitude suits SaaS products, mobile apps, and any services with registered users where it's important to understand onboarding completion, drop-off steps, and feature usage. Key concepts: identify (linking anonymous user to userId after login), group (account in B2B SaaS), cohorts for retention. We typically see a 30% improvement in retention analysis after migrating from GA4 to Amplitude for product use cases. Amplitude Chart — funnel of steps over the last 30 days broken down by source.

Monitoring Data Quality

Analytics without monitoring is a black box. We set up:

• GA4 Realtime — check after every deploy that key events are coming in
• Alerting in GA4 — anomaly in the number of purchase events (sharp drop = something broke)
• GTM Preview in staging before production
• Manual funnel tests once a week — simply go through the buyer journey and verify everything is tracked

What the work includes

Process and timeline

1. Audit of existing tags and data (2 days)
2. Event schema design (2 days)
3. Data layer development and tag setup (3–5 days)
4. QA in Preview Mode and staging (2 days)
5. Deploy and dashboard setup (1 day)

Cost is calculated individually. Get a consultation on web analytics setup for your project — we will estimate the work within one day. Contact us to get started with a free audit of your current tracking.