T174719 BannerHistory sampled/BannerLoader ratios, France campaign, mobile and iOS, by platform, 2017-10-19
ActivePublic
Actions

Authored by • AndyRussG on Oct 23 2017, 3:38 PM.

Project Tags

None

Referenced Files

	F10385815: T174719 BannerHistory sampled/BannerLoader ratios, France campaign, mobile and iOS, by platform, 2017-10-19
	Oct 23 2017, 3:38 PM

Subscribers

None

	CREATE TEMPORARY MACRO json_value(path string, col string)
	get_json_object( substr( reflect("java.net.URLDecoder", "decode", col ), 2 ), path );

	SELECT
	bh_sampled.requests,
	bl.requests,
	( bh_sampled.requests / bl.requests ) AS ratio,
	bl.browser,
	bl.os
	FROM (
	SELECT
	count(*) AS requests,
	user_agent_map['browser_family'] AS browser,
	user_agent_map['os_family'] AS os
	FROM
	wmf.webrequest
	WHERE
	uri_path LIKE '%beacon/event%'
	AND uri_query LIKE '%CentralNoticeBannerHistory%'
	AND year = 2017
	AND month = 10
	AND day = 19
	AND geocoded_data['country_code'] = 'FR'
	AND referer LIKE '%.m.%'
	AND json_value( '$.event.r', uri_query ) IS NOT NULL
	AND webrequest_source = 'text'
	GROUP BY
	user_agent_map['browser_family'],
	user_agent_map['os_family']
	) bh_sampled
	JOIN (
	SELECT
	count(*) AS requests,
	user_agent_map['browser_family'] AS browser,
	user_agent_map['os_family'] AS os
	FROM
	wmf.webrequest
	WHERE
	uri_query LIKE '%BannerLoader%'
	AND year = 2017
	AND month = 10
	AND day = 19
	AND geocoded_data['country_code'] = 'FR'
	AND referer LIKE '%.m.%'
	AND webrequest_source = 'text'
	GROUP BY
	user_agent_map['browser_family'],
	user_agent_map['os_family']
	) bl
	ON (
	bl.browser = bh_sampled.browser
	AND bl.os = bh_sampled.os
	);

Event Timeline