DEV Community

Oxymore Services
Oxymore Services

Posted on

How We Built 1,200 SEO Landing Pages for a Local Plumber in 4 Hours (Programmatic SEO + Supabase)

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", ...}
);
Enter fullscreen mode Exit fullscreen mode

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} />;
}
Enter fullscreen mode Exit fullscreen mode

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 }
};
Enter fullscreen mode Exit fullscreen mode

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)