Last month we shipped a programmatic SEO engine for a Los Angeles plumbing client. The brief: rank for "[service] in [neighborhood]" across every neighborhood they serve. The cost of hand-writing each page would have been prohibitive. The total build time end-to-end: 4 hours and 11 minutes. 60 days later, it's indexed 1,089 of 1,200 pages and ranks on page 1 for 183 queries.
Here's the full recipe.
More context on the broader SMB website landscape: Small Business Website Statistics 2026 — includes the pSEO benchmarks that informed this build.
The two tables
Supabase schema is dead simple:
create table services (
id uuid primary key default gen_random_uuid(),
slug text unique not null, -- "water-heater-repair"
display_name text not null, -- "Water Heater Repair"
short_description text,
avg_cost_range text, -- "$150 - $450"
common_issues text[] -- {"no hot water", "leaking", ...}
);
create table neighborhoods (
id uuid primary key default gen_random_uuid(),
slug text unique not null, -- "silver-lake"
display_name text not null, -- "Silver Lake"
zip_codes text[], -- {"90026", "90039"}
population int,
local_landmarks text[] -- {"Silver Lake Reservoir", ...}
);
12 services × 100 neighborhoods = 1,200 combinations.
The Next.js route
Single dynamic route handles all 1,200:
// app/[service]/[neighborhood]/page.tsx
import { createClient } from '@/lib/supabase';
import { notFound } from 'next/navigation';
export async function generateStaticParams() {
const supabase = createClient();
const { data } = await supabase.rpc('get_all_service_neighborhood_pairs');
return data.map(({ service, neighborhood }) => ({ service, neighborhood }));
}
export async function generateMetadata({ params }) {
const { service, neighborhood } = params;
const data = await fetchPageData(service, neighborhood);
return {
title: `${data.service.display_name} in ${data.neighborhood.display_name} | Acme Plumbing`,
description: `Licensed ${data.service.display_name.toLowerCase()} in ${data.neighborhood.display_name}. Same-day service. Call now.`,
alternates: { canonical: `/${service}/${neighborhood}` }
};
}
export default async function Page({ params }) {
const data = await fetchPageData(params.service, params.neighborhood);
if (!data) notFound();
return <ServiceNeighborhoodTemplate {...data} />;
}
The template that doesn't look templated
This is where most pSEO goes wrong. Template content → duplicate-content penalty → Google deindexes 80% of your pages.
Our approach: 6 content blocks, each with 3-5 variations, rotated by hash of (service_slug + neighborhood_slug). That means:
- Hero intro paragraph: 4 variants
- "Common issues we fix" lead-in: 5 variants
- Pricing explainer: 3 variants
- Testimonial callout: pulled from real reviews, randomly selected from reviews tagged for that neighborhood
- "Why [neighborhood]" paragraph: generated from neighborhood table — mentions population, landmarks, zip codes
- FAQ: 5 slots, questions pulled from service.common_issues with answers templated per-service
With 4 × 5 × 3 × many × many × 5 permutation possibilities, no two pages look alike structurally — even though the backend is 100% deterministic.
The JSON-LD schema
Every page gets fresh LocalBusiness + Service + FAQPage schema:
const schema = {
'@context': 'https://schema.org',
'@type': 'Service',
serviceType: service.display_name,
provider: {
'@type': 'LocalBusiness',
name: 'Acme Plumbing',
areaServed: {
'@type': 'City',
name: neighborhood.display_name,
containedInPlace: { '@type': 'City', name: 'Los Angeles' }
}
},
offers: { '@type': 'Offer', priceRange: service.avg_cost_range }
};
Submission + indexing
Generated a dynamic sitemap.xml via Next.js route handler, submitted to Google Search Console. Added IndexNow API ping for Bing. Within 48 hours Google had indexed 340 pages; within 60 days it was 1,089 of 1,200.
The results (60 days post-launch)
- 1,089 pages indexed out of 1,200 (90.75%)
- 183 queries ranking on page 1 for "[service] [neighborhood]" patterns
- 2,840 organic clicks from the new pages (previously: 0, because the pages didn't exist)
- $47,200 attributable revenue per client's CRM tracking
- Zero manual-review penalties so far
What I'd want feedback on
- Anyone running pSEO at this scale — what's your dedup strategy when two services have overlapping content (e.g., "drain cleaning" and "drain unclogging" are different service pages but the content vectors look similar)?
- How are you handling schema versioning when Google changes its LocalBusiness spec?
- For those using LLM-generated variations instead of templated ones — are you seeing better or worse indexing rates?
About us: Oxymore Services — Los Angeles-based web dev, SEO and Google Ads agency. We build Next.js + Supabase sites that ship fast and rank. See our cost calculator for programmatic SEO pricing. info@oxymoreservices.com
Top comments (0)