Web Development

Building 500+ Location Pages with AI-Generated Content

Building 790 location pages with AI content, database schema, and SEO implementation.

Executive Summary

We built a system that generates 790 unique location pages for Jordan James Media—79 standalone location pages plus 711 service-location combinations. Each page has unique, AI-generated content that passes Google's quality guidelines while maintaining consistent AI-first messaging.

Series Note: This is Part 2 of our Site Infrastructure series. In Part 1, we covered the hub & spoke architecture strategy. Here, we implement the locations hub at scale. See it in action on our locations hub or explore pages like SEO Sydney.

Total Pages: 790 Locations: 79 Sydney suburbs and regions Services: 9 Build Time: ~4 hours development, ~3 minutes to generate Content: 100% unique per page

The Goal: Local SEO at Scale

Every suburb in Sydney is a potential customer. Someone searching "web design Parramatta" or "SEO services Bondi" should find us. But creating unique, valuable content for hundreds of locations manually? Impossible.

The challenge: Google explicitly penalizes "doorway pages"—thin content pages that exist only to rank for location keywords. We needed pages that are genuinely useful, not just keyword-stuffed templates.

We needed a system that could generate 500+ location pages with unique content, proper SEO, and maintainable code. Here's how we built it.

The Architecture

Database Schema

Two main tables drive the entire system:

Locations Table:

export const locations = pgTable("locations", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }).notNull(),
  state: varchar("state", { length: 50 }).notNull(),
  region: varchar("region", { length: 255 }),
  description: text("description"),
  population: integer("population"),
  latitude: real("latitude"),
  longitude: real("longitude"),
  targetKeywords: text("target_keywords").array(),
  nearbySuburbs: text("nearby_suburbs").array(),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

Service-Locations Table:

