飲食店顧客管理のヒント(テーブルチェックとPOSレジデータの統合データベースの作成）

高級店路線の飲食店にとって顧客管理は生命線といえます。「誰がいつ予約（利用）した」を捉えても、「いつどんなメニューをオーダーしたか」「予約した人だけではなく同席した人の状況はどうか」まで把握することで、的確なマーケティングができることになります。その中核は、予約管理システムとPOSシステムをドッキングさせるデータベースの作成とその運用（各種分析）です。

システム会社に大金払ってシステム入れようとしても、もともとレッドオーシャンな飲食業界、うまく運用ができずただ固定費が増えるだけというリスクも少なくありません。

そこで、ちょっとExcelができる方ならできるようコメントいたします。

問題の所在

飲食店向けの予約・顧客管理システムを導入して予約管理を行っているものの、現場が多忙なためレジシステム（POS）との連携が不十分で、どの顧客がいつ来店しどんなメニューをオーダーしたのかをほとんど把握できていませんでした。このため、RFM分析など各種分析や、顧客に対して的確なプロモーションができません。

予約・顧客管理システムをテーブルチェックで行う場合、たしかにテーブルチェックのダウンロードを行うと、顧客ごとの最終利用日や利用回数、利用累計金額が出てきます。しかしこれらはあくまでPOSレジデータと紐付けられたデータの集積であって、たとえば予約通り来店と利用はしたけれどもPOSレジデータと結びつけられていないと利用金額にはまったく反映されません。

さらに、POSレジデータとの紐付けが行われていても、画面上は過去の利用状況はわかりますが、データとしてはダウンロードできないため、もろもろの分析には使えません。これはテーブルチェックがダメなわけではなく、そこまでダウンロードできるとするとデータが膨大すぎるためと思われます。

そこで、POSレジデータにテーブルチェックの顧客情報を統合させることで、また、テーブルチェックの顧客データベースにPOSレジデータから得られたデータを紐付けることが多様な分析や情報を把握することができるのです。

大まかな順序

毎月のデータベースの作成はおおよそ次の手順となります。

テーブルチェックから顧客データベースをダウンロード

テーブルチェックから日々の予約・利用リストをダウンロード

POSシステムからのレジデータのダウンロード

レジデータとテーブルチェックのデータとの利用顧客の整合性の検証

POSレジ月次データへ利用顧客情報の統合

同伴者情報の追加とデータベースの完成

さまざまな分析

翌月以降のデータベースの更新

各手順の概要

1.テーブルチェックから顧客データベースをダウンロード

テーブルチェックの顧客データをダウンロードします。複数のファイルになることもあります。

ダウンロードされた情報を1枚のExcelシート（以下「テーブルチェックデータベース月次ワークシート」といいます。）にまとめます。

重要なのは、ダウンロードしたファイルには顧客IDが付与されていることです。この顧客IDを基礎にしてPOSのレジデータと紐づけを行うのです。

テーブルチェックの氏名の入力欄は「氏（かな）」「名（かな）」「氏（漢字）」「名（漢字）」の4つですが、これをワークシート上で「氏（かな）」「名（かな）」を「氏名（かな）」に、「氏（漢字）」「名（漢字）」「氏名（漢字）」にます。

まず、列を2つ挿入します。「氏名1」「氏名2」などのタイトルをつけます。そして、「氏（かな）」と「名（かな）」、「氏（漢字）」と「名（漢字）」をそれぞれ＆関数で結ぶのです。これによって、「氏名1」はかな、「氏名2」は漢字となります。そして、「氏（かな）」と「名（かな）」しか入力されていなくて「氏（漢字）」と「名（漢字）」はブランク（空欄）のことも少なくありません。すると「氏名2」がブランクのままになってしまいます。そこで、氏名1のかなの氏名をそのまま氏名2にコピペします。フィルターで（空白セル）を選べば簡単ですよね。

2.テーブルチェックから日々の予約・利用リストをダウンロード

テーブルチェックの日々の予約情報をダウンロードします。毎日やるのが面倒な場合は1ヶ月分をまとめてダウンロードすることになります。通常ダウンロードの時は日々1つのブックになっているため、「○年○月分」として1つのシート（以下「テーブルチェック月次データ」といいます。）にまとめます。

テーブルチェックの日々の予約情報をダウンロードすると、個々の予約について、予約した人の顧客IDが記載されています。

テーブルチェックでは、予約して来店した顧客に対して、オペレーションに応じて「確認」「確認済み」「ご案内済み」「お会計済み」とステータスが変化しますが、本来であれば、すべての予約客に対してPOSレジとの紐付けを行えば、キャンセル客以外はすべて「お会計済み」となります。この場合には、「テーブルチェック月次データ」のすべてにPOSの伝票番号が入っていることになります。実際はなかなかそうならないことも現実です。

3.POSシステムからのレジデータのダウンロード

今度はPOSレジデータのダウンロードを行います。

さて、POSレジデータはそのままでは使えないことがあります。伝票の取消し（赤伝）、伝票分割したりなど、いろいろな修正が入っているからです。これらまで取り込んでしまうと、重複したデータを集計しかねないからです。

とはいえ、ここで重要なのは、ダウンロードしただけのオリジナルのデータは必ずそのまま残すことです。もっとも、ダウンロードした情報はたいていCSV形式なので、これを「名前を付けて保存」でExcelブック形式で保存すればよいだけです。別のシートを作成してコピーし、そのシート上で編集を行うことです。なお、以下これを、「POSレジ月次データ」ということとします。

「POSレジ月次データ」の編集用シートから、必要のない行をすべて削除します。

「POSレジ月次データ」には、伝票番号に関する情報、伝票の人数、伝票金額、支払状況、注文した商品、商品の数量や価格など重要な情報が多数入っています。

そして、重要なのは顧客情報です。つまり、テーブルチェックの顧客情報とは別に、POSシステムでも顧客情報を入力できるのです。

4.POSレジ月次データへ利用顧客情報の統合

「POSレジ月次データ」に「テーブルチェック月次データ」の情報を紐付けるもっとも重要な作業です。

テーブルチェックでは、予約して来店した顧客に対して、オペレーションに応じて「確認」「確認済み」「ご案内済み」「お会計済み」とステータスが変化します。

本来であれば、すべての予約客に対してPOSレジとの紐付けを行えば、キャンセル客以外はすべて「お会計済み」となっています。この場合には、「テーブルチェック月次データ」にPOSの伝票番号が入っています。

「テーブルチェック月次データ」では、ひとつの予約に対して伝票番号が紐付けられます。追加で伝票を発行した場合には、ひとつの予約について複数の伝票番号が紐付けられます。

いっぽう、「POSレジ月次データ」のデータは、レジのデータだけに、ひとつの伝票番号に対してオーダーしたメニュー（商品）ごとに1行になっているため、ひとつの伝票番号が数十行になることもあり、月間では膨大な行数になります。

そこで、伝票番号をキーにして、「POSレジ月次データ」に「テーブルチェック月次データ」の顧客IDと氏名を紐付けするのです。

つまり、LOOKUP関数で検索するのは伝票番号であり、返される値は顧客IDです。

具体的には、「POSレジ月次データ」に3列を新たに挿入します。つまり、テーブルチェックデータベースの「顧客ID」「氏名（かな）」「氏名（漢字）」の3つの情報を貼り付けるための3列を挿入するのです。

「POSレジ月次データ」でも、テーブルチェックとは別に顧客情報を入力することができるため、データでも顧客情報が記録されている列があります。

このため、挿入する3列の位置ですが、「POSレジ月次データ」で顧客が記載されている列の隣が望ましいです。

基本的にはLOOKUP関数により紐付けすることになりますが、「POSレジ月次データ」での伝票番号と「テーブルチェック月次データ」での伝票番号とは完全一致しないため、LOOKUP関数で引っ張ることができないことがあるからです。

具体的には、「POSレジ月次データ」の伝票番号には番号（数値）の前に「No:」などが付いていたり、「テーブルチェック月次データ」の伝票番号は、割り勘による分割会計を行った場合には子番号が付されるため、桁数も異なります。そこで、いろいろな操作を行うことによって、「POSレジ月次データ」の伝票番号と「テーブルチェック月次データ」の伝票番号を一致させてLOOKUP関数で返せるようにします。

「POSレジ月次データ」に顧客IDを貼り付けることができたら、「テーブルチェックデータベース月次ワークシート」から顧客IDと紐付いている「氏名（かな）」「氏名（漢字）」情報をLOOKUP関数で貼り付けます。

5.POSレジデータとテーブルチェック月次データとの利用顧客の整合性の検証

「POSレジ月次データ」に、テーブルチェックの顧客IDや氏名の情報が紐付けられたことにより、POSのほうで独自に登録していた顧客情報が一致しているかどうかの検証を行います。

テーブルチェックの予約情報と実際のPOSレジデータで登録した顧客情報は基本的には一致するはずですが、紐付けを失念したり、まちがった伝票と紐付けていたり、テーブルチェックの予約者と実際の利用者が違っていたりするものです。

この整合性を検証するのがなかなか困難なものです。

たとえば、人数だとか、利用したテーブルだとか、日々の日報で記録されている顧客に関するメモ書き（「〇〇さんが〇名でご利用して〇〇を注文された」など）を手がかりにします。

6.同伴者情報の追加と累計データベースの完成

「POSレジ月次データ」を作成できたら、これを過去の月次データを蓄積したデータベース（以下「累計データベース」といいます。）に追加します。

ところで、テーブルチェックで予約をする場合には、基本的には予約者1人についての顧客IDが付与されます。

そして、「POSレジ月次データ」への「テーブルチェック月次データ」の情報を紐付ける場合も、基本的にはその予約者として紐付けられています。

いっぽう、テーブルチェックでも、POSレジでも、同伴者に関する情報を記録することができます。

この同伴者に関する情報をデータとして加えないと、同伴者が利用状況を的確につかむことはできません、

そこで、「同伴者データベース」のためのExcelシートを作成します。

たとえば、ある伝票について、予約したAさんと一緒にBさんとCさんも利用していた記録があった場合には、その伝票情報をすべて別のExcelブック（「同伴者データベース」）にコピーします。

オリジナルではAさんのみが利用していることになっているため、BさんとCさんも利用したならば「伝票番号-1」を作ってBさんの顧客IDと氏名情報を、「伝票番号-2」を作ってCさんの顧客IDと氏名情報をそれぞれ紐付けます。

そして、この「同伴者データベース」の情報も、過去の情報が蓄積したデータベースに追加します（以下「同伴者累計データベース」といいます）。

ただし、私の場合は、「累計データベース」と「同伴者累計データベース」は別に管理します。と申しますのは、同伴者情報を加えると、その分だけオーダー情報がダブることになります。また、将来的に同伴者の統合（夫婦の場合など）や同伴者の間違いがあった場合に、別管理していれば「同伴者累計データベース」だけの変更で足りますが、ドッキングしてしまうとわけわからなくなるおそれがあるためです。

このため、実際に分析等で使う場合には、「累計データベース」をコピーしたブックに対して「同伴者累計データベース」の情報を追加します。

具体的には、「同伴者累計データベース」から、フィルターで「-」の行を追加すればよいのです。先の例では、Aさんの情報はもともとあるため、「伝票番号-1」のBさん情報と「伝票番号-2」のCさんの情報が入ることになります。

こうして、同伴者情報も含めたデータベースが完成します。

7.さまざまな分析

「累計データベース」をコピーし、これに「同伴者累計データベース」から同伴者情報を加えたものを活用します。

たとえばピボットテーブルを用いて、顧客IDや氏名を「フィルター」にして、「行」に営業年月日、客数、商品カテゴリー、商品を、「∑値」に数量や価格とすれば、ある顧客が過去にいつ何人で来店して何をオーダーしていくら利用したのかをすぐにチェックすることができます。

これを営業時間前のミーティングで活用して、今日のメニューや物販などのおススメをスタッフで共有することができます。

あるメニューや物販商品について、どの顧客がいつオーダーしたかなどもチェックできます。

別の形で分析を行って顧客を来客頻度や利用日に応じてランキングするなどもできます（後述）。

8.翌月以降のデータベースの更新

その後、時の経過に応じて顧客情報の追加や修正や統合、削除は常に行われます。この場合に顧客IDや氏名情報も更新されます。新たな顧客IDが付されますし、前月までは名字しかなかった顧客もフルネームで情報が反映されたりします。

つまり、翌月になってダウンロードしたテーブルチェックデータベース月次ワークシートは、新規の増加分に加えて、既存の登録者の変更により別の顧客IDが付されていることになります。

また、「あの時実は誰々さんも一緒に利用していた」ことが後日判明することもあります。

このため、「累計データベース」「同伴者累計データベース」は毎月更新されなければならないのです。

私の場合、前回の「テーブルチェックデータベース月次ワークシート」からの顧客IDが、今回の「テーブルチェックデータベース月次ワークシート」からの顧客IDと一致するかLOOKUP関数でチェックします。

もちろん、今回から新規の顧客は前回には存在しないわけですが、既存の顧客でも顧客IDが変更されるものがあります。

もっとも、変更された顧客IDでも、過去飲食の利用がないものについては無視することができるので、過去利用があった顧客のIDを抽出し、「累計データベース」や「同伴者累計データベース」で更新を行います。

各種分析

こうして得られたデータベースからさまざまな分析を行うことができます。

典型的なものが、RFM分析です。

「最終利用日（Recency）」「利用頻度（Frequency）」「利用金額（Monetary）」の3つの指標を使って顧客をランキングする方法です。

ただし、利用金額については、先ほど申し上げたように同伴者分も含めた額で集計されるためミスリードする恐れがあるため、基本RFだけで分析し、適宜Mを使うことも考えられます。

さて、先ほどの日々の情報のベースはPOSレジデータがベースです。

ということは、伝票ごとにすべてのオーダー情報があるため、ひとつの伝票情報が数十行になることもあります。これは、個々の商品等や数量を把握するのには適していますが、人数や伝票金額については最終的な数値が数十行にもわたるため、この伝票金額はいくらだったのかとか何人利用したかなどの集計には適しません。

また、ランチタイムかディナータイムなのか、物販販売高を除いた飲食利用額はいくらなのかについても、伝票情報は1行で足ります。

そこで、「重複削除」機能で伝票情報を1行にすることでデータベースを軽くし、そこにランチかディナーか、飲食のみなのか物販のみなのか（これも元の伝票情報からの抽出や加工で取り出すことができます）などの情報を追加した「1行データベース」を作成します。

1行データベースで、ピボットテーブルの「フィルター」に「ランチかディナーか」「飲食か飲食なしか」「物販か」「曜日」「客層」などを、「行」に顧客IDを、列に「営業年」と「営業月」を、「∑値」に伝票番号を入れれば、顧客IDについて月次での伝票発行数（つまり利用回数）を出すことができます。

また、「行」に顧客IDを、「列」に営業年月日をとって、営業年月日を「MAX（最大）」にすれば直近の利用日をつかむことができます。

そこから、ディナー、ランチ、物販、あるいは曜日ごとに、利用日ごとのポイント、利用回数ごとのポイント、利用日ポイントと利用回数ポイントのウエイト付け（利用回数よりも最近利用したかを重視するか）によって、最終的なRFランキングを出します。

（おわり）