Wanted to know how the “Ratio of sum of all time spent in 3rd party resources to the total time spent in all content” looked like (it is logical that the more the third party requests, more the fraction of time spent on third party content). Used the time attribute of the requests table (assuming it would have the major time components like sending and receiving).
First wrote the following query to get the percentiles. Used the same logic mentioned in the post to detect first and third party origins. Added a check for time > 0 to eliminate erroneous data (if any).
SELECT
NTH(10, quantiles(thirdpartytimetototaltimeratio)) p10,
NTH(20, quantiles(thirdpartytimetototaltimeratio)) p20,
NTH(30, quantiles(thirdpartytimetototaltimeratio)) p30,
NTH(40, quantiles(thirdpartytimetototaltimeratio)) p40,
NTH(50, quantiles(thirdpartytimetototaltimeratio)) p50,
NTH(60, quantiles(thirdpartytimetototaltimeratio)) p60,
NTH(70, quantiles(thirdpartytimetototaltimeratio)) p70,
NTH(80, quantiles(thirdpartytimetototaltimeratio)) p80,
NTH(90, quantiles(thirdpartytimetototaltimeratio)) p90,
NTH(99, quantiles(thirdpartytimetototaltimeratio)) p99,
COUNT(*) totalpages
FROM
(
SELECT
thirdpartytime/totaltime thirdpartytimetototaltimeratio
FROM
(
SELECT
pages.pageid,
SUM(IF (req_host CONTAINS REGEXP_EXTRACT(origin, r’([\w-]+)’), 0, requests.time)) AS thirdpartytime,
SUM(requests.time) as totaltime
FROM httparchive:runs.2017_08_01_requests requests JOIN EACH (
SELECT pages.pageid, DOMAIN(url) as origin
FROM httparchive:runs.2017_08_01_pages pages
) pages ON pages.pageid = requests.pageid
GROUP BY pages.pageid
)
WHERE totaltime > 0
)
The results:
Next tried to do a histogram.
SELECT
thirdpartytimetototaltimeratiobucket, COUNT(*) totalpages
FROM
(
SELECT
ROUND((thirdpartytime/totaltime) * 100, 0) thirdpartytimetototaltimeratiobucket
FROM
(
SELECT
pages.pageid,
SUM(IF (req_host CONTAINS REGEXP_EXTRACT(origin, r’([\w-]+)’), 0, requests.time)) AS thirdpartytime,
SUM(requests.time) as totaltime
FROM httparchive:runs.2017_08_01_requests requests JOIN EACH (
SELECT pages.pageid, DOMAIN(url) as origin
FROM httparchive:runs.2017_08_01_pages pages
) pages ON pages.pageid = requests.pageid
GROUP BY pages.pageid
)
WHERE totaltime > 0
)
GROUP BY thirdpartytimetototaltimeratiobucket
ORDER BY thirdpartytimetototaltimeratiobucket ASC
The results:
