Kommo + Metabase: CRM analytics and dashboards without exporting to Excel

Kommo’s built-in analytics covers the basics: pipeline conversion, deal count by stage, revenue for a period. The moment a custom report is needed — “how long does a deal spend at each stage”, “conversion by lead source”, “manager activity broken down by task type” — the team exports data to Excel and builds pivot tables by hand. This is slow, does not update in real time, and requires constant manual effort.

Metabase is an open-source BI tool that enables dashboards on live data. Integrating with Kommo through an intermediate database delivers real-time analytics without Excel and without the limitations of built-in reports.

Why Kommo’s built-in analytics falls short

Kommo provides an Analytics section with standard reports:
— Summary report: deal count, amount, pipeline conversion
— Employee report: deals and tasks per manager
— Call report: count and duration
— Goals: plan vs. actual revenue

This is not enough when:
— A report broken down by custom fields (lead source, client type, region) is needed
— Time-on-stage data is needed (how long a deal has been stuck in “Negotiations”)
— Cohort analytics are needed (leads from January — how many closed by March)
— Combined reports are needed: manager activity + conversion on their deals
— The dashboard needs to update automatically and be visible to the whole team or board

Integration architecture: Kommo -> Metabase

Metabase does not connect directly to the Kommo API — it works with relational databases (PostgreSQL, MySQL, ClickHouse, and others). An intermediate layer is required: an ETL pipeline that regularly exports data from the Kommo API into a database, and Metabase builds dashboards on that database.

Kommo API
  ↓ (ETL, every 15–60 minutes)
PostgreSQL / ClickHouse
  ↑
Metabase (dashboards, SQL questions, alerts)

What is exported from Kommo:
— /api/v4/leads — deals with all fields, including custom ones
— /api/v4/contacts — contacts
— /api/v4/events — change history (stage changes, responsible user changes)
— /api/v4/tasks — tasks (completed and open)
— /api/v4/users — managers
— /api/v4/pipelines — pipelines and stages (reference data)

Database schema

A few tables are sufficient for CRM analytics:

CREATE TABLE leads (
  id          BIGINT PRIMARY KEY,
  name        TEXT,
  price       INTEGER,
  status_id   INTEGER,
  pipeline_id INTEGER,
  created_at  TIMESTAMP,
  updated_at  TIMESTAMP,
  closed_at   TIMESTAMP,
  responsible_user_id INTEGER,
  loss_reason TEXT,
  -- custom fields (denormalized for query simplicity)
  source      TEXT,
  client_type TEXT,
  region      TEXT
);

CREATE TABLE lead_status_history (
  id          BIGSERIAL PRIMARY KEY,
  lead_id     BIGINT,
  from_status INTEGER,
  to_status   INTEGER,
  changed_at  TIMESTAMP,
  changed_by  INTEGER
);

CREATE TABLE tasks (
  id          BIGINT PRIMARY KEY,
  lead_id     BIGINT,
  task_type   INTEGER,
  created_at  TIMESTAMP,
  completed_at TIMESTAMP,
  responsible_user_id INTEGER
);

The lead_status_history table is the key one for time-on-stage analytics: it is used to calculate how long a deal spent at each stage.

Dashboard examples in Metabase

Time on stage (SQL question):

SELECT
  p.name AS pipeline_name,
  s.name AS stage_name,
  AVG(EXTRACT(EPOCH FROM (h.next_change - h.changed_at)) / 3600) AS avg_hours
FROM lead_status_history h
JOIN pipelines p ON h.pipeline_id = p.id
JOIN stages s ON h.to_status = s.id
WHERE h.changed_at > NOW() - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1, avg_hours DESC

Conversion by source:

SELECT
  source,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status_id = 142) AS won,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status_id = 142) / COUNT(*), 1) AS win_rate
FROM leads
WHERE created_at > NOW() - INTERVAL '6 months'
GROUP BY source
ORDER BY win_rate DESC

Manager activity:

