How I Built a Keyword Ranking SEO Model That Actually Works (And Created 3 Service Pages in 90 Minutes)

Why Most Keyword Tools Miss the Point

Here’s the uncomfortable truth about SEO tools: SEMrush, Ahrefs, and Moz show you data, not strategy.

They’ll tell you:

• “This keyword gets 2,400 searches/month”
• “Difficulty score: 47”
• “Your competitors rank for this”

What they won’t tell you:

• Which keywords you’re actually targeting
• Which keywords have no target page
• Whether your content strategy makes any sense
• How to systematically ensure complete coverage

After many years of doing SEO, I finally bridged the gap between keyword research and actual execution using Claude Code.

The Problem: Orphaned Keywords Everywhere

This morning, I ran a simple query in my Django-based SEO platform:

# Find keywords without target URLs
orphaned = KeywordRanking.objects.filter(
    client_id=6,  # We Build Stores
    target_url__isnull=True
).values('keyword').distinct()

Result: 10 keywords with no target page. 10 opportunities just sitting there, unaddressed.

Traditional SEO workflow:

1. Export keywords to spreadsheet
2. Manually review them
3. Forget about most of them
4. Write a blog post about one or two
5. Never check if it worked

My workflow:

1. Query the database
2. See exactly what’s missing
3. Create systematic solutions
4. Update the database
5. Track actual rankings over time

The difference? One takes weeks and relies on memory. The other takes minutes and relies on data.

The Solution: A Django Model That Tracks Strategy

Here’s the core of the system - a simple Django model:

class KeywordRanking(models.Model):
    client = models.ForeignKey(Client, on_delete=models.CASCADE)
    keyword = models.CharField(max_length=255)
    target_url = models.CharField(max_length=500, blank=True, null=True)
    current_position = models.IntegerField(null=True, blank=True)
    search_volume = models.IntegerField(null=True, blank=True)
    difficulty = models.IntegerField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

This isn’t revolutionary technology. It’s just structured thinking.

But notice what this enables:

• Link keywords to actual pages (not just “we should rank for this”)
• Track coverage gaps (orphaned keywords)
• Monitor progress (position changes over time)
• Strategic planning (group keywords by target page)

Real Example: This Morning’s 90-Minute Sprint

When I found those 10 orphaned keywords, I grouped them by topic:

Group 1: Digital Marketing (5 keywords)

Keywords like “digital marketing agency uk”, “digital marketing services”, - all related to full-service digital marketing.

Strategic decision: Create a comprehensive digital marketing agency page.

Group 2: Platform Migration (1 keyword)

“ekm to shopify migration” - specific, high-value, underserved.

Strategic decision: Create a dedicated EKM to Shopify migration page with real pricing from actual proposals.

Group 3: Web Development Services (4 keywords)

“website speed optimisation”, “website maintenance services”, “web development uk” - all technical service keywords.

Strategic decision: Create a web development services page covering speed, maintenance, and custom development.

The Claude Code Multiplier

Here’s where it gets interesting. Creating three comprehensive service pages would traditionally take me at least a days work. But using Claude Code and Astro I reduced that to 90 minutes.

Each page includes:

• Comprehensive hero section
• Service breakdowns
• Pricing tables
• FAQ sections
• Google reviews
• Contact forms
• Full header/footer integration
• Mobile responsive design
• SEO optimisation

How? I told Claude Code: “Create a digital marketing agency page targeting these 5 keywords” and it built the entire thing. Then I refined it. Then we moved to the next page.

The Database Update: Systematic Coverage

After creating the pages, I updated the database:

# Update digital marketing keywords
keywords = ['digital marketing agency', 'ppc management', ...]
KeywordRanking.objects.filter(
    keyword__iexact__in=keywords,
    client_id=6
).update(target_url='/digital-marketing-agency')

# Result: 5 keywords now have a target page

Before: 10 keywords with no strategy After: 10 keywords with dedicated, comprehensive service pages Time: 90 minutes total

The Philosophy: Service Pages vs. Blog Posts

This is where most SEO strategies go wrong. They try to rank blog posts for commercial keywords.

Wrong approach:

• Blog post: “10 Tips for Digital Marketing” targeting “digital marketing agency”
• Result: Google shows actual agencies, not your tips article

