Comprehensive analytics of an online project using Google Analytics and Google Docs

Recently, we managed to deal with a variety of analytical systems, build an economic model, and conduct several experiments to optimize conversions to registration and activation. Thanks to Ilya Krasinsky (ex. LinguaLeo product chief), we learned how to properly configure and analyze Google Analytics data, learn how to build economic models of Internet projects. Next, we began to deal with event based analytics and setting mixpanel. Now we decided to share this knowledge with the habrosocommunity.





In this article we will look at:

• Build a product economy using google spreadsheets
• Generating a Google Analytics report on cohorts and channels
• Creating a paid channel performance analysis table

Build a product economy using google spreadsheets

First let's build an economic model. Most online projects work according to the simplest scheme:



Attracting a user -> Registration -> Activation -> Payment

')

Accordingly, almost all projects need to consider the following metrics:

User Acquisition - the number of attracted users

Registration Conversion - conversion from attracted user to registered

Registrations - the number of registered users

Paying Conversion - conversion from attracted user to paid.

Payments - the number of purchases

Revenue - revenue



In addition, one user can make multiple purchases. Moreover, different purchases can be of different sizes, so to build the economy of the product we will need several more metrics:

Average Price - average purchase price

Average Payment Count - the average number of purchases



Now that we know the average check of our user and the average number of payments, we can calculate how much money each paying user brings us. Just multiply the average bill by the average number of purchases.

Average Revenue per Paying User - the average amount received from one paying user:

ARPPU = Average Price * APC

So, we got to one of the main metrics.

Average Revenue per User - Average amount received from one attracted user



We can get this figure if the average income from the payer is multiplied by the conversion into payment:

ARPU = ARPPU * Paying Conversion

Or we can simply divide the revenue by the number of attracted users:

ARPU = Revenue / User Acquisition

Almost always, Internet projects use various paid promotion channels, so we will introduce several more metrics:

Marketing costs - Costs of attracting users

Cost per Acquisition - Cost of attracting one user

CPA = Marketing costs / User Acquisition

And of course, if we deduct the cost of attraction from our revenues, we will get a profit.

Profit - Profit

Profit = Revenue - Marketing costs

Now consider an example:

We attracted 10,000 users for 100,000 rubles. 20% of users registered and 1% of users made a purchase. The average purchase check was 500 rubles and users pay us an average of 1.2 times.



Then we get the following model:

User Acquisition	10,000
Registration Conversion	20%
Registrations	2000
Payment Conversion	one%
Payments	100
Average Price	500 rub.
Average Payment Count	1.2
ARPPU	600 rub.
ARPU	6 rub.
CPA	10 rub.
Marketing costs	100,000 rubles
Revenue	60 000 rub.
Profit	-40 000 rub.

As we see, the cost of attracting a user is 10 rubles, and we earn only 6 rubles per user. Most product economies do not pay off. But now we know what parameters we can influence in order to make the product payback.



For those who want to build a similar model for their project, I created a Google table with ready-made formulas and comments for each metric.

Report generation in Google Analytics by cohorts and channels

In order to fill in the above table, we need to get the correct numbers from various analytical systems.

Most of these numbers can be obtained if you properly configure Google Analytics. We use the Measurement Protocol , which allows you to send events directly from the backend. We also use Ecommerce Tracking , which allows you to transfer data about purchases and revenues.







Thus, we can send the following events:

• check in
• Activation
• Purchase

In our case, we considered the user activated if he viewed at least one lesson from any course.



Next, you need to set up goals in Google Analytics to display conversion data for various events.



Goals are configured in the Admin tab of the Goals menu. There is no need to customize the purchase goal, the purchase is automatically considered a goal if you send it through Ecommerce Tracking.



The next step is to create a report in the Settings tab, thanks to which we will see conversions to registration, activation and purchase for each channel.





To analyze product changes over time, we can set up cohorts. To do this, click on the arrow in the upper left corner and create segments with the date of the first session. Usually, cohorts are created weekly or monthly.





As a result, you should get something like this:





It is worth noting that in this case, only new users are taken into account, which have gone through the funnel attraction -> registration -> activation -> purchase. If your user first came from the search, then received a letter and made a purchase through the letter, then this will not be the first visit, but the attraction channel in this report will be considered an e-mail, not a search.

Creating a paid channel performance analysis table

Using data from the report, we can compare the effectiveness of different channels. To do this, export the data to a table using the appropriate menu. Then we remove the extra sources of traffic, add metrics and indicate the costs for each channel. As a result, you should get something like this table:





Using this table, we can analyze the effectiveness of each channel in terms of activation and marketing / revenue ratio. So you will understand what kind of advertising brings you a profit, and what - only losses.



Now you know how to conduct a basic analysis of your Internet project. The next step is to analyze user behavior and conduct experiments to optimize conversions. For these purposes, we actively use another analytical tool - Mixpanel.