Recording custom page views and then converting them into payload?
My site logs clicks to the database whenever a user views an article. This table is automatically cleared every 3 days. We use the data to compile the most viewed pages during this 3-day period.
I would also like to use this data for marketing purposes, for example, to determine which users like sections of the site.
I wrote a script in php that does the following:
-
grab user IDs of any registered member who has viewed the article in the last 3 days
-
for each user, I ask how many times they viewed the articles in each section, eg Bobby looked 10 pages in the Food and Drink section and 6 pages in the Sports section. I tried to combine this step and the previous one, but got a strange result for the results.
-
This gives me a nice array in this form:
[Bobby Jones] => Array ([Movie] => 10 [Home] => 1 [Food & Drink] => 2 [Health & Beauty] => 1 [Gifts & Gadgets] => 3)
What I want from this data will end up having a table that registers the data in the array above and then increments it when I run my queries.
Unfortunately, this adds a lot of overhead. When I have my arrays like the one above, I have to run another query to check if this combination of user and category exists in the database, and if so, I have to increment it by that day's values. For example, if Bobby has viewed 10 pages of a movie last week and has viewed 6 this week, I need to UPDATE the table. If he has never viewed the Food page before, I need to INSERT instead.
This query returns 400 or so users who have interacted with the site in the last 3 days. This means that for each user, I have to make 1 request to get their browsing totals, 1 request to see if they have already viewed this category, and another update / insert request, depending on whether they have viewed him or not. You can see how inefficient this is.
Can anyone suggest a better way to do this? My ultimate goal is to complete a spreadsheet that shows me how often my users view my categories, so I can say "show me all users who like food and drinks" and so on.
Thanks, Matt
source to share
You can execute UPDATE / INSERT behavior using MySQL INSERT...ON DUPLICATE KEY UPDATE
.
You can combine SELECT and INSERT queries using MySQL INSERT...SELECT
.
If you post more details (like a schema, for example), I can show you an example query combining these two methods, although the MySQL manual is quite detailed for both objects.
source to share
If you are using MySQL and the version is high enough, see INSERT ... ON DUPLICATE KEY UPDATE . This should shorten the request.
Then make sure your tables are entered correctly and that these two queries should be lightweight.
source to share