# templates-new-template-creation Full Instructions

## New Template Creation Workflow

**Complete workflow for creating new Excel templates from keyword to published template.**

## Phase 1: Scaffold Creation

**Create minimal valid template structure:**

```bash
php v2/scripts/templates/create-new-template.php --keyword="Primary Keyword" --category=category
```

**Required:**
- Primary keyword/topic
- Template category (shift_planning, time_tracking, payroll, termination, etc.)

**Output:**
- Registry entry (status: draft)
- Template definition JSON (minimal structure)
- Template data directory
- Target keywords file

**Verify:**
- Registry entry created
- Definition file exists
- Data directory created

## Phase 2: Data Collection Pipeline

**Run comprehensive data collection:**

```bash
php v2/scripts/templates/run-new-template-pipeline.php --template={slug}
```

**Pipeline Steps (Automated):**

1. **Ensure candidate keywords** - Regenerates template-candidate-keywords.json (prefers target-keywords.json for new templates)
2. **SISTRIX Keywords** - Keyword research and metrics
3. **PAA Questions** - People Also Ask questions
4. **SERP Features** - SERP feature analysis
5. **FAQ Research** - FAQ research data collection
6. **Competition Levels** - Competition analysis
7. **Search Intent** - Intent classification
8. **Competitor Analysis** - Web page competitor analysis
9. **Competitor Depth** - Content depth analysis
10. **Firecrawl Validation** - Validate/remediate sparse competitors
11. **Template Competitor Discovery** - Find Excel templates via Firecrawl Search or DuckDuckGo
12. **Template Competitor Analysis** - Analyze Excel file structure
13. **Template Comparison Report** - Generate comparison report
14. **SERP Skeleton** - Generate SERP analysis skeleton
15. **Content Depth Report** - Generate content depth report
16. **Pre-Content Checklist** - Generate pre-content checklist
17. **Template Design Brief** - Generate TEMPLATE_DESIGN_BRIEF.md for Phase 4 planning

**SISTRIX is mandatory.** The pipeline uses `--template-priority` to bypass daily credit limits. Do not skip.

**Skip Options:**
- `--skip-paa` - Skip PAA step only (use when SISTRIX credits low)
- `--skip-competitor` - Skip competitor analysis
- `--skip-template-analysis` - Skip Excel template competitive analysis

**Output Files:**
- `keywords-sistrix.json`
- `paa-questions.json`
- `serp-features.json`
- `faq-research.json`
- `competitor-analysis.json`
- `competitive-depth-analysis.md`
- `competitor-templates.json`
- `competitor-template-analysis.json`
- `template-comparison-report.md`
- `SERP_ANALYSIS.md`
- `TEMPLATE_DESIGN_BRIEF.md`
- `CONTENT_OUTLINE.md`

## Phase 3: Manual Analysis & Planning

**CRITICAL:** Complete manual analysis before template definition creation.

### 3.1 SERP Analysis (30-60 minutes)

**Required Steps:**

1. **Run Serper MCP (MANDATORY):**
   ```
   Use Serper MCP to search Google for [primary keyword]
   ```
   - Capture PAA questions
   - Capture featured snippet format
   - Capture organic result order
   - Document in SERP_ANALYSIS.md

2. **Complete SERP_ANALYSIS.md:**
   - Fill manual sections (30+ minutes)
   - Document SERP features
   - Analyze top 5-10 ranking pages
   - Identify content gaps
   - Document recommendations

### 3.2 Competitive Analysis Review

**Review Files:**

1. **competitive-depth-analysis.md:**
   - Content depth vs competitors
   - Word count targets
   - Content gap analysis

2. **template-comparison-report.md:**
   - Excel template features comparison
   - Formula and function analysis
   - Styling pattern analysis
   - Feature gap identification

3. **competitor-template-analysis.json:**
   - Formulas used by competitors
   - Functions used by competitors
   - Structure patterns
   - Styling patterns

### 3.3 Content Outline Creation

**Complete CONTENT_OUTLINE.md:**

- Define content blocks structure (4-7 blocks)
- Plan FAQ questions (8-12 questions)
- Plan internal links to tools/products
- Set content depth targets
- Map PAA questions to blocks
- Map competitor gaps to blocks

### 3.4 Template Design Planning

**Plan Template Definition:**

