How to automate marketing reporting in the restaurant business

With the help of the marketing agency and OWOX BI products, marketers at the restaurant chain have entirely updated their analytics system. 

There are 57 restaurants and delivery service locations, serving both Japanese and Italian cuisine. In connection with the business’s scaling and diversification, the marketing department faced new tasks:

• Understand what advertising activities bring new users to the website and mobile application and how much it costs to attract a client. They had to combine data from the website, mobile application, and CRM system to figure this out.
• Receive fast performance reports and compare actual performance with the forecast. For this, they needed to automate marketing reporting.
• Evaluate the incremental impact of media impressions on performance channels and targeted website and mobile application activities. To do this, they merged raw data at the client ID level with impressions from the Campaign Manager.

Let’s see how they accomplished each of these tasks and give some advice to those just starting down the marketing automation path.

Note: All reports presented in the article are based on a test dataset.

The team built advanced analytics based on OWOX BI, Google BigQuery, and Power BI. Schematically, the system looks like this:

OWOX BI imports cost data from all advertising systems as well as raw website data from Google Analytics and application data from AppsFlyer. Then it transmits all this data to Google BigQuery.

Google BigQuery also receives forecast metrics that agency experts calculate in the interface using the R language.

Inside Google BigQuery, data is processed and merged using views and scheduled queries.

Finally, the data is visualized in Power BI.

The team focused on three reports by which they can track the necessary metrics:

• Basic management report for the balanced scorecard system
• Report on attracting users to the website
• Report on attracting users to the application

They developed an Impact Matrix — a hierarchy of metrics — to determine which metrics to focus on and in which cases. They also used the balanced scorecard methodology to monitor prediction data. As a result, they received a report that allows to:

• understand which metrics are ahead and which are behind the plan
• track major KPIs that have different weights for the business (CPO, CR, Revenue, CTR)
• predict KPIs

In the report architecture, can be distinguish three levels: the business level, KPI level, and detailed level.

Business level

At this level, you can see the plan vs fact for crucial business metrics.

From the data above, the team can conclude that the CR KPI was exceeded by 43%. At the same time, the traffic volume and conversions didn’t reach the planned values. A high CR can indicate a good website and application UX — there are few dips, and users convert well. However, it’s necessary to increase the volume of traffic to ensure the required sales volume.

KPI level

Based on the KPI-level report, the marketers analyze efficient and inefficient placements from the point of view of CR and cost indicators — this allows to quickly split the budget between channels to fulfill the general plan.

The report illustrates the upper-level correspondence of planned and actual KPIs (CR, Visits, Revenue, Transactions) to the forecast values. For example, you can see that the plan for overall website traffic volume wasn’t met, but this is offset by higher CR in organic and direct traffic. Accordingly, it’s necessary to increase the CR of paid traffic (CPC type) with more dynamic optimization or an increased purchase price. The team may also need to increase the purchase of paid traffic in principle to fulfill the general traffic plan.

Detailed level

At this level, marketers look at the dynamics of metrics by region and device type — this also helps redirect the budget to what works better.

From the data above, can be concluded that the team missed the plan in terms of the total number of transactions and CR for the given period of analysis by 5.5%. At the same time, both desktop and mobile traffic show approximately the same tendency to fall behind the volume of conversions (desktop fell by 5.7%, mobile by 5.6%). Accordingly, both device types bring about an equal number of conversions. But to optimize, traffic in the CR section must be analyzed.

Here, can be seen that mobile traffic (1.8%) and tablet traffic (2.8%) showed the lowest CRs in December. Since mobile traffic brings about the same number of conversions as desktop traffic and is probably cheaper, marketers can try to optimize mobile traffic by purchasing better quality traffic to increase its CR.

This graph illustrates the performance plan for traffic across cities. With information on bought volumes, it’s possible to conclude where underperformance is critical and impacts the effectiveness of all campaigns.

Thanks to these reports, the team could divide all purchases into new and returning customers. This made it possible to exclude returning customers from advertising campaigns for new customers, thereby, reducing CPO across all channels. By seeing statistics in the channels section for returning and new users simultaneously, the marketers were able to understand which sources drive more new orders and tilt the budget in their favor. In sources where repeated purchases prevail, they reduced the budget, thereby reducing the total planned CPO.

By the way, the main KPIs in these reports are calculated based on CRM data.

The report above demonstrates that the largest number of new purchases for all traffic from the website is led by inst_kz (81.82%), Facebook Ads (43.45%), mobrain_int (31.25%), and gomobile_int (30.38%). Since, in general, the company has an active and loyal audience and a high percentage of market coverage, it’s clear why some websites lead customers who have already made an order at least once in the application. For greater efficiency and execution of the Customer Acquisition KPI, the team can try to exclude the active CRM audience in the ad campaign settings and launch promotions for new customers.

Here, you can see that more than half of desktop traffic across all channel groups is returning customers. This indicates the importance of working with a loyal base and increasing retention.

With post-view analytics, you can evaluate the influence of media activity on the number of orders.

Impression statistics are uploaded to BigQuery from Google Campaign Manager and are detailed down to the individual user (client ID). The report compares segments of those who saw and those who didn’t see media ads. As a result, we can evaluate the incremental effect of media impressions on performance channels and targeted actions on the website and in the mobile application.

For example, the team learned that conversions to orders from those who saw videos and banners were 42% higher than for those who didn’t see them. Due to post-view analytics, they also determined that the audience of those who saw media advertising brought in double the income.

In the future, the team plans to develop cross-platform reporting. The first step is to evaluate the impact of web traffic on application conversions by linking mobile application and website data. Such reporting will create a complete understanding of the value of advertising channels and assess their impact on each other. It will also help to make management decisions about the development of customer interaction channels and adjust the marketing communication strategy.