Methods for calculating statistics of visiting pages on the site "on the fly"

Often there is the task of counting the number of visits to a particular portal page or counting clicks on a link. For example. It may be interesting to count the number of views of an article or advertisement, the number of clicks on the “show phone number” on the advertisement page, etc. etc.



The main thing is to understand exactly what you are planning to use these counters for and what they may be needed in the foreseeable future. Do you need to select / sort / group entities by them or do you just need rare one-time reports (for example, once a day)? Or maybe you want to show these counters in the admin panel or in the user's personal account? Do you want to show these numbers in real time or can you limit yourself to the hour / daily cutoff? Depending on these and possibly some other criteria, the choice of method for counting and storing the counters will depend.



Counting clicks / visits



The task of counting clicks / visits can be solved in different ways:

• when visiting the page or when accessing some URL, do +1 to the counter;
• periodically review the logs of the Web server and read the number of calls to the necessary URLs;
• do through redirect with a special URL, and do +1 before the redirect (but this, as you understand, does not work in all cases);
• using JavaScript to access the counter URL for an event;
• other ways for which there is enough imagination.

')

At this stage, it is necessary to decide whether we need to count the visits of search robots. You may not want to read requests made from the internal network. It may also be interesting to consider only requests from authorized users, etc.



It is also important to understand how critical the accuracy of the calculation is, whether it is possible to make a mistake by 1-2-3 units or not. Much can depend on it. If accuracy is important, then performance can be neglected, or vice versa, if performance is important (for example, page speed), then accuracy can be neglected slightly.



Counter Storage



You can store counters in different ways too:

• in the database in the form of a numeric field directly in the table of the entity / page;
• in a database in a separate table created specifically for the counters, but also in the form of a simple numeric field;
• group different counters, serialize them and also store them in a BLOB or varchar field;
• in any NoSQL database, for example, as Id -> count;
• combination memcache + DBMS;
• in files. At this point, whoever is at that much, can be in one file in a serialized form, it is possible by file to the entity. In general, you can think of a lot of things, including the implementation of its simple DBMS for storing counters.

Life example



Let's look at a specific example from our practice. At once I will say that we use MySQL.



Task: counting the number of visits to the page of the announcement of the Purchase and Sale section of the Auto Mail.Ru project, excluding Web bots (Google, Yandex, Mail.Ru Search). You also need to consider the total number of views of all announcements of a car dealership publishing its ads for sale on our portal Auto Mail.Ru.



Important points:

• The accuracy of counting is important, but in some very rare force majeure situations, a total loss of no more than 200 visits is acceptable (these are visits in just a few minutes in the morning / afternoon / evening).
• It is necessary to sort the entities by the number of visits.
• Updating the counter in the database in the morning / afternoon / evening hours at least once every 30 minutes. This is necessary for the relevance of sorts and adequate display of the number of visits in your account.

At first they tried to implement "in the forehead." We created the views_count field in the entity table (similarly in the car dealership table) and updated it every time we looked at the advertisement page (did UPDATE

. .