How to measure the ROPO effect for an omnichannel retailer

All businesses want to find out the real value of their advertising channels and it’s quite impossible to achieve without tracking the relationship between online and offline customer behavior. For example, by disabling advertising that, at first glance, doesn’t pay off, the company risks reducing its sales.

In this case, we describe the solution provided by the OWOX BI team for a large consumer electronics and home appliances retail chain that had challenges with measuring the ROPO effect.

The customers of omnichannel retailers typically purchase products in three unique ways:

• Ordering solely online at websites. The revenue from the website accounts for about 20% of the company's total turnover.
• Purchasing offline in the company’s chain stores.
• Finding products on the website and buying them in offline stores. This behavior is known as ROPO Effect — research online and purchase offline.

The marketers were looking to evaluate the impact online channels have on offline sales. This would allow them to more accurately calculate the return on advertising investments and build a better marketing strategy based on the complete data. Another task was to improve the online experience of the customers, by discovering the reasons why they choose to buy in offline stores after visiting the website. To achieve these two goals, it was decided to integrate the data about online and offline touchpoints of the signed-in users (about 12% of the total number of website visitors).

The company collects, stores and processes all the data in different systems:

• The data about user interactions with the website is collected in Google Analytics 360.
• The data about offline purchases and order returns is collected in the company’s CRM system (SAP). The structure and the collection algorithm of this data is completely different from Google Analytics.

To analyze the impact of online channels on the company’s total revenue, the marketers needed to have all the data merged into a single system. Google Analytics doesn’t suit the task, as it doesn’t support data reprocessing: once processed, the data cannot be modified if an order is canceled or returned for any reason. Moreover, importing all the data about offline transactions of users who have never visited the website, would significantly distort the accuracy of the Google Analytics statistics. Google Analytics may also fail to track some of the purchase data on website pages because the JavaScript wasn’t loaded in the browser.

In order to achieve their goals, the marketers decided to take the following steps:

1. Bring together the data about user interactions with the website, offline purchases, and order completion rates.
2. Merge the data about offline purchases with the data about online sessions.
3. Visualize the data for in-depth analysis.

The flowchart of this entire process is given below:

The data about all user actions and orders placed on the website is sent to the Google BigQuery cloud data warehouse, using native integration available to Google Analytics 360 accounts. Therefore, the company’s specialists decided to use Google BigQuery to collect all other data.

To transfer the data about offline purchases and order completions from the CRM to Google BigQuery, the specialists set up automatic daily data uploads via FTP.

OWOX BI analysts merged and processed the collected data. First, the data about online orders was supplemented with the statuses of each order, using an SQL query. The query merges the data based on matching values from two tables, with the ID of the transaction (Order ID) used as the key.

Next, the analysts merged the data about offline purchases and website behavior of the same customers. For this purpose, they used the User ID in Google Analytics. A User ID is a unique identifier assigned to each user who has signed in on the company’s website. Then, User IDs are associated with the customer loyalty cards in the CRM system and sent as custom dimension values to Google Analytics. The time period for data integration was set to 180 days while considering the time period from the website visit to the purchase. In this way, more granular audience segmentation was possible.

As a result, the following data about each of the orders (both online and offline) was received:

The OWOX BI team visualized the data in Google Data Studio by creating an informative dashboard. The company can export the data from the dashboard for a more detailed analysis and budget planning.

For example, the interactive bar chart, the screenshot of which is given below, shows the number of online, offline, and ROPO purchases along with the revenue obtained from them. This data can be filtered by city, time period, and product type. ROPO purchases constitute about 10% of the total revenue, depending on the city. The chart also demonstrates that the percentage of orders from each channel does not coincide with the percentage of revenue obtained from the channel — it depends on the average order value. In this case, online purchases have a higher average order value than offline purchases.

The table below demonstrates the additional revenue from ROPO purchases across different regions, channels, and product categories. The data can be exported in a tabular format and is used by the company in distributing the advertising budget.

• The informative and automated dashboard was obtained, allowing taking into account the ROPO effect in the operational planning of advertising campaigns.
• The company found out that online channels contributed to about 10% of offline revenue.
• By analyzing the behavior of users who research products on the website before buying them in the company’s chain stores, it’s now possible to discover the reasons why these customers choose to shop offline. The company can now revamp the website for a better user experience and higher conversion rates. As an example, the company discovered that most offline customers who had visited the website used discount coupons when purchasing offline. Armed with this information, the marketers have already improved the customer experience with discount coupons on the website. In addition, the company simplified the online credit application form so that customers don’t have to go to a brick-and-mortar store to buy on credit.