I’ve built dozens of dashboards to keep a real-time pulse on competitors, and one simple truth has stuck with me: the right data, captured consistently, beats random deep dives every time. In this article I’m going to walk you through how I build a practical 30-day competitor tracking dashboard using SEMrush and Google Sheets. This setup is lightweight, repeatable, and ideal for marketers and founders who want daily visibility into search positions, traffic trends, and content moves without drowning in complexity.

Why a 30-day dashboard?

A 30-day window gives you sensitivity to recent changes (content pushes, SEO wins, paid campaigns) while smoothing out daily noise. It’s short enough to react quickly and long enough to identify meaningful trends. I use this cadence to spot when a competitor’s new page begins to gain SERP traction, or when a paid campaign is likely driving organic lift.

High-level workflow

Here’s the approach I follow every time:

  • Define which competitors and metrics matter.
  • Export data from SEMrush (or use the API) on a daily cadence.
  • Ingest that data into a structured Google Sheet.
  • Create calculated fields, visualizations and alerts.
  • Review daily and do a weekly strategic check-in.

    • What to track

    Picking the right metrics is the most important step. I typically track:

  • Organic keywords and top keyword movements (gainers/losers)
  • Estimated organic traffic (SEMrush Traffic)
  • Top landing pages (ranked pages)
  • Position changes for priority keywords
  • Paid search activity (if any)
  • Backlink acquisition (new referring domains)

    • Data sources in SEMrush

    SEMrush offers multiple useful endpoints:

  • Domain Overview – quick snapshot (traffic, keywords, paid vs organic)
  • Organic Research – full keyword list and positions
  • Positions / Position Tracking – daily rankings for tracked keywords
  • Backlink Analytics – referring domains and new links

    • If you have a SEMrush subscription with API access you can automate exports via the SEMrush API. If not, you can use scheduled CSV exports from the interface and import them into Google Sheets.

    Google Sheets structure

    I build my sheet in tabs. Here’s my standard layout:

    TabPurpose
    CompetitorsMaster list of competitor domains and friendly names
    Daily_RawDaily CSV/API dumps from SEMrush (one row per competitor per metric per day)
    KeywordsTracked keyword positions over time
    Top_PagesTop landing pages and traffic estimates
    BacklinksNew referring domains per day
    DashboardCharts, tables, alert tiles

    Keeping raw data untouched is essential — it lets you rebuild calculations without losing history.

    Automating data import

    Option A: SEMrush API (recommended)

  • Use SEMrush’s domain_ranks or organic endpoints to pull domain-level metrics and keyword positions.
  • Set up a Google Apps Script to call the API daily and append to the Daily_Raw tab. I schedule the script with triggers (daily at a low-traffic hour).
  • Example Apps Script pseudo-flow: fetch API → parse JSON → append rows with timestamp, domain, metric, value.

    • Option B: Scheduled CSV exports

  • SEMrush allows exports from Organic Research and Position Tracking. Export daily or weekly and import via Google Sheets’ File → Import or with an Apps Script that reads the CSV from Google Drive.

    • Option C: Third-party connectors

  • Tools like Supermetrics can connect SEMrush to Google Sheets directly and simplify scheduling, but they add cost. I recommend them if you already use them for other connectors.

    • Key formulas and tricks in Google Sheets

    I use a few core formulas and techniques to transform raw rows into dashboard-ready metrics:

  • QUERY — to aggregate by date and domain: =QUERY(Daily_Raw!A:E,"select A, B, sum(E) where ... group by A, B",1)
  • ARRAYFORMULA — to build automatic columns of date and normalized metrics.
  • VLOOKUP / INDEX-MATCH — to join competitor metadata from Competitors tab.
  • SPARKLINE — tiny trend lines in the Dashboard tab for quick visual cues: =SPARKLINE(range, {"charttype","line";"linewidth",2})
  • Conditional Formatting — highlight position drops greater than 5 spots or traffic declines >20%.

    • Building the Dashboard

    I design the Dashboard tab with three rows of tiles:

  • Top summary tiles: current estimated organic traffic vs 30 days ago, total tracked keywords up/down
  • Trend charts: 30-day traffic trend per competitor (line chart), keywords gained/lost (area or bar)
  • Alerts & Leaders: table of biggest winners and losers in the last 7 days, new backlinks, and pages with rapid position gains

    • To create an alert tile for “big movers” I use a formula that calculates delta in position: =INDEX(current_pos) - INDEX(pos_30_days_ago). Then I filter for absolute values > 5 and sort by magnitude.

    Example: position delta formula

    Assume you have a Keywords tab with columns: Date, Competitor, Keyword, Position. Here’s a simple way to compute 30-day deltas:

  • Latest position per keyword: =MAXIFS(PositionRange, KeywordRange, keyword, DateRange, today)
  • Position 30 days earlier: =MAXIFS(PositionRange, KeywordRange, keyword, DateRange, today-30)
  • Delta: =Latest - Earlier (negative means improvement)

    • Visuals that matter

    I keep visuals minimal but informative:

  • Line charts for traffic/keywords over 30 days (one chart per competitor or an overlay)
  • Bar charts for weekly keyword gains vs losses
  • Table with conditional formatting for “Top pages by recent rank improvement”

    • Use chart titles that include the date range. I also place a small note showing when the last data import ran — it saves wasted interpretations when an import failed.

    Actionable workflows from the dashboard

    A dashboard is only useful if it leads to action. I use it to:

  • Identify competitor content that’s gaining rankings and perform a content gap audit
  • Spot sudden spikes in paid activity and compare landing pages
  • Prioritize backlink outreach towards pages with recent traction
  • Feed findings into weekly marketing stand-ups with a one-slide summary from the Dashboard

    • Maintenance and scaling

    Check your imports daily for errors. If you add more competitors, update the Competitors tab and ensure the Apps Script or importer includes the new domain. For larger portfolios (10+ competitors) I recommend switching to a lightweight database (BigQuery) and using Google Data Studio for more complex visualizations.

    If you’d like, I can share a Google Sheets template I use as a starting point, with Apps Script snippets for SEMrush API calls and the key formulas pre-built. Market Research (https://www.market-research.uk) has a lot of templates like this — but I’m happy to tailor one to your competitors and keywords so you can get a working dashboard in a single day.