Designing database tables for storing analytic’s data depends on what kind of data you are storing. All analytics platform are not same. Some track number of users and their locations while some also store information like browser used and time user stayed on the website. In this article I will show you the database design behind QBlock Analytics. If you don’t know what QBlock is then, QBlock lets you track users with adblock installed and also lets you display alternative ads.
QBlock is taken down due to lack of users and higher expenses.
Total Number of Tables
This table stores total page views and adblock ads blocked page views of a particular user account. username column is primary key therefore while retrieving total page views you must use the username in WHERE clause.
Whenever someone visits a page with QBlock analytics code installed the page views column value is incremented by 1. blocked_page views column value is incremnted by 1 only if adblock is installed. Adblock detection takes place on frontend. The analytics code must send username to the server so that you can identify the specific row in table and increment its page views count.
This table stores the total page views date wise. The username_date column is a value formed from the combination of username and a date separated by an underscore.
Let me make it clear with some sample data on all two tables for only one user. Suppose the username is qnimate.
qnimate_11.10.2014 1000 100
qnimate_12.10.2014 1000 100
qnimate_13.10.2014 1000 100
qnimate_14.10.2014 1000 100
qnimate_15.10.2014 1000 100
qnimate_16.10.2014 1000 100
Before inserting into daily stats table we need to check if we have a username_date combination of current date. If not then we insert a new row otherwise we update the current date row.
To retrieve page views of qnimate user for last 5 days, we first need to find dates of last 5 days. And then we can retrieve the rows 1 by 1 using the WHERE clause. To find yesterday’s dates using PHP you can refer this article.
This approach seems hacky but works very well. You don’t need to do any sorting in any case. Everything is retrieved via the Index which is very fast. You can add more fields to complete_statistics table and also daily_statistics table to store more varieties of data.