- **Sheet Structure:**
  - Instructions sheet (Anleitung)
  - Data entry sheet (Daten)
  - Calculation sheet (Berechnungen) - optional
  - Report sheet (Übersicht) - optional

- **Formulas & Functions:**
  - List formulas to implement
  - Functions to use (from competitor analysis)
  - Error handling (IFERROR)
  - Named ranges

- **Data Validation:**
  - Validation rules for input cells
  - Dropdown lists
  - Custom formula validation

- **Conditional Formatting:**
  - Visual feedback rules
  - Compliance checks
  - Status indicators

- **Styling:**
  - Color scheme (Ordio branding)
  - Font choices
  - Header styling
  - Input cell styling

**Reference:**
- [Excel Best Practices Research](../../docs/systems/excel-generator/TEMPLATE_BEST_PRACTICES_RESEARCH.md)
- [Template Competitive Analysis Guide](../../docs/systems/templates/TEMPLATE_COMPETITIVE_ANALYSIS_GUIDE.md)

## Phase 4: Template Definition Creation

**Create comprehensive template definition JSON:**

### 4.1 Update Definition Structure

**Add Sheets:**

```json
{
  "metadata": { ... },
  "sheets": [
    {
      "name": "Anleitung",
      "type": "instructions",
      "order": 0,
      "cells": [ ... ]
    },
    {
      "name": "Daten",
      "type": "data_entry",
      "order": 1,
      "cells": [ ... ],
      "rows": [ ... ],
      "example_rows": [ ... ],
      "data_validation": [ ... ],
      "conditional_formatting": [ ... ]
    }
  ]
}
```

### 4.2 Follow Excel Best Practices

**Required:**

- ✅ English function names (`SUM`, `IF`, `VLOOKUP`)
- ✅ Formula prefix `=` (added automatically)
- ✅ Error handling (`IFERROR` where appropriate)
- ✅ Named ranges for key values
- ✅ Data validation on input cells
- ✅ Conditional formatting for visual feedback
- ✅ Consistent styling (Ordio branding)
- ✅ Example data rows (2-3 rows)
- ✅ Instructions sheet content

**Reference:**
- [Excel Best Practices Research](../../docs/systems/excel-generator/TEMPLATE_BEST_PRACTICES_RESEARCH.md)
- [Template Definition Guide](../../v2/systems/excel-template-generator/docs/TEMPLATE_DEFINITION_GUIDE.md)

### 4.3 Add Ordio CTA

**Required for all production templates.** Per [template-ordio-cta.mdc](template-ordio-cta.mdc) and [IN_TEMPLATE_ORDIO_CTA_GUIDE.md](../../docs/guides/templates/IN_TEMPLATE_ORDIO_CTA_GUIDE.md):

1. **Anleitung:** Add one `info_cell` at end with tailored Ordio mention (use `template-ordio-feature-mapping.json` for `anleitung_override` or category `anleitung_pattern`).
2. **Ordio sheet:** Add `ordio_cta` sheet as last sheet (order: 99) with:
   - A1: title (from mapping `headline`)
   - A3–A5: 2–3 benefit bullets
   - A6: primary CTA link (`https://ordio.com[primary-path]`)

**Validate:**
```bash
php v2/scripts/templates/validate-template-ordio-cta.php --template={slug}
```

### 4.4 Validate Definition

```bash
php v2/systems/excel-template-generator/scripts/template-validator.php {slug}
python3 v2/scripts/dev-helpers/validate-template-consistency.py --template={slug}
php v2/scripts/templates/validate-template-ordio-cta.php --template={slug}
```

**Must Pass:**
- JSON schema validation
- Formula syntax validation
- Cell reference validation
- Data validation rules validation

## Phase 5: Excel Template Generation

**Generate Excel file from definition:**

```bash
php v2/systems/excel-template-generator/scripts/template-cli.php generate {slug}
```

**Validate Excel File:**

```bash
python3 v2/scripts/dev-helpers/validate-generated-xlsx.py
```

**Test in Excel:**
- Open in Microsoft Excel (no repair prompt)
- Open in LibreOffice (compatibility check)
- Test formulas and calculations
- Test data validation
- Test conditional formatting
- Verify styling and branding

**Generate Google Sheets template:**
```bash
php v2/scripts/templates/generate-google-sheets-template.php --template={slug}
```

**Sync Google Sheets** (after generation):
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --template={slug}
```

**Note:** For external sheets (source: "external"), do not run sync – it skips them.

**If corruption errors:**
- Check PhpSpreadsheet version (1.29+)
- Check output buffer issues
- See [EXCEL_TEMPLATE_BEST_PRACTICES.md](../../docs/systems/excel-generator/EXCEL_TEMPLATE_BEST_PRACTICES.md)

## Phase 6: Content Blocks & SEO

**Add template to internal linking configs:**

- Add entry to `docs/systems/templates/template-internal-link-targets.json` (product, tools, related_templates, blog)
- Add entry to `docs/data/template-internal-link-mapping.json` (lexikon, tools, product per topic)
- Add template to `docs/data/blog-template-mapping.json` templates section (topic keywords → `/vorlagen/{slug}` with synonyms)

**Run blog-template audit (after adding to mapping):**

```bash
php v2/scripts/blog/audit-blog-template-links.php --suggest-placements
```

Review `docs/data/blog-template-links-audit.json` for posts missing the template link. See [NEW_TEMPLATE_LINKING_CHECKLIST.md](../../docs/systems/templates/NEW_TEMPLATE_LINKING_CHECKLIST.md).

**Create SEO-optimized content blocks:**

```bash
php v2/scripts/templates/generate-template-content-blocks.php --template={slug}
```

**Suggest contextual links (after content draft):**

```bash
php v2/scripts/templates/suggest-template-contextual-links.php --template={slug}
```

Review `docs/systems/templates/template-data/{slug}/data/suggested-contextual-links.json` and add links in content.md, then sync to content-blocks.json.

**Validate Content Blocks:**

```bash
php v2/scripts/templates/validate-template-content-blocks.php --template={slug}
```

**Generate SEO Meta:**

```bash
php v2/scripts/templates/generate-template-seo-meta.php --template={slug}
```

**Requirements:**
- Primary keyword in title, description
- Content blocks cover all key topics
- Internal links to relevant tools/products (min 3; use suggest script)
- Ordio mentions (once per major section)

**If SISTRIX `primary_keyword` differs from registry `name` (e.g. slug `stundenzettel-excel-vorlage` but head term „stundenzettel vorlage“):** keep the URL on the SISTRIX slug, set `template-data/{slug}/data/seo-meta.json` to lead with the primary phrase, and add a **hero override** in `v2/config/template-page-config.php` (see `TEMPLATE_CONTENT_WORKFLOW.md` → „Primary keyword vs. registry name“).

## Phase 7: FAQ Creation

**Generate FAQs:**

```bash
# Generate questions
php v2/scripts/templates/generate-template-faq-questions.php --template={slug}

# Generate answers
php v2/scripts/templates/generate-template-faq-answers-optimized.php --template={slug}
```

**Validate FAQs:**

```bash
php v2/scripts/templates/validate-template-faq-schema.php --template={slug}
php v2/scripts/templates/validate-template-faq-quality.php --template={slug}
```

**Requirements:**
- 8-12 FAQs optimal
- Answers 40-80 words
- Primary keyword in 3-5 FAQs
- Natural du tone
- No template language

## Phase 8: Validation & Quality Checks

**Run all validators:**

```bash
# Improvement readiness (use --new-template flag)
php v2/scripts/templates/validate-template-improvement-readiness.php --template={slug} --new-template

# Content completeness
php v2/scripts/templates/validate-template-content-completeness.php --template={slug}

# Excel quality
php v2/systems/excel-template-generator/scripts/analyze-template-quality.php {slug}

# Data completeness
php v2/scripts/templates/validate-template-data-completeness.php --template={slug}

# Content blocks
php v2/scripts/templates/validate-template-content-blocks.php --template={slug}

