Social media competitor analysis template

Most competitor analysis templates are a grid of vanity metrics with an email box in front of them. This one is on the page. Fourteen columns: twelve you type, two the sheet works out. All of it is built around the only number in the sheet that ranks anything, which is how far a post beat what that account normally gets.

It assumes you already film and cut. If your problem is what to make rather than how to make it, this is the sheet.

Copy the header row

Tab separated. Paste the first line into A1 of a blank sheet and Google Sheets or Excel splits it into fourteen columns on its own. The second line is a filled example so you can see the shape before you delete it.

Date	Account	Platform	Post URL	Format	Hook type	Hook first 3 seconds	Views	Account median views	Ratio vs median	Comments	Comments per 1k views	What to steal	What to skip
2026-09-01	@a.competitor	Instagram	https://www.instagram.com/reel/EXAMPLE/	Talking head, one location, no b-roll	Contrarian claim	"Stop rewriting your hook. It is not the hook."	48200	11500	4.19	612	12.7	Open on the objection, not the promise	The 40 second rant, our audience leaves at 12

That is the whole template. No download, no email, no second page with the good bits on it.

What goes in each column

Column	What goes in it
`Date`	The day it was posted, not the day you logged it. Formats date fast now: Susan Li told Meta’s Q1 2026 earnings call on 29 April 2026 that same-day posts are more than 30 percent of recommended Reels, more than double a year earlier. A row without a date is a row you can never age out.
`Account`	The handle, spelled exactly as it appears. This is the key the whole baseline hangs off, so a typo here quietly breaks column J.
`Platform`	Instagram, TikTok or YouTube. The same handle has a different median on each one. A row without this is a row you cannot trust.
`Post URL`	The link. A row you cannot rewatch is a row you will end up arguing about in a meeting.
`Format`	What you would have to book to make it. Talking head, voiceover over b-roll, screen recording, product demo, skit, carousel. This is the line an editor actually needs.
`Hook type`	The mechanic, not the topic. Contrarian claim, POV, before and after, number promise, cold open on the result, direct question. Keep the list short enough that it stays sortable.
`Hook first 3 seconds`	The opening line, typed out word for word, plus any text on screen. This is the most useful thirty seconds you will spend on a row. Hook type is a category. The verbatim line is the thing you can actually rewrite.
`Views`	Whatever the post shows. On its own it means close to nothing, which is why it is column H and not column A.
`Account median views`	The middle value of that account’s last 30 posts. How to get it is below.
`Ratio vs median`	Views divided by that account’s median. The sort column, and the reason this template exists. 1.0 is their ordinary day.
`Comments`	The raw count. Public on every platform, unlike saves and sends.
`Comments per 1k views`	Comments divided by views, times a thousand. Separates a lot of people saw this from a lot of people had something to say about it.
`What to steal`	One sentence. The mechanic that would survive being moved to your account. Adam Mosseri said on 22 January 2025 that watch time, likes and sends are the top three ranking signals, with sends slightly more important for reaching people who do not already follow the account. So the useful question here is not did people like this, it is why would someone send this to one specific person.
`What to skip`	One sentence. The part that only worked because of their face, their budget or the audience they already have. Leaving this column blank is how teams end up copying a competitor badly.

Ratio vs median, the column everything sorts by

Raw views rank accounts, not posts. Sort a sheet by column H and you get a list of the biggest accounts you track, in order of size, which you already knew when you added them.

Sort by column J and something else happens.

Post	Views	Their median	Ratio
Small account, one good Tuesday	12,000	3,000	4.0x
Large account, ordinary post	400,000	500,000	0.8x
Mid account, format experiment	61,000	22,000	2.8x
Large account, genuine hit	1,900,000	500,000	3.8x

Row two has 33 times the views of row one and is the least interesting post in the table. It is a big account having a slow day. Row one is a lesson: something in that hook worked on an audience that normally ignores this account. That is the post you rewatch.

Read the column like this:

Under 1.0. Below their normal. Skip it, unless you are deliberately keeping a record of what fails.
1.0 to 1.5. Noise. Every account has good days.
1.5 to 3.0. Worth a row and a rewatch.
Over 3.0. Worth a meeting. Something in that hook or format did work that their usual posts do not.

How to get the account median

Open the account. Take the view counts of the last 30 posts. Sort them and take the middle one. That is the median.

Use the median, not the average. One post at two million drags an average so far up that every other post on the account reads as a failure, including the ones you should be studying. The median ignores the freak result.

Two rules keep the number honest.

Compare like ages. A post from Tuesday has not finished counting. Either wait a week before you log a row, or only compare posts sitting in the same rough age band.
Recompute monthly. A growing account moves its own goalposts. A median from March flatters everything you log in September.

