GoogleAnalyticsの年間のデータをエクセルへ出力するコマンドを作成しました。
出力されるエクセルは下記のイメージ画像となります。
本記事はその２「エクセルデータの作成編」です。その１「GoogleAnalyticsからデータの取得編」のコマンドを実行していることが前提となります。
なお、データを取得するサイトのidとトークンファイルは取得・保存している前提でコマンドを紹介します。
サイトのidとトークンファイルの取得と保存方法は「RでGoogle Analyticsの目次」から「RGoogleAnalyticsパッケージ基本的な利用方法」を確認してください。
また、解析環境が整っていない場合は「解析の準備」の項目を確認してください。初心者でも実行できるようにまとめています。
解析コマンドなどのまとめはこちらから：RでGoogle Analyticsの目次
その１「GoogleAnalyticsからデータの取得編」はこちらから。 GoogleAnalyticsの年間のデータをエクセルへ出力：その１「GoogleAnalyticsからデータの取得編」
必要なパッケージを読み込む。
#パッケージの読み込み
library("XLConnect")
library("reshape")
library("tcltk")
library("ggplot2")
#XLConnectとggplot2パッケージがインストールされていない場合は下記コマンドを実行してください。
install.pakages(c("XLConnect", "ggplot2"))
コマンドの紹介
・エクセルファイルの保存先とファイルの設定コマンド
コマンドを実行することで保存先を指定されるダイアログが表示されます。 ダイアログは他のウィンドウに隠れる場合がありますのでご注意を。
#保存フォルダの指定。ダイアログが隠れる場合があります。
setwd(paste(as.character(tkchooseDirectory(title = "保存ディレクトリを選択"), sep = "", collapse ="")))
#ワークブックの作成
wb <- loadWorkbook("年間アクセス傾向まとめ.xlsx", create = TRUE)
#シートの作成
createSheet(wb, name = "年間アクセス傾向まとめ")
#セルスタイルの調整
setMissingValue(wb, value = "-")
#列の固定
createFreezePane(wb, "年間アクセス傾向まとめ", 1, rowSplit = 27)
#タイトルの書き込み
writeWorksheet(wb, "年間アクセス傾向まとめ", sheet = "年間アクセス傾向まとめ",
startRow = 1, startCol = 1, header = FALSE)
・グラフサイズを指定するコマンド
紹介するコマンドはOSがwindowsの場合は指定サイズ、MACの場合は80%縮小し出力します。詳細はコマンド内のコメントを確認してください。
#グラフサイズの調整
#OSがwindowsの場合はそのまま、MACの場合は80%縮小、グラフのX,Y軸のラベルサイズを指定。
if(.Platform[1] == "windows"){
sizeRatio <- 1
XTextSize <- 10
YTextSize <- 8
Fontfamily <- ""
}else{
sizeRatio <- 0.8
XTextSize <- 8
YTextSize <- 6
Fontfamily <- "HiraKakuProN-W3"
}
#"HeatMapDayofMonth.jpg"のサイズ
HMAMwidth <- 450 * sizeRatio
HMAMheight <- 390 * sizeRatio
#"MonthSumHistPlot.jpg", "MonthAveHistPlot.jpg",
#"DaySumHistPlot.jpg", "DayAveHistPlot.jpg"のサイズ
MonDaywidth <- 390 * sizeRatio
MonDayheight <- 190 * sizeRatio
#"HeatMapPlotofMDay.jpg", "DSHistPlot.png", "DMAHistPlot.png"のサイズ
MDaywidth <- 355 * sizeRatio
MDayheight <- 340 * sizeRatio
#"HeatMapPlotofHour.jpg"のサイズ
HMPHwidth <- 360 * sizeRatio
HMPHheight <- 360 * sizeRatio
#"PageRatioHistPlot.jpg"のサイズ
PageHwidth <- 650 * sizeRatio
PageHheight <- 360 * sizeRatio
・月毎の毎日の訪問者数のデータを書き込むコマンド
ヒートマップの色調整として、6%以上を6%に調整しています。詳細はコマンド内のコメントを参考にしてください。
#データの書き込み
writeWorksheet(wb, MonthAnaData, sheet = "年間アクセス傾向まとめ",
startRow = 26, startCol = 3)
#タイトルの書き込み
writeWorksheet(wb, "月,日別訪問者数", sheet = "年間アクセス傾向まとめ",
startRow = 2, startCol = 3, header = FALSE)
writeWorksheet(wb, "月,日別訪問者数", sheet = "年間アクセス傾向まとめ",
startRow = 25, startCol = 3, header = FALSE)
###ヒートマップ#####
#データ作成
PlotData <- melt(MonthAnaData[1:31, 1:13], id.var = "日")
#パーセント化
ParcentData <- NULL
MonthVector <- unique(PlotData[, 2])
for(i in seq(MonthVector)){
VistData <- as.vector(subset(PlotData[, 3], PlotData[, 2] == MonthVector[i]))
ParcentData <- c(ParcentData, as.numeric(VistData) / sum(as.numeric(VistData), na.rm = TRUE) * 100)
}
#データの補正。6%以上を6%に調整
ParcentData <- ifelse(ParcentData > 6, 6, ParcentData)
#データの変更
PlotData[, 3] <- ParcentData
#日付けのfactor化
PlotData[, 1] <- factor(PlotData[, 1],
levels = sort(unique(as.numeric(PlotData[, 1])), decreasing = TRUE))
#プロット。
HeatMapPlot <- ggplot(PlotData, aes(x = PlotData[, 2], y = PlotData[, 1], fill = PlotData[, 3]))
HeatMapPlot <- HeatMapPlot +
geom_tile(colour = "white") +
labs(x = "", y = "日", fill = "訪問者(%)") +
scale_fill_gradient2(low = "steelblue", mid = "khaki", high = "red",
midpoint = 3, space = "Lab") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
axis.title.y = element_text(angle = 360, size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
########
###ヒストグラフ#####
###月#####
#データ作成
MHistPlotData <- as.data.frame(t(MonthAnaData[32:33, 2:13]))
MHistPlotData <- cbind(rownames(MHistPlotData), MHistPlotData)
#月の並び替えのための因子化
MHistPlotData[, 1] <- factor(MHistPlotData[, 1],
levels = month.name)
#合計プロット
MSHistPlot <- ggplot(MHistPlotData, aes(x = MHistPlotData[, 1],
y = MHistPlotData[, 2],
fill = "red"))
MSHistPlot <- MSHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE, width = 0.8) +
labs(x = "", y = "訪問者数", title = "月別訪問者数 合計") +
scale_fill_manual(values = "red") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
plot.title = element_text(size = 8),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
#平均プロット
MAHistPlot <- ggplot(MHistPlotData, aes(x = MHistPlotData[, 1],
y = MHistPlotData[, 3],
fill = "steelblue"))
MAHistPlot <- MAHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE, width = 0.8) +
labs(x = "", y = "訪問者数", title = "月別訪問者数 平均") +
scale_fill_manual(values = "steelblue") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
plot.title = element_text(size = 8),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
########
###日#####
#データ作成
DHistPlotData <- MonthAnaData[1:31, c(1, 14:15)]
DHistPlotData[, 1] <- formatC(as.numeric(DHistPlotData[, 1]), width = 2, flag = "0")
DHistPlotData[, 2] <- as.numeric(DHistPlotData[, 2])
DHistPlotData[, 3] <- as.numeric(DHistPlotData[, 3])
#合計プロット
DSHistPlot <- ggplot(DHistPlotData, aes(x = DHistPlotData[, 1],
y = DHistPlotData[, 2],
fill = "red"))
DSHistPlot <- DSHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE, width = 0.75) +
labs(x = "", y = "訪問者数", title = "日別訪問者数 合計") +
scale_fill_manual(values = "red") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
#平均プロット
DAHistPlot <- ggplot(DHistPlotData, aes(x = DHistPlotData[, 1],
y = DHistPlotData[, 3],
fill = "steelblue"))
DAHistPlot <- DAHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE, width = 0.75) +
labs(x = "", y = "訪問者数", title = "日別訪問者数 平均") +
scale_fill_manual(values = "steelblue") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
plot.title = element_text(size = 8),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
########
###グラフの書き込み#####
#ヒートマップ
#タイトルの書き込み
writeWorksheet(wb, "月,日別訪問者数割合(6%以上を6%に調整)",
sheet = "年間アクセス傾向まとめ", startRow = 3, startCol = 3,
header = FALSE)
#グラフの書き込み
jpeg(filename = "HeatMapDayofMonth.jpg", width = HMAMwidth, height = HMAMheight)
print(HeatMapPlot)
dev.off()
createName(wb, name = "HeatMapDayofMonth",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 2)), sep = "!"))
addImage(wb, filename = "HeatMapDayofMonth.jpg", name = "HeatMapDayofMonth",
originalSize = TRUE)
#月合計プロット
jpeg(filename = "MonthSumHistPlot.jpg", width = MonDaywidth, height = MonDayheight)
print(MSHistPlot)
dev.off()
createName(wb, name = "MonthSumHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 9)), sep = "!"))
addImage(wb, filename = "MonthSumHistPlot.jpg", name = "MonthSumHistPlot",
originalSize = TRUE)
#月平均プロット
jpeg(filename = "MonthAveHistPlot.jpg", width = MonDaywidth, height = MonDayheight)
print(MAHistPlot)
dev.off()
createName(wb, name = "MonthAveHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(14, 9)), sep = "!"))
addImage(wb, filename = "MonthAveHistPlot.jpg", name = "MonthAveHistPlot",
originalSize = TRUE)
#日合計プロット
jpeg(filename = "DaySumHistPlot.jpg", width = MonDaywidth, height = MonDayheight)
print(DSHistPlot)
dev.off()
createName(wb, name = "DaySumHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 16)), sep = "!"))
addImage(wb, filename = "DaySumHistPlot.jpg", name = "DaySumHistPlot",
originalSize = TRUE)
#日平均プロット
jpeg(filename = "DayAveHistPlot.jpg", width = MonDaywidth, height = MonDayheight)
print(DAHistPlot)
dev.off()
createName(wb, name = "DayAveHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(14, 16)), sep = "!"))
addImage(wb, filename = "DayAveHistPlot.jpg", name = "DayAveHistPlot",
originalSize = TRUE)
・月の曜日毎の訪問者数のデータを書き込むコマンド
ヒートマップの色調整として、20%以上を20%に調整しています。詳細はコマンド内のコメントを確認してください。
#データの書き込み
writeWorksheet(wb, WeekDayAnaData, sheet = "年間アクセス傾向まとめ", startRow = 26, startCol = 23)
#タイトルの書き込み
writeWorksheet(wb, "月,曜日別訪問者数", sheet = "年間アクセス傾向まとめ",
startRow = 2, startCol = 23, header = FALSE)
writeWorksheet(wb, "月,曜日別訪問者数", sheet = "年間アクセス傾向まとめ",
startRow = 25, startCol = 23, header = FALSE)
###ヒートマップ#####
#データ作成
PlotData <- melt(WeekDayAnaData[1:7, 1:13], id.var = "曜日")
colnames(PlotData) <- c("曜日", "月", "訪問者数")
#パーセント化
ParcentData <- NULL
MonthVector <- unique(PlotData[, 2])
for(i in seq(MonthVector)){
VistData <- as.vector(subset(PlotData[, 3], PlotData[, 2] == MonthVector[i]))
ParcentData <- c(ParcentData, as.numeric(VistData) / sum(as.numeric(VistData), na.rm = TRUE) * 100)
}
#データの調整。20%以上を20%に調整
PlotData[, 3] <- ifelse(ParcentData > 20, 20, ParcentData)
#曜日の因子化
PlotData[, 1] <- factor(PlotData[, 1],
levels = rev(unique(PlotData[, 1])))
#プロット
HeatMapPlot <- ggplot(PlotData, aes(x = PlotData[, 2], y = PlotData[, 1], fill = PlotData[, 3]))
HeatMapPlot <- HeatMapPlot +
geom_tile(colour = "white") +
labs(x = "", y = "曜日", fill = "訪問者(%)") +
scale_fill_gradient2(low = "steelblue", mid = "khaki", high = "red",
midpoint = 14, space = "Lab") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
axis.title.y = element_text(angle = 90, size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
###曜日#####
#データ作成
WDHistPlotData <- WeekDayAnaData[1:7, c(1, 14:15)]
WDHistPlotData[, 1] <- factor(WDHistPlotData[, 1],
levels = WDHistPlotData[, 1])
#合計プロット
DSHistPlot <- ggplot(WDHistPlotData, aes(x = WDHistPlotData[, 1],
y = WDHistPlotData[, 2],
fill = "red"))
DSHistPlot <- DSHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE) +
labs(x = "", y = "訪問者数", title = "日別訪問者数 合計") +
scale_fill_manual(values = "red") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
plot.title = element_text(size = 8),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = TextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = 8))
#平均プロット
MAHistPlot <- ggplot(WDHistPlotData, aes(x = WDHistPlotData[, 1],
y = WDHistPlotData[, 3],
fill = "steelblue"))
MAHistPlot <- MAHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE) +
labs(x = "", y = "訪問者数", title = "日別訪問者数 平均") +
scale_fill_manual(values = "steelblue") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
plot.title = element_text(size = 8),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
###グラフの書き込み#####
#ヒートマップ
#タイトルの書き込み
writeWorksheet(wb, "月,曜日別訪問者数割合(20%以上を20%に調整)",
sheet = "年間アクセス傾向まとめ",startRow = 3,
startCol = 23, header = FALSE)
#グラフの書き込み
jpeg(filename = "HeatMapPlotofMDay.jpg", width = MDaywidth, height = MDayheight)
print(HeatMapPlot)
dev.off()
createName(wb, name = "HeatMapPlotofMDay",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 23)), sep = "!"))
addImage(wb, filename = "HeatMapPlotofMDay.jpg", name = "HeatMapPlotofMDay",
originalSize = TRUE)
#月合計プロット
jpeg(filename = "DSHistPlot.png", width = MDaywidth, height = MDayheight)
print(DSHistPlot)
dev.off()
createName(wb, name = "DSHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 29)), sep = "!"))
addImage(wb, filename = "DSHistPlot.png", name = "DSHistPlot", originalSize = TRUE)
#月平均プロット
jpeg(filename = "DMAHistPlot.png", width = MDaywidth, height = MDayheight)
print(MAHistPlot)
dev.off()
createName(wb, name = "DMAHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 35)), sep = "!"))
addImage(wb, filename = "DMAHistPlot.png", name = "DMAHistPlot", originalSize = TRUE)
・曜日毎の時間別訪問者数のデータを書き込むコマンド
ヒートマップの色調整として、5%以上を5%に調整しています。詳細はコマンド内のコメントを確認してください。
#データの書き込み
writeWorksheet(wb, WeekDayHourAnaData, sheet = "年間アクセス傾向まとめ",
startRow = 26, startCol = 41)
#タイトルの書き込み
writeWorksheet(wb, "曜日,時間別訪問者数", sheet = "年間アクセス傾向まとめ",
startRow = 2, startCol = 41, header = FALSE)
writeWorksheet(wb, "曜日,時間別訪問者数", sheet = "年間アクセス傾向まとめ",
startRow = 25, startCol = 41, header = FALSE)
###ヒートマップ#####
#データ作成
PlotData <- melt(WeekDayHourAnaData[1:24, 1:8], id.var = "時間")
colnames(PlotData) <- c("時間", "月", "訪問者数")
#パーセント化
ParcentData <- NULL
MonthVector <- unique(PlotData[, 2])
for(i in seq(MonthVector)){
VistData <- as.vector(subset(PlotData[, 3], PlotData[, 2] == MonthVector[i]))
ParcentData <- c(ParcentData, as.numeric(VistData) / sum(as.numeric(VistData), na.rm = TRUE) * 100)
}
#データの補正。5%以上を5%に調整
PlotData[, 3] <- ifelse(ParcentData > 20, 20, ParcentData)
#時間の因子化
PlotData[, 1] <- factor(PlotData[, 1], levels = rev(unique(PlotData[, 1])))
#プロット。
HeatMapPlot <- ggplot(PlotData, aes(x = PlotData[, 2], y = PlotData[, 1], fill = PlotData[, 3]))
HeatMapPlot <- HeatMapPlot +
geom_tile(colour = "white") +
labs(x = "", y = "時間", fill = "訪問者(%)") +
scale_fill_gradient2(low = "steelblue", mid = "khaki", high = "red",
midpoint = 4.5, space = "Lab") +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
axis.title.y = element_text(angle = 90, size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
###グラフの書き込み#####
#ヒートマップ
#タイトルの書き込み
writeWorksheet(wb, "曜日,時間別訪問者数割合(5%以上を5%に調整)",
sheet = "年間アクセス傾向まとめ", startRow = 3, startCol = 43,
header = FALSE)
#グラフの書き込み
jpeg(filename = "HeatMapPlotofHour.jpg", width = HMPHwidth, height = HMPHheight)
print(HeatMapPlot)
dev.off()
createName(wb, name = "HeatMapPlotofHour",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 43)), sep = "!"))
addImage(wb, filename = "HeatMapPlotofHour.jpg", name = "HeatMapPlotofHour",
originalSize = TRUE)
・ページ閲覧数のデータをを書き込むコマンド
#データの書き込み
writeWorksheet(wb, MTopTenAnaData, sheet = "年間アクセス傾向まとめ",
startRow = 26, startCol = 52)
#タイトルの書き込み
writeWorksheet(wb, "月別ページ閲覧数 注意：訪問者数ではありません",
sheet = "年間アクセス傾向まとめ", startRow = 3, startCol = 52,
header = FALSE)
writeWorksheet(wb, "月別ページ閲覧数 注意：訪問者数ではありません",
sheet = "年間アクセス傾向まとめ", startRow = 25, startCol = 52,
header = FALSE)
###ヒストグラフ#####
#データ作成。閲覧割合1.5%以上
MHistPlotData <- subset(MTopTenAnaData[, c(1, 16)], MTopTenAnaData[, 16] > 1.5)
#ページ名の因子化
MHistPlotData[, 1] <- factor(MHistPlotData[, 1], levels = rev(MHistPlotData[, 1]))
#プロット
PageRatioHistPlot <- ggplot(MHistPlotData, aes(x = MHistPlotData[, 1],
y = MHistPlotData[, 2],
fill = "red"))
PageRatioHistPlot <- PageRatioHistPlot +
geom_bar(stat = 'identity', show_guide = FALSE) +
labs(x = "", y = "%", title = "閲覧数1.5%以上のページ") +
scale_fill_manual(values = "red") +
coord_flip() +
theme_bw(base_family = Fontfamily) +
theme(axis.ticks = element_blank(),
plot.title = element_text(size = 8),
axis.title.y = element_text(size = 8),
axis.text.x = element_text(size = XTextSize,
angle = 60,
hjust = 1.1,
colour = "grey50"),
axis.text.y = element_text(size = YTextSize))
###グラフの書き込み#####
#ヒストグラフ
jpeg(filename = "PageRatioHistPlot.jpg", width = PageHwidth, height = PageHheight)
print(PageRatioHistPlot)
dev.off()
createName(wb, name = "PageRatioHistPlot",
formula = paste("年間アクセス傾向まとめ", idx2cref(c(4, 55)), sep = "!"))
addImage(wb, filename = "PageRatioHistPlot.jpg", name = "PageRatioHistPlot",
originalSize = TRUE)
・データをエクセルに保存するコマンド
saveWorkbook(wb)
解析コマンドなどのまとめはこちらから：RでGoogle Analyticsの目次
その１「GoogleAnalyticsからデータの取得編」はこちらから GoogleAnalyticsの年間のデータをエクセルへ出力：その１「GoogleAnalyticsからデータの取得編」
少しでも、あなたのウェブや実験の解析が楽になりますように！！
