What is ROPO analysis? Why you need it and how to conduct it

Imagine that you did a great job but you got 40 percent less for it than you expected. Just because you couldn’t prove the result of your efforts. Roughly the same thing happens to omnichannel internet marketers who don’t conduct ROPO analysis. But the undervalued online marketing in this case is just half of the problem. Companies that don’t track the relationship between online and offline customer behavior risk reducing their sales, for example by disabling advertising that, at first glance, doesn’t pay off.

In this article, we’ll explain what ROPO analysis is and what advantages it can give to your business. We’ll tell you how to connect users’ behavior on the site with purchases at retail stores using OWOX BI what data is needed for ROPO reports, and how to build them.

Let’s start with the theory. ROPO (research online, purchase offline) describes the behavior of customers who look for products on the internet and buy them in a physical store. There are several reasons why customers do this:

In the Consumer Barometer, you can also sort data by product type — such as electronics or consumer goods — to get data relevant for your business.

How you should use the results of ROPO analysis depends on the goals you want to achieve. Do you want to find out the real effectiveness of your online channels, improve advertising campaigns and save money, increase sales online or offline, or increase profits? Let’s consider each of these goals in more detail.

So you’ve made sure that a sufficiently large percentage of users who study goods on your site go buy them in retail stores. Because of this, it’s difficult to calculate the exact conversion and payback of online advertising, which could lead to poor decisions.

If a product sells poorly online, don’t rush to remove it from the site or turn off the advertising campaign. It may turn out that sales of this product offline are three times higher than online. Compare the activity of website visitors with purchases in physical stores and you’ll find out whether you’re wasting your money or sitting on a gold mine.

When you determine the share of ROPO sales, you can go ahead and calculate the ROAS of any advertising channel or campaign based on offline purchases. This may show a completely different picture.

Only by studying the customer journey can you make advertising campaigns as effective as possible. Suppose you’ve configured an automated email for abandoned shopping carts. Some customers may use the shopping cart as a shopping list and then go and shop in a physical store. By identifying such users, you can exclude them from these reminder emails and save money.

People who look at your site before they purchase tend to spend more than those who don’t. Having determined which products on your site sell well according to the ROPO principle, you can focus on promoting them and thereby increase your profits.

If you conduct ROPO analysis and find that your customers prefer to buy certain products in retail stores, you should think about what’s wrong with your website. Why don’t customers — or why can’t customers — buy online, instead going to your store (hopefully) or even to your competitors. There may be various reasons for this behavior:

• Prices online and offline are different.
• Some functionality isn’t available on the site, for example it’s impossible to pay in installments.
• Delivery takes too long.
• There’s a complex interface or a long payment process.

After examining the behavior of your users in detail, you’ll be able to understand what prevents them from making purchases on the site and fix it by:

• offering discounts in the online store 
• offering an additional warranty or free or fast delivery
• providing convenient payment methods
• adding a section with frequently asked questions
• providing information on how to contact you.

In short, you can adapt your website to the customer’s custom path.

Conceptually, there are two ways to assess the impact of online advertising on offline sales. The first is based on identifying specific users and linking the actions of visitors on the site with purchases in a physical store. It’s clear that for this to happen, users must be authorized.

The second solution is so-called impersonal data fusion. You can use impersonal data fusion to assess the impact of television advertising on offline sales or in the event that your site doesn’t let users register. Similar tasks are for those who advertise products sold on Amazon or other marketplaces on Facebook. In such cases, it’s impossible to associate conversions from advertisements and banner views with orders for particular users. This problem is solved using indirect correlation. This is a topic for a separate article. If you would be interested in an article on indirect correlation, let us know in the comments.

In this article, we’ll focus on identifying specific users, which is a priority for any business. Doing so provides an estimate of the effectiveness of online advertising based on a combination of user actions both online and offline.

The main stages of identifying users:

1. Combine online data with transaction data from your CRM.
2. Identify a segment of ROPO purchases and understand the share of these purchases in relation to online and offline sales.
3. Build dashboards for high-level monitoring of data and its dynamics.
4. Build detailed tables for agencies so they can use ROPO data in their media planning and track results.
5. Find answers to these questions: Do ROPO buyers order through the website? What prevents them from initially placing an order on the site? How can you save on remarketing to these customers?

We recommend merging data from your website, data on advertising campaign costs, and data from your CRM in Google BigQuery. To do this, you can use Power BI, Google Data Studio, Amazon Redshift, or some other tool. A small business can even export data to Google Sheets. However, you should understand that automated data processing can fail for unobvious and unexpected reasons. Therefore, you need to use a mechanism that works reliably and doesn’t distract you from looking for insights and applying data.

Google BigQuery is good because, like any SaaS, it doesn’t require investment in hardware and makes it easy to combine data, regardless of the quantity. This means that as your data volume grows you won’t need to change all your requests and settings. And if you don’t have much data, then you won’t pay much to process it.
In addition, BigQuery supports data reprocessing. If a customer makes an order offline and then goes to the site and logs in, you can retroactively combine all their actions. That’s not possible with Google Analytics.

You can export user behavior data from Google Analytics to Google BigQuery using standard export. For those who use the free version of Google Analytics, we recommend OWOX BI Pipeline to collect raw unsampled data from your site in BigQuery. You can get acquainted with the structure of this data in our help center.

It’s necessary to verify the source data. You must have configured and transferred from the site to the Google Analytics User ID (or another common user ID). Since the data will be connected precisely by this ID, it’s important that it’s present in both tables and is correct. That is, for each authorized user, the User ID should be sent to both the Custom Dimension and the & uid parameter.

