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

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

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

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

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

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

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

Googleスプレッドシート CSVカスタム例 動画タイトル・動画URL

タイトル・動画URL

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

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

Googleスプレッドシート CSVカスタム例 サムネイル

サムネイル

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

=IMAGE(thumbnailsのセル)

Googleスプレッドシート CSVカスタム例 概要欄・文字数

概要欄・文字数

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

=LEN(descriptionのセル)

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

Googleスプレッドシート CSVカスタム例 ハッシュタグ・ハッシュタグ数

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

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

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

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関数を記述しています。

タグ・タグ数

Googleスプレッドシート CSVカスタム例 タグ・タグ数

[‘タグ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を足せば、タグの数が算出できます。

Googleスプレッドシート CSVカスタム例 視聴回数・コメント数・コメント率

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

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

=commentCountのセル/viewCountのセル

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

Googleスプレッドシート CSVカスタム例 高評価数・視聴回数に対する高評価率・高評価率

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

=likeCountのセル/viewCountのセル

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

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

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

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

Googleスプレッドシート CSVカスタム例 低評価数・視聴回数に対する低評価率・低評価率・視聴回数に対する評価率

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

=dislikeCountのセル/viewCountのセル

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

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

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

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

dislikeCount(低評価数)は調整中です

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

CSVのdislikeCount(低評価)と実際の低評価数

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

Googleスプレッドシート CSVカスタム例 再生時間

再生時間

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

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

カテゴリ

Googleスプレッドシート CSVカスタム例 カテゴリ

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

動画カテゴリ一覧シート
動画カテゴリ一覧シート

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

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

Googleスプレッドシート CSVカスタム例 投稿日時

投稿日時

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

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

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

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

Googleスプレッドシート CSVカスタム例 何日前の投稿か

何日前の投稿か

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

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

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

過去と比較する

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

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

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

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

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

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

タイトル

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

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

ChatGPT GAS依頼

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

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」を選択し、上記のコードをハッシュタグ抽出コードの下にペーストします。

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に追記しました。修正後がこちらです。

Googleスプレッドシート カスタマイズ例 変更比較 概要欄 調整後

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

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

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

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

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

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

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

各指標・カテゴリ比較

Googleスプレッドシート カスタマイズ例 変更比較 各指標

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

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

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

エクスポート日

Googleスプレッドシート カスタマイズ例 変更比較 エクスポート日

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

競合(似ている)動画と比較する

先程ご紹介した「過去の動画と比較する」を活用すれば、競合(似たている)動画との比較バージョン例も作成できます。

カスタマイズのポイント
  • 変更前後のタイトル・概要欄・ハッシュタグ・タグの差分と文字数比較
  • 視聴回数・コメント・評価などの差を比較
  • 投稿日時の違いを確認

ほぼ「過去の動画と比較する」のシートと同じであるため、詳しい解説は省略いたします。
一番最後だけ「投稿日時」という行に変えています。

Googleスプレッドシート 比較カスタム例 投稿日時

それぞれの動画投稿日時を呼び出して日本時間に変換し、アップ日にどれぐらいの差があったかチェックします。
ほぼアップ日や時間帯に変わりがなければ、どこに差があったのか分析するヒントになるでしょう。

サンプルのGoogleスプレッドシートを配布します!

今回、例として作成したGoogleスプレッドシートを配布いたします。

仕組みとしては、YouTubeチャンネル動画の詳細設定一括保存ツールで出力したCSVをGoogleスプレッドシートにインポートしています。これを今回は「オリジナルCSV」と表記しました。
別途カスタマイズ用シートを作成し、その「オリジナルCSV」シートから値を参照しています。

サンプルCSV

これにより再度ツールからCSVを出力した際、「オリジナルCSV」を差し替えるだけで、カスタマイズしたシートにも自動的に変更が反映されるようにしました。

過去や競合の比較も、分析用シートとは別に比較対象のデータを掲載したCSVを用意し、それぞれそこから参照しています。

ABテストCSVと比較対象CSV
「ABテストCSV」と「比較対象CSV」は、それぞれ1つだけ参照するため、入力している行は1行のみです。
Googleスプレッドシート 各シート構成
シートの構成

使い方

今回配布するGoogleスプレッドシートの使い方をご紹介いたします。

スプレッドシートをコピーする
Googleスプレッドシート ファイル→コピーを作成する

Googleスプレッドシートを開いたら、メニューから「ファイル」→「コピーを作成する」を選択します。

ドキュメントをコピー

ファイル名と保存場所を指定したら、「コピーを作成」をクリックします。

作成したコピーのスプレッドシートへ自動的に移動します。

インポートするシートを選択し、「ファイル」→「インポート」

インポートするシート(今回は「オリジナルCSV」)を選んだら、メニューから「ファイル」→「インポート」を選択します。

CSVをGoogleドライブにアップロード

インポート画面が表示されたら、「アップロード」タブを選択し、差し替えたいCSVファイルをアップします。

シートを置換する
ファイルをインポート

ファイルのインポート設定画面が表示されます。
「インポート場所」を「現在のシートを置換する」にしたら、「データをインポート」をクリックします。

差し替えCSV インポート後のスプレッドシート

「オリジナルCSV」がアップしたCSVに差し替えられました。
※画像はYouTube公式チャンネルのデータを使用しています。

「カスタム例」のシートは、4つのデータしか表示されていません。
全てのデータを表示したい場合は、オートフィルで自動入力しましょう。

Googleスプレッドシート カスタム例 オートフィル
最終行のA~W列を選択し、右下の青い丸にマウスカーソルをあわせます。ポインタが「+」マークになったら、下にドラッグしましょう。
Googleスプレッドシート カスタム例 オートフィル完了
オートフィルでこのように全てのデータが入力できました。

「ABテストCSV」や「比較対象CSV」は1行のみですので、CSVをまるごと差し替える必要はありません。
CSVを書き換えるだけで、「ABテスト カスタム例」と「競合比較カスタム例」の右側のC列(「変更後」や「比較対象2」)に反映されます。

ABテストCSVと比較対象CSV

左側のB列(「変更前」や「比較対象1」)は、デフォルトでは「オリジナルCSV」の3行目が参照されます。

違う行を参照したい場合は、B列の関数を編集しましょう。

さらに他のデータと結びつければ、分析の精度が向上するでしょう。
皆さんご自身で、このGoogleスプレッドシートをご自由にカスタマイズして活用してみてください。

動画のタイトル、タグ、概要欄などをCSV形式で保存します!
新しいタブで開きます

受付時間 平日10:00~17:00(土日祝除く)

東京オフィス
香川オフィス
関連の事業・サービスの詳細はこちら!
企業・団体のYouTubeの運営や管理をサポート