I would like to generate analytics on a per-user basis on how many times they've viewed a particular page for 7 days, and how many times they've viewed a page during their lifetime as a user.

This will be tracked in 1d, 7d, 14d, 30d, and lifetime intervals. If a user visits the page today, they will appear to have 1 visit in the 1d, as well as 1 additional visit in all date range categories above 1d- since visiting once today means you've visited at least one in all other date range categories.

enter image description here

To do this I am storing all events in a data lake, and rolling up these date range counts every 24 hours based on the criteria. The actual users data is in a document, but it wouldn't be feasible to store all events for a user in their document given how much data that would eventually be, and the inevitable data skew that would create on the cluster. This works now, but it's taking more and more time to generate these rollups as the data grows, and even with partitioning, the number of pages we are doing this rollup on is growing at a pace where the current method may not scale gracefully.

When we receive these events, we could update the users counts at runtime. But without the context of the date, there would be no "dropoff". If it were a lifetime count we could always increment that field, but the counts need to update daily as a page view today is not a page view tomorrow.

Something that stands out to me is that the counts will consistently gravitate towards one end. But that may just be an observation and not anything useful.

enter image description here

Is the only way to do this the way I've described, or is there a more clever way to update these fields?

  • 1
    An obvious observation is that the "lifetime" category can consist of a simple cumulative sum, which doesn't need to be repeatedly rolled up from the base data. It's only the 1d-30d counts which need to be managed, and once a visit is older than 30d then the base data can be purged and just the lifetime total stored. I can't see any obvious scalability problem with this. – Steve Apr 27 at 15:00
  • 2
    Would it help if you kept the day-based records only up to 30 days and consolidate everything older than 30 days in a single "lifetime" record? For the true lifetime count, you would have to add this "lifetime" number to the 30d count. And if the other periods are not "today and the X days earlier", but rather "the current calendar month", you could do similar roll-ups of the data where you combine several records with the same next-higher cut-off characteristic in the same record. – Bart van Ingen Schenau Apr 27 at 15:02
  • BartvanIngenSchenau and Steve - Good points. I am currently doing this rollup on all data historically. The lifetime count is just a cumulative sum, so I could limit this to 30 days worth of data, and purge data after 30 days. That could make a big difference. – micah Apr 27 at 15:08

Your Answer

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

Browse other questions tagged or ask your own question.