9 Steps to Know Everything: Building Marketing Analytics for Comfy

This article was prepared based on a report by COMFY at the eCommerce 2018 conference. It will tell you how to set up an effective marketing analytics system for evaluating ad campaigns that takes into account offline sales and the contribution of each session before a customer places an order.

About the company

COMFY is a leader among multi-channel retailers of household appliances and electronics in Ukraine in terms of efficiency. The company was founded in 2005 and today has about 3,000 employees. The retail network includes 90 stores in 55 cities, and the comfy.ua website is one of the five largest online stores in the country.

COMFY has won awards from industry authorities, including Marketing Strategy of the Year in the X-Ray Marketing Awards 2017 and Retailer of the Year and E-commerce Retailer of the Year in the technology, electronics, and gadgets segment in the Retail & Development Awards 2018. What’s more, COMFY entertains customers with funny videos.

Goal

Like any multi-channel retailer, COMFY aims to effectively distribute its marketing budget online and offline. The company’s management needs to know answers to questions such as:

• Are marketing efforts having the maximum effect or are there things that could be optimized?
• How will optimization of spending help to reduce costs and increase sales?
• How can KPIs be improved without changing the advertising budget?
• How do attraction channels interact and which channel combinations bring more sales?
• Is money spent on online advertising efficient in terms of attracting new customers and increasing offline sales?

To maintain a leading position in the market, the company needs to constantly improve how it finds answers to these questions.

Problem

When developing a marketing strategy, it’s important to properly evaluate advertising campaigns. COMFY monitors key website indicators using Google Analytics. This tool provides ready-made reports and shows differences in the behavior of users who come from different traffic channels.

At some point, however, there weren’t enough reports in Google Analytics that the company could use for complete analysis and strategic decision-making. The thing is, COMFY has many physical stores. Customers can view products on the website but purchase offline, and vice versa. The company’s marketers need to know the following:

• What path did a user follow before making a purchase in a physical store?
• Which channel had the most influence on the user’s buying decision?
• At what stage of the funnel did a particular channel perform most effectively?

Google Analytics can’t answer these questions because it isn’t an marketing analytics system. In standard Google Analytics reports, the value of an order is distributed between traffic sources according to attribution models, which don’t take into account the mutual influence of channels. Often, the Last Non-Direct Click model is used by default. This means that if a client comes to a site ten times from paid traffic sources before making a purchase and on the eleventh time is redirected from a Google Ads campaign and does make a purchase, the entire value of the order will be attributed to the Google Ads campaign.

Standard First Click, Last Click, and Last Non-Direct Click models don’t take into account nearly 80% of sessions made before conversion. You might argue that Google Analytics, in addition to these models, offers alternative models that allow you to evaluate the role of channels when they participate in the middle of the chain. Plus, it’s possible to view and compare statistics from different attribution models.

For instance, in the group of reports on multi-channel sequences, there’s an Assisted Conversions report:

Or you can use the Model Comparison Tool:

Surely, these Google Analytics features add some variety to this analytical tool and allow for detailed analysis of the mutual influence of traffic sources. Still, there are drawbacks that can’t be avoided. For instance, if you evaluate channels by different attribution models, then add up their values, the sum will be greater than what the company actually earned. In addition, Google Analytics attribution models don’t account for offline purchases, cancellations, and refunds. You can find out more about the pros and cons of various models in our article comparing multi-channel attribution models.

The OWOX BI team has helped COMFY marketers solve these problems and get the missing answers.

Solution

To assess the effectiveness of COMFY’s advertising campaigns, we recommended our own ML funnel-based attribution model — OWOX BI Attribution. This model allows you to find out the contribution of each session before an order is placed, which advances the user through the transaction funnel and takes into account offline sales. At the same time, all calculations are transparent and are stored in your project in Google BigQuery.

To create a ML funnel-based model, you need to collect, process, and combine all the necessary data. Here’s how COMFY did it:

Step 1. Collect user behavior data on the website

Our analysts helped COMFY develop and implement an individual metrics system for collecting data on the website.

Before running OWOX BI Attribution, we recommend that customers check:

• whether all conversions and micro conversions in the user’s path to the order get into Google Analytics;
• the accuracy of data recorded in Google Analytics.

By checking this at least once a month, you’ll be able to avoid losing important user data. You can read more on how to properly set up Google Analytics in our Google Analytics setup tutorial. A similar check was performed by the COMFY team.

Step 2. Collect data on online advertising costs

To upload cost data from Google Ads to Google Analytics, you just need to integrate these two services. It appears to be more difficult with other ad platforms. Google Analytics lets you upload all costs manually using CSV files. To avoid this routine work, COMFY marketers use OWOX BI Pipeline.

To set up cost import into Google Analytics, you can simply choose an advertising platform (Facebook, Instagram, Criteo, etc.) and provide access to it. After that, data will be automatically sent to the specified Google Analytics view.

In addition, the COMFY team added SEO and email marketing costs to their OWOX BI account by linking other services to the pipeline. Thanks to this, they can compare costs, revenues, and return on advertising spend across all advertising channels in the Cost analysis report.

Step 3. Export data from Google Analytics into Google BigQuery

Information about users’ actions on the COMFY website and the costs of online advertising are collected in Google Analytics. But how can we get reports from this raw, unsampled data? The fact is that the comfy.ua site has a very high volume of traffic, so Google Analytics uses sampling. That is to say, it builds reports not based on 100% of the data but on a particular sample.

