Top UK Youtubers 2024

Top-Youtubers-animated-gif

Table of Contents

Objective

To discover the top performing UK Youtubers to form marketing collaborations with, throughout the year 2024.

User Story

As the Head of Marketing, I want to identify the top YouTubers in the UK based on subscriber count, videos uploaded and views accumulated, so that I can decide on which channels would be best to run marketing campaigns with to generate a good ROI.

Data source

We need data on the top UK YouTubers in 2024 that includes their

Stages

Design

Dashboard components required

To understand what it should contain, we need to figure out what questions we need the dashboard to answer:

  1. Who are the top 10 YouTubers with the most subscribers?
  2. Which 3 channels have uploaded the most videos?
  3. Which 3 channels have the most views?
  4. Which 3 channels have the highest average views per video?
  5. Which 3 channels have the highest views per subscriber ratio?
  6. Which 3 channels have the highest subscriber engagement rate per video uploaded?

Dashboard mockup

Some of the data visuals that may be appropriate in answering our questions include:

  1. Table
  2. Treemap
  3. Scorecards
  4. Horizontal bar chart

Dashboard-Mockup

Tools Used

Tool Purpose
Excel Exploring the data
SQL Server Cleaning, testing, and analyzing the data
Power BI Visualizing the data via interactive dashboards
GitHub Hosting the project documentation
Mokkup AI Designing the mockup of the dashboard

Development

Pseudocode

Data exploration notes

This is the stage where we scan the data for errors, inconcsistencies, bugs, weird and corrupted characters etc.

  1. There are at least 4 columns that contain the data we need for this analysis, which signals we have everything we need from the file without needing to contact the client for any more data.
  2. The first column contains the channel ID with what appears to be channel IDS, which are separated by a @ symbol - we need to extract the channel names from this.
  3. Some of the cells and header names are in a different language - we need to confirm if these columns are needed, and if so, we need to address them.
  4. We have more data than we need, so some of these columns would need to be removed

Data cleaning

The aim is to refine the dataset to ensure it is structured and ready for analysis.

The cleaned data should meet the following criteria and constraints:

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
  1. Remove unnecessary columns by only selecting the ones you need
  2. Extract Youtube channel names from the first column
  3. Rename columns using aliases

Transform the data

/* 
Data Cleaning Steps
1. Remove Unnecessary columns by only selecting the ones we need.
2. Extract the Youtube channel names from the first column.
3. Rename the column names.
*/

SELECT 
    NOMBRE, total_subscribers, total_videos, total_views
FROM
    top_uk_youtubers_2024;

SELECT 
    SUBSTRING(Nombre,
            1,
            LOCATE('@', Nombre) - 1)
            AS Channel_Name, total_subscribers, total_views, total_videos
FROM
    top_uk_youtubers_2024

Create the SQL view

/*
# 1. Create a view to store the transformed data
# 2. Cast the extracted channel name as VARCHAR(100)
# 3. Select the required columns from the top_uk_youtubers_2024 SQL table 
*/

-- 1.
CREATE VIEW view_uk_youtubers_2024 AS

-- 2.
SELECT
   CAST(SUBSTRING(Nombre,
            1,
            LOCATE('@', Nombre) - 1)
        AS CHAR (100)) AS Channel_Name, total_subscribers, total_views, total_videos

-- 3.
FROM
    top_uk_youtubers_2024

Testing

Here are the data quaity tests conducted

Row Count check

/*
Data Quality Tests

1. The data needs to be 100 records of youtube channels (row count check). --- (passed)
*/

SELECT
    COUNT(*) AS no_of_rows
FROM
    view_uk_youtubers_2024;

Column Count check

/*
Data Quality Tests

2. The data needs 4 fields (column count test). --- (passed)
*/

SELECT 
    COUNT(*) AS coulmns_count
FROM
    Information_Schema.Columns
WHERE
    table_name = 'view_uk_youtubers_2024';

Data Type check

/*
Data Quality Tests

3. Channel name column must be string format and the other columns must be numerical data types (data type check). --(passed)
*/

SELECT 
    Column_name, Data_type
FROM
    Information_Schema.Columns
WHERE
    table_name = 'view_uk_youtubers_2024';

Duplicate Count check

/*
Data Quality Tests

4. Each record must be unique in the dataset (duplicate count check). -- (passed)
*/

SELECT 
    channel_name, COUNT(*) AS duplicate
