Ten public datasets, cleaned, checked and queryable.

01Context

Real data I can show.

The numbers from my own business stay private. To show how I work with data, I built a dashboard on real public data: ten analyses, each with its question, source, period, licence and query. Every chart carries the same label: public data, not my business.

02The pipeline

From the source to your browser.

Each analysis runs the same way, so every number on the page can be traced back to where it came from.

1. 01SourcesEleven public sources, each with its licence and period recorded.
2. 02CleanA Python script downloads and cleans each source, and the cleaning steps are written down with the data.
3. 03VerifyThe numbers are cross-checked against the primary source.
4. 04StoreSmall JSON files for the charts, parquet tables for SQL.
5. 05ChartOne clear chart per question, with its source, period and caveat.
6. 06QueryLive SQL on the tables, in your browser, with DuckDB-WASM.

03One real query

Which lead sources close?

This is the query behind the sales analysis, exactly as it runs on the published table, with its output. Public data, not my business.

SELECT origin, count(*) AS leads,
       count(*) FILTER (WHERE won) AS won,
       round(100.0 * count(*) FILTER (WHERE won)
             / count(*), 1) AS conversion_pct
FROM seller_leads
WHERE first_contact_date >= DATE '2018-01-01'
GROUP BY origin
ORDER BY conversion_pct DESC

Olist, Marketing Funnel by Olist (Kaggle) · CC BY-NC-SA 4.0 · 8,000 anonymized marketing qualified leads, June 2017 to May 2018 · public data, not my business · full analysis in the dashboard

04Verification

Checked against the primary source.

The four newest analyses were each re-run from a fresh download into an empty folder, then checked against the publisher's own files. No number was typed by hand.

• US e-commerce share · CensusAll 107 quarters of both Census workbooks equal FRED, with 0 mismatches, and the latest five quarters match the Census release PDF.
• Retail inventories-to-sales ratio · Census MTISAll 415 months of both ratios equal the Census workbook, not only FRED's copy.
• Port of LA container volumeLoaded and empty imports and exports add up to the port's own total in all 140 months.
• Global supply chain pressure · NY FedThe download equals the latest column of the NY Fed interactive data for all 345 months.
• Online retailer registrations · City of LACounts by ZIP from the API equal a local count of all 1,951 downloaded rows.

When a source has a problem, I write it down instead of smoothing it over. The Port of LA's November 2020 total cell is malformed, so the file uses the sum of its parts, which matches the port's 2020 annual total.

05Sources

Eleven sources, with their licences.

06What I learned

Public data moves.

Write the check down, next to the number.

Public data gets revised. Census revised its monthly retail estimates after the release I built from, and the NY Fed re-estimates the whole supply chain index every month, so each file records its release and vintage. That taught me to treat a number as finished only when I can show where it came from.

07Tools

What it runs on.

• PythonDownload, cleaning and check scripts, one per analysis.
• SQLThe method and sample queries for every analysis.
• JSON and parquetJSON for the charts, parquet tables for SQL.
• EChartsThe interactive charts in the dashboard.
• DuckDB-WASMLive SQL in the browser, nothing to install.