I have web content (articles, stories) where each article is grouped in a category such as NEWS, STORY, etc. Website visitors are grouped by region. In each region, I want to be able to rank each category by the number of site visitors who read articles in a category.
I can get a count by region and category.
I can get a count by Region, VisitorID, Category.
However, I want to know how many site visitors had CAT1 as their most-read category. How many had CAT1 as their second most-read category? How many had CAT2 as their most-read category?
Here's an example:
asked 09 May '11, 12:01
Try aggregating by region, visitor id, and classification. Then, use streamstats to create the rank field.
Breaking down the search command, here are the results from each section:
stats count as hitcount by geo_region, visid, Classification
You want to sort in order of the number of hits most to fewest sort geo_region,visid,-hitcount
Use streamstats to count the number of rows for each visitor id. By starting with 1 for each visitor id, this creates a rank field.
streamstats count as rank by visid
Then, count the number of rows per Classification and rank
stats count as rankcount by geo_region, Classification, rank
answered 09 May '11, 12:05