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.)
