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:

1. Condition [Boolean] - True if the compute logic evalutes to True; False otherwise.

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

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('<p style="font-family: Georgia, serif; text-shadow: 1px 1px 2px lightgray;">',
'Data is Refreshed at:', FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', CURRENT_DATETIME()), '.</p>') as SubText
FROM base4;

---

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:

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.

---