DEV Community

kavela
kavela

Posted on • Originally published at assetvs.com

Lighting Up 2,232 Dead URLs With One World Bank API Call

#wordpress #seo #database #webdev

On assetvs.com we run a programmatic SEO site comparing investment returns across 16 countries and 5 asset classes (stocks, bonds, real estate, bills, gold). The site has 3,655 URLs in the sitemap. Last week we discovered that 2,232 of them — every currency-pair page — were silently 404ing.

This is the story of how one missing table turned a third of our site dark, and how a single World Bank endpoint brought it all back online in an afternoon.

The architecture (quick recap)

assetvs uses the same virtual-routing pattern we've written about before on the network: no wp_posts, just custom tables and rewrite rules. The relevant tables are:

Table Rows Purpose
wp_ir_countries 16 G7 + EU + ANZ + JP, ISO codes
wp_ir_assets 5 stocks/bonds/real-estate/bills/gold
wp_ir_annual_returns 3,955 nominal returns, country × asset × year
wp_ir_inflation 976 for real-return calculation
wp_ir_comparisons 100 full-period asset-vs-asset summaries
wp_ir_exchange_rates 0 → 976 currency pair history

That last row is the punchline.

The URL patterns

The router (IR_Router) maps slugs onto eight page types:

  • /{country}/ and /{asset}/ — 21 hubs
  • /{country}/{asset}/ — 80 country-asset combos
  • /{country}/{asset_a}-vs-{asset_b}/ — 100 comparisons (alphabetical canonical)
  • /{country}/{asset_a}-vs-{asset_b}/{decade}/ — sliced by 1960s–2010s
  • /{asset}/{country_a}-vs-{country_b}/ — 600 cross-country comparisons
  • /currencies/{a}-{b}/ and /currencies/{a}-{b}/{year}/ — 2,232 currency pages
  • /rankings/{asset}/, /about/, /data/, etc.

Everything renders server-side from the custom tables. No posts, no taxonomies, no theme fight.

The bug, hiding in plain sight

The currency builder (build_currency_pair) had this shape:

$history = $this->get_fx_pair_history($a, $b);
if (empty($history)) {
    return null;
}
// ...render the page
Enter fullscreen mode Exit fullscreen mode

Returning null from the page builder cascades up to a 404. Reasonable defense — but wp_ir_exchange_rates had never been populated. We'd built the schema, written the renderer, generated 2,232 rewrite rules, listed the URLs in the sitemap, and submitted them to Google Search Console for two months. All of them 404.

GSC's coverage report had been flagging "Not found (404)" for weeks, but with 3,655 URLs and zero indexed pages overall (new domain trust issues), nobody had drilled into which URLs were 404 vs which were just unindexed-but-live.

The fix: one API

The World Bank publishes annual official exchange rates as indicator PA.NUS.FCRF, free, no auth:

https://api.worldbank.org/v2/country/{ISO}/indicator/PA.NUS.FCRF?date=1960:2020&format=json
Enter fullscreen mode Exit fullscreen mode

For each of our 16 countries, that's one HTTP call returning 61 annual rates against USD. Sixteen calls total. We wrote a one-shot fill script:

foreach ($countries as $c) {
    $rows = wb_fetch($c->iso2);
    foreach ($rows as $year => $rate) {
        $wpdb->insert('wp_ir_exchange_rates', [
            'country_id' => $c->id,
            'year'       => $year,
            'rate_per_usd' => $rate,
            'yoy_change' => $yoy,
        ]);
    }
}
Enter fullscreen mode Exit fullscreen mode

Result: 976 rows. The currency pair builder derives any (A, B) pair by triangulating through USD — so 16 base currencies give us C(16,2) × 2 directions × 61 years × a few view modes = the 2,232 URLs we'd been promising Google for two months.

Lessons we keep relearning

1. "Sitemap submitted" ≠ "page renders." GSC will happily accept a sitemap of 404s and quietly file them under "Discovered – currently not indexed" or "Not found." There is no alarm. Build a synthetic monitor that hits one URL per page-type weekly and asserts HTTP 200. We'd have caught this in March.

2. Returning null from a builder is a foot-gun. The render path collapses three failure modes into one 404: missing data, missing slug, and genuinely-unknown URL. Now we log a distinct warning when a known-routed slug returns null, so missing tables surface in error logs instead of just the GSC dashboard.

3. New-domain GSC trust dwarfs technical issues. Even with the FX table filled, indexing didn't snap back overnight. We're at zero indexed pages two months in, and lighting up the currency tier is a prerequisite for indexing — but not sufficient. Content depth is next: stock index names per country, country-level macro from World Bank, narrative blocks generated through our LLM pool.

4. Cross-site editorial linking is cheap and free crawl signal. We added a historysaid_for_country($slug) linker that pulls the top 5 inquiries citing the country slug from historysaid.com/wp-json/hs/v1/inquiries/by-country/{slug} (12-hour transient cache) and renders them as a "Further Reading" block. United States gets 8 inquiries, Germany 10, UK 10. Two sites, one network, free internal-link equity.

What's next

  • Fill stock_index_name per country (S&P 500, FTSE 100, DAX, Nikkei…) so each /{country}/stocks/ page has a named anchor instead of generic "stocks."
  • Pull World Bank macro (GDP per capita, inflation regime, currency volatility) into country hubs.
  • Generate narrative paragraphs (~150 words) per country-asset page through our LLM pool — same enrichment pattern we use on HistorySaid's 18,304 indicator pages.

The larger lesson: when 2,232 URLs go dark, it usually isn't an SEO problem. It's a missing INSERT.

Live site: assetvs.com

Top comments (0)