Data

📊 NEW: Explore FLOTO Data with Interactive SQL Dashboards 📊

We’re excited to announce a major upgrade to our data infrastructure! You can now access FLOTO data directly through our new Apache Superset dashboard. This powerful tool allows you to run your own SQL queries against our data warehouse, create custom views, and export the exact data you need for your analysis.

(🙋‍♂️ Need an Superset account? Request one here.)

Once your account is set up, you can log in to the platform at any time. Inside, you will find pre-built dashboards for visualizing key metrics, as well as the SQL Lab, where you can directly query the data warehouse.

While the Superset dashboard is the most flexible way to access our data, we will continue to update our archive with weekly CSV files, which are available for download in the “Data” tab.

FLOTO Data Warehouse

Our data is published in a SQL warehouse, where we provide cleaned and processed structured data for each measurement type. This facilitates easier analysis by researchers and other data users.

Querying and Analyzing Data

With Superset, you can now join tables and select specific data points directly using Superset’s SQL Lab before exporting. This allows you to create a tailored dataset for your specific research questions.

For example, to combine ping latency from Chicago with Ookla speed test results, you could run a query like this in Superset:

SELECT
  p.device_short_uuid,
  p.measurement_datetime,
  p.meas__ping_latency__chicago_rtt_avg_ms,
  s.meas__ookla__speedtest_ookla_download,
  s.meas__ookla__speedtest_ookla_upload
FROM
  ping p
JOIN
  speed_ookla s ON p.device_short_uuid = s.device_short_uuid
WHERE
  p.measurement_datetime > '2024-01-01';

After running your query, you can export the results as a CSV file and perform further analysis locally using tools like Python’s pandas library.

Available Tables

Below is an overview of each table’s structure. All tables include common columns such as device_short_uuid and measurement_datetime to allow for joining across tables.

  • dev: Contains information about connected devices on the local network.
  • dns_latency: Measures the latency of DNS queries.
  • hops: Provides information about the number of network hops to a target.
  • ip: Contains IP address information for the device.
  • lml (last-mile latency): Measures various aspects of last-mile network performance.
  • ping: Measures network latency to specific targets across various global locations and popular websites.
  • speed_ookla: Contains results from Ookla speed tests.
  • speed_ndt7: Contains results from NDT7 (Network Diagnostic Tool) speed tests.

Table Structures

dev

Provides information about devices connected to the local network of the FLOTO device.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__connected_devices_arp__devices_1day: Number of devices connected in the last 24 hours
  • meas__connected_devices_arp__devices_1week: Number of devices connected in the last week
  • meas__connected_devices_arp__devices_active: Number of currently active devices
  • meas__connected_devices_arp__devices_total: Total number of unique devices ever connected

dns_latency

Contains measurements of DNS query latency.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__dns_latency__dns_query_avg_ms: Average DNS query latency in milliseconds
  • meas__dns_latency__dns_query_max_ms: Maximum DNS query latency in milliseconds

hops

Provides information about the number of network hops to reach a specific target (Google).

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__hops_to_target__hops_to_google: Number of hops to reach Google’s servers

ip

Contains IP address information for the FLOTO device.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__ipquery__ipv4: IPv4 address of the FLOTO device

lml (last-mile latency)

Provides detailed measurements of last-mile network performance to Cloudflare DNS servers.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__lm_rtt__cloudflare_dns_last_mile_ping_packet_loss_pct: Packet loss percentage
  • meas__lm_rtt__cloudflare_dns_last_mile_ping_rtt_avg_ms: Average round-trip time in ms
  • meas__lm_rtt__cloudflare_dns_last_mile_ping_rtt_max_ms: Maximum round-trip time in ms
  • meas__lm_rtt__cloudflare_dns_last_mile_ping_rtt_min_ms: Minimum round-trip time in ms
  • meas__lm_rtt__cloudflare_dns_last_mile_tr_rtt_median_ms: Median traceroute round-trip time in ms

ping

Contains latency test results to global locations and popular websites.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__ping_latency__[location]_packet_loss_pct: Packet loss percentage
  • meas__ping_latency__[location]_rtt_avg_ms: Average round-trip time in ms
  • meas__ping_latency__[location]_rtt_max_ms: Maximum round-trip time in ms
  • meas__ping_latency__[location]_rtt_min_ms: Minimum round-trip time in ms
  • Locations include: atlanta, chicago, denver, hong_kong, johannesburg, paris, sao_paulo, seattle, stockholm, sydney, tunis, washington_dc, amazon, facebook, google, suntimes, tribune, uchicago, wikipedia, youtube

speed_ookla

Contains results from Ookla speed tests.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__ookla__speedtest_ookla_download: Download speed in Mbps
  • meas__ookla__speedtest_ookla_upload: Upload speed in Mbps
  • meas__ookla__speedtest_ookla_latency: Latency in ms
  • meas__ookla__speedtest_ookla_jitter: Jitter in ms
  • meas__ookla__speedtest_ookla_pktloss2: Packet loss percentage
  • meas__ookla__speedtest_ookla_server_id: Ookla server ID
  • meas__ookla__speedtest_ookla_server_name: Name of the Ookla server
  • meas__ookla__speedtest_ookla_server_host: Hostname of the Ookla server
  • meas__test_bytes_consumed: Total bytes consumed during the test

speed_ndt7

Contains results from NDT7 (Network Diagnostic Tool) speed tests.

  • device_short_uuid: Unique identifier for the FLOTO device
  • measurement_datetime: Timestamp of the measurement
  • meas__ndt7__speedtest_ndt7_download: Download speed in Mbps
  • meas__ndt7__speedtest_ndt7_upload: Upload speed in Mbps
  • meas__ndt7__speedtest_ndt7_downloadlatency: Download latency in ms
  • meas__ndt7__speedtest_ndt7_downloadretrans: Download retransmission rate
  • meas__ndt7__speedtest_ndt7_server: Hostname of the NDT7 server
  • meas__ndt7__speedtest_ndt7_server_ip: IP address of the NDT7 server
  • meas__test_bytes_consumed: Total bytes consumed during the test

Device Metadata

FLOTO provides rich metadata about devices through its API endpoint (https://portal.floto.science/api/devices). This data can be joined with the performance data in your analysis using the device UUID. Key fields include:

  • device_name: The name assigned to the device
  • latitude and longitude: The geographical location of the device
  • is_online: Whether the device is currently online
  • os_version: The OS version running on the device
  • cpu_temp: The CPU temperature of the device
  • memory_usage and memory_total: Memory usage statistics
  • storage_usage and storage_total: Storage usage statistics

By combining performance data with device metadata, you can conduct more comprehensive analyses, controlling for factors like device location, hardware specifications, and operational status.

Accessing the Data

The primary way to access data is through our Superset SQL Lab. For users who prefer pre-packaged files, weekly CSV snapshots can be downloaded from the FLOTO Data Portal.

For any questions about the structured data or for access to historical data, please contact us at contact@floto.science.