How to: Create custom metrics in PowerMetrics

We talk a lot about metrics at Klipfolio. We’re passionate about metrics and how you and your team can use them to drive better business decisions. 

Metrics are a core part of PowerMetrics. The platform enables people like you to create metrics, prepare and model data, build dashboards and reports, store data and its corresponding history, and share insights with your team. 

PowerMetrics offers three ways to create a metric: instant metrics, custom metrics, and calculated metrics.  

In this article, I’m going to dive into custom metrics. But be sure to check out this resource for more information on metrics and this resource on calculated metrics.

First, let’s start with the basics. 

What is a metric?

A metric is a measurable value that represents performance, success, or progress towards a specific goal or objective. Metrics also store the history of the value over time and can be visualized multiple ways. 

For example, let’s say you want to track your Monthly Recurring Revenue—how it changes month over month and which vertical has the highest amount of new subscriptions or expansions. 

To create a Monthly Recurring Revenue metric, you would take your MRR data from your data source (like a spreadsheet or accounting software), model your data with the dimensions you need, and create a metric using a calculation. Now that you have a quantifiable metric that you can use to monitor performance. 

Metrics visualize your data, making it easy to analyze and understand versus sifting through a spreadsheet and doing back-of-a-napkin math. 

I mentioned earlier that PowerMetrics offers three ways to create a metric: instant, custom, or calculated. Let’s take a look at custom metrics.  

What is a custom metric?

Custom metrics are true to their name—a custom metric built using your own modelled data source. A modelled data source is the channel between your service (let’s say Google Analytics, for example) and the custom metric that you’ll create using that data. 

By comparison, our instant metrics use pre-built connectors to third party services like HubSpot or Stripe, and we auto-model the data for you.

Custom metrics require you to pull your data into PowerMetrics from a data source created by you, like a spreadsheet, SQL query, or REST API. Then you can model this data to create custom metrics. 

Once you’ve created your data source, PowerMetrics automates the data collection and starts to stack up your data history, but more on that to come later. 

What is a data model?

PowerMetrics requires a modelled data source in order to create a metric. You can do this step in our data modeller tool. Why does the data source need to be modelled? For metrics to be used effectively, they need to be standardized. 

Think of metrics like building blocks. For metrics to fit together in order to compare time periods or dimensions, they need to have the same foundation. A standardized set of metrics gives you and your team a rich analytics and reporting system. 

A data model is a cleaner, simplified version of your data that is easy and quick to work with. When you model your data, you define and manipulate the data you want to include by applying formulas or data formats, like text, number, percentage, currency, date, or duration. 

Once your data is modelled, you can create a metric with it. It’s worth noting that a data model can be used to create multiple metrics—there’s no limitation to how many times you can use your data. 

How-to create a custom metric

You need three ingredients to create a custom metric: a data source, a data model, and the metric itself. Let’s look at each step in a bit more detail.

1. Create a data source 

In this step, you’ll gather your data, whether it’s an Excel spreadsheet, an SQL query, or data you’ve exported from a cloud application. Ideally, you should try to make sure there is a relevant date with a timestamp that you can add to your data source, too. Once you have it ready to go, you’re ready to move into the second step: model it. 

2. Create a data model from your data source

In this step, you’ll bring your data into our data modeller. Here you’ll be able to further refine and format your data, like removing null values or fixing text discrepancies. You can also use formulas in the data modeller to populate additional columns in your data, or merge the model with another for a richer data set. 

When you’re modeling your data, it’s important to consider the metric you’re planning to create with it. Does your data source contain the right information, like numeric values you want to track or records to be counted? Are all of the dimensions you want to use in your metric available in your data source so you can segment your data and look at it from different perspectives? Think about the columns you need, the dimensions you want to filter by, and the time dimension and make sure it’s included in your data source so we can store it in your metric.

A few other tips to be successful with the data modeller:

• Don’t over prepare your data. You want to have access to the flexibility and customization available in PowerMetrics
• Use the unpivot function to convert data from a pivot table format to a list table
• Use formulas to optimize your data, like grouping it into categories to simplify your metric
• Group related columns, like first name and last name, into combined dimensions, like a name column, for clarity and ease of use

3. Create a custom metric

It’s time to create your metric. Here you will select your metric value, segmentation, and date and time from the data model, as well as, define the data shape and display settings for your metric. 

Data shape will allow you to select an aggregation method, like transactional values, current values, or periodic summary. Display settings is where you name your metric, choose the data format, like numeric, currency, percentage, or duration, and optionally choose to show your data as cumulative or set a favourable trend. 

Other features for custom metrics

Data modeling is a core feature of custom metrics, but there are a few others that I want to touch on, too.

Data history and storage

PowerMetrics can store up to 10 years worth of data for you. And, depending on your plan type, you can access a date range window that displays anywhere from two months to three years. Data history and the ability to compare metrics across time periods is key to making data-driven business decisions. 

Traditional data storage software, like databases or spreadsheets, requires manual data entry or configuration, which can be time consuming to regularly update. Once you configure a metric in PowerMetrics, your metrics will pull in and incorporate your new data automatically, so you can always have the most up-to-date information at your fingertips.

PowerMetrics also offers backfill. Instant metrics have backfill auto-enabled, which means when you create a metric, we go back and automatically retrieve up to six months of data. Backfill is available for custom metrics, but would require you to pull the custom data in from your data source. 

And, if your data source doesn’t have historical data, we start recording it for you as soon as you create a metric with that data. 

Query Builder

Query Builder is a powerful tool that lets you access your custom data within the supported data services that you connect to. Within Query Builder, you can connect the tables and fields in your data.

For example, you could connect your Google Analytics data, select the Acquisitions table, and then go on to select the columns like New Users, Sessions, and Session Medium to be used in your data source and model. Now, you can query that information within the Query Builder interface to pull that data into PowerMetrics. You can preview it, add additional columns like a date to give your data a shape, or add optional filters if the API allows it. Then, you can create a custom metric from the model.

It’s a metrics-based approach to reporting and analytics

Metrics are dynamic and enable you to set up your dimensions, aggregation types, history, and trend indicators, giving you complete control from start to finish. You can also add goals and notifications to all metrics to monitor your progress, anywhere, anytime. Build, manage, and explore your metrics to match your skill level, technical expertise, or data goals—all in PowerMetrics.