FROM
    view_uk_youtubers_2024
GROUP BY channel_name
HAVING COUNT(*) > 1

Visualisation

Results

Power BI Visualisation

PowerBI Dashboard

This is an interactive dashboard showing the Top Uk Youtubers in 2024 so far

DAX Measures

1. Total Subscribers (M)

Total Subscribers (M) = 
VAR million = 1000000
VAR sumOfSubscribers = SUM(view_uk_youtubers_2024[total_subscribers])
VAR totalSubscribers = DIVIDE(sumOfSubscribers,million)

RETURN totalSubscribers

2. Total Views (B)

Total Views (B) = 
VAR billion = 1000000000
VAR sumOfTotalViews = SUM(view_uk_youtubers_2024[total_views])
VAR totalViews = ROUND(sumOfTotalViews / billion, 2)

RETURN totalViews

3. Total Videos

Total Videos = 
VAR totalVideos = SUM(view_uk_youtubers_2024[total_videos])

RETURN totalVideos

4. Average Views Per Video (M)

Average Views per Video (M) = 
VAR sumOfTotalViews = SUM(view_uk_youtubers_2024[total_views])
VAR sumOfTotalVideos = SUM(view_uk_youtubers_2024[total_videos])
VAR  avgViewsPerVideo = DIVIDE(sumOfTotalViews,sumOfTotalVideos, BLANK())
VAR finalAvgViewsPerVideo = DIVIDE(avgViewsPerVideo, 1000000, BLANK())

RETURN finalAvgViewsPerVideo

5. Subscriber Engagement Rate

Subscriber Engagement Rate = 
VAR sumOfTotalSubscribers = SUM(view_uk_youtubers_2024[total_subscribers])
VAR sumOfTotalVideos = SUM(view_uk_youtubers_2024[total_videos])
VAR subscriberEngRate = DIVIDE(sumOfTotalSubscribers, sumOfTotalVideos, BLANK())

RETURN subscriberEngRate

6. Views per subscriber

Views Per Subscriber = 
VAR sumOfTotalViews = SUM(view_uk_youtubers_2024[total_views])
VAR sumOfTotalSubscribers = SUM(view_uk_youtubers_2024[total_subscribers])
VAR viewsPerSubscriber = DIVIDE(sumOfTotalViews, sumOfTotalSubscribers, BLANK())

RETURN viewsPerSubscriber

Analysis

Findings

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:

  1. Who are the top 10 YouTubers with the most subscribers?
  2. Which 3 channels have uploaded the most videos?
  3. Which 3 channels have the most views?
  4. Which 3 channels have the highest average views per video?
  5. Which 3 channels have the highest views per subscriber ratio?
  6. 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?

Channel Name Averge Views per Video (M)
Mark Ronson 32.27
Jessie J 5.97
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 prioritize analysing the metrics that are important in generating the expected ROI for our marketing client, which are the YouTube channels wuth the most

Validation

1. Youtubers with the most subscribers

Calculation breakdown

Campaign idea = product placement

  1. 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

c. Dan Rhodes

Best option from category: Dan Rhodes

SQL Query

/*

1. Define the Variables
2. Create the CTE that rounds the average views per video
3. Select the columns that are required for the analysis
4. Filter the results by Youtube channels with the highest subscriber bases
5. Order by net profits (From highest to lowest)

*/

-- 1. 
Set @conversionrate = 0.02; 			-- Conversion rate at 2%
Set @productcost = 5.0; 				-- Product cost at $5.0
Set @campaigncost = 50000;				-- The Campaign Cost at $50000

-- 2. 
WITH channeldata AS (
Select 
channel_name,
total_views,
total_videos,
round(cast(total_views as float ) / total_videos , -4) as rounded_avg_views_per_video
from 
view_uk_youtubers_2024)


-- 3.
Select
channel_name,
rounded_avg_views_per_video,
(rounded_avg_views_per_video * @conversionrate) as potential_units_sold_per_video,
(rounded_avg_views_per_video * @conversionrate * @productcost) as potential_revenue_per_video,
(rounded_avg_views_per_video * @conversionrate * @productcost) - @campaigncost as net_profit
from channeldata

-- 4.
Where trim(Channel_Name) in ('NoCopyrightSounds', 'DanTDM', 'Dan Rhodes')

-- 5.
order by
net_profit desc

Output

Most Subscribers

