Kommo + Zoho Analytics: Sales BI Dashboard for Companies in the Zoho Ecosystem
Zoho Analytics is a cloud-based BI tool within the Zoho ecosystem - it creates interactive reports and dashboards, supports SQL queries, and automatically refreshes data. Kommo is a CRM that holds sales pipeline data. Companies already using Zoho Books, Zoho Projects, or other Zoho tools often choose Zoho Analytics as their centralized BI layer - you can combine data from your CRM, accounting, and project management into a single dashboard.
The core challenge: Zoho Analytics has no native connector for Kommo (which is not Zoho CRM). Data from Kommo must be loaded via the Import API - similar to the PostgreSQL approach used for Tableau, but over HTTP instead of JDBC.
Zoho Analytics Import API lets you push data via POST /api/{email}/{dbName}/{tableName}, automatically creating tables on the first load. Authentication uses OAuth 2.0 (Client Credentials for server-to-server flows).
Architecture
Kommo REST API (incremental fetch by updated_at)
-> Python ETL (cron every 2 hours)
-> Zoho Analytics Import API (Bulk Import or Row API)
Table: kommo_leads
Zoho Analytics
-> Reports based on kommo_leads
-> Dashboards: funnel, KPIs, revenue forecastOAuth 2.0 Implementation for Zoho Analytics
import requests, os, json, time
from pathlib import Path
ZA_CLIENT_ID = os.environ["ZOHO_CLIENT_ID"]
ZA_CLIENT_SECRET = os.environ["ZOHO_CLIENT_SECRET"]
ZA_REFRESH_TOKEN = os.environ["ZOHO_REFRESH_TOKEN"]
ZA_ORG_EMAIL = os.environ["ZOHO_ORG_EMAIL"]
ZA_DB_NAME = os.environ["ZOHO_DB_NAME"] # database name in Zoho Analytics
ZA_TABLE_NAME = "kommo_leads"
TOKEN_CACHE = Path("/tmp/zoho_token.json")
def get_zoho_token() -> str:
# Check cached token
if TOKEN_CACHE.exists():
cached = json.loads(TOKEN_CACHE.read_text())
if cached.get("expires_at", 0) > time.time() + 60:
return cached["access_token"]
# Refresh
r = requests.post(
"https://accounts.zoho.com/oauth/v2/token",
data={
"client_id": ZA_CLIENT_ID,
"client_secret": ZA_CLIENT_SECRET,
"refresh_token": ZA_REFRESH_TOKEN,
"grant_type": "refresh_token",
},
)
r.raise_for_status()
data = r.json()
token_data = {
"access_token": data["access_token"],
"expires_at": time.time() + data.get("expires_in", 3600) - 30,
}
TOKEN_CACHE.write_text(json.dumps(token_data))
return token_data["access_token"]ETL: Kommo -> Zoho Analytics
KOMMO_DOMAIN = os.environ["KOMMO_DOMAIN"]
KOMMO_TOKEN = os.environ["KOMMO_TOKEN"]
KOMMO_BASE = f"https://{KOMMO_DOMAIN}/api/v4"
KOMMO_HDR = {"Authorization": f"Bearer {KOMMO_TOKEN}"}
def fetch_leads_since(since_ts: int) -> list:
leads, page = [], 1
session = requests.Session()
session.headers.update(KOMMO_HDR)
while True:
r = session.get(f"{KOMMO_BASE}/leads", params={
"updated_at[from]": since_ts,
"limit": 250,
"page": page,
})
if r.status_code == 204:
break
batch = r.json().get("_embedded", {}).get("leads", [])
if not batch:
break
leads.extend(batch)
if len(batch) < 250:
break
page += 1
return leads
def leads_to_rows(leads: list) -> list[dict]:
rows = []
for lead in leads:
closed = lead.get("closed_at")
rows.append({
"lead_id": lead["id"],
"name": lead.get("name", ""),
"price": lead.get("price", 0),
"status_id": lead.get("status_id"),
"pipeline_id": lead.get("pipeline_id"),
"responsible_id": lead.get("responsible_user_id"),
"created_at": _ts(lead.get("created_at")),
"updated_at": _ts(lead.get("updated_at")),
"closed_at": _ts(closed) if closed else "",
})
return rows
def _ts(epoch: int | None) -> str:
if not epoch:
return ""
from datetime import datetime, timezone
return datetime.fromtimestamp(epoch, tz=timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
def import_to_zoho(rows: list[dict]):
if not rows:
return
token = get_zoho_token()
# Format: JSON array
import_data = json.dumps(rows)
url = f"https://analyticsapi.zoho.com/api/{ZA_ORG_EMAIL}/{ZA_DB_NAME}/{ZA_TABLE_NAME}"
r = requests.post(
url,
headers={"Authorization": f"Zoho-oauthtoken {token}"},
params={
"ZOHO_ACTION": "IMPORT",
"ZOHO_OUTPUT_FORMAT": "JSON",
"ZOHO_ERROR_FORMAT": "JSON",
"ZOHO_API_VERSION": "1.0",
"ZOHO_IMPORT_TYPE": "UPDATEADD", # Upsert: update or insert
"ZOHO_AUTO_IDENTIFY": "TRUE", # Auto-detect column types
},
data={"ZOHO_DO_IN_BACKGROUND": "false", "ZOHO_IMPORT_DATA": import_data},
)
result = r.json()
summary = result.get("response", {}).get("result", {}).get("importSummary", {})
print(f"Imported: {summary.get('totalRecordCount', 0)} records")
def run_etl():
# Read last sync timestamp from file
ts_file = Path("/tmp/kommo_last_sync.txt")
since = int(ts_file.read_text()) if ts_file.exists() else 0
leads = fetch_leads_since(since)
if leads:
rows = leads_to_rows(leads)
import_to_zoho(rows)
# Update timestamp
max_ts = max(l.get("updated_at", 0) for l in leads)
ts_file.write_text(str(max_ts))
print(f"Synced {len(leads)} leads")
if __name__ == "__main__":
run_etl()Dashboards in Zoho Analytics
After the initial data load, create reports in Zoho Analytics:
- Sales funnel: Count leads by
status_id-> Bar chart - Revenue by manager:
SUM(price) GROUP BY responsible_id-> Pie chart - Closing dynamics:
COUNT(*) WHERE status_id=142 GROUP BY date(closed_at)-> Line chart - Average deal cycle:
AVG(closed_at - created_at) WHERE status_id=142-> KPI widget
Zoho Analytics supports SQL queries natively - all standard aggregations and JOINs with other tables (for example, data from Zoho Books) are available out of the box.
Real-World Use Case
A company using Zoho Books for accounting and Kommo for sales wanted to see revenue from Zoho Books and deal statuses from Kommo in a single dashboard. The solution: an ETL pipeline from Kommo to Zoho Analytics, combined with the native Zoho Books -> Zoho Analytics connector. The dashboard refreshes every two hours. The director can see funnel conversion rates and issued invoices in one place, without switching between systems.
Who This Is For
Companies already using other Zoho products - Books, Projects, Desk, Campaigns. If Zoho Analytics is already licensed for other data sources, adding Kommo as an additional source requires no extra licensing costs - only ETL development.
A similar approach for Tableau is covered in Kommo + Tableau: Sales BI Dashboard.
Frequently Asked Questions
What is the difference between UPDATEADD and other import modes?
Zoho Analytics supports several import modes: APPEND (add only), TRUNCATEADD (clear then add), and UPDATEADD (update existing records by key, or insert new ones). For incremental sync you need UPDATEADD with lead_id as the key column.
How do I set up automatic refresh without cron?
Zoho Analytics supports Data Sync - you can configure a schedule directly in the interface for SQL-based sources. For Kommo, you need either a Data Bridge (an on-premise component) or an external ETL. The simplest option is GitHub Actions or any other scheduler.
Are there rate limits on the Zoho Analytics Import API?
Yes: 10 requests per minute for the Import API, with a maximum of 25,000 rows per import call. For large data volumes, split imports into batches of 5,000 - 10,000 rows.
Summary
Kommo + Zoho Analytics:
- OAuth 2.0 Client Credentials + Refresh Token for authentication
- Kommo paginated API (
updated_at[from]) for incremental data fetching - Import API with
ZOHO_IMPORT_TYPE=UPDATEADDfor upsert by lead_id - Zoho Analytics dashboards with SQL queries on top of Kommo data
If you use the Zoho ecosystem and want to combine Kommo CRM data with other Zoho services, reach out to Exceltic.dev.