John Olutubo
Data Analytics Project
Influencer Marketing Analysis
Table of contents
- Objective
- Data Source
- Stages
- Design
- Development
- Data Quality Tests
- Visualization
- Analysis
- Recommendations
Objective
Key Pain Point
Identify the top YouTube Influencers in the UK for 2024 to determine which influencers would be most effective for marketing campaigns throughout the year.
Ideal Solution
A dashboard is needed to provide insights into the top UK YouTubers in 2024, including:
- Subscriber count
- Total views
- Total videos
- Engagement metrics
This dashboard will enable the marketing team to make informed decisions on collaborations with YouTubers.
User Story
As the Head of Marketing, I need a dashboard analyzing YouTube channel data in the UK to identify top-performing channels based on metrics such as subscriber base and average views. This will help make informed collaboration decisions, maximizing the effectiveness of marketing campaigns.
Data Source
Data Requirements
Data on the top UK YouTubers in 2024 is required, including:
- Channel names
- Total subscribers
- Total views
- Total videos uploaded
Data Origin
The data is sourced from Kaggle (an Excel extract). Access the dataset here.
Stages
- Design
- Development
- Testing
- Analysis
Design
Dashboard Components
The dashboard should address the following questions:
- 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?
Dashboard Mockup
Visuals:
- Scorecards
- Treemap
- Horizontal bar chart
- Table
Tools
| Tool | Purpose |
|---|---|
| Excel | Data exploration |
| SQL Server | Data cleaning, testing, and analysis |
| Power BI | Data visualization via interactive dashboards |
| GitHub | Project documentation and version control |
| Mokkup AI | Dashboard wireframe/mockup design |
Development
Pseudocode
General approach:
- Retrieve the data
- Explore the data in Excel
- Load the data into SQL Server
- Clean the data using SQL
- Test the data using SQL
- Visualize the data in Power BI
- Generate insights
- Document and publish the findings
Data Exploration Notes
Initial observations:
- The dataset contains at least 4 necessary columns.
- The first column includes channel IDs separated by ‘@’; channel names need to be extracted.
- Some cells and headers are in a different language; this needs to be addressed.
- Excess data is present; irrelevant columns need to be removed.
Data Cleaning
Clean data should:
- Retain only relevant columns
- Have appropriate data types
- Contain no null values
Constraints:
| Property | Description |
|---|---|
| Number of Rows | 100 |
| Number of Columns | 4 |
Expected schema:
| Column Name | Data Type | Nullable |
|---|---|---|
| channel_name | VARCHAR | NO |
| total_subscribers | INTEGER | NO |
| total_views | INTEGER | NO |
| total_videos | INTEGER | NO |
Transforming the data
- Unnecessary columns are removed by selectively retaining those deemed necessary.
- YouTube channel names are extracted from the first column.
- Column renaming is performed utilizing aliases.
/*
# 1. Select the required columns
# 2. Extract the channel name from the 'NOMBRE' column
*/
-- 1.
SELECT
SUBSTRING(NOMBRE, 1, CHARINDEX('@', NOMBRE) -1) AS channel_name, -- 2.
total_subscribers,
total_views,
total_videos
FROM
top_uk_youtubers_2024
Creating 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, CHARINDEX('@', NOMBRE) -1) AS VARCHAR(100)) AS channel_name, -- 2.
total_subscribers,
total_views,
total_videos
-- 3.
FROM
top_uk_youtubers_2024
Data Quality Tests
Here are the data quality tests conducted:
Row count check
SQL QUERY
/*
# Count the total number of records (or rows) are in the SQL view
*/
SELECT
COUNT(*) AS no_of_rows
FROM
view_uk_youtubers_2024;
Output
Column count check
SQL query
/*
# Count the total number of columns (or fields) are in the SQL view
*/
SELECT
COUNT(*) AS column_count
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'view_uk_youtubers_2024'
Output
Data type check
SQL query
/*
# Check the data types of each column from the view by checking the INFORMATION SCHEMA view
*/
-- 1.
SELECT
COLUMN_NAME,
DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'view_uk_youtubers_2024';
Output
Duplicate count check
SQL query
/*
# 1. Check for duplicate rows in the view
# 2. Group by the channel name
# 3. Filter for groups with more than one row
*/
-- 1.
SELECT
channel_name,
COUNT(*) AS duplicate_count
FROM
view_uk_youtubers_2024
-- 2.
GROUP BY
channel_name
-- 3.
HAVING
COUNT(*) > 1;
Output
Visualization
Dashboard Overview
This visualization showcases the current standings of the Top UK Youtube Influencers for 2024.
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
Key questions addressed:
- 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. Top 10 YouTubers by 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. Top 3 channels with the Most Videos Uploaded
| Rank | Channel Name | Videos Uploaded |
|---|---|---|
| 1 | GRM Daily | 14,696 |
| 2 | Manchester City | 8,248 |
| 3 | Yogscast | 6,435 |
3. Top 3 channels with the Most Views
| Rank | Channel Name | Total Views (B) |
|---|---|---|
| 1 | DanTDM | 19.78 |
| 2 | Dan Rhodes | 18.56 |
| 3 | Mister Max | 15.97 |
4. Top 3 channels with 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. Top 3 channels with 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. Top 3 channels with the Highest Subscriber Engagement Rate
| 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, the focus is on metrics crucial for determining the expected ROI for the marketing client. These metrics primarily include:
- Subscribers
- Total views
- Videos uploaded
Validation
1. Youtubers with the most subscribers
Calculation breakdown
Campaign Idea: Product Placement
- 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
- 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
- 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
SQL query
/*
# 1. Define variables
# 2. Create a CTE that rounds the average views per video
# 3. Select the column 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 @campaignCost FLOAT = 50000.0; -- The campaign cost @ $50,000
-- 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) - @campaignCost) AS net_profit
FROM
ChannelData
-- 4.
WHERE
channel_name in ('NoCopyrightSounds', 'DanTDM', 'Dan Rhodes')
-- 5.
ORDER BY
net_profit DESC
Output
2. Youtubers with the most videos uploaded
Calculation breakdown
Campaign Idea: Sponsored Video Series
- 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)
- 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)
- 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
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
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
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
Discovery
Key Findings:
The analysis reveals that:
- NoCopyrightSOunds, Dan Rhodes, and DanTDM are the channels with the highest number of subscribers in the UK.
- GRM Daily, Man City, and Yogscast lead in terms of the most videos uploaded.
- DanTDM, Dan Rhodes, and Mister Max stand out as the channels with the most views.
- Entertainment channels prove valuable for broader reach. Channels consistently posting content and generating high engagement primarily focus on entertainment and music.
Recommendations
- For maximizing visibility, prioritizing collaboration with Dan Rhodes is advised, given that this channel boasts the highest number of YouTube subscribers in the UK.
- Considering the lower potential return on investments, it’s prudent to reassess collaborations with GRM Daily, Man City, and Yogcasts within the current budget constraints.
- If the goal is to maximize reach, collaborating with Mister Max is recommended. However, for long-term viability, partnerships with DanTDM and Dan Rhodes are preferable due to their substantial subscriber bases and consistently high view counts.
- Based on the analysis, the top three channels for collaboration are NoCopyrightSounds, DanTDM, and Dan Rhodes, as they consistently attract the most engagement on their channels.
Potential ROI
- A collaboration deal with Dan Rhodes is projected to yield a net profit of $1,065,000 per video.
- An influencer marketing contract with Mister Max could generate a net profit of $1,276,000 for the client.
- Opting for a product placement campaign with DanTDM may result in a net profit of approximately $484,000 per video. Alternatively, an influencer marketing campaign deal could yield a one-off net profit of $404,000.
- Considering NoCopyrightSounds, the potential net profit per video stands at $642,000, making it a viable option.
Action Plan
Based on the analysis, advancing a long-term partnership with the Dan Rhodes channel is the optimal choice for promoting the client’s products.
Engagements with the marketing client will be initiated to align on expected outcomes from this collaboration. Upon achieving the anticipated milestones, potential partnerships with DanTDM, Mister Max, and NoCopyrightSounds channels can be explored in the future.
Steps to Implement Recommended Decisions Effectively:
- Initiate contact with the teams behind each of these channels, starting with Dan Rhodes.
- Negotiate contracts within the allocated marketing campaign budgets.
- Launch the campaigns and monitor their performance against the established KPIs.
- Review campaign outcomes, gather insights, and optimize strategies based on feedback from converted customers and each channel’s audience.
Let’s connect on LinkedIn