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:
From leads_table. Group By 1
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.
Part 1 – How to build a snapshot report
Part 2 – Creating a metric based on a report
Part 3 – Linking metrics & alerts to reports
We hope you found this article helpful. If you have a tip that other users might like to know about, please let us know and we’ll share it in a future article.
