You have three problems here:
- How to identify user constantly
- How to count user clicks(actions)
- How to get result statistics
Here I think that showing different subjects on one page is not a problem
Problem 1
Basically you can use an IP address but it is not a constant id for user. For example if user uses mobile phone and walks, he can switch between towers or loose connection and then restore it with different ip.
There are many ways to identify user by the web, but there is no way to identify user on 100% without authorization (active action done by user)
For example you can set Cookie to user with his generated ID. You can easily generate id you can look here. When you set up cookie and user will come back to you, you will know who it is and do the stuff you need.
Also within user uniqueness you can reed this article - browser uniqueness
Also if you use Cookie, you can easily store there subject id for your task. If you will not use Cookie i recommend you use mongodb for this kind of tasks (many objects with small data, that must be retrieved from db very fast, inserted to db very fast and there are no updates in your case)
Problem 2
You showed table that has 3 fields: ID, Used title, Is title clicked.
In this kind of table you will lose all not unique click (when user clicks on subject twice, goes there tomorrow or refreshes target page multiple times)
I suggest you to use following kind of table
- ID - some unique id, auto increment field will be good here
- Date - some period of measurements (daily, hourly or something like that)
- SubjectID - id of subject that was shown
- UniqueClicks - count of users that clicks on subject
- Clicks - Total count of clicks on subject
In this case you will have aggregated data by period of time and you will easily show data in admin panel
But still we have problem with collecting this data. Solution of this problem depends on count of users. If there is more than 1000 clicks in minute, I think that you need some logging system. For example you will send all data to file 'clickLog-' . date('Ymd_H') . '.log' and send data to this file in some static format, for example:
clientId;SubjectId;
When hour is end you can aggregate this data by shell script or your code and put it to db:
cat clickLog-20160907_12.log | sort -u | awk -F';' '{print $2}' | sort | uniq -c
after this code you will have 2 columns of data. First will be count of unique clicks and second will be subject id
Modifying this script you can get total clicks with just removing sort -u section
Also if you have several subject ids you can do it with for:
For example bash script for unique clicks can be following
for i in subj1 subj2 subj3; do
uniqClicks=$(cat clickLog-20160907_12.log |
grep ';'$i'$' |
sort -u |
wc -l);
clicks=$(cat clickLog-20160907_12.log |
grep ';'$i'$' |
wc -l);
# save data here
done
After this manipulations you will have prepared aggregated data for calculating and source data for future processing (if needed)
And also your db will be small and fast and all source data will be stored in files.
Problem 3
If you will do solution in Problem 2 section, all queries for getting statistic will be so simple, that your database will do it very fast
For example you can run this query in PostgreSQL:
SELECT
SubjectId,
sum(uniqueClicks) AS uniqueClicks,
sum(clicks) AS clicks
FROM
statistic_table
WHERE
Date BETWEEN '2016-09-01 00:00:00' and '2016-09-08 00:00:00'
GROUP BY
SubjectId
ORDER BY
sum(uniqueClicks) DESC
in this case if you have 3 subject ids and hourly based aggregation you will have 504 new rows in weeks (3 subjects * 24 hours * 7 days) that is really small amount of data for database.
Alternatives
You can also use Google Analytics for all calculations. But in this case you need to do some other steps. Most of them are configuration steps that need to be done to enable google analytics monitoring scripts on your site. If you have it, you can easily configure goals support and just apply to script additonal data with subjectid by using GA script api
