Ppc competitor analysis excel

Create the workbook structure: 6 sheets with named ranges

Open a new Excel workbook and create six sheets: Keywords, Competitors, AdCopy, LandingPages, AuctionInsights, ActionSheet. Define a named range on each sheet's data area so subsequent formulas reference the range, not absolute cell addresses. Excel's named-range discipline is what keeps the workbook readable in month 6 when you've forgotten what's in row 247 of the AdCopy sheet.

# Named ranges to define (Formulas → Name Manager):
Keywords.Data       = Keywords!$A$2:$E$1000
Competitors.Data    = Competitors!$A$2:$F$100
AdCopy.Data         = AdCopy!$A$2:$H$5000
LandingPages.Data   = LandingPages!$A$2:$I$500
AuctionInsights.Data = AuctionInsights!$A$2:$G$5000
ActionSheet.Data    = ActionSheet!$A$2:$F$100

Build the Keywords sheet with spend-ranked formulas

Columns: Keyword, MonthlySpend, MonthlyImpressions, YourCPC, YourCVR. Use RANK.EQ to add a Rank column that auto-sorts by spend descending. Conditional format the top 20 rows green - those are your audit anchors. The Excel-specific advantage here is the RANK.EQ formula auto-updates when you paste in next month's spend data, so the audit anchors refresh themselves.

# Formula in cell F2 (Rank column):
=RANK.EQ([@MonthlySpend], Keywords[MonthlySpend])
# Conditional format rule:
=$F2<=20  →  fill green

Build the Competitors sheet with a multi-source flag

Columns: Domain, FoundIn_AuctionInsights, FoundIn_SpyFu, FoundIn_SEMrush, SourceCount, ConfidenceTier. The FoundIn columns are TRUE/FALSE. SourceCount sums them with =COUNTIF on the row. ConfidenceTier uses nested IFs: 3 sources = 'High', 2 = 'Medium', 1 = 'Low'. Filter the sheet to High-confidence only when planning audit time allocation.

# SourceCount formula:
=COUNTIF([@[FoundIn_AuctionInsights]:[FoundIn_SEMrush]], TRUE)
# ConfidenceTier formula:
=IF([@SourceCount]=3, "High", IF([@SourceCount]=2, "Medium", "Low"))

Build the AdCopy sheet with XLOOKUP-linked competitor data

Columns: Competitor, Keyword, Headline1, Headline2, Description, FirstSeen, LastSeen, PatternTag. Add an XLOOKUP formula in a helper column that pulls each competitor's ConfidenceTier from the Competitors sheet - so when you sort AdCopy, you can immediately filter to High-confidence competitors only. The PatternTag column should use Data Validation with a fixed list: price-led, social-proof, urgency, feature-led, comparison.

# Helper column formula:
=XLOOKUP([@Competitor], Competitors[Domain], Competitors[ConfidenceTier], "Unknown")
# Data Validation source for PatternTag:
price-led,social-proof,urgency,feature-led,comparison

Build the LandingPages sheet with a pivot-ready structure

Columns: Competitor, URL, PageType, HeroOffer, CTAText, SocialProof, WordCount, VideoPresent, FormLength. Insert a PivotTable on a separate sheet aggregating by PageType and VideoPresent - this immediately surfaces 'how many competitor landing pages use video vs not' and 'which page types dominate'. PivotTables are the Excel feature that turns raw landing-page rows into insight in 30 seconds.

Build the AuctionInsights sheet as a time-series with sparklines

Columns: Month, Competitor, ImpressionShare, OverlapRate, PositionAboveRate, OutrankingShare, AbsoluteTopShare. Each monthly export adds rows; the sheet grows over time. Insert sparklines (Insert → Sparklines → Line) showing each competitor's impression share trend over the last 6 months in a summary column. Excel sparklines are the cheapest competitive-trend visualization in any tool.

# Pivot to produce per-competitor monthly time series:
# Rows: Competitor
# Columns: Month
# Values: AVERAGE of ImpressionShare
# Then insert sparkline referencing the row range

Build the ActionSheet as the workbook's only output tab

Columns: Owner, Category (Bidding / AdCopy / LandingPage), Change, EvidenceSource (which sheet/row sourced this), ExpectedImpact (High/Med/Low), ShipDate, Status. Use Data Validation on Category, ExpectedImpact, and Status. Conditional format Status: green for Shipped, yellow for In Progress, red for Blocked. The ActionSheet is the only sheet leadership sees - all other sheets are scaffolding.