Sales funnel: make an automatically updated report from the database using Excel
If you sell an online service, you probably would like to see what is happening at each stage of the sales funnel. From the analysis of the funnel one can draw important conclusions: how clear and easy the process of installation and initial application settings, how many and which clients become active users of the service, what percentage goes from free version to the paid. In addition, the dynamics of conversion rates you can make a conclusion about the effectiveness of measures to increase sales.
Under the cut you will find a description of some techniques for working with Excel, which can be useful in the analysis of data arrays. We will explain how we conduct managerial statistics service jivosite.ru using Excel's pivot table and connect to MySQL via ODBC on the example of report on sales funnel. The proposed method is quite simple and universal, it can be used to create beautiful reports in minutes.
You want to build a report on sales funnel for your service jivosite.ru. This is an online consultant for online stores, which is sold on the freemium model. Users connect with a two-week demo period during which available the extended version. After 2 weeks remains a free version that continues to run indefinitely.
Thus, we have the following sales funnel:
You want to show the number of customers at each level of the funnel, as well as to calculate conversion rate for each level, broken down by weeks and months. The original data are in MySQL, reports should be built automatically by pressing a few buttons, and to allow, if necessary, to build sections for different categories and to enter filters without additional programming.
To load data from database to Excel, we need ODBC driver. In our case we will use connector ODBC MySQL Windows. On the Mac connector, we have something did not work, but maybe it's already corrected in new versions.
After installing the driver, create a blank workbook in Excel, open the tab "Data" — "From other sources" — "From Microsoft Query"
Then select "New data source", enter a name for the connection, select the driver "MySQL ODBC Driver". Then click "Connection", enter the connection settings to our database, click "OK". Thereafter, if the connection is successful, Microsoft will offer a step-by-step Query wizard to create queries. Close all pop-up disclaimer, and then click "SQL" and enter our SQL query, which will give the original table manually. Our query just selects from the table of connected clients with the slave DB server.
In our source table we will use the following columns:
the
The result of the query will go to Excel sheet in an ordered table.
This ordered table there are a number of useful properties, which we will later use.
To a large original dataset has become a convenient and beautiful reports, we will use PivotTable. Click on the upper-left cell of the table with the source data (cell A1) then Insert – Pivot table – OK. Thus, the original array to the pivot table will be the entire result of a query in MySQL, adding new columns and rows, the pivot table will be updated automatic.
An empty pivot table looks like this:
Before you generate a report, we for each row in the source table you want to add the week number and the year in which the client was connected. It is necessary to group the data by year and week. To do this, open the worksheet with the source data, scroll to the right until the last column, we click on the cell to the right of the heading of the last column, they write "Week connection". In the table, added new column with empty values in the rows. Now in the cell under the new column, write the formula "=NOMADINI(" and click on the cell in the row, in which we indicate the date the client connection.
In this case the formula will look like "=NOMADINI([@created];21)". If the cell is in the same row as the formula, Excel generates a smart link to it by column name, and automatically fills all rows in the table this formula. When adding rows to the source data table new calculated cell will be added automatically. Conveniently, Akselu respect :). Please note that there is different algorithms for computing the week number. For ourselves, we chose the scheme No. 21.
Similarly, add the column "Year of connection" with formula "=YEAR([@created])". After this, proceed to the worksheet with our pivot table, and click on the PivotTable, right-click – "Refresh" below the table to know about the new columns in the source data.
Of course, these columns could be added to the original data with SQL, but Aksel is somehow faster and nicer. Although it is certainly a matter of taste :)
Now drag the columns "Year of connection" and "Week connection" from the field list into the "row labels" and the "name" field (this field stores the URL of the site) in the Values area.
We get a neat table, where the weeks of the year, breaking a number of connected customers. We dragged the "name" field to the values area to Aksel counted the number of items in this column (i.e. all elements), grouped by weeks and years. This is the number of registrations (second stage funnel).
Count the number of registrants in each week clients installed on the site our chat. To do this, drag the field "was_installed" in the values area. In this field in the source data is "0" if the widget is not selected and 1 if selected. Then right click "value field Settings" select the operation "Sum". Now in the pivot table a second column, where we see how many of the clients registered in any week, installed the widget on the website.
Now calculate active clients. Active will be considered those who held more than 20 conversations with the site visitors. For this we need the source data table add column "is_active" cell formula "=IF([@[chats_count]]>20;1;0)". In the column "chats_count" we have a number of chat client. As a result, in the column "is_active" we will have a "1" if the customer has more than 20 chat rooms. Now the field is_active can also be dragged to the values area.
Adding a little fenhua in the form of histograms and renaming the columns, we get such a sign:
Here we've got nice statistics, which is also automatically updated from the database. To update data, you must first go to the sheet with the source data, there is a right click on the table – "Update". And then right click on pivot table – Refresh.
To calculate k0, it is necessary to take data on unique visitors from Google Analytics, and we'll leave it beyond the scope of this manual (this task, by the way, we do decide to copy-paste from Google Analytics).
First, let's count k1 – the ratio of the number installed the chat on your website to the total number of registered clients.
There is one really beautiful moment that we have not found how to solve the direct way: in the original data table need add column "one" with the formula "=1" to all acaciah the original table appeared a unit in this column.
You can now add this calculated field:
The name write "k1" to specify the formula "=SUM(was_installed)/SUM(one)".
If the PivotTable report is grouped by week, we get the ratio of the number of customers who have installed the widget (SUM(was_installed)), to the total number of clients registered in the week (SUM(one)). If the report is grouped by month, the coefficient will be recalculated accordingly. It is important to note that the conversion rate shows you what percentage of customers have installed the chat on its website among those who have registered in a particular week. Ie, if a customer registers on the fourth week, and installed the chat on the website only for 10 weeks, it will change the figure in the report for the 4th week.
Now consider the conversion of the active clients:
k2 = SUM(is_active)/SUM(was_installed)
Similarly, add the field for the conversion of active clients in paid:
k3 = SUM(is_paid)/SUM(is_active)
Only k3 in the screenshots I can't show you a trade secret :)
Now our pivot table were fields k1, k2, k3, which you can drag into the values area. Adding a little fenhua we have the following table on the funnel by weeks:
From it it is already possible to draw some conclusions, but the issues of business intelligence, we will leave for another post, now we are interested in the technical aspects.
Of the weekly report to make the report by months is very simple. In the source data, add a column "Month connect" with the formula "=MONTH([@created])", right click on the pivot table – "update" and drag it into the PivotTable, the field "Month connection" to the "row labels" (after field "Year of connection"). You get something like:
And here is a beautiful plate by months:
If you are not familiar with PivotTables, I suggest you play with them yourself. This is a great Analytics tool, which pogam to identify interesting dependencies. For example, it is interesting to see the conversion rate at different stages in terms of source of clients (advertising campaigns). For this purpose we save in database the labels UTM when you register each client and build a report on the effectiveness of different advertising campaigns in absolute (RUR) and relative (conversion) units.
By the way, double click on each cell in a PivotTable opens a list of data rows that were used to calculate the figures. Very convenient to understand where it grows.
In pivot tables there are many features and tools that allow you to quickly to interesting reports. Highly recommend to all entrepreneurs who want to be aware of the processes taking place in their business, to master these tools.
Article based on information from habrahabr.ru
Under the cut you will find a description of some techniques for working with Excel, which can be useful in the analysis of data arrays. We will explain how we conduct managerial statistics service jivosite.ru using Excel's pivot table and connect to MySQL via ODBC on the example of report on sales funnel. The proposed method is quite simple and universal, it can be used to create beautiful reports in minutes.
problem Statement
You want to build a report on sales funnel for your service jivosite.ru. This is an online consultant for online stores, which is sold on the freemium model. Users connect with a two-week demo period during which available the extended version. After 2 weeks remains a free version that continues to run indefinitely.
Thus, we have the following sales funnel:
You want to show the number of customers at each level of the funnel, as well as to calculate conversion rate for each level, broken down by weeks and months. The original data are in MySQL, reports should be built automatically by pressing a few buttons, and to allow, if necessary, to build sections for different categories and to enter filters without additional programming.
Downloaded the source data from the database
To load data from database to Excel, we need ODBC driver. In our case we will use connector ODBC MySQL Windows. On the Mac connector, we have something did not work, but maybe it's already corrected in new versions.
After installing the driver, create a blank workbook in Excel, open the tab "Data" — "From other sources" — "From Microsoft Query"
Then select "New data source", enter a name for the connection, select the driver "MySQL ODBC Driver". Then click "Connection", enter the connection settings to our database, click "OK". Thereafter, if the connection is successful, Microsoft will offer a step-by-step Query wizard to create queries. Close all pop-up disclaimer, and then click "SQL" and enter our SQL query, which will give the original table manually. Our query just selects from the table of connected clients with the slave DB server.
In our source table we will use the following columns:
the
-
the
- created date — the date of registration of the client the
- name — the URL of the site the
- was_installed — 1 if the client sets a widget on your site, 0 if never installed the
- chats_count — number of dialogues held with the help of our service the
- is_paid 0, and if a customer we didn't pay anything, 1 — if pay
The result of the query will go to Excel sheet in an ordered table.
This ordered table there are a number of useful properties, which we will later use.
Create a pivot table
To a large original dataset has become a convenient and beautiful reports, we will use PivotTable. Click on the upper-left cell of the table with the source data (cell A1) then Insert – Pivot table – OK. Thus, the original array to the pivot table will be the entire result of a query in MySQL, adding new columns and rows, the pivot table will be updated automatic.
An empty pivot table looks like this:
Consider the number of customers at each stage of sales
Before you generate a report, we for each row in the source table you want to add the week number and the year in which the client was connected. It is necessary to group the data by year and week. To do this, open the worksheet with the source data, scroll to the right until the last column, we click on the cell to the right of the heading of the last column, they write "Week connection". In the table, added new column with empty values in the rows. Now in the cell under the new column, write the formula "=NOMADINI(" and click on the cell in the row, in which we indicate the date the client connection.
In this case the formula will look like "=NOMADINI([@created];21)". If the cell is in the same row as the formula, Excel generates a smart link to it by column name, and automatically fills all rows in the table this formula. When adding rows to the source data table new calculated cell will be added automatically. Conveniently, Akselu respect :). Please note that there is different algorithms for computing the week number. For ourselves, we chose the scheme No. 21.
Similarly, add the column "Year of connection" with formula "=YEAR([@created])". After this, proceed to the worksheet with our pivot table, and click on the PivotTable, right-click – "Refresh" below the table to know about the new columns in the source data.
Of course, these columns could be added to the original data with SQL, but Aksel is somehow faster and nicer. Although it is certainly a matter of taste :)
Now drag the columns "Year of connection" and "Week connection" from the field list into the "row labels" and the "name" field (this field stores the URL of the site) in the Values area.
We get a neat table, where the weeks of the year, breaking a number of connected customers. We dragged the "name" field to the values area to Aksel counted the number of items in this column (i.e. all elements), grouped by weeks and years. This is the number of registrations (second stage funnel).
Count the number of registrants in each week clients installed on the site our chat. To do this, drag the field "was_installed" in the values area. In this field in the source data is "0" if the widget is not selected and 1 if selected. Then right click "value field Settings" select the operation "Sum". Now in the pivot table a second column, where we see how many of the clients registered in any week, installed the widget on the website.
Now calculate active clients. Active will be considered those who held more than 20 conversations with the site visitors. For this we need the source data table add column "is_active" cell formula "=IF([@[chats_count]]>20;1;0)". In the column "chats_count" we have a number of chat client. As a result, in the column "is_active" we will have a "1" if the customer has more than 20 chat rooms. Now the field is_active can also be dragged to the values area.
Adding a little fenhua in the form of histograms and renaming the columns, we get such a sign:
Here we've got nice statistics, which is also automatically updated from the database. To update data, you must first go to the sheet with the source data, there is a right click on the table – "Update". And then right click on pivot table – Refresh.
Consider conversion rates
To calculate k0, it is necessary to take data on unique visitors from Google Analytics, and we'll leave it beyond the scope of this manual (this task, by the way, we do decide to copy-paste from Google Analytics).
First, let's count k1 – the ratio of the number installed the chat on your website to the total number of registered clients.
There is one really beautiful moment that we have not found how to solve the direct way: in the original data table need add column "one" with the formula "=1" to all acaciah the original table appeared a unit in this column.
You can now add this calculated field:
The name write "k1" to specify the formula "=SUM(was_installed)/SUM(one)".
If the PivotTable report is grouped by week, we get the ratio of the number of customers who have installed the widget (SUM(was_installed)), to the total number of clients registered in the week (SUM(one)). If the report is grouped by month, the coefficient will be recalculated accordingly. It is important to note that the conversion rate shows you what percentage of customers have installed the chat on its website among those who have registered in a particular week. Ie, if a customer registers on the fourth week, and installed the chat on the website only for 10 weeks, it will change the figure in the report for the 4th week.
Now consider the conversion of the active clients:
k2 = SUM(is_active)/SUM(was_installed)
Similarly, add the field for the conversion of active clients in paid:
k3 = SUM(is_paid)/SUM(is_active)
Only k3 in the screenshots I can't show you a trade secret :)
Now our pivot table were fields k1, k2, k3, which you can drag into the values area. Adding a little fenhua we have the following table on the funnel by weeks:
From it it is already possible to draw some conclusions, but the issues of business intelligence, we will leave for another post, now we are interested in the technical aspects.
sales Funnel by month
Of the weekly report to make the report by months is very simple. In the source data, add a column "Month connect" with the formula "=MONTH([@created])", right click on the pivot table – "update" and drag it into the PivotTable, the field "Month connection" to the "row labels" (after field "Year of connection"). You get something like:
And here is a beautiful plate by months:
Other reports
If you are not familiar with PivotTables, I suggest you play with them yourself. This is a great Analytics tool, which pogam to identify interesting dependencies. For example, it is interesting to see the conversion rate at different stages in terms of source of clients (advertising campaigns). For this purpose we save in database the labels UTM when you register each client and build a report on the effectiveness of different advertising campaigns in absolute (RUR) and relative (conversion) units.
By the way, double click on each cell in a PivotTable opens a list of data rows that were used to calculate the figures. Very convenient to understand where it grows.
In pivot tables there are many features and tools that allow you to quickly to interesting reports. Highly recommend to all entrepreneurs who want to be aware of the processes taking place in their business, to master these tools.
Комментарии
Отправить комментарий