What you actually buy when you buy a YouTube ad
Most online advertising is sold by the impression or by the click. Video on YouTube has a third currency that confuses many people at first: the view. With the most common video format, the skippable in-stream ad, you can let your ad start a million times and pay only for the people who chose to keep watching. That makes two numbers the foundation of every video report: the view rate, which says how many of the people who were shown the ad stayed, and the cost per view (CPV), which says what one of those stays cost.
This free lesson shows how to calculate both in Excel, for single ads and for a whole account, and how to avoid the mistake that makes many video reports quietly wrong: averaging percentages. You do not need any advertising background. If you can divide one cell by another, you can follow every step, and the interactive sheet above checks each formula as you type it.
The example company is Tarnwick Cycles, a fictional maker of e-bikes that is launching a model called the Fell 3. The company, its ads and every number are invented for learning, and the amounts are illustrative. Real prices and platform rules differ by country and change over time, so learn the arithmetic here and take the levels from your own account.
How a skippable in-stream ad works
A skippable in-stream ad plays before, during or after another video. For the first few seconds the viewer has to watch. Then, after about five seconds, a button appears that lets them skip to the video they came for. Some people press it at once, some watch a little longer and leave, and some watch to the end.
Two events are counted. An impression is counted each time the ad starts to play. A view is counted only when the viewer shows real interest. The usual convention for this format is that a view is counted when someone watches 30 seconds of the ad, or the whole ad if it is shorter than 30 seconds, or interacts with it, for example by clicking through to the website. If you bid on a cost-per-view basis, you pay when a view is counted and not before.
The consequence is unusual and worth pausing on. A viewer who skips after six seconds has still seen your brand name, your product and perhaps your main claim, and you paid nothing for it. Good video advertisers therefore put the brand into the first five seconds: the free part of the audience is usually the larger part.
The two core metrics: view rate and CPV
- View rate = views ÷ impressions. It is a percentage. A view rate of 30 % means that three of every ten people who were shown the ad became a view.
- Cost per view (CPV) = cost ÷ views. It is an amount of money, usually a few cents, so it is shown with three decimals.
- CPM, the cost per 1,000 impressions, = cost ÷ impressions × 1,000. You do not need it in this lesson, but it connects the other two, as the next paragraph shows.
The three numbers are tied together by one identity: CPM = CPV × view rate × 1,000. Out of 1,000 impressions, the view rate decides how many become views, and each view costs the CPV. Turned round, CPV = CPM ÷ 1,000 ÷ view rate. This tells you where a CPV comes from. The CPM is the price the auction sets for reaching your audience. The view rate is what your creative makes of that audience. If two ads run to the same audience at the same CPM, the one with the higher view rate has the lower CPV. A better opening scene is, quite literally, a discount.
The view rate is also the fastest quality signal you get. It reacts within days, long before sales or survey results arrive, and it reacts to the two things you control most directly: the first seconds of the creative and the fit between the ad and the audience.
A fully worked example: ten ads of Tarnwick Cycles
In March 2027 Tarnwick Cycles ran ten skippable in-stream ads for the launch of the Fell 3. The report gives four facts per ad: its length, the impressions, the views and the cost. The last three columns of the table are the ones you calculate in the lab.
| Ad | Length | Impressions | Views | Cost | View rate | CPV | Share of views |
|---|---|---|---|---|---|---|---|
| Fell 3 launch film | 45 s | 182,400 | 61,100 | €2,810.60 | 33.5 % | €0.046 | 16.1 % |
| Commuter story | 30 s | 96,500 | 27,800 | €1,362.20 | 28.8 % | €0.049 | 7.3 % |
| Hill-climb test | 20 s | 148,200 | 55,600 | €2,001.60 | 37.5 % | €0.036 | 14.7 % |
| Battery range explainer | 60 s | 74,300 | 16,900 | €963.30 | 22.7 % | €0.057 | 4.5 % |
| Rider testimonial: Ines | 35 s | 58,900 | 19,400 | €853.60 | 32.9 % | €0.044 | 5.1 % |
| Workshop tour | 90 s | 41,200 | 7,800 | €499.20 | 18.9 % | €0.064 | 2.1 % |
| Spring offer 15s | 15 s | 213,600 | 91,800 | €2,937.60 | 43.0 % | €0.032 | 24.2 % |
| Test-ride invitation | 25 s | 124,800 | 43,700 | €1,791.70 | 35.0 % | €0.041 | 11.5 % |
| Cargo bike school run | 40 s | 67,400 | 20,900 | €1,066.00 | 31.0 % | €0.051 | 5.5 % |
| Accessories bundle | 18 s | 88,700 | 33,700 | €1,213.20 | 38.0 % | €0.036 | 8.9 % |
| All ads | 1,096,000 | 378,700 | €15,499.00 | 34.6 % | €0.041 | 100.0 % |
Take the Hill-climb test, a 20-second ad. It was shown 148,200 times and produced 55,600 views, so its view rate is 55,600 ÷ 148,200 = 37.5 %. It cost €2,001.60, so its CPV is €2,001.60 ÷ 55,600 = €0.036. The Spring offer 15s did even better on both counts: 91,800 views from 213,600 impressions is a view rate of 43.0 %, and €2,937.60 ÷ 91,800 is a CPV of €0.032.
At the other end stands the Workshop tour, a 90-second film. Only 7,800 of its 41,200 impressions became views, a view rate of 18.9 %, and each view cost €0.064, twice the price of a view of the spring offer. Before you call it a bad ad, notice what the view means in each case. A view of the 15-second offer is 15 seconds of attention. A view of the workshop tour is at least 30 seconds, often much more. Later lessons of the course price that difference; for now, remember that view rate and CPV favour short ads by construction.
The formulas step by step
- View rate (F4):
=D4/C4, views divided by impressions. Copy it down to F13. Format the column as a percentage. - CPV (G4):
=ROUND(E4/D4,3), cost divided by views, rounded to three decimals. Copy it down to G13. - Totals (row 14): the sheet already holds
=SUM(C4:C13),=SUM(D4:D13)and=SUM(E4:E13)for impressions, views and cost. - Account view rate (F14):
=D14/C14, total views divided by total impressions. - Account CPV (G14):
=ROUND(E14/D14,3), total cost divided by total views. - Share of all views (H4):
=D4/$D$14. The dollar signs lock the total, so the formula can be copied down to H13.
Two details in these formulas come back in every lesson of the course. The first is the order of a ratio: the thing you are counting goes on top, the thing it is measured against goes below. Views per impression is views ÷ impressions; cost per view is cost ÷ views. If a result looks absurd, a view rate of 270 % or a CPV of 21 euros, the ratio is upside down. The second is the absolute reference. In =D4/$D$14 the first reference moves down with the formula (D5, D6, ...), while $D$14 stays put. Without the dollar signs the second row would divide by D15, which is empty, and Excel would show a division error.
The account average: totals, not averages
Now the step where most spreadsheets go wrong. What is the view rate of the whole account? The tempting answer is =AVERAGE(F4:F13), the average of the ten view rates. It gives 32.1 %. The correct answer divides all views by all impressions: 378,700 ÷ 1,096,000 = 34.6 %.
The plain average is 2.4 percentage points too low. It gives every ad the same weight: the Workshop tour, with 41,200 impressions, counts as much as the Spring offer with 213,600, which was shown more than five times as often. Because the weakest view rates in this account belong to small ads, the average is dragged down. In another account the error runs the other way. Imagine one large ad at a 20 % view rate on 100,000 impressions and one small test at 60 % on 10,000 impressions. The average of the two rates is 40 %. The real view rate is 26,000 views ÷ 110,000 impressions = 23.6 %. Nobody watching that account would recognise it in the 40 %.
The same holds for money. The average of the ten CPVs is €0.0456; total cost ÷ total views is €15,499.00 ÷ 378,700 = €0.0409, or €0.041 rounded. The average of the CPVs is higher here because the expensive views belong to small ads: the Workshop tour pays €0.064 per view but bought only 7,800 of them. The rule is simple and has no exceptions: a rate for a group is the sum of the numerators divided by the sum of the denominators.
The last column, the share of all views, shows where the account's views really come from. The Spring offer alone delivers 24.2 % of them, and the three largest ads together 55.1 %. When three ads carry more than half of the result, the account average is mostly a statement about those three.
Common mistakes
- Averaging the rates. Use totals for every group: the account, a campaign, a month, an audience.
- Turning the ratio upside down. View rate is views ÷ impressions, never the other way round. CPV is cost ÷ views.
- Confusing CPV with the cost per impression. Cost ÷ impressions is a much smaller number and, multiplied by 1,000, is the CPM. They answer different questions.
- Forgetting the dollar signs. A share needs the total locked:
$D$14. - Comparing ads of very different length. A 15-second ad will nearly always beat a 90-second film on view rate and CPV. That says little about which one told the story better.
- Reading the bid as the price. The maximum CPV you bid is a ceiling. The CPV in the report is what you actually paid on average, and it is usually lower.
- Treating a view as a sale. A view is 30 seconds of attention. It is a good start and nothing more.
How to read the result
Read a video report in three passes. First the account: a view rate of 34.6 % at a CPV of €0.041. Multiply the two and by 1,000 and you get the CPM, about €14.14: this is what Tarnwick Cycles pays the auction for 1,000 chances to be watched. Keep that number in mind, because it changes with the season and the audience and is mostly outside the advertiser's control.
Second, the spread. The best ad, Spring offer 15s, has a view rate of 43.0 %, the weakest, Workshop tour, 18.9 %. The cheapest view costs €0.032, the dearest €0.064. A spread like this is normal. It is also the main opportunity: moving budget from the weak half to the strong half improves the account without a single new idea.
Third, the reasons. Sort the table by length in your head. The three ads under 25 seconds have the three best view rates, the 60- and 90-second films the two worst. Within a length class the differences are about the creative. The 45-second launch film reaches 33.5 %, clearly more than the 60-second battery explainer with 22.7 %, and for a long film that is a respectable result. Lesson 8 of the course builds a fair benchmark by length so that every ad is judged against its own class.
People often ask what a good view rate is. There is no universal answer. It depends on the length of the ad, the audience (people who already visited your site skip less than strangers), the device (ads on TV screens are skipped less than ads on phones) and the country. Rates between 15 % and 45 % are all plausible for skippable in-stream ads, which is an illustrative range and not an official benchmark. The useful benchmark is your own history: the same kind of ad, to the same kind of audience, last quarter.
Why view rate and CPV are the start, not the verdict
A low CPV is pleasant, but it is a price, and a price says nothing about what you got. A cheap view from an audience that will never buy an e-bike is worth less than an expensive one from someone comparing models this week. View rate has a similar blind spot. It can be pushed up by showing ads where people cannot or do not skip, for example inside game apps, and those views are close to worthless. The course returns to this in the lesson on placement quality.
That is why the following lessons add layers. How far did people watch beyond the 30-second mark? What does a completed view cost? How many different people were reached, and how often? And finally, did anything change in people's minds: do more of them remember the ad or consider the brand than in a comparable group that did not see it? View rate and CPV stay on the dashboard all the way through, as the first two columns and not the last two.
What the rest of the course covers
The course has 20 labs. Each one is a spreadsheet with a new data set of the same fictional company and four to six tasks that are checked as you type. After this free lesson you will work through:
- Quartile reports and completion rate: who reaches 25 %, 50 %, 75 % and the end of the video, and where a film loses its audience.
- Cost per completed view (CPCV): the price of a finished video, ranked across ads and compared between short and long formats.
- Reach and frequency: unique people, average frequency, cost per 1,000 people reached, frequency distributions, effective reach and what a frequency cap saves.
- Planning: budgets for a view target, daily budgets, the implied CPM, reach curves with marginal cost, and a what-if grid of CPM against view rate.
- Creative and formats: the first five seconds, benchmarks by length, watch time and earned actions, bumpers, non-skippable ads, in-feed and Shorts on one scale, creative wear-out with SLOPE and FORECAST.LINEAR.
- Audiences and placements: roll-ups by audience type and device with SUMIFS, and a placement audit that finds views nobody watched.
- Video sequencing: step-through rates, sequence completion and the cost per person who saw the whole story.
- Brand lift: reading a survey with control and exposed groups, absolute and relative lift, lifted users and their cost, a two-proportion z-test, confidence intervals and the survey size a study needs.
- Decisions: filling a fixed budget by cost per lifted user, and a final quarterly dashboard that runs from delivery to brand lift.
Along the way you practise the spreadsheet skills that analysts in any marketing team use daily: ratios and absolute references, ROUND and ROUNDUP, IF with OR, SUMIFS and COUNTIFS, XLOOKUP and INDEX with MATCH, SUMPRODUCT, RANK.EQ, trend functions, a little statistics with NORM.S.DIST, conditional formatting and charts. The course ends with a 20-question exam and a certificate.
Try it in the sheet
Scroll up to the lab and fill the three empty columns for the ten ads, then the two account cells in row 14. If the Coach rejects the account view rate, check whether you averaged column F: the answer it expects is 34.6 %, from the totals. When all five tasks are green you have built the core of every YouTube ads report, and you have done it the way that stays correct when the account grows from ten ads to a thousand.
One last reminder: Tarnwick Cycles does not exist, and none of the figures on this page are real campaign results or official platform data. They are constructed so that the arithmetic is easy to follow and the patterns, short ads leading on view rate, long films costing more per view, are the ones you are likely to meet in practice.
Frequently asked questions
What is the view rate of a YouTube ad?
The view rate is views divided by impressions. If an ad was shown 148,200 times and 55,600 of those plays were counted as views, the view rate is 37.5 %. It shows what share of the people who were shown the ad kept watching instead of skipping.
How do I calculate CPV in Excel?
Divide the cost by the views: =E4/D4. Because a CPV is only a few cents, round it to three decimals with =ROUND(E4/D4,3). For a whole account divide the total cost by the total views instead of averaging the CPVs of the single ads.
When does a skippable in-stream ad count as a view?
The usual convention is that a view is counted when the viewer watches 30 seconds of the ad, or the whole ad if it is shorter than 30 seconds, or interacts with it. The definition is set by the platform, differs between formats and can change, so check it before comparing numbers.
Why should I not average the view rates of my ads?
A plain average gives a small ad the same weight as a large one. One ad with 20 % on 100,000 impressions and one with 60 % on 10,000 impressions average 40 %, but the true account view rate is 26,000 views divided by 110,000 impressions, which is 23.6 %. Always divide total views by total impressions.
How are CPV, view rate and CPM connected?
CPM = CPV × view rate × 1,000. Out of 1,000 impressions the view rate decides how many become paid views, and each costs the CPV. Turned round, CPV = CPM ÷ 1,000 ÷ view rate, so a higher view rate at the same CPM means a lower CPV.
What is a good view rate?
There is no universal figure. View rates depend on the length of the ad, the audience, the device and the country, and short ads have higher rates than long ones by construction. Compare an ad with your own earlier ads of similar length for a similar audience rather than with a general number.
Keep going: YouTube & Video Ads: View Rate, CPV, Cost per Completed View, Reach & Frequency, Video Sequencing & Brand Lift
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.
- Quartile Reports: Who Reaches 25 %, 50 %, 75 % and the End of the Video
- Cost per Completed View: What You Really Pay for a Finished Video
- Reach and Frequency: How Many People, How Often and at What Cost
- Planning with CPV: Budget for a View Target, Daily Budgets and the Implied CPM
- Watch Time and Earned Actions: Hours Bought, Cost per Hour and the Effective CPV
- Audiences and Devices: View Rate and CPV per Audience Type with SUMIFS
- The First Five Seconds: Hold Rate, View Rate and a Fair Benchmark by Length
- Formats Side by Side: Skippable, Non-Skippable, Bumper, In-Feed and Shorts on One Scale
- Frequency Distribution: Effective Reach, Wasted Impressions and the Case for a Cap
- The Reach Curve: Diminishing Returns and the Marginal Cost of the Next 1,000 People
- Video Ad Sequencing: Step-Through Rates, Sequence Completion and the Cost per Finisher
- Reading a Brand-Lift Survey: Absolute Lift, Relative Lift and the Cost per Lifted User
- Is the Lift Real? A Two-Proportion z-Test for Survey Results
- How Sure Is the Lift? Confidence Intervals and the Survey Size You Need
- Creative Wear-Out: Trend, Forecast and the Week to Refresh the Ad
- Placement Quality: When a High View Rate Is a Warning Sign
- A What-If Grid: The CPV for Every Combination of CPM and View Rate
- Filling a Fixed Budget: Ranking Video Options by Cost per Lifted User
- Final Project: A Quarterly Video Dashboard from Delivery to Brand Lift