2. Youtubers with the most videos uploaded

Calculation breakdown

Campaign idea = sponsored video series

  1. 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

b. Yogscast

Best option from category: Yogscast

SQL query

/* 
# 1. Define variables
# 2. Create a CTE that rounds the average views per video
# 3. Select the columns you need and create calculated columns from existing ones
# 4. Filter results by YouTube channels
# 5. Sort results by net profits (from highest to lowest)
*/


-- 1.
DECLARE @conversionRate FLOAT = 0.02;           -- The conversion rate @ 2%
DECLARE @productCost FLOAT = 5.0;               -- The product cost @ $5
DECLARE @campaignCostPerVideo FLOAT = 5000.0;   -- The campaign cost per video @ $5,000
DECLARE @numberOfVideos INT = 11;               -- The number of videos (11)


-- 2.
WITH ChannelData AS (
    SELECT
        channel_name,
        total_views,
        total_videos,
        ROUND((CAST(total_views AS FLOAT) / total_videos), -4) AS rounded_avg_views_per_video
    FROM
        youtube_db.dbo.view_uk_youtubers_2024
)


-- 3.
SELECT
    channel_name,
    rounded_avg_views_per_video,
    (rounded_avg_views_per_video * @conversionRate) AS potential_units_sold_per_video,
    (rounded_avg_views_per_video * @conversionRate * @productCost) AS potential_revenue_per_video,
    ((rounded_avg_views_per_video * @conversionRate * @productCost) - (@campaignCostPerVideo * @numberOfVideos)) AS net_profit
FROM
    ChannelData


-- 4.
WHERE
    channel_name IN ('GRM Daily', 'Man City', 'YOGSCAST Lewis & Simon ')


-- 5.
ORDER BY
    net_profit DESC;

Output

Most videos

3. Youtubers with the most views

Calculation breakdown

Campaign idea = Influencer marketing

a. DanTDM

b. Dan Rhodes

c. Mister Max

Best option from category: Mister Max

SQL query

/*
# 1. Define variables
# 2. Create a CTE that rounds the average views per video
# 3. Select the columns you need and create calculated columns from existing ones
# 4. Filter results by YouTube channels
# 5. Sort results by net profits (from highest to lowest)
*/



-- 1.
DECLARE @conversionRate FLOAT = 0.02;        -- The conversion rate @ 2%
DECLARE @productCost MONEY = 5.0;            -- The product cost @ $5
DECLARE @campaignCost MONEY = 130000.0;      -- The campaign cost @ $130,000



-- 2.
WITH ChannelData AS (
    SELECT
        channel_name,
        total_views,
        total_videos,
        ROUND(CAST(total_views AS FLOAT) / total_videos, -4) AS avg_views_per_video
    FROM
        youtube_db.dbo.view_uk_youtubers_2024
)


-- 3.
SELECT
    channel_name,
    avg_views_per_video,
    (avg_views_per_video * @conversionRate) AS potential_units_sold_per_video,
    (avg_views_per_video * @conversionRate * @productCost) AS potential_revenue_per_video,
    (avg_views_per_video * @conversionRate * @productCost) - @campaignCost AS net_profit
FROM
    ChannelData


-- 4.
WHERE
    channel_name IN ('Mister Max', 'DanTDM', 'Dan Rhodes')


-- 5.
ORDER BY
    net_profit DESC;

Output

Most views

Discovery

We discovered that

  1. NoCopyrightSOunds, Dan Rhodes and DanTDM are the channnels with the most subscribers in the UK
  2. GRM Daily, Man City and Yogscast are the channels with the most videos uploaded
  3. DanTDM, Dan RHodes and Mister Max are the channels with the most views
  4. 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

  1. 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
    1. 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.
  3. 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.
  4. 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

  1. Setting up a collaboration deal with Dan Rhodes would make the client a net profit of $1,065,000 per video
  2. An influencer marketing contract with Mister Max can see the client generate a net profit of $1,276,000
  3. 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.
  4. NoCopyrightSounds could profit the client $642,000 per video too (which is worth considering)

Action plan

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.

  1. Reach out to the teams behind each of these channels, starting with Dan Rhodes
  2. Negotiate contracts within the budgets allocated to each marketing campaign
  3. Kick off the campaigns and track each of their performances against the KPIs
  4. Review how the campaigns have gone, gather insights and optimize based on feedback from converted customers and each channel’s audiences