Count daily pageviews to articles from search result pages on mobile web
ActivePublic
Actions

Authored by • chelsyx on Sep 7 2017, 7:55 PM.

Tags

None

Referenced Files

	F9380561: Count daily pageviews to articles from search result pages on mobile web
	Sep 7 2017, 7:55 PM

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;

Count daily pageviews to articles from search result pages on mobile webActivePublicActions