Count daily full-text search result pageviews on mobile web
ActivePublic
Actions

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

Tags

None

Referenced Files

	F9380552: Count daily full-text search result pageviews on mobile web
	Sep 7 2017, 7:52 PM

Subscribers

None

	USE wmf;
	SELECT
	date,
	COUNT(DISTINCT CONCAT(client_ip, user_agent, query)) AS n_search,
	COUNT(*) AS n_serp,
	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(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'search') AS query,
	PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), '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
	-- flag for pageviews that are search results pages
	AND page_id IS NULL
	AND (
	uri_path = '/wiki/Special:Search'
	OR (
	uri_path = '/w/index.php'
	AND (
	LENGTH(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'search')) > 0
	OR LENGTH(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'searchToken')) > 0
	)
	)
	)
	) AS serp
	GROUP BY date;

Count daily full-text search result pageviews on mobile webActivePublicActions