The data pipeline: Extract, Transform, Load, Check

DOSM Domestic Tourism Survey

File download from OpenDOSM

WhyVisitors, spending and where tourists come from, by state.

data.gov.my

File download

WhyPopulation, income, poverty, basic amenities, GDP.

Tourism Malaysia

The public dashboard's own data API

WhyHotels, rooms and how full they are.

OpenStreetMap

Overpass API, one query per kind of place

WhyAttractions, airports, rail stations, hotels, eateries.

Travel web pages

Exa search API, 295 searches

WhyWhat travellers write, in Malay, English and Chinese.

Video comments

YouTube Data API

WhyShort first-hand reactions to each state.

Wikivoyage and rainfall

MediaWiki API and Open-Meteo API

WhyReal places and ten years of rain for the trip planner.

Find rows by their label

Search for the row that says "Johor" instead of trusting cell B12.

WhyThe sheets are laid out for printing, so positions shift from file to file.

Dashes stay empty

"-" and "n.a." become an empty cell, never 0.

WhyA dash means "not published". A zero would mean "nobody came".

One code per state

Every spelling is mapped to one 3-letter code.

Why"Penang" and "Pulau Pinang" must be the same state, or tables from different agencies cannot be matched.

Remove repeated state-years

Two DOSM editions cover the same years. One copy is kept.

WhyCounting a state twice in one year would double its visitors.

Keep only the rows we need

Drop the rows split by sex, age group or sub-total; keep the "everyone" row.

WhyEach state and year must appear exactly once, or the join would multiply rows.

Map points inside a state

Each point is placed in the state whose border it falls inside; repeats are dropped.

WhyA point out at sea or listed twice cannot be counted for a state.

Skip review sites

Pages from TripAdvisor, Google Maps, Facebook, Instagram and TikTok are not read.

WhyTheir terms do not allow reviews to be copied.

Drop junk paragraphs

Menus, cookie notices, "subscribe", headings and one-liners under 40 characters.

WhyMost of a web page is not travel writing.

Drop very short comments

Comments under 60 characters.

Why"Nice video!" says nothing about a place.

Remove personal details

Links 1,394, e-mails 317, usernames 877, phone numbers 832.

WhyPrivacy. We need what was said, not who said it.

Cap long pages

At most 6 passages are taken from one page.

WhyOne very long blog must not drown out a whole state.

Remove exact duplicates

Same text after ignoring capitals, spaces and punctuation.

WhyA repeated post would count one opinion many times.

Remove near-duplicates

Texts sharing at least 70% of their 5-word runs with an earlier text.

WhyThe same article copied to a second site with a word changed.

AI filter: is this a real trip?

Gemini 2.5 Flash Lite reads each text and answers from fixed lists.

WhyAdverts, news and "I want to go one day" are not experiences.

Ask a second time

The same yes/no question again, more strictly. Kept only on two yeses.

WhyOne pass let too much chatter through. Fewer posts, but cleaner.

Wrong-state check

If the place the AI found names a different state, the post is not counted.

WhyA search for Sabah that returns a post about Kuala Lumpur must not score Sabah.

Steady small samples

A topic with few mentions is pulled towards the national average.

WhyTwelve posts should not decide whether a state is called safe.

Save as clean tables

Each cleaned source is stored as a typed Parquet table, with a data dictionary.

WhyFixed column types, so a number can never quietly turn into text.

Match by state code and year

Every clean table is looked up for each of the 16 states. No state is ever dropped.

WhyOne table means every score is worked out from the same numbers.

Carry the latest year forward

If a year is not published, use the most recent earlier year and record which year it was.

WhyDOSM's state spending stops at 2023. We say so on screen instead of guessing.

Never fill a gap

A value that was never published stays empty.

WhyAn invented number looks as real as a true one. Almost all are 2021 detail DOSM did not publish.

Work out the scores

Gini, Opportunity, Bottleneck Diagnoser, Room to grow, Simulator.

WhyAnyone can check by hand why a state ranks where it does. Steps are on the Methodology page.

Ship small files

The final table and scores are exported as JSON with the website. No database.

WhyNothing to log in to, nothing to go down.

Range rules

Percentages must be 0-100, counts cannot be negative, nights stayed must be believable.

WhyA value outside its range means we read the file wrongly. The pipeline stops if one appears.

Every state, every year

Each year must hold exactly the 16 states, once each.

WhyA missing or doubled state would bend every share and every ranking.

Totals must match the publisher

Our 16 states added up = DOSM's national total; tourists + day-trippers = visitors; state rooms = Tourism Malaysia's total.

WhyIt proves nothing was lost or double-counted. 2025: 290,064.7 = 290,064.7 thousand visitors.

Compare the two editions

Where both editions give the same state and year, do the numbers agree?

WhyThey agree everywhere, so dropping the repeated copy loses nothing.

Outlier scan

Flag any yearly jump in visitors far from the usual change (robust z-score above 3.5).

WhyAll are 2022: travel bouncing back after the pandemic. Real, so kept unchanged. A flag in a normal year would point to an error.

Automatic tests

53 on the data and the maths, 57 on the website.

WhyIf one fails, nothing is published.

Two calculators must agree

The website recalculates every score live; its answers are compared with Python's.

WhySo the screen can never drift from the pipeline.

Shake the weights

Re-rank the states 1,000 times with random weights.

WhyThe ranking must not depend on the weights we happened to choose.