Kommo + Lightdash: Open-Source Sales BI Dashboard with dbt, No Tableau Required

Lightdash is an open-source BI tool that runs on top of dbt models. It does not store data itself - it reads from PostgreSQL/BigQuery/Snowflake through dbt metrics. Unlike Tableau or Looker, it requires no BI license: self-hosted Lightdash is free, and your data stays in your own warehouse. For Kommo, the integration works via ETL: deal data is exported from the Kommo API into PostgreSQL, dbt builds the models, and Lightdash renders the dashboard.

Lightdash API uses Bearer token authentication (Personal Access Token). Key operations: POST /api/v1/projects/{id}/explores/{name}/runQuery - run a query against a metric, GET /api/v1/projects - list projects. However, most of the work happens at the dbt model level, not directly through the Lightdash API.

A dbt model is a SQL transformation described in a .yml file with metadata. Lightdash reads dbt metrics: and dimensions: and builds an interface for non-SQL users on top of them.

Architecture: Kommo -> PostgreSQL -> dbt -> Lightdash

Kommo API
  -> Python ETL (cron every 4 hours)
  -> PostgreSQL: tables kommo_leads, kommo_contacts
  -> dbt: models kommo_deals, metrics (win_rate, avg_deal_size)
  -> Lightdash: sales dashboard

ETL: Kommo -> PostgreSQL

import requests, os, psycopg2
from datetime import datetime, timezone

KOMMO_SUBDOMAIN = os.environ["KOMMO_SUBDOMAIN"]
KOMMO_TOKEN     = os.environ["KOMMO_ACCESS_TOKEN"]
PG_DSN          = os.environ["PG_DSN"]

KOMMO_BASE = f"https://{KOMMO_SUBDOMAIN}.kommo.com/api/v4"
KOMMO_HDR  = {"Authorization": f"Bearer {KOMMO_TOKEN}"}

def fetch_leads(updated_after_ts: int | None = None) -> list[dict]:
    params = {"limit": 250, "with": "contacts,custom_fields_values"}
    if updated_after_ts:
        params["filter[updated_at][from]"] = updated_after_ts
    all_leads = []
    page = 1
    while True:
        params["page"] = page
        r = requests.get(f"{KOMMO_BASE}/leads", headers=KOMMO_HDR, params=params)
        if r.status_code == 204:
            break
        data = r.json()
        items = data.get("_embedded", {}).get("leads", []) or []
        if not items:
            break
        all_leads.extend(items)
        page += 1
    return all_leads

def upsert_leads_to_pg(leads: list[dict], conn):
    cur = conn.cursor()
    cur.execute(
        "CREATE TABLE IF NOT EXISTS kommo_leads ("
        "    id             BIGINT PRIMARY KEY,"
        "    name           TEXT,"
        "    status_id      INT,"
        "    pipeline_id    INT,"
        "    responsible_id INT,"
        "    price          NUMERIC,"
        "    created_at     TIMESTAMPTZ,"
        "    updated_at     TIMESTAMPTZ,"
        "    closed_at      TIMESTAMPTZ,"
        "    loss_reason_id INT"
        ")"
    )
    for lead in leads:
        cur.execute(
            "INSERT INTO kommo_leads"
            "    (id, name, status_id, pipeline_id, responsible_id,"
            "     price, created_at, updated_at, closed_at, loss_reason_id)"
            " VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            " ON CONFLICT (id) DO UPDATE SET"
            "    name           = EXCLUDED.name,"
            "    status_id      = EXCLUDED.status_id,"
            "    pipeline_id    = EXCLUDED.pipeline_id,"
            "    responsible_id = EXCLUDED.responsible_id,"
            "    price          = EXCLUDED.price,"
            "    updated_at     = EXCLUDED.updated_at,"
            "    closed_at      = EXCLUDED.closed_at,"
            "    loss_reason_id = EXCLUDED.loss_reason_id",
            (
            lead["id"],
            lead.get("name"),
            lead.get("status_id"),
            lead.get("pipeline_id"),
            lead.get("responsible_user_id"),
            lead.get("price"),
            datetime.fromtimestamp(lead["created_at"], tz=timezone.utc) if lead.get("created_at") else None,
            datetime.fromtimestamp(lead["updated_at"], tz=timezone.utc) if lead.get("updated_at") else None,
            datetime.fromtimestamp(lead["closed_at"],  tz=timezone.utc) if lead.get("closed_at")  else None,
            lead.get("loss_reason_id"),
        ))
    conn.commit()
    cur.close()

def run_etl():
    conn = psycopg2.connect(PG_DSN)
    leads = fetch_leads()
    upsert_leads_to_pg(leads, conn)
    conn.close()
    print(f"ETL done: {len(leads)} leads")

