Hoff and OWOX BI: How to Discover 2.4 Times More Keywords Assisting in Conversions and Increase PPC Advertising ROI by 17%
Hoff is Russia’s only hypermarket retailer of furniture and household items. The Company offers comprehensive solutions for interior design. Hoff is an omnichannel retailer, whose customers make purchases on the website, in brick-and-mortar stores, and/or by phone through a call-center.
As an independent brand, Hoff appeared in the market in September of 2011. By 2017, the Hoff retail chain has grown to 25 stores in 11 Russian cities. Every year, Hoff welcomes about 5 million visitors to its physical stores. The Company’s website hoff.ru attracts 12.5 million unique visitors every year.
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 Smart Data helps getting instant answers to questions about data in natural English, without involving any additional resources.
- OWOX BI Attribution assists in discovering the real value of advertising efforts, reallocating marketing budget and achieving ROI increases. It considers offline orders and distributing value only to sessions which contributed to a customer’s progression through the funnel.
- OWOX BI Pipeline facilitates analyzing data across multiple data sources in a single system. It automates cost data import to Google Analytics and helps combine user behavior data from Google Analytics and a number of other services in Google BigQuery, no coding required.
OWOX is a certified partner of Google Analytics and Google Cloud Platform, and also the first company in EMEA certified as a Google Analytics 360 Authorized Reseller. OWOX BI solutions are used in more than 9000 projects worldwide.
Hoff was looking to improve their online advertising ROI. The Company’s strategy involved the following steps:
- Collect complete data about the offline and online activities of each customer, across devices.
- Evaluate the revenue driven by each keyword, instead of assigning all the credit to the last keyword.
- Automate bid management and adjustments for PPC advertising.
The process involved: collecting full data (user actions; orders made online, offline, and by phone; advertising performance data), implementing a custom attribution model, and automating bid management in Alytics.
To automate bids, Hoff needed to know how well each keyword performs. To determine keyword values, Hoff needed to stitch together all customer touchpoints, including: online sessions across devices, phone calls and purchases in physical stores. In Google Analytics, there’s no such option.
Attribution models in Google Analytics and Yandex.Metrica can’t evaluate advertising channels accurately because of the following shortcomings:
- Ignoring cancelled orders.
- Omitting offline purchases.
- Ignoring gross margins of purchases.
- Omitting phone orders.
- Mistracking users visiting on multiple devices, as multiple unique users.
- Sampling data in reports.
Incorrect evaluation of keyword performance makes it difficult to set bids correctly. Consequently, a high bid would be a waste of budget, and a low one would drive less customers.
Data about phone orders and offline orders, as well as cost data for non-Google paid advertising campaigns, is unavailable in Google Analytics. Hoff set up data collection and attribution modeling in Google BigQuery.
This service was chosen for the following reasons:
- High-speed query processing — up to 20 seconds — at any amount of data.
- APIs enabling easy integrations with other systems.
- Possibility to easily upload data from files or Google Sheets.
- Charging only for use.
- No need to maintain databases.
To manage bids, Hoff calculates the value of channels and keywords in Google BigQuery, and imports the results to Alytics using RESTful API.
Now, let’s take a closer look at each step.
Step 1. Collect data in Google BigQuery
Google AdWords cost and performance data is automatically imported to Google Analytics thanks to the native integration and account linking.
Hoff uses OWOX BI Pipeline for:
- Collecting session, cost, and revenue performance data from Yandex.Direct in Google Analytics. This data appears in the Cost Analysis report, along with the data from Google AdWords.
- Importing consolidated session, cost, and revenue performance data from Google Analytics to Google BigQuery.
- Importing user behavior data from the website to Google BigQuery, in near-real time.
The following data is sent to Google BigQuery using APIs:
- Products’ gross margins and order completion rates from CRM and ERP.
- ClientIDs of users who’ve made phone calls, from the CallTouch call tracking system. Hoff uses multiple phone numbers provided by CallTouch: the more visits and the longer average session duration, the more phone numbers CallTouch provides. The system displays one of the numbers in each session on the website and associates the number with the user’s ClientID. Next, Hoff imports ClientIDs of the callers from CallTouch to Google BigQuery.
The data collection flowchart is given below:
Step 2. Process the data
Now all user behavior data from physical stores, call center, and the website, is collected in Google BigQuery. Hoff imports session data: hit sequences, session IDs and encrypted personal data of the users, to Google BigQuery using OWOX BI Pipeline, and associates the sessions with users by the following parameters:
- ClientID, the unique identifier for the browser-device pair.
- UserID, the unique identifier of each user, which is bound to the user’s email address.
- Loyalty card number.
Hoff groups sessions using SQL queries.
Let’s take a look at 3 examples of how the data is combined.
Anna browses sofas on the website using a browser app on her smartphone. After some time, she visits the website again on the same device, makes her choice, signs in via email, and adds the sofa to her cart. Since the device and browser are the same, Hoff groups these two sessions by ClientID. Anna comes home, opens her laptop, logs in to her account, and buys the sofa she chose. Now the sessions on her phone and laptop can be combined by UserID:
Nick visits Hoff.ru while looking for a new kitchen table on his work computer. During his lunch break, he creates an account on the website using his smartphone, and places an order.
Nick used two different devices and authorized only from his smartphone. These two sessions can’t be linked by ClientID or UserID. As a result, Google Analytics counts them as sessions from two different users.
Back at work, Nick remembers that his wife has always wanted a ‘wenge’ table, not a ‘walnut’ one, and also that he has a loyalty card. He enters Hoff.ru in on his work computer, logs in, changes his order and makes a purchase using his loyalty card.
Now Hoff can stitch together all Nick’s sessions by associating the purchase on a computer with:
- Looking for a table (Session 1), by the ClientID, as both sessions were initiated in the same browser on the same computer.
- Creating an account on the phone (Session 2) — by the UserID, as Nick logged in to his Hoff account in both sessions.
As a result, the data related to Nick’s journey from the very first visit to a purchase is collected in Google BigQuery.
Nick browses Hoff.ru on his work computer, looking for chairs to match the new table. He calls the phone number displayed on the website and places an order. Then he drives to the store and buys the chairs using his loyalty card.
Hoff combines Nick’s previous actions on the website with actions in the store and on the phone:
- Phone order and online activities — by ClientID, as Nick has bought the table before, and CallTouch has displayed the phone number in the same browser, on the same device.
- Offline purchase and online activities — by the number of Nick’s loyalty card.
Now Hoff has enough data to link together all Nick’s interactions. This is how they do it:
With the consolidated data, Hoff can see which of the search queries bring in revenue, and which of them "consume" bids without assisting in a purchase.
Step 3. Implement the attribution model
The logic of the Hoff’s attribution model is as follows: the total value of the first and the last session in a customer’s conversion path equals the value of the sessions in between. The first session is when a user visits the website for the very first time and gets acquainted with the brand. The last session is when the user makes a purchase. Combining data across different devices helps find a connection between the sessions and their chronological order.
The value for each channel is determined in 2 stages:
- Identifying the channel which has initiated the first session. If a user has been to the website before, the acquisition channel is associated with the channel of the previous session. For example, if a user visits the website by clicking a Google display ad, and has visited the website before through organic search in Bing, the model will treat Bing as the channel that has introduced the user to the brand.
Assigning value to sessions. The revenue from a purchase is distributed to user sessions according to the following rules:
- The first session receives 20% of the credit for the sale.
- The last session receives 30% of the credit.
- The sessions in between them receive the remaining 50%. The sessions are evaluated by the number of hours before the purchase. The more pages were viewed within the session and the fewer hours passed before the purchase, the more value the session receives.
Step 4. Sending the data to Alytics and using it
The value of keywords and channels is automatically recalculated on a daily basis. The result is stored in a table in Google BigQuery and Google Cloud Storage. Hoff exports the attribution results from Google Cloud Storage to Alytics using the RESTful API.
Hoff has set up rules by which Alytics determines the bid size. The greater ROI of the search phrase mentioning the product, the higher the bid. For example, if ROI for tables is less than 15%, the bid for a "buy tables" keyword will equal $1. If ROI is greater than 75%, the bid will equal $4. All numbers were determined experimentally.
Thanks to combining data about online and offline customer behavior, determining keyword values and automating bid adjustments, Hoff is now able to answer such questions as:
- How would online advertising ROI change in consideration of order fulfillment?
- How does online advertising affect orders placed in a call center?
- Which keywords drive the most profitable orders?
- Which products deliver the greatest ROI?
- What share of offline sales was influenced by online advertising?
As a result, Hoff was able to:
- Optimize bids and increase PPC advertising ROI by 17%.
- Measure the revenue impact of keywords more accurately and see 2.4 times more keywords assisting in conversions. Standard attribution models simply ignored them.
- Discover that some of the key phrases have a greater impact on offline purchases than they do on online sales.