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?
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.
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.
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.
Did this article help you?
Thanks for your feedback!