セールスファンネル：Excelを使用してデータベースから自動更新されたレポートを作成する

オンラインサービスを販売している場合は、おそらく販売ファネルのすべての段階で何が起こるかを見たいと思うでしょう。 目標到達プロセスの分析から、重要な結論を導き出すことができます：アプリケーションのインストールと初期セットアップがどれだけ明確で便利か、サービスのアクティブユーザーになるクライアントの数、無料バージョンから有料バージョンに移行する割合。 さらに、コンバージョン率のダイナミクスにより、売り上げを伸ばすための対策が効果的であると結論付けることができます。



カットの下に、データ配列の分析に役立つ可能性のあるExcelを操作するためのテクニックのいくつかの説明があります。 Excelピボットテーブルを使用してjivosite.ruサービスの管理統計を維持し、セールスファンネルレポートの例を使用してODBC経由でMySQLに接続する方法を説明します。 提案された方法は非常にシンプルで普遍的であり、その助けにより数分で美しいレポートを作成できます。

問題の声明

jivosite.ruサービスの販売目標到達プロセスレポートを作成する必要があります。 これは、フリーストアモデルで販売されているオンラインストアのオンラインコンサルタントです。 ユーザーは、2週間のデモ期間に接続し、その期間中に拡張バージョンが利用可能になります。 2週間後、無料版が残りますが、これは時間制限なしで機能し続けます。



したがって、次の販売ファネルがあります。







目標到達プロセスの各レベルの顧客数を表示し、週と月ごとに分解した各レベルのコンバージョン率を計算する必要があります。 ソースデータはMySQLにあり、レポートはいくつかのボタンを押すことで自動的に作成される必要があります。また、必要に応じて、さまざまなカテゴリのセクションを作成し、追加プログラミングなしでフィルターを導入できます。

データベースからソースデータをロードします

データベースからExcelにデータをロードするには、ODBCドライバーが必要です。 ここでは、Windows用のODBC-MySQLコネクターを使用します 。 コネクタのあるケシでは何かがうまくいきませんでしたが、おそらく新しいバージョンではすでに修正されています。



ドライバーをインストールした後、空のExcelブックを作成し、[データ]-[他のソースから]-[Microsoft Queryから]タブを開きます







次に、「新しいデータソース」を選択し、接続の名前を入力し、「MySQL ODBC Driver」ドライバーを選択します。 次に、「通信」ボタンを押して、データベースに接続するためのパラメーターを入力し、「OK」をクリックします。 その後、接続が正常に確立されると、Microsoft Queryはクエリを作成するためのステップバイステップのウィザードを提供します。 失敗したポップアップウィンドウをすべて閉じてから、[SQL]をクリックして、ソーステーブルを手動で返すSQLクエリを入力します。 リクエストは、データベーススレーブサーバーから接続されたクライアントのテーブルから選択するだけです。



ソーステーブルでは、次の列を使用します。

• 作成済み-クライアント登録日
• 名前-サイトURL
• was_installed-クライアントがサイトにウィジェットをインストールした場合は1、インストールしていない場合は0
• chats_count-サービスを使用して保持されたダイアログの数
• is_paid-クライアントが何も支払わなかった場合は0、支払われた場合は1-

クエリの結果は、順序付けられたテーブルの形式でExcelワークシートに送られます。







この順序付けされたテーブルには、後で使用するいくつかの便利なプロパティがあります。

ピボットテーブルを作成する

大きなソースデータ配列を便利で美しいレポートに変換するには、 ピボットテーブルを使用します。 ソースデータを含むテーブルの左上のセル（セルA1）をクリックし、[挿入]-[ピボットテーブル]-[OK]をクリックします。 したがって、ピボットテーブルのソース結果はMySQLのクエリ結果全体になります。新しい列と行を追加すると、ピボットテーブルは自動的に更新されます。



空のピボットテーブルは次のようになります。

販売の各段階で顧客の数を数えます

このようなレポートを生成する前に、ソーステーブルの各行にクライアントが接続された週番号と年を追加する必要があります。 これは、年と週ごとにデータをグループ化するために必要です。 これを行うには、ソースデータを含むシートを開き、最後の列まで右にスクロールし、最後の列のヘッダーの右側のセルをクリックして、「接続週」と書き込みます。 行に空の値を持つ新しい列がテーブルに追加されました。 次に、新しい列の見出しの下のセルに、「= WEEKLY（」）という式を記述し、この行のセルをクリックします。ここで、クライアントの接続日を示します。







この場合、式は「= WEEKLY（[@ created]; 21）」のようになります。 セルが数式と同じ行にある場合、スマートExcelは列の名前でセルへのリンクを作成し、テーブルのすべての行にこの数式を自動的に入力します。 ソースデータテーブルに行を追加すると、新しい計算されたセルが自動的に追加されます。 便利、Excel尊重:)。 週数を計算するためのさまざまなアルゴリズムがあることに注意してください。 私たちは、スキーム番号21を選択しました。



同様に、「接続年」列に「= YEAR（[@ created]）」という式を追加します。 その後、ピボットテーブルを使用してシートに移動し、ピボットテーブルを右クリックして[更新]をクリックすると、テーブルがソースデータの新しい列を検出します。



もちろん、SQLを使用してこれらの列をソースデータに追加することもできますが、Excelでは、何らかの方法でより高速で快適です。 ただし、これはもちろん好みの問題です:)



