Setting up alerts in Redash

Tue, 03 Dec 2019 10:55:11 +0300

A very handy function on alert building is implemented in Redash. Alerts stand for notifications, arising at adjustment of some specific indicator. At that, the degree of change is set manually by a user on his own in the interface. Alerts can be set to a mail or to a channel / direct messages Slack.

Let’s recall how to collect the data, using Google Analytics, and set up alerts for this data, applying the internal Redash database (query-results). As a foundation for an alert we will focus on the reduction of number of users on the site for the previous day by more than 30%.

Building a query to Google Analytics

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users", 
    "dimensions": "ga:date"
}

As a result of performance of this query, we will receive a number of users for the last 30 days.

Turning on the storage of query results

One of the main features of redash is an opportunity of calling upon the results of query performance, that are stored in the internal DBMS SQLite.
In order to turn on the storage of results, we need to go to Data Sources and turn on query-results (beta).

And now, with a simple command presented below:

select * from query_37

we will receive a result, similar to the one of the previous query to GA, however this one has an opportunity of using SQL language for processing of the data set obtained.

Building a query for evaluation of changes within the number of users

In order to set up the alert, first of all we need to write a query, that will eventually provide us with a target indicator for check, in our case it is growth or reduction in the number of users on the website.
Lets write a query, calling upon the internal DBMS of Redash:

SELECT sum(CASE WHEN date(ga_date)=DATE('now', '-1 day') THEN ga_users ELSE NULL END) AS yesterday,
sum(CASE WHEN date(ga_date)=DATE('now', '-2 day') THEN ga_users ELSE NULL END) AS before_yesterday,
(sum(CASE WHEN date(ga_date)=DATE('now', '-1 day') THEN ga_users ELSE NULL END)*1.0/
sum(CASE WHEN date(ga_date)=DATE('now', '-2 day') THEN ga_users ELSE NULL END)*1.0-1)*100 AS difference
FROM query_37

In the above-shown query we are calculating the number of users for two previous days, and also evaluating the change of number of users in percentage.
In the current example, we have received the following table of data, that we will use further at the process of alert setting:

Now, in order for us to be able to receive alerts, we need to set query performance by schedule (regular update of results / scheduled query in redash terminology).

We set an update for 10 o’clock of every morning:

Setting alert

Going to the menu Create – Alert. Inserting the name of query, in my case it is “Alert on users”.
Hereafter, we can change the reflected name of notification or leave the one, proposed by the system.

Choosing a target metric within the example reviewed – difference. Below, in the comparison operator (Op) selecting less then and setting a value of -30.

In the block on the right you need to select where exactly the alert will be sent. You can read about the setting of alert’s path more thoroughly on the official website of Redash.

Now, the alert has appeared on the page with the list of alerts and is by default in the status OK.

As soon as the indicator exceeds the level that we’ve set, the status will change to TRIGGERED, and the alert will be sent to mail / to Slack.

LEFT JOIN: blog on analytics, visualisation & data science, posts tagged: alert

Setting up alerts in Redash

Building a query to Google Analytics

Turning on the storage of query results

Building a query for evaluation of changes within the number of users

Setting alert