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: Jan 05, 2015
Metric Insights sometime packs a lot of information to a single display grid to allow full view of associated data, but you can easily modify these to include only those columns of interest to you.
Updated on: Dec 01, 2014
Metric Insights will remember the section that was last opened on the Home Page between logins, but if you want to always start new sessions in the same section, you can do so with the 'Start me in' setting in your Preference Editor.
Updated on: Nov 19, 2014
In the example below, you are following the user "John Frank" and the Discussion on an Annotation
Updated on: Nov 03, 2014
This describes our Proof of Concept process for potential customers.
Updated on: Oct 28, 2014
I used the "Recollect data" button to refetch data but my old data is still showing. Why?
The "Recollect data" button returns data that is new and does not already exist in Metric Insights. It does not delete, replace, or update existing data. If you want to remove and replace the current data with a fresh batch of data, use the delete data button and then use the recollect button.
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
Updated on: Oct 13, 2014
For starters, you'll never see a 'total' dimension value for a report. There is no general way to compute a total across multiple instances of a report. Therefore, we compute totals for metrics only.
Assuming that you're working with a metric:
- Did you elect to create a 'Total' dimension value? - Review Dimension Editor
- Does the underlying Measure have the proper settings? Review Measure Editor
- Did you elect to use the 'Total' dimension value for your metric? Review Metric Editor
Updated on: Oct 06, 2014
The Daily/Weekly/Monthly drop-down list is used to select another Measurement Interval for viewing. The contents of this drop-down list are determined by the Measure of the Element being viewed. When you select a different Measurement Interval from the list, Metric Insights displays an element that 1) has the appropriate Measurement Interval; and 2) shares the same Measure as the element that you're currently viewing. To ensure that you see the correct element, you will need to create a distinct Measure for each KPI that you plan to track in Metric Insights.
Updated on: Aug 31, 2014
The starting point for the generation of an Alert is always a Metric. Once you have set up a Metric to trigger Alerts, you can perform additional functions. This article describes how to extend an Alert to an External Report.