SELECT
  u.name AS manager,
  COUNT(t.id) AS tasks_completed,
  COUNT(DISTINCT t.lead_id) AS deals_touched,
  AVG(l.price) FILTER (WHERE l.status_id = 142) AS avg_won_deal
FROM tasks t
JOIN users u ON t.responsible_user_id = u.id
JOIN leads l ON t.lead_id = l.id
WHERE t.completed_at > NOW() - INTERVAL '30 days'
GROUP BY u.name

Metabase builds a visualization from any SQL query: table, bar chart, line graph, funnel, scatter plot. A dashboard of 6–8 questions is assembled in a few hours once the ETL is configured.

Metabase vs Power BI, Looker, Google Data Studio

Metabase Community Edition is free and self-hosted. For small teams it is the optimal choice: runs on any VPS in 30 minutes (Docker), no licensing required.

If the company already uses Power BI — the integration scheme is similar. More detail in the article on Kommo and Power BI integration.

ETL: refresh frequency and incremental loading

A full reload of the database on every update is inefficient at large volumes. The correct approach — incremental loading:

# Read only records changed since the last update
last_updated = get_last_sync_timestamp()
leads = kommo_api.get_leads(
    updated_from=int(last_updated.timestamp())
)
upsert_leads(leads)  # INSERT ... ON CONFLICT DO UPDATE
save_sync_timestamp(now())

For the lead_status_history table — separate logic: read events from /api/v4/events?filter[type]=lead_status_changed filtered by date.

Refresh frequency:
— Operational dashboards (today, this week) -> every 15–30 minutes
— Strategic reports (quarter, year) -> once an hour or once a day

Real-world case

For a distribution company (4 pipelines, 12 managers, 300+ deals per month):
— Before integration: a weekly Excel report took 3–4 hours to prepare; by the time it was presented the data was already 1–7 days out of date
— After: Metabase dashboard refreshes every 30 minutes, accessible to all managers via a link
— Key dashboard metrics: conversion by stage, time on stage, manager activity, revenue by region
— Insight discovered within a week of launch: deals from one source (partner channel) converted at 2.3x lower rates at the same average deal value — marketing had no idea

Who this is relevant for

Kommo + Metabase integration makes sense if:
— Custom reports not available in Kommo’s built-in analytics are needed
— The dashboard needs to update automatically and be accessible to managers without CRM access
— Time-on-stage analytics or cohort lead analysis is needed
— The team values open source and does not want to pay for BI licenses

Frequently asked questions

Is Metabase Community Edition free forever?

Yes. Community Edition is fully free, MIT license, self-hosted. The paid Pro ($500/month) adds SSO, advanced access controls, audit logs, and embedding without Metabase branding. Community Edition is sufficient for internal use.

Which database is better for Kommo data: PostgreSQL or ClickHouse?

Up to 5 million events — PostgreSQL with indexes handles it well. For larger volumes or analytical queries with GROUP BY across tens of millions of rows — ClickHouse is significantly faster. For most Kommo accounts (up to 100,000 deals) PostgreSQL is the right choice.

How difficult is it to deploy Metabase?

Docker: docker run -p 3000:3000 metabase/metabase — and in 2 minutes Metabase is available on localhost. Connecting to the database and creating the first dashboard — another hour. It is one of the easiest BI tools to deploy.

Can Metabase dashboards be embedded in other services?

Yes. Metabase supports iframe embedding — dashboards can be embedded in Notion, an internal portal, or shared via a link without requiring a login (signed embedding). In the Pro version — embedding without Metabase branding.

Summary

• Kommo’s built-in analytics does not cover custom reports and time-on-stage metrics
• Metabase connects to PostgreSQL, which an ETL pipeline populates with data from the Kommo API
• Incremental loading every 15–30 minutes — operational dashboards without overloading the API
• Metabase Community Edition is free; deployed in 30 minutes on any VPS
• Typical development timeline for ETL + basic dashboard set — 3–4 weeks

If you want to see real-time pipeline analytics from Kommo without Excel — describe which reports you need. Exceltic.dev will assess your data volume and propose a concrete architecture.