コピペでできる!ECサイトのメルマガ効果測定をスプレッドシートで圧倒的に効率化する方法

column11_fig2

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

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

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

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

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

普段Googleスプレッドシートを使っていない方には少し難易度が高い内容のため、サンプルシートも配布しています。サンプルご希望の方はHIT-MALLのお問い合わせフォームにて、資料請求にチェックを入れ、自由入力欄に「ECメルマガ効果測定レポートを希望」とご入力ください。

必要な情報の準備

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テストや改善施策を積み重ねていくことが大切です。

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

      この記事で紹介しているGoogleスプレッドシートの「ECメルマガ効果測定レポート」のサンプルをご希望の方は、HIT-MALLのお問い合わせフォームにて、資料請求にチェックを入れ、自由入力欄に「ECメルマガ効果測定レポートを希望」とご入力ください。

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

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