# Template Competitive Analysis Guide

**Last Updated:** 2026-03-08

Guide for analyzing competitor Excel templates to inform template design and optimization.

## Overview

Competitive analysis for templates goes beyond web page analysis—it includes analyzing actual Excel template files to understand:
- Formulas and functions used
- Template structure and organization
- Styling and formatting patterns
- Data validation strategies
- Conditional formatting usage
- Features and capabilities
- Usability patterns

## Analysis Types

### 1. Web Page Competitor Analysis

**Purpose:** Understand SEO positioning and content strategy

**Tools:**
- SISTRIX API (competitor URLs)
- Firecrawl API (content extraction)
- Manual SERP review

**What to Analyze:**
- Content structure (headings, word count)
- FAQ questions and answers
- Internal linking patterns
- Schema markup
- Content depth and coverage

**Script:**
```bash
php v2/scripts/templates/collect-template-competitor-analysis.php --template={slug}
```

### 2. Excel Template Competitor Analysis

**Purpose:** Understand Excel template design and functionality

**Tools:**
- Web Search MCP (discover templates)
- Firecrawl Search (discover templates)
- PhpSpreadsheet (analyze Excel files)
- Manual Excel inspection

**What to Analyze:**
- Formulas and functions used
- Sheet structure and organization
- Data validation rules
- Conditional formatting patterns
- Styling and branding
- Named ranges usage
- Example data patterns

**Scripts:**
```bash
# Discover competitor templates
php v2/scripts/templates/collect-template-competitor-templates.php --template={slug}

# Analyze competitor templates
php v2/scripts/templates/analyze-competitor-template.php --template={slug} --all

# Generate comparison report
php v2/scripts/templates/generate-template-comparison-report.php --template={slug}
```

## Discovery Process

### Step 1: Search for Competitor Templates

**Automated Discovery** (run as part of `run-new-template-pipeline.php`):
```bash
php v2/scripts/templates/collect-template-competitor-templates.php --template={slug}
```

Uses **Firecrawl Search** (when `--use-firecrawl-search` or config enabled) or **DuckDuckGo HTML** (free fallback) to find URLs. Output: `competitor-templates.json`.

**Search Queries** (automated):
- `{primary keyword} excel vorlage kostenlos`
- `{primary keyword} excel template download`
- `{primary keyword} vorlage excel`
- `{primary keyword} excel kostenlos`

**Manual Discovery** (when automated finds few results):
- Search Google manually (incognito)
- Check competitor websites
- Check template repositories
- Check industry-specific resources

### Step 2: Download Competitor Templates

**Download Process:**
1. Identify downloadable Excel files (.xlsx)
2. Download to `v2/data/templates/competitor-downloads/`
3. Verify file is valid Excel file
4. Document source URL and metadata

**Manual Download:**
- Right-click → Save As
- Use browser developer tools to find direct download links
- Use curl/wget for programmatic downloads

### Step 3: Analyze Excel Files

**Automated Analysis:**
```bash
php v2/scripts/templates/analyze-competitor-template.php --template={slug} --url={excel-url}
```

**Manual Analysis:**
1. Open Excel file in Microsoft Excel
2. Review sheet structure
3. Inspect formulas (show formulas: Ctrl+`)
4. Check data validation rules
5. Review conditional formatting
6. Analyze styling patterns
7. Test functionality

## Analysis Checklist

### Formula Analysis

- [ ] Functions used (SUM, IF, VLOOKUP, etc.)
- [ ] Formula complexity (simple vs advanced)
- [ ] Error handling (IFERROR usage)
- [ ] Named ranges usage
- [ ] Volatile functions (NOW, TODAY, RAND)
- [ ] Array formulas
- [ ] Formula organization patterns

### Structure Analysis

- [ ] Number of sheets
- [ ] Sheet types (instructions, data entry, calculations, reports)
- [ ] Sheet organization and flow
- [ ] Cell organization patterns
- [ ] Table usage (Excel Tables)
- [ ] Freeze panes usage

### Data Validation Analysis

- [ ] Validation types used (list, number, date, custom)
- [ ] Validation coverage (which cells have validation)
- [ ] Input messages and error alerts
- [ ] Custom formula validation
- [ ] Dropdown lists and options

### Conditional Formatting Analysis

- [ ] Formatting rules used
- [ ] Color schemes
- [ ] Visual feedback patterns
- [ ] Data bars, color scales, icon sets
- [ ] Conditional logic complexity

### Styling Analysis

- [ ] Color palette (2-4 colors typical)
- [ ] Font choices and sizes
- [ ] Header styling
- [ ] Input cell styling
- [ ] Formula cell styling
- [ ] Border usage
- [ ] Branding consistency

### Feature Analysis

- [ ] Charts and graphs
- [ ] Pivot tables
- [ ] Macros/VBA (if present)
- [ ] Protected sheets
- [ ] Print settings
- [ ] Page layout optimization

### Usability Analysis

- [ ] Instructions clarity
- [ ] Example data quality
- [ ] Navigation ease
- [ ] Input field clarity
- [ ] Error prevention
- [ ] User guidance quality

## Comparison Matrix

Create a comparison matrix comparing our template vs competitors:

| Feature | Our Template | Competitor 1 | Competitor 2 | Competitor 3 |
|---------|-------------|--------------|--------------|--------------|
| Sheets | X | Y | Z | W |
| Formulas | X | Y | Z | W |
| Functions | List | List | List | List |
| Data Validation | Yes/No | Yes/No | Yes/No | Yes/No |
| Conditional Formatting | Yes/No | Yes/No | Yes/No | Yes/No |
| Named Ranges | Count | Count | Count | Count |
| Example Data | Yes/No | Yes/No | Yes/No | Yes/No |
| Instructions | Quality | Quality | Quality | Quality |

## Gap Analysis

### Identify Gaps

1. **Missing Features:**
   - Features competitors have that we don't
   - Prioritize by user value and complexity

2. **Missing Formulas:**
   - Functions competitors use that we don't
   - Consider if they add value

3. **Missing Validation:**
   - Data validation rules competitors use
   - Consider error prevention value

4. **Missing Formatting:**
   - Conditional formatting patterns
   - Visual feedback mechanisms

5. **Structure Gaps:**
   - Sheet organization differences
   - Navigation improvements

### Prioritize Improvements

1. **High Value, Low Effort:** Implement first
2. **High Value, High Effort:** Plan for future
3. **Low Value, Low Effort:** Consider if time allows
4. **Low Value, High Effort:** Skip unless critical

## Best Practices Extraction

### Extract Best Practices

From competitor analysis, extract:
- Formula patterns that work well
- Styling patterns that improve usability
- Structure patterns that improve navigation
- Validation patterns that prevent errors
- Formatting patterns that provide feedback

### Document Findings

Create `best-practices-from-competitors.md`:
- List best practices found
- Explain why they work
- Document how to implement
- Reference competitor examples

## Implementation

### Update Template Definition

Based on analysis:
1. Add missing formulas
2. Add missing features
3. Improve structure
4. Enhance styling
5. Add validation rules
6. Add conditional formatting

### Test Improvements

After implementing:
1. Regenerate Excel template
2. Test functionality
3. Compare with competitors
4. Validate quality
5. Get user feedback (if possible)

## Reporting

### Comparison Report

Generated automatically:
```bash
php v2/scripts/templates/generate-template-comparison-report.php --template={slug}
```

**Report Includes:**
- Competitor overview
- Common functions and features
- Detailed competitor analysis
- Recommendations for improvement
- Next steps

### Manual Notes

Document in `COMPETITIVE_ANALYSIS_NOTES.md`:
- Key findings
- Unique competitor features
- Implementation ideas
- Questions to research
- Follow-up actions

## Tools Reference

### Discovery Tools

- **Web Search MCP:** Free general search
- **Firecrawl Search:** Paid, includes full content
- **Serper MCP:** Real Google SERPs

### Analysis Tools

- **PhpSpreadsheet:** Read and analyze Excel files
- **Excel MCP:** Inspect Excel files interactively
- **Manual Excel:** Open and inspect manually

### Scripts

- `collect-template-competitor-templates.php` - Discover templates
- `analyze-competitor-template.php` - Analyze Excel files
- `generate-template-comparison-report.php` - Generate report

## References

- [Template Creation Workflow](TEMPLATE_CREATION_WORKFLOW.md)
- [Excel Best Practices Research](../excel-generator/TEMPLATE_BEST_PRACTICES_RESEARCH.md)
- [Firecrawl Integration](../../systems/firecrawl/FIRECRAWL_INTEGRATION.md)
- [MCP Usage Guide](../../../.cursor/rules/mcp-usage.mdc)