COMFY specialists use Google BigQuery, a cloud service for storing and processing data, to work with unsampled data. We won’t dwell on the advantages of Google BigQuery since we’ve described them in a separate article.

How can you export raw data from Google Analytics into Google BigQuery? The paid version of Google Analytics 360 provides a standard export option. But what if you aren’t ready to spend $100K to $120K per year on an analytics system? In this case, the solution from OWOX BI can help, and this is what the COMFY team used.

Data about visitors’ actions on the comfy.ua site is passed from Google Analytics to Google BigQuery in real time and is stored in separate sheets for each day:

This allows COMFY marketers to work with unsampled data and build reports without restrictions on the number of parameters and indicators.

To transfer advertising costs to Google BigQuery, COMFY marketers created a separate stream in OWOX BI Pipeline. Every day, their project receives new data on costs. Information for the previous 21 days is updated in Google BigQuery if it has changed in the advertising service.

Step 4. Upload offline data to Google BigQuery

Another advantage of Google BigQuery is the ability to collect data from various sources: Google Analytics, CRM systems, ERP systems, DoubleClick, YouTube, Google Ads, any other advertising platform, email, call tracking services, and so on. Then this data is combined and used for marketing analytics.

The COMFY team set up a daily automatic upload of offline sales data from their ERP system to Google BigQuery. Google BigQuery’s security system allows you to control access to users’ personal data: names, emails, phone numbers, and even credit card numbers.

Step 5. Combine online and offline data

The data uploaded to Google BigQuery from different touch points must be linked by a key parameter. Often this parameter is the User ID.

When a user registers on the comfy.ua website, they’re assigned a unique User ID, which is linked in the ERP system with their bonus card number, telephone number, and email address. The next time the authorized user visits the site, their User ID is transmitted from the site to Google Analytics and then to Google BigQuery. In this way, company marketers can track the entire chain of a given customer by combining their actions on the site with any offline purchases.

In addition, OWOX BI Pipeline updates the value of the User ID field in the session data tables for the last 30 days on a daily basis. This means that if a specific Client ID has been assigned a User ID, you can track this user’s actions over the past month, even if they weren’t yet authorized on the site.

Step 6. Set up OWOX BI Attribution and run calculations

When all the necessary data has been collected in Google BigQuery, you can create a ML funnel-based attribution model in 30 minutes. When setting up this model, you can add steps to the pre-transactional funnel that are important to your business. You should also pay attention to the transactional The period over which the conversion value is distributed and conversion The time between the visitors first interaction with the goods until purchasing. Only sessions from this period will receive order value. windows — the period for which you’ll receive data analysis depends on the windows you choose.

The COMFY team selected enhanced e-commerce events as funnel steps in the attribution model:

In the GIF above, you can see the five funnel steps that are typical for most e-commerce projects.

If your business requires a more detailed funnel, you can always add additional steps:

Additionally, in the model settings, you can exclude any traffic source from the calculations so that a value isn’t assigned to it. For example, you can remove direct traffic, email channels, and brand campaigns from direct calculations:

COMFY marketers have also added data on transactions at physical stores. For this, they added a table with all sales (online and offline), which they created in the previous step, as a source of transaction data. The structure of the table can be viewed in our help center.

After creating and setting the attribution model, you can run a one-time calculation or schedule automated calculations at a specified frequency.

Step 7. Estimate the contribution of traffic sources considering offline transactions

To visualize the results of the attribution model calculations and compare the effectiveness of traffic channels, COMFY uses several tools, including OWOX BI Smart Data.

Getting a report is simple: just select an interesting question, click on it, and in the next tab you’ll get an answer in the form of tables and graphs:

Reports in OWOX BI Smart Data help COMFY marketers get answers to questions such as:

• How does revenue/ROAS/CPA/ROI differ by campaign/traffic sources and channels compared to Google Analytics Last Non-Direct Click model?
• How much is a particular channel/traffic source or a particular campaign undervalued/overvalued compared to the Google Analytics Last Non-Direct Click model?
• Should the budget for certain groups of campaigns be increased/decreased?
• How has a specific channel/traffic source or a particular campaign affected the user’s progress at each specific step of the funnel?

Keep in mind that these reports are advisory. In addition to information from the reports, it’s necessary to take into account such factors as the seasonality of goods, sales on TV, the influence of other offline attribution channels, brand campaigns, and other variables.

This report shows the differences in revenue brought in by different advertising channels according to different attribution models. When COMFY marketers evaluated ad performance only based on the Last Non-Direct Click model (excluding offline purchases), the revenue of some channels turned out to be negative. However, when they took into account offline orders and calculated revenue based on a ML funnel-based attribution model, they saw that these channels were greatly underestimated, as can be seen in the screenshot above.

All calculation data, including the logic of the queries used, is stored in the Google BigQuery project:

More information about the structure of the data stored in intermediate and resulting tables can be found in our help center. And if you want to find out more about what the COMFY analysts, marketing specialists, and CMO use for attribution, read our article.

Step 8. Customize reports in Google BigQuery

In addition to reports in OWOX BI Smart Data, the COMFY team uses their own reports, which are built using SQL queries that choose data in Google BigQuery. They help test hypotheses about users’ actions on unsampled data and information from the ERP.