Top_UK_YouTubers_2024
Sayo Odufuwa
Like this project
Posted Aug 2, 2024
Contribute to Odufuwa03/Top_UK_YouTubers_2024 development by creating an account on GitHub.
Likes
0
Views
4
Top_UK_YouTubers_2024
Table of Contents
Objective
Key Pain Point
The Head of Marketing needs to identify the top YouTubers in 2024 to determine the best influencers for marketing campaigns throughout the year.
Ideal Solution
Develop a dashboard that offers insights into the leading UK YouTubers in 2024, including:
Subscriber count
Total views
Total videos
Engagement metrics
This dashboard will enable the marketing team to make informed decisions about which YouTubers to collaborate with for their campaigns.
User Story
As the Head of Marketing, I seek to utilize a dashboard that analyzes YouTube channel data in the UK.
This dashboard should enable the identification of top-performing channels based on metrics such as subscriber count and average views.
Armed with this information, I can make more informed decisions about selecting the right YouTubers for collaborations, thereby maximizing the effectiveness of each marketing campaign.
Data Source
What data is needed to achieve our objective? We need data on the top UK YouTubers in 2024 that includes their
channel names
total subscribers
total views
total videos uploaded
Where is the data coming from? The data is sourced from Kaggle (an Excel extract), see here to find it
Stages
Design
Developement
Testing
Analysis
Design
Dashboard components required
What should the dashboard contain based on the requirements provided?
To understand what it should contain, we need to figure out what questions we need the dashboard to answer:
Who are the top 10 YouTubers with the most subscribers?
Which 3 channels have uploaded the most videos?
Which 3 channels have the most views?
Which 3 channels have the highest average views per video?
Which 3 channels have the highest views per subscriber ratio?
Which 3 channels have the highest subscriber engagement rate per video uploaded?
For now, these are some of the questions we need to answer, this may change as we progress down our analysis.
Tools
Tools Purpose Excel Exploring the data SQL Server Cleaning, testing, and analyzing the data Power BI Visualizing the data via interactive dashboards
Development
Pseudocode
What's the general approach in creating this solution from start to finish?
Get the data
Explore the data in Excel
Load the data into SQL Server
Clean the data with SQL
Test the data with SQL
Visualize the data in Power BI
Generate the findings based on the insights
Write the documentation + commentary
Publish the data to GitHub Pages
Data exploration notes
Initial Data Assessment
This is the stage where we scan the data for errors, inconsistencies, bugs, and any unusual or corrupted characters.
Initial Observations:
Relevant Columns: There are at least four columns containing the necessary data for our analysis, indicating that we have all required information without needing to contact the client for additional data.
Channel IDs: The first column contains channel IDs, separated by an @ symbol. We need to extract the channel names from these IDs.
Language Discrepancies: Some cells and header names are in a different language. We need to verify if these columns are essential for our analysis and address any language-related issues if they are.
Excess Data: The dataset contains more columns than needed. We will need to remove the extraneous columns to streamline our analysis.
Data Cleaning
What do we expect the clean data to look like? (What should it contain? What contraints should we apply to it?) The aim is to refine our dataset to ensure it is structured and ready for analysis.
The cleaned data should meet the following criteria and constraints:
Only relevant columns should be retained.
All data types should be appropriate for the contents of each column.
No column should contain null values, indicating complete data for all records. Below is a table outlining the constraints on our cleaned dataset:
Property Description Number of Rows 100 Number of Columns 4
And here is a tabular representation of the expected schema for the clean data:
Column Name Data Type Nullable channel_name VARCHAR NO total_subscribers INTEGER NO total_views INTEGER NO total_videos INTEGER NO
What steps are needed to clean and shape the data into the desired format?
Remove unnecessary columns by only selecting the ones you need
Extract Youtube channel names from the first column
Rename columns using aliases
Transform the data
/*
# Data cleaning steps
1. Remove unnecessary columns by selecting the ones we need
2. Extract youtube channel names from first columns
3. Rename the columns names
*/
-- 1.
select
NOMBRE,
total_subscribers,
total_videos,
total_views
from
top_uk_youtubers_2024
-- 2.
-- CHAIRINDEX
SELECT CHARINDEX('@', NOMBRE), NOMBRE FROM top_uk_youtubers_2024
-- SUBSTRING
-- 3.
SELECT
CAST (SUBSTRING(NOMBRE, 1, CHARINDEX('@', NOMBRE) -1) AS varchar(100)) AS channel_name,
total_subscribers,
total_videos,
total_views
FROM
top_uk_youtubers_2024
## Create the SQL view
```sql
CREATE VIEW view_top_uk_youtubers_2024 AS
SELECT
CAST (SUBSTRING(NOMBRE, 1, CHARINDEX('@', NOMBRE) -1) AS varchar(100)) AS channel_name,
total_subscribers,
total_videos,
total_views
FROM
top_uk_youtubers_2024
Testing
What data quality and validation checks are you going to create? Here are the data quality tests conducted:
Row count check
--- Row count check
SELECT COUNT(*) as no_of_rows
FROM [youtube_db].[dbo].[view_top_uk_youtubers_2024]
Output
Column count check
-- 2. Column count check
SELECT
COUNT (*) AS column_count
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'view_top_uk_youtubers_2024'
Output
Data type check
-- 3. Data types check
SELECT
COLUMN_NAME,
DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'view_top_uk_youtubers_2024'
Output
Duplicate count check
-- 4. Duplicate records check
SELECT
channel_name,
COUNT(*) AS duplicat_count
FROM
view_top_uk_youtubers_2024
GROUP BY
channel_name
HAVING
COUNT(*) > 1
Output
Visualization
Results
What does the visualization look like?
This shows the Top UK Youtubers in 2024 so far.
DAX Measures
1. Total Subscribers (M)
2. Total Videos
3. Total Views (B)
4. Views Per subscriber
5. Avg Views per Videos (M)
6.Subscriber Engagement Rate
Analysis
Findings
What did we find? For this analysis, we're going to focus on the questions below to get the information we need for our marketing client -
Here are the key questions we need to answer for our marketing client:
Who are the top 10 YouTubers with the most subscribers?
Which 3 channels have uploaded the most videos?
Which 3 channels have the most views?
Which 3 channels have the highest average views per video?
Which 3 channels have the highest views per subscriber ratio?
Which 3 channels have the highest subscriber engagement rate per video uploaded?
1. Who are the top 10 YouTubers with the most subscribers?
Rank Channel Name Subscribers (M) 1 NoCopyrightSounds 33.60 2 DanTDM 28.60 3 Dan Rhodes 26.50 4 Miss Katy 24.50 5 Mister Max 24.40 6 KSI 24.10 7 Jelly 23.50 8 Dua Lipa 23.30 9 Sidemen 21.00 10 Ali-A 18.90
2. Which 3 channels have uploaded the most videos?
Rank Channel Name Videos Uploaded 1 GRM Daily 14,696 2 Manchester City 8,248 3 Yogscast 6,435
3. Which 3 channels have the most views?
Rank Channel Name Total Views (B) 1 DanTDM 19.78 2 Dan Rhodes 18.56 3 Mister Max 15.97
4. Which 3 channels have the highest average views per video?
Rank Channel Name Averge Views per Video (M) 1 Mark Ronson 32.27 2 Jessie J 5.97 3 Dua Lipa 5.76
5. Which 3 channels have the highest views per subscriber ratio?
Rank Channel Name Views per Subscriber 1 GRM Daily 1185.79 2 Nickelodeon 1061.04 3 Disney Junior UK 1031.97
6. Which 3 channels have the highest subscriber engagement rate per video uploaded?
Rank Channel Name Subscriber Engagement Rate 1 Mark Ronson 343,000 2 Jessie J 110,416.67 3 Dua Lipa 104,954.95
Notes
For this analysis, we’ll prioritize analysing the metrics that are important in generating the expected ROI for our marketing client, which are the YouTube channels wuth the most
subscribers
total views
videos uploaded
Validation
1. Youtubers with the most subscribers
Calculation breakdown
Campaign idea = product placement
a. NoCopyrightSounds
Average views per video = 6.92 million
Product cost = $5
Potential units sold per video = 6.92 million x 2% conversion rate = 138,400 units sold
Potential revenue per video = 138,400 x $5 = $692,000
Campaign cost (one-time fee) = $50,000
Net profit = $692,000 - $50,000 = $642,000
b. DanTDM
Average views per video = 5.34 million
Product cost = $5
Potential units sold per video = 5.34 million x 2% conversion rate = 106,800 units sold
Potential revenue per video = 106,800 x $5 = $534,000
Campaign cost (one-time fee) = $50,000
Net profit = $534,000 - $50,000 = $484,000
c. Dan Rhodes
Average views per video = 11.15 million
Product cost = $5
Potential units sold per video = 11.15 million x 2% conversion rate = 223,000 units sold
Potential revenue per video = 223,000 x $5 = $1,115,000
Campaign cost (one-time fee) = $50,000
Net profit = $1,115,000 - $50,000 = $1,065,000
Best option from category: Dan Rhodes
2. Youtubers with the most videos uploaded
Calculation breakdown
Campaign idea = sponsored video series
a. GRM Daily
Average views per video = 510,000
Product cost = $5
Potential units sold per video = 510,000 x 2% conversion rate = 10,200 units sold
Potential revenue per video = 10,200 x $5= $51,000
Campaign cost (11-videos @ $5,000 each) = $55,000
Net profit = $51,000 - $55,000 = -$4,000 (potential loss)
b. Manchester City
Average views per video = 240,000
Product cost = $5
Potential units sold per video = 240,000 x 2% conversion rate = 4,800 units sold
Potential revenue per video = 4,800 x $5= $24,000
Campaign cost (11-videos @ $5,000 each) = $55,000
Net profit = $24,000 - $55,000 = -$31,000 (potential loss)
b. Yogscast
Average views per video = 710,000
Product cost = $5
Potential units sold per video = 710,000 x 2% conversion rate = 14,200 units sold
Potential revenue per video = 14,200 x $5= $71,000
Campaign cost (11-videos @ $5,000 each) = $55,000
Net profit = $71,000 - $55,000 = $16,000 (profit)
Best option from category: Yogscast
3. Youtubers with the most views
Calculation breakdown
Campaign idea = Influencer marketing
a. DanTDM
Average views per video = 5.34 million
Product cost = $5
Potential units sold per video = 5.34 million x 2% conversion rate = 106,800 units sold
Potential revenue per video = 106,800 x $5 = $534,000
Campaign cost (3-month contract) = $130,000
Net profit = $534,000 - $130,000 = $404,000
b. Dan Rhodes
Average views per video = 11.15 million
Product cost = $5
Potential units sold per video = 11.15 million x 2% conversion rate = 223,000 units sold
Potential revenue per video = 223,000 x $5 = $1,115,000
Campaign cost (3-month contract) = $130,000
Net profit = $1,115,000 - $130,000 = $985,000
c. Mister Max
Average views per video = 14.06 million
Product cost = $5
Potential units sold per video = 14.06 million x 2% conversion rate = 281,200 units sold
Potential revenue per video = 281,200 x $5 = $1,406,000
Campaign cost (3-month contract) = $130,000
Net profit = $1,406,000 - $130,000 = $1,276,000
Best option from category: Mister Max
Discovery
What did we learn?
We discovered that
NoCopyrightSOunds, Dan Rhodes and DanTDM are the channnels with the most subscribers in the UK
GRM Daily, Man City and Yogscast are the channels with the most videos uploaded
DanTDM, Dan RHodes and Mister Max are the channels with the most views
Entertainment channels are useful for broader reach, as the channels posting consistently on their platforms and generating the most engagement are focus on entertainment and music
Recommendations
What do you recommend based on the insights gathered?
Dan Rhodes is the best YouTube channel to collaborate with if we want to maximize visbility because this channel has the most YouTube subscribers in the UK
Although GRM Daily, Man City and Yogcasts are regular publishers on YouTube, it may be worth considering whether collaborating with them with the current budget caps are worth the effort, as the potential return on investments is significantly lower compared to the other channels.
Mister Max is the best YouTuber to collaborate with if we're interested in maximizing reach, but collaborating with DanTDM and Dan Rhodes may be better long-term options considering the fact that they both have large subscriber bases and are averaging significantly high number of views.
The top 3 channels to form collaborations with are NoCopyrightSounds, DanTDM and Dan Rhodes based on this analysis, because they attract the most engagement on their channels consistently.
Potential ROI
What ROI do we expect if we take this course of action?
Setting up a collaboration deal with Dan Rhodes would make the client a net profit of $1,065,000 per video
An influencer marketing contract with Mister Max can see the client generate a net profit of $1,276,000
If we go with a product placement campaign with DanTDM, this could generate the client approximately $484,000 per video. If we advance with an influencer marketing campaign deal instead, this would make the client a one-off net profit of $404,000.
NoCopyrightSounds could profit the client $642,000 per video too (which is worth considering)
Action plan
What course of action should we take and why?
Based on our analysis, we beieve the best channel to advance a long-term partnership deal with to promote the client's products is the Dan Rhodes channel.
We'll have conversations with the marketing client to forecast what they also expect from this collaboration. Once we observe we're hitting the expected milestones, we'll advance with potential partnerships with DanTDM, Mister Max and NoCopyrightSounds channels in the future.
What steps do we take to implement the recommended decisions effectively?
Reach out to the teams behind each of these channels, starting with Dan Rhodes
Negotiate contracts within the budgets allocated to each marketing campaign
Kick off the campaigns and track each of their performances against the KPIs
Review how the campaigns have gone, gather insights and optimize based on feedback from converted customers and each channel's audiences