Right approach:

• Service page: “/digital-marketing-agency” with clear services, pricing, CTAs
• Blog posts: Authority content that links to service pages

Now that every commercial keyword has a service page, I can write blog posts about whatever’s interesting:

• Case studies
• Industry insights
• Controversial opinions
• Technical deep-dives

The blog builds authority. The service pages convert.

The Technical Stack

For those wondering about implementation:

Backend

# Django models
- KeywordRanking (shown above)
- Client (who we're tracking for)
- RankingSnapshot (historical data)

Database queries I run regularly

# Coverage analysis
total_keywords = KeywordRanking.objects.filter(client_id=6).count()
covered = KeywordRanking.objects.filter(
    client_id=6,
    target_url__isnull=False
).count()
coverage_rate = (covered / total_keywords) * 100

# Opportunity identification
high_volume_uncovered = KeywordRanking.objects.filter(
    client_id=6,
    target_url__isnull=True,
    search_volume__gte=500
).order_by('-search_volume')

Frontend

• Astro for service pages (fast, simple, SEO-friendly)
• Tailwind CSS for styling
• No JavaScript bloat

Development

• Claude Code for page creation
• Python for data analysis
• Django admin for manual review

The Results: Complete Keyword Coverage

Starting position:

• 50 total keywords tracked
• 40 keywords with target pages (80% coverage)
• 10 orphaned keywords

After 90 minutes:

• 50 total keywords tracked
• 0 keywords with target pages (100% coverage)
• 0 orphaned keywords

What this enables:

1. Clear strategy - Every keyword has a home
2. Better content - Blog posts can focus on authority, not keywords
3. Improved tracking - We can now monitor if the strategy works
4. Systematic optimisation - Gradual improvements to targeted pages

Why This Matters for Your Business

Most businesses approach SEO backwards:

Traditional approach:

1. Write content
2. Hope it ranks
3. Check rankings occasionally
4. Wonder why it’s not working

Strategic approach:

1. Identify all relevant keywords
2. Map them to specific pages (service or blog)
3. Ensure complete coverage
4. Track performance systematically
5. Optimise based on data

The difference isn’t intelligence - it’s systems.

The Honest Truth About SEO Tools

I used to pay for SEMrush for competitor research and keyword discovery. But it doesn’t tell me what to do.

That’s what the custom Django system does. It turns data into action:

• Which keywords need pages?
• Which pages need optimisation?
• What’s our coverage rate?
• Where are the gaps?

You can build this yourself with:

• Django
• A database (PostgreSQL, MySQL, even SQLite)
• Basic Python knowledge
• Claude Code
• Time to think strategically

Or you can hire someone who’s already built it.

The Real Lesson: Build Tools That Match Your Thinking

The best SEO tool isn’t the one with the most features. It’s the one that matches your strategic process.

For me, that means:

• Keyword → Target URL mapping
• Coverage gap identification
• Historical tracking
• Easy querying

Your needs might be different. Maybe you need:

• Client reporting automation
• Rank tracking by location
• Competitor monitoring
• Link building workflow

The point: Off-the-shelf tools solve generic problems. Custom tools solve your specific problems.

What Happens Next

Now that we have complete keyword coverage, the optimisation phase begins:

1. Monitor rankings for all 50 keywords
2. Identify quick wins (keywords on page 2-3)
3. Optimize systematically (on-page SEO, content depth, internal linking)
4. Track what works (data-driven iteration)
5. Scale what succeeds (apply winning patterns)

This is how modern SEO works. Not keyword stuffing. Not link spam. Strategic, systematic, measurable.

Want to See This in Action?

The three pages I built this morning are live:

• Digital Marketing Agency - 5 keywords
• EKM to Shopify Migration - 1 keyword
• Web Development Services - 4 keywords

Check them out. They’re comprehensive, fast-loading, and built in 90 minutes.

That’s the power of:

1. Strategic thinking (keyword → page mapping)
2. Custom tools (Django tracking system)
3. AI multiplication (Claude Code development)

Mapping your keywords to destination pages means you can’t fail to spot where your weak points are and address them.

That’s the power of modern AI tooling.