ROPO analysis: How useful is it for omnichannel marketing analytics

Vlada Malysheva, Creative Writer @ OWOX

Mar 19, 2019

Up to 40% of customers go online to learn more about the products and their availability before they visit the offline store and buy something. The percentage of such customers surely depends on the company. However, tons of users first see online ads or special offers, read reviews and testimonials from the website, and only then do they decide to purchase offline. This means that your online ad initiatives can have a serious influence on the number of offline sales.

In this case, we describe the solution provided by the OWOX BI team for a chain of stores is part of the Sephora company (owned by the LVMH group) and occupies a leading position in the global market for perfumes and cosmetic products. It had challenges with applying ROPO analysis.

Goal

Customers can generally buy the goods offered by the retailer both online and offline. When buying a new perfume, a customer may first want to explore the aromas and only then make a purchase online or in a physical store.

The marketing team wanted to deeply understand the behavior of their users in terms of their interaction between online and offline stores. They wanted to show in numbers that online marketing efforts aren’t limited to generating revenue from online orders but also affect offline sales (the so-called research online, purchase offline, or ROPO, effect).

We suggested building a system of omnichannel marketing analytics and reports.

Challenge

The priority for the company, as for many large omnichannel retailers, was to build an effective marketing analytics system across all sales channels.

The first problem faced by the marketing team in solving this problem was data fragmentation. Throughout the company’s existence, a lot of data had been accumulated, and it was stored in various sources and formats, each with its own specific processing method. To determine the ROPO effect, a single repository was needed in which all data necessary for analysis could be combined.

From this problem followed another: What storage to use? There were two options:

Store all data on the company’s own servers.
Upload all data to cloud storage.

Each data storage option has its advantages and disadvantages. In the case of using the company’s own servers, it’s necessary to take into account the time spent organizing such storage, the money required to buy the necessary hardware, maintenance costs, problems with scaling, and the need to build an automated system for collecting and processing data.

The next challenge was choosing a tool to automate the delivery of data from various sources to a single repository for further analysis. There are quite a few tools for this, but it was needed to choose the best in terms of price, quality, functionality, flexibility, and scalability.

Solution

To solve problems with ROPO analysis, the marketing experts and analysts had to take the following steps:

Select a single repository for merging data
Automate the flow of data
Based on the data obtained, build the reports and dynamic indicators necessary for the company’s management

To implement this plan, the marketing team turned to OWOX BI, since we’re experts in online analytics and data fusion and have been a partner of the company since 2016.

Step 1. Choose a single repository for merging data

The experts chose Google Cloud Storage as unified storage with a connection to Google BigQuery. The main reasons for choosing Google Cloud Storage were:

Speed. Google Cloud Storage can process terabytes of information in seconds and petabytes in minutes.
Efficiency and cost transparency. Compared with other tools, Google’s service is inexpensive and convenient to use.
Simple scalability. With a significant increase in data volume, you don’t need to reserve additional capacity, virtual servers, etc.
Convenient integration with external services. A large number of integrations are available for working with data from popular services.
Reliability and data security. BigQuery’s security, regulatory, and certification standards allow you to store sensitive data in your project.
Google Analytics 360 Export. It’s possible to upload raw data from Google Analytics directly to BigQuery (including historical data for the past 13 months).
Compute Engine, Data Prep, etc. Google Cloud has many integrated services that simplify the implementation of solutions.

Step 2. Automate the flow of data

The team of analysts, following the recommendations of OWOX BI, did the following actions to automate the data flow:

Set up automatic data export from Google Analytics to Google BigQuery. The company is a user of Google Analytics 360, which allows it to fully customize integration with BigQuery in a few clicks.
Set up automatic integration of Google Ads and Google Analytics. It’s worth noting that part of the cost data is automatically imported into Google Analytics using the OWOX BI Pipeline. Total expenses are analyzed in another system that’s more suitable for the purposes of the company (in addition to advertising costs, it also collects fixed costs for the production of promotional materials, agency commissions, and other expenses, without which it’s impossible to calculate the net ROI).
Set up automatic uploading of expenses to Google Analytics for Criteo, Facebook, and other advertising sources through OWOX BI Pipeline. You can find methods of loading advertising costs in Google Analytics and the advantage of automatic import to Google Analytics and BigQuery in our article.
Downloaded data from the CRM to Google BigQuery. While exploring the advantages of BigQuery and other Google Cloud Platform products, the marketing team decided to try building ROPO reports based on one-time uploads. To do this, they uploaded data once a month to Google Cloud Storage, from which it was sent to BigQuery.
After the marketing team got acquainted with all the advantages of working with data in BigQuery, they decided to continue using BigQuery as a CRM data store. Using the BigQuery integration from OWOX BI, they set up automatic uploading of data from their CRM to BigQuery. This integration allowed to independently manage (add, delete, and update) data in the cloud, which was one of the advantages of using this approach on an ongoing basis.

Step 3. Build reports for company management

Using SQL queries, the marketing team merged all data collected in BigQuery into a single table. Now they can use this data to build reports in a company-friendly format using the Data Studio data visualization tool.

It’s worth considering that data for building reports can be merged not only in visualization services like Data Studio, Tableau, and Google Charts. Using the instructions developed by the OWOX team, in a few clicks, you can connect a table created in BigQuery directly to the OWOX BI Attribution and OWOX BI Smart Data tools to automatically generate ROPO reports in the OWOX BI office.

Results

As a result of building a system for marketing omnichannel analytics, the marketing team answered a number of questions important for business development.

Having built the entire chain of user touchpoints, from interacting with online advertising to buying in an offline store over a selected period, it was possible to identify 3 percent of all users who entered the website by User ID — that is, only those visitors who are registered on the website. It was possible to identify not only sessions of users authorized at a specific time but also sessions of unauthorized users who have a known loyalty card. Among identified users:

31% made purchases in the online store.
17% visited the online store before an offline purchase (ROPO users).
53% made purchases in both online and offline stores.

After analyzing all the necessary data, the digital team could show in figures the influence of digital media advertising on sales in offline stores.The graph below shows the influence on offline sales of an email newsletter sent on August 23–25.

The report also allows the team to assess changes in the share of ROPO users to understand how far the expectations for this metric correspond to actual data.

This graph shows what percentage of ROPO revenue was generated by a specific advertising campaign.

When building the graphs below, it was possible to visually show the share of ROPO users who made purchases both online and offline and to track the dynamics of changes in this category of users.

In addition to the main analysis, the marketers wanted to find out how the behavior of ROPO users differs by product category in terms of what customers look at on the website and what they buy offline.

For example, there was a hypothesis that perfume brings the most ROPO revenue. However, the report showed that in fact, the facial care category in the anti-aging products series has a large ROPO share. These products can be considered when developing the next online advertising campaign.

All this data and the correct analysis of the ROPO effect helped to clearly define and present the effectiveness of online advertising, taking into account all actions of users both online and offline. Also, thanks to ROPO analysis and the ability to download offline transactions into a separate Google Analytics view, marketers can better understand the behavior of different segments of their target audience, allowing them to plan marketing activities in more detail.

The next step is to increase the percentage of identified users in order to more accurately determine the behavior of each segment. Part of this task was solved by identifying users not only who are authorized at a given time in a particular session but who are not authorized but have a loyalty card that can be matched retroactively.