FlowsBook a callSign inCreate free accountSign up
Roads and traffic

How to export traffic speed data to a spreadsheet

Get speeds, free-flow speeds and jam factors for your places into Google Sheets, Excel or a CSV, one row per reading, so you can chart them and build your own history.

Part of the guide Location intelligence automation

By the xMap team · · 4 min read

To export traffic speed data for your own analysis, read the roads around each place on a schedule and write every reading as a new row: the place, the time, the current speed, the free-flow speed and the jam factor. Over a few weeks those rows become a history you can chart, filter and share, in Google Sheets, Excel, a CSV file or your own database.

This is for analysts, operations planners, consultants and anyone asked to prove that the roads near a site are slow at certain hours. It explains which columns you get, four ways out of xMap Flows, how to lay the sheet out, a made-up example, the usual mistakes, and a flow that keeps adding rows on its own.

The columns you get

The Traffic Flow block adds the same fields to every place on every run, rounded to one decimal, so the columns never shift between rows.

ColumnExampleNote
speed_kmh27.5Current speed, averaged over the roads measured
free_flow_kmh45.2Speed on those roads when clear
jam_factor3.60 free to 10 standstill
congestion_pct39.2How much slower than free flow
traffic_levelLightThe jam factor in words
traffic_confidence0.940 to 1, how sure the reading is
roads_measured, roads_closed6, 1Road stretches measured and closed
busiest_roadStrandHighest jam factor nearby
traffic_updated2026-10-22T08:00:00ZWhen it was measured, in UTC

The fields Traffic Flow adds to each place. A place with no road measured has empty numbers and says No roads measured.

Four ways to get the data out

  • Google Sheets, row by row. A Google Sheets app block at the end of the flow adds a row for each place on every run. Best for a growing history that others can open. See Google Sheets, Docs and Drive.
  • A file from one run. In a run's panel, See all on any block opens every item it passed on, with Download CSV and Download Excel for the columns you choose. Best for a one-off check.
  • A live feed for Excel or Power BI. A flow's latest results can be published as a CSV or JSON address that Excel and Power BI read on refresh (the latest values, not a history); see Results in Power BI, Looker Studio or Excel.
  • Your own database. Save to my database writes each reading into a PostgreSQL, BigQuery or Snowflake table, for teams with a data warehouse.

Laying out the sheet

Keep one row per place per reading, never one column per day. A long, narrow table is easy to filter and to turn into a pivot or a chart; a wide one breaks as soon as you add a site.

  1. Put the place name and your own site ID first, so rows join back to your other data.
  2. Add the reading time and a local-time column next to it.
  3. Keep speed, free-flow speed, jam factor and congestion together.
  4. Add the confidence and closed-road columns at the end, for filtering out weak readings.
  5. Freeze the header row and never edit it by hand, or the flow may write into the wrong column.

A made-up example: a logistics consultant in Johannesburg

Invented for illustration. A consultant is asked whether a client's three warehouses in Johannesburg suffer more from evening traffic than the client's competitors' sites nearby. She lists six locations and schedules readings at 07:00, 12:00 and 17:00 on weekdays, with every road within 500 m.

After three weeks the sheet holds about 270 rows. A pivot of median congestion by site and hour shows the client's two eastern warehouses well above the others at 17:00, while all six look alike at 07:00. She charts the jam factor by hour for each site and attaches the sheet to her report, so the client can check every number.

Mistakes to avoid

  • Exporting one run and calling it a pattern. A single reading is one moment. Collect weeks.
  • Mixing distances. If some rows used adjacent roads and others 1 km, they do not compare. Record the setting, or keep one per sheet.
  • Forgetting the time zone. Group by local hour, not UTC.
  • Keeping low-confidence rows. Filter them out before averaging.
  • Looking for a car count column. There is none: this is speed data. Hourly car counts exist only for UAE roads, through the Road traffic history dataset, which you can export the same way.

A flow that keeps adding rows

Rather than export by hand each week, let an AI employee write the rows as it reads them. It is a plain case of workflow automation doing the dull part of an analysis.

  1. Schedule: several times a day, at the hours you want in the history.
  2. My data: your places from a table.
  3. Traffic Flow: one road distance for all of them.
  4. Calculated field: optional extra columns, such as the ratio of speed to free flow.
  5. A Google Sheets app block: add a row per place.

Run it once by hand to check the columns land where you expect, then deploy it. The template Road speeds around the airport and Road speeds outside my depots already read speeds every hour and are quick to point at a sheet. The AI employee's Team inbox thread notes each day's runs, so a gap in the sheet is easy to explain.

Questions

Can I export past traffic speeds I did not collect?

No. Speeds are live readings, so the history is what your flow collected. Start collecting now; UAE roads are the exception, with hourly counts and speeds since January 2021 in the Road traffic history dataset.

What file formats are available?

CSV and Excel from a run's items, rows in Google Sheets, a CSV or JSON feed for Excel and Power BI, or rows in your own PostgreSQL, BigQuery or Snowflake table.

Why are some speed cells empty?

No road was measured around that place at that moment, so the reading says No roads measured and leaves the numbers empty.

Is the reading time local?

It is in UTC. Convert it to local time before grouping by hour.

Templates that do this

Read next

More on roads and traffic · Every article · Templates

Put it to work

An AI employee in xMap Flows does this on live data, on its own timetable. Start from a template or describe it in plain words.