ROPO analysis: How is an omnichannel end-to-end analytics useful? Ile de Beaute case study
About Ile de Beaute
The Ile de Beaute 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.
More than 30,000 items from leading perfume, decorative cosmetics, and body care brands are presented in 127 Ile de Beaute stores. The online store, iledebeaute.ru, provides customers with quick access to the entire catalog and allows customers to order goods anywhere in Russia.
Customers can generally buy the goods offered by Ile de Beaute 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 Ile de Beaute 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 are not 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 end-to-end analytics and reports.
- The priority for Ile de Beaute, as for many large omnichannel retailers, was to build an effective end-to-end analytics system across all sales channels.
- The first problem faced by the Ile de Beaute 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 delivery of data from various sources to a single repository for further analysis. There are quite a few tools for this, but Ile de Beaute needed to choose the best in terms of price, quality, functionality, flexibility, and scalability.
To solve problems with ROPO analysis, Ile de Beaute 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 Ile de Beaute team turned to OWOX BI, since we’re experts in online analytics and data fusion and have been a partner of Ile de Beaute since 2016.
Step 1. Choose a single repository for merging data
Ile de Beaute 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
To automate the flow of data, Ile de Beaute analysts, following the recommendations of OWOX BI, did the following:
- Set up automatic data export from Google Analytics to Google BigQuery. Ile de Beaute is a user of Google Analytics 360, which allows them 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 Ile de Beaute (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 Yandex.Direct, Yandex.Market, VKontakte, 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 their CRM to Google BigQuery. While exploring the advantages of BigQuery and other Google Cloud Platform products, the Ile de Beaute 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 Ile de Beaute 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 the Ile de Beaute team 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 Ile de Beaute team combined 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 combined 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 BI Attribution and BI Smart Data tools to automatically generate ROPO reports in the OWOX BI office.
As a result of building a system for end-to-end omnichannel analytics, the Ile de Beaute team answered a number of questions important for business development.
- Having built the entire chain of user touch points, from interacting with online advertising to buying in an offline store over a selected period, we were able to identify 3 percent of all users who entered the site by User ID — that is, only those visitors who are registered on the site. We were able 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 at Ile de Beaute was able to 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 August 23–25.
- The report also allows the Ile de Beaute team to assess changes in the share of ROPO users to understand how far the expectations for this indicator correspond to actual data.
- This graph shows what percentage of ROPO revenue was generated by a specific advertising campaign.
- When building the graphs below, we were able 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, Ile de Beaute marketers wanted to find out how the behavior of ROPO users differs by product category in terms of what customers look at on the site and what they buy offline.
For example, they had 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 the Ile de Beaute team 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 at Ile de Beaute 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.