Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Paste
P5974
Count daily pageviews to articles from search result pages on mobile web
Active
Public
Actions
Authored by
•
chelsyx
on Sep 7 2017, 7:55 PM.
Edit Paste
Archive Paste
View Raw File
Subscribe
Mute Notifications
Award Token
Flag For Later
Tags
None
Referenced Files
F9380561: Count daily pageviews to articles from search result pages on mobile web
Sep 7 2017, 7:55 PM
2017-09-07 19:55:49 (UTC+0)
Subscribers
None
USE
wmf
;
SELECT
date
,
COUNT
(
DISTINCT
CONCAT
(
client_ip
,
user_agent
,
query
))
AS
n_search
,
COUNT
(
*
)
AS
n_pv
,
COUNT
(
DISTINCT
CONCAT
(
client_ip
,
user_agent
))
AS
n_user
FROM
(
SELECT
CONCAT
(
year
,
'-'
,
LPAD
(
month
,
2
,
'0'
),
'-'
,
LPAD
(
day
,
2
,
'0'
))
AS
date
,
access_method
,
client_ip
,
user_agent
,
agent_type
,
PARSE_URL
(
referer
,
'QUERY'
,
'search'
)
AS
query
,
PARSE_URL
(
referer
,
'QUERY'
,
'searchToken'
)
AS
searchToken
FROM
webrequest
WHERE
year
=
2017
AND
month
=
08
AND
day
=
01
AND
webrequest_source
=
'text'
AND
access_method
=
'mobile web'
AND
agent_type
=
'user'
AND
is_pageview
-- only those that have been referred by a search results page:
AND
referer_class
=
'internal'
AND
(
LENGTH
(
PARSE_URL
(
referer
,
'QUERY'
,
'search'
))
>
0
OR
LENGTH
(
PARSE_URL
(
referer
,
'QUERY'
,
'searchToken'
))
>
0
)
)
AS
pv
GROUP
BY
date
;
Event Timeline
•
chelsyx
created this paste.
Sep 7 2017, 7:55 PM
2017-09-07 19:55:49 (UTC+0)
deryckchan
mentioned this in
T212843: [EPIC] Access to Wikidata's lexicographical data from Wiktionaries and other WMF sites
.
Jan 26 2019, 11:18 PM
2019-01-26 23:18:27 (UTC+0)
Log In to Comment