Build a program where it categorizes in bulk YouTube channels with AppScript

In this article, we will build a process where via a Google Sheet we give hundreds or thousands of youtube channels and then categorize them.

Identify the problem you want to solve:

One of the big pains, when you run Youtube Ads, is that you waste a lot of budget on irrelevant channels, mostly music channels and kids’ channels. Why this is a problem? Because in music people listen to the music and do not “see” your ads, while on kids’ channels, the actual viewers are kids that watch kids’ programs on their parents' devices.

So a normal process to solve this is to go to your Google Ads account, find the Youtube Ads campaign you want, and then manually exclude the channels ONE BY ONE.

This is an excruciating process especially when the channel count is in the thousands.

Visualize the solution in your head:

So what if we could do this with a program that would categorize the youtube channels based on their content, and then use those categories to build massive blacklists (and whitelists) and then upload them negative placement lists in Google Ads?

That would solve the problem in minutes instead of months!!

Write the steps down:

1. Get a list of all the youtube channels my ads were shown from Google Ads
2. Import them in a Google Sheet
3. Create a Google Cloud project where I enable the YouTube Data API and create the necessary API credentials in order to use the API
4. Go back to the Google Sheet Script and write a script where it gets the youtube channel ID as input and then it returns the categories that fall to

Now, let’s get each step out of the way

Get a list of all the youtube channels my ads were shown from Google Ads

Go to your Google Ads account, find your YouTube Ads campaign, and then on the left menu go to Content > Where Ads Showed

Then this table will appear with all the placements (youtube channels) where your ads appeared. Go to: Downloads > Google Sheet

Import them in a Google Sheet

Now you have a sheet like the one below. In every row, there is a Youtube Channel.

Now we need to make a little bit of housekeeping:

From the full URL, we only need the youtube channel id: (in the first row we need the UCmHgxU394HiIAsN1fMegqzw)

http://youtube.com/channel/UCmHgxU394HiIAsN1fMegqzw

In order to do this we will use the “Filter()” formula in google sheets, and specifically, the formula will be

=SPLIT(YOUTUBE_CHANNEL,”/”)

With this, you will separate the “http://youtube.com/channel” from the channel id.

Now we are ready to move forward with the YouTube API

Create a Google Cloud project where I enable the YouTube Data API

Now that we have separated the channel id from the URL we will call Youtube Data API to tell us which category they belong to.

In order to do this we need a Google Cloud project to enable the API.

if you are unfamiliar with google cloud or how to create an account you can find more information on my blog post here in the first part where I create a Google Cloud account from scratch.

https://medium.com/@george.halikiopoulos/create-your-first-bigquery-project-with-google-search-console-data-511096744ed7

Now back to Google Cloud, we create a new project.

After you created your project go to the search bar and search for APIs and Services.

and then press “Enable APIs and Services”

This will send you to the APIs Library where you will have to look for the Youtube API Data v3. That’s the API that will give you information on the channel categories. Click it and then press enable.

Now you need το create the credentials for this API in order for your Google Sheet to connect with the Youtube Data API.

Just find the Create Credentials button and click API Key .

This will generate a key. Store it somewhere safe, you will need it later

Go back to the Google Sheet Script and write a script with the help of Jasper.ai where it gets the youtube channel ID as input and then it returns the categories that fall to

Now we go to the fun part where Jasper.ai plays a crucial role. Now we need to build the script. This script will take as input the channel id that we created, call the Youtube Data API and ask it in what category this channel is, then return the category as output.

Go to extensions and then Apps Script.

This will open the Apps Script environment.

Go to files and open a new Script file.

Copy and paste the following script. Put your API key we created earlier in the api_key variable, press save project.

function youtubeCall(channel_id) {

// replace this with the ID of the channel you want to call
// replace this with your API key from Google Developer Console
var api_key= 'PUT_YOUR_KEY_HERE';


// Construct Youtube Data api v3 search URL using channel ID and API Key given by user
var url = ('https://www.googleapis.com/youtube/v3/channels?part=topicDetails&id=' + channel_id + "&key=" + api_key);


// Make a HTTP request based on constructed URL

var resp = UrlFetchApp.fetch(url);

var data = JSON.parse(resp.getContentText());
// get the category id from first item in response array since it is /channels endpoint which only returns one item

var categoryId = data.items[0].topicDetails.topicCategories
Logger.log(categoryId);


return categoryId[0] + '<>' + categoryId[1] + '<>' +categoryId[2] + '<>' +categoryId[3];
}

Now we have a custom-built function called youtubeCall which takes channel_id as an argument and returns the first four categories of this channel.

Now we need to put it to the test.

Go to our Google sheet and beside the column with the channel ids create a new column and write the new method “=youtubecall(channel_id)”

This will return the following line:

https://en.wikipedia.org/wiki/Music<>https://en.wikipedia.org/wiki/Hip_hop_music<>https://en.wikipedia.org/wiki/Music_of_Asia<>undefined

As you can see this channel is associated with Music.

Now you have in a spreadsheet all your channels, and what categories they are associated with. You can use filters to filter for example all the Music youtube channels and create a huge Placement List that will help you optimize your Youtube Ads Budget.