Time series data correlation: avoid misleading relationships

Time series data correlation measures whether two business measures move together across ordered periods. Before reading a coefficient, align the periods and check trend, seasonality and lag: a strong relationship between the levels can disappear when you compare the underlying changes.

Start with the period and the business question

A finance team might ask whether orders booked earlier help explain recognized revenue later. Another might compare revenue with sales activity in the same month. Those are different questions, even when the measures come from the same exports.

Write down what each date means. An order date, shipment date and revenue recognition date can put the same transaction in different months. Agree whether the rows represent calendar months, fiscal periods or completed weeks, and use the same business scope and currency.

Build one period table before calculating anything. Keep a continuous calendar, aggregate duplicate period rows, and mark incomplete extracts. Reconcile monthly revenue to the financial report and operating counts to their source export. A high correlation cannot repair a missing month or a double-counted invoice.

For the spreadsheet function itself, use the separate correlation calculation guide. The task here is deciding whether the ordered observations support the interpretation.

What the coefficient measures

Pearson correlation measures the direction and strength of a linear association. Positive values indicate that higher observations tend to occur together; negative values indicate that higher observations of one measure tend to accompany lower observations of the other. A value near zero means little linear association, which does not exclude a curved relationship.

Correlation r = Sum of [(x - mean x) times (y - mean y)] / Square root of {Sum of (x - mean x) squared times Sum of (y - mean y) squared}

The calculation pairs observations by row. It does not know whether the dates match or whether the values share a trend. NIST's scatter plot guidance explains how plotted pairs expose relationship patterns and why association does not establish cause and effect.

Time ordering adds another issue: neighboring observations often resemble one another. Revenue this month may be related to revenue last month. NIST defines autocorrelation as correlation between values of the same series at different times and notes the assumption of equally spaced observations. Treating dependent months as independent evidence can make conventional significance claims unreliable.

A spurious relationship in a monthly export

Consider a fictional US distributor. Revenue rises during its expansion, while cumulative office badges issued also rises as employees join and replacement badges are created. Management has no established mechanism by which issuing a badge increases sales.

Revenue change is current revenue minus previous revenue. Newly issued badges are the change in the cumulative count. January has no change observation because the previous month is absent; those blanks are not zero.

The raw levels produce a correlation of 0.9923. Comparing changes produces -0.0400, very close to zero. The positive relationship between the levels mainly reflects both series moving upward over time; the month-to-month movements tell a different story.

Here is the calculation behind both figures. Centered sums mean deviations from each series' own mean, using only the paired rows for that comparison.

For levels, 8,300,000 / square root of (6,137,500,000 times 11,400) = 0.9923. Substitute the change-column sums into the same formula to obtain -0.0400. Calculations retain full precision; displayed means and sums round to two decimal places and coefficients to four.

This small illustration is enough to reject the claim that the raw coefficient alone supports issuing more badges. It does not prove that every transformed relationship is meaningful or that a weak change correlation rules out every possible connection.

Compare transformations that answer the question

First differences compare the current period with the previous period. They ask whether incremental movements line up. Percentage changes divide that difference by the previous value and ask whether relative growth moves together; they become problematic when the earlier value is zero or negative.

Seasonal differences compare a period with the matching period in the previous seasonal cycle. For monthly annual seasonality, compare a month with the same month a year earlier. That can reduce a recurring seasonal pattern, but overlapping comparisons can still be dependent.

Hyndman and Athanasopoulos explain in Forecasting: Principles and Practice how differencing can reduce trend and seasonality, while other transformations can stabilize changing variation. Select a transformation for the business question and the series' behavior. Repeatedly transforming until an attractive coefficient appears creates a selection problem.

A moving average can make a trend easier to see. It also smooths neighboring periods together, so a correlation between smoothed lines needs particular caution. Use data analysis graphs and charts to inspect both the time pattern and the paired observations.

Choose lags from how the business works

A lag shifts the pairing. If a booking normally becomes revenue in the following month, pair January bookings with February revenue, February bookings with March revenue, and continue consistently. Label the relationship explicitly as earlier bookings versus later revenue.

Choose plausible lags before looking for the strongest result. Document the operational reason, the pair count after shifting, and whether the association persists in a later period. Searching many shifts and reporting only the largest coefficient overstates the evidence.

If the relationship matters after those checks, time series regression addresses a different question: estimating a relationship while accounting for specified influences. A correlation screen does not perform that estimation or establish causality.

Where it goes wrong

• Misaligned definitions. Booking month paired with recognition month can obscure a real timing relationship or create an artificial one.
• Shared trend mistaken for a driver. Two rising levels can correlate strongly even when their changes barely relate, as the badge example shows.
• Shared seasonality overlooked. Holiday demand can lift unrelated series together. Compare matching seasonal periods and inspect the calendar.
• Missing periods entered as zero. An absent file is not a month with no revenue. Check valid pairs and exclusions.
• A favorable lag selected afterward. Searching across many lags makes accidental relationships easier to find. Record the intended lag before calculation.

Compare your own period files in Covirage

Bring your revenue export and period-level operating measures, with the dates, units and any transformations labeled. Covirage maps the columns, its tools compute and check the supplied figures, and the external AI model explains the observed movements with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See revenue driver analysis for reviewing the business measures behind revenue movements.