Mastering Data Analysis: Identify Significant Changes Through Indexing

Amara James Moosa
Jan 7
3 min read

Updated: Feb 26

Introduction

As Mark Twain famously said, "There are three kinds of lies: lies, damned lies, and statistics." While data offers invaluable insights, interpreting it correctly can be a real challenge. Every day, analysts are faced with a flood of information, from subtle trends to sudden spikes. How do you separate the signal from the noise? This article will guide you through the process of identifying truly significant metric shifts, helping you avoid time-consuming investigations of inconsequential variations.

Indexing Solution

One powerful technique for analyzing metric shifts is to create an indexed time series. By setting a base period (e.g., the first month) as the starting point (index of 100), subsequent periods are expressed as a percentage of this base. This 'levels the playing field' by standardizing the scale, making it easier to spot significant trends and compare metrics with vastly different magnitudes. For instance, a consistent upward trend in the indexed series indicates sustained growth, while sudden drops signal potential issues.

How Is It Computed

The first step is to select a base period for your analysis. This period should represent a typical or normal state for the business metric. Stakeholders can provide valuable input in this selection process.
Once the base period is chosen, we assign it an index value of 100. This serves as our baseline for comparison.
To calculate the indexed value for any subsequent time period (t), we use the following formula:

Indexed Value (X_t) = ( (X_t) / (X_0) ) * 100

Where:

X_t: Represents the value of the metric in time period (t).
X_0: Represents the value of the metric in the base period.

By multiplying the ratio of the current period's value to the base periods' value by 100, we express the current period's value as a percentage of the base period.

Real World Example

Imagine you're a newly hired product analyst at a major eCommerce site, joining the team after the height of the COVID-19 pandemic in 2021. While familiarizing yourself with key metrics, you notice that 2020 revenue appears unusually low compared to previous years (see Table 1 below). To determine if this was a truly significant shift jumping in unnecessarily root cause analysis, you decide to conduct a deeper analysis.

Period	Revenue	Revenue
2019	$277,900,000	100
2020	$173,500,000	62.4
2021	$196,055,000	70.5

*Table 1: Generated data for educational purpose only.

Let's dive into an example. We've selected 2019 as our base year, with a revenue of $277,900,000 and an assigned index of 100. Now, let's calculate the index numbers for 2020 and 2021:

2020: ($173,500,000 / $277,900,000) * 100 = 62.4
2021: ($196,055,000 / $277,900,000) * 100 = 70.5

Notice the sharp drop in 2020, with an index of 62.4, indicating a significant revenue decline compared to the base year. This alarming observation prompted the analyst to conduct thorough research to uncover the root cause of this drop.

How is it used and by whom?

Across various fields, index-number trend series are a powerful tool when comparing data more than 3 series.

Finance: Track investment performance, inflation, and market trends.
Product: Monitor product sales, customer satisfaction, and market share.
Marketing: Analyze campaign effectiveness, market trends, and consumer behavior.
Public Policy: Track economic indicators and social trends to inform policy decisions.
Security: Identify and analyze security threats, monitor the effectiveness of security measures, and forecast future risks

Important Note

Indexing may not be suitable for all data, particularly when dealing with negative values or values that fluctuate between positive and negative.

Pro Tip

Beyond analyzing single metrics, indexing excels when comparing multiple time series with different starting points. This allows for a more accurate and insightful analysis of relative changes.

Conclusion

By employing the indexing technique, analysts can effectively identify significant shifts in their metrics, cutting through the noise of raw data. This method provides a standardized framework for comparing data across different time periods, regardless of the original scale. By understanding the magnitude and direction of changes relative to a chosen base period, businesses can make more informed decisions, identify potential problems proactively, and capitalize on emerging opportunities.

Data Analytics Training Resources

Analysts Builder

Master key analytics tools. Analysts Build provides in-depth training in SQL, Python, and Tableau, along with resources for career advancement. Use code ABNEW20OFF for 20% off. Details: https://www.analystbuilder.com/?via=amara