Darjeeling's Success Story: ROPO Analysis Proves that 40% of Customers Visit the Company's Website before Buying Offline

Solution

To achieve their goals, Darjeeling’s marketing specialists decided to take the following steps:

1. Collect the data about online sessions, offline sales and order completion rates.
2. Bring together the data about offline sales, and user behavior on the website, while considering order completion rates.
3. Create reports and dashboards based on the collected data, to evaluate the impact of online advertising on offline sales.

Data flow chart:

Step 1. Bring all the data together

Darjeeling imports the data about web user behavior to Google BigQuery using OWOX BI Pipeline.

Darjeeling’s marketers send the transaction data from their CRM system to Google BigQuery. This helps take account of the order completion rates and avoid possible data losses, such as the ones that happened because JavaScript wasn’t loaded in the browser.

The marketers import the data about the completed orders to Google Cloud Storage on a daily basis. OWOX BI analysts created a script to help Darjeeling collect the data from Google Cloud Platform and import it to Google BigQuery. In the near future Darjeeling is going to start using the Data Prep service. This will help Darjeeling import data from Google Cloud Platform to other data warehouses such as BigQuery without resorting to the script.

Step 2. Combine the data about offline orders and web user behavior

OWOX BI analysts helped Darjeeling merge the data about online sessions and order completion rates in a single table. To do that, they used the user_id. It’s a unique identifier assigned to each user who has signed in on the company’s website. The value of the user_id is linked to the number of the customer’s loyalty card and stored in the CRM system. When a user visits the website, the customer’s user id is sent to Google Analytics and Google BigQuery as a custom dimension value. The data is also combined by the two other keys: the transaction_id and the time, a particular date selected to analyze the transactions prior to the date. Let’s have a closer look at each step.

OWOX BI analysts started with creating a table in Google BigQuery to store the data about all the completed orders.

Here’s the table structure:

Next, the analysts combined the data about offline purchases with the data about online sessions, and identified the traffic channels for each session. The analysts decided to only take account of the specific information: the data about offline purchases that were made after the latest customer’s visit on the website.

This is how the data was merged:

1. The analysts used the following keys from the table: transaction_id, user_id, and time.
2. Next, they selected the data about all online user interactions before the selected date, taking account of the order completion rates.
3. Finally, they identified the channel groupings for the sessions that were closest in time to the transaction date.

As a result, Darjeeling obtained the table with the following results:

According to Darjeeling’s research of their customers’ shopping journey, it takes the customers up to 90 days to make a purchase decision after visiting the website. Using this data, the OWOX BI analysts calculated the number of days between the website visit and the purchase for each of the orders. The results were grouped into segments of 7, 10, 14, 30 and 60+ days. As a result, it turned out that 85% of all ROPO purchases are made within 14 days.

‘‘

It was interesting to work on this task, as the majority of the job was done in Google BigQuery and we brought together the data from different systems. We got great results that our clients can use in their work.

Anastasia Chausova,

Digital Analyst at OWOX BI

Step 3. Create reports and dashboards

Darjeeling chose Google Data Studio and Google Sheets to visualize the results. The OWOX BI analysts created interactive dashboards with a system of filters. Darjeeling can also export the data from the dashboards for further analysis and marketing budget planning.

The chart below demonstrates that the number of online purchases varies from month to month. With the cost data in Google BigQuery, Darjeeling can calculate the ROI for each of the online channels. The cost data can be displayed for each product type, conversion time and city.

The chart above also demonstrates that the percentage of ROPO purchases varies from 5% to 15%, from month to month. Bearing in mind the data about ROPO purchases, the real contribution of online channels is 7 to 19% of the total sales, instead of the earlier estimated 3-6%. For Darjeeling, this means that online advertising drives both online and offline sales, performing much better than previously estimated.

The next two charts show the monthly changes in the Average Revenue Per Order and the Average Revenue Per User, with account of the purchase type.

As you can see, Web and ROPO purchases demonstrate the largest Average Revenue, both per User and per Order. This means that customers tend to buy more if they visited the company’s website beforehand.

The revenues from all the online channels, including the data about ROPO purchases, are shown in the table below.

Darjeeling used to estimate the revenue from online sources as 8,400 euros for the period in review, taking into account only Web and Click & Collect types of purchases. The only channel grouping available for the analysis used to be SEO. However, having included the data about ROPO purchases, the estimated revenue from all of the online sources now constitutes 100,700 euros monthly, which is 90% more revenue. These results mean that the ROI and ROAS are way higher for the SEO channel grouping.

Results

• Darjeeling’s VP of e-marketing, Acquisition & CRM demonstrated the company’s decision makers how online channels contribute to the offline sales. This information helped Darjeeling reconsider the development strategy.
• Darjeeling can receive the information about the percentage of ROPO sales from informative dashboards, and see that the real revenue from online channels is 7 to 19% instead of the 3 to 6% estimated before.
• Thanks to the retrospective data update in OWOX BI Pipeline, Darjeeling’s marketers identified more users and came to realize that 30 to 40% of customers visit the company’s website before buying offline.
• The obtained data helps the company optimize their ad budget resources and invest more in online advertising.

‘‘

We’ve been working with OWOX BI on ROPO analysis for the past 6 months. By reconciling data from our CRM systems and behavioral online raw data into Google BigQuery, we were able to better understand our customers’ journeys. We can now manage our digital marketing investments by taking into account the offline sales and provide greater insights to our CEO. Great job, OWOX BI team!

Christophe Lemaire,

VP of e-marketing, Acquisition & CRM at Chantelle Group

P.S. We’re always happy to answer your questions. Feel free to ask them in the comment section below.

P.P.S. We’ve added ROPO reports to OWOX BI Smart Data for more convenience.

TRY ROPO REPORTS