【無料配布あり】YouTube動画設定保存ツールで出力したCSVを活用してチャンネル分析しよう！

前回の記事でご紹介した通り、YouTubeチャンネル動画の詳細設定一括保存ツールから出力したCSVからは、さまざまなデータが取得できます。

YouTube動画のABテストや競合分析に！YouTubeチャンネル動画設定保存ツールの使い方

ただ、このままでは分析に使える状態とは言い難いでしょう。
そこで、分析に使えそうなカスタマイズサンプルGoogleスプレッドシートを作成しました。
今回は、そのサンプルGoogleスプレッドシートを使って、カスタマイズの例を詳しく解説いたします。

見やすい形に整えたカスタマイズ例

CSVをGoogleスプレッドシートをインポートし、別のシートで参照して見やすくカスタマイズしました。
CSVのままでは分かりにくい表示を調整したり、分析に役立つような項目を追加したりしています。

カスタマイズのポイント

サムネイルをURLではなく画像で表示
概要欄の文字数をカウント
概要欄に記入したハッシュタグを抽出
ハッシュタグの数をカウント
タグを見やすく改行
タグの数をカウント
視聴回数からコメントの割合を算出
視聴回数から高評価や低評価の割合をそれぞれ算出
高評価と低評価をあわせた数から、高評価率や低評価率の割合をそれぞれ算出
視聴回数から高評価と低評価を合わせた数値の割合を算出
再生時間を「H:MM:SS」の形式に変換
カテゴリ名の表示
投稿日時を日本時間に変換
その動画が今日から投稿して何日目か算出

では、それぞれの項目についてポイントを解説していきます。

タイトル・動画URL

title（動画タイトル）を1列目に、Video URL（動画URL）を2列目にして見やすくします。

1行目と1列目は固定させ、右にスクロールしても動画タイトルが見えるようにします。

サムネイル

Googleスプレッドシートで使えるIMAGE関数で、サムネイル画像を表示させて見やすくします。

=IMAGE(thumbnailsのセル)

概要欄・文字数

description（概要欄）の長さが視聴回数に影響したかが確認できます。

=LEN(descriptionのセル)

ハッシュタグ・ハッシュタグ数

description（概要欄）に記入したハッシュタグを抽出し、その数もカウントします。
概要欄の下の方にハッシュタグを書いてしまった場合、これですぐに確認できるため便利です。

ただし、ハッシュタグを抽出できるような関数はGoogleスプレッドシートには存在しません。Google App Scriptで関数を作成する必要があります。

そこでChatGPTに依頼し、スクリプトを書いていただきました。

できたコードがこちらです。

function EXTRACT_HASHTAGS(text) {
  var regex = /#[\w一-龠ぁ-んァ-ヶー]+/g;
  var matches = text.match(regex);
  if (matches) {
    return matches.join(', ');
  } else {
    return "ハッシュタグなし";
  }
}

「拡張機能」→「Apps Script」を選択し、上記のコードをペーストします。

スプレッドシートにハッシュタグを抽出する関数を記述します。

=EXTRACT_HASHTAGS(descriptionのセル)

抽出したハッシュタグのセルを参照して、「#」の文字数をカウントする関数を記述すれば、ハッシュタグの数が表示できます。

=IF( ハッシュタグのセル=”ハッシュタグなし”,0,
(
LEN( ハッシュタグのセル ) – LEN( SUBSTITUTE( ハッシュタグのセル, “#”, “” ) )
)
)

※関数が長いため、わかりやすいように区切りで改行しています。

LEN(ハッシュタグのセル)はテキストの長さを、SUBSTITUTE(ハッシュタグのセル, "#", "")は「#」をすべて取り除いた後の長さを返しています。この差でハッシュタグの数を算出します。
ハッシュタグがなければ「ハッシュタグなし」と出力するため、その場合は「0」となるようIF関数を記述しています。

タグ・タグ数

[‘タグ1’, ‘タグ2’, ‘タグ3’…..] の形式では見づらく、タグもどれぐらい設定したか一目で分かりません。
そこでタグごとに改行して、視認性を高めます。

SUBSTITUTE関数を利用し、[]を削除して', 'を改行（CHAR(10)）に置き換えます。

=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE( tagsのセル, “[‘”, “” )
, “‘]”, “”
)
, “‘, ‘”, CHAR(10)
)

※関数が長いため、わかりやすいように区切りで改行しています。

タグ数は「, 」の文字数を数え、そこに1を足します。

=IF( tagsのセル=0,0,
(
LEN( tagsのセル )
– LEN( SUBSTITUTE ( tagsのセル, “, “, “” ) )
)
/ 2+1)

※関数が長いため、わかりやすいように区切りで改行しています。

タグの文字数から、コンマとスペースを取り除いた文字数を引き、その差をコンマとスペースの組の長さ（この場合は2文字）で割っています。そこに1を足せば、タグの数が算出できます。

視聴回数・コメント数・コメント率

