# Handling gaps in statistics

For a web application we are building I am supposed to overhaul our statistic collection service.

Basically we are collecting simple statistics such as positive comments, negative comments, views, likes, etc.

We are not using any analytics API and we are supposed to store metrics in a relational database.

What we came up with is the following database design:

statistics: from:DateTime, to:DateTime, ref_id, metric_id, value
metrics: id, name


The Data is collected by a job that runs every few minutes and collects data from various sources. For every metric a seperate record is created. When the metrics value does not change instead of creating a new record the to column for the metric_id will be extended to the current DateTime.

So far so good. In a perfect world that idea should work good enough but unfortunately that probably won't be the case. There are basically three sources of failure:

• The job does not run for some reason
• A bug in the application causes metric/stat records to not be created
• 3rd party APIs which collect data may fail

In this case gaps will occur that aren't supposed to be there. My question is: Are there any best practices to "patch" those gaps? My thoughts on that:

• Silently extend the to Timestamp of the record before the gap occured to the start time of the latest "after-the-gap" record
• Interpolate statistics linaeally: Subtract the metrics of the record after the gap and the record before the gap and distribute the results across the timespan of the gap

I don't like both ideas. Silently extending seems to be just a dirty cover-up for failure but at least end users won't be affected. The second options looks a bit better but results will still be inaccurate because statistics are most often not a linear matter.

I would appreciate any thoughts or ideas on that matter.

• You might get some ideas from financial applications. Missing data for certain time-periods is a very common scenario when showing graphs and calculating statistics for financial metrics. (Some days are bank holidays in certain markets or there is no trading in a stock etc)
– Bjorn
Jul 14 '15 at 9:39

Provided that the probability of an observation being missing does not depend on the value of the observation (technically, data are "Missing at Random"), the method of multiple imputation is a reasonable approach.
There is a multiple-imputation tag on this site that you can follow, and a useful website with further information. Tools (like mice) are available in packages written for R.