Simple check: GIF results from HA

1

Just making sure I’m not losing my mind. I had a simple thought around GIFs online, and decide to run bigQ to get some figures. Nothing should be simpler as I could simply remix some existing recipes:

#standardSQL
SELECT
  SUBSTR(_TABLE_SUFFIX, 0, 10) AS date,
  UNIX_DATE(CAST(REPLACE(SUBSTR(_TABLE_SUFFIX, 0, 10), '_', '-') AS DATE)) * 1000 * 60 * 60 * 24 AS timestamp,
  IF(ENDS_WITH(_TABLE_SUFFIX, 'desktop'), 'desktop', 'mobile') AS client,
  ROUND(APPROX_QUANTILES(bytesGif, 1001)[OFFSET(101)] / 1024, 2) AS p10,
  ROUND(APPROX_QUANTILES(bytesGif, 1001)[OFFSET(251)] / 1024, 2) AS p25,
  ROUND(APPROX_QUANTILES(bytesGif, 1001)[OFFSET(501)] / 1024, 2) AS p50,
  ROUND(APPROX_QUANTILES(bytesGif, 1001)[OFFSET(751)] / 1024, 2) AS p75,
  ROUND(APPROX_QUANTILES(bytesGif, 1001)[OFFSET(901)] / 1024, 2) AS p90
FROM
  `httparchive.summary_pages.*`
WHERE
  bytesGif > 0
GROUP BY
  date,
  timestamp,
  client
ORDER BY
  date DESC,
  client

This is essentially the image data recipe from HA, and just redone just to feature image/gif, and it gave me all the data I needed - if not more. My only issue is what seems like low results…

date client p10 p25 p50 p75 p90
2020_05_01 mobile 0.03 0.08 0.62 7.02 38.08
2020_05_01 desktop 0.03 0.08 0.83 8.41 46.73

Are GIFs really vanishing like this? I just keep thinking of the animation being added to sites, but then - maybe they’ve all been converted to video already? Maybe they have.
Might also check the boards for this other request, but: can I split the GIF results between 87a/89a?

Thanks!

1 Like
2

try bytesGif > 1000 to avoid the tracking GIFs.

2 Likes