今回は、関数を使ってURLから各階層を抽出・取得する方法をご紹介します。
Google Analytics(GA)を使用している場合、クロスドメイン設定の有無によりパスだけのケースとドメイン名が付いているケースとがあるのでそれぞれ場合分けしてご紹介します。
(クロスドメイントラッキングの詳細は以下をご参照ください)
抽出するための関数は、FIND関数やLEFT関数・RIGHT関数を駆使して抽出するので若干ややこしい部分もありますが、解析業務において吐き出したURLから階層別に分析したいとき、そのままだと活用しづらいからデータとして整形したいときなどに活用できますのでぜひご活用ください。
それでは、それぞれのパターンについて見ていきたいと思います。
クロスドメイン設定なしの場合
第1階層を取得する関数
まず第1階層を取得する関数ですが、以下を画像のように入力しましょう。
=IFERROR((LEFT(B3,FIND(“/”,B3)))&(IFERROR(LEFT((RIGHT(B3,LEN(B3)-(FIND(“/”,B3)))),FIND(“/”,(RIGHT(B3,LEN(B3)-(FIND(“/”,B3)))))),””)),”-“)
<解説>
//IFERROR関数。最終行でエラー時に返す値を指定。
=IFERROR(
//左から数えて最初に「/」が出てくるまでの文字数分を返す
(LEFT(B3,FIND("/", B3))) //→返り値：「/」
// LEFT関数と並列でIFERROR関数を実行して結合する
&
(IFERROR(
LEFT(
//上記のLEFT関数で取得した「/」までを除いた文字列を取得
//(B3セルの全文字数) - (最初に「/」が出てくるまでの文字数)した文字数分、文字列を右から数えて返す
(RIGHT(B3,LEN(B3)-(FIND("/", B3)))),
//最初に「/」が出てくるまでの文字数を除いた文字列から、次に「/」が出てくる位置を取得
FIND("/",(RIGHT(B3,LEN(B3)-(FIND("/", B3)))))
), // →返り値：「」
//※LEFT(RIGHT関数で取得した文字列,FIND関数で取得した値)で最初に出てきた「/」から2回目に出てくる「/」までの文字列を取得できる
"")),
"-")
第2階層を取得する関数
続いて第2階層を取得する関数は以下のように入力しましょう。
=IFERROR(C3&(LEFT((RIGHT(B3,LEN(B3)-LEN(C3))),FIND(“/”,(RIGHT(B3,LEN(B3)-LEN(C3)))))),”-“)
第3階層を取得する関数
第3階層を取得する関数は以下のように入力しましょう。
=IFERROR(IF(COUNTIFS(D3,”<>-“),D3&(LEFT((RIGHT(B3,LEN(B3)-LEN(D3))),FIND(“/”,(RIGHT(B3,LEN(B3)-LEN(D3)))))),”-“),”-“)
クロスドメイン設定ありの場合
第1階層を取得する関数
第1階層を取得する関数は、以下を画像のように入力しましょう。
=IFERROR((LEFT(B3,FIND(“com/”,B3)+3))&(IFERROR((LEFT((RIGHT(B3,LEN(B3)-(FIND(“/”,B3)))),FIND(“/”,(RIGHT(B3,LEN(B3)-(FIND(“/”,B3))))))),””)),”-“)
<解説>
=IFERROR(
// B3セルから「com/」を検索し何番目に出てくるかを返す。
//そのままだと「c」が出てくる位置の値を返すことになるので、その値に3を足して「com/」までを取得する
(LEFT(B3,FIND("com/",B3)+3))
&
(IFERROR(
//上記のLEFT関数で取得した「/」までを除いた文字列を取得
//(B3セルの全文字数) - (最初に「/」が出てくるまでの文字数)した文字数分、文字列を右から数えて返す
(LEFT((RIGHT(B3,LEN(B3)-(FIND("/",B3)))),
//最初に「/」が出てくるまでの文字数を除いた文字列から、次に「/」が出てくる位置を取得
FIND("/",(RIGHT(B3,LEN(B3)-(FIND("/",B3))))))),
"")),
"-")
第2階層を取得する関数
第2階層を取得する関数は、以下を画像のように入力しましょう。
(第2・第3階層はクロスドメインなしと全く同じ関数で取得可能です！)
=IFERROR(C3&(LEFT((RIGHT(B3,LEN(B3)-LEN(C3))),FIND(“/”,(RIGHT(B3,LEN(B3)-LEN(C3)))))),”-“)
第3階層を取得する関数
第3階層を取得する関数は、以下を画像のように入力しましょう。
(第2・第3階層はクロスドメインなしと全く同じ関数で取得可能です！)
=IFERROR(IF(COUNTIFS(D3,”<>-“),D3&(LEFT((RIGHT(B3,LEN(B3)-LEN(D3))),FIND(“/”,(RIGHT(B3,LEN(B3)-LEN(D3)))))),”-“),”-“)
まとめ
いかがでしたか。
GAから出てくるURLは設定によって異なる形式出でてくるので微修正が必要な場合もあります。
もちろんディレクトリレポートから出力したり、セカンダリディメンションとして使うことで取得することも可能ではありますが、吐き出したローデータをデータを整形する必要があるようなケースではぜひ上記のような関数を使って作業を効率化・自動化して頂けたらと思います。