Eldorado: How to Increase Email-Driven Revenue by 237% and Optimize Ad Spend
Find out how timely emails and their own attribution model helped Eldorado improve their conversion rate and optimize advertising costs.
Eldorado, the Russian retail chain of home appliances, electronics and household goods, has more than 600 stores in more than 200 cities in the Russian Federation. At the same time, Eldorado is actively developing transnational franchising in Kyrgyzstan, Moldova, Armenia and Kazakhstan.
OWOX provides analytics services for multi-channel businesses, helps implement Google Analytics 360 Suite in Ecommerce projects and develops online services based on Google Cloud Platform. OWOX BI, the company’s own service, includes three products. OWOX BI Pipeline helps combine data from Google Analytics and a number of other services in Google BigQuery, and automates cost data import to Google Analytics. OWOX BI Attribution allows for evaluating advertising campaigns considering their impact on the conversion process. OWOX BI Smart Data helps get instant answers to questions of the data without involving any additional resources. OWOX BI products are used in 9000+ projects in 90+ countries worldwide. OWOX is a certified partner of Google Analytics and Google Cloud Platform, being the first authorized Google Analytics 360 reseller in the EMEA region.
In a highly competitive business environment, Eldorado aims to acquire new customers, increase LTV and optimize advertising budget. Business analysts at Eldorado wanted to analyze the efficiency of their customer acquisition channels considering the resulting gain from each of the advertising campaigns. Standard attribution models were not appropriate for this task, since they ignore the following factors:
- Visitors to price comparison websites seek to buy products at the lowest price. This means that, while considering their next purchase, the customers may want to choose by the price again, and rather than visiting the company’s online store, they may revisit a price comparison website. In evaluating this traffic source, one should consider the customer’s LTV over 180 days and the total cost of acquisition and retention, as the company may spend money each time they bring customers back via price comparison websites.
- Customers who have bought a cheaper product with a low margin may return to the website later to buy more expensive products. Thus, though the margin of the cheaper product may not cover the acquisition cost, eventually (over 180 days) such advertising campaigns pay off better.
In view of the above, Eldorado analysts have developed their own CLTV Customer Lifetime Value — sum of the total income generated by a customer during the period in question -based attribution model that considers advertising costs and margin from the purchases over a period of 180 days. Furthermore, Eldorado was looking for ways to turn potential buyers, who had interest in a certain product or product category, into loyal customers. Some of the visitors browsed the website, but haven’t bought any products. Some of the visitors started the checkout but didn’t finish the process (“abandoned carts”). To bring such visitors back to the website and motivate them into making a purchase, it was decided to set up triggered emails:
- Reminders of the abandoned carts, to users who started but haven't finished the checkout.
- Recommendations based on the viewed products, to users who browsed certain types of pages (catalog pages or product pages).
The content of the triggered emails should be personalized to meet the current needs of each customer. To ensure this, there should be no delays in collecting data about the actions of website visitors.
The data required to achieve these goals was stored in different systems — Google Analytics and a number of advertising services. It was necessary not only to combine the data, but also to ensure quick access to, and near real-time processing of the data.
To achieve the goals, the company had to solve the following tasks:
- Collect data from multiple sources in a single system and optimal structure.
- Obtain all the data required for the attribution model, and ensure that the data can be historically updated.
It was decided to check the hypotheses within a period of 180 days.
The data was obtained from the following systems:
- Google Analytics — the data from the website is available in Google Analytics via a Google Tag Manager container.
- Advertising sources: Google AdWords, Yandex.Direct, Yandex.Market, and Criteo.
As a single data storage, it was decided to use Google BigQuery:
- This cloud storage allows for collecting and rapid processing of massive amounts of data.
- It provides a large set of ready-made solutions and tools for integration with CRM and ERP systems.
- Eldorado, being Google Analytics 360 clients, use Google BigQuery Export and receive $500 to spend on data processing every month.
- The data is securely protected.
The following tools and features were used to collect all the data in Google BigQuery:
- Google BigQuery Export for Google Analytics 360 — in this way, Eldorado receives data from Google Analytics in Google BigQuery, including 13 months of historical data.
- OWOX BI Pipeline Google Analytics to Google BigQuery the company receives near real-time hit data from the website in Google BigQuery.
- OWOX BI Pipeline Cost Data Import once set up, the data about costs, clicks and impressions from advertising sources, is available in Google Analytics, and then in Google BigQuery.
As a result, all the necessary data is collected in Google BigQuery, and then sent to the company’s internal analytics systems.
Data obtained in Google BigQuery via the standard Export for Google Analytics 360 and via OWOX BI Pipeline, was organized into a data cube. Such a cube, created in an optimal structure for Eldorado, allowed for significantly reducing the cost of data processing.
The flowchart below shows how the necessary data is collected and combined:
Implementing new triggered emails
To set up triggered emails, OWOX analysts have developed two views Virtual tables in Google BigQuery, containing the results of SQL-queries using SQL-queries. The queries aggregate data about user actions on the website, obtained via OWOX BI Pipeline, and data obtained via Google BigQuery Export, into an optimal structure for the task.
The efficiency of the emails was measured with A/B testing. Triggered emails to the first group of users were sent on the next day after visiting the website. The other group received triggered emails in just under an hour, as the emails were sent off using the data from the tables in Google BigQuery and the data cube. In both cases, the emails were sent via the same service.
Unlike Google Analytics, the userID User ID from the Eldorado's internal database field value in pivot tables in Google BigQuery can be retroactively updated by overwriting the tables. Once a user is authenticated on the website on any device (once the userID value is known), the previous sessions of the same user also acquire the userID value, if this value wasn’t specified or defined before. Due to the retroactive updates to the userID field, Eldorado is able to obtain more accurate user data, and send personalized triggered emails to a greater number of visitors.
Creating the attribution model
Eldorado analysts have developed their own attribution model with the following features:
- Distributing visitor acquisition costs over visits only from paid traffic sources.
- Evaluating total margin per user for 30, 60, 90 and 180 days.
- Considering only data from first-time buyers in the calculations. On a repeat purchase, the value of the order is assigned to the channel that brought the user to the website for the first time.
This attribution model was made possible due to retroactive data updates in Google BigQuery, including updates to the UserID field and to the data about visitor acquisition cost.
As a result of collecting data in a single data storage, setting up new triggered emails and historical data updates, Eldorado was able to achieve their goals:
- Improve triggered emails KPIs.
Email notifications about abandoned products are sent within an hour to most users who added products to their shopping cart, but haven’t completed the purchase. The data for triggered emails is collected automatically, twice per hour.
CTDR Conversion Increase in purchases resulting from emails Increase in turnover resulting from emails Standard triggered emails 21% 4,9% 13% 8% Triggered emails with the data obtained via OWOX BI Pipeline, and the data cube 37% 5,5% 24% 27% Increase by 76% 12% 85% 237%
The efficiency of the new mailing method was confirmed by A/B testing:
- Number of visits to the website through delivered emails increased by 76%.
- Conversion rate for purchases from users who visited the website through delivered emails increased by 12%.
- Number of purchases after “abandoned cart” email optimization increased by 85%.
- Share of turnover in the target group increased by 237%.
The graphs below show data about customers’ LTV per session (FM — margin, COST — costs) according to Eldorado’s own attribution model that considers lifetime value of the customers who were attracted to different product groups via paid traffic channels. This value increases over time — a fact which is ignored by the Last-Click Attribution model. The maximum bet for each campaign is limited to the margin obtained by the company for 180 days, excluding customer retention costs.
As can be seen with the CLTV-based attribution model over the period of 180 days, users first attracted through CPC channels, returned and made repeat purchases through free channels. The value of such CPC channels turned out to be several times higher than according to the Last Click attribution model. The volume and frequency of repeat purchases, as well as the company’s revenue, were different for each product category, advertising campaign and source.