Create custom dashboards

Overview

This guide provides a comprehensive overview of how to create and manage custom dashboards in Zenskar using Metabase. It explains both the visual and SQL editor approaches, offers best practices, and includes references to Zenskar’s schema and typical use cases.

Metabase

Zenskar provides powerful analytics capabilities through Metabase, an open-source business intelligence tool that is tightly integrated with Zenskar’s platform. All standard and custom dashboards in Zenskar are built using Metabase.

Dashboards are used to monitor billing and revenue metrics, track customer payments, and visualize product and contract performance. These dashboards consist of individual tiles, each powered by a Metabase "question" (i.e., a query), which can be created using:

• Visual editor: A point-and-click interface for building queries.
• SQL (native) editor: A text-based interface that supports full SQL syntax and Metabase-specific enhancements like dashboard filters.

All stats, charts, and screenshots in this documentation are illustrative and based on example dashboards.

Visual editor

The visual editor is ideal for users who want to build queries without writing SQL. It offers dropdown-based options to select tables, columns, aggregations, and filters.

When to use

• Generating KPIs such as total invoice amount, overdue invoices, or count of customers
• Applying standard filters such as status or date
• Creating common charts such as bar graphs, pie charts, or tables

Steps to create a visual question

1. Navigate to Ask a question > Simple question

2. Select a table from the Zenskar schema (e.g., invoices)

3. Choose the fields and aggregation type (e.g., sum(amount))

4. Apply filters such as:

   • status is open
   • due_date is before today

5. Choose a visualization type (e.g., bar chart, KPI, line chart)

6. Click Save, name your question, and assign it to a collection for reuse

Example: using the Customers table

Let’s explore the Customers table to learn how filters and summarization work in the visual editor.

Example schema of theCustomers table:

• ID
• External ID
• Customer Name
• Custom Data
• Address
• Ship To Address
• Email
• Phone Number
• Created At
• Updated At

Example 1: Count of customers created in the last 30 days:

• Table: Customers
• Filter: Created At is within the last 30 days
• Summarize: Count of rows
• Visualization: KPI

Example 2: Group customers by organization:

• Table: Customers
• Summarize: Group by Organisation, Count of rows
• Sort by count descending
• Visualization: Bar chart

Limitations

• Cannot perform joins between multiple tables
• Cannot define complex logic using CASE, subqueries, or unions
• Once converted to SQL, a visual question cannot be reverted

SQL (native) editor

The SQL editor gives full control to users who are comfortable writing SQL. It supports advanced features such as:

• Joins across tables
• Subqueries and CTEs
• Time-based functions and comparisons
• Custom dashboard filters using Metabase variables

When to use

• Combining data from multiple sources (e.g., invoices and payments)
• Performing calculations like DSO (days sales outstanding) or customer aging
• Custom metrics like "collection rate by segment"

Steps to create a SQL question

1. Go to Ask a question > Native query
2. Write a SQL query using the relevant tables and logic

Example:

SELECT
  c.customer_name,
  COUNT(i.id) AS invoice_count,
  SUM(i.amount) AS total_invoiced,
  SUM(p.amount) AS total_collected,
  (SUM(p.amount) / SUM(i.amount)) * 100 AS collection_rate
FROM invoices i
JOIN customers c ON i.customer_id = c.id
LEFT JOIN payments p ON p.invoice_id = i.id
WHERE i.status = 'sent'
  AND i.created_at BETWEEN {{start_date}} AND {{end_date}}
GROUP BY c.customer_name
ORDER BY total_invoiced DESC;

3. Use double curly braces {{ }} to define dashboard-level filters
4. Save the question and map the variables to filters when adding to a dashboard

Zenskar schema reference

Zenskar’s data model is exposed in Metabase through a secure schema. Here are key tables frequently used:

Creating and organizing dashboards

Dashboards in Metabase are collections of questions arranged into a layout.

To create a dashboard:

1. Click + New dashboard from the dashboard screen
2. Enter a title and optional description
3. Add existing questions using the Add a card option
4. Arrange and resize the tiles
5. Click Add filter to introduce date, status, or other filters
6. Connect filters to relevant variables in SQL or visual questions
7. Save and optionally share or embed the dashboard

Filter types

• Date range: for filtering data based on created_at or due_date
• Dropdowns: linked to fields like status, segment, or region
• Text or number inputs: for manual entry

Best practices for performance

• Use time filters to limit query size (e.g., last 90 days)
• Pre-aggregate heavy metrics using Zenskar’s backend pipelines
• Use indexed fields when joining tables (e.g., customer_id)
• Limit the number of dashboard tiles per page (8–12 recommended)

Example metrics and use cases

The following metrics are commonly built in dashboards using Metabase:

Example: DSO

SELECT
  AVG(DATEDIFF(p.payment_date, i.due_date)) AS avg_dso
FROM invoices i
JOIN payments p ON p.invoice_id = i.id
WHERE i.status = 'paid';

Example: Zenskar billing and collections dashboard

Zenskar’s default Billing and collections dashboard is powered by Metabase. It includes tabs such as:

• Invoices: Total billed, overdue, aging buckets
• Payments: Collected vs pending, payment delays
• DSO trend: Monthly view of days sales outstanding

All stats, charts, and screenshots in this documentation are illustrative and based on example dashboards.

Managing access and sharing

You can control who has access to dashboards:

• Set permissions by role (admin, finance, ops)
• Share a public link or embed in an external tool
• Export results as CSV or images