Getting insights from time series data
An overview of methods you can use to track progress, estimate impact, and more.
What is time series analysis?
A time series is a dataset where every row represents an event or measurement at a different point in time. For example, all of the tables in the Sample Database are time series datasets, because every row has a “Created At” timestamp. Fact tables can also be used for time series analysis.
Time series analysis combines metrics and data visualizations to answer business questions like:
- How is my revenue growing, shrinking, or plateauing over time?
- When do my customers come back (or not)?
- Are my customers getting more value from my business over time?
Trends
A trend tracks a measure or metric over different time periods.
When analyzing trends, you’ll want to pick a time period that matches your business. For example, if you post fresh content to a blog each week, you’ll look at weekly audience engagement metrics. If your business uses a monthly subscription model, you’ll track your subscription revenue monthly.
Analyzing trends in Metabase
- Comparing time periods shows you how to get the total revenue one year at a time, so that you can compare the revenue year over year. Month over month and season over season comparisons are also covered in this tutorial.
- Visualizing time periods gives you tips on how to organize and present trends from different questions on a single dashboard.
- Rates of change calculates a percentage metric over all of the months in a dataset and visualizes the trend as a line chart. You’ll also learn how to display extra context in the chart tooltip.
- Dates in SQL is the SQL companion to Rates of change.
- The Trend visualization type is a snapshot of the delta from the previous time period to the current time period.
Rolling metrics
A trend with a rolling metric calculates the metric over a shifting window. For example, instead of calculating revenue each calendar month, you’d calculate revenue over shifting 30-day periods:
- Jan 1 to Jan 30
- Jan 2 to Jan 31
- Jan 3 to Feb 1
- and so on, until Jan 28 to Feb 28
The most common type of rolling metric is a rolling average (sometimes called a moving average). Rolling metrics are useful because they smooth out seasonality and noise in your data.
For example, churn rate is commonly calculated as a rolling metric, because subscription cancellation can happen at any time in the calendar month.
KPIs
Some metrics summarize a group of numbers better than an average or ratio. These metrics are often used as Key Performance Indicators (KPIs).
For example, Customer Lifetime Value (CLV or LTV) is a metric that estimates customer value using a combination of average historical spend and predicted future spend.
The trend in a metric like LTV can give you a better representation of how your business is doing (compared to an average or ratio), because the LTV calculation is based on more information.
Measuring LTV with Metabase
- Calculating LTV: are you doing it wrong? is a light primer on when to calculate LTV, and how to interpret the results.
- Calculating LTV using SQL is an advanced SQL tutorial that calculates the monthly change in LTV using revenue, active subscriptions, and churn rate.
Next: Period-over-period comparisons for time series
How to measure the change in a metric over time by comparing two or more time periods.