コメント率は、コメント数を視聴回数で割った数値です。動画に対する反応度合いを分析できます。

=commentCountのセル/viewCountのセル

高評価数・視聴回数に対する高評価率・高評価率

「視聴回数に対する高評価率」は、高評価数を視聴回数で割った数値です。動画を見た人がどれぐらい高評価をしてくれたのか割合を算出します。

=likeCountのセル/viewCountのセル

「高評価率」は高評価数を「高評価と低評価をすべてあわせた数」で割った数値です。すべての評価ボタンを押してくれた回数のうち、高評価はどれぐらいの割合か算出します。

=IFERROR(likeCountのセル/(likeCountのセル+dislikeCountのセル),0)

どちらか片方の評価が0ならエラーとなってしまうため、IFERROR関数を使用します。

低評価数・視聴回数に対する低評価率・低評価率・視聴回数に対する評価率

「視聴回数に対する低評価率」は先程の「視聴回数に対する高評価率」と同じく、低評価数を視聴回数で割った数値です。動画を見た人がどれぐらい低評価を押したのか割合を算出します。

=dislikeCountのセル/viewCountのセル

「低評価率」も先程の「高評価率」と同じく、低評価を「高評価と低評価をすべてあわせた数」で割った数値です。すべての評価ボタンを押してくれた回数のうち、低評価はどれぐらいの割合か算出します。

=IFERROR(dislikeCountのセル/(likeCountのセル+dislikeCountのセル),0)

「視聴回数に対する評価率」は、「高評価と低評価をすべてあわせた数」を視聴回数で割った数値です。これにより、どれぐらいの視聴者が評価ボタンを押してくれたのか算出します。

=(likeCountのセル+dislikeCountのセル)/viewCountのセル

dislikeCount（低評価数）は調整中です

今回はdislikeCount（低評価数）のカスタマイズ例も解説しておりますが、dislikeCountはツールの仕様により、うまく取得されません。実際に低評価があっても「0」と表記されてしまうため、現在調整中です。

「【初心者向け】GoPro HERO 11 Blackのカメラ設定について解説」でも、実際には5つ低評価が入っていますが、CSVのdislikeCountは「0」と表示されています。

再生時間

duration（再生時間）の秒数表示をTIMEVALUE関数で時間表記に変換します。

=TIMEVALUE(“0:0:”&durationのセル)

カテゴリ

categoryId（カテゴリ）は番号で表示されるため、カテゴリ名が分かりません。
このカスタマイズでは、別途カテゴリ一覧のシートを作成し、VLOOKUP関数でIDからカテゴリ名を参照します。

動画カテゴリ一覧シート

=VLOOKUP(categoryIdのセル,’カテゴリ一覧シートA:B’,2)

最新のカテゴリIDと名前の確認方法はこちらで紹介しています。

投稿日時

publishedAt（投稿日時）は協定世界時（UTC）であるため、日本時間に換算しました。

=DATEVALUE( MID( publishedAtのセル, 1, 10 ) )
+TIMEVALUE( MID( publishedAtのセル, 12, 8 ) )
+TIME( 9, 0, 0 )

※関数が長いため、わかりやすいように区切りで改行しています。

MID関数で日付と時刻を抽出し、DATEVALUE関数とTIMEVALUE関数で日付と時刻に変換します。
最後に、日本時間に合わせるためにTIME(9,0,0)＝9時間を加算しています。

何日前の投稿か

今日（TODAY関数）から何日前に公開した動画なのか確認できます。

=DATEDIF( publishedAtのセル, TODAY(), “Y”)
& “年”
& DATEDIF( publishedAtのセル, TODAY(), “YM”)
& “ヶ月”
& DATEDIF( publishedAtのセル, TODAY(), “MD”)
& “日前”

※関数が長いため、わかりやすいように区切りで改行しています。

過去と比較する

タイトルや概要欄などを変更した後の影響を分析したいときがあるでしょう。
そこで、テキスト情報を変更する前と後の比較ができるシートを作成してみました。

カスタマイズのポイント

変更前後のタイトル・概要欄・ハッシュタグ・タグの差分と文字数比較
視聴回数・コメント・評価などの比較
変化率の確認
CSVをエクスポートした日からどれぐらい経っているか

テキスト情報更新後（もしくは前）のCSVを、別シートでインポートします。

それぞれの値を参照してカスタマイズしました。

サムネイルの比較をしたいときは

サムネイルの差し替えは、URLを参照している都合上、CSVだけでは比較できません。
サムネイルの比較をしたい場合は、変更する前にサムネイル画像をダウンロードし、「変更前」の行に画像を貼り付ける必要があります。

タイトル

変更前と後でタイトルに変更があった場合、「差」の列で文字の差異を表示します。
文字の差異があるかどうかの判別はEXACT関数でできますが、具体的にどの部分か表示する関数は存在しません。
Google App Scriptで関数を作成する必要があります。

これもChatGPTにスクリプトを書いていただきました。