Make sure the same ID isn’t used by multiple users. Check the percentage of users with a User ID and the values themselves over time to see if there are any anomalies. For this purpose, it’s possible to build a segment of users with IDs in Google Analytics and look at how this segment correlates with the total number of users.

Also check for anomalies in data on the number of sessions, users, transactions, and income by day and by traffic source. The easiest way to do this is in the GA interface. You can take a time interval of several months and see standard Audience Overview, All Traffic, Source/Medium, Ecommerce Overview, and other reports. Check if these reports have unreasonable peaks or failures.

You can import orders from your CRM to Google BigQuery one time or set up automatic data uploading to regularly calculate the share of ROPO purchases.

The table being uploaded from your CRM should include at least:

• Date of the transaction
• Transaction ID
• Order price
• User ID

Additional fields that will allow you to get reports in additional sections:

• City
• Item ID
• Number of items in the order
• Price of each item
• Payment type
• Delivery type
• Order status

With CRM data, pay attention to:

• Availability of all required fields
• Availability of data for all days
• Comparable format for date, income, and city
• Presence of the User ID parameter in all transactions
• Duplicate transactions

If you want to not only find out the share of ROPO purchases but also calculate their ROAS, you’ll need cost data from your advertising sources. You can import your expenses from different services into Google Analytics, then upload them to Google BigQuery as a single stream using OWOX BI Pipeline.

All your data can be combined according to this scheme:

OWOX BI offers a free trial period. During this time, you can set up data collection in your Google BigQuery project to create reports on ROPO and other marketing indicators.

After you collect all the data in Google BigQuery, you need to link it. As a key, you can use the User ID. This is a unique identifier that you assign to each user in your database and associate with the user’s email address or loyalty card. When users enter your website and log in — for example, enter a personal account — their User ID is transferred to Google Analytics (provided that you have this function configured in GA).

In this way, users are linked to their actions on the site.

In order to recognize as many of your users as possible, you can offer bonuses for logging in: discounts for logging in, useful downloadable materials, promotions, etc. You can find more ways to motivate your users in our article «Why and How to Integrate Online and Offline Customer Touchpoints»

You can also use the special huid parameter in links you send to your customers by email. In this parameter, you can write the User ID value from your CRM system. This will help you identify a user even if they haven’t logged in on the site. For example, say you already have a customer in your CRM with an email and a unique User ID. You send an email to this customer with a link that includes the ID. The customer clicks on the link and goes to your website and performs some actions on the site without registering. Through Google Tag Manager, you can transfer this identifier to Google Analytics in the User ID or Custom Dimensions field.

If the user leaves a request on your site and then comes to the store and makes a purchase, you can link their actions using the transaction ID.

In the diagram above, you can see data on user behavior on the site (left) and data on the purchase from the CRM (right).

If you’re interested in the technical details of data integration and ROPO analysis, read some of our customers’ success stories:

• Darjeeling’s Success Story: ROPO Analysis Proves that 40% of Customers Visit the Company’s Website before Buying Offline​
• Fabelio found out what online customers are doing in their retail stores
• How to identify ROPO: online influence on offline purchases
• ROPO analysis: How useful is it for omnichannel marketing analytics
• How to measure the ROPO effect for an omnichannel retailer

Our clients are often interested in ROPO analysis. Therefore, we’ve added a separate block of ROPO reports to OWOX BI Smart Data. With this service, you can ask questions of your data in Russian or English using natural language. The service will process the request and give you a clear answer along with visuals.

To receive ROPO reports, you need to upload data on user behavior from Google Analytics and offline order data from your CRM to your Google BigQuery project. Then connect your project to OWOX BI Smart Data. You can try OWOX BI for free right now to see how it works.

To receive ROPO reports in Smart Data, you need data on user behavior from Google Analytics and data on offline orders from your CRM.

In Smart Data, you can change all highlighted fields for a question by selecting options from the drop-down list: share of income, transactions of users, amount of income, average check size, etc. You can learn more about the available metrics in the help section.

All information from the report can be uploaded to Google Data Studio or downloaded as a CSV file. You can also copy the SQL query and refine it in Google BigQuery as needed.

Consider a few examples of reports.

The 30-day conversion window means that a maximum of 30 days have passed between the user last visiting the site and making an offline purchase. The conversion window can be changed to suit your business.

This report shows how revenue is distributed between online, offline, and ROPO sales. It helps to separate ROPO orders from regular offline purchases and to understand the real role of the ROPO effect in your multi-channel sales.

This is an analog of the Time to Conversion report (Time Lag) in Google Analytics, only here online actions and offline purchases are related. This report shows what percentage or number of transactions, customers, and revenue fall on each day within the conversion window.

This will help you understand the real conversion window for ROPO purchases as well as track the relationship between the transaction value and the number of days users need to make a purchasing decision.

With this report, you can determine which online channels, sources, and ad campaigns lead to offline purchases.

You may ask, how accurate are the results of ROPO analysis if only users registered on the site are considered?" This is a good question, and in order to answer it, you need to understand how many users there are. It’s difficult to get an exact answer for a specific site. If a user isn’t logged in, we don’t know for sure whether he was on the website. Unless it’s possible to use panel research.

In our experience, the share of authorized users who can be associated with purchases offline, even for the average omnichannel consumer electronics retailer, reaches 40 percent. This result is cumulative throughout the year. During seasonal sales, it increases. The main thing is that even without a 100 percent audience pool, you can get a representative sample for budget redistribution. This means that you don’t need to combine the actions of each user; you only need to combine the data of a sufficient number of users to build reports.