Updated on: Sep 22, 2015
There are two popular scenarios for alerting with there is missing data or a data source fails to update. In both cases, we start by pulling in your source data. Then we create a metric to implement some logic and set an alert rule.
Updated on: Aug 28, 2015
Updated on: Aug 28, 2015Manual System
Updated on: Aug 13, 2015Manual Distribution
Updated on: Jan 30, 2015
Let's say that you track sales activity in Salesforce, and web traffic in Google Analytics. How would you compute the ratio of Sales Opportunities to Web Visits? It would be easy if you had all of the data in one place. With Metric Insights, you do.
In this edition of Tips & Tricks, we'll show you how to use existing elements as data sources for new elements. We'll start with metrics because they're easy.
Let's say that your Monthly Opportunities metric has element ID = 1, and your Monthly Visits metric has element ID = 2. One way to compute the ratio of Opportunities to Visits would be to use 'Existing Metrics' as your data source, along with the following simple fetch command:
:1 / :2
Metric Insights interprets that as the ratio of metric #1 to metric #2, which is what we want. The arithmetic for such combinations is intuitive. If it looks reasonable, it will probably work.
You may occasionally need to do a computation that can't be handled with simple arithmetic. In such cases, you can work directly with the MySQL table where your metric data is stored.
Let's repeat the calculation of the Opportunity-to-Visit ratio. This time, we'll do it the hard way. Instead of 'Existing Metrics', we'll use 'Dashboard DB' as the data source. The Dashboard database is the logical backbone of Metric Insights, and is where all of your data and metadata are stored. The table that we want is metric_measured_value.
Here's the SQL code:
Select m1.measurement_time, m1.measurement_value_int / m2.measurement_value_int From metric_measured_value m1, metric_measured_value m2 Where m1.measurement_time = m2.measurement_time And m1.metric_id = 1 And m2.metric_id = 2
That's not as elegant as the first method, but it's more flexible, so it might come in handy for complex calculations.
Note: We're assuming that both metrics have data type = Integer. If either of them has data type = Decimal, we would use measurement_value_float instead of measurement_value_int.
As our final example, we'll once again compute the ratio of Opportunities to Visits, but this time we'll assume that the relevant data has been collected in reports instead of metrics. Specifically, we'll assume that we have a 'Monthly Opportunities' report, with columns named 'Opportunity Month' and 'Opportunities'. We'll also assume that we have a 'Monthly Visits' report, with columns named 'Visit Month' and 'Visits'.
The first step is to edit both reports and set 'Would you like to create other elements based on this report?' to 'Yes'. (Look for it in the Advanced Settings section.) When you do that, Metric Insights will create a MySQL table based on your report. The rules for converting report and column names to table and field names are simple: uppercase letters are converted to lowercase, and all special characters (including spaces) are converted to underscores.
Back in the metric editor, select 'Existing Reports' as your data source and use the following SQL code:
Select opportunity_month, opportunities / visits From monthly_opportunities, monthly_visits Where opportunity_month = visit_month
As a bonus tip, you can get a list of tables for the 'Existing Reports' data source by creating a report whose SQL statement is simply 'show tables'. Once you know the name of the table, you can find out what's in it by replacing the SQL statement with 'desc <table>', where <table> is the name of your table. (These tricks will also work for any other SQL data source.)
In version 3.1 (soon to be released), we will include a graphical 'SQL Builder' that will make this kind of operation even easier.Manual Tips and Tricks
Updated on: Oct 24, 2014
Introducing Tips & Tricks, a new series of articles designed to help you get the most out of Metric Insights. This inaugural article covers snapshotting, a powerful method that you can use to track 'static' data over time.
Let's say you want to track a KPI over time, but you're not actually storing that KPI in any of your source systems. For instance, you might want to track the number of sales leads that you have. You know how many you have right now -- you just count the number of records in your 'Leads' table. If you don't record the result, however, you won't be able to track the number over time. That's what snapshotting is for. You start by taking 'snapshots' of your Leads table at regular intervals, for example, once per week. Over time, you'll accumulate a series of snapshots that can then be used to construct a 'Weekly Leads' metric.
Metric Insights provides two different mechanisms to make this easy. If you know which KPIs you want to track, you can compute them directly as metrics. For the Weekly Leads example, your fetch command might look something like this:
Select now(), count(*) From leads_table Group By 1
Note: The 'now()' function returns the current datetime value. It only works with SQL data sources at the moment, but will be available for any of our plug-ins starting with 3.1 releases.
This fetch command returns a single record, containing the number of leads as of now. All that remains is to connect the metric to a weekly data collection trigger. Metric Insights will then compute the number of leads at weekly intervals, which is exactly what we want.
This method works well if you know what you want to track. If you change your mind later, however, you might discover that you haven't kept enough data to compute your new KPI. For example, the Weekly Leads metric won't help you to compute Weekly Leads by Source. That's where the other snapshotting method comes in handy.
The starting point is to create a "Weekly Leads" report, whose fetch command might look something like this:
Select lead_name, lead_source, etc. From leads_table
Instead of simply counting the number of records in the Leads table, we're actually collecting all of the records. The extra data will provide the flexibility that we're looking for.
We'll connect the report to a weekly data collection trigger, as before. We'll also set "Keep History" to "Yes", so that all instances of the report will be preserved. Metric Insights will take a snapshot of the Leads table at weekly intervals and will timestamp each snapshot based on the :measurement_time parameter. (See this article for an overview of the :measurement_time parameter.)
The final step is to set "Would you like to create other elements based on this report" to "Yes". That will cause Metric Insights to store the report data in a MySQL table named weekly_leads. We'll use that table to create our new metric, with a fetch command that looks something like this:
Select lead_source, measurement_time, count(*) From leads_table Group By 1, 2
That's a typical fetch command for a dimensioned metric. The only unusual item is the measurement_time field, which did not appear in the source data. Metric Insights created that field for us, and populated it with the timestamp for each snapshot.
We could have created this metric using the direct method, above. The advantage of the second method is that it provides greater flexibility in case you change your mind about what you want to track. Any field included in the Weekly Leads report can be used in the fetch command for your metric.
We've created a series of videos about snapshotting. You can find them (along with other useful videos) on our tutorials page.Manual Tips and Tricks