Tabellab

Opt-in Rate by Lead Magnet: Sign-ups per Form View, the Overall Rate and Each Magnet's Share of the List

Lesson 1 of 20Lab

All lessons
  1. Opt-in Rate by Lead Magnet: Sign-ups per Form View, the Overall Rate and Each Magnet's Share of the ListLabFree
  2. Opt-in Rate by Placement: Landing Page, Pop-up, Inline and Footer Forms Compared with the Site RateLab Locked
  3. Cost per Lead: Production, Promotion and Tool Costs, Spreading One-off Costs and the Blended CPLLab Locked
  4. Lead Quality: Confirmation Rate, Bounces, Early Unsubscribes and the Cost per Net LeadLab Locked
  5. Lead-to-Customer Conversion: Cohorts, the 90-Day Window and Revenue per LeadLab Locked
  6. From Lead to Profit: Cost per Customer, Net Result and ROI of Each MagnetLab Locked
  7. Break-even Cost per Lead: Value per Lead, Repeat Orders and Headroom for PromotionLab Locked
  8. The Magnet Scorecard: Weighted Scores with SUMPRODUCT, Ranks and a Scale, Keep or Retire VerdictLab Locked
  9. Content Upgrades vs Generic Forms: Group Totals with SUMIFS, Rate per Offer Type and LiftLab Locked
  10. From Log to Table: Sign-ups by Magnet and Placement with SUMIFS, Mixed References and COUNTIFSLab Locked
  11. Nurture Sequence Timing: Click Rate per Email, Running Totals and the Day the Orders Are InLab Locked
  12. Days to First Purchase: Date Differences, Blank Dates, a Conversion Window and the MedianLab Locked
  13. Forecasting List Growth: Trend per Week with SLOPE and a Four-Week Forecast with FORECAST.LINEARLab Locked
  14. What-if Grid: Opt-in Rate × Lead-to-Customer Rate, Mixed References and the Loss ZoneLab Locked
  15. A/B Tests of Opt-in Forms: Lift, the z-Score and an Honest VerdictLab Locked
  16. Magnet Fatigue: Monthly Opt-in Rate, Cost per Lead, a 3-Month Moving Average and the Trend over a YearLab Locked
  17. Cleaning the List: Consent, Confirmation, Duplicates and the True Cost per Valid LeadLab Locked
  18. Splitting the Promotion Budget: Profit per Lead, Return per Dollar, Ranks and the Expected Profit of a PlanLab Locked
  19. Payback: Cumulative Profit per Lead, the Payback Month and ROI after Six MonthsLab Locked
  20. Final Project: Quarterly Magnet Scorecard with Opt-in Rate, Cost per Lead, Conversion, ROI, a Flag and a 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…

What a lead magnet is, and why its first number is the opt-in rate

Almost every business with a website wants the same thing from a first-time visitor: a way to stay in touch. Most visitors are not ready to buy on the first visit, and most never come back on their own. An email address, given voluntarily, turns an anonymous visit into a relationship you can continue. A lead magnet is the reason a visitor hands it over: something useful, delivered immediately, in exchange for signing up.

Lead magnets come in many shapes. A printable or a PDF checklist is quick to make and quick to use. A template or spreadsheet saves the subscriber real work. A short email course spreads the value over several days. A quiz gives a personal result. A discount code is the plainest offer of all. A content upgrade is a magnet made for one specific article, such as the worksheet that goes with the post the visitor is reading right now.

Whatever the format, the first question is always the same: of the people who saw the offer, how many took it? That is the opt-in rate. This free lesson shows how to calculate it correctly in a spreadsheet, how to get an overall rate that is not distorted, how to see which magnets actually build the list, and how to read the result without fooling yourself. The interactive lab above uses the same data as the worked example below, so you can type every formula yourself.

The example company is Mossgate Planners, a fictional online shop that sells paper planners and digital planning templates. All magnets, names and numbers in this article and in the whole course are invented for learning. Where a figure is described as typical, treat it as illustrative, not as a benchmark for your own site.

The definition: sign-ups divided by form views

Opt-in rate = sign-ups ÷ form views. A sign-up is one submitted form. A form view is one visitor who actually had the form in front of them: the pop-up opened, the inline form scrolled into view, the landing page loaded. If your tool only reports pageviews, be aware that a form at the bottom of a long article is seen by far fewer people than the page has views, and the rate based on pageviews will look worse than the form really is.

The rate is a measure of persuasion, not of size. A magnet shown to 2,000 people that collects 200 addresses has a 10% opt-in rate; a magnet shown to 20,000 people that collects 400 has a 2% rate. The second one produced twice as many sign-ups, but the first one is the stronger offer. You need both views, and keeping them apart is the main skill of this lesson.

From form view to customer: where the opt-in rate sitsForm views58,000saw the formSign-ups2,728typed an emailConfirmed leadslesson 4clicked the linkWelcome emailslesson 11got the magnetCustomerslesson 5placed an orderOpt-in rate = 2,728 ÷ 58,000 = 4.70%this lessonLead quality, nurture timing and conversionlater lessons of the courseAll ten forms of Mossgate Planners, March 2026 (fictional data)
Figure 1. The opt-in rate measures the first step of the funnel only: 2,728 sign-ups from 58,000 form views across all ten forms. What happens to those sign-ups afterwards is the subject of later lessons.

An email address is personal data. Ask for it openly, say what the subscriber will receive and how often, do not pre-tick consent boxes, and make unsubscribing a one-click matter. This course teaches the arithmetic of lead magnets; it is not legal advice, and the consent rules that apply depend on where you and your subscribers are.

A fully worked example: ten magnets at Mossgate Planners

In March 2026 Mossgate Planners ran ten opt-in forms. Nine offered a lead magnet; the tenth was the plain newsletter form with no magnet at all, which is a useful baseline. The table shows the raw data, form views and sign-ups, together with the three columns you calculate in the lab.

Lead magnetFormatForm viewsSign-upsOpt-in rateShare of sign-upsvs overall
Weekly Planner PrintablePDF8,4004124.9%15.1%Above
Habit Tracker TemplateSpreadsheet5,2003186.1%11.7%Above
Budget Binder Starter KitPDF bundle3,9002466.3%9.0%Above
5-Day Declutter Email CourseEmail course2,600943.6%3.4%Below
Planner Style QuizQuiz4,70058312.4%21.4%Above
10% Welcome DiscountCoupon12,5005254.2%19.2%Below
Meal Planning Cheat SheetPDF6,1002013.3%7.4%Below
Goal-Setting Workshop ReplayVideo1,800633.5%2.3%Below
Sticker Sheet SamplerPrintable3,3001725.2%6.3%Above
Newsletter only (no magnet)None9,5001141.2%4.2%Below
All forms58,0002,7284.70%100.0%

Start with one row. The Planner Style Quiz was seen 4,700 times and produced 583 sign-ups. Its opt-in rate is 583 ÷ 4,700 = 12.4%. The plain newsletter form was seen 9,500 times and produced 114 sign-ups: 114 ÷ 9,500 = 1.2%. The same visitors, on the same site, are roughly ten times as likely to sign up when they get something specific in return.

Opt-in rate by lead magnetAbove the overall rateBelowOverall rate 4.70%Planner Style Quiz12.4%Budget Binder Starter Kit6.3%Habit Tracker Template6.1%Sticker Sheet Sampler5.2%Weekly Planner Printable4.9%10% Welcome Discount4.2%5-Day Declutter Email Course3.6%Goal-Setting Workshop Replay3.5%Meal Planning Cheat Sheet3.3%Newsletter only (no magnet)1.2%0%2%4%6%8%10%12%14%Sign-ups ÷ form views, March 2026 (fictional data)
Figure 2. Opt-in rate of the ten forms, sorted from highest to lowest. The dashed line is the overall rate of 4.70%. Green bars beat it, peach bars fall short.

Step by step in the spreadsheet

  1. Opt-in rate per magnet (E4). Type =D4/C4, sign-ups divided by form views, and format the cell as a percentage. Copy it down to E13. Because both references are relative, each row divides its own sign-ups by its own form views.
  2. Totals (C14 and D14). Type =SUM(C4:C13) in C14 and copy it one cell to the right. SUM adds every number in a range; the colon means "from … to".
  3. Overall rate (E14). Type =D14/C14. This divides all 2,728 sign-ups by all 58,000 form views and gives 4.70%.
  4. Share of sign-ups (F4). Type =D4/$D$14 and copy down. The dollar signs make D14 an absolute reference, so every row divides by the same total.
  5. Above or below (G4). Type =IF(E4>$E$14,"Above","Below") and copy down. IF checks the test, then returns the first text when it is true and the second when it is false.
Not sure whether a reference needs dollar signs? Ask what should happen when the formula is copied down one row. If the reference should move down too (this magnet's sign-ups), leave it relative. If it must keep pointing at one cell (the total), lock it. In most spreadsheet programs the F4 key adds the dollar signs for you while you are editing a formula.

Why the overall rate is not the average of the rates

There are two ways to get one number for the whole site, and they give different answers. Dividing total sign-ups by total form views gives 4.70%. Averaging the ten individual rates with =AVERAGE(E4:E13) gives 5.08%. Only the first is the opt-in rate of the site.

The reason is weight. The plain average treats every form as equally important. But the welcome discount form was seen 12,500 times and the workshop replay only 1,800 times. The quiz, with its exceptional 12.4%, pulls the plain average up although it accounts for only 8% of all form views. Sum-over-sum weights each form by its traffic automatically, which is exactly what "the rate of the whole site" means.

This rule comes back in every lesson of the course, for cost per lead, for conversion rates and for ROI: a rate for a group is the sum of the numerators divided by the sum of the denominators. Averages of rates are only right when every row has the same denominator, which real data almost never has.

Rate and volume: two different rankings

Sort the table by opt-in rate and the quiz leads, followed by the Budget Binder Starter Kit and the Habit Tracker Template. Sort it by sign-ups and the order changes: the quiz is still first with 583, but the 10% Welcome Discount is second with 525 and the Weekly Planner Printable third with 412.

The discount is the instructive case. It collects 19.2% of all sign-ups, nearly one in five, yet its opt-in rate of 4.2% is below the site rate. It wins on exposure: its form is shown 12,500 times, more than any other. If you judged magnets by sign-up counts alone you would call it a star. Judged by rate, it is an average offer in a very good position.

The share column also protects you from the opposite mistake. The Goal-Setting Workshop Replay and the 5-Day Declutter Email Course have unremarkable rates and tiny shares (2.3% and 3.4%). It would be easy to delete them. Later lessons show that their few leads are among the most likely to become customers, which is why the opt-in rate is the first number of a magnet and never the last.

How to read the result

Five of the ten forms are above the overall rate of 4.70%: Weekly Planner Printable, Habit Tracker Template, Budget Binder Starter Kit, Planner Style Quiz and Sticker Sheet Sampler. The label is a quick orientation, not a verdict. Three questions help you read it properly.

  • Is the difference large or small? The Weekly Planner Printable is "Above" at 4.9%, only a fraction of a point over the line. The quiz is "Above" at 12.4%. The label is the same; the meaning is not.
  • Is there enough data? The workshop replay had 1,800 form views and 63 sign-ups. Ten sign-ups more or fewer in a month would move its rate by more than half a percentage point. Small forms need more time before you judge them.
  • Where was the form shown? A magnet offered on a dedicated landing page meets visitors who already clicked on the offer; the same magnet in a footer meets everyone. Lesson 2 separates the effect of the magnet from the effect of the placement.

The baseline matters too. The newsletter-only form is the honest comparison for every magnet: it shows what the site collects when it offers nothing but "updates". Every magnet in the table beats it, most of them several times over. A magnet that failed to beat the plain form would not be worth its production cost.

Common mistakes and how to spot them

  • Dividing the wrong way round. =C4/D4 gives form views per sign-up (about 20), not a rate. If your "rate" is far above 100%, the division is upside down.
  • Using pageviews instead of form views. A footer form on a page with 10,000 pageviews may have been seen 2,000 times. Divide by what the visitor actually saw, or at least use the same definition for every form you compare.
  • Averaging the rates. =AVERAGE(E4:E13) overstates the site rate here by about a third of a percentage point, and the error grows with the spread of the traffic.
  • Forgetting the dollar signs. =D4/D14 works in row 4 and breaks in row 5, where it becomes =D5/D15 and divides by an empty cell.
  • Typing percentages as whole numbers. A cell formatted as a percentage shows 0.049 as 4.9%. If you type 4.9 it shows 490%. Let the formula produce the fraction and let the format do the rest.
  • Counting sign-ups as leads. A sign-up with a mistyped address, a duplicate or an unconfirmed double opt-in is not a lead. The opt-in rate counts form submissions; lesson 4 counts what is left.

What moves an opt-in rate

The arithmetic is simple; the interesting part is why the rates differ. Four things explain most of the spread you see in a table like this one.

  • Specificity. "Weekly Planner Printable" says exactly what the visitor gets. "Join our newsletter" does not. Specific, finished, immediately usable magnets tend to convert better than vague promises.
  • Match with the page. A budgeting worksheet on a budgeting article meets a reader who is thinking about budgets right now. This is the whole idea of content upgrades, measured in lesson 9.
  • Effort for the visitor. Every extra form field costs sign-ups. A quiz is the exception that proves the rule: it asks for effort first and for the email last, when the visitor wants the result.
  • Placement and timing. The same offer performs differently in a pop-up, inline, in a sidebar or on its own landing page. Lesson 2 compares nine placements of one magnet.

None of these should tempt you into tricks. Pop-ups that cannot be closed, buttons that shame the visitor for declining, and countdown timers that reset on every visit raise the opt-in rate for a while and lower the quality of the list for good. The later lessons of the course make that cost visible: leads who were pushed do not confirm, do not open and do not buy.

Quick reference: formulas of this lesson

MeasureFormula in wordsIn the sheet
Opt-in ratesign-ups ÷ form views=D4/C4
Totalsum of a column=SUM(D4:D13)
Overall ratetotal sign-ups ÷ total form views=D14/C14
Share of sign-upsmagnet's sign-ups ÷ all sign-ups=D4/$D$14
Above or belowcompare the rate with the overall rate=IF(E4>$E$14,"Above","Below")

Where this is used in practice

  • Monthly list report. One table per month with form views, sign-ups, rate and share per form shows at a glance where the list grows and which forms are wasting good positions.
  • Deciding what to build next. If checklists convert at twice the rate of video replays on your site, the next magnet should probably be a checklist.
  • Briefing a designer or writer. "The sidebar form converts at a fifth of the site rate" is a clearer brief than "the sidebar is not working".
  • Checking a tool's dashboard. Many form tools report an average conversion rate. Recalculating sum over sum tells you whether the dashboard number is weighted or not.

What you will build in the rest of this course

The opt-in rate answers one question. A lead magnet programme raises many more, and the remaining nineteen labs take them one at a time, always with a new data set from the same fictional shop and always with every formula checked as you type.

Lessons 2 to 8 build the core. You compare placements of one magnet, calculate a fair cost per lead that spreads production cost over the life of the magnet, clean sign-ups down to confirmed net leads, follow a monthly cohort to its first orders, work out the cost per customer and the ROI of each magnet, find the break-even cost per lead with repeat orders, and combine everything in a weighted scorecard with ranks and a Scale, Keep or Retire verdict.

Lessons 9 to 13 add structure and time. You measure the lift of content upgrades over generic forms with SUMIFS, turn a sign-up log into a magnet-by-placement grid with mixed references, time a welcome sequence with running totals, calculate days to first purchase with date arithmetic and the median, and forecast list growth with SLOPE and FORECAST.LINEAR.

Lessons 14 to 19 are about decisions under uncertainty. You build a what-if grid of opt-in rate against conversion rate, read A/B tests of forms with a z-score, detect magnet fatigue with a moving average, clean a list and reprice its leads, split a promotion budget by return per dollar, and find the payback month of each magnet.

Lesson 20 is the final project: a quarterly magnet scorecard built from raw monthly rows, with opt-in rate, cost per lead, lead-to-customer rate and ROI per magnet, a rule that flags the loss-makers and a chart. After the labs, a 20-question exam leads to the certificate.

Summary

The opt-in rate is sign-ups divided by form views, calculated per form with a simple division. The rate of the whole site is total sign-ups divided by total form views, 4.70% in the example, and not the average of the individual rates. The share of sign-ups shows where the list grows, and an IF label gives a first orientation against the overall rate.

Read rate and volume together. At Mossgate Planners the quiz is the most persuasive offer, the welcome discount is a large but average one, and the plain newsletter form shows what happens without any magnet. Which of these sign-ups turn into confirmed leads, and which leads turn into customers, is what the rest of the course works out.

Frequently asked questions

What is a good opt-in rate for a lead magnet?

There is no single good number, because the rate depends on the placement, the traffic source and how form views are counted. Compare each magnet with your own overall rate and with a plain newsletter form on the same site. In the fictional example the site rate is 4.70% and individual magnets range from 1.2% to 12.4%.

How do I calculate the opt-in rate in Excel?

Divide sign-ups by form views, for example =D4/C4, format the cell as a percentage and copy the formula down. For the whole site divide the total sign-ups by the total form views, for example =SUM(D4:D13)/SUM(C4:C13).

Why should I not average the opt-in rates of my forms?

A plain average gives every form the same weight, whether it was seen 1,800 or 12,500 times. The opt-in rate of the site is total sign-ups divided by total form views, which weights each form by its traffic.

What is the difference between a lead magnet and a content upgrade?

A lead magnet is any free item offered in exchange for an email address. A content upgrade is a lead magnet made for one specific article, such as the checklist or template that belongs to that post, and it is offered only there.

Should I count pageviews or form views in the denominator?

Use form views where your tool reports them, because they count visitors who actually saw the form. If you only have pageviews, use them consistently for all forms and remember that forms low on the page will look weaker than they are.

Is a magnet with a high opt-in rate always the best one?

No. A high rate shows that the offer is attractive, not that the leads are valuable. Some magnets collect many addresses that never confirm or never buy. Later lessons add confirmation rate, cost per lead, lead-to-customer rate and ROI before any magnet is judged.

Keep going: Lead Magnets & Content Upgrades: Opt-in Rates by Magnet and Placement, Cost per Lead, Lead-to-Customer Conversion, Nurture Timing & Magnet Scorecards

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. Opt-in Rate by Placement: Landing Page, Pop-up, Inline and Footer Forms Compared with the Site Rate
  2. Cost per Lead: Production, Promotion and Tool Costs, Spreading One-off Costs and the Blended CPL
  3. Lead Quality: Confirmation Rate, Bounces, Early Unsubscribes and the Cost per Net Lead
  4. Lead-to-Customer Conversion: Cohorts, the 90-Day Window and Revenue per Lead
  5. From Lead to Profit: Cost per Customer, Net Result and ROI of Each Magnet
  6. Break-even Cost per Lead: Value per Lead, Repeat Orders and Headroom for Promotion
  7. The Magnet Scorecard: Weighted Scores with SUMPRODUCT, Ranks and a Scale, Keep or Retire Verdict
  8. Content Upgrades vs Generic Forms: Group Totals with SUMIFS, Rate per Offer Type and Lift
  9. From Log to Table: Sign-ups by Magnet and Placement with SUMIFS, Mixed References and COUNTIFS
  10. Nurture Sequence Timing: Click Rate per Email, Running Totals and the Day the Orders Are In
  11. Days to First Purchase: Date Differences, Blank Dates, a Conversion Window and the Median
  12. Forecasting List Growth: Trend per Week with SLOPE and a Four-Week Forecast with FORECAST.LINEAR
  13. What-if Grid: Opt-in Rate × Lead-to-Customer Rate, Mixed References and the Loss Zone
  14. A/B Tests of Opt-in Forms: Lift, the z-Score and an Honest Verdict
  15. Magnet Fatigue: Monthly Opt-in Rate, Cost per Lead, a 3-Month Moving Average and the Trend over a Year
  16. Cleaning the List: Consent, Confirmation, Duplicates and the True Cost per Valid Lead
  17. Splitting the Promotion Budget: Profit per Lead, Return per Dollar, Ranks and the Expected Profit of a Plan
  18. Payback: Cumulative Profit per Lead, the Payback Month and ROI after Six Months
  19. Final Project: Quarterly Magnet Scorecard with Opt-in Rate, Cost per Lead, Conversion, ROI, a Flag and a Chart

Buy course — $9.90 See the full course