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.

  • $\begingroup$ 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) $\endgroup$
    – 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.

Instead of doing a simple single linear interpolation, several sets of "complete" data are generated stochastically, in principle using information from all of the associated data. Analyses are then run separately on each of the imputed data sets, and combined appropriately. This way you don't throw out data and you can estimate the error introduced by the imputation process.

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy