# Template Creation Workflow

**Last Updated:** 2026-03-26

Comprehensive workflow for creating new Excel templates with deep analysis, competitive research, and best practices.

**Quick checklist:** See [TEMPLATE_CREATION_2026.md](TEMPLATE_CREATION_2026.md) for the 2026 checklist.

## Overview

This workflow guides the creation of new Excel templates from initial keyword to published template.

### Optional Path: Register Existing Google Sheet (No Excel Definition)

If you have an **existing Google Sheet** you want to offer as a template (no Excel definition, no sync from definition):

1. Run scaffold: `php v2/scripts/templates/create-new-template.php --keyword="..." --category=... --title="..."`
2. Add entry to `v2/data/google-sheets-template-ids.json` with `source: "external"`, `spreadsheet_id`, and `copy_url`
3. Create template documentation (e.g. `ARBEITSZEITERFASSUNG_EXCEL_VORLAGE.md`) with copy URL and structure
4. Sync will skip this template – never overwrites external sheets

See [GOOGLE_SHEETS_INTEGRATION.md](../google-sheets-integration/GOOGLE_SHEETS_INTEGRATION.md) "Registering External/Manual Google Sheets" and `arbeitszeiterfassung-excel-vorlage` for the full workflow. It combines SEO/keyword research (similar to blog posts) with Excel template-specific competitive analysis and design optimization.

## Workflow Phases

### Phase 1: Scaffold Creation

**Goal:** Create minimal valid template structure

**Steps:**

1. **Create template scaffold:**
   ```bash
   php v2/scripts/templates/create-new-template.php --keyword="Primary Keyword" --category=category
   ```

2. **Verify scaffold created:**
   - Registry entry added
   - Template definition JSON created
   - Template data directory created
   - Target keywords file created

**Output:**
- `v2/systems/excel-template-generator/data/template-definitions/examples/{slug}.json`
- `docs/systems/templates/template-data/{slug}/data/target-keywords.json`
- Registry entry in `template-registry.json` (status: draft)

### Phase 2: Data Collection Pipeline

**Goal:** Collect comprehensive data for template creation

**Steps:**

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

2. **Pipeline steps (automated):**
   - **Ensure candidate keywords** – Regenerates template-candidate-keywords.json (required for SISTRIX; prefers target-keywords.json primary for new templates)
   - SISTRIX Keywords collection
   - PAA Questions collection
   - SERP Features analysis
   - FAQ Research data collection
   - Competition Levels analysis
   - Search Intent classification
   - Competitor Analysis (web pages)
   - Competitor Depth analysis
   - Firecrawl Validation
   - **Template Competitor Discovery** (Excel templates)
   - **Template Competitor Analysis** (Excel structure)
   - **Template Comparison Report** generation
   - SERP Skeleton generation
   - Content Depth Report
   - Pre-Content Checklist
   - **Template Design Brief** – Generates TEMPLATE_DESIGN_BRIEF.md (aggregates competitor analysis, formulas, content targets for Phase 4)

**Output Files:**
- `keywords-sistrix.json` - Keyword research data
- `paa-questions.json` - People Also Ask questions
- `serp-features.json` - SERP feature analysis
- `faq-research.json` - FAQ research data
- `competitor-analysis.json` - Web page competitor analysis
- `competitive-depth-analysis.md` - Content depth analysis
- `competitor-templates.json` - Discovered Excel templates
- `competitor-template-analysis.json` - Excel template analysis
- `template-comparison-report.md` - Our template vs competitors
- `SERP_ANALYSIS.md` - SERP analysis skeleton
- `TEMPLATE_DESIGN_BRIEF.md` - Design brief for Phase 4
- `CONTENT_OUTLINE.md` - Content outline skeleton

### Phase 3: Manual Analysis & Planning

**Goal:** Complete manual analysis and create comprehensive plan

**Steps:**

1. **Complete SERP Analysis:**
   - Review `SERP_ANALYSIS.md`
   - Search primary keyword manually (incognito)
   - Document SERP features (featured snippets, PAA, knowledge panels)
   - Analyze top 5-10 ranking pages
   - Identify content gaps and opportunities
   - **Time:** 30-60 minutes

2. **Review Competitive Analysis:**
   - Review `competitive-depth-analysis.md`
   - Review `template-comparison-report.md`
   - Identify Excel template features to include
   - Identify formulas and functions competitors use
   - Identify styling patterns and best practices
   - Document gaps vs competitors

3. **Create Content Outline:**
   - Complete `CONTENT_OUTLINE.md`
   - Define content blocks structure
   - Plan FAQ questions (8-12 questions)
   - Plan internal links to tools/products
   - Set content depth targets

4. **Plan Template Design:**
   - Review Excel best practices research
   - Plan sheet structure (instructions, data entry, calculations, reports)
   - Plan formulas and functions to use
   - Plan data validation rules
   - Plan conditional formatting
   - Plan styling and branding

### Phase 4: Template Definition Creation

**Goal:** Create comprehensive template definition JSON

**Steps:**

1. **Update template definition:**
   - Add sheets based on structure plan
   - Add cells with proper styling
   - Add formulas using English function names
   - Add data validation rules
   - Add conditional formatting
   - Add example data rows (2-3 rows)
   - Add instructions sheet content

2. **Follow Excel best practices:**
   - Use English function names (`SUM`, `IF`, `VLOOKUP`)
   - Use named ranges for key values
   - Add placeholder text to input_ranges (`"placeholder": "[Enter date]"`)
   - Add hyperlinks for sheet navigation
   - Implement error handling (`IFERROR`)
   - Use consistent styling
   - Follow Ordio branding guidelines

3. **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}
   ```

### Phase 5: Excel Template Generation

**Goal:** Generate Excel file from definition

**Steps:**

1. **Generate Excel template:**
   ```bash
   php v2/systems/excel-template-generator/scripts/template-cli.php generate {slug}
   ```

2. **Validate Excel file:**
   ```bash
   python3 v2/scripts/dev-helpers/validate-generated-xlsx.py
   ```

3. **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

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

5. **Sync Google Sheets** (updates timestamp; ensures copy URL is current):
   ```bash
   php v2/scripts/templates/sync-google-sheets-templates.php --template={slug}
   ```

6. **Verify Google Sheets template:**
   - Check copy URL is generated
   - Open copy URL in browser
   - Verify template structure matches Excel version
   - Test formulas work correctly
   - Verify formatting and data validation
   - Test example data displays correctly

**Note:** Google Sheets generation is optional but recommended. Templates work in Excel format even if Google Sheets version is not generated. For **external sheets** (e.g. arbeitszeiterfassung-excel-vorlage), do not run sync – they are registered with `source: "external"` and sync skips them.

### Phase 6: Content Blocks & SEO

**Goal:** Create SEO-optimized content blocks

**Steps:**

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

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

3. **Generate SEO meta:**
   ```bash
   php v2/scripts/templates/generate-template-seo-meta.php --template={slug}
   ```

4. **Review and optimize:**
   - Ensure primary keyword in title, description
   - Ensure content blocks cover all key topics
   - Ensure internal links to relevant tools/products
   - Ensure Ordio mentions (once per major section)

### Phase 7: FAQ Creation

**Goal:** Create comprehensive FAQs

**Steps:**

1. **Generate FAQ questions:**
   ```bash
   php v2/scripts/templates/generate-template-faq-questions.php --template={slug}
   ```

2. **Generate FAQ answers:**
   ```bash
   php v2/scripts/templates/generate-template-faq-answers-optimized.php --template={slug}
   ```

3. **Validate FAQs:**
   ```bash
   php v2/scripts/templates/validate-template-faq-schema.php --template={slug}
   ```

4. **Review FAQ quality:**
   - 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

**Goal:** Ensure template meets all quality standards

**Steps:**

1. **Run improvement readiness validator:**
   ```bash
   php v2/scripts/templates/validate-template-improvement-readiness.php --template={slug} --new-template
   ```

2. **Run content completeness validator:**
   ```bash
   php v2/scripts/templates/validate-template-content-completeness.php --template={slug}
   ```

3. **Run Excel quality validator:**
   ```bash
   php v2/systems/excel-template-generator/scripts/analyze-template-quality.php {slug}
   ```

4. **Run all validators:**
   ```bash
   php v2/scripts/templates/validate-template-data-completeness.php --template={slug}
   php v2/scripts/templates/validate-template-content-blocks.php --template={slug}
   php v2/scripts/templates/validate-template-internal-links.php --template={slug}
   ```

### Phase 9: Publishing

**Goal:** Publish template to production

**Steps:**

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

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

3. **Create hero-visual.json (mandatory before adding slug to `$publishedTemplatesForIndex`):**
   - Create `v2/data/template-hero-visuals/{slug}.json` with **tailored** dummy data: **exactly 8** rows; each row width matches `data_structure` (table = `count(headers)`, key_value = 2 cells, list = 1 cell)
   - Non-empty `sheet_name`; no formulas; no placeholder wording (Beispiel/Dummy/Musterdaten)
   - See [TEMPLATE_HERO_VISUALS.md](TEMPLATE_HERO_VISUALS.md) for full contract
   - Run `php v2/scripts/templates/validate-hero-visuals.php` and optionally `python3 v2/scripts/templates/audit-hero-visual-rows.py`
   - **Note:** Published templates do not use definition extraction for the landing preview; missing/invalid JSON shows a placeholder and fails CI (`make validate`)

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)

## Data Flow

```mermaid
flowchart TB
    subgraph Phase1 [Phase 1: Scaffold]
        CREATE[create-new-template.php] --> REGISTRY[template-registry.json]
        CREATE --> DEFINITION[template-definition.json]
        CREATE --> TARGET[target-keywords.json]
    end
    
    subgraph Phase2 [Phase 2: Data Collection]
        PIPELINE[run-new-template-pipeline.php] --> SISTRIX[SISTRIX Keywords]
        PIPELINE --> PAA[PAA Questions]
        PIPELINE --> SERP[SERP Features]
        PIPELINE --> COMP[Competitor Analysis]
        PIPELINE --> TEMPLATE_COMP[Template Competitor Analysis]
        PIPELINE --> COMPARE[Comparison Report]
    end
    
    subgraph Phase3 [Phase 3: Manual Analysis]
        SERP_REVIEW[Manual SERP Review]
        COMP_REVIEW[Review Competitive Analysis]
        OUTLINE[Create CONTENT_OUTLINE.md]
        DESIGN[Plan Template Design]
    end
    
    subgraph Phase4 [Phase 4: Template Definition]
        UPDATE_DEF[Update Definition JSON]
        VALIDATE_DEF[Validate Definition]
    end
    
    subgraph Phase5 [Phase 5: Excel Generation]
        GENERATE[Generate Excel File]
        VALIDATE_XLSX[Validate Excel]
        TEST[Test in Excel/LibreOffice]
    end
    
    subgraph Phase6 [Phase 6: Content & SEO]
        CONTENT_BLOCKS[Generate Content Blocks]
        SEO_META[Generate SEO Meta]
    end
    
    subgraph Phase7 [Phase 7: FAQs]
        FAQ_QUESTIONS[Generate FAQ Questions]
        FAQ_ANSWERS[Generate FAQ Answers]
    end
    
    subgraph Phase8 [Phase 8: Validation]
        VALIDATORS[Run All Validators]
    end
    
    subgraph Phase9 [Phase 9: Publishing]
        PUBLISH[Update Registry Status]
        ROUTES[Generate Routes]
        SITEMAP[Update Sitemap]
    end
    
    REGISTRY --> PIPELINE
    SISTRIX --> SERP_REVIEW
    COMP --> COMP_REVIEW
    TEMPLATE_COMP --> COMP_REVIEW
    SERP_REVIEW --> OUTLINE
    COMP_REVIEW --> DESIGN
    OUTLINE --> UPDATE_DEF
    DESIGN --> UPDATE_DEF
    UPDATE_DEF --> VALIDATE_DEF
    VALIDATE_DEF --> GENERATE
    GENERATE --> VALIDATE_XLSX
    VALIDATE_XLSX --> TEST
    TEST --> CONTENT_BLOCKS
    CONTENT_BLOCKS --> SEO_META
    SEO_META --> FAQ_QUESTIONS
    FAQ_QUESTIONS --> FAQ_ANSWERS
    FAQ_ANSWERS --> VALIDATORS
    VALIDATORS --> PUBLISH
    PUBLISH --> ROUTES
    ROUTES --> SITEMAP
```

## 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
3. ✅ SERP analysis completed (30+ minutes)
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](../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

## References

- [Template Creation 2026 Checklist](TEMPLATE_CREATION_2026.md)
- [Template Competitive Analysis Guide](TEMPLATE_COMPETITIVE_ANALYSIS_GUIDE.md)
- [Excel Best Practices Research](../excel-generator/TEMPLATE_BEST_PRACTICES_RESEARCH.md)
- [Template System Overview](TEMPLATE_SYSTEM_OVERVIEW.md)
- [Template Content Workflow](TEMPLATE_CONTENT_WORKFLOW.md)
- [Blog Content Creation Workflow](../../content/blog/CONTENT_CREATION_WORKFLOW_2026.md)