できたコードがこちらです。

function FIND_DIFFERENCE(str1, str2) {
  var length = Math.max(str1.length, str2.length);
  var difference = [];

  for (var i = 0; i < length; i++) {
    if (str1.charAt(i) !== str2.charAt(i)) {
      difference.push(str2.charAt(i));
    }
  }

  return difference.join('');
}

「拡張機能」→「Apps Script」を選択し、上記のコードをハッシュタグ抽出コードの下にペーストします。

今回作成した、スプレッドシートに文字の差異を表示する関数を「差」の列に記述します。

=IF(
EXACT( 変更前のタイトルセル,変更後のタイトルセル ) , “変更なし”,
FIND_DIFFERENCE( 変更前のタイトルセル,変更後のタイトルセル )
)

※関数が長いため、わかりやすいように区切りで改行しています。

EXACT関数で完全一致（true）したら「変更なし」、違う（false）なら文字の差異を表示するようにしました。もし変更の有無だけ分かればいい場合は、FIND_DIFFERENCE関数の箇所を削除し、”変更あり”に書き換えましょう。

概要欄・ハッシュタグ・タグ

先程作成した関数で、概要欄やハッシュタグ、タグも同様に文字の差異を表示できます。

文字数の差異は、LEN関数でそれぞれ長さを算出し、「差」で「変更前」から「変更後」を引いた数を表示しています。

=LEN(変更前の概要欄セル)-LEN(変更後の概要欄セル)

「変化率」の列は、「変更前」と「変更後」でどれぐらいの変化があるか、%で表したものです。

=IFERROR((変更後の数字表記セル/変更前の数字表記セル)-1,0)

「文字数」や「視聴回数」といった数字の表記がある行には、基本的にこの変化率を表示しています。

概要欄について、「変更後」では「初心者向け」を「初心者に向けて」としたり、アプリのURLを掲載したりと、上部だけ少し文を変えています。しかし、先程作成したFIND_DIFFERENCE関数では、変更した部分より下の文章も表示されてしまいます。そのため、別途ChatGPTに改善したスクリプトを書いていただきました。

function FIND_DIFFERENCE_DOCUMENT(str1, str2) {
  var lines1 = str1.split("\n");
  var lines2 = str2.split("\n");
  var maxLength = Math.max(lines1.length, lines2.length);
  var diff = [];

  var startDiff = -1;
  var endDiff = maxLength;

  for (var i = 0; i < maxLength; i++) {
    if ((lines1[i] !== lines2[i]) && startDiff === -1) {
      startDiff = i;
    }
    if ((lines1[i] === lines2[i]) && startDiff !== -1) {
      endDiff = i;
      break;
    }
  }

  for (var j = startDiff; j < endDiff; j++) {
    if (lines2[j] !== undefined) {
      diff.push(lines2[j]);
    }
  }

  return diff.join("\n");
}

上記を新しくFIND_DIFFERENCE_DOCUMENT関数としてGoogle App Scriptに追記しました。修正後がこちらです。

=IF(
EXACT( 変更前の概要欄セル, 変更後の概要欄セル ),
“変更なし”,
FIND_DIFFERENCE_DOCUMENT( 変更前の概要欄セル, 変更後の概要欄セル )
)

※関数が長いため、わかりやすいように区切りで改行しています。

これでも、「変更後」で追記した「アプリはこちら」の表記が「差」の列にありません。
文章の差異を完璧に表示させるのには、Google App Scriptでは限界があるようです。
もし完全な文章の差異を確認する場合は、テキスト比較ツールを使ったほうが良いでしょう。

その下の「ハッシュタグ」や「タグ」の行も同じように記述しています。

タグ・ハッシュタグの「差」セル（D6・D8）の記述

=IF(
EXACT( 変更前のハッシュタグ・タグのセル, 変更後のハッシュタグ・タグのセル ),
“変更なし”,
FIND_DIFFERENCE( 変更前のハッシュタグ・タグのセル, 変更後のハッシュタグ・タグのセル )
)

※関数が長いため、わかりやすいように区切りで改行しています。

各指標・カテゴリ比較

「変更前」と「変更後」の各指標の変化を表示しています。
それぞれのCSVから値を参照し、「コメント率」「視聴回数に対する高評価率」などの数値は、最初にご紹介したカスタマイズ例と同じ関数を記述しています。

一目でわかるよう、差や変化率が0より大きければ赤を、小さければ青を、0なら灰色になるよう、条件付き書式を設定しています。

カテゴリについては、変更がなければ「差」の列に「変更なし」、あれば「変更あり」と表示するようにしています。

エクスポート日

変更後、変更前のデータからどれぐらいの日数が経ったか確認するための項目です。
投稿日時ではなく、このデータを書き出した日を記述しています。そのため、他のCSVから参照する形式ではなく、手動で入力しています。
この日数表記で、変更後の効果が何日間で現れたのかがわかります。なるべく記載しておくのをおすすめいたします。