Bitrix24: How to Use Customer Cohort Analysis to Measure Ad Performance
Our partnership with Bitrix24 started not all that long ago. At first, the company was only looking to automate their reports as they had to export data from Google Analytics and the advertising services on a daily basis. One of these reports was on customer cohort analysis. Marketing specialists from Bitrix24 decided to improve the report after reading a success story from another client — boodmo, the largest Indian online marketplace for automotive components.
Bitrix24 provides an integrated suite of products for business. The suite includes tools for project management, documentation, CRM, telephony, calendars, and more. The company has also recently released an online website builder and a tool for marketing automation. Bitrix24 also offers a free suite for companies with less than 12 employees.
Bitrix24 now has more than 3,000,000 registered portals on 13 domains. The company’s user coverage can be seen on the interactive Bitrix24 client map.
At first the main goal for Bitrix24 marketing specialists was to reach more customers and they did everything to get as many service registrations as possible. Now that they have gained a lot of active customers in their database, they push to operate smartly and improve the customer lifecycle. In addition, the company’s marketing experts wanted to spend their advertising resources only on the target audience ready to buy Bitrix24. One more thing to consider was that the customer acquisition costs have increased along with the business competition. That’s why most of Bitrix24’s advertising efforts start paying off two months or more after the purchase, as most customers pay for their Bitrix24 subscription on a monthly basis.
To spend the advertising budget more efficiently, Bitrix24 specialists decided to conduct customer cohort analysis. A cohort is a group of users sharing some common characteristics. By grouping users into cohorts based on the time of their first registration, Bitrix24 was looking to get more detailed information about what their customers do after they register on the website. Cohort analysis also helps calculate the revenue from each cohort, assess the effectiveness of customer acquisition campaigns and optimize advertising costs. In addition, comparing behavioral data for the cohorts across different sources, channels and campaigns helps understand which campaigns work better for customer acquisition and which ones improve retention rate and motivate users to register multiple portals.
We’re constantly evolving, both as a product and as a team that has some experience and expertise. Quantity becomes quality. I believe that any business comes to the understanding that you really can advertise more effectively and optimize your ad budget.
In order to do effective cohort analysis, Bitrix24 needed to bring the data together in one system. Besides that, they constantly faced data sampling while working with the free Google Analytics version. The company’s analysts wanted to avoid or at least minimize data sampling without having to export data by days.
Also the company required a number of specific metrics to be included in the cohort analysis report:
- Number of registrations — the number of registered portals.
- MAU — the number of monthly active users.
- CR to MAU — the number of monthly active users as compared to the number of registered users.
- Retention 2nd day — the number of customers that got back on the second day after registering a new portal.
- ?R to retention 2nd day — the percentage of customers that re-visit the website on the second day after the registration.
- Paying Users — the number of paying customers.
- CR to Paying — the number of paying users as compared to the number of registered users.
- Revenue SUM — the total revenue generated by a cohort.
- Churn Rate — the percentage of customer churn.
- ROMI — the return on marketing investment, calculated as revenue / costs × 100%.
Bitrix24 analysts solved the goal using the Google BigQuery cloud database the following way:
- Configuring the import of the customer behavior data and marketing costs data to Google BigQuery using OWOX BI Pipeline.
- Sending user and transaction data from 1C to Google BigQuery via the Measurement Protocol.
- Grouping users by cohorts in Google BigQuery and choosing all the necessary indicators.
- Sending the data from Google BigQuery to Google Sheets and creating cohort analysis reports.
The diagram below shows the data flow:
Now let’s take a look at each step in detail.
Step 1. Import data about web user behavior and ad costs to Google BigQuery
Having considered all the alternative platforms, Bitrix24 chose Google BigQuery for combining the data.
We’ve been using Google products for a long time and have studied Google Analytics well. We like how easy it is to work with data and how detailed the documentation is.
Bitrix24 analysts transfer web user behavior data to Google BigQuery using OWOX BI Pipeline.
Here are the advantages of this solution:
- No data sampling and limitations on the number of dimensions and metrics in Google Analytics reports. More information about the limitations can be found here.
- Data from the website appears in GBQ within only 5 minutes.
- Revenues and advertising costs can be compared by sessions and users, delivering better insights into the cohort profitability. OWOX BI Pipeline makes it possible to see how much money was spent to acquire each user, by distributing advertising costs over user sessions.
Also with the help of OWOX BI Pipeline, the company sends data from the advertising services first into Google Analytics and then to Google BigQuery. The exception is for AdWords since GA has a native integration with it.
Step 2. Transfer user and transaction data to BigQuery
Bitrix24 collects information about customers in the CRM system. Next, the company exports data about the users that re-visit the website on the second day after registering the portal, along with the data about active users and transactions to Google BigQuery via the Measurement Protocol.
Step 3. Create cohorts and calculate metrics
With all the necessary data collected in Google BigQuery, Bitrix24 analysts created cohorts and calculated the chosen metrics for each of the cohorts.
As customers pay for the company products monthly, the period of time for analysis was set to a month. Cohorts were created based on the time of the first registration. This means that all users who registered, let’s say in July, belong to the same cohort.
With metrics calculated in Google BigQuery the company got a table with the following structure:
Step 4. Create cohort analysis reports
Bitrix24 analysts visualized the data in Google Sheets by exporting the data from Google BigQuery via the OWOX BI BigQuery Reports add-on. The data in the Google Sheets reports is automatically updated every day and can be filtered by channel, source, campaign and ad content.
The numbers in the screenshot were changed for confidentiality purposes. The report demonstrates that the company’s advertising initiatives not only help acquire new customers. They also help increase customer lifetime value, bringing back already-acquired customers who’re looking to add another Bitrix24 portal. In most cases, the company’s advertising investments start to pay off in three months.
Creating the report turned out to be somewhat more complicated than we expected. We had to think over many things, understand what exactly we want to see, and what questions we need to answer with the report. But it was definitely worth it! We managed to automate a set of regular reports in the marketing department. Before that, we had to create such reports manually, double check everything and spend a lot of time and effort. Moreover, we got some interesting discoveries. For example, we found out that our advertising not only works for the customer acquisition but it also contributes to customer retention and helps us get more registrations from the same customer. We’d guessed so before, but discovering how effective our ads were in that regard, was a real surprise...
- Bitrix24 now has a set of fully automated reports that help measure the performance of advertising channels, sources and campaigns across customer cohorts, on a monthly basis.
- Knowing how well their advertising performs, Bitrix24’s marketing specialists can now optimize costs in order to increase the total revenue from each of the cohorts and reduce churn rate.
- With the help of the cohort analysis the company can determine the sources that bring in the most loyal customers. This information can also be used for remarketing.
If you have any questions or tips on how to do cohort analysis, we’re looking forward to your comments ;)
Credit your ad efforts properly with OWOX BI Pipeline.