Exceptions Reports and Alerting - prior to Version 5 and the Creation of Datasets
Creating Exception Reports has been simplified in newer Versions of Metric Insights by using Datasets. See: Create a Change Report from Dataset
How and when to send an exceptions report in an alert email.
There are many scenarios when sending a report makes more sense then sending a metric's chart in an alert email. The most common scenario is when the user wants an exceptions report, a report that has a list of items that need attention. We sometimes refer to these as 'problem children.' Reports give the user more flexibility than a metric's chart in that a metric only contains a time-series, a numerical value, and a dimension value (optional). For example, a metric could tell you sales had dropped on 01-01-15 (time series) to 9% (value) for the Western Region (dimension).
If a user wants to receive more information than this, they will need to use a report since a report can have many columns and does not require a time-series.
This approach is especially powerful as an alternative to using a high number of dimension values. Instead of creating a single metric that is dimensioned by 1000 values, like cities, managing a single report can be faster and easier. This technique is especially nice if you've already created the report (perhaps in another application such as Tableau) and simply need to connect it to the alerting mechanism.
1. The Workflow
Configure the data source (#1) if not done so already.
2. Save Report Data in Mysql Table
Build your report by fetching data from the datasource to include only the 'problem children.' In other words, create the report that you want to see an the alert email. If you want to see a report of stores that are below their sales target, filter the records to only return those stores using the syntax in the plugin command box.
Let's say that you're a manager for a bank that has 10,000 customers. As a protection against accounting errors, you've asked to be alerted if any account balance changes by more than 20% on any given day. You could then set at alert that would fire if today's balance differs from yesterday's balance by more than 20%.You can do this with a metric, but a report lets you see all the problems at once. Let's start with what you want, namely, a list of affected accounts. Here is a generic SQL statement:
Select account_id, balance_today, balance_yesterday From source_table Where balance_today < 0.8 * balance_yesterday Or balance_today > 1.2 * balance_yesterday
Once you create the report, use the option to save the data in a MySql Table so it can be queried in the next step. You can use the :measurement_time parameter in the fetch command for incremental data collection (only get new data).
3. Create the Metric and Alert Rule
Since only metrics create alert emails, we need to create a metric to look to see if there are any problem children on the report for a given period. Use 'Existing Reports' as the data source and select the 'measurement_time' column for the time series--we create the column for you--and then 'count(*)' to see how many problem children exist. Use the 'SQL Builder' if you need a hand.
4. Alert Rule
The metric counts the records in the report's table (the problem children). We want an alert, i.e., to send the report out when there are any problem children (when the county is more than 1). To do this, create an alert rule based on a fixed value.
5. Connect the Metric to the Report
Assign the report(s) you want to send out when the alert fires. You can have multiple and different types. If the metric and report use the same dimension, an alert for a particular value will send out the corresponding version of the report. For example, an alert for Western Sales (metric) can send out the Western Sales version of the report.
6. Alert Email
An alert email is sent based on your alert rule (immediate vs digest etc). The alert email will have the metric's chart and all connected reports assigned to it in the email. The Alert rule and threshold will appear in the email 'Problem Children more than 0 on 1-1-15.'
If you only want to send the report and not the metric's chart in the email go to the Alert window on the metric and uncheck the box next to the rule. The rule still applies to the report (if you go the report and look at its alert window it will still be checked).
Troubleshooting Tip: If you are not getting immediate alerts when you think you should, check the alert rule on the Report's Alert window. It will assume the default settings for the user. For example, if I create an alert rule to go out immediately, but my users default settings (Alerts page) are set to show 'Browser only' then the report will default to 'Browser only.' Change the report's alert rule setting to immediate to match the metric. We are working on improvements in the process too!
If you cannot isolate the problem children in one fetch command from the data source
If you cannot filter the data to just the problem children using the native syntax or integration, bring in all the data from the data source. Then create another report that queries that report (report #3 queries report #2) and use SQL to identify the problem children.
Tip: if you want a subset of problem children dimension value
If you do want to create a dimension consisting of problem children, you can source those values from the report instead of the data source. This creates dimensions for only the items that need alerting. For example, if you have 5000 stores, but only 25 are problems on a given day, creating 25 dimensions is exponentially faster than updating 5000.