Today we’re reporting and monitoring our Total Sales and Profit by each of our Sales regions. For each region, we evaluate at any given time whether they’re Underperforming or “On-Track” with their current profit margins.
If any of our regions are underperforming, we would like to have more detailed information sent to us right away so that we can correct or track down issues regionally.
Threshold-based Email alerts Arcadia feature:
Today Arcadia has a feature that allows you to evaluate a given metric based on a static threshold through the Email job feature:
This features allows you to email a snapshot of either a Visual or an entire Dashboard, based on the evaluation of a query running from a secondary Visual (We’ll call this the Threshold Visual)
A Threshold Visual can be created from the Email Visual modal, or it can be constructed on its own as a table Visual:
There are 2 primary requirements to ensure that a threshold is evaluated properly against a Threshold Visual:
- The Threshold Visual should only contain 1 Measure (i.e. total_profit)
- The first value in the table of the Threshold Visual will be the value that’s evaluated against the threshold in the Email job.
Based on those requirements, you must craft your Threshold Visual carefully in order to ensure that you’re going to get the expected result when your trigger threshold is evaluated.
Click here to see the complete documentation regarding Threshold-based Visuals and setting up email notifications.
Step-by-Step example of creating a Threshold-based Email alert from Arcadia:
Step 1: Define the measure you’re wanting to monitor and threshold you’re looking to use to trigger an alert.
In our example, we’re interested in monitoring “total profit” ( sum([profit]) ). Specifically, we’re interested in knowing whether any of our sales regions have a total profit less than $10,000.
Step 2: Generate the Threshold Visual (query) that will analyze the measure you’re monitoring
In this example, we’re interested in monitoring “total profit” across each of our regions. NOTE: We’re sorting our measure in ascending order here because we want any regional with a total profit less than $10,000 to show up at the top of the table (first record), which allows the threshold to trigger properly against the first measure value in the table.
Step 3: Generate the Visual or Dashboard you want to be Emailed when the threshold is triggered
If any of our regions have a total profit less than $10,000, we want to see a breakdown of profit by the region, and also the product category and sub category:
You’ll notice we’re using a filter in this Visual that limits the results to only those regions with a “total profit” of less than $10,000. This was achieved by using a Derived Data sub-query, which calculates the total profit by region:
If we look closer at the query we can see how the sub-query results are joined back to the main query so that we filter down into regions in the Visual that have less than $10,000 in total profit:
WITH T1 AS ( SELECT TA_0.`region` as `__region`, sum((TA_0.`profit`)) as `total_profit_subquery` FROM `default`.`superstore_sales` TA_0 GROUP BY 1 ORDER BY 2 ASC LIMIT 100 ) SELECT TA_0.`product_category` as `product_category`, TA_0.`region` as `region`, TA_0.`product_sub_category` as `product_sub_category`, sum((TA_0.`profit`)) as `total_profit` FROM `default`.`superstore_sales` TA_0 INNER JOIN `T1` T1 ON ( TA_0.`region` = T1.`__region` ) WHERE (T1.`total_profit_subquery` < 10000) GROUP BY 1, 2, 3 ORDER BY 4 ASC LIMIT 5000
Step 4: Create the Email job and threshold for the Email alert
To create a regular Email job that will send a copy of our Profit by Category and Subcategory, you need to click the “Email” button from the Visual builder screen:
And then select the “Email based on threshold” option, and select the “Set Threshold” tab. Within this tab, you can choose which Visual (Visual ID) will run the query to evaluate the threshold trigger, the actual value of the threshold trigger, and also the frequency that the threshold should be evaluated and how often to send an email when the threshold trigger is met.
In our example, the Threshold Visual ID from our previous steps was 9202 and we also decided that “total profit” less than $10,000 would be our threshold:
After the threshold has been configured, you can than select the “Email Detail” tab and configure who the email alert should be sent to, the message to send along with the email, the type of Visual attachment, and if page parameters should be included.
Step 5: Run job and confirm threshold is working
This may be difficult depending on if your threshold will be met on the first test run, but you can run your new Email job by going to the Job Status page in Arcadia:
And then click the “Run Now” button next to your Email threshold job:
If you can’t get your threshold to work, you can create a second Email job that will for sure meet a higher threshold.