# Internal links
php v2/scripts/templates/validate-template-internal-links.php --template={slug}
```

**All must pass before publishing.**

## Phase 9: Publishing

**Publish template to production:**

1. **Update registry status:**
   - Change `status` from `draft` to `published` in `template-registry.json`

2. **Create hero-visual.json (before index listing):**
   - Add `v2/data/template-hero-visuals/{slug}.json` with **8** tailored rows and correct column widths (`table` / `key_value` / `list` per [TEMPLATE_HERO_VISUALS.md](../../docs/systems/templates/TEMPLATE_HERO_VISUALS.md))
   - Run `php v2/scripts/templates/validate-hero-visuals.php` (required for `make validate` / CI)
   - Published landing previews **do not** use definition extraction; missing JSON shows a placeholder

3. **Add to index config:**
   - Add slug to `$publishedTemplatesForIndex` in `templates-index-config.php`
   - Add index card overrides if needed

4. **Generate routes:**
   ```bash
   php v2/systems/excel-template-generator/scripts/generate-template-routes.php
   ```

5. **Update sitemap:**
   - Add template URL to `sitemap-pages.json`
   - Add to `llms.txt` and `llms-full.txt`

6. **Final validation:**
   - Test template page loads correctly
   - Test download functionality
   - Test form submission
   - Validate schema (Google Rich Results Test)

## Key Differences from Blog Workflow

### Template-Specific Steps

1. **Excel Template Competitive Analysis:**
   - Discover competitor Excel templates (not just web pages)
   - Download and analyze Excel files
   - Extract formulas, functions, styling patterns
   - Compare features and capabilities

2. **Template Definition Creation:**
   - Create JSON definition with sheets, cells, formulas
   - Follow Excel best practices
   - Implement data validation and conditional formatting
   - Generate Excel file from definition

3. **Excel Quality Validation:**
   - Validate Excel file structure
   - Test formulas and calculations
   - Test data validation
   - Test conditional formatting
   - Verify no corruption errors

### Shared Steps (Similar to Blog)

1. **Keyword Research:** SISTRIX collection
2. **Competitor Analysis:** Web page analysis
3. **Content Creation:** Content blocks and FAQs
4. **SEO Optimization:** Meta tags and schema
5. **Validation:** Quality checks and validators

## Prerequisites

### Required Tools

- PHP 8.0+ with PhpSpreadsheet
- SISTRIX API key (for keyword research)
- Firecrawl API key (optional, for competitor analysis)
- Python 3.x (for Excel validation)

### Required Data

- Primary keyword/topic
- Template category
- Target audience understanding

## Time Estimates

- **Phase 1 (Scaffold):** 5 minutes
- **Phase 2 (Data Collection):** 30-60 minutes (automated)
- **Phase 3 (Manual Analysis):** 2-4 hours
- **Phase 4 (Template Definition):** 4-8 hours
- **Phase 5 (Excel Generation):** 30 minutes
- **Phase 6 (Content & SEO):** 2-3 hours
- **Phase 7 (FAQs):** 1-2 hours
- **Phase 8 (Validation):** 1 hour
- **Phase 9 (Publishing):** 30 minutes

**Total:** 12-20 hours per template

## Success Criteria

1. ✅ Template scaffold created
2. ✅ All data collection completed (SISTRIX mandatory – `keywords-sistrix.json` has real API data)
3. ✅ SERP analysis completed (30+ minutes, Serper MCP mandatory)
4. ✅ Competitive analysis reviewed
5. ✅ Template definition created and validated
6. ✅ Excel file generated and tested (no corruption)
7. ✅ Content blocks created and validated
8. ✅ FAQs created (8-12 questions)
9. ✅ All validators pass
10. ✅ Template published and accessible

## Troubleshooting

### Common Issues

1. **Excel corruption errors:**
   - Check PhpSpreadsheet version (1.29+)
   - Validate Excel file structure
   - Check for output buffer issues
   - See [EXCEL_TEMPLATE_BEST_PRACTICES.md](../../docs/systems/excel-generator/EXCEL_TEMPLATE_BEST_PRACTICES.md)

2. **Missing competitor templates:**
   - Use Web Search MCP to discover templates
   - Use Firecrawl Search for deeper discovery
   - Manually add URLs to `competitor-templates.json`

3. **Validation failures:**
   - Review validator output
   - Address specific issues
   - Re-run validators after fixes

4. **SERP analysis incomplete:**
   - **CRITICAL:** Must run Serper MCP for primary keyword
   - Complete all manual sections in SERP_ANALYSIS.md
   - Document findings thoroughly

## References

- [Template Creation Workflow](../../docs/systems/templates/TEMPLATE_CREATION_WORKFLOW.md)
- [Template Competitive Analysis Guide](../../docs/systems/templates/TEMPLATE_COMPETITIVE_ANALYSIS_GUIDE.md)
- [Excel Best Practices Research](../../docs/systems/excel-generator/TEMPLATE_BEST_PRACTICES_RESEARCH.md)
- [Blog New Post Creation](../blog-new-post-creation.mdc)