ここで、「接続の年」列と「接続の週」列をフィールドのリストから「行名」フィールドにドラッグし、「名前」フィールド（このフィールドにサイトURLがあります）を「値」フィールドにドラッグします。







接続されたクライアントの数が年の週ごとに分類された、きちんとしたテーブルを取得します。 「名前」フィールドを値エリアにドラッグして、Exelがこの列の要素（つまり、すべての要素）を週と年でグループ化してカウントするようにしました。 これが登録数（目標到達プロセスの第2段階）になります。



毎週登録した顧客の何人がサイトにチャットをインストールしたかを計算します。 これを行うには、「was_installed」フィールドを値ペインにドラッグします。 このフィールドの初期データでは、ウィジェットがインストールされていない場合は「0」、インストールされている場合は「1」です。 次に、右クリック-[値フィールドのパラメーター]-[合計]操作を選択します。 これで、ピボットテーブルに2番目の列が表示されます。この列には、毎週登録した顧客のうち、サイトにウィジェットをインストールした顧客の数が表示されます。







次に、アクティブな顧客をカウントします。 サイト訪問者と20以上の対話をしたアクティブな人々を検討します。 これを行うには、セル式「= IF（[@ [chats_count]]> 20; 1; 0）」で列「is_active」をソースデータテーブルに追加する必要があります。 chats_count列には、クライアントチャットの数があります。 その結果、クライアントのチャットが20を超える場合、「is_active」列に「1」が表示されます。 is_activeフィールドも値の範囲にドラッグできるようになりました。



ヒストグラムの形で少し風水を追加し、列の名前を変更すると、このプレートが得られます。







ここでは、データベースから自動的に更新されるすてきな統計を既に受信しています。 データを更新するには、最初にソースデータのあるシートに移動し、そこでテーブルを右クリックして「更新」する必要があります。 次に、ピボットテーブル-[更新]を右クリックします。

コンバージョン率を計算します

k0を計算するには、Googleアナリティクスからユニークビジターのデータを取得する必要があります。これはこのマニュアルの範囲外です（ちなみに、Googleアナリティクスからコピーアンドペーストすることでこの問題を解決しています）。



まず、k1を計算します。これは、登録済みの顧客の総数に対する、サイトにチャットをインストールした人の数の比率です。



直接解決する方法を見つけられなかった、あまり美しくない瞬間があります。ソースデータテーブルで、式「= 1」の列「1」を追加します。これにより、ソーステーブルのすべてのセルでこの列にユニットが表示されます。



これで、このような計算フィールドを追加できます。







名前は「k1」と表記されており、式では「= SUM（was_installed）/ SUM（1）」を示しています。







レポートがピボットテーブルで週ごとにグループ化されている場合、ウィジェットをインストールした顧客数（SUM（was_installed））と今週登録した顧客総数（SUM（1））の比率を取得します。 レポートが月ごとにグループ化されている場合、それに応じて係数が再計算されます。 コンバージョンは、特定の週に登録したユーザーのうち、サイトにチャットをインストールしたユーザーの割合を示していることに注意することが重要です。 つまり クライアントが4週目に登録し、10週目にのみサイトでチャットを設定した場合、4週目のレポートの数字は変わります。



ここで、インストール済みのクライアントからアクティブなクライアントへの変換を検討します。

k2 = SUM（is_active）/ SUM（was_installed）



同様に、アクティブな顧客から有料の顧客に変換するためのフィールドを追加します。

k3 = SUM（is_paid）/ SUM（is_active）



スクリーンショットに表示できないのはk3のみです。企業秘密:)



ピボットテーブルにはフィールドk1、k2、k3があり、値の範囲にドラッグできます。 風水を少し追加すると、漏斗ごとにそのようなテーブルが得られ、週ごとに分類されます。







そこからいくつかの結論を引き出すことができますが、ビジネス分析の質問は別の投稿に任せます。今は技術的な問題に興味があります。

毎月の販売目標到達プロセス

週次レポートから、月次レポートの作成は非常に簡単です。 ソースデータで、「Connection Month」列を式「= MONTH（[@ created]）」で追加し、ピボットテーブル-「update」を右クリックして、ピボットテーブルの「Month of Connection」フィールドを「String Names」フィールド（フィールドの後）にドラッグします「接続年」）。 次のようになります。







そして、これは美しい毎月のタブレットです：

その他のレポートオプション

ピボットテーブルに慣れていない場合は、自分で試してみることをお勧めします。 これは、興味深い依存関係を特定しようとする優れた分析ツールです。 たとえば、顧客のソース（広告キャンペーン）のコンテキストのさまざまな段階でコンバージョンを確認するのは興味深いことです。 これを行うために、各クライアントを登録するときにUTMタグをデータベースに保存し、絶対（ルーブル）および相対（変換）単位でさまざまな広告キャンペーンの有効性に関するレポートを作成します。



ところで、ピボットテーブルの各セルをダブルクリックすると、この数値の計算に使用されたソースデータの行のリストが開きます。 どこから成長しているかを把握することは非常に便利です。



概要表には、興味深いレポートをすばやく取得できる機能とツールがさらに多くあります。 自分のビジネスで行われているプロセスに遅れないようにしたいすべての起業家がこれらのツールを習得することを強くお勧めします。