Analyze Google's top-ranking pages for any keyword, extract recurring entities with AI, cluster them into SEO-friendly topic groups, and export the full dataset to Google Sheets.
Table of Contents
SEO Content Intelligence Engine is a full-stack keyword analysis tool that automates competitive SERP research. Instead of manually reviewing the top-ranking pages for a keyword, this app does it for you — scraping, extracting structured entities, grouping them into topic clusters, and presenting the results in an interactive dashboard.
Key features:
- Top 10 SERP analysis — pulls Google organic results via Serper.dev and scrapes each page
- AI entity extraction — OpenAI identifies repeated entities (brands, prices, features, promotions, etc.)
- Topic clustering — entities are grouped into SEO-relevant topic angles with color-coded visualizations
- Bilingual UI — locale-aware routing with English (
/en) and Traditional Chinese (/zh) - Auth & isolation — Supabase Auth with email confirmation; per-user data isolation via RLS
- Google Sheets export — one-click export to a spreadsheet shared with your account
- Security hardened — SSRF-aware scraping, Zod validation, burst / daily / global API limits
Keyword ──▶ Serper.dev ──▶ Scrape top 10 pages ──▶ OpenAI entity extraction
│
▼
Google Sheet ◀── Export ◀── Dashboard ◀── Supabase ◀── Topic clustering
- Enter a keyword — submit a search query from the dashboard.
- SERP collection — the backend fetches the top 10 Google organic results via Serper.dev.
- Page scraping — each ranking page is scraped and cleaned for LLM processing.
- Entity extraction — OpenAI identifies repeated entities across all results.
- Topic clustering — extracted entities are grouped into SEO-relevant topic clusters.
- Storage & visualization — results are persisted in Supabase and rendered with interactive charts.
- Export — one-click export to a Google Sheet shared with your account.
| Layer | Technology |
|---|---|
| Framework |  Next.js 16 (App Router) |
| Frontend |  React 19 · Tailwind CSS 4 · shadcn/ui · Recharts |
| Auth & Database |  Supabase (Auth + Postgres + RLS) |
| AI |  OpenAI API (gpt-5.4-mini) |
| SERP Data | Serper.dev |
| Export | Google Sheets API + Drive API |
| Scraping | Axios + Cheerio |
| Validation | Zod |
src/
app/
api/
analyze/ keyword analysis pipeline
export/gsheet/ Google Sheets export
searches/ search history and detail endpoints
[locale]/
dashboard/ analysis workspace and result pages
login/ signup/ auth screens
components/ dashboard UI, charts, tables, forms
i18n/ locale helpers and UI copy
lib/
gsheet.ts Google Sheets export
openai.ts entity extraction and clustering
scraper.ts HTML fetching and content cleanup
serper.ts Google SERP lookup
supabase/ client/server Supabase helpers
rate-limit.ts in-memory request throttling
validation.ts Zod schemas
supabase/
migrations/ database schema and RLS policies
apps-script/
Code.js optional Google Apps Script companion
- Node.js 18+
- A Supabase project
- API keys for Serper.dev and OpenAI
- A Google Cloud service account with Sheets & Drive access (for export)
Create .env.local in the project root:
SERPER_API_KEY=
OPENAI_API_KEY=
NEXT_PUBLIC_SUPABASE_URL=
NEXT_PUBLIC_SUPABASE_ANON_KEY=
GOOGLE_SERVICE_ACCOUNT_EMAIL=
GOOGLE_PRIVATE_KEY=
NEXT_PUBLIC_APP_URL=http://localhost:3000Note:
GOOGLE_PRIVATE_KEYmust preserve newline characters. The app converts escaped\nsequences back into real newlines.
-
Set up Supabase — create a project, apply the SQL files in
supabase/migrations/, enable email auth, and configure redirect URLs. -
Clone and install:
git clone https://github.com/balaboom123/SEO-Content-Intelligence-Engine.git cd SEO-Content-Intelligence-Engine npm install -
Run the dev server:
npm run dev
-
Open http://localhost:3000. The root route redirects to the default locale.
| Method | Path | Description |
|---|---|---|
POST |
/api/analyze |
Run a new keyword analysis (auth + email verification required) |
GET |
/api/searches |
List current user's search history |
GET |
/api/searches/:id |
Get one analysis with SERP results, entities, and clusters |
DELETE |
/api/searches/:id |
Delete one saved analysis |
POST |
/api/export/gsheet |
Export an analysis to Google Sheets |
| Table | Description |
|---|---|
searches |
Analysis jobs owned by a user |
serp_results |
Ranked Google results per search |
entities |
Extracted entities attached to a SERP result |
entity_clusters |
Grouped topic angles attached to a search |
All tables are protected by Row Level Security (RLS) policies scoping access to the owning user.
The analysis pipeline is currently tuned for Taiwan SERPs and Traditional Chinese telecom content:
- Serper requests target
gl: "tw",hl: "zh-TW" - The entity extraction prompt is specialized for telecom plan analysis
- The UI supports English and Chinese, but the analysis logic is not yet generalized for all industries
- Generalize entity extraction for any industry
- Support additional SERP regions and languages
- Add automated test suite
- Persistent rate limiting (replace in-memory store)
- Historical trend tracking across repeated keyword analyses
See the open issues for a full list of proposed features and known issues.
Contributions are welcome! If you have a suggestion:
- Fork the repo
- Create your feature branch (
git checkout -b feature/amazing-feature) - Commit your changes (
git commit -m 'Add amazing feature') - Push to the branch (
git push origin feature/amazing-feature) - Open a Pull Request
Distributed under the MIT License. See LICENSE for more information.