- Joined
- Jul 11, 2008
- Messages
- 1,940
- Reaction score
- 185
- Points
- 63
The analysis is where the value is. And in 2025, with LLMs freely available and basic spreadsheet automation within reach of anyone willing to spend an afternoon learning it, there's no reason the analysis needs to be manual, irregular, or limited to whatever you happen to notice when you scroll through your records.
This is a practical guide. Not conceptual. By the end of it you'll have a structure for collecting CLV data systematically, a set of prompts that extract genuine analytical insight from that data, and a clear picture of what your tracking is actually telling you versus what you've been assuming it says. The tools required are a Google Sheet, access to any capable LLM, and the habit of recording consistently. Nothing else.
What You Need to Record and Why Each Field Matters
The minimum viable CLV record has five fields. Everything beyond these is useful but optional until the habit is established.
Date and kick-off time. Not just the date - the time relative to kick-off matters for analysis. A bet placed forty-eight hours before kick-off and a bet placed two hours before kick-off at the same price have different CLV implications because the closing line has had different amounts of time to incorporate information. Recording both date and time-to-kick-off allows the analysis to separate edge that existed early from edge that emerged close to kick-off, which tells you different things about the type of information you're acting on.
Competition and market type. These need to be standardised rather than free-text. Premier League Asian Handicap, Championship First Half Totals, Europa League Match Result - exact consistent naming matters because every analysis prompt depends on grouping by these categories. Inconsistent naming fragments your data and makes the LLM's pattern analysis unreliable. Decide your naming convention before you start and stick to it. A dropdown list in Google Sheets enforces this without relying on memory.
Your price and the operator. The price you actually obtained, in decimal format, and which operator you placed at. Recording the operator allows analysis of whether your CLV performance varies across operators - which it almost certainly does - and which books you're getting genuine value at versus which you're betting at out of habit.
Closing price. The price available on the same selection at the same operator as close to kick-off as you can reasonably get it - last five minutes is fine, last thirty minutes is acceptable. Pinnacle's closing line is the standard benchmark if you have access to it for the selection in question. If you don't, use the best available closing price from an odds comparison site. Record where the closing price came from because it affects how to interpret the CLV figure.
Stake and outcome. Stake in units, not currency, to maintain proportion without exposing financial amounts in the record. Outcome as win, loss, or void. The outcome field is what eventually allows you to separate CLV performance from results performance - the two should correlate over large samples, and if they don't, the analysis needs to explain why.
That's the minimum. Optional fields that add significant analytical value when you have the discipline to populate them consistently: the opening line when you first saw the market, how the line moved between your bet and close, whether you placed before or after significant news in the fixture, and a brief note on your primary reason for the bet. That last field becomes valuable faster than most people expect - prompting an LLM to analyse your stated reasoning patterns against your CLV performance is one of the most uncomfortable and most useful analyses you can run.
The Google Sheets Setup That Makes Analysis Possible
The sheet doesn't need to be complex. It needs to be structured consistently enough that data can be filtered, sorted, and copied into an LLM prompt without manual cleanup.
One tab for raw data entry, one tab for calculated fields, one tab for monthly summaries. That's it.
In the raw data tab, each row is one bet. Columns in order: date, kick-off time, hours-to-kick-off (calculated from the previous two), competition, market type, operator, your decimal price, closing decimal price, CLV percentage (calculated field: (your price / closing price - 1) x 100), stake in units, outcome, and notes. The CLV percentage column is the one you'll be copying into LLM prompts most frequently so it needs to be clean - no error values, no blanks for completed bets.
The calculated fields tab uses AVERAGEIFS to break down average CLV by competition, by market type, by operator, and by time-to-kick-off bucket. These summaries are what the LLM will analyse rather than the raw row data - feeding an LLM a hundred rows of raw bet data produces worse analysis than feeding it the aggregated summaries with sample sizes attached.
One formula worth building explicitly: a rolling 30-bet CLV average that updates automatically as you add rows. This is the figure to monitor weekly rather than your cumulative average, because cumulative averages smooth over regime changes in your performance that the rolling figure makes visible. A shift in your rolling 30-bet CLV that isn't explained by sample variance is worth investigating. It usually means something changed - your analysis approach, the markets you're targeting, the operators you're using, or the competitive landscape in a specific niche.
Google Sheets' built-in scripting via Apps Script can automate a weekly summary email if you want to build that later. For now, the manual habit of checking the rolling figure and the category breakdowns once a week is sufficient and more sustainable than over-engineering the infrastructure before the data collection habit is established.
The Core LLM Analysis Prompts
These are prompts you'll use regularly once you have a meaningful data sample - roughly three months and at least a hundred completed bets is the minimum for the pattern analysis to produce reliable conclusions rather than noise.
The prompt structure follows the principle from the prompt engineering article: provide the data, ask for analysis, specify the output format. Never ask the LLM to provide data it doesn't have. Never ask for a conclusion before you've given it the information to reach one.
The category performance prompt. Paste your AVERAGEIFS summary table - competition, market type, average CLV, sample size for each. Then:
"I'm a sports bettor tracking closing line value across multiple market types and competitions. The table above shows my average CLV percentage and sample size for each category. Please analyse this data and identify: (1) which categories show statistically meaningful positive CLV given their sample sizes, (2) which categories show negative CLV that warrants reducing or eliminating activity, (3) where sample sizes are too small to draw conclusions and more data is needed before acting on apparent patterns. Flag any category where the CLV is positive but the sample is under 50 bets as requiring more data before confidence is warranted."
The instruction to flag small samples is essential. LLMs without that instruction will treat a 3-bet sample with 8% average CLV as meaningful evidence of edge. It isn't. Making the LLM apply basic statistical caution to small samples produces more honest output than it generates without the instruction.
The operator comparison prompt. Paste your average CLV by operator with sample sizes. Then:
"The table above shows my average CLV by operator across all market types. Please identify which operators I'm consistently getting value at, which I'm consistently not, and whether the pattern suggests I should shift volume toward specific operators. Note any operators where my sample is large enough to be confident in the conclusion versus operators where the sample is still small. Also flag any operator where my stake-weighted CLV - meaning larger stakes have higher CLV - differs significantly from my flat-stake average CLV, if that data is present."
The stake-weighted CLV instruction matters because an operator where you get good prices but stake small looks different from one where you get good prices and back your judgement with full stakes. The analysis should reflect that difference.
The time-to-kick-off prompt. Paste your CLV by time bucket - 48+ hours before, 24-48 hours, 12-24 hours, 2-12 hours, under 2 hours. Then:
"The table shows my average CLV split by how far before kick-off I placed each bet. Please analyse whether my CLV is better when I bet early or late, what this suggests about the type of edge I'm finding - information that exists before the market has processed it, versus line movement I'm reacting to, versus early value before sharp money adjusts the line - and what the optimal betting timing strategy appears to be based on this data."
This prompt often produces the most surprising results. Many bettors discover their CLV is meaningfully better at a specific time window without having consciously targeted that window. Understanding why - and then consciously targeting it - is one of the highest-value adjustments most bettors can make to their process.
The notes pattern prompt. This one requires the notes field to have been populated consistently. Copy every entry in your notes field that corresponds to a bet with positive CLV, then separately copy every entry corresponding to a bet with negative CLV. Paste both lists, labelled clearly, and prompt:
"The first list contains my stated reasoning for bets that achieved positive CLV. The second list contains my stated reasoning for bets that achieved negative CLV. Please identify linguistic or conceptual patterns that distinguish the two lists - what types of reasoning, analytical approaches, or information sources appear more frequently in the positive CLV group versus the negative CLV group. Be specific about patterns you can identify with confidence versus patterns that may be coincidental given the sample."
This is the most uncomfortable prompt to run and the most valuable. The output will tell you what your own reasoning looks like when you're finding genuine edge versus when you're rationalising a bet you want to place. Most bettors who run this analysis once run it regularly afterwards.
The Monthly Review Process
The prompts above are analytical tools. They need a regular process to be useful rather than just occasionally interesting.
Once a month, run through four questions in order before touching any of the prompts.
First - has my rolling 30-bet CLV changed significantly compared to the previous month? Not whether it's positive or negative, but whether it's moved in a direction that requires explanation. A drop from +2.1% to +0.4% over thirty bets might be variance. Might not be. The next questions determine which.
Second - has my competition or market type mix changed? If you were primarily betting Championship Asian Handicap in month one and shifted toward Premier League match result in month two, your CLV change might reflect the market mix rather than your analytical performance. The category breakdown shows this.
Third - has my time-to-kick-off distribution changed? Shifting from primarily early bets to primarily late bets - or vice versa - changes the nature of the edge you're trying to capture and will affect CLV in ways that aren't reflections of your analytical quality.
Fourth - have any of my target operators changed their pricing approach or limit structure in ways that would affect line availability at the times I'm betting? This is the external factor that your CLV tracking records without explaining - a line that used to be soft at a specific operator for forty-eight hours before kick-off that's now moving faster reflects the operator's infrastructure change, not your deteriorating edge.
Only after working through these four questions manually does it make sense to run the LLM analysis prompts - because the prompts work best when you already have hypotheses for them to test rather than when you're using them to generate hypotheses from scratch.
What the Data Will Actually Tell You
I want to be direct about what most bettors discover when they build this and run it honestly for six months, because it's worth knowing in advance rather than as a surprise.
The competition and market type breakdown almost always shows that positive CLV is concentrated in one or two categories and that the bettor has been extrapolating that edge across six or eight categories where it doesn't actually exist. The areas where they're confidently betting with no CLV evidence of edge are usually the areas where the betting feels most comfortable and the analysis feels most fluent. The areas with genuine CLV evidence are often the ones that required more work and felt less certain at bet placement. That inverse relationship between confidence and edge is one of the most consistent findings across bettors who track rigorously, and it's uncomfortable enough that many people stop tracking when they encounter it.
The operator breakdown almost always shows that one or two operators are responsible for the majority of positive CLV and that the rest are flat to negative. This is the data that tells you where to concentrate volume - not which operator has the most attractive interface or the most useful promotional offer, but where you're actually getting the best prices on the markets you're betting.
The time-to-kick-off breakdown varies more than the others. Some bettors genuinely have early information advantages. Some are better at line movement analysis close to kick-off. Most have better CLV in a specific window they've never consciously targeted because the data pattern was invisible without systematic tracking.
The notes analysis is the one that changes how people bet rather than just where and when they bet. Seeing in black and white that your reasoning when you have edge looks different from your reasoning when you don't is the kind of feedback that no amount of results-based review produces. Results are noisy. Reasoning patterns, tracked across a hundred bets, are considerably less so.
Anyway. The recording is the easy part. This is the part that makes the recording worth doing.
Frequently Asked Questions
Q: How many bets do you need before the LLM analysis starts producing reliable conclusions rather than noise?
A: It depends on the effect size you're trying to detect and the category you're analysing. For an overall CLV average, fifty completed bets gives you a rough directional signal - enough to know whether you're likely positive or negative, not enough to be confident in a specific number. For category-level analysis - CLV by competition or market type - you need at least thirty bets per category before conclusions are meaningful, and fifty is more comfortable. For the notes pattern analysis, quality matters more than quantity - thirty bets with detailed, honest notes produces better analysis than a hundred bets with vague one-word entries. The instruction to the LLM to flag small samples and explicitly note where conclusions require more data is the practical workaround for the period before you have statistically robust samples in every category. Use it consistently and don't let the LLM - or yourself - draw strong conclusions from fifteen bets in a category regardless of how compelling the pattern looks.
Q: What's the best way to get closing prices for markets where Pinnacle isn't available - lower leagues, some prop markets?
A: A few options in roughly descending order of reliability. The Betfair exchange closing price is the most robust alternative to Pinnacle for match markets where exchange liquidity is adequate - use the last traded price rather than the available price at close, and note that exchange prices include commission which needs to be accounted for when calculating CLV. Odds comparison sites - OddsPortal, OddsChecker - record historical prices including closing prices for most regulated markets and are accessible for retrospective recording if you miss the window on the day. For markets where neither exchange nor comparison site data exists - some lower-league props, some niche competition totals - the best available closing price from whichever operator you track as your personal sharp benchmark is the fallback. In all cases, note which closing price source you used and be consistent within a category. Mixing Pinnacle closing prices with exchange prices with OddsPortal prices for the same market category produces CLV figures that aren't directly comparable.
Q: Is there a risk that tracking CLV too obsessively starts affecting your betting decisions negatively - avoiding markets with thin CLV history or over-staking in categories with strong CLV records?
A: Yes, and it's a real failure mode worth naming. The tracking is a diagnostic tool, not a decision-making algorithm. Using CLV history to identify which categories and operators to prioritise is the correct application. Using it to place larger stakes in categories with strong CLV records assumes that past CLV performance predicts future CLV performance within that category, which requires an underlying edge to be genuine and persistent rather than an artefact of a specific historical window. The overfitting article is the relevant framing here - a category with positive CLV across thirty bets might be genuine edge or might be a small-sample run of good timing. The tracking tells you where to look. The analytical work on whether the edge is structural still has to happen independently. The two should inform each other rather than either one replacing the other.