A filled row, start to finish

Made up numbers, round on purpose, so you can see what a finished row reads like.

`Date`	2026-09-01
`Account`	@a.competitor
`Platform`	Instagram
`Post URL`	instagram.com/reel/EXAMPLE/
`Format`	Talking head, one location, no b-roll
`Hook type`	Contrarian claim
`Hook first 3 seconds`	“Stop rewriting your hook. It is not the hook.” No text on screen for the first two seconds.
`Views`	48,200
`Account median views`	11,500
`Ratio vs median`	4.19
`Comments`	612
`Comments per 1k views`	12.7
`What to steal`	Opening on the objection instead of the promise. Works on any topic where the audience has already been sold something that did not deliver.
`What to skip`	The 40 second rant. Their audience knows them and will sit through it. Ours leaves at 12 seconds.

The columns I left out, and why

Saves and shares. You cannot fill them. TikTok shows both on a public video; Instagram shows neither on someone else’s post. A column you can only fill on half your rows is a column that quietly stops getting filled, and then so does the sheet.
Likes. On short-form video, likes track views closely enough that a likes column mostly restates column H. Comments per thousand views tells you something likes do not.
Follower count. The median already normalises for audience size, which was the entire job follower count was doing. It also keeps changing while the post does not.
Hashtags. Log them if you enjoy it, but they will not tell you why a post beat its own baseline, and they roughly double the time each row takes.
Posting time. Their best hour is a fact about their audience. Your own analytics answer this question properly, about your audience, for free.

Rebuild it in a spreadsheet

Paste the header row into A1 and you are most of the way there. Two cells do work for you.

The two formulas

J2   =IF(I2=0, "", H2/I2)
L2   =IF(H2=0, "", K2/H2*1000)

Fill both down. Then freeze the top row and sort by column J, largest first. That sort is the deliverable. Everything above it is just getting the denominator right.

A baseline tab, so you type each median once

Second sheet, call it Baselines. Column A the handle, columns B to AE the view counts of that account’s last 30 posts, column AF the median.

Baselines!AF2   =MEDIAN(B2:AE2)
Main sheet I2   =VLOOKUP(B2, Baselines!A:AF, 32, FALSE)

Update the Baselines tab once a month. Nothing else on it needs touching.

A weekly rhythm that survives contact with a Monday

Ten rows a week. Five accounts, the two highest ratio posts from each. Twenty minutes if you do not rewatch, forty if you do, and you should.

At ten rows a week you have two hundred rows in five months, which is enough that column F stops being a guess. Two or three hook types will show up again and again in your niche. That is not laziness on their part. That is the shape their audience responds to, and it is the most useful thing this sheet will ever tell you.

This is the manual version of what Silma does

Straight version, since you got this far. The read above is exactly what Silma runs, done by hand.

It watches the accounts you name, pulls their recent posts daily, computes each account’s own median, and ranks what it finds by how far each post beat that number. Same denominator, same sort. It also does the slow part of columns F and G, which is watching the video and naming the hook. That is competitor tracking for accounts you already know about, and Discover for outliers in your niche from accounts you have never heard of.

What it does not do: film, edit, schedule or post. Plans start at $9 a month.

The sheet is free and it works. Use it. A tool earns its place at the point where the arithmetic is the bottleneck rather than the judgement.

Questions

Is the template really free?+

Yes. Every column, both formulas and a filled example row are on this page. There is no download, no email box, and no second version with the good columns in it.

Why the median and not the average?+

One post at two million views drags an average so far up that every other post on that account reads as a failure, including the ones worth studying. The median ignores the freak result and tells you what a normal day looks like for that account.

Does this work for TikTok and YouTube Shorts?+

Yes. Column C exists for exactly that. Keep a separate median per account per platform, because the same handle rarely performs the same on both, and a ratio built on a mixed baseline is worse than no ratio.

How many posts should I log a week?+

Ten. Five accounts, the two strongest posts from each. Fewer and the patterns take months to surface. More and you stop rewatching, which is where the value of columns F, G and M comes from.

Do I need a tool for this?+

No. A spreadsheet and this page do the job. A tool earns its place when the arithmetic and the daily scanning start eating the hours you should be spending on the work itself.

Sources

Two figures on this page carry a name and a date. Everything else is method, not data.

Susan Li, Meta Q1 2026 earnings call, 29 April 2026. Same-day posts are more than 30 percent of recommended Reels on Instagram and Facebook, more than double the level a year earlier.
Adam Mosseri, Instagram, 22 January 2025. Watch time, likes and sends are the top three ranking signals, with sends slightly more important for content shown to people who do not follow the account.