ブログ運営をしていると当然アクセスが気になります。どんなキーワードで検索されているのか、そのときの順位はどうなっているのかなど。このようなブロガーなら気になる情報を取得する方法があります。それがサーチコンソール（別名：サチコ）です。
そこで今回は3月のサーチコンソールのレポートを解析することにします。検索キーワード、クリック数、表示回数、掲載順位がわかります。表示回数が多いキーワードであればコンテンツを充実させることで掲載順位をアップさせることができればアクセスアップに繋がります。どの部分を頑張ればいいかわかるのでサチコには登録しておきましょう。
レポートは過去1ヶ月とか1週間という形でまとめてダウンロードできますが、今回は1日1日すべておこないます。というのはレポートに表示されるのは1000件までだからです。毎日であれば1000件という上限に到達することはないので（本当はしてほしいのだが・・・）検索されたキーワードの表示回数とそのときの順位が取得できそうです。
こんな形で結果を知ることができます。やっぱり検索数が多く、上位表示ができそうなキーワードに力を入れたいですよね。そこで毎日のレポートを一行ずつ取得して以下のようなクラスに格納することを考えます。
|
1 2 3 4 5 6 7 8 9 |
class Data { // 以下は擬似コードです 日付 検索キーワード クリック数 表示回数 掲載順位 } |
毎日のサーチコンソールのレポートはすでにダウンロードしているので、あとは複数のファイルを一気にWindowFormの上にドラッグ＆ドロップすればDataオブジェクトのリストを取得することはできそうです。
まずファイルのドラッグ＆ドロップを受け入れる設定をおこないます。Form1にドロップするのでForm1.AllowDropをtrueにすればいいですね。
ドラッグオーバーされたら仮想メソッドOnDragOverメソッドがよばれ、ドロップされたらOnDragDropメソッドが呼ばれます。それぞれをオーバーライドして処理ができるようにします。
OnDragDropメソッドでドロップされたファイルのパスの配列が取得できたら自作メソッドGetDataFromExcelFileでデータを取得します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
public partial class Form1 : Form { public Form1() { InitializeComponent(); AllowDrop = true; } protected override void OnDragOver(DragEventArgs drgevent) { if (drgevent.Data.GetDataPresent(DataFormats.FileDrop)) drgevent.Effect = DragDropEffects.Copy; else drgevent.Effect = DragDropEffects.None; base.OnDragOver(drgevent); } protected override void OnDragDrop(DragEventArgs drgevent) { if (drgevent.Data.GetDataPresent(DataFormats.FileDrop)) { string[] files = (string[]) drgevent.Data.GetData(DataFormats.FileDrop); // これらのファイルを解析すればよい } base.OnDragDrop(drgevent); } } |
ではどのような処理をすればいいでしょうか？
そのまえに取得したデータを格納するクラスを作成します。
KeyWordプロパティは検索されたキーワード、DateTimeプロパティは検索された日、DisplayedCountプロパティは検索結果が表示された回数、ClickedCountプロパティは検索結果に表示されたリンクがクリックされた回数、Rankingプロパティはそのときの順位です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
public class Data { public Data(int year, int month, int day, string keyWord, int displayedCount, double ranking, int clickedCount) { DateTime = new DateTime(year, month, day); KeyWord = keyWord; DisplayedCount = displayedCount; Ranking = ranking; ClickedCount = clickedCount; } public DateTime DateTime { private set; get; } public string KeyWord { private set; get; } public int DisplayedCount { private set; get; } public double Ranking { private set; get; } public int ClickedCount { private set; get; } } |
数日分の上記のデータを検索ワードを軸にしてまとめます。GetDisplayedCounts()メソッドはその期間の表示回数を返します。GetClickedCounts()メソッドはその期間のクリックされた総数を返します。GetRankingAverage()メソッドは順位の平均を返します。
この結果を分析して上位表示されているはずなのにGetClickedCounts()が返す値が少なすぎるのであればタイトルを見直したほうがいいかもしれません。またGetDisplayedCounts()メソッドが返す値が大きく、掲載順位もまあまあであればコンテンツを充実させることで順位をあげてアクセスアップを実現することができるかもしれません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
public class KeyWordInfo { public KeyWordInfo(string keyWord, List<Data> datas) { KeyWord = keyWord; Datas = datas; } public string KeyWord { get; private set; } public List<Data> Datas { get; private set; } public int GetDisplayedCounts() { return Datas.Sum(x => x.DisplayedCount); } public int GetClickedCounts() { return Datas.Sum(x => x.ClickedCount); } public double GetRankingAverage() { return Datas.Average(x => x.Ranking); } } |
ではドロップされたファイルを取得したあとのことを考えましょう。
まずExcelファイルではない見当外れなファイルがドロップされることがあります。そのようなファイルは除外しなければなりません。Excelファイルであってもサーチコンソールのレポートとは無関係のファイルだった場合も除外しなければなりません。
GetDataFromExcelFilesメソッドはExcelファイルから必要なデータを抜き出して生成したDataオブジェクトをリストにするメソッドですが、ExcelファイルでないファイルやExcelファイルであっても必要なデータを抜き出せなかった場合は例外処理をしています。
それからExcelファイルを操作するのでNugetでClosedXMLをインストールしておいてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
using ClosedXML.Excel; public partial class Form1 : Form { protected override void OnDragDrop(DragEventArgs drgevent) { if (drgevent.Data.GetDataPresent(DataFormats.FileDrop)) { string[] files = (string[]) drgevent.Data.GetData(DataFormats.FileDrop); // Excelファイルから必要なデータを抜き出して生成したDataオブジェクトをリストに List<Data> datas = GetDataFromExcelFile(files); // そのあとの処理は後述 } base.OnDragDrop(drgevent); } List<Data> GetDataFromExcelFiles(string[] filePaths) { List<Data> datas = new List<Data>(); foreach (string file in filePaths) { try { datas.AddRange(GetDataFromExcelFile(file)); } catch { } } return datas; } } |
これがExcelファイルから必要なデータを抜き出すメソッドです。「フィルタ」というシートのB3に日付が書かれています。また「クエリ」というシートの一番上には「検索キーワード」「クリック数」「表示回数」「CTR」「掲載順位」が書かれています。そこで2行目以下のセルを調べれば必要なデータが取得できます。A列を調べてなにも書かれていないのであればレポートはそこで終わりと判断しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
public partial class Form1 : Form { List<Data> GetDataFromExcelFile(string filePath) { List<Data> vs = new List<Data>(); using (var workbook = new XLWorkbook(filePath)) { var worksheet0 = workbook.Worksheet("フィルタ"); string date = worksheet0.Cell("B3").GetValue<string>(); // 日付が「2021/03/10-2021/03/10」という形で入っている。 // ハイフンより前の部分を/で分解すれば年・月・日が取得できる date = date.Substring(0, 10); int year = int.Parse(date.Substring(0, 4)); int month = int.Parse(date.Substring(5, 2)); int day = int.Parse(date.Substring(8, 2)); var worksheet = workbook.Worksheet("クエリ"); int i = 2; while (true) { // 検索キーワード、クリック数、表示回数、掲載順位を取得 string keyWord = worksheet.Cell(i, "A").GetValue<string>(); if (keyWord == null || keyWord == "") break; int clickCount = worksheet.Cell(i, "B").GetValue<int>(); int showCount = worksheet.Cell(i, "C").GetValue<int>(); double ranking = worksheet.Cell(i, "E").GetValue<double>(); vs.Add(new Data(year, month, day, keyWord, showCount, ranking, clickCount)); i++; } } return vs; } } |
Dataオブジェクトのリストを集めたら、日付順に並べ替えます。そしてアクセス解析の関心事としては
検索結果に多く表示されているキーワードはなにか？
検索され、実際にアクセスを呼び込めているキーワードはなにか？
現在、上位表示されているキーワードはなにか？
なので、これらを求めるメソッドを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
public partial class Form1 : Form { List<KeyWordInfo> GetDisplayedKeyWordInfos(List<Data> datas) { List<KeyWordInfo> keyWordInfos = new List<KeyWordInfo>(); var query = datas.GroupBy(x => x.KeyWord); foreach (var group in query) { string keyWord = group.Key; var group2 = group.OrderBy(x => x.DateTime).ToList(); keyWordInfos.Add(new KeyWordInfo(keyWord, group2)); } return keyWordInfos.OrderByDescending(x => x.GetDisplayedCounts()).ToList(); } List<KeyWordInfo> GetClickedKeyWordInfos(List<Data> datas) { List<KeyWordInfo> keyWordInfos = new List<KeyWordInfo>(); var query = datas.GroupBy(x => x.KeyWord); foreach (var group in query) { string keyWord = group.Key; var group2 = group.OrderBy(x => x.DateTime).ToList(); keyWordInfos.Add(new KeyWordInfo(keyWord, group2)); } return keyWordInfos.OrderByDescending(x => x.GetClickedCounts()).ToList(); } List<KeyWordInfo> GetRankingKeyWordInfos(List<Data> datas) { List<KeyWordInfo> keyWordInfos = new List<KeyWordInfo>(); var query = datas.GroupBy(x => x.KeyWord); foreach (var group in query) { string keyWord = group.Key; var group2 = group.OrderBy(x => x.DateTime).ToList(); keyWordInfos.Add(new KeyWordInfo(keyWord, group2)); } return keyWordInfos.OrderBy(x => x.GetRankingAverage()).ToList(); } } |
GetDisplayedKeyWordInfosメソッドやGetClickedKeyWordInfosメソッド、GetRankingKeyWordInfosメソッドで必要なデータを集めることができたので、これを元にExcelファイルを作成します。
キーワードごとに新しいシートを作成していますが、あまり多くすると管理しにくくなります。過ぎたるは及ばざるが如しです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
public partial class Form1 : Form { void SaveExcel(string excelPath, List<KeyWordInfo> keyWordInfos) { using (var workbook = new XLWorkbook()) { int no = 0; foreach (KeyWordInfo info in keyWordInfos) { var worksheet = workbook.Worksheets.Add("シート" + no++); worksheet.Cell(1, "A").Value = info.KeyWord; worksheet.Cell(2, "A").Value = "月日"; worksheet.Cell(2, "B").Value = "表示回数"; worksheet.Cell(2, "C").Value = "クリック数"; worksheet.Cell(2, "D").Value = "掲載順位"; worksheet.Columns("A").Width = 12; worksheet.Columns("B").Width = 10; worksheet.Columns("C").Width = 10; worksheet.Columns("D").Width = 10; int i = 3; foreach (Data data in info.Datas) { worksheet.Cell(i, "A").Value = data.DateTime.ToShortDateString(); worksheet.Cell(i, "B").Value = data.DisplayedCount.ToString(); worksheet.Cell(i, "C").Value = data.ClickedCount.ToString(); worksheet.Cell(i, "D").Value = data.Ranking.ToString(); i++; } } workbook.SaveAs(excelPath); } } } |
最後にこれらのメソッドをつかってExcelファイルがドラッグ＆ドロップされたときの処理を実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
public partial class Form1 : Form { protected override void OnDragDrop(DragEventArgs drgevent) { if (drgevent.Data.GetDataPresent(DataFormats.FileDrop)) { string[] files = (string[]) drgevent.Data.GetData(DataFormats.FileDrop); List<Data> datas = GetDataFromExcelFiles(files); // 表示回数上位10件を出力する List<KeyWordInfo> displayedKeyWordInfos = GetDisplayedKeyWordInfos(datas); SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel(*.xlsx)|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { SaveExcel(dialog.FileName, displayedKeyWordInfos.Take(10).ToList()); MessageBox.Show("終了しました"); } dialog.Dispose(); } base.OnDragDrop(drgevent); } } |
完成♪
