Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Please explain the (uniq) pageview database entries #12441

Closed
matomoto opened this issue Jan 14, 2018 · 3 comments
Closed

Please explain the (uniq) pageview database entries #12441

matomoto opened this issue Jan 14, 2018 · 3 comments
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@matomoto
Copy link

Hello Matomos,

While researching a solution to the problem "Let me delete Visitors from Visitor Log" (#3385) I have some questions regarding the database entries in the database table "archive_numeric_xxxx_xx".

Specifically I have questions about the column "name" and the entries "Actions_nb_pageviews" as well as "Actions_nb_uniq_pageviews".

I've already tried a lot. Deleting the entries via the visitorID is as far as possible (works), but resetting or subtracting the entries in the pageview and unique pageviews seems to be very complicated.

I have tested with two database variants. A database live. A database as backup dump from 2018-01-12 ~18:00:00. Even when querying the database entries, there are differences.

If I let the entries for 2018-01-12 show, then there are many entries in the database-backup-dump and in the database-live only one. Question: Are entries deleted retrospectively?

All entries for the 2018-01-12 in the database-backup-dump (2018-01-12 ~18:00:00):

WHERE name='Actions_nb_pageviews' ORDER BY idsite, ts_archived, date1, date2 ASC
if filter: idsite, date1 = 2018-01-12, date2 = 2018-01-12, ts_archived = 2018-01-12.

idsite - idarchive - name - date1 - date2 - ts_archived - value
32 - 797 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - **2018-01-12 11:46:13 - 121**
32 - 798 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:46:14 - 28
32 - 796 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:46:16 - 1109
32 - 795 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:46:20 - 3020
32 - 803 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:51:28 - 30
32 - 802 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:51:29 - 1111
32 - 801 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:51:31 - 3022
32 - 876 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:54:35 - 34
32 - 875 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:54:37 - 1115
32 - 874 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 11:54:39 - 3026
32 - 881 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 12:01:58 - 35
32 - 880 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 12:02:00 - 1116
32 - 879 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 12:02:02 - 3027
32 - 886 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 12:06:18 - 55
32 - 885 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 12:06:20 - 1136
32 - 884 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 12:06:21 - 3047
32 - 909 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:09:37 - 70
32 - 911 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:10:31 - 1151
32 - 910 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:10:33 - 3062
32 - 934 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:13:03 - 71
32 - 935 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:22:29 - 72
32 - 936 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:28:17 - 72
32 - 937 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:32:34 - 74
32 - 938 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:37:26 - 75
32 - 939 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 13:48:08 - 75
32 - 940 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 14:01:58 - 81
32 - 941 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 14:53:09 - 95
32 - 942 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:01:14 - 103
32 - 948 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:02:53 - 1184
32 - 947 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:02:55 - 3095
32 - 951 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:06:56 - 105
32 - 952 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:37:17 - 122
32 - 953 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:39:53 - 123
32 - 954 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:43:34 - 125
32 - 955 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:46:55 - 130
32 - 956 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:52:55 - 132
32 - 957 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 15:56:06 - 135
32 - 960 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:00:24 - 135
32 - 959 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:00:27 - 1216
32 - 958 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:00:30 - 3127
32 - 963 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:03:33 - 137
32 - 964 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:14:13 - 139
32 - 965 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:20:33 - 143
32 - 966 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 16:23:42 - 154
32 - 967 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - 2018-01-12 17:38:57 - 194

All entries for the 2018-01-12 in the database-live (at least a day later [yet 2018-01-14]):

WHERE name='Actions_nb_pageviews' ORDER BY idsite, ts_archived, date1, date2 ASC
if filter: idsite, date1 = 2018-01-12, date2 = 2018-01-12, ts_archived = **2018-01-12**.

idsite - idarchive - name - date1 - date2 - ts_archived - value
32 - 797 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - **2018-01-12** 11:46:13 - 121

All entries for the 2018-01-12 in the database-live (at least a day later [yet 2018-01-14]):

WHERE name='Actions_nb_pageviews' ORDER BY idsite, ts_archived, date1, date2 ASC
if filter: idsite, date1 = 2018-01-12, date2 = 2018-01-12, ts_archived = **2018-01-13**.

idsite - idarchive - name - date1 - date2 - ts_archived - value
32 - 971 - Actions_nb_pageviews - 2018-01-12 - 2018-01-12 - **2018-01-13** 11:09:30 - 210

If you want to reset the pageviews on the same day, it is not possible to find the appropriate database entries. At least one day later, there seems to be only one entry left and the others have been deleted?

In addition, there are really damn many entries in the table archive_numeric_xxxx_xx. It is very hard to look through it.

Intermediate info:
Summary for the day 2018-01-12
pageviews 210
unique pageviews 190

If I search for and change the value "210" in the database table, this also affects the total number of pageviews for one month. (and probably the total in general).
If I search for and change the value "190" in the database table (close to the 210 for the 2018-01-12), then this does not immediately affect the total number of unique pageviews for a month, but time-delayed. (and probably the total in general).

Another question is how to count the number of pageviews and unique pageviews?
If I find all the database entries through a visitorid, I do not know how many were counted for what.
@mattab
Copy link
Member

mattab commented Jan 14, 2018

Are entries deleted retrospectively?

Yes

Another question is how to count the number of pageviews and unique pageviews?

See our glossary definition here: https://glossary.matomo.org/

For your other questions please ask in our forums: https://forum.matomo.org/

@mattab mattab closed this as completed Jan 14, 2018
@mattab mattab added the answered For when a question was asked and we referred to forum or answered it. label Jan 14, 2018
@tsteur
Copy link
Member

tsteur commented Jan 14, 2018

BTW: You don't really want to mess around with archive tables and instead invalidate archives and re-process them eventually. Everything else won't really work or be correct. https://matomo.org/faq/how-to/faq_155/

@matomoto
Copy link
Author

Thanks for the hints. Eventually the "InvalidateReports" is a helpfull Plugin.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@tsteur @mattab @matomoto