This project was inspired by a recurring task I performed during my role as a Data Analyst within a local Value Betting Syndicate. However, to ensure confidentiality, the dataset used in this published project is a mock version and does not contain real data (You can find the source file here).
Tasks included:
Collecting and converting PDF wagering histories to Excel.
Cleaning data and verifying accuracy
Processing data as needed (datetime segmentation, odds standardization, calculated fields).
Calculated key metrics like ROI and P/L using pivot tables.
Incorporating calculated metrics into dashboard.
Project Impact:
This project, which I managed independently from start to finish:
Enhanced the efficiency and frequency of reporting processes within the organization.
By providing accurate and timely insights, my work facilitated smoother day-to-day operations and empowering strategic decision-making.
During the data processing phase of this project, several key steps were undertaken to refine and structure the dataset for in-depth analysis, including:
1. Separated Datetime column into Date and Time for detailed analysis.
2. Refined column labels for better interpretability and clarity.
3. Extracted and standardized odds data for consistency.
4. Cleanup Selection Column to remove irrelevant information.
5. Standardized data via 'Find and Replace' to unify the 'bet_type' and 'result' columns.
6. Extracted and cleansed market information from the event column for data integrity.
7. Profit and Loss (P/L) calculation into a new column to measure financial performance.
8. Introduced Live Bet column to mark live bets based on the selection column data.
9. Calculated ommited data, where possible, to enhance dataset reliability and completeness.
10. Created Odds Buckets to categorize bets and analyze outcomes.
11. Classified Market Category to categorize bets and analyze market-specific trends.
The processed document went from this:
To this:
Next, I used pivot table functionalities to calculate key metrics to assess the strategic effectiveness and financial health of the betting account, including:
Return on Investment (ROI): Evaluates the profitability of betting activities.
Picks Volume: Reflects the frequency of betting activity
Hit Rate: Measures the precision of bet selections.
Profit/Loss (P/L): Financial summary indicating net outcomes.
Average Stake: Reveals the standard bet size, indicating the risk tolerance.
Average Odds: Offers insights into the risk-return profile.
Below is the comparison of each these to their twelve month average for a quick
This mock data for this account showed around average results on 'Hit Rate', 'Average Stake' and 'Average odds'.
However, the amount of bets this account placed (47% above average) and a rare ROI performance (60% above average) drove its high profitability compared to a 12-months average P/L. The high ROI, coupled with the insights from the 'Monthly Growth' in the dashboard below might indicate that the account was in a pick in variance.
The dashboard below effectively captures all the critical information. It allows for advanced filtering based on Market Category, Bet Type, Odds group, and calendar Month. For example, we can easily see here, that 'Handicaps' were responsible for most of the P/L and almost 2/3 of the picks on this account.
While here we see that the Odds bucket 1.5-1.99 in December returned 110% of the total P/L. That is how a great run looks like!
This project, which I managed independently from start to finish:
Enhanced the efficiency and frequency of reporting processes within the organization.
By providing accurate and timely insights, my work facilitated smoother day-to-day operations and empowering strategic decision-making.
Thanks for reading. Feel free to reach out for any comment.