Automate YouTube Content Strategy with AI, Apify & Google Sheets

Description

📌 Overview

This advanced multi-phase n8n workflow automates the complete research, analysis, and ideation pipeline for a YouTube strategist. It scrapes competitor channels, analyzes top-performing titles and thumbnails, identifies niche trends, gathers audience sentiment from comments, and produces data-driven content ideas—automatically writing them into a structured Google Sheets dashboard.

This system is ideal for:

YouTube creators
Agencies
Content strategists
Automation engineers
Anyone who wants to generate YouTube content ideas backed by real data

🧠 High‑Level Architecture

The workflow is split into 5 phases, each building on the previous:

Phase 1 — Niche Outliers (Input: User Form)

Scrapes 3 high‑quality channels from your niche, extracts their outlier videos, and analyzes why they work.

Phase 2 — Broad Niche Insights (Weekly)

Scrapes the top trending content in your broad niche (e.g., "AI", "fitness", "personal finance") and logs weekly insights.

Phase 3 — Niche Insights (Daily)

Scrapes the top videos in your specific micro‑niche daily to keep track of content momentum.

Phase 4 — Comment Analysis

Analyzes real comments from your channel to understand what your audience likes, dislikes, and wants more of.

Phase 5 — Content Ideation

Generates 3 highly‑optimized title + thumbnail concepts using all prior insights.

Everything is automatically logged into a Google Sheets dashboard.

🧩 Phase-by-Phase Breakdown

⭐ Phase 1 — Niche Outliers (Form Trigger)

User enters 3 YouTube channel URLs in a form.
Workflow scrapes each channel using Apify YouTube Scraper.
Filters for top-performing videos.
Extracts: title, views, likes, thumbnail, URL.
AI analyzes:

Power words in titles (OpenRouter/GPT 4.1-mini)
Thumbnail attention hooks (OpenAI Vision)
All insights are appended into the “Niche Outliers” sheet.

Purpose: Understand what the best creators in your niche are doing.

🌐 Phase 2 — Broad Niche Insights (Weekly — Sundays @ 5 AM)

Workflow scrapes the top videos for a broad niche (e.g., “artificial intelligence”).
Analyzes:

Title structure
Power words
Thumbnail cues
Writes weekly insights to “Broad Niche Weekly” sheet.

Purpose: Stay informed about macro‑level trends.

🎯 Phase 3 — Niche Insights (Daily @ 6 AM)

Scrapes the top videos in your specific micro‑niche (e.g., “n8n automations”).
Runs title + thumbnail analysis.
Appends daily results to “Niche Daily”.
Results feed directly into Phase 5.

Purpose: Track daily momentum and trending formats.

💬 Phase 4 — Comment Analysis (Channel Feedback)

Scrapes your channel’s latest 5 videos.
Extracts up to 30 comments from each.
Aggregates comments.
AI identifies:

What viewers love
What viewers dislike
What viewers are asking for
Stores patterns in “Comment Analysis” sheet.

Purpose: Understand real audience demand.

💡 Phase 5 — Content Ideation Using AI

Using insights from all previous phases:

Top titles
Power words
Thumbnail patterns
Daily niche trends
Audience comment analysis
Channel positioning

The Creative Agent produces:

3 optimized video titles
3 matching thumbnail concepts

These are appended to the “Ideation” sheet.

A Slack notification is sent when ideation is ready.

Purpose: Fully automated content idea generation.

🗂️ Outputs in Google Sheets

The workflow populates these tabs:

📌 Niche Outliers (top competitor videos)

📌 Broad Niche Weekly (weekly trend analysis)

📌 Niche Daily (daily trend analysis)

📌 Comment Analysis (audience sentiment)

📌 Ideation (final titles + thumbnails)

🔧 What This Workflow Automates

✔ Competitor analysis
✔ Thumbnail + title breakdowns
✔ Daily niche tracking
✔ Weekly niche tracking
✔ Viewer sentiment analysis
✔ Fully AI‑generated content ideas
✔ Automatic data logging to Google Sheets
✔ Slack notifications

This is essentially a 24/7 AI YouTube strategist.

⚙️ Setup Requirements

Apify API Key** (used in 5 scraper nodes)
OpenRouter API Key** (for GPT 4.1-mini intelligence)
OpenAI API Key** (for thumbnail image analysis)
Google Sheets OAuth2 Credential**
Make a copy of the provided sheet template
Fill Set nodes:

Phase II: Broad niche (e.g., "AI")
Phase III: Micro niche (e.g., "n8n automations")
Phase IV: Your Channel URL
Phase V: Your Channel Description

🧪 Testing Guide

Test the Form Trigger with 3 competitor channel URLs.
Test both Schedule Triggers (weekly + daily) manually.
Verify Sheets are receiving rows.
Run the full pipeline end‑to‑end.
Confirm Slack notification.

Everything should chain together smoothly.

🎉 Final Result

By the end of this workflow, you have a:

🧠 Data‑driven YouTube strategy system

that:

studies your niche
finds outliers
understands your audience
detects trends
generates smart content ideas every day