# Google Sheets Competitor Traffic Report

> Pull SimilarWeb traffic data for your competitors and export a structured comparison spreadsheet to Google Sheets automatically.

Source: https://cotera.co/solutions/ops/google-sheets-competitor-traffic-report

---

- **Team:** Operations
- **Tools:** Google Sheets, SimilarWeb
- **Difficulty:** medium
- **Setup time:** 10 min
- **Time saved:** 2-3 hrs/report

## How it works
1. **Discover Competitors** — Find similar sites by audience overlap via SimilarWeb
2. **Pull Traffic Metrics** — Monthly visits, rankings, bounce rate, engagement
3. **Calculate Trends** — Month-over-month growth and traffic source breakdown
4. **Export to Sheets** — Build a sorted, formatted comparison spreadsheet

## Example requests
- Build a competitor traffic report for Figma vs Canva vs Sketch and export to my sheet
- Compare traffic for the top 5 CRM platforms and add to my competitive tracking spreadsheet
- Pull SimilarWeb data for HubSpot and its competitors and export to Google Sheets

## Prompt

```markdown
## Task

Build a competitor traffic comparison spreadsheet by pulling web traffic data from SimilarWeb and exporting it to Google Sheets. Use @google_search to identify competitor domains, @SimilarWeb/Get Traffic Data to pull traffic metrics for each, @SimilarWeb/Get Similar Sites to discover additional competitors, @Google Sheets/Read Range to check for existing data, and @Google Sheets/Append Row to add each competitor as a row.

**Example:** Compare traffic for Notion, Coda, Confluence, and Slite and export a comparison sheet.

## Input

The user will provide:
1. A primary company or domain to analyze
2. Optional: known competitors to include
3. Number of competitors to track (default: 5-10)
4. Google Sheets spreadsheet ID for export

**Example:** "Build a competitor traffic report for Figma. Include Canva, Sketch, and Adobe XD. Export to spreadsheet ID 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"

## Context

### Research Strategy

1. Google the primary company to confirm their domain
2. Use SimilarWeb/Get Similar Sites to discover competitors by audience overlap
3. Merge discovered competitors with any user-specified ones
4. For each competitor (including the primary company), pull SimilarWeb/Get Traffic Data
5. Read the existing spreadsheet to check for prior entries
6. Append each competitor as a new row with all metrics

### Column Structure

The spreadsheet should have these columns:
- Company Name
- Domain
- Monthly Visits
- Global Rank
- Category Rank
- Bounce Rate
- Pages per Visit
- Avg Visit Duration
- Top Traffic Source
- MoM Traffic Change
- Data Snapshot Date

### Data Quality

- Use the most recent month of data available from SimilarWeb
- Note the snapshot date so comparisons are apples-to-apples
- If a competitor's traffic data is unavailable (too small), still add the row with "N/A" values
- Calculate month-over-month change if historical data is available
- Sort rows by monthly visits (highest first) when appending

## Output

**Competitor Traffic Overview:**
| Company | Monthly Visits | Global Rank | MoM Change |
|---------|---------------|-------------|------------|
| [Company 1] | X | Y | +/-Z% |

**Traffic Source Breakdown (top competitor):**
| Source | Share |
|--------|-------|
| Direct | X% |
| Search | X% |
| Social | X% |
| Referral | X% |

**Rows Added to Sheet:** [X] competitors exported

**Key Findings:**
- **Market leader by traffic:** [Company] with [X] monthly visits
- **Fastest growing:** [Company] with [X]% MoM increase
- **Highest engagement:** [Company] with [X] pages per visit and [X] avg duration

**Recommendation:** [One sentence on competitive positioning — e.g., "Figma leads in traffic and engagement, but Canva is growing faster month-over-month. Worth monitoring their product expansion."]
```

## Related reading
- [Airtable Vs Google Sheets Automation](https://cotera.co/articles/airtable-vs-google-sheets-automation.md)
- [Automate Google Ads Reporting Ai](https://cotera.co/articles/automate-google-ads-reporting-ai.md)
- [Automated Google Ads Reporting Guide](https://cotera.co/articles/automated-google-ads-reporting-guide.md)