Darjeeling’s Success Story: ROPO Analysis Proves that 40% of Customers Visit the Company’s Website before Buying Offline
Darjeeling is a recognized brand and top retailer in the women’s lingerie market, founded by Groupe Chantelle in 1995.
Presently, there are 155 Darjeeling chain stores all over France, and more than 8.7 million visitors come to the stores every year. The company sells over 5 million items a year, with an annual turnover of 100 million euros.
Darjeeling used to only take account of purchases made directly on the website, when estimating the turnover by ad channel. With this approach to calculation, the estimated number of online sales constituted 6% of the total number of sales, while offline sales constituted the other 94%. However, the company didn’t take into account the fact that many customers research online before buying in a physical store. This means that people visit Darjeeling’s website to browse women’s lingerie and then make a purchase in a physical store. The customers’ buying decision may also be influenced by customer generated content such as reviews, forum posts, etc. Bearing this information in mind, the OWOX BI team took into the account the following purchase scenarios:
- Click & Collect — customers purchase online but collect their purchases in a retail store.
- E-Reservation — customers place their orders online and purchase in a retail store.
- Shop Only — customers research and purchase products in a retail store.
- Web — customers purchase products on the website.
Darjeeling’s marketers were looking to delve into the online-to-store user experiences when evaluating the performance of online advertising. To do this, they needed to include the data about offline purchases in their calculations.
Darjeeling uses different systems to collect, store and process data. The user behavior data is sent to Google Analytics, and the data about costs and order completion rates is collected in the company’s CRM system. The data in the system is in French, while the data collected in Google Analytics is in English. The data structure is also different between the two systems. To evaluate the overall effect of online advertising on offline sales, Darjeeling needed to combine all the data into a single system. The OWOX BI team faced a similar challenge when working with M.video, a Russian retailer of consumer electronics and home appliances. Check out our blogpost to learn more about it.
To achieve their goals, Darjeeling’s marketing specialists decided to take the following steps:
- Collect the data about online sessions, offline sales and order completion rates.
- Bring together the data about offline sales, and user behavior on the website, while considering order completion rates.
- 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.
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:
- The analysts used the following keys from the table: transaction_id, user_id, and time.
- Next, they selected the data about all online user interactions before the selected date, taking account of the order completion rates.
- 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.
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.
- 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!
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.