We are looking for problems with website conversion to Google Spreadsheets
Hi, Habr! My name is Dmitry Okunev, I work as a product-manager in the fintech company ID Finance. This is the first blog post of the company. Here we will share our experience, including international expertise. Go.
In most online businesses, website conversion is the cornerstone of turning a lead into a customer. We at ID Finance are making a huge amount of changes that can affect CR both in a positive and negative direction, therefore, without constant, literally daily tracking of the conversion funnel efficiency, it would be problematic to work.
There are many different approaches to measuring the conversion of your site; we have developed our own dashboard format for such tracking, which is based on the daily automated upload of a funnel from Google Analytics. Why exactly this way?
History knows very few cases where the product from the first iteration begins to convert incoming traffic into customers as effectively as we would like. We ourselves set up traps: an inefficient and complex UX, a large number of filling forms, bugs, the number of which grows with each delivery - you can and should work with all this. But in order to understand what to work with, problems first need to be: a) localized, b) constantly monitored, because both the product and the quality of traffic are constantly changing. Having at hand a tool to display the current situation, you can quickly make decisions on improvement, track problems, implement additional tracking (if they affect the user flow).
')
Now each of our projects (the company operates in eight countries under four brands) contains its own report, each of which contains 10-15 deshborda, covering all the main use cases for both new and repeat customers, including the mobile application.
Any funnel on the site can be described as a sequence of some user actions. Google Analytics allows you to describe this sequence as a segment.
The segments themselves are divided into conditions and sequences, and also set by the scope: users (users) or sessions (sessions). To get the desired user group, you can specify intersections (impose a condition on the sequence). For example, if we want to get a picture of the funnel customers only from mobile devices, with the participants of a certain variation of the AB-test.
Google Analytics provides a special Core Reporting API to access the data we need. You can get acquainted with it by the link . We will not dwell on the query syntax in detail here - Google documentation will in any case be much more comprehensive.
Here are just a few examples of the segment that you can query in the Core Reporting API:
It is not hard to guess that if we want to look at the conversion of the transition from the page / registration / step1 to / registration / step2, and then from the second to the third and so on, we need to consistently make a request to step 1, then to step 1 and step 2, and so on to the bitter end. To do this, we use a small script, which is written below.
Through many experiments, we came up with the following dashboard format - each project creates its own Google Spreadsheet document with a set of funnels of interest and a couple of pages on the configuration.
Here it is in its original state (plus a simple example). Link
The file is in read-only mode, so the easiest way is to copy it to yourself and conduct all experiments from there.
File structure:
The 'Funnels' sheet contains descriptions of all segments, in our case, funnels describing a different user flow. For example, there will necessarily be described a flow to fill in a questionnaire for a new client, all the ways for repeated ones (they may have several entry points), a funnel for pre-approved customers, etc.
Parameters of this sheet:
For example, filling out a new form will look like this:
When a report is unloaded, all the rows from the right column will be merged using the '- >>' characters, which in GA syntax means a non-strict sequence of actions (each next step is searched after the previous one, but not necessarily immediately). The report also supports the format of the sequence “immediately after”; to do this, at the beginning of the desired line you need to set the '->' characters. That is, the line 'ga: pagePath = ~ ^ \ Q / secure / registration / step4 \ E' tells the script to upload the number of users who were in this period to the URL / secure / registration / step4 , and before that to / secure / registration / step3 , but not necessarily in a row. The string '-> ga: pagePath = ~ ^ \ Q / secure / registration / step4 \ E' causes the script to search for a strict sequence, where the user visited the questionnaire step 3 and immediately after went to step 4 (it is assumed that no other hits, including events between them, too, was not sent).
Sheet 'Configuration' - here we store the configuration of the reports. Each report is uploaded and stored in a separate sheet of the document.
Sheet options:
The lines below, up to 'Funnels', contain GA response data, nothing needs to be filled in here.
Funnels - here we select one or several funnels from the 'Funnels' sheet, on the basis of which the report will be unloaded.
Go to Tools - Script Editor. Here is a small script for uploading data:
The script has not been updated for quite a long time and there are still many plans for improvement. For example, if the unloading time exceeds 5 minutes, the script may stop producing an error - it is necessary to continue unloading from this point on with a new request.
We post it “as is”, you can use it, modify it to your liking or write your own.
To run the script, you must first give it permission to access your Google Analytics profile and open access to the GA API in the Google Cloud Console. When you first run the script, you will get an error with a link in the 'Last Run Status' line of the 'Configuration' sheet, click on the link and enable API access. If necessary, you can also increase the quota for API requests, if the current is not enough.
The main cases that we solve with it - periodic and one-time upload of certain reports - are covered by the 'runPeriodicReports' and 'runOneOffReports' functions. To launch the 'Periodic' report, use the 'Current Project's Triggers' function, where we select 'runPeriodicReports' and the frequency of the launch.
Suppose we are interested in how the funnel of the questionnaire of new clients changed during the month. To do this, create the desired sequence on the sheet 'Funnels'. Let it be the sequence described above - let's call it 'new clients'.
Now we will create a report configuration that will upload us data through the 'new clients' funnel. To do this, select the period from 01/10/2018 through 01/31/2018, set the unloading period as 'daily' and the frequency as 'once'.
Now go to 'Tools' - 'Script Editor', select 'runOffReports' (because we chose the frequency of uploading 'Once', and click 'Run'.
We will create a new sheet on which data will appear after some time: the number of users at each stage of the funnel.
We divide each next step by the previous simplest formula and translate the result into percentages to get the conversion in percent. We also add an aggregated conversion, let it be the value of decision (our 'thank you page') / s1 - how our profile works from beginning to end. This will be useful to the marketing department.
Build graphics.
It is seen that on January 26, at the next delivery, we improved the conversion towards the end of the funnel. A rather low conversion at the first step of the funnel is the place where the user is created in the database. This may be a traffic pattern (many non-target customers), and UI problems.
Sometimes the picture is less pleasant.
It is seen that some changes have worsened our conversion in several places. It makes sense to look at the recordings of the sessions, to give the QA task the search for bugs, and it is possible to think about the correct changes we made in these parts of the product.
If the report we are satisfied with, we set the periodicity of the launch to daily in the 'Current Project Triggers' (we’ll set the launch time at night to have something to see in the morning), and convert it to the 'Periodic' mode on the 'Configuration' sheet. From this point on our schedule will be updated daily.
This way you can not only get daily conversion charts. We use it to quickly evaluate AB tests, find problems with traffic partners, and some other tasks. Of course, they should not be limited to one, and certainly you should not refuse to look at the numbers in your database in favor of this report, but you can safely use it to search for bottlenecks in your funnel and monitor it.
If you use your own custom solutions for tracking conversion, share in the comments - we will be happy to discuss!
Fintech Holding ID Finance specializes in data science and credit scoring. As an application of our expertise in the field of data collection and analysis, as well as the construction of scoring models, the company launched its own financial services under the brands MoneyMan, Solva, Plazo and AmmoPay. These are projects for lending to individuals and microbusiness, automating POS loans and installment programs. We operate in eight countries on four continents, including Europe, Asia and both Americas. The company's headquarters is in Barcelona, ​​an R & D center in Minsk, and a team of data analysts and risk managers in Moscow. In addition to me, the company employs about 600 people. It may seem that we are a little-known company. But it is not so. In the West, we know well. About us, for example, write Forbes, Business Insider, Finextra, Venture Beat, Crowdfund Insider, The Banker and the BBC. Thanks to the guys from PR, we also publish in Russian-speaking media: Forbes, VC, Roem, RusBase, etc. Several times a month we will publish our posts, share our news, successful and not quite case studies. We will be in touch!
In most online businesses, website conversion is the cornerstone of turning a lead into a customer. We at ID Finance are making a huge amount of changes that can affect CR both in a positive and negative direction, therefore, without constant, literally daily tracking of the conversion funnel efficiency, it would be problematic to work.
There are many different approaches to measuring the conversion of your site; we have developed our own dashboard format for such tracking, which is based on the daily automated upload of a funnel from Google Analytics. Why exactly this way?
- Google Analytics is session analytics, but in most cases we are interested in users, i.e. unique clientID. This data is most easily obtained by requesting the users metric from GA.
- In our case, a dashboard is a regular Google Spreadsheet file that can be easily shared, set permissions for different users, and reading it does not require much experience with analytics.
- The report data is suitable for periodic reports before the stakeholders, they are easily read and updated, and you can immediately see the conversion changes in the product.
- The flexible report format allows, in addition to monitoring CR, to perform a number of other tasks: quickly find problem areas of a user flow, measure AB tests, test hypotheses, perform quick calculations on data, etc.
Why do you need it?
History knows very few cases where the product from the first iteration begins to convert incoming traffic into customers as effectively as we would like. We ourselves set up traps: an inefficient and complex UX, a large number of filling forms, bugs, the number of which grows with each delivery - you can and should work with all this. But in order to understand what to work with, problems first need to be: a) localized, b) constantly monitored, because both the product and the quality of traffic are constantly changing. Having at hand a tool to display the current situation, you can quickly make decisions on improvement, track problems, implement additional tracking (if they affect the user flow).
')
Now each of our projects (the company operates in eight countries under four brands) contains its own report, each of which contains 10-15 deshborda, covering all the main use cases for both new and repeat customers, including the mobile application.
How it works?
Any funnel on the site can be described as a sequence of some user actions. Google Analytics allows you to describe this sequence as a segment.
The segments themselves are divided into conditions and sequences, and also set by the scope: users (users) or sessions (sessions). To get the desired user group, you can specify intersections (impose a condition on the sequence). For example, if we want to get a picture of the funnel customers only from mobile devices, with the participants of a certain variation of the AB-test.
Google Analytics provides a special Core Reporting API to access the data we need. You can get acquainted with it by the link . We will not dwell on the query syntax in detail here - Google documentation will in any case be much more comprehensive.
Here are just a few examples of the segment that you can query in the Core Reporting API:
- 1) Get a list of all users from mobile devices that were on page / registration / step1:
users :: condition :: ga: deviceCategory == mobile; ga: pagePath == / registration / step1 - 2) Get a list of users who were on page / registration / step1, and then on page / registration / step2:
users :: sequence :: ga: pagePath == / registration / step1; - >> ga: pagePath == / registration / step2
It is not hard to guess that if we want to look at the conversion of the transition from the page / registration / step1 to / registration / step2, and then from the second to the third and so on, we need to consistently make a request to step 1, then to step 1 and step 2, and so on to the bitter end. To do this, we use a small script, which is written below.
Our Spreadsheet
Through many experiments, we came up with the following dashboard format - each project creates its own Google Spreadsheet document with a set of funnels of interest and a couple of pages on the configuration.
Here it is in its original state (plus a simple example). Link
The file is in read-only mode, so the easiest way is to copy it to yourself and conduct all experiments from there.
File structure:
The 'Funnels' sheet contains descriptions of all segments, in our case, funnels describing a different user flow. For example, there will necessarily be described a flow to fill in a questionnaire for a new client, all the ways for repeated ones (they may have several entry points), a funnel for pre-approved customers, etc.
Parameters of this sheet:
- Reference Segment - reference segment. This can include, for example, all clients from the first session on the site. This segment will not stick together in a sequence and is requested separately. It is optional.
- Additional user condition for Funnel - an additional user parameter that can be superimposed on a funnel (for example, a variation of the AB test, for this you need to write it in GA, for example, in one of the Custom Dimensions)
- Additional session condition for Funnel - an additional session parameter that can be superimposed on a funnel (for example, ga: source == google; ga: medium == cpc - only users from Google contextual advertising).
- Sequence type - users or sessions. The type of sequence to be requested, users or sessions. By default, users will be prompted.
- Funnel steps are what we have gathered for. Here we consistently introduce all the funnel steps that interest us. A step can be any type of hit (hit), in our case these are events (event) and pageviews (pageview).
For example, filling out a new form will look like this:
When a report is unloaded, all the rows from the right column will be merged using the '- >>' characters, which in GA syntax means a non-strict sequence of actions (each next step is searched after the previous one, but not necessarily immediately). The report also supports the format of the sequence “immediately after”; to do this, at the beginning of the desired line you need to set the '->' characters. That is, the line 'ga: pagePath = ~ ^ \ Q / secure / registration / step4 \ E' tells the script to upload the number of users who were in this period to the URL / secure / registration / step4 , and before that to / secure / registration / step3 , but not necessarily in a row. The string '-> ga: pagePath = ~ ^ \ Q / secure / registration / step4 \ E' causes the script to search for a strict sequence, where the user visited the questionnaire step 3 and immediately after went to step 4 (it is assumed that no other hits, including events between them, too, was not sent).
Sheet 'Configuration' - here we store the configuration of the reports. Each report is uploaded and stored in a separate sheet of the document.
Sheet options:
- Run? - Periodic and Once options are available. We choose the first option if we want to make a periodic report update. In this case, one line will be added to the corresponding sheet at the top with new data corresponding to the selected period. Once - for a single pass. In this case, the sheet will be overwritten as a whole, so you need to be careful with the choice of this parameter. It is better to create a new report in the 'Once' mode, then simply switch to 'Periodic' if you want to continue the unloading by timer.
- View (Profile) ID - View ID from Google Analytics, from where we upload data. It is written in the format 'ga: XXXXXXXXX'.
- Start / End Date - for what period we upload data. It will be ignored during repeated runs of the Periodic report - it will be repelled from the dates in the unloading itself.
- Period Type - 'daily' options are available daily, 'WoD' is a sliding window in 7 days (we use case reports when traffic is low and 'daily' shows greater volatility), 'WoW' is calendar week, 'Whole Period '- get data for the entire period in one line (for understandable reasons, it is not compatible with reports of' periodic ').
- High precision? - the level of sampling data. You can read more about it, for example, here . By default 'yes', it is better to leave it like that.
The lines below, up to 'Funnels', contain GA response data, nothing needs to be filled in here.
Funnels - here we select one or several funnels from the 'Funnels' sheet, on the basis of which the report will be unloaded.
Script to unload
Go to Tools - Script Editor. Here is a small script for uploading data:
The script has not been updated for quite a long time and there are still many plans for improvement. For example, if the unloading time exceeds 5 minutes, the script may stop producing an error - it is necessary to continue unloading from this point on with a new request.
We post it “as is”, you can use it, modify it to your liking or write your own.
To run the script, you must first give it permission to access your Google Analytics profile and open access to the GA API in the Google Cloud Console. When you first run the script, you will get an error with a link in the 'Last Run Status' line of the 'Configuration' sheet, click on the link and enable API access. If necessary, you can also increase the quota for API requests, if the current is not enough.
The main cases that we solve with it - periodic and one-time upload of certain reports - are covered by the 'runPeriodicReports' and 'runOneOffReports' functions. To launch the 'Periodic' report, use the 'Current Project's Triggers' function, where we select 'runPeriodicReports' and the frequency of the launch.
Usage example
Suppose we are interested in how the funnel of the questionnaire of new clients changed during the month. To do this, create the desired sequence on the sheet 'Funnels'. Let it be the sequence described above - let's call it 'new clients'.
Now we will create a report configuration that will upload us data through the 'new clients' funnel. To do this, select the period from 01/10/2018 through 01/31/2018, set the unloading period as 'daily' and the frequency as 'once'.
Now go to 'Tools' - 'Script Editor', select 'runOffReports' (because we chose the frequency of uploading 'Once', and click 'Run'.
We will create a new sheet on which data will appear after some time: the number of users at each stage of the funnel.
We divide each next step by the previous simplest formula and translate the result into percentages to get the conversion in percent. We also add an aggregated conversion, let it be the value of decision (our 'thank you page') / s1 - how our profile works from beginning to end. This will be useful to the marketing department.
Build graphics.
It is seen that on January 26, at the next delivery, we improved the conversion towards the end of the funnel. A rather low conversion at the first step of the funnel is the place where the user is created in the database. This may be a traffic pattern (many non-target customers), and UI problems.
Sometimes the picture is less pleasant.
It is seen that some changes have worsened our conversion in several places. It makes sense to look at the recordings of the sessions, to give the QA task the search for bugs, and it is possible to think about the correct changes we made in these parts of the product.
If the report we are satisfied with, we set the periodicity of the launch to daily in the 'Current Project Triggers' (we’ll set the launch time at night to have something to see in the morning), and convert it to the 'Periodic' mode on the 'Configuration' sheet. From this point on our schedule will be updated daily.
Conclusion
This way you can not only get daily conversion charts. We use it to quickly evaluate AB tests, find problems with traffic partners, and some other tasks. Of course, they should not be limited to one, and certainly you should not refuse to look at the numbers in your database in favor of this report, but you can safely use it to search for bottlenecks in your funnel and monitor it.
If you use your own custom solutions for tracking conversion, share in the comments - we will be happy to discuss!
A little bit about us
Fintech Holding ID Finance specializes in data science and credit scoring. As an application of our expertise in the field of data collection and analysis, as well as the construction of scoring models, the company launched its own financial services under the brands MoneyMan, Solva, Plazo and AmmoPay. These are projects for lending to individuals and microbusiness, automating POS loans and installment programs. We operate in eight countries on four continents, including Europe, Asia and both Americas. The company's headquarters is in Barcelona, ​​an R & D center in Minsk, and a team of data analysts and risk managers in Moscow. In addition to me, the company employs about 600 people. It may seem that we are a little-known company. But it is not so. In the West, we know well. About us, for example, write Forbes, Business Insider, Finextra, Venture Beat, Crowdfund Insider, The Banker and the BBC. Thanks to the guys from PR, we also publish in Russian-speaking media: Forbes, VC, Roem, RusBase, etc. Several times a month we will publish our posts, share our news, successful and not quite case studies. We will be in touch!
Source: https://habr.com/ru/post/352748/
All Articles