How to increase email-driven revenue by 237% and optimize ad spend

The well-optimized email process can provide businesses with the highest possible ROI over this sales channel. However, you have to adjust email marketing campaigns to achieve it: the content should be personalized for your audience. Otherwise, there’s a good chance your email efforts are putting money down the drain.

In this case, we describe the solution provided by the OWOX BI team for a large retail chain of home appliances, electronics, and household goods, that has more than 600 stores in more than 200 cities. The business had challenges with finding out how to improve its conversion rate and optimize advertising costs by applying triggered emails.

Goals

In a highly competitive business environment, the main aim is to acquire new customers, increase LTV and optimize the advertising budget. Business analysts wanted to analyze the efficiency of their customer acquisition channels considering the resulting gain from each of the advertising campaigns. Standard attribution models weren’t appropriate for this task, since they ignore the following factors:

1. 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.
2. 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, the analyst team developed their own LTV-based attribution model that considers advertising costs and margin from the purchases over a period of 180 days. Furthermore, the ways to turn potential buyers, who had an interest in a certain product or product category, into loyal customers were testing. 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). It was decided to set up triggered emails to bring such visitors back to the website and motivate them into making a purchase:

1. Reminders of the abandoned carts, to users who started but haven't finished the checkout.
2. Recommendations are 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. There should be no delays in collecting data about the actions of website visitors to ensure this.

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 merge the data, but also to ensure quick access to it, and near-real time data processing.

Solution

The company had to solve the following tasks to achieve the goals:

• 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:

1. Google Analytics: the data from the website is available in Google Analytics via a Google Tag Manager container.
2. Advertising sources: Google AdWords, Criteo.

As single data storage, it was decided to use Google BigQuery:

1. This cloud storage allows for the collecting and rapid processing of massive amounts of data.
2. It provides a large set of ready-made solutions and tools for integration with CRM and ERP systems.
3. The data is securely protected.

The following tools and features were used to collect all the data in Google BigQuery:

1. Google BigQuery Export for Google Analytics 360 — to receive data from Google Analytics in Google BigQuery, including 13 months of historical data.
2. OWOX BI Pipeline — the company receives near real-time hit data from the website in Google BigQuery along with the data about costs, clicks, and impressions from advertising sources, which 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 Cube

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, allows for significant reducing of the data processing cost.

The flowchart below shows how the necessary data is collected and merged:

Implementing new triggered emails

The OWOX BI analysts developed two views using SQL queries to set up triggered emails. 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 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 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, more accurate user data was obtained, and personalized triggered emails were sent to a greater number of visitors.

Creating the attribution model

The team developed its 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 costs.

Results

As a result of collecting data in single data storage, setting up new triggered emails and historical data updates, these goals were achieved:

1. Improve triggered email 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.

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%.

2. Optimize the advertising budget and reduce the share of advertising cost

The graphs below show data about customers’ LTV per session (FM — margin, COST — costs) according to the enterprise’s own attribution model that considers the lifetime value of the customers who visited different product groups via paid traffic channels. This value increases over time — a fact that 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, at first users came through CPC channels, then 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.

‍