export const serviceLocations = pgTable("service_locations", {
  id: serial("id").primaryKey(),
  serviceSlug: varchar("service_slug", { length: 255 }).notNull(),
  locationSlug: varchar("location_slug", { length: 255 }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  metaDescription: text("meta_description"),
  heroHeadline: text("hero_headline"),
  heroSubheadline: text("hero_subheadline"),
  mainContent: text("main_content"),
  benefits: text("benefits").array(),
  faqs: jsonb("faqs"),
  targetKeywords: text("target_keywords").array(),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

The Math

The system creates a matrix of pages:

79 locations (Sydney suburbs + regions)
9 services (Web Design, SEO, Google Ads, Content Marketing, Social Media, etc.)
79 × 9 = 711 service-location pages
Plus 79 standalone location pages
Total: 790 unique pages

Each page has unique content. No templates. No duplicate paragraphs.

Google's Guidelines: What We Had to Avoid

Before diving into the implementation, it's critical to understand what Google penalizes:

Doorway Pages (BAD)

Google defines doorway pages as "sites or pages created to rank for specific, similar search queries." They're characterized by:

Multiple pages targeting different cities with essentially the same content
Pages that funnel users to the same destination
Pages with no unique value beyond keyword targeting

Example of what NOT to do:

"We offer web design in Parramatta. Contact us for web design in Parramatta today!"
"We offer web design in Bondi. Contact us for web design in Bondi today!"
"We offer web design in Chatswood. Contact us for web design in Chatswood today!"

This is thin content. Google sees through it. Users bounce. Rankings tank.

What Google WANTS

Unique, helpful content for each location
Local relevance - content that actually relates to the specific area
User value - information that helps someone in that location
Minimum 800-1200 words of unique content per page

Content Generation Strategy

City-Specific Insights

The seed script contains detailed insights for major cities:

const cityInsights: Record<string, CityInsight> = {
  sydney: {
    businessEnvironment: "Australia's largest business hub with over 500,000 registered businesses",
    digitalLandscape: "Highly competitive digital market with sophisticated consumers",
    localChallenges: "Standing out in a crowded market requires cutting-edge strategies",
    opportunities: "Access to Australia's largest consumer base and business network",
    industries: ["Finance", "Technology", "Tourism", "Retail", "Professional Services"],
  },
  parramatta: {
    businessEnvironment: "Western Sydney's CBD and fastest-growing business district",
    digitalLandscape: "Rapidly digitizing business community with growing online presence",
    localChallenges: "Competing with Sydney CBD while serving local Western Sydney market",
    opportunities: "Gateway to Western Sydney's 2.5 million residents",
    industries: ["Healthcare", "Education", "Government", "Retail", "Construction"],
  },
  // ... 6 more cities with detailed insights
};

Service-Specific Content Generators

Each service has its own content generation function that creates unique paragraphs based on location attributes:

Web Design Content Generator:

function generateWebDesignContent(location: string, cityData: CityInsight): string {
  return `
## AI Website Development in ${location}

${location} businesses need websites that do more than look good—they need sites that convert visitors into customers. Our AI-powered web development approach creates websites that continuously optimize themselves based on user behavior.

### Why ${location} Businesses Choose AI-First Web Design

${cityData.businessEnvironment}. In this environment, a static website isn't enough. You need a digital presence that:

- **Adapts to user behavior** - AI analyzes how visitors interact and optimizes in real-time
- **Loads instantly** - Critical for ${location}'s mobile-first users
- **Converts visitors** - Smart CTAs and forms that improve over time
- **Ranks locally** - Built-in SEO for "${location} + your service" searches

### The ${location} Digital Landscape

${cityData.digitalLandscape}. Our AI-powered websites give you an edge by:

1. **Continuous improvement** - Unlike traditional sites that stay static, ours get better every day
2. **No WordPress limitations** - Custom-built on modern tech stacks
3. **Future-proof architecture** - As AI improves, your website improves automatically

### Industries We Serve in ${location}

${cityData.industries.map(i => `- ${i}`).join('\n')}

Each industry has unique needs. Our AI learns your specific customer behavior patterns and optimizes accordingly.
  `;
}

SEO Content Generator:

function generateSEOContent(location: string, cityData: CityInsight): string {
  return `
## AI-Powered SEO Services in ${location}

Ranking in ${location} requires more than traditional SEO tactics. ${cityData.localChallenges}. Our AI-powered SEO approach analyzes thousands of ranking factors and continuously adapts your strategy.

### Local SEO for ${location} Businesses

When someone searches "best [your service] ${location}", you need to appear. Our AI SEO system:

- **Optimizes Google Business Profile** - Critical for local pack rankings
- **Builds local citations** - Consistent NAP across ${location} directories
- **Generates location-specific content** - Relevant to ${location} searchers
- **Monitors competitor movements** - AI tracks what's working in ${location}

### Why Traditional SEO Fails in ${location}

${cityData.digitalLandscape}. Static SEO strategies can't keep up with:

- Algorithm updates (Google makes thousands per year)
- Competitor movements
- Changing user behavior
- New search features

Our AI monitors all of this 24/7 and adjusts your strategy automatically.

### ${location} SEO Opportunities

${cityData.opportunities}. We help you capture this market through:

1. **Hyper-local keyword targeting** - Not just "${location}" but specific suburbs and neighborhoods
2. **Local link building** - Relationships with ${location} businesses and organizations
3. **Review management** - Building social proof from ${location} customers
4. **Local content strategy** - Content that resonates with ${location} audiences
  `;
}

Google Ads Content Generator:

function generateGoogleAdsContent(location: string, cityData: CityInsight): string {
  return `
## AI Google Ads Management in ${location}

Google Ads in ${location} is expensive. ${cityData.localChallenges}. Without AI optimization, you're leaving money on the table—or worse, burning it.

### The ${location} Google Ads Challenge

${cityData.businessEnvironment}. This means:

- **High competition** - Many businesses bidding on the same keywords
- **Expensive clicks** - CPCs in ${location} can exceed $50 for competitive terms
- **Sophisticated competitors** - Agencies and in-house teams optimizing constantly

### How AI Changes the Game

Our AI-powered Google Ads management:

- **Bid optimization** - AI adjusts bids thousands of times per day based on conversion likelihood
- **Ad copy testing** - Continuously tests headlines and descriptions
- **Audience refinement** - Identifies your best customers in ${location}
- **Budget allocation** - Shifts spend to highest-performing campaigns automatically

### ${location}-Specific Targeting

We don't just target "${location}". Our AI identifies:

- **High-value suburbs** within ${location}
- **Time-of-day patterns** for ${location} searchers
- **Device preferences** of your ${location} audience
- **Competitor gaps** in the ${location} market

### Industries We Serve in ${location}

${cityData.industries.map(i => `- ${i}`).join('\n')}

Each industry has different cost-per-click benchmarks and conversion patterns. Our AI learns yours.
  `;
}

Keyword Generation

Target keywords are generated per location with multiple variations:

function generateTargetKeywords(location: string, service: string): string[] {
  const serviceVariations = {
    'web-design': ['web design', 'website design', 'web development', 'website development'],
    'seo': ['SEO', 'search engine optimization', 'SEO services', 'SEO agency'],
    'google-ads': ['Google Ads', 'PPC', 'paid search', 'Google advertising'],
  };
  
  const variations = serviceVariations[service] || [service];
  const keywords: string[] = [];
  
  variations.forEach(v => {
    keywords.push(
      `${v} ${location}`,
      `${location} ${v}`,
      `${v} agency ${location}`,
      `${v} company ${location}`,
      `best ${v} ${location}`,
      `${v} services ${location}`,
      `${v} near ${location}`,
      `affordable ${v} ${location}`,
      `professional ${v} ${location}`,
    );
  });
  
  return keywords;
}

Dynamic Routing

Location Pages

Route: /locations/{slug}

Each location page shows:

Hero section with location name, region, and AI-first messaging
Services grid - All 9 services available in that area
Local business benefits - Why businesses in this area choose us
Nearby suburbs - Internal links to related location pages
Contact CTA - With location context pre-filled

Example URL: /locations/parramatta

Service-Location Pages

Route: /locations/{location}/{service}

Each combination page shows:

Service-specific hero - Tailored headline for that service + location
Location-customized content - 800+ words unique to this combination
Benefits list - Service benefits relevant to the location
FAQs - Location-specific frequently asked questions
Pricing section - If applicable for that service
Related services - Other services in the same location
Nearby locations - Same service in nearby suburbs

Example URL: /locations/parramatta/web-design

SEO Implementation

Structured Data (Schema Markup)

Every page includes three types of schema:

1. LocalBusiness Schema:

{
  "@context": "https://schema.org",
  "@type": "ProfessionalService",
  "name": "Jordan James Media - Parramatta",
  "description": "AI-powered digital marketing agency serving Parramatta businesses",
  "url": "https://jordanjamesmedia.com/locations/parramatta",
  "areaServed": {
    "@type": "City",
    "name": "Parramatta",
    "containedInPlace": {
      "@type": "State",
      "name": "New South Wales"
    }
  },
  "serviceArea": {
    "@type": "GeoCircle",
    "geoMidpoint": {
      "@type": "GeoCoordinates",
      "latitude": -33.8151,
      "longitude": 151.0011
    },
    "geoRadius": "20000"
  }
}

2. Service Schema:

{
  "@context": "https://schema.org",
  "@type": "Service",
  "serviceType": "AI Website Development",
  "provider": {
    "@type": "ProfessionalService",
    "name": "Jordan James Media"
  },
  "areaServed": {
    "@type": "City",
    "name": "Parramatta"
  },
  "description": "AI-powered website development for Parramatta businesses"
}

3. BreadcrumbList Schema:

{
  "@context": "https://schema.org",
  "@type": "BreadcrumbList",
  "itemListElement": [
    { "@type": "ListItem", "position": 1, "name": "Home", "item": "https://jordanjamesmedia.com" },
    { "@type": "ListItem", "position": 2, "name": "Locations", "item": "https://jordanjamesmedia.com/locations" },
    { "@type": "ListItem", "position": 3, "name": "Parramatta", "item": "https://jordanjamesmedia.com/locations/parramatta" },
    { "@type": "ListItem", "position": 4, "name": "Web Design", "item": "https://jordanjamesmedia.com/locations/parramatta/web-design" }
  ]
}

Meta Tags

Dynamic generation based on page type:

// For service-location pages
const title = `AI ${serviceName} ${locationName} | AI-Powered Marketing Agency`;
const description = `Professional AI-powered ${serviceName.toLowerCase()} for ${locationName} businesses. ${cityData.opportunities}. No WordPress. No legacy systems. Just results.`;

// For location pages
const title = `Digital Marketing Agency ${locationName} | AI-Powered Services`;
const description = `AI-powered digital marketing services for ${locationName} businesses. Web design, SEO, Google Ads, and more. Future-proof your business with AI-first marketing.`;

Internal Linking Strategy

Each page links to:

Parent location page - Service-location pages link up to their location
Related services - Other services in the same location
Nearby suburbs - Same service in geographically close areas
Main service page - The canonical service page

This creates a strong internal link structure:

Home
├── Locations
│   ├── Parramatta
│   │   ├── Web Design
│   │   ├── SEO
│   │   └── Google Ads
│   ├── Bondi
│   │   ├── Web Design
│   │   ├── SEO
│   │   └── Google Ads
│   └── ...
└── Services
    ├── Web Design (links to all location variants)
    ├── SEO (links to all location variants)
    └── ...

The AI-First Messaging

Every location page reinforces our positioning:

Service Names

All services are prefixed with "AI":

AI Website Development
AI Search Engine Optimization (AI SEO)
AI Google Ads Management
AI Content Marketing
AI Social Media Marketing

Key Phrases Used Throughout

"Exponentially better" - AI improves over time
"Continuous improvement" - Not static like traditional agencies
"Future-proof your business" - AI technology advances benefit clients
"No WordPress" - We don't use legacy systems
"No legacy systems" - Modern tech stack only
"Built on an AI stack" - Technology differentiator

Consistent But Not Repetitive

The messaging is consistent across all 790 pages, but the context changes:

In Parramatta: "Parramatta businesses are future-proofing with AI-powered marketing"
In Bondi: "Bondi's competitive market demands AI-powered marketing that evolves"
In Chatswood: "Chatswood businesses gain an edge with AI-first digital strategies"

Database Setup

Neon PostgreSQL

We use Neon's serverless PostgreSQL:

Region: AWS Sydney (ap-southeast-2) - Low latency for Australian users
Version: PostgreSQL 17
Features: Auto-scaling, branching for development, instant provisioning
Cost: Pay-per-use, scales to zero when idle

Drizzle ORM

Schema-first approach with full TypeScript support:

import { pgTable, serial, varchar, text, integer, real, boolean, timestamp, jsonb } from 'drizzle-orm/pg-core';

export const locations = pgTable("locations", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }).notNull(),
  state: varchar("state", { length: 50 }).notNull(),
  region: varchar("region", { length: 255 }),
  description: text("description"),
  population: integer("population"),
  latitude: real("latitude"),
  longitude: real("longitude"),
  targetKeywords: text("target_keywords").array(),
  nearbySuburbs: text("nearby_suburbs").array(),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

The Seed Script

The seed-locations.ts script is the heart of the system:

npx tsx scripts/seed-locations.ts

What it does:

Clears existing data - Truncates location and service-location tables
Generates 79 locations - Each with unique description, keywords, nearby suburbs
Creates 711 service-location combinations - Each with 800+ words of unique content
Validates data - Ensures no duplicates, all required fields present
Logs progress - Shows what's being created in real-time

Output:

🌍 Seeding locations...
  ✅ Created: Sydney CBD
  ✅ Created: Parramatta
  ✅ Created: Bondi
  ... (79 total)

🔗 Seeding service-locations...
  ✅ Created: Web Design - Sydney CBD
  ✅ Created: SEO - Sydney CBD
  ✅ Created: Google Ads - Sydney CBD
  ... (711 total)

✅ Seeding complete!
   Locations: 79
   Service-Locations: 711
   Total Pages: 790

Performance Considerations

Data Persistence

With Neon PostgreSQL:

Data persists across server restarts
No re-seeding needed unless content updates required
Branch-based development - Test changes without affecting production

Page Load Performance

Location pages achieve 95+ Lighthouse scores through:

Server-side rendering - Content ready on first paint
Optimized images - WebP format, lazy loading
Minimal JavaScript - Only what's needed for interactivity
CDN caching - Static assets cached at edge

Incremental Updates

New locations can be added without regenerating everything:

Add new location to seed script
Run seed script (only inserts new records)
New pages automatically available

Results

Metric	Value
Total Pages	790
Unique Content	100%
Words per Page	800-1500
Build Time	~3 minutes
Lighthouse Score	95+
Development Time	~4 hours

Page Breakdown

Type	Count
Location Pages	79
Web Design + Location	79
SEO + Location	79
Google Ads + Location	79
Content Marketing + Location	79
Social Media + Location	79
Other Services + Location	316
Total	790

What's Next

The foundation is set. Future improvements:

Expand beyond Sydney - Melbourne, Brisbane, Perth, Adelaide
Dynamic pricing display - Location-based pricing tiers
Local testimonials - Per-location social proof from real clients
Competitor analysis - Location-specific positioning based on local competitors
Performance tracking - Per-location analytics and conversion tracking
A/B testing - Test different content approaches per location
Automated content refresh - AI updates content based on performance data

The Key Insight

Location pages aren't about quantity. They're about relevance.

Google's guidelines are clear: doorway pages with thin content will be penalized. But pages with genuine, unique, helpful content will rank.

Our approach:

800+ words of unique content per page
Location-specific insights based on real data
Service-specific value tailored to local needs
Consistent AI-first messaging without repetition

AI-generated content that's actually useful beats hand-written content that's generic. The system scales because the content quality scales with it.

790 pages. Each one unique. Each one valuable. That's the power of AI-first development.

Continue the Series

This is Part 2 of our Site Infrastructure series. Keep reading:

Part 1: Hub & Spoke SEO Architecture: The Complete Guide — The strategy behind our content structure
Part 3: Headless CMS SEO Playbook — Technical architecture for content systems
Part 4: Enterprise Site Migration SEO Playbook — How to migrate without losing rankings

See It In Action

Explore our location pages:

Locations Hub — All 21 Australian cities
Sydney | Melbourne | Brisbane | Perth
SEO Sydney | Google Ads Melbourne

Related services:

Tell me what you need.

You get a straight answer, from me.

Tell me about your business