How to optimize advertising campaigns with assisted conversions in Google BigQuery

The more advertising channels the businesses develop, the more customers could be acquired by different campaigns. Accordingly, the revenue generated will grow considerably if the business users get valuable data insights to improve the marketing performance.

In this case, we describe the solution provided by the OWOX BI team for a large consumer electronics and home appliances retail chain that had challenges with optimizing advertising campaigns for different user segments over different platforms.

The company was looking to optimize advertising spendings while taking into account a number of factors: the contribution of multiple advertising channels to the customer’s purchase journey, a business group of categories (BGC), and differences in user behavior among various regions.

A business group of categories (BGC) is a group of similar product categories. For example, Audio Equipment is a common name for MP3 players, earphones, etc. Segmentation by BGC is driven by the internal structure of the retailer: each department is responsible for one BGC.

The behavior of customers from different cities or regions differs due to economic factors. That’s why the Media&CRM department segments orders by geography.

According to the retailer’s experience, 80% of online and offline customers interact with multiple advertising channels before making a purchase. The Assisted Conversions report in Google Analytics allows for analyzing the effect of online channels on conversion paths. However, the report doesn’t allow for segmentation by region and BGC. This makes it impossible to fully assess the contribution of each channel.

In addition, the company was looking to make better decisions, backed by full user behavior data. Google Analytics applies sampling if the volume of data exceeds 500 K sessions (100 M sessions in Google Analytics 360) for the reporting period. Also, it wasn’t possible to see the full picture of the conversion paths in the Multi-Channel Funnel reports, since the number of conversions each month exceeded 1 million. Given the scale of operations, such measurement errors drastically affected the quality of the retailer’s decisions.

To compare the performance of advertising channels in the different region—BGC segments, it was decided to collect user behavior data in a big data warehouse. OWOX suggested using Google BigQuery, as this service ensures security, flexibility, and rapid processing of data.

Website visitors’ behavior data is automatically imported to Google BigQuery from Google Analytics, thanks to the Google BigQuery Export feature. This feature is only available for Google Analytics 360 clients.

AdWords campaign performance data is automatically imported to Google Analytics. OWOX BI Pipeline helps import cost data for advertising campaigns to Google Analytics and also collects data on all the advertising costs in Google BigQuery.

The internal system (ERP) stores data about the relationships between product categories and BGC. For example, Microwave Ovens belong to the Small Domestic Appliances (SDA) group, and Laptops belong to the Computers group. The analysts copy the data from the internal system to Google Sheets, and then import it to Google BigQuery using the OWOX BI BigQuery Reports add-on.

The data collection flowchart is given below:

The data required for the attribution model is stored in two tables in Google BigQuery. The first table stores data about purchases, user behavior, and advertising spending from Google Analytics. The other one stores data about relationships between product categories and BGC from Google Sheets.

OWOX experts merged the data from these two tables by the Product Category ID, using the JOIN operation. The data was combined as follows:

The company wanted to see what channels users interact with the most often and in what sequence. The channels which most frequently bring the first two sessions in the conversion path would perform best at the upper stages of the funnel, as they help attract users to the website. The channels which most commonly bring the final two sessions before purchase would perform better at the lower funnel: they help buyers make the decisions.

OWOX analysts suggested segmenting orders by the number of sessions to the transaction (1, 2, 3, 4, and 5+ sessions). Long conversion paths (5+ sessions) are treated similarly to the short ones: the main focus is placed on the first two and the last two sessions. These are the sessions during which a user learns about a product, and makes a buying decision. The sessions in between contribute much less, therefore they are analyzed all together.

The OWOX team set up automatic import of segmentation results to Google Sheets using the OWOX BI BigQuery Reports add-on and created a report. The report shows how advertising channels perform for different locations and different BGCs. For example, now it can be seen how often users visit the website by clicking Google ads at different stages of the funnel in long (5+ sessions) conversion paths. The company’s specialists can compare the performance of ads in Google and Criteo and see which of the channels would perform better at the upper, middle, and lower funnel, for each region-BGC segment.

To facilitate the work of the managers and marketing specialists, OWOX experts visualized the data in form of interactive dashboards. Google Data Studio was chosen as a dashboarding solution, for the following reasons:

• Dashboards are convenient to work with: data can be easily filtered by date or selected dimensions.
• Data sources for reporting can be connected in just a few clicks.
• Unlimited custom reports and dashboards can be created at no cost.

As a result, the dashboard was received, displaying how purchases are segmented by ad source, region, a business group of categories (BGC), and the path length before the purchase.

As a result of collecting and processing data in Google BigQuery using Google Analytics 360 and OWOX BI, the company could evaluate the performance of advertising channels for different regions and BGC, and also visualized the results in Google Data Studio. This helped answer such questions as:

• Which channels perform better at the upper funnel, middle funnel, and lower funnel?
• Which channels perform better in a particular «region—BGC» segment?
• Which «region-BGC» segment gets the most purchases?

Now the plans are to review and reallocate the performance marketing budget in the second half of 2017 based on the test results.