Advanced cohort reports. How to bring together CRM and marketing analytics in food tech

In 2021, a nationwide restaurant chain transformed from a classic sushi restaurant with a delivery option and a small share of ECOM into a food tech company, in which the share of ECOM has grown significantly. In this regard, the importance of analytics as a decision-making tool has increased. At the same time, the mobile applications were updated followed by the website.

In this regard, the company set the task of creating an end-to-end App + Web analytics system and building an understandable and visually convenient cohort report to test the following hypothesis and tasks:

1. The hypothesis: increasing the advertising budget affects the increase in sales through organic channels.
2. The tasks: 

• budgeting based on the mutual influence of channels. How to attribute the value properly?
• making a global media plan considering local regional indicators and the needs of specific restaurants. 
• Customer Journey tracking during multi-channel interaction with the client on the website and in the application.

In the process of solving the set tasks, the food tech retailer and the iDataFusion team encountered some problems and limitations:

1. Sketchy data: the website and the mobile applications. Each of them is connected to CRM, but the user data is not combined in one path.
2. Inconsistency: The mobile applications use two analytics systems. Firebase performs full tracking, while AppsFlyer tracks installs and purchases. The website is tracked by GA UA. Desynchronization in the transfer of user identifiers, hashing method, and the presence and names of parameters.
3. Manual work: Many indicators were calculated manually upon request.
4. GEO location in Google Analytics is far from real.

Required parameters:
● purchased orders and revenue
● agency commissions, CPA rates
● promo codes

Custom geo-parameters:
● cities, divisions, regions

Analytics reports:
● classic end-to-end analytics report
● classic cohort report
● marketing reports based on cohorts (the share of the past record in the revenue is 70%)

Attribution:
● Last Non-Direct, First click, Linear

As a result, we have got a classic system integration scheme, in which all data is collected in Google BigQuery. The specifics of the case lie in the fact that the main array of initial data, GA4 and Firebase, is collected directly into Google BigQuery by native export.

The GCP Apps application collects and transforms AppsFlyer and ERP data as well as calculates the attribution and prepares datasets for visualization

Previously, the main report had to be collected manually. For a report on a particular region, a separate request to analysts had to be made.

Now all Excel report data has been visualized in Google Data Studio filtered by region. Drilldown to campaign and key word is also implemented. This dashboard can be used by managers at the regional level. It is also suitable for ad hoc analysis due to sufficiently deep granulation.


The report allows you to track changes in the main marketing KPIs in real time and make prompt decisions.

The report was based on buyers, not users. The possibility of filtering by source, call center, mobile application, and cash desk was implemented. The users, the amounts, or the orders were displayed.

As a result, we have decided to create cohorts of users. Filtering by sources, division by regions and by geo, and displaying the main indicators were implemented.

A cohort report in Looker was implemented based on the same data. Its principal difference is that the cohorts accumulate expenses and incomes.

In the “regular” marketing report, the income from newly attracted users is only 10%, so this report does not allow us to evaluate the effectiveness of advertising channels.

We have divided each of the revenue and expense figures into two items: “from the current cohort” and “from the past cohorts”, which makes it much more accurate to measure the effectiveness of marketing.

The food tech e-delivery company with MAU of 0.7 million and annual revenue of €20 million received easy-to-use marketing and sales cohort reports that allow you to get the necessary slice of data and visualize them almost instantly. The decision-making time was decreased. As a main result, the company was able to fine-tune the advertising, reduce customer acquisition costs in paid online ads by more than 15% and optimize its marketing budget.