> For the complete documentation index, see [llms.txt](https://docs.getinfinity.app/user-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.getinfinity.app/user-guide/comprehensive-user-guide/12.-advanced-topics/custom-computes-for-event-based-subscriptions.md).

# Custom Computes for Event Based Subscriptions

## Custom Computes

Custom Computes are SQL-based conditions that periodically run against your datasets to monitor KPIs (Key Performance Indicators) and trigger alerts based on the evaluation of these conditions.

They help automate **event-based subscriptions**, notifying users via email when specific business conditions are met or breached.

***

### Overview

* **Custom Computes** run encapsulated SQL queries on your data tables.
* They snapshot the latest data **hourly** and execute compute logic on a **scheduled** basis (e.g., daily).
* If the compute condition returns **true**, a **notification email** is triggered.

Typical use cases include:

* Monitoring if **Total Sales** in the current month fall below a threshold.
* Detecting if **Market Share** of a product drops unexpectedly.

***

### How It Works

Each Custom Compute consists of:

* **KPI/Metric**: The field being monitored (e.g., Total Sales).
* **Condition**: The logical comparison (e.g., `< 1M`).
* **Value**: The threshold that triggers an alert (e.g., 1M).

A Custom Compute:

* Periodically **runs a SQL query**.
* **Evaluates a condition**.
* **Sends alerts** when conditions are satisfied.

***

### Pre-requisites

Before creating a Custom Compute:

* **Create a Chart** in Infinity.\
  This chart serves as the foundation for running and monitoring the compute.

***

## Creating a Custom Compute

To create a Custom Compute:

1. Go to **Settings → Site Settings → Computes**.
2. Click **Add Compute**.

You will see a setup screen with the following fields:

***

### Compute Configuration

| Field                        | Description                                                                                         |
| ---------------------------- | --------------------------------------------------------------------------------------------------- |
| **Name**                     | Unique name for the compute.                                                                        |
| **Type**                     | Programming language for logic. (Options: `BIG_QUERY`, `JavaScript`, `Python`)                      |
| **Return Type**              | Define output: **Condition** (Boolean, Integer, Double, Percent, String) **SubText** (String only). |
| **Message: Condition Met**   | Notification text when condition is true.                                                           |
| **Message: Condition Unmet** | Notification text when condition is false.                                                          |
| **Enable Custom Email**      | Enable a personalized email format from the Email tab.                                              |
| **Default Chart**            | The chart associated with this compute.                                                             |
| **Period**                   | How often the compute runs (e.g., Daily).                                                           |

***

### DB Query Tab

Write your SQL query that defines the compute's business logic. The query must always return the following two columns with the exact same names:&#x20;

1. Condition \[Boolean] - True if the compute logic evalutes to True; False otherwise.&#x20;
2. SubText \[String] - details that subscribed user would receive in the notification email body.

You can use **dynamic placeholders**:

| Placeholder     | Description                    |
| --------------- | ------------------------------ |
| `${dataset_id}` | Dataset ID                     |
| `${table_name}` | Table Name                     |
| `${condition}`  | Logical operator               |
| `${operator}`   | Logical operator (alternative) |
| `${value}`      | Threshold value                |

These placeholders are automatically replaced at runtime.

***

### Email Tab

Configure the alert email.

| Field             | Description                              |
| ----------------- | ---------------------------------------- |
| **Subject**       | Subject line for the notification email. |
| **Email Message** | Body content of the notification email.  |

You can dynamically include:

* `${subtexts}` to inject a custom sub-message.
* `${metric}` and `${result}` in the body.

***

## Example: Data Refresh Monitoring Compute

Let's walk through an example of monitoring whether your dataset is properly refreshed.

***

### DB Query

<pre class="language-sql"><code class="lang-sql">WITH base1 AS (
    SELECT *, dense_rank() OVER (ORDER BY subscription_upload_date DESC) AS snapshot_rank
    FROM `${dataset_id}.${table_name}`
    WHERE period = 'HOURLY'
),
base2 AS (
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 1
    EXCEPT DISTINCT
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 2
),
base3 AS (
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 2
    EXCEPT DISTINCT
    SELECT * EXCEPT(subscription_upload_date, period, snapshot_rank, data_refreshed)
    FROM base1
    WHERE snapshot_rank = 1
),
base4 AS (
    SELECT * FROM base2
    UNION DISTINCT
    SELECT * FROM base3
)
SELECT (CASE WHEN COALESCE(COUNT(*), 0) > 0 THEN TRUE ELSE FALSE END) AS condition,
concat('&#x3C;p style="font-family: Georgia, serif; text-shadow: 1px 1px 2px lightgray;">',
<strong>'Data is Refreshed at:', FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', CURRENT_DATETIME()), '.&#x3C;/p>') as SubText
</strong>FROM base4;
</code></pre>

***

### Compute Settings

| Field                        | Value                                      |
| ---------------------------- | ------------------------------------------ |
| **Name**                     | Data Refresh                               |
| **Type**                     | BIG\_QUERY                                 |
| **Return Type**              | Boolean                                    |
| **Message: Condition Met**   | `Metric: ${metric} with ${result}`         |
| **Message: Condition Unmet** | `Condition not met with result: ${result}` |
| **Enable Custom Email**      | Enabled                                    |
| **Period**                   | Daily                                      |

***

### Email Setup

| Field             | Value                                               |
| ----------------- | --------------------------------------------------- |
| **Subject**       | Data Refresh                                        |
| **Email Message** | `You wanted us to let *you* know when ${subtexts}.` |

The email will be sent when a data refresh issue is detected.

***

## Best Practices

* Always ensure your compute SQL query is **optimized** for execution efficiency.
* Use **meaningful names** for computes.
* Test the compute manually before setting it to periodic execution.
* Keep notification messages **clear** and **actionable**.
* Use placeholders to make queries flexible across environments.

***

## Troubleshooting

| Issue                     | Solution                                                                             |
| ------------------------- | ------------------------------------------------------------------------------------ |
| Compute not triggering    | Check if the dataset is correctly refreshed and the condition is evaluated properly. |
| Incorrect values in email | Verify placeholder usage like `${metric}`, `${result}`, and `${subtexts}`.           |
| SQL errors                | Test query directly in the database first before pasting it into the compute setup.  |

***

## Quick Template for New Computes

You can use this template to quickly create a new compute:

```sql
WITH base AS (
    SELECT *
    FROM `${dataset_id}.${table_name}`
    WHERE --your conditions here--
)
SELECT (CASE WHEN [your condition] THEN TRUE ELSE FALSE END) AS condition, 
[your details appear in the mail body] AS subtext
FROM base;
```

* Replace `--your conditions here--` and `[your condition]` as needed.
* Replace `[your details appear in the mail body]` with your content. You can use plain text or format it using HTML markup.
* Setup corresponding email notifications under the **Email Tab**.

***

## Summary

Custom Computes empower you to monitor key data-driven events automatically and notify the right teams when something important happens.\
By integrating computes into your Infinity workflows, you ensure proactive detection of potential business issues.

***