Tabellab

Watch-Through Rate & Average Watch Time: Reading the Two Numbers That Show How Well a Short Video Holds Its Viewers

Lesson 1 of 20Lab

All lessons
  1. Watch-Through Rate & Average Watch Time: Reading the Two Numbers That Show How Well a Short Video Holds Its ViewersLabFree
  2. Retention Curves: Viewers Still Watching per Second, the Hook Rate and Where a Video Loses PeopleLab Locked
  3. For You vs Follower Views: Traffic Sources per Video, Source Shares and the Account MixLab Locked
  4. Engagement by Views: Engagement Rate, Shares per 1,000 Views and a Weighted Interaction ScoreLab Locked
  5. Posting Cadence: Days Between Posts, Posts per Week with COUNTIFS and Views per Post with SUMIFSLab Locked
  6. Posting Time Slots: Average Views per Slot with AVERAGEIFS, an Index Against the Account Average and a Highlight RuleLab Locked
  7. Sound & Trend Tests: Average Views by Audio Type, Pooled Watch-Through Rate and the Lift Against Original AudioLab Locked
  8. From Views to Link Clicks: Profile Visit Rate, Link Click Rate and Clicks per 1,000 ViewsLab Locked
  9. Hook Rate and Hold Rate: Splitting Watch-Through into Two Steps and Diagnosing Each Video with IFSLab Locked
  10. Video Length Buckets: Watch-Through and Watch Time by Length with Two-Condition COUNTIFS and SUMIFSLab Locked
  11. Follower Growth: Net New Followers, Weekly Growth Rate, Followers per 1,000 Views and a Rule for Shrinking WeeksLab Locked
  12. Typical Performance: Mean vs Median Views, the Share of the Top Three Videos and an Index Against the MedianLab Locked
  13. The Lifetime of a Video: Cumulative Views, the Share Reached per Day and When to Judge a PostLab Locked
  14. Content Pillars: Views per Video and Profile Visits per 1,000 Views by Pillar, a Rank with RANK.EQ and a Lookup with XLOOKUPLab Locked
  15. A Posting Frequency Test: Views per Post, the Slope of Weekly Views on Posts and a Forecast for a New CadenceLab Locked
  16. Follower Forecast: Weekly Gains, the Trend with SLOPE, a Forecast with FORECAST.LINEAR and Weeks Until a GoalLab Locked
  17. Quality Checks on an Analytics Export: Duplicate Rows, Impossible Numbers, Replay Loops and a Clean TotalLab Locked
  18. Paired Video Tests: Difference in Percentage Points, Relative Lift and a Verdict with Minimum ViewsLab Locked
  19. What-If Grid: Monthly Link Clicks for Every Mix of Views per Post and Profile Visit Rate, a Heat Map and the Numbers Needed for a TargetLab Locked
  20. Final Project: Monthly Organic Video Scorecard with Watch-Through Rate, For You Share, Link Clicks per 1,000 Views, a Flag and a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why views alone tell you so little

Open the analytics of any short-video account and the first number you see is views. It is also the least informative one. On TikTok and similar feeds a view is counted almost the moment a video starts to play, so a person who swiped away after half a second and a person who watched three times in a row both count as one view. If you want to know whether a video was any good, you have to look at what happened after the view started.

Two numbers do that job: average watch time, which tells you how long people stayed, and watch-through rate, which tells you how many reached the end. Recommendation feeds are built to keep people watching, so videos that hold attention tend to be shown to more people. Nobody outside the platform knows the exact recipe and it changes over time, but the direction is stable enough to plan with: hold people longer, and the video usually travels further.

This free lesson shows you how to calculate both numbers in a spreadsheet, how to make videos of different lengths comparable, and how to work out a correct rate for a whole account. The example is Quillfeather Paper Co., a fictional small stationery brand that posts short videos about wax seals, fountain pens and notebooks. The company, the videos and all numbers are invented for learning, and every benchmark mentioned is illustrative.

The three raw numbers you need

Everything in this lesson is built from numbers you can read in the analytics of a single video and copy into a sheet. You need only three of them plus the length of the video:

  • Views: how many times the video started to play.
  • Play time (often shown as total play time or total watch time): all seconds watched, added up over every view. Some tools show hours; multiply by 3,600 to get seconds.
  • Full watches: the number of views that reached the last second. Many dashboards show this as a percentage ("watched full video"); multiply that percentage by the views to get the count, or use the percentage directly.
  • Video length in seconds, which you know yourself.

From these you derive three ratios. Average watch time = play time ÷ views. Watch-through rate (also called completion rate) = full watches ÷ views. Average percentage watched = average watch time ÷ video length. The first is measured in seconds, the other two are shares between 0 % and 100 % (the percentage watched can exceed 100 % when people replay a clip, a case the course returns to in a later lab).

One video, three numbers: Desk reset in 20 secondsaverage watch time 9.8 s46.7 % of the length12,400 views start2,976 reach the end (24 %)0 s21 sPlay time = 12,400 views × 9.8 s = 121,520 secondsAverage watch time measures depth, watch-through rate measures arrival (fictional data)
The 21-second video "Desk reset in 20 seconds": 12,400 views, an average watch time of 9.8 seconds and 2,976 full watches. The average viewer leaves before the middle, yet about one view in four reaches the last second.

Average watch time: depth in seconds

Take the video Desk reset in 20 seconds. It is 21 seconds long, collected 12,400 views and 121,520 seconds of play time. Divide: 121,520 ÷ 12,400 = 9.8 seconds. The average viewer stayed for just under ten seconds. In a spreadsheet with play time in column D and views in column C this is =D5/C5.

Average watch time is an average in the full sense of the word: it mixes people who left after one second with people who watched everything. A value of 9.8 seconds does not mean that most people left at second ten. It means that all the seconds, shared out evenly over all views, would give each view 9.8 seconds. To see where people leave you need the retention curve, which is the subject of lab 2.

Watch-through rate: who arrives at the end

The same video had 2,976 full watches. 2,976 ÷ 12,400 = 24 %. Roughly one view in four reached the last second. The formula is =E5/C5, formatted as a percentage. For How to fold a letter, a 34-second tutorial, the rate is 1,290 ÷ 8,600 = 15 %.

Watch-through rate is a hard, simple yardstick, and for very short clips it is the one most creators look at first. Its weakness is that it only counts arrivals. A view that stopped one second before the end counts the same as a view that stopped after one second. That is why it should never be read without the average watch time next to it.

Average percentage watched: making lengths comparable

Seconds cannot be compared between a 12-second clip and a 58-second studio tour. Dividing the average watch time by the length solves that. Ink swatch test is 18 seconds long with an average watch time of 11.9 seconds: 11.9 ÷ 18 = 66.1 %. In one formula from the raw numbers: =D7/C7/B7, play time divided by views divided by length.

Now the comparison is fair. Studio tour has the second-longest average watch time of all twelve videos, 14.5 seconds, but that is only 25 % of its 58 seconds. Fountain pen mistake holds people for 9.9 seconds, fewer than the studio tour, yet that is 82.5 % of the clip. Which one is "better" depends on the job: the short clip is finished and probably replayed, the long one delivers more seconds of attention per view.

A fully worked example: twelve videos of Quillfeather Paper Co.

Here are all twelve videos with the three raw numbers and the three calculated columns from the lab above, measured seven days after posting.

VideoLengthViewsPlay time sFull watchesAvg watch timeWatch-throughAvg % watched
Wax seal ASMR14 s48,200539,84022,17211.2 s46 %80.0 %
Desk reset in 20 seconds21 s12,400121,5202,9769.8 s24 %46.7 %
How to fold a letter34 s8,600112,6601,29013.1 s15 %38.5 %
Ink swatch test18 s22,900272,5108,70211.9 s38 %66.1 %
Packing an order27 s6,10063,4401,03710.4 s17 %38.5 %
Fountain pen mistake12 s31,500311,85016,3809.9 s52 %82.5 %
Notebook flip-through45 s4,30054,18034412.6 s8 %28.0 %
Studio tour58 s3,90056,55023414.5 s6 %25.0 %
Calligraphy alphabet31 s15,800240,1603,31815.2 s21 %49.0 %
Reply: ink smudge fix16 s9,700103,7903,97710.7 s41 %66.9 %
Planner setup39 s7,20092,88079212.9 s11 %33.1 %
Paper weight explained24 s5,40044,8207568.3 s14 %34.6 %
All videos176,0002,014,20061,97811.4 s35.2 %

These are the formulas in the three calculated columns. Each is typed once in row 4 and then filled down as far as row 15:

  • Average watch time (F4): =D4/C4, play time ÷ views.
  • Watch-through rate (G4): =E4/C4, full watches ÷ views, formatted as a percentage.
  • Average percentage watched (H4): =D4/C4/B4, the average watch time divided by the length. If column F is already filled, =F4/B4 gives the same result.

Read the table from the top. Wax seal ASMR is the reach winner with 48,200 views, and it earned them: 46 % watch-through and 80 % of the clip watched on average. Fountain pen mistake has the highest watch-through rate of all, 52 %, on the shortest video. At the other end, Studio tour is finished by only 6 % of its views. The five videos of 30 seconds or more all sit below 25 % watch-through, while every clip under 20 seconds is above 35 %.

Watch-through rate by videoFountain pen mistake (12 s)52 %Wax seal ASMR (14 s)46 %Reply: ink smudge fix (16 s)41 %Ink swatch test (18 s)38 %Desk reset in 20 seconds (21 s)24 %Calligraphy alphabet (31 s)21 %Packing an order (27 s)17 %How to fold a letter (34 s)15 %Paper weight explained (24 s)14 %Planner setup (39 s)11 %Notebook flip-through (45 s)8 %Studio tour (58 s)6 %Account: 35.2 %Full watches ÷ views, 7 days after posting (fictional data)
Watch-through rate of the twelve videos, sorted. Green bars are above the pooled account rate of 35.2 %, orange bars below. The short clips lead; the two longest videos are at the bottom.

The bar chart makes the pattern obvious, and it also shows the trap. If you ranked videos by watch-through rate alone you would conclude that Quillfeather Paper Co. should only ever post twelve-second clips. The next figure adds the missing half of the picture.

Longer videos, smaller share watched0 s20 s40 s60 s0 %25 %50 %75 %100 %Fountain pen mistake: 82.5 %Studio tour: 25.0 %Calligraphy alphabet: 49.0 %Video length in seconds vs average percentage watched (fictional data)
Each dot is one video. The share of the video that the average viewer watches falls as videos get longer, which is why seconds and percentages must be read together.

The account rate: add up first, then divide

What is the watch-through rate of the whole account? The tempting answer is to average the twelve percentages. That gives 24.4 %. The correct answer is to add up all full watches and divide by all views: 61,978 ÷ 176,000 = 35.2 %. In the sheet: =SUM(E4:E15)/SUM(C4:C15).

The two answers differ by more than ten percentage points, and the reason is weighting. A plain average of percentages gives Studio tour with 3,900 views the same say as Wax seal ASMR with 48,200. But the question "what share of our views reached the end?" is about views, not about videos, and most views belong to the short, strong clips. The pooled rate answers that question; the simple average answers a different one ("how does a typical video do?"), for which a later lab uses the median instead.

The same rule gives the account's average watch time: =SUM(D4:D15)/SUM(C4:C15), which is 2,014,200 ÷ 176,000 = 11.4 seconds per view. Remember the rule as sum first, divide afterwards. It applies to every ratio in this course, from For You shares to link clicks per 1,000 views.

Dashboards differ in how they define a view, a full watch and play time, and definitions change. Do not mix numbers from different platforms or different export dates in one column. The arithmetic in this lesson stays valid whatever the definition, as long as every row uses the same one.

Step by step in Excel or Google Sheets

  1. Put one video per row with four typed columns: length in seconds, views, play time in seconds and full watches. Keep the header in row 3 and the data in rows 4 to 15 if you want to follow the lab exactly.
  2. Click F4 and type =D4/C4. Press Enter. Format the cell with one decimal place.
  3. Click G4 and type =E4/C4. Format it as a percentage (Ctrl + Shift + %).
  4. Click H4 and type =D4/C4/B4. Format it as a percentage as well.
  5. Select F4:H4 and double-click the small square at the bottom-right corner of the selection (the fill handle). The three formulas are copied down to row 15, and the row numbers adjust automatically.
  6. In a free cell type =SUM(E4:E15)/SUM(C4:C15) for the account watch-through rate and =SUM(D4:D15)/SUM(C4:C15) for the account average watch time.
  7. Sort the table by watch-through rate or by average percentage watched (Data › Sort) and look at the top and bottom three videos. Write down what they have in common before you look at anything else.
Always record the numbers at the same age of the video, for example seven days after posting. A video that is two days old cannot be compared with one that has been collecting views for two months. Lab 13 of the course shows how to find the right waiting time from the lifetime curve of your own videos.

How to read the result

Three patterns come up again and again when you have the three columns side by side.

  • High watch-through, high percentage watched, short video. The clip is easy to finish and is probably replayed. Good for reach. Check in the traffic sources (lab 3) whether the recommendation feed picked it up.
  • Low watch-through but many seconds watched. A longer video that holds a core audience. It will rarely win on completion, but it may win on profile visits and followers, because people who watch fifteen seconds of a tutorial are interested in the subject.
  • Low on everything. The opening did not stop the scroll or the video did not deliver what the first second promised. This is where the retention curve and the hook rate (labs 2 and 9) tell you which of the two it was.

For Quillfeather Paper Co. the reading is clear. The sensory, wordless clips under 20 seconds carry the reach. The tutorials between 30 and 40 seconds have modest completion but respectable watch times of roughly 13 to 15 seconds. The two longest videos have the lowest completion and the lowest share watched, so the next test would be a 25-second cut of the studio tour rather than another full-length one.

Common mistakes and how to spot them

  • Averaging percentages. =AVERAGE(G4:G15) is not the account watch-through rate. Use the sums of full watches and views.
  • Dividing the wrong way round. =C4/E4 gives numbers above 1, such as 2.17. A rate above 100 % in column G is the signal that numerator and denominator are swapped.
  • Mixing units. Play time exported in hours and length in seconds gives an average watch time that is 3,600 times too small. Convert everything to seconds first.
  • Comparing seconds across lengths. Fourteen seconds is excellent for a 15-second clip and weak for a 60-second one. Compare percentages, or compare seconds only within a length group.
  • Typing results instead of formulas. A typed 0.24 does not update when you correct the views. The Coach in the lab rejects typed results for exactly that reason.
  • Judging too early. In the first hours the numbers rest on few views and swing widely. Fix one measuring date for all videos.
  • Chasing completion with tricks. A misleading first second raises the hook and lowers trust. Measure honestly and improve the content, not the bait.

Quick reference: formulas in this lesson

MeasureDefinitionFormula (row 4)
Average watch timePlay time ÷ views=D4/C4
Watch-through rateFull watches ÷ views=E4/C4
Average percentage watchedAverage watch time ÷ length=D4/C4/B4
Account watch-through rateAll full watches ÷ all views=SUM(E4:E15)/SUM(C4:C15)
Account average watch timeAll play time ÷ all views=SUM(D4:D15)/SUM(C4:C15)

Where this is used in practice

  • Weekly content review. Paste the week's videos into the sheet, sort by percentage watched and discuss the top two and bottom two. Ten minutes, and the team argues about evidence instead of taste.
  • Deciding on length. When a topic can be told in 15 or in 40 seconds, the history of both columns tells you what each length usually earns for your account.
  • Briefing an editor or freelancer. "Average percentage watched above 60 % for clips under 20 seconds" is a brief someone can work to; "make it more engaging" is not.
  • Reporting to a client or manager. The pooled account rate, month by month, is one honest line on a chart. It is harder to inflate than views and says more about content quality.

What the other nineteen labs cover

Lesson 1 is free. Labs 2 to 20 each have their own live sheet and build on today's two numbers, from the retention curve through traffic sources, cadence and sound tests to the path from profile visits to link clicks and a final monthly scorecard.

  • Lab 2: Retention Curves. You turn the viewers still watching at each second into a retention curve, find the hook rate after three seconds, measure how much of the drop-off happens early and chart the curve.
  • Lab 3: For You vs Follower Views. You add up the traffic sources of each video, calculate the For You and Following shares, find the mix of the whole account and chart the sources as stacked columns.
  • Lab 4: Engagement by Views. You calculate an engagement rate on views, shares per 1,000 views and a weighted interaction score with locked weights, then the engagement rate of the whole account.
  • Lab 5: Posting Cadence. You measure the gap between posts from the dates, count posts and add views per week with COUNTIFS and SUMIFS, and compare views per post across weeks.
  • Lab 6: Posting Time Slots. You count posts per time slot, average their first-day views with AVERAGEIFS, index each slot against the account average and highlight the strongest posts with conditional formatting.
  • Lab 7: Sound & Trend Tests. You compare trending sounds, original audio and voice-over: videos per group, average views, pooled watch-through rate and the lift against the baseline, with a column chart.
  • Lab 8: From Views to Link Clicks. You follow the organic funnel from video views to profile visits to clicks on the profile link, week by week and for the whole period, and find how many views one click takes.
  • Lab 9: Hook Rate and Hold Rate. You split watch-through into hook rate and hold rate, label each video Fix hook, Fix middle or Keep with IFS against house targets, and pool the hook rate per hook type.
  • Lab 10: Video Length Buckets. You sort sixteen videos into length buckets with two-condition COUNTIFS and SUMIFS, compare watch-through rate and average watch time per bucket and chart the result.
  • Lab 11: Follower Growth. You calculate net new followers, the weekly growth rate and net followers per 1,000 views, pool twelve weeks and flag the weeks in which the account shrank.
  • Lab 12: Typical Performance. You compare the mean and the median of fifteen videos, measure how much of all views the top three hold, recalculate the mean without the outlier and index each video against the median.
  • Lab 13: The Lifetime of a Video. You build the cumulative view curve of one video, see what share of its 14-day views arrives in the first days, follow the For You share day by day and chart the curve.
  • Lab 14: Content Pillars. You summarise eighteen videos by content pillar, compare reach and profile visits per 1,000 views, rank the videos with RANK.EQ and look up the pillar of the best one with XLOOKUP.
  • Lab 15: A Posting Frequency Test. You read a ten-week frequency test: views per post at each cadence, the extra weekly views one more post brings with SLOPE, the correlation, and a forecast for a planned number of posts.
  • Lab 16: Follower Forecast. You calculate weekly follower gains, fit a trend with SLOPE, forecast a future week with FORECAST.LINEAR, estimate the weeks until a goal with ROUNDUP and chart the series.
  • Lab 17: Quality Checks on an Analytics Export. You audit a raw export before reporting: highlight duplicate IDs, label each row OK, Loops or Error with IFS, count the errors and the distinct videos and total the views of the clean rows.
  • Lab 18: Paired Video Tests. You compare two versions of a video in eight tests: the difference in watch-through in percentage points, the relative lift, a verdict that respects minimum views, and the count of valid tests.
  • Lab 19: What-If Grid. You build a two-way grid of monthly link clicks with mixed references, turn it into a heat map and work backwards from a target to the views per post or the profile visit rate you need.
  • Lab 20: Final Project. You build a monthly scorecard from sixteen videos: views, pooled watch-through rate, For You share and link clicks per 1,000 views, a rule that flags months below target and a combo chart.

Summary

A view says a video started; watch time and watch-through say whether it was worth starting. Average watch time is =D4/C4, watch-through rate is =E4/C4, and the average percentage watched, =D4/C4/B4, makes videos of different lengths comparable. For the whole account, sum first and divide afterwards: Quillfeather Paper Co. has a pooled watch-through rate of 35.2 % and an average watch time of 11.4 seconds, not the 24.4 % that a simple average of the twelve rates would suggest.

The lab at the top of this page lets you build the sheet yourself. Plan around a quarter of an hour; each formula is checked the moment you enter it, and at the end you have a template you can fill with the numbers of your own videos. Nothing in it promises more views; it shows you clearly which of your videos held people, and that is the starting point for every decision in the labs that follow.

Frequently asked questions

What is watch-through rate on TikTok?

Watch-through rate, also called completion rate, is the share of views that reached the end of the video: full watches divided by views. If 2,976 of 12,400 views reached the last second, the rate is 24 %.

How do I calculate average watch time?

Divide the total play time by the number of views, with both in the same unit. 121,520 seconds of play time from 12,400 views is an average watch time of 9.8 seconds. In a spreadsheet: =D4/C4.

What is a good watch-through rate for a short video?

There is no universal number. It depends heavily on length: clips under 15 seconds are finished far more often than videos of 45 seconds. Compare each video with your own videos of similar length rather than with a figure from the internet.

Why should I not average the watch-through rates of my videos?

A simple average gives a video with 4,000 views the same weight as one with 48,000. For the rate of the whole account, add up all full watches and divide by all views: =SUM(E4:E15)/SUM(C4:C15).

What is the difference between average watch time and average percentage watched?

Average watch time is measured in seconds. Average percentage watched divides it by the video length, which makes a 12-second clip and a 58-second video comparable. Use both: seconds show depth, the percentage shows fit to the length.

Can average watch time be longer than the video?

Yes. When viewers let a short clip loop, the replays add play time to the same view, and the average watch time can exceed the length. That is a good sign for a very short video, not a data error.

Keep going: TikTok Organic Content Analytics: Watch-Through Rate, Average Watch Time, For You vs Follower Views, Posting Cadence, Sound Tests & Profile-to-Link Clicks

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Retention Curves: Viewers Still Watching per Second, the Hook Rate and Where a Video Loses People
  2. For You vs Follower Views: Traffic Sources per Video, Source Shares and the Account Mix
  3. Engagement by Views: Engagement Rate, Shares per 1,000 Views and a Weighted Interaction Score
  4. Posting Cadence: Days Between Posts, Posts per Week with COUNTIFS and Views per Post with SUMIFS
  5. Posting Time Slots: Average Views per Slot with AVERAGEIFS, an Index Against the Account Average and a Highlight Rule
  6. Sound & Trend Tests: Average Views by Audio Type, Pooled Watch-Through Rate and the Lift Against Original Audio
  7. From Views to Link Clicks: Profile Visit Rate, Link Click Rate and Clicks per 1,000 Views
  8. Hook Rate and Hold Rate: Splitting Watch-Through into Two Steps and Diagnosing Each Video with IFS
  9. Video Length Buckets: Watch-Through and Watch Time by Length with Two-Condition COUNTIFS and SUMIFS
  10. Follower Growth: Net New Followers, Weekly Growth Rate, Followers per 1,000 Views and a Rule for Shrinking Weeks
  11. Typical Performance: Mean vs Median Views, the Share of the Top Three Videos and an Index Against the Median
  12. The Lifetime of a Video: Cumulative Views, the Share Reached per Day and When to Judge a Post
  13. Content Pillars: Views per Video and Profile Visits per 1,000 Views by Pillar, a Rank with RANK.EQ and a Lookup with XLOOKUP
  14. A Posting Frequency Test: Views per Post, the Slope of Weekly Views on Posts and a Forecast for a New Cadence
  15. Follower Forecast: Weekly Gains, the Trend with SLOPE, a Forecast with FORECAST.LINEAR and Weeks Until a Goal
  16. Quality Checks on an Analytics Export: Duplicate Rows, Impossible Numbers, Replay Loops and a Clean Total
  17. Paired Video Tests: Difference in Percentage Points, Relative Lift and a Verdict with Minimum Views
  18. What-If Grid: Monthly Link Clicks for Every Mix of Views per Post and Profile Visit Rate, a Heat Map and the Numbers Needed for a Target
  19. Final Project: Monthly Organic Video Scorecard with Watch-Through Rate, For You Share, Link Clicks per 1,000 Views, a Flag and a Combo Chart

Buy course — $9.90 See the full course