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
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.
| Column | Example | Note |
|---|---|---|
| speed_kmh | 27.5 | Current speed, averaged over the roads measured |
| free_flow_kmh | 45.2 | Speed on those roads when clear |
| jam_factor | 3.6 | 0 free to 10 standstill |
| congestion_pct | 39.2 | How much slower than free flow |
| traffic_level | Light | The jam factor in words |
| traffic_confidence | 0.94 | 0 to 1, how sure the reading is |
| roads_measured, roads_closed | 6, 1 | Road stretches measured and closed |
| busiest_road | Strand | Highest jam factor nearby |
| traffic_updated | 2026-10-22T08:00:00Z | When 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.
- Put the place name and your own site ID first, so rows join back to your other data.
- Add the reading time and a local-time column next to it.
- Keep speed, free-flow speed, jam factor and congestion together.
- Add the confidence and closed-road columns at the end, for filtering out weak readings.
- 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.
- Schedule: several times a day, at the hours you want in the history.
- My data: your places from a table.
- Traffic Flow: one road distance for all of them.
- Calculated field: optional extra columns, such as the ratio of speed to free flow.
- 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.