メール配信の効果測定をGoogle スプレッドシートで効率化する方法

column11_fig2

ECサイトのリピーター獲得施策として欠かせないのがメルマガ配信。しかしメールの原稿作成やリスト作成、ワークフローの作成などだけでも大きな負担となります。配信しただけで終わってしまっている、効果測定やその後の考察と改善、さらには改善施策を行った後のメールの効果の確認までとなると、とても手が回らないというEC事業者の方も多いでしょう。

そこで、この記事ではGoogleスプレッドシートをGoogleアナリティクスと連携することで、劇的にECメルマガの効果測定を効率化できる上に、配信数からコンバージョンまで、チェックすべき各指標を串刺しで効果測定ができるシートを作成する方法を解説します。

最初にレポートを設定していくのに時間がかかりますが、一度作ってしまえば自動的にデータが更新されていく優れものです。最終的には以下のようなレポートができあがります。

ECメルマガ効果測定レポートのサンプル

メルマガ効果測定レポートサンプル

普段Googleスプレッドシートを使っていない方には少し難易度が高い内容のため、サンプルシートも配布しています。以下よりダウンロードしてご利用ください。

メール配信効果を測る指標(KPI)

一般的に、配信したメールの効果は以下の指標(KPI)を参考に良し悪しを判断します。

メール配信効果を測る指標(KPI)

開封率
配信したメールのうちの開封した購読者の割合。
クリック率
メルマガ内のURLをクリックされた割合。特別な理由がなければ、母数は開封数に設定した方が効果を測定しやすい。

必要な情報の準備

Googleアナリティクスでの測定は、メルマガを配信する際に設定したパラメータを活用して行います。

メルマガ配信時に効果測定のために「https://sample.jp/?utm_medium=email&utm_source=newslettersample&utm_campaign=20200914」のようなパラメータを付与したURLで配信しているかと思います。今回は例として、以下のようなパラメータ設定で配信している場合のレポートを作成していきます。

utm_medium 必ず「email」を設定し、デフォルトチャネルグループが「Email」として計測されるようにしている。
utm_source 必ず「newsletter」を設定している。
utm_campaign メルマガ配信日をキャンペーン名に設定している。
utm_content A/Bテストは実施しておらず、特に設定していない。

まず、配信時に付与した参照元(utm_source)とキャンペーン(utm_campaign)などのパラメータ情報をスプレッドシートに書き出して整理しておきます。A/Bテストを実施して、広告のコンテンツ(utm_content)も利用している場合は、コンテンツ欄も追加しましょう。

さらに、Googleアナリティクスでは測定できない情報をスプレッドシートに追記します。メールの配信数、メールの開封数(開封率)はメール配信ツールのデータを追記します。

また、配信日と効果測定の終了日を入力する欄も忘れずに作成します。例えば配信日から2週間分のデータを取得する場合は、「=[配信日のセル]+13」という計算式を入力しておきます。

スプレッドシートの入力例

ID 配信日 計測終了日 メディア
(medium)
参照元
(source)
キャンペーン名
(campaign)
配信数 開封数
1 2020-04-19 2020-05-02 email newsletter 20200419 1,000 100
2 2020-05-25 2020-06-07 email newsletter 20200525 1,110 130

Googleスプレッドシートにアドオンを追加

続いて、GoogleスプレッドシートにGoogleアナリティクスのデータを出力するためのアドオンを追加しましょう。新しいGoogleスプレッドシートを開き、「アドオン」>「アドオンを取得」から、Googleアナリティクスのアドオンを選択してインストールします。

column11_img1.png

「アドオン」>「アドオンを取得」をクリックする。


column11_img2.png

「Google Analytics」を選択する。


column11_img3.png

「インストール」ボタンをクリック。


column11_img4.png

インストールに成功すると、アドオンに「Google Analytics」が追加される。

Googleアナリティクスのデータを出力

アドオンの追加ができたら、いよいよGoogleアナリティクスのデータを呼び出す設定をします。

レポート設定

「アドオン」>「Google Analytics」>「Create new report」を順にクリックすると、画面右に「Create a new report」というレポート作成画面が表示されます。レポート作成画面では、「レポート名」「ビュー」「メトリクスとディメンション」の3点を設定する必要があります。設定の仕方について、順に説明していきます。

column11_img5.png

「アドオン」>「Google Analytics」>「Create new report」をクリックする。


column11_img6.png

レポート名

レポート名はGoogleアナリティクスから呼び出したデータを書き出すシートの名前になります。あとで集計シートを作成する際に使用しますので、分かりやすく短い名称にしておきましょう。ここでは「すべて」というレポート名に設定しました。

ビューを選択

今回レポートを作成したいECサイトのアカウント、プロパティ、ビューを選択します。

メトリクスとディメンション

レポートに含めるメトリクス(セッションなど)とディメンション(キャンペーン名など)を選択します。今回はそれぞれ以下を選択してください。

メトリクス(Metrics)
  • Sessions
  • Transactions
  • Revenue
ディメンション(Dimention)
  • Date
  • Default Channel Grouping
  • Medium
  • Source
  • Campaign

設定が終わったら最後に「Create Report」ボタンをクリックすると、Report Configurationというシートが新たに作成されます。

column11_img7.png

「Report Configuration」シートが追加される。

レポート設定の微調整

ECのメルマガ効果測定に必要な情報を抽出できるように、あと少しだけレポート設定を加工します。編集するのは、「Start Date」「Segments」「Limit」です。

Start Dateの初期値は30daysAgo(30日前)となっています。たとえば2020年度のデータを抽出するレポートにしたい場合は「2020-04-01」など特定の日付に書き換えましょう。

Segmentsはデータを抽出するセグメントを絞り込むための項目です。今回はECサイトのメルマガ配信に限定したレポートを作成したいので、「デフォルトチャネルグループがメールである」という条件を追加します。

Limitは初期値は1000となっていますが、そのままでは最大1,000件までしか出力しなくなってしまうため、空にしておきます。

最終的に以下のように設定できているのを確認できれば、レポートの設定は完了です。

「すべて」レポートの設定

Report Name すべて
View ID ※ECサイトのGoogleアナリティクスのビューID
Start Date 2020-04-01
End Date yesterday
Metrics ga:sessions,ga:transactions,ga:transactionRevenue
Dimensions ga:campaign,ga:source,ga:medium,ga:date,ga:channelGrouping
Segments sessions::condition::ga:channelGrouping==Email
Limit

メルマガごとの直帰率も把握したい場合はもうひと手間

1つのレポートだけでもメルマガのクリック率やCVRを集計することはできますが、さらに直帰率も把握したい場合は、もう1つレポートを作成します。

先程作成した「すべて」のレポート設定をコピーして隣の列に張り付け、セグメントを変更します。今度は「デフォルトチャネルグループがメールである」かつ「ページビュー数が2より小さい」という条件のセグメントを設定します。

「直帰」レポートの設定

Report Name 直帰
View ID ※ECサイトのGoogleアナリティクスのビューID
Start Date 2020-04-01
End Date yesterday
Metrics ga:sessions,ga:transactions,ga:transactionRevenue
Dimensions ga:campaign,ga:source,ga:medium,ga:date,ga:channelGrouping
Segments sessions::condition::ga:channelGrouping==Email;perSession::ga:pageviews<2
Limit
column11_img8.png

「すべて」と「直帰」の2種類のレポートを設定すると上記の内容になる。

レポートの実行

続いて「アドオン」 > 「Google Analytics」>「Run Reports」をクリックしてレポートを実行し、Googleアナリティクスからデータを抽出します。レポート生成が完了すると「Report Status」というメニューが表示されるので、OKボタンをクリックします。新しく「すべて」と「直帰」という名前のシートが作成されているはずです。

column11_img9.png

「アドオン」 > 「Google Analytics」>「Run Reports」をクリック。


column11_img10.png

成功すると「2 reports completed successfully」というメッセージが表示される。

集計用のスプレッドシートを作成

ECメルマガ効果測定レポートの完成イメージ

メルマガ効果測定レポートサンプル

無事にGoogleアナリティクスからメルマガ配信のセッション数やコンバージョン数のデータを抽出できましたが、今のままではただデータが並んでいるだけで、効果の評価はできません。続いて集計用のシートを作成していきます。

冒頭で作成したメルマガの配信日、配信数、パラメータ情報などを整理したシートに、クリック数やCV数、売上、直帰率を入力する列を追加しましょう。それぞれ以下の表を参考に計算式を入力してください。

集計用シートの各列の計算式

項目名 値/計算式
A列 No ※手入力
B列 配信日 ※手入力
C列 効果計測終了日 =B2+13
※計測期間が2週間の場合
D列 メディア
(medium)
※手入力
E列 参照元
(source)
※手入力
F列 キャンペーン名
(campaign)
※手入力
G列 配信数 ※手入力
H列 開封数 ※手入力
I列 クリック数 =sumifs('すべて'!F:F,'すべて'!B:B,E2,'すべて'!A:A,F2,'すべて'!D:D,">="&B2,'すべて'!D:D,"<="&C2)
J列 CV数 =sumifs('すべて'!G:G,'すべて'!B:B,E2,'すべて'!A:A,F2,'すべて'!D:D,">="&B2,'すべて'!D:D,"<="&C2)
K列 売上 =sumifs('すべて'!H:H,'すべて'!B:B,E2,'すべて'!A:A,F2,'すべて'!D:D,">="&B2,'すべて'!D:D,"<="&C2)
L列 直帰 =sumifs('直帰'!F:F,'直帰'!B:B,E2,'直帰'!A:A,F2,'直帰'!D:D,">="&B2,'直帰'!D:D,"<="&C2)
M列 直帰率 =iferror(L2/I2,"-")
N列 開封率 =iferror(H2/G2,"-")
O列 クリック率 =iferror(I2/H2,"-")
P列 CVR =iferror(J2/I2,"-")

