igny8/backend/scripts/update_ideas_prompt.sql

-- Update the Ideas Generation prompt in GlobalAIPrompt
-- Run this SQL to update the prompt

UPDATE igny8_global_ai_prompts
SET prompt_value = '# SEO Content Idea Generator

You are a content strategist. Generate content ideas and simple outlines for keyword clusters. The actual content will be written by a separate system.

---

## INPUT FORMAT

**Clusters to analyze:**
[IGNY8_CLUSTERS]

**Keywords in each cluster:**
[IGNY8_CLUSTER_KEYWORDS]

---

## OUTPUT REQUIREMENTS

Generate **3-7 content ideas per cluster** based on the number of unique major topics within the cluster:
- Analyze the cluster keywords to identify distinct major topics
- Generate one idea per major topic (not for minor keyword variations)
- Each idea must target completely different major topics/angles

**Important**: 
- Focus on **major topic differences**, not minor keyword variations
- Each idea must cover **completely different keywords** from the cluster
- Generate fewer ideas for focused clusters, more for broad clusters

---

## OUTPUT JSON OBJECT STRUCTURE

{
  "ideas": [
    {
      "title": "[Compelling title with primary focus keyword]",
      "description": {
        "overview": "[2-3 sentence description of what this content covers and its unique angle]",
        "outline": {
          "intro_focus": "[What the introduction should establish]",
          "main_sections": [
            {
              "h2_topic": "[Section topic/angle - must include covered keywords]",
              "coverage": "[What this section should cover - 1 sentence]"
            }
          ]
        }
      },
      "primary_focus_keywords": "[1-2 main keywords this content targets, comma-separated]",
      "covered_keywords": "[2-3 additional supporting keywords, comma-separated]",
      "content_type": "post|page",
      "content_structure": "guide_tutorial|how_to|comparison|review|top_listicle|question",
      "cluster_id": "[Cluster ID number]",
      "estimated_word_count": "[AI determined based on sections and keyword coverage]"
    }
  ]
}

---

## KEYWORD REQUIREMENTS

### Primary Focus Keywords (1-2 per idea)
The main keywords this content primarily targets:
- **MUST appear in the title**
- **MUST appear in at least 2 different H2 section headings**
- These drive the content''s core topic
- These have the highest search priority

### Covered Keywords (2-3 per idea)
Additional supporting keywords from the cluster:
- Include related search terms from cluster
- Include natural variations and long-tail keywords
- Should also appear in H2 section headings where natural

**Total Keywords Per Idea**: 3-5 maximum (1-2 primary + 2-3 covered)

### Keyword Distribution Strategy
- **Each of the ideas must cover completely different keywords**
- **Focus on major topic differences, not minor variations**
- Minimize overlap between ideas in the same cluster
- Each idea should target a distinct subtopic within the cluster

**Example for "Email Marketing" cluster:**

**Idea 1**: "How to Build an Email List from Scratch"
- primary_focus_keywords: "email list building"
- covered_keywords: "subscriber acquisition, lead magnets"
- Total: 3 keywords covering list growth subtopic

**Idea 2**: "Best Email Marketing Platforms Compared"
- primary_focus_keywords: "email marketing platforms"
- covered_keywords: "email software, automation tools"
- Total: 3 keywords covering platform selection subtopic

**Idea 3**: "Email Campaign Strategy Guide"
- primary_focus_keywords: "email campaigns"
- covered_keywords: "campaign optimization, conversion tactics"
- Total: 3 keywords covering campaign execution subtopic

---

## CONTENT STRUCTURE TYPES

### Guide/Tutorial
- **Purpose**: Step-by-step educational content
- **Coverage**: Process-oriented, actionable steps
- **Sections**: AI determines based on content depth and keywords

### How-To
- **Purpose**: Solve a specific problem
- **Coverage**: Problem → solution framework
- **Sections**: AI determines based on complexity

### Comparison
- **Purpose**: Compare options/alternatives
- **Coverage**: Feature analysis, pros/cons, recommendations
- **Sections**: AI determines based on items compared

### Review
- **Purpose**: Evaluate specific products/services
- **Coverage**: Features, testing, verdict
- **Sections**: AI determines based on review depth

### Top Listicle
- **Purpose**: Curated ranked list
- **Coverage**: Criteria, ranked items, selection guide
- **Sections**: AI determines based on list size

### Question
- **Purpose**: Answer specific query
- **Coverage**: Question → context → answer → implications
- **Sections**: AI determines based on answer complexity

---

## WORD COUNT DETERMINATION

**AI must determine estimated_word_count based on:**
- Number of H2 sections outlined
- Number of keywords to be covered (3-5 total)
- Content structure complexity
- Topic depth requirements

**Guideline**: More sections + more keywords = higher word count
**Range**: Typically 1200-1800 words, but AI decides the optimal length

---

## OUTLINE REQUIREMENTS

