Skip to content
BranchesTags

Repository files navigation

Header Image

Implemented an ETL pipeline for YouTube channel analytics using the YouTube API, enriched with sentiment analysis via Hugging Face. Orchestrated workflows with Airflow and Docker on AWS, storing data in MySQL. Visualized key metrics and comparative insights in an interactive Power BI dashboard.

📋 Table of Contents

  1. Summary
  2. Motivation
  3. ETL Pipeline
  4. Data Visualization
  5. Getting Started
  6. License
  7. Credits

🎯 Summary

To empower YouTube content creators and marketers with actionable insights into their channel's performance, especially in comparison to related channels, I developed a comprehensive ETL pipeline and designed an interactive Power BI report. This project involved:

  • Data Extraction: Utilized the YouTube API to gather extensive data from three selected channels, including videos and comments.
  • Data Transformation: Performed sentiment analysis on video comments via API requests to a RoBERTa sentiment analysis model, which I deployed using Gradio on a private Hugging Face Space.
  • Data Loading: Stored the transformed data in a MySQL database hosted on AWS.
  • Automation: Managed the ETL workflow using Apache Airflow, Docker, and AWS.
  • Data Visualization: Designed an interactive Power BI report to deliver insights into channel performance, featuring key metrics and comparative analysis.

This project enables YouTube content creators to easily monitor and evaluate their channel's performance relative to their peers, allowing for more informed decision-making and strategic planning.

🛠️ Built With

  • Python
  • Pandas
  • MySQL
  • Airflow
  • Docker
  • AWS
  • Hugging Face
  • Gradio
  • Power BI
  • Visual Studio Code
  • Jupyter Notebook

(back to top)

💡 Motivation

  • Problem: Analyzing and comparing the performance of multiple YouTube channels is crucial for content creators and marketers. Most available tools focus on single-channel analytics, making it difficult to perform comparisons with similar YouTube channels. Additionally, understanding audience sentiment towards content is often overlooked, despite its significant impact on channel growth and engagement.
  • Project Goal: Empower content creators and marketers with insights into a YouTube channel's performance and audience sentiment to enable informed decision-making and content optimization strategies. This is achieved by developing an automated ETL pipeline and by providing insightful visualizations.

(back to top)

🔄 ETL Pipeline

Built using Apache Airflow to automate the extraction, transformation, and loading of data from multiple YouTube channels.

  • Data Extraction: Utilized the YouTube API to gather comprehensive data from three selected channels, including video metadata, view counts, likes, comments, and more.
  • Data Transformation: Performed sentiment analysis on video comments using a RoBERTa model, featuring 125 million parameters, trained on ~124 million tweets and fine-tuned using the TweetEval benchmark. Deployed the model on a private Hugging Face Space using Gradio and integrated it into the ETL pipeline via API requests.
  • Data Loading: Stored the transformed data in a MySQL database hosted on an AWS RDS instance, ensuring persistent storage and facilitating easy access for comparative analysis.
  • Automation: Orchestrated the ETL workflow using Apache Airflow with Docker, hosted on an AWS EC2 t2.micro instance.

ETL pipeline

(back to top)

📊 Data Visualization

Created an interactive Power BI report offering in-depth insights into channel performance and audience engagement. The report queries data directly from the MySQL database hosted on AWS RDS, ensuring that the visualizations are always based on the most up-to-date information.

Home Page: Provides a comprehensive overview, including total subscribers, views, likes, and averages per video and per 1000 views. Users can easily compare multiple channels side-by-side to understand channel performance across key metrics relative to their peers. Users can also filter data by specific time periods.

Power BI Home

Comments Page: Shows total comments, per-video and per-1000-view averages, monthly trends, and sentiment analysis. Users can select time periods for granular analysis.

Power BI Comments

Videos Page: Displays total video counts, average videos uploaded per month, average video length, and monthly upload trends over time. Like other pages, time period filtering is available, facilitating a deeper understanding of content production.

Power BI Videos

Top 5 Videos Page: Ranks each channel's top 5 videos based on views, likes, or comments with clickable video links, enabling a tailored exploration of high-performing content.

Power BI Top 5 Videos

The report enables users to navigate interactively through metrics and time periods to discover trends in channel performance and audience engagement.

(back to top)

🚀 Getting Started

Follow these steps to set up the required infrastructure and tools for the project.

Prerequisites

Ensure you have the following tools and services:

  • AWS Account:
    • EC2 instance: Running Amazon Linux 2 (t2.micro for free tier) to host the ETL pipeline.
    • RDS instance: To host a MySQL database.
  • Google Account: To create a YouTube API key for data extraction.
  • SSH Client: To connect your local machine to EC2 and set up an SSH tunnel.
  • Docker: Installed on the EC2 instance to containerize the ETL pipeline.
  • Apache Airflow: Running inside Docker containers on EC2 to orchestrate the ETL pipeline.
  • Hugging Face Account: To host the sentiment analysis model on Hugging Face Spaces.
  • Power BI Desktop: Installed on your local machine and connected to the AWS MySQL database to visualize data.

(back to top)

Set Up AWS

Launch EC2 Instance

  • Go to the AWS Management Console and create a new EC2 instance.
    • Select Amazon Linux 2 as the operating system.
    • Choose the t2.micro instance type for free-tier eligibility.
  • Configure EC2 Security Groups:
    • SSH (port 22) from your local machine’s IP address to connect via SSH.
    • Airflow Webserver (port 8080) from your local machine’s IP address to access the Airflow Webserver UI.
    • MySQL (port 3306) from the EC2 instance’s security group for database connections to the RDS instance.
    • HTTP (port 80) and HTTPS (port 443) to all IP addresses to download packages.

