How to improve internal analytics using the ML funnel-based attribution model

With applying as many advertising channels as possible to reach customers, businesses face a big problem with the correct evaluation of performance, success, and, obviously, revenue growth. The questions they need to answer are What channel works better? Which one should be eliminated and which provides qualified leads? Now it’s time for attribution models to come into the game.

In this case, we describe the solution provided by the OWOX BI team for a large online retailer that had challenges with improving its performance and using the correct attribution model.

Before cooperation with OWOX BI, the company used the Last Click attribution model that gave all the value to the last source, ignoring the contribution of all the previous steps before the order. To evaluate the performance of ad campaigns correctly, it was decided to set up the ML funnel-based attribution model.

The company has a variety of user touchpoints: social media ads, price comparison website, PPC, website, phone calls, direct marketing, fulfillment centers, and outposts. Similar to most retailers with multichannel marketing, it faced the problem of scattering the data out, as the company collects and stores the data in different systems.

To evaluate the contribution of each channel to the customers’ journey along the funnel, the retailer needed to merge the data about user behavior, ad costs, offline orders, and the actual company’s revenue, with the account of the completed orders. This means that the company needed to set up marketing analytics, taking these steps:

1. Develop an individual set of metrics to collect user behavior data from the website to Google Analytics 360. Using the standard export, the unsampled data can be sent to Google BigQuery.
2. Set up the OWOX BI Pipeline to collect data from ad services to Google BigQuery.
3. Configure the export of the transaction data from the CRM system to Google BigQuery.
4. Create an ML funnel-based attribution model based on the merged data in Google BigQuery.
5. Do the record linkage for channel groupings together with the OWOX BI analysts, as the retailer’s own names for channel groupings are different from GA 360.
6. Obtain reports in Google Sheets for monthly budget planning.

Below is the data consolidation chart:

Now, let’s take a closer look at how the ML funnel-based attribution model was built along with reports.

The OWOX BI analysts helped develop, set up, and implement the individual set of metrics for the retailer. Moreover, our specialists regularly test and update the metric system for new domains along with new features.

The data about user behavior on the website was collected in Google Analytics 360 and sent to Google BigQuery on daily basis, to be linked with the data about ad costs and transactions. The company chose the paid version of Google Analytics as its website has a high level of traffic. The standard version applies sampling when the number of user sessions goes over 500,000, while Google Analytics 360 allows getting accurate data down to a hit.

The data about AdWords costs goes to Google Analytics 360, thanks to the native integration. While OWOX BI Pipeline is used to send the data from Facebook to Google Analytics 360 and to merge the cost data about all ad services in Google BigQuery. The table below shows the structure of the sent data:

To take into account the data about returns and completed orders, the analysts export the data about transactions from the CRM system to Google BigQuery. The structure of the data is shown below:

This structure helps merge data about completed orders with the data about website user behavior, using the user_id and time keys.

The retailer’s sales funnel consists of 5 steps: Visit, Product Page, Add to cart, Checkout, Purchase. The OWOX BI team calculated the average time period from the website visit to purchase and recommended the optimal conversion window and transaction window.

Using this data, an ML funnel-based attribution model was created:

The ML funnel-based attribution model evaluates the probability that a user moves from one step of the sales funnel to another. The grey column demonstrates the probability value. The lower the probability of moving from one step to another, the more value gets the session in which a user passed this step. Only the sessions that led to order will get the value. You can learn more about the calculation logic of OWOX BI Attribution in our blogpost.

The attribution results are used to build reports that we’ll describe in step 6.

All the traffic sources in Google Analytics 360 reports are by default formed into the following channel groupings: Direct, Organic, Email, Referral, Social, Display, CPC, and Other.

However, the marketing specialists use their own channel grouping names for internal reports. To create the attribution model, the company’s team used the already collected data for the past periods with their own names for channel groupings. That’s why it was too late to change the names in Google Analytics 360 settings. Due to this fact, the OWOX BI analysts conducted the record linkage and created an updatable list of matched names for channel groupings in Google Sheets. The table below demonstrates the list structure:

The OWOX BI team created a script to combine the record linkage in Google BigQuery with the attribution results on a monthly basis, using the source and medium keys.

With the help of the OWOX BI analysts, two reports were created. The first report helped understand which affiliates attribute the value of other channels to themselves. This report is available in OWOX BI Smart Data. The analysts exported the data from the obtained report to Google Sheets, using the free OWOX BI BigQuery Reports add-on.

Here are the steps that the OWOX BI analysts took to export the data to Google Sheets:

1. Navigate to Smart Data and ask How is the value of sources and mediums distributed among the funnel steps, and open the report.
2. Navigate to the top right corner and choose Copy the SQL query to Clipboard.

1. Create a new report in Google Sheets. To do that, open the Add-ons menu, then choose OWOX BI BigQuery Reports and Add a new report. Then select existing Google Cloud Platform projects, choose Add a new report and click Paste:

Please be aware that each new report is created in a new sheet:

Provide the report configuration in a sidebar: select an existing Google Cloud Platform project and a Google BigQuery query, that will provide data to be uploaded.

Note! You can find more details on report configuration here.

1. Now the report is available in Google Sheets. You can schedule the automatic report update for more convenience. To do that, navigate to Schedule reports in the settings of OWOX BI BigQuery Reports:

Set the necessary time period for the update:

Note! To learn more about how to schedule the regular report update, follow this link.

The OWOX BI specialists modified the query and added dynamic parameters: the source and the analysis period.

Note! Follow this link to find out more about dynamic parameters in queries.

As a result, the traffic analysis report was obtained and it demonstrates which funnel step is getting more influence from a certain source:

Having filtered the affiliate partners only, the company can identify the ones with the most value on the final funnel step:

The second report demonstrates the actual costs, revenue, and ROAS on ad campaigns. Using this report, the marketing specialists can find out which sources bring more revenue, and which ones don’t pay off:

• The correct and flexible system of data collection was set up by the OWOX BI team.
• Using the OWOX BI and Google products, the data collection process was automated. All the data is now available in a single interface, in real-time.
• The ML funnel-based attribution model helped the company better evaluate the performance of ad campaigns and channels.