YouTubeリサーチするGASプログラムはスプレッドシートに直接エンゲージメント率を出力できるの担当者必見
こんにちは！【YouTubeで結果を出す】には、その時々の傾向を把握することが大事ですよね。
傾向といっても、なんとなく追っているだけでは置いていかれるばかりです。
エンゲージメント率を算出し、可視化することはYouTubeリサーチの上で今や最低限です。
エンゲージメント率の算出自体は簡単です。
エンゲージメント率の計算方法
エンゲージメント率＝エンゲージメント数（高評価数+コメント数）÷再生回数×100
問題は「数」です。
リサーチする上ではサンプル数が多いほど有効性が上がります。どんな計算をしても、サンプル数が少なければ意味がありません。
一度に大量のデータをサンプリングして初めてリサーチと言えるのです。
一度にYouTube動画情報のデータを取得するにはどうすれば良いでしょうか？
YouTube API を使って動画情報を取得しよう
YouTube API自体の使い方は簡単で、必要な値をを設定したURLを叩くだけでjson形式の情報をスラスラと取得できます。
https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=UUyhBnB7_27lhyaYgjCf9Z4g&maxResults=50&order=date&pageToken=&key=ここにKey
後ほど、API Keyの取得フローも紹介します。
ここまでは簡単なのです。問題は、json形式の情報をいかに大量に、簡単に、リサーチ分析可能なスプレッドシートの状態にするかなのです。
一発でGoogleスプレッドシートに出力しどこからでもリサーチ可能
方法はいくつもあると思います。PythonでCSVとして出力するのもアリだと思います。しかし、CSVデータからリサーチするのも意外と面倒なんですよね。
まずそのCSVファイルをどこに保存するか…クラウドでお手軽に開きたいけど、そこも自動化するとなると、ログインしたりなにかと手順が多い。
そこで、身近でお手軽で動作も軽いGoogleスプレッドシート！スプレッドシート系はこれだけで良いと言っても過言ではありません。
そこで…
Googleスプレッドシートを開いて…
チェックしたいチャンネルのIDを入力して…
var channelID = "xxx";
実行ボタンを押せば…
▶︎RUN！
はい。Googleスプレッドシートに直接データが出力されます！！
パチパチ。
これはGAS(GoogleAppsScript)を使います。
後述するプログラムを貼り付けるだけなので、GAS初心者だけでなく、プログラム初心者にもオススメです。
ではフローを書いていきますね。
YouTube API Keyを取得
※別記事にまとめますのでお待ち下さい。
Googleスプレッドシートでスクリプトエディタを開く
適切なGoogleアカウントでスプレッドシートを開きます。
スクリプトエディタにプログラムを貼り付ける(初回のみ)
スクリプトエディタ内で「ファイル」「スクリプトの追加」をします。バージョンによって＋ボタンだったりします。
上部太文字のスクリプトの名前も適宜変えます。
それから、下記プログラムを貼り付けます。
function get_video_list() {
var today = new Date();
Logger.log(today);
var myToday = Utilities.formatDate(today,'Asia/Tokyo', "YYYY-MM-dd-HHmm");
Logger.log(myToday);
// パラメータを設定 ==================================================
// 作成したAPI key
var apikey = '取得したkey';
// 書き込み先のシート名
var sheetName = "samp"+myToday;
//var sheetName = "samp; //同じシートタブに未取得データ追記する場合にはシート名を指定する
// チェックしたいチャンネルのID
var channelID = "UCCy_q-N7F2FOIZ6ZggHIAKg"; //sony sample
// チェックする範囲の設定
// true : 最新50件のみチェックする。 (2回目以降の実行(定期実行時)はtrueを推奨)
// false: 対象チャンネルの全動画を取得する。
var getNewVideoOnly = true;
insertSheet(sheetName);
// スプレッドシートに記録済みの情報を取得 ==================================================
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
var recorded_video_IDs_array = [];
if (sheet.getLastRow() > 2) {
recorded_video_IDs_array = sheet.getRange(2,4,sheet.getLastRow()-1,1).getValues() || []; //getRange(行番号, 列番号, 行数, 列数)
}
else {
recorded_video_IDs_array = [];
}
var recorded_video_IDs_hash = {};
for (var i=0; i<recorded_video_IDs_array.length; i++) {
recorded_video_IDs_hash[recorded_video_IDs_array[i]] = 1;
}
// YouTube Data APIを叩いてチャンネル情報を取得 ==================================================
// 「アップロードされた動画リスト」のIDを取得
var baseUrl = "https://www.googleapis.com/youtube/v3/";
var dataURL = baseUrl + 'channels?part=contentDetails&order=date&id=' + channelID + '&key=' + apikey;
var response = UrlFetchApp.fetch(dataURL);
var responseJson = JSON.parse(response.getContentText());
var uploads_video_play_list_ID = responseJson.items[0].contentDetails.relatedPlaylists.uploads;
var page_token = '';
var record_data = [];
// 各動画の情報を取得
while (page_token !== undefined) {
dataURL = baseUrl + 'playlistItems?part=snippet&playlistId=' + uploads_video_play_list_ID +
'&maxResults=50&order=date&pageToken=' + page_token + '&key=' + apikey;
//'&maxResults=50&order=date&key=' + apikey;
response = UrlFetchApp.fetch(dataURL);
responseJson = JSON.parse(response.getContentText());
page_token = responseJson.nextPageToken;
//データをプッシュします
record_data.push(['タイトル', 'URL', '長さ','再生回数','高評価数','低評価数','コメント数','投稿日','サムネイル','エンゲージメント','エンゲージメント率','集計日']);
// スプレッドシートに未記録の情報だけを抜き出す ==================================================
for (var i=0; i<responseJson.items.length; i++) {
// 動画情報取得
var video_id = responseJson.items[i].snippet.resourceId.videoId;
var video_title = responseJson.items[i].snippet.title;
var thumbnail_url = responseJson.items[i].snippet.thumbnails.default.url;
var publishedAt = responseJson.items[i].snippet.publishedAt;
var publishAtJST = Utilities.formatDate(new Date(publishedAt), "JST", "yyyy/MM/dd HH:mm:ss");
//投稿日差分
var todayMoment = Moment.moment(); //現在日時
var pubMoment = Moment.moment(publishAtJST,'JST',"yyyy/MM/dd HH:mm:ss"); //投稿日
var difference = todayMoment.diff(publishedAt,"d"); //
// 記録済みならスキップ
if (recorded_video_IDs_hash[video_id]) {
continue;
}
// 他のスキップする条件があれば記述
//(サンゼさんのチャンネルの場合、タイトルが「数字.」から始まるものがチュートリアルなのでそれ以外はスキップ。）
/*
if (! video_title.match(/[0-9]+\./)) {
continue;
}
*/
// スプレッドシートに書き込む文字列を整える
var video_url = 'https://youtube.com/watch?v=' + video_id;
var cell_value_title = '=hyperlink("' + video_url + '","' + video_title + '")';
var cell_value_thumbnail = '=image("' + thumbnail_url + '")';
// 動画の時間を取得
var video_duration = '';
dataURL = baseUrl + 'videos?part=contentDetails&id=' + video_id + '&key=' + apikey;
response = UrlFetchApp.fetch(dataURL);
var responseJson_video = JSON.parse(response.getContentText());
video_duration = convertDurationTime(responseJson_video.items[0].contentDetails.duration);
//original 再生回数
var count_view = '';
var count_like = '';
var count_dislike = '';
var count_comment = '';
var engagement='';
var engagement_per='';
dataURL = baseUrl + 'videos?part=statistics&fields=items%2Fstatistics&id=' + video_id + '&key=' + apikey;
response = UrlFetchApp.fetch(dataURL);
var responseJson_count = JSON.parse(response.getContentText());
count_view = responseJson_count.items[0].statistics.viewCount;
count_like = responseJson_count.items[0].statistics.likeCount;
count_dislike = responseJson_count.items[0].statistics.dislikeCount;
count_comment = responseJson_count.items[0].statistics.commentCount;
engagement=Number(count_like)+Number(count_comment);
engagement_per=engagement/count_view;
//データをプッシュします
record_data.push([video_title, video_url, video_duration,count_view,count_like,count_dislike,count_comment,publishAtJST,cell_value_thumbnail,engagement,engagement_per,today]);
}
// 新しい動画のみ取得する場合、次のページを検索しない
if (getNewVideoOnly) {
page_token = undefined;
}
}
// スプレッドシートに書き込む ==================================================
if (0 < record_data.length) {
var last_row = sheet.getRange("B:B").getValues().filter(String).length + 1;
sheet.getRange(last_row, 1, record_data.length, 12).setValues(record_data);
newConditionalFormatRule("M2:M999",1,10,'General');
newConditionalFormatRule("K2:K999",0.1,1,'0.00%');
setNumberFormat("K2:K999");
whenDateAfter("H2:H999");
}
}
function convertDurationTime(duration) {
var reg = new RegExp('^PT([0-9]*H)?([0-9]*M)?([0-9]*S)?');
var regResult = duration.match(reg);
var hour = regResult[1];
var minutes = regResult[2];
var sec = regResult[3];
if(hour == undefined) {hour = '00';}
else {
hour = hour.split('H')[0];
if(hour.length == 1){hour = '0' + hour;}
}
if(minutes == undefined) {minutes = '00';}
else {
minutes = minutes.split('M')[0];
if(minutes.length == 1){minutes = '0' + minutes;}
}
if(sec == undefined) {sec = '00';}
else {
sec = sec.split('S')[0];
if(sec.length == 1){sec = '0' + sec;}
}
return hour + ":" + minutes + ":" + sec
}
function insertSheet(sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.insertSheet(sheetName, 1);
}
function newConditionalFormatRule(odrcol,min,max,format) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(odrcol);
var rule = SpreadsheetApp.newConditionalFormatRule()
//.whenFormulaSatisfied('=$B:$B=max(B:B)') // B列で一番大きい値
.whenNumberBetween(min,max)
.setFontColor("#FF0000")
.setBackground("#FFC0CB")
.setBold(true)
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function whenDateAfter(odrcol) {
// セルに条件付き書式規則を追加して、
var MyDate = new Date();
MyDate.setDate(MyDate.getDate() - 10);
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(odrcol);
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenDateAfter(new Date(MyDate))
.setFontColor("#FF0000")
.setBackground("#FFC0CB")
.setBold(true)
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function setNumberFormat(odrcol) {
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList([odrcol]);
// 指定された範囲に対して常に3つの小数点を表示します。
rangeList.setNumberFormat('0.00%');
}
忘れずに保存ボタンを押します。
パラメータの変更
以下のようにパラメータを設定します。
忘れずに保存ボタンを押します。
実行
「関数を選択」から「get_video_list」を選びます。
初回のみ「許可を確認」を押します。
初回のみ 作業するアカウントをえらびます。
初回のみ 小さな「詳細」を押します。
「安全ではないページに移動」と出ますのでそちらを押します。
データの確認
処理が終わったら、新しいシートタブが出来ていることを確認します。
以上です。一目瞭然でデータが捗ります。嬉しいですね！
問題■1200件以上のデータ取得について
この方法で、1200件が３、４分で取得可能です。
しかし、データ取得に6分以上の時間がかかりますと、エラーが出て取得が2020年12月現在できません。
こちらは次回解決していきたいと思います！
次回
・1200件以上のデータを取得する
・開始投稿日を設定する
それでは！データを取得して一歩先へ行きたい方是非フォローお願いします。
この記事が気に入ったらサポートをしてみませんか？