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

***


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.getinfinity.app/user-guide/comprehensive-user-guide/12.-advanced-topics/custom-computes-for-event-based-subscriptions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.