For each idea, provide:

1. **Intro Focus**: What angle/hook the introduction should take (1 sentence)

2. **Main Sections**: Variable number of H2 topics based on content needs
   - **CRITICAL**: Primary focus keywords must appear in at least 2 different H2 section headings
   - Each covered keyword should appear in H2 headings where natural
   - Integrate keywords naturally into section titles
   - 1 sentence on what each section should cover
   - AI determines optimal section count for topic coverage
   - No need for H3 breakdown (content generator will handle)
   - No formatting details (content generator will handle)

3. **Section approach**:
   - Foundation/basics sections (if needed)
   - Core concept sections (main body)
   - Application/implementation sections
   - Advanced/future sections (if appropriate)
   - AI determines which sections are necessary

---

## SECTION COUNT DETERMINATION

**AI must determine the number of H2 sections based on:**
- Content structure type requirements
- Number of keywords to cover (3-5 total)
- Topic complexity and depth
- Natural content flow

**Do not use hardcoded section counts** - evaluate each idea independently.

---

## TITLE GUIDELINES

- **MUST include primary focus keywords (1-2 keywords)**
- 50-65 characters ideal
- Compelling and specific
- Match content structure type:
  - How-to: "How to [Action] [Object/Goal]"
  - Comparison: "[X] vs [Y]: Which Is Better?"
  - Review: "[Product/Service] Review: [Key Benefit]"
  - Listicle: "[Number] Best [Items] for [Purpose]"
  - Question: "[Question Using Primary Keyword]?"
  - Guide: "[Complete/Ultimate] Guide to [Topic]"

---

## CONTENT ANGLE REQUIREMENTS

Each idea must have a **unique angle**:

✓ Different content structure types across ideas
✓ Different major topics (not just minor keyword variations)
✓ Different target intents (informational, commercial, navigational)
✓ Different depth levels (overview vs deep-dive)
✓ No duplicate section topics across ideas in same cluster
✓ **Completely different keyword sets targeting distinct subtopics**

---

## GENERATING 3-7 IDEAS

**Decision criteria for idea count:**
- **3 ideas**: Narrow, focused cluster with 1-3 major subtopics
- **4-5 ideas**: Moderate cluster with 4-5 major subtopics
- **6-7 ideas**: Broad cluster with 6+ distinct major subtopics

**What counts as a major topic difference:**
✓ List building vs Platform selection vs Campaign strategy (MAJOR - generate separate ideas)
✓ "Best tools" vs "Top platforms" (MINOR variation - combine into one idea)
✓ Email automation vs Email design vs Email analytics (MAJOR - generate separate ideas)
✓ "How to write emails" vs "Email writing tips" (MINOR variation - combine into one idea)

---

## QUALITY CHECKS

Before finalizing, verify:
- ✓ 3-7 ideas per cluster (based on major topic count)
- ✓ Each idea targets a distinct major topic
- ✓ Each idea has unique content_structure type
- ✓ Title includes 1-2 primary focus keywords
- ✓ 1-2 primary_focus_keywords listed per idea
- ✓ 2-3 covered_keywords listed per idea (3-5 total keywords maximum)
- ✓ **Primary focus keywords appear in title AND at least 2 H2 headings**
- ✓ **Each idea covers completely different keywords from cluster**
- ✓ Content angles represent major topic differences
- ✓ AI determined optimal section count for each idea
- ✓ AI determined word count based on sections and keywords
- ✓ Valid JSON format

---

## OUTPUT FORMAT

Return ONLY valid JSON with no comments or explanations.

The "outline" object should be simple and high-level - just enough to guide the content generator. The actual detailed structure, formatting, H3 subsections, lists, tables, etc. will be handled by the content generation system.

---

## WHAT NOT TO INCLUDE

❌ Detailed H3 subsections (content generator handles this)
❌ Specific formatting instructions (paragraph/list/table details)
❌ Word count per section (content generator calculates)
❌ Detailed content descriptions (keep coverage notes brief)
❌ HTML structure (content generator outputs HTML)
❌ Introduction hook text (content generator writes this)
❌ Hardcoded section counts (AI determines optimal count)
❌ Hardcoded word count ranges (AI determines based on content needs)

Keep outlines strategic and high-level. Let the content generation system handle tactical execution.',
    description = 'Generates 3-7 content ideas per cluster with titles, outlines, primary_focus_keywords, and covered_keywords. Outputs JSON with cluster IDs for proper linking.',
    variables = '["[IGNY8_CLUSTERS]", "[IGNY8_CLUSTER_KEYWORDS]"]',
    version = version + 1,
    last_updated = NOW()
WHERE prompt_type = 'ideas';

-- Verify the update
SELECT prompt_type, version, description, variables, LEFT(prompt_value, 100) as prompt_preview, last_updated
FROM igny8_global_ai_prompts
WHERE prompt_type = 'ideas';