Quantify impact of download tests > 1 Gbps

[cristinaleonr]

• The percentage of download tests from clients (other than the IST) that transfer over 1 GB of data is 9%.

WITH
 counts AS (
 SELECT
   COUNTIF(ARRAY_REVERSE(raw.Download.ServerMeasurements)[
   OFFSET
     (0)].TCPInfo.BytesAcked/1000000 > 1000) AS more,
   COUNTIF(TRUE) AS total
 FROM
   measurement-lab.ndt.ndt7
 WHERE
   date BETWEEN "2023-01-01"
   AND "2023-05-31"
   AND raw.Download IS NOT NULL
   AND ARRAY_LENGTH(raw.Download.ServerMeasurements) > 0
   AND "ist" NOT IN (SELECT metadata.Value FROM UNNEST(raw.Download.ClientMetadata) AS metadata)
)


SELECT
 more / total AS percentage
FROM
 counts

• The percentage of total bytes transferred by these tests is 8.8%.

WITH
 total AS (
 SELECT  ARRAY_REVERSE(raw.Download.ServerMeasurements)[OFFSET(0)].TCPInfo.BytesAcked/1000000 AS totalMB
 FROM
   `measurement-lab.ndt.ndt7`
 WHERE
   date BETWEEN "2023-01-01"
   AND "2023-05-31"
   AND raw.Download IS NOT NULL
   AND ARRAY_LENGTH(raw.Download.ServerMeasurements) > 0
   AND "ist" NOT IN (SELECT metadata.Value FROM UNNEST(raw.Download.ClientMetadata) AS metadata)
),


 grouped AS(
 SELECT
   SUM(IF(totalMB > 1000, totalMB, 0)) AS top,
   SUM(totalMB) AS totalMB
 FROM
   total
)


SELECT
 top/totalMB AS percentage
FROM
 grouped

• Given 15% virtual traffic, if these tests were capped at 1250 MB, the data savings would be 5TB/month, which translates to $517/month.

WITH
total AS (
 SELECT
   id,
   ARRAY_REVERSE(raw.Download.ServerMeasurements)[
 OFFSET
   (0)].TCPInfo.BytesAcked/1000000 AS totalMB
 FROM
   `measurement-lab.ndt.ndt7`
 WHERE
   date BETWEEN "2023-06-01"
   AND "2023-06-30"
   AND raw.Download IS NOT NULL
   AND ARRAY_LENGTH(raw.Download.ServerMeasurements) > 0 
   AND "ist" NOT IN (SELECT metadata.Value FROM UNNEST(raw.Download.ClientMetadata) AS metadata)
),
 
cap AS (
 SELECT
   totalMB,
 IF
   (totalMB < 1250, totalMB, 1250) AS cappedMB
 FROM
   total 
),
 
calc AS (
 SELECT
   SUM(totalMB)/1000000 AS sumTotalTB,
   SUM(cappedMB)/1000000 AS sumCappedTB
 FROM
   cap 
)


SELECT
 sumTotalTB-sumCappedTB AS savingsTB,
 (sumTotalTB-sumCappedTB) * 100 AS savingsDollars,
 (sumTotalTB-sumCappedTB) * 0.15 AS savingsVirtualTB,
 (sumTotalTB-sumCappedTB) * 0.15 * 100 AS savingsVirtualDollars
FROM
 calc

• Assuming an increase of virtual traffic to 50%, if these tests were capped at 1250 MB, the data savings would be 17TB/month, which translates to $1724/month.

WITH
total AS (
 SELECT
   id,
   ARRAY_REVERSE(raw.Download.ServerMeasurements)[
 OFFSET
   (0)].TCPInfo.BytesAcked/1000000 AS totalMB
 FROM
   `measurement-lab.ndt.ndt7`
 WHERE
   date BETWEEN "2023-06-01"
   AND "2023-06-30"
   AND raw.Download IS NOT NULL
   AND ARRAY_LENGTH(raw.Download.ServerMeasurements) > 0 
   AND "ist" NOT IN (SELECT metadata.Value FROM UNNEST(raw.Download.ClientMetadata) AS metadata)
),
 
cap AS (
 SELECT
   totalMB,
 IF
   (totalMB < 1250, totalMB, 1250) AS cappedMB
 FROM
   total 
),
 
calc AS (
 SELECT
   SUM(totalMB)/1000000 AS sumTotalTB,
   SUM(cappedMB)/1000000 AS sumCappedTB
 FROM
   cap 
)


SELECT
 sumTotalTB-sumCappedTB AS savingsTB,
 (sumTotalTB-sumCappedTB) * 100 AS savingsDollars,
 (sumTotalTB-sumCappedTB) * 0.5 AS savingsVirtualTB,
 (sumTotalTB-sumCappedTB) * 0.5 * 100 AS savingsVirtualDollars
FROM
 calc