How to discover 2.4 times more keywords assisting in conversions and increase PPC advertising ROI by 17%

Winning the marketing competition today is impossible without applying data analysis to improve business performance. The new technologies allow companies to transform their workflow, effectively leverage data for making decisions, and reach marketing goals faster.

In this case, we describe the solution provided by the OWOX BI team for an omnichannel retailer of furniture and household items which customers make purchases on the website, in brick-and-mortar stores, and/or by phone through a call center. It had challenges with improving its online advertising ROI.

Goals

As the company was looking to improve its online advertising ROI, its strategy involved the following steps:

1. Collect complete data about the offline and online activities of each customer, across devices.
2. Evaluate the revenue driven by each keyword, instead of assigning all the credit to the last keyword.
3. 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 the bid management system.

Challenge

The company needed to know how well each keyword performs to automate bids. All customer touchpoints, including online sessions across devices, phone calls, and purchases in physical stores need to be stitched together to determine keyword values. In Google Analytics, there’s no such option.

Attribution models in Google Analytics can’t evaluate advertising channels accurately because of the following shortcomings:

• Ignoring canceled 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 fewer customers.

Solution

Data about phone orders and offline orders, as well as cost data for non-Google paid advertising campaigns, is unavailable in Google Analytics. So, the data collection and attribution modeling is set up 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 enable easy integrations with other systems.
• Possibility to easily upload data from files or Google Sheets.
• Charging only for use.
• No need to maintain databases.

The value of channels and keywords were calculated in Google BigQuery to manage bids and then the results were imported to the bid management system 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.

OWOX BI Pipeline is used for:

• Collecting session, cost, and revenue performance data from advertising services to 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 call tracking system. The multiple phone numbers provided by the system are used: the more visits and the longer the average session duration, the more phone numbers the system provides. The system displays one of the numbers in each session on the website and associates the number with the user’s ClientID. Next, the ClientIDs of the callers are imported from the call tracking system to Google BigQuery.

The data collection flowchart is given below:

Step 2. Process the data

Now all user behavior data from physical stores, call centers, and the website, is collected in Google BigQuery. The session data (hit sequences, session IDs, and encrypted personal data of the users) is transferred to Google BigQuery using OWOX BI Pipeline. The sessions are associated 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.

The company groups sessions using SQL queries. Let’s take a look at 3 examples of how the data is combined.

Example 1

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, these two sessions are grouped 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:

Example 2

Nick visits the website 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 the website on his work computer, logs in, changes his order, and makes a purchase using his loyalty card.

Now all of Nick’s sessions can be stitched together 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 account in both sessions.

As a result, the data related to Nick’s journey from the very first visit to purchase is collected in Google BigQuery.

Example 3

Nick browses the website 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.

Nick’s previous actions on the website were merged with actions in the store and on the phone:

• Phone order and online activities — by ClientID, as Nick has bought the table before, and the call tracking system 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 it’s enough data to link together all Nick’s interactions. This is how they do it:

As a result, all the user’s ClientIDs, email addresses, loyalty cards, and orders were consolidated to 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 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:

1. 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 an organic search in Bing, the model will treat Bing as the channel that has introduced the user to the brand.
2. 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 the bid management system 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. The attribution results are exported from Google Cloud Storage to the bid management system using the RESTful API.

The rules were set up for the bid management system to determine 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.

Results

Thanks to merging data on online and offline customer behavior, determining keyword values, and automating bid adjustments, the company 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, it was possible 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.

‍