if __name__ == "__main__":
    run_etl()

dbt Models for Lightdash

# models/kommo_deals.yml
version: 2

models:
  - name: kommo_deals
    description: "Kommo deals with BI metrics"
    columns:
      - name: id
        description: "Deal ID"
      - name: name
        description: "Deal name"
      - name: status
        description: "Status: open / won / lost"
      - name: pipeline_id
        description: "Pipeline ID"
      - name: responsible_id
        description: "Responsible manager"
      - name: price
        description: "Deal amount USD"
      - name: created_date
        description: "Creation date"
      - name: closed_date
        description: "Close date"
      - name: days_to_close
        description: "Sales cycle length (days)"

    meta:
      joins: []

metrics:
  - name: win_rate
    label: "Win Rate %"
    model: ref('kommo_deals')
    description: "Percentage of won deals"
    type: average
    sql: "CASE WHEN status = 'won' THEN 100.0 ELSE 0 END"
    timestamp: created_date
    time_grains: [month, quarter]

  - name: avg_deal_size
    label: "Avg Deal Size"
    model: ref('kommo_deals')
    description: "Average size of closed deals"
    type: average
    sql: "price"
    filters:
      - field: status
        operator: "equals"
        value: "'won'"
    timestamp: closed_date
    time_grains: [month, quarter]

  - name: deals_created
    label: "Deals Created"
    model: ref('kommo_deals')
    description: "Number of new deals"
    type: count
    sql: "id"
    timestamp: created_date
    time_grains: [week, month]

-- models/kommo_deals.sql
SELECT
    id,
    name,
    CASE
        WHEN status_id IN ({{ var('kommo_won_stages') }})  THEN 'won'
        WHEN status_id IN ({{ var('kommo_lost_stages') }}) THEN 'lost'
        ELSE 'open'
    END AS status,
    pipeline_id,
    responsible_id,
    price,
    created_at::date AS created_date,
    closed_at::date  AS closed_date,
    EXTRACT(DAY FROM (COALESCE(closed_at, NOW()) - created_at))::int AS days_to_close
FROM {{ source('raw', 'kommo_leads') }}

dbt_project.yml

name: 'exceltic_analytics'
version: '1.0.0'

vars:
  kommo_won_stages:  [142, 143]  # IDs of your Won statuses in Kommo
  kommo_lost_stages: [144, 145]  # IDs of your Lost statuses

models:
  exceltic_analytics:
    +materialized: table

Lightdash Self-Hosted: Quick Start

git clone https://github.com/lightdash/lightdash
cd lightdash
cp .env.example .env
# Fill in LIGHTDASH_SECRET, DB credentials, dbt project path
docker-compose up -d

After launching: Connect Project -> select dbt core project -> point to your models directory. Lightdash automatically parses metrics: and columns: from .yml files and builds the Explorer interface.

Who This Is For

Tech companies with a development team that already uses dbt + PostgreSQL/BigQuery. A Head of Sales wants a dashboard without a Tableau license ($70/user/month). Data is already in the data warehouse - you just need to build the models and connect Lightdash.

A similar enterprise BI integration is described for Kommo + Qlik Sense.

Frequently Asked Questions

Is dbt Cloud required, or is dbt Core enough?

dbt Core (open-source, free) is sufficient. Lightdash works with dbt Core via a local dbt compile run. dbt Cloud additionally provides managed orchestration, version history, and a browser-based IDE - but these are optional for the Lightdash integration.

How often should the ETL from Kommo run?

For operational reporting, every 4 hours is enough (cron 0 */4 * * *). The Kommo API has no rate limit for standard usage - 7 requests/second. With 1,000+ deals, a full sync takes 2-5 minutes. For incremental sync, use filter[updated_at][from] with the timestamp of the last successful run.

Can data from HubSpot or other CRMs be added to the same dashboard?

Yes. dbt allows you to combine data from multiple sources in a single model. Add an ETL for HubSpot (a separate hubspot_deals table), create a dbt model all_deals with UNION ALL and field mapping. Lightdash will see the combined model as a single data source.

Summary

Kommo + Lightdash - open-source BI without Tableau:

• Python ETL cron: fetch_leads() + upsert to PostgreSQL with ON CONFLICT DO UPDATE
• dbt models: CASE status_id IN (...) for won/lost, days_to_close metric
• Lightdash metrics: in .yml: win_rate, avg_deal_size, deals_created with time_grains
• Self-hosted for free: docker-compose up in 30 minutes
• No Tableau/Looker licenses - data stays in your warehouse

If you need help setting up an ETL from Kommo into your data warehouse or building a Lightdash dashboard, describe your task to the Exceltic.dev team.