The Library Data Pipeline: Building a Predictive Data Model in Excel

Transfer raw data into decision-ready information

Posted on January 7, 2026 in Blog Posts

Libraries are swimming in data, but raw numbers rarely lead directly to good decisions. To move from a messy spreadsheet to a defensible strategy, you need a process to refine that raw material.

In this post, we will walk through the entire data pipeline, which is the structured process of transforming raw data into decision-ready information.  

We will be using the example of a new video subscription. Imagine your library has subscribed to Swank Streaming since last January. Usage is growing, and you need to forecast next month’s usage to justify the budget renewal. But there’s a catch: the vendor’s reporting server crashed in June, leaving a hole in your data.

We are going to take that messy dataset and turn it into a solid prediction using three connected steps: Cleanup → Forecasting → Optimization.

The Raw Data

Here is the usage report you downloaded from the vendor. Notice the problem immediately?

If you take an average of the list to predict November, the “0” in June will drag your average down artificially. You cannot predict the future until you fix the past.

---

🔥 Stay up-to-date with LibTech Insights by signing up for our free newsletter. Just one weekly email with our new blog posts, top tech news stories, and other bonus content. Check out some posts from our archive:

• AI Tools for Academic Libraries: AI Task Management
• Beyond the Job Title: Scholarly Communications Technologies Librarian
• OLMoTrace: An AI That’s Transparent about Its Training Data

---

Step 1: Cleanup (Imputation)

The goal is to repair the missing data point so it doesn’t skew our average. We already know the usage didn’t actually drop to zero; the tracking broke. If we leave it at zero, Excel will think the platform crashed. We need to fill that gap with a mathematical best guess, a process called imputation.

We can use interpolation by averaging the months surrounding the error (May and July).

May = 130

July = 140

June = (130+ 140)/2 = 135

We replaced “0” with “135”. Now our data reflect the reality of user behavior, not the failure in technology. The pipeline is clear for the next step.

Step 2: Prediction (Weighted Moving Average)

Now that we cleaned the numbers for June, we can forecast November.

We know that recent data are the most important, so we will use a four-period Weighted Moving Average (WMA). This is a simple method that allows us to clearly understand how the forecast was calculated. We need to assign weights to the last four months. A logical guess would be using a step-up approach. We would give most importance (40%) to the most recent month, 30% to the month before, 20% to the one before that, and 10% to the oldest.

To evaluate the accuracy of the WMA model, we also track error metrics in our spreadsheet. Error is simply the difference between the actual usage and the forecasted usage. Absolute Error is the positive value of that error, ignoring whether we over- or underestimated. Mean Absolute Deviate (MAD) is the average of all Absolute Errors and serves as our primary measure of model reliability. When we have a lower MAD value, it indicates a better forecast. Finally, we have Tracking Signal (TS), which compares the total accumulated Error to the MAD and helps us detect if the model is biased (either over- or under-forecasting).

Let’s plug this into Excel and see how we did:

The result for November was 161. But look at the MAD score, it is 15.50. That means our logical guess missed the mark by about 15.5 plays every month. Also, notice that all the errors are negative, which means that our forecast is consistently behind real growth. A standard WMA model often struggles to keep up with a fast trend like this.

We can do better, though. We don’t need to switch to a complex new model or keep guessing for the weights—we can solve for them!

Step 3: Optimization (Excel Solver)

We can keep the exact spreadsheet structure, but now we can introduce Excel Solver. (Refer to my earlier post for a full introduction to Solver.)

We can tell Solver to change the weights for the last four months and make the MAD cell as low as possible. Let’s set up Solver to fix this!

• Go to the Data tab and click Solver
• Set Objective: Set the cell reference to the MAD value (K10)
• To: Select Min (we want the lowest possible error)
• By changing variable cells: Select the weights (K2:K5)
• Subject to the Constraints: Click Add to enter the rules:
  • Minimum total for each weight: $K$2:$K$5<=1 (each weight has to be less than one)
  • All weights: $K$6=1 (all weights when added up have to be equal to one)
  • Ensure the “Make Unconstrained Variables Non-Negative” box is checked (the weights cannot be a negative value)
• Select a Solving Method: GRG Nonlinear (We chose this method because our objective is to minimize the MAD score, which is a nonlinear function. Simplex LP only works for straight-line relationships)
• Click Solve.

The Optimal Solution

Solver assigned 100% weight to the most recent month because our rapid growth makes historical data act like an anchor. Since the data from three months ago are so much lower than today’s, including them simply drags the forecast down and increases error. Solver realized it had to “cut the anchor” to minimize that error, resulting in a forecast of 175, which is identical to October. This happens because moving averages have a mathematical ceiling: they cannot predict values higher than your history. By hugging that ceiling, Solver provided a defensible statistical floor (175) rather than our initial, lagging guess (161).

Conclusion

Building a data pipeline might seem like a lot of work just to arrive at a forecast that matches the previous month’s numbers. But the value isn’t just in the final digit. It is in the rigor of the path you took to get there.

Through this process, we transformed a broken dataset into a strategic asset. We used Imputation to repair the past, a Weighted Moving Average to model the future, and Solver to remove human bias. In doing so, we proved that our initial “logical” guess (161) was statistically flawed and that the true baseline for our resource is significantly higher (175).

This is the difference between reporting data and leveraging data. When you walk into a budget meeting, you aren’t just saying, “I think usage is going up.” You are saying, “Our optimized model confirms this growth trend is real and sustainable.” That creates the kind of defensibility and confidence that every library leader needs.

Related Posts