M.video and OWOX BI: How to Optimize Advertising Campaigns with Assisted Conversions in Google BigQuery
M.video is the largest consumer electronics and home appliances retail chain in Russia, and also one of the largest European companies in the retail segment. M.video is the first publicly traded company in its market sector. M.video’s shares have been traded on the Moscow Exchange since 2007. M.video is actively developing omnichannel retailing. Сustomers make purchases on the mvideo.ru website and in brick-and-mortar stores. 85% of the customers come back to make repeat purchases, proving the M.video’s good reputation.
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.
M.video was looking to optimize advertising spend while taking into account a number of factors: contribution of multiple advertising channels to the customer’s purchase journey, business group of categories (BGC), and differences in user behavior among Russia’s 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 M.video: each department is responsible for one BGC.
The behavior of customers from Moscow, St.Petersburg, and other regions of Russia differs due to economic factors. That’s why M.video’s Media&CRM department segments orders by geography.
According to M.video, 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, M.video 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, M.video wasn’t able 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 M.video’s operations, such measurement errors drastically affected the quality of their decisions.
To compare the performance of advertising channels in different «region—BGC» segments, M.video 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.
Step 1. Collect full data in Google BigQuery
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 Yandex.Direct campaigns to Google Analytics, and also to collect data on all the advertising costs in Google BigQuery.
M.video’s 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. M.video’s 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:
Step 2. Process the obtained data
The information required for the attribution model is stored in two tables in Google BigQuery. The first table stores data about purchases, user behavior and advertising spend from Google Analytics. The other one stores data about relationships between product categories and BGC from Google Sheets.
OWOX experts combined the data from these two tables by the Product Category ID, using the JOIN operation. The data was combined as follows:
M.video wanted to see what channels, in what sequence, users interact with the most often. 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 a 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 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 inbetween contribute much less, therefore they are analyzed all together.
Step 3. Create reports
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, M.video can now see how often users from Moscow 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 for ads in Google and Criteo and see which of the channels would perform better at the upper, middle, lower funnel, for each «region-BGC» segment.
To facilitate the work of M.video’s managers and marketing specialists, OWOX experts visualized the data in form of interactive dashboards. M.video chose Google Data Studio as a dashboarding solution, for the following reasons:
- Dashboards are convenient to work with. Information 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, M.video received a dashboard displaying how purchases are segmented by ad source, region, 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, M.video was able to 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?
M.video plans to review and reallocate their Performance Marketing budget in the second half of 2017 based on the test results.