Case study
Advanced cohort reports. How to bring together CRM and marketing analytics in food tech.
How a food tech service set up a system of cohort reports for App + Web.
The Task
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.
Data Issues and Limitations
In the process of solving the set tasks, the food tech retailer and the MTRENDO-HOLOWAY 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. 
Requirements for Reports
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%)

● Last Non-Direct, First click, Linear
Proposed Data Collection and Processing Scheme
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 M&H Apps application collects and transforms AppsFlyer and ERP data as well as calculates the attribution and prepares datasets for visualization
An Example of a Marketing Report on ERP Data in Excel
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.
Marketing Report Based on Analytical DWH Data
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.
Cohort Report on ERP Data Used by the Company
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.
Classic Cohort Report Based on Analytical DWH Data
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.
Cohort Report in Google Data Studio
A cohort report in Google Data Studio 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 Results
  1. The food tech company received easy-to-use reports that allow you to get the necessary slice of data and visualize them almost instantly.
  2. The decision-making time was also decreased. 
  3. The insights found led to changes in media plans both at the global and the local levels.
As a main result, the company was able to fine-tune the advertising and optimize its budget.