Launch RDS MySQL Instance

  • Go to the AWS RDS Console and create a MySQL instance.
    • Choose the Free Tier option (db.t2.micro).
    • Set Public Accessibility to No for added security (you’ll access it via SSH tunnel).
    • Create a new database named youtube_analytics.
    • Save the RDS endpoint, username, and password for later use.
  • Configure RDS Security Group:
    • Allow inbound connections on port 3306 from the EC2 instance's security group.

(back to top)

Connect to EC2 and Set Up SSH Tunnel

  • SSH Connection: Use PuTTY on Windows (or your preferred SSH client) to connect:
    • Host Name: <your-ec2-public-ip-address>
    • Port: 22
    • Authentication: Use your .ppk private key file.
  • Set up SSH tunnel for RDS access:
    • In PuTTY, go to Connection > SSH > Tunnels.
    • Source Port: 3308 (port on your local machine).
    • Destination: <your-rds-endpoint>:3306 (port on the RDS instance for MySQL).
    • Click Add, then Open to establish the SSH connection.
    • This will forward traffic from port 3308 on your local machine to the RDS instance's MySQL port (3306) via the EC2 instance.

(back to top)

Set Up YouTube API

  • Create a YouTube API Key
    • Go to the Google Cloud Console.
    • Navigate to APIs & Services > Credentials.
    • Click Create Credentials and select API Key.
    • Save the API Key. You will store it in a .env file as explained later.
  • To improve security, restrict the usage of your API key to specific IP addresses:
    • Under Edit API key, go to Set an application restriction and select IP addresses.
    • Add the following IP addresses:
      • Your local machine's public IP address.
      • Your AWS EC2 instance's public IP address.
  • To further improve security, restrict the API key to only allow access to the YouTube Data API v3:
    • Under Edit API key, go to API restrictions and select Restrict key.
    • From the dropdown menu, choose YouTube Data API v3.
    • Click Save to apply the changes.

(back to top)

Set Up Hugging Face

  • Set up the sentiment analysis model on Hugging Face Spaces:
    • Log in to your Hugging Face account and create a new Space:
      • Choose Space hardware: "CPU basic 2 vCPU 16 GB FREE" for free-tier eligibility.
      • Select "Private" Space for better security.
    • Upload the following files from this repo's huggingface_space subdirectory:
      • app_roberta.py: Contains the code for deploying the RoBERTa sentiment analysis model as a Gradio web application with API endpoint.
      • requirements.txt: Lists the Python dependencies needed for model deployment on Hugging Face Spaces.
    • After deployment, note your Space's name (e.g., "YourUserName/roberta-sentiment-analysis-api") for later use.
  • Create a Hugging Face access token:
    • Go to your Hugging Face account settings.
    • Navigate to Settings > Access Tokens, generate a new token and save it for later use.

(back to top)

Manage Environment Variables

  • To store sensitive information, create a .env file on your local machine with the following content: 
    aws_mysql_endpoint = <your-rds-endpoint>
aws_mysql_user = <your-rds-username>
aws_mysql_password = <your-rds-password>

youtube_api_key = <your-youtube-api-key>

huggingface_space_name = <your-hf-space-name>
huggingface_access_token = <your-hf-access-token>
  • Replace the placeholders with your actual values, and ensure that AWS RDS, YouTube API and Hugging Face credentials are stored here.
  • Upload .env file from your local machine to EC2 using an SCP client (or similar tool): 
    scp -i <your-key.pem> .env ec2-user@<your-ec2-public-ip-address>:/home/ec2-user/
  • The .env file will be used to load environment variables into Docker, keeping sensitive information safe and accessible to your Airflow DAG code.

(back to top)

Set Up Docker and Apache Airflow on EC2

Install Docker: Once connected to your EC2 instance, run the following commands to install Docker.

sudo yum update -y
sudo amazon-linux-extras install docker
sudo service docker start
sudo usermod -a -G docker ec2-user

Start Apache Airflow: Run the provided bash script airflow_start_ec2.sh to start Airflow inside Docker containers.

./airflow_start_ec2.sh

(back to top)

Set Up Power BI

Once the SSH tunnel is active, you can connect Power BI to your MySQL database on AWS RDS.

  • Open Power BI Desktop: Go to the Home tab and select Get Data > MySQL database.
  • Configure the MySQL Connection:
    • Server: localhost:3308 (this points to port 3308 on your local machine, which forwards traffic to RDS via SSH tunnel).
    • Database: youtube_analytics.
    • Username: Your RDS MySQL username.
    • Password: Your RDS MySQL password.

(back to top)

Additional Tips

  • Airflow Web UI: After starting Airflow on your EC2 instance, you can access the web UI by visiting http://<your-ec2-public-ip-address>:8080 in a browser (ensure port 8080 is open in the security group).
  • Connecting to RDS: You can use the same SSH tunnel setup to connect to RDS with any MySQL client (e.g., MySQL Workbench) by using localhost:3308 as the connection address.

(back to top)

©️ License

This project is licensed under the MIT License.

(back to top)

👏 Credits

This project was made possible with the help of the following resources and tutorials:

(back to top)

About

ETL project for YouTube analytics: extracts channel, video, and comment data via the YouTube API, enriches comments with Hugging Face sentiment analysis, stores results in MySQL on AWS, orchestrates workflows with Airflow/Docker, and visualizes insights in Power BI.

Resources

Readme
MIT license
Activity

Stars

1 star

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages