What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 122 Stack Exchange communities.

UPDATED: Asked on Stack Overflow

Starting with posts.xml, we can create a simple table (filtering by PostType=Question), with four columns:

  • Id
  • ViewCount
  • CreationDate
  • Tags

It's somewhat trivial to normalize the Tags out such that we have three tables:

  • Questions (Id, ViewCount, CreationDate)
  • QuestionTags (Id, TagName)
  • Tags (TagName)

Given those three tables, how would you go about determining the estimated page views for a given tag (or group of tags) over a month? The tricky parts:

  • Views do not come in at a constant rate, the have a initial bump and a long tail; i.e., you can't do "ViewCount/Months_Open" to determine per-question page views
  • Overall traffic is increasing at a steady rate, so past data would need to be adjusted for inflation

Also, should I post this as a SQL/database question on Stack Overflow?

share|improve this question
add comment

2 Answers

It does sound more like an sql question.

Unfortunately, I don't think posting it anywhere else will help you. Sorry to be the bearer of bad news twice in one day, but I don't think you have enough information there to show the results you want. All that's stored is the number of views, but to group them by month you also need some better idea of when each view occurred.

I think the best you can do is an approximation that credits all the views for a question on the creation date. It's not horrible, since most posts gets most of their views in the first few hours. But it's going to short you in some important ways: posts near the end of month, new links that bump old content, etc.

share|improve this answer
    
If you start running the query now, you can use the current View count as a baseline, store it in a new table with a date stamp then track views per month going forward. –  Rob Allen Sep 10 '09 at 15:21
    
Maybe: compare different monthly data-dumps with each other. That seems awfully sketchy, though. –  Joel Coehoorn Sep 10 '09 at 15:26
    
It's definitely doable, just a tricky query to develop. On the aggregate, all questions have the same tail pattern (dont have stats on hand, but it was like 9 9 8 7 7 5 1 1 1...) and there is a constant growth rate (say, 13%); I just need to figure out how to account for those instead of doing a (ViewCount/MonthsOpen). –  Alex Papadimoulis Sep 10 '09 at 15:29
    
Regarding the tail: we can derive that based on recent vs. old questions. A 1-2 week old question has (say) 100 views/week; a 3 week has 80/week, a 4-6 week has 70/week, and so on. So, we can use this to predict how newly-asked questions will behave. –  Alex Papadimoulis Sep 10 '09 at 15:33
    
@Joel - Where is the sketchy? If you are seeing 1k page views in the previous dump and 1500 views in this dump, thats 500 views in the time between. –  Rob Allen Sep 10 '09 at 16:03
    
It's sketchy because to be reliable over time it requires fully automating the monthly data dump process, and that's a non-trivial operation at the moment. –  Joel Coehoorn Sep 10 '09 at 16:10
    
I see. You mean Sketchy as in potentially a pain. I was thinking Sketchy as in underhanded/dubious –  Rob Allen Sep 10 '09 at 18:07
add comment
up vote 0 down vote accepted

To answer my own question....

Yes, I should have asked this on Stack Overflow in the first place... and given a big-ass bounty to make it happen.

share|improve this answer
add comment

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .