Hook Rate and Hold Rate: a Sheets calculator for paid-social creative

Ask a creative team which ad is winning and you usually get an opinion about the hook. Ask the account which ad is winning and, for video, it already has an answer: how many people stopped scrolling in the first three seconds, and how many of those stayed. Retention numbers, not adjectives. This template turns a raw Ads Manager export into those numbers automatically, so a creative review starts with hook rate and hold rate on the screen before anyone argues about taste.

Before you start

• Pull a video-ad export from Meta Ads Manager (Export table to .csv, or the columns customiser) with one row per ad. You need the following Meta metric fields present: Impressions, "Video plays at 3 seconds" (video_play_actions), ThruPlays (video_thruplay_watched_actions), Video plays at 25%/50%/75%/100%, Link clicks, Spend and Results.
• Paste the export into a raw "Data" tab; the formulas below live on a separate "Calculator" tab and reference it, so a re-export is a paste-over, not a rebuild.

The sheet

Tab layout: Data (raw paste from Ads Manager, one row per ad, headers in row 1) and Calculator (formulas below, row 2 onward, dragged down to match).

Data tab columns (A:K), one row per ad:
A  Ad name
B  Impressions
C  3-second video plays        (video_play_actions)
D  ThruPlays                   (video_thruplay_watched_actions)
E  Video plays at 25%
F  Video plays at 50%
G  Video plays at 75%
H  Video plays at 100%
I  Link clicks
J  Spend
K  Results

Calculator tab, row 2 (drag down for every row in Data):
A2   =Data!A2                                   Ad name
B2   =Data!B2                                   Impressions
C2   =IFERROR(Data!C2/Data!B2, 0)                Hook rate (3-sec plays / impressions)
D2   =IFERROR(Data!D2/Data!C2, 0)                Hold rate (ThruPlays / 3-sec plays)
E2   =IFERROR(Data!H2/Data!C2, 0)                Hook-to-completion (100% plays / 3-sec plays)
F2   =IFERROR(Data!I2/Data!B2, 0)                Link CTR (link clicks / impressions)
G2   =IFERROR(Data!J2/Data!B2*1000, 0)           CPM (spend / impressions * 1000)
H2   =IFERROR(Data!J2/Data!D2, 0)                Cost per ThruPlay (spend / ThruPlays)
I2   =IFERROR(Data!J2/Data!K2, 0)                Cost per result

Format columns C, D, E and F as percentages; format G, H and I as currency. Set up conditional formatting on the hook rate column (C): green scale at 30% and above, red below 15%, amber in between. Do the same on hold rate (D) with green at 50%+ and red below 25%. These bands are a starting point for a typical feed/Reels placement, not a fixed law, a longer-form ad or a niche B2B audience can behave very differently, so recalibrate the bands once you have a few months of your own account's data.

How to read it

Hook rate tells you whether the first three seconds earn attention: a scroll-stopping open, a face, motion, or a bold claim in caption text. Hold rate tells you whether what follows the hook keeps that attention, a weak hook with a strong hold is still a low-reach ad, and a strong hook with a weak hold means the opening over-promises against the body of the creative. Cost per ThruPlay is the cleanest single efficiency number for judging one video ad against another at the same objective, because it strips out CTR and conversion differences that belong to the landing page, not the creative.

Sort the Calculator tab by hook rate descending before a creative review, and you have retention-first evidence on the table instead of a debate about which thumbnail "feels" stronger.

Worth knowing

This reads a static export, it does not pull live from the Ads Manager API, so refresh it by re-pasting a new export into the Data tab whenever you want current numbers. The percentage bands are directional defaults meant to start a conversation, not a benchmark claim for every account, vertical or placement.