※注:列IDがずれると計算式もそれに応じてずらす必要があります。また、シートの名称を「すべて」と「直帰」ではない名称に設定していた場合も、シート名の部分を変更する必要があります。

クリック数、CV数、売上

sumifs関数は複数の条件に当てはまる行を合計する関数です。今回は、

  • 「すべて」シートのB列(Source)が「集計シート」のE列の参照元(source)と一致する
  • 「すべて」シートのA列(Campaign)が「集計シート」のF列のキャンペーン名(campaign)と一致する
  • 「すべて」シートのD列(Date)が「集計シート」のB列の配信日以降かつC列の計測終了日以前である
    • のすべてに当てはまる行を集計しています。

      直帰

      直帰(率)は「直帰」シートに出力したデータを活用して算出します。こちらもsumifs関数を使い、参照元、キャンペーン、日付の条件すべてが当てはまる行を集計します。

      これで、冒頭でお見せしたようなレポートが完成します。

      ECメルマガ効果測定レポートの完成イメージ

      メルマガ効果測定レポートサンプル

      毎日の更新も自動化

      これでGoogleアナリティクスのデータをExcelにエクスポートして、コピペして、といったような作業をしなくてよくなりました。忙しいEC事業担当者のために、さらにこのレポートのデータ更新も自動化させてしまいましょう。

      「アドオン」>「Google Analytics」>「Schedule reports」をクリックすると、レポートのスケジュール設定モーダルが表示されます。「Enable reports to run automatically.」にチェックをいれ、レポートを実行する頻度と時間を設定しましょう。

      毎日レポートの実行を行わなくても、自動でシートのデータを更新してくれるようになります。

      column11_img11.png

      「アドオン」>「Google Analytics」>「Schedule reports」をクリック。


      column11_img12.png

      「Enable reports to run automatically.」にチェックを入れ、更新頻度と更新の時間を設定する。

      おまけ:メルマガのパラメータ発行も一元管理で便利に!

      効果測定のレポートにメルマガ配信時のパラメータを追加すると、一元的に管理できるためとても便利です。パラメータ発行シートを作成したら、メルマガ効果測定シートに入力するキャンペーン名などはパラメータ発行シートのデータを参照するようにすると、データ入力の二度手間を省けます。

      パラメータ発行シートの例

      項目名 値/計算式
      A列 No ※手入力
      B列 配信日 ※手入力
      D列 メディア(medium) ※手入力
      E列 参照元(source) ※手入力
      F列 キャンペーン名(campaign) ※手入力
      G列 ランディングページ ※手入力
      H列 パラメータ付URL =F2&"?utm_medium="&C2&"&utm_source="&D2&"&utm_campaign="&E2

      ECメルマガ効果測定用パラメータ生成シートのサンプル

      ECメルマガ効果測定用パラメータ生成シートのサンプル

      まとめ

      若者を中心にメール離れが加速しているとも言われますが、メルマガはECサイトの重要なリピーター獲得施策です。配信結果の振り返りと、地道なA/Bテスト、さらに件名の変更や配信する時間帯の工夫などの改善施策を積み重ねていくことが大切です。

      昨今では、読者の状況や感情に合わせてメールの内容を送信するためには、読者の属性をデータベース化し、その情報を活用することもメルマガ配信の成功に重要な要素となってきました。こちらについて、「EC事業者向けMA(マーケティングオートメーション)ツールとメール配信ツールの違いと活用法」でMAツールとメール配信ツールという切り口で詳しく解説していますので、参考にしてください。

      たとえば、メルマガを受信したユーザーにとって、「件名」は最初に興味を引かれる部分です。このメルマガ効果測定レポートから開封率が低下していることが分かったら、「件名」に工夫をするのが重要でしょう。レポートを活用すると、こういったメール配信の効果を高める改善ポイントを見つけることができるのです。

      一方で配信頻度が高ければ高いほど、配信対象のセグメントが複雑であればあるほど手間がかかる側面もあります。なるべく効果測定のレポート作成と確認に時間をかけすぎず、振り返りと改善の検討により時間とパワーをかけていきましょう。

      この記事で紹介しているGoogleスプレッドシートの「ECメルマガ効果測定レポート」のサンプルご希望の方はECメルマガ効果測定レポート ダウンロードページにて、ダウンロードしてご利用ください。

      ECメルマガ効果測定レポートのサンプル

      メルマガ効果測定レポートサンプル

      メルマガは既存顧客向けの施策としてとても重要で効果の高いプロモーション施策ですが、新規顧客の獲得は不得意です。新規顧客を獲得するためにどんな施策を選んだら良いのかについては、「ECサイトの集客力をUPさせるWEBプロモーション施策の選び方」を参考にしてください。

      ECメルマガ効果測定レポート

      GoogleスプレッドシートをGoogleアナリティクスの連携で、メルマガの効果測定を劇的に効率化。配信数からコンバージョンまで、各指標を串刺しで確認できるレポートのサンプルを配布中です。