> ## Documentation Index
> Fetch the complete documentation index at: https://docs2.zenskar.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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 the`Customers` 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:

```sql theme={null}
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:

| Table       | Description                             | Common fields                                                  |
| ----------- | --------------------------------------- | -------------------------------------------------------------- |
| `invoices`  | Details of all issued invoices          | `id`, `amount`, `status`, `due_date`, `created_at`             |
| `payments`  | Payment transactions linked to invoices | `id`, `amount`, `payment_status`, `payment_date`, `invoice_id` |
| `customers` | Customer master records                 | `id`, `customer_name`, `email`, `organisation`, `created_at`   |
| `contracts` | Contractual agreements                  | `id`, `customer_id`, `start_date`, `end_date`                  |
| `products`  | Products or SKUs being billed           | `id`, `name`, `type`, `unit_price`                             |

<br />

***

## 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:

| Metric                       | Description                                          | Sample query method           |
| ---------------------------- | ---------------------------------------------------- | ----------------------------- |
| DSO (days sales outstanding) | Measures average time to collect payment             | SQL with `DATEDIFF`           |
| Collection rate              | Ratio of payments received to invoice amount         | Visual or SQL                 |
| Open invoices by segment     | Unpaid invoices grouped by customer segment          | Visual                        |
| Revenue by product           | Total invoice amount by product type                 | Visual or SQL                 |
| Payment delay distribution   | Histogram of delay between invoice and payment dates | SQL with `CASE` or `bucket()` |

### Example: DSO

```sql theme={null}
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