Ulmart Improves Internal Analytics Using the Funnel Based Attribution Model
Ulmart is the first Russian internet-retailer to exceed $1 billion in sales. In 2008, the company opened for business in Saint Petersburg. Having become one of the Russia’s biggest online retailers, Ulmart offers over 150,000 products from consumer electronics to kids’ and automotive goods. The company’s distribution network today consists of over 400 sales points in more than 200 cities of Russia.
Before cooperation with OWOX BI, Ulmart 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, Ulmart’s specialists decided to set up the Funnel Based Attribution Model.
Ulmart has a variety of user touchpoints: social media ads, Yandex.Market price comparison website, PPC, ulmart.ru, phone calls, direct marketing, fulfillment centers and outposts. Similar to most companies with multichannel marketing, Ulmart 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, Ulmart needed to bring together 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 Ulmart needed to set up end-to-end analytics, taking these steps:
- Develop an individual set of metrics to collect user behavior data from the website to Google Analytics 360. Using the standard export, Ulmart’s team sends the unsampled data to Google BigQuery.
- Set up the OWOX BI Pipeline from ad services to Google BigQuery.
- Configure the export of the transaction data from the CRM system to Google BigQuery.
- Create a Funnel Based Attribution Model based on the combined data, in Google BigQuery.
- Do the record linkage for channel groupings together with the OWOX BI analysts, as Ulmart uses its own names for channel groupings different from GA 360.
- Obtain reports in Google Sheets for monthly budget planning.
Below is the Ulmart’s data consolidation chart:
Now, let’s take a closer look at how Ulmart built the Funnel Based Attribution Model along with reports.
Step 1. Send data about web user behavior to Google BigQuery
The OWOX BI analysts helped the Ulmart team develop, set up and implement the individual set of metrics for ulmart.ru. Moreover, our specialists regularly test and update the metric system for new domains along with new features.
Ulmart collects data about user behavior on the website in Google Analytics 360 and sends this data to Google BigQuery on daily basis, to be linked with the data about ad costs and transactions. The company chose the paid version of GA as ulmart.ru has a high level of traffic. The standard version applies sampling when the number of user sessions goes over 500,000, while GA 360 allows Ulmart to get accurate data down to a hit.
Step 2. Collect the data about ad costs in Google BigQuery
The data about AdWords costs goes to Google Analytics 360, thanks to the native integration. Ulmart uses OWOX BI Pipeline to send the data from Facebook, Yandex.Metrica, Yandex.Direct to GA 360, and to combine the cost data about all ad services in Google BigQuery. The table below shows the structure of the sent data:
Step 3. Send the data about orders to Google BigQuery
To take into account the data about returns and completed orders, Ulmart exports the data about transactions from the CRM system to Google BigQuery. The structure of the aforementioned data is shown below:
This structure helps combine data about completed orders with the data about website user behavior, using the user_id and time keys.
Step 4. Build the attribution model
Ulmart’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 a purchase, and recommended the optimal conversion window Time from the first user interaction with the website to purchase. Only the sessions that are in this time period will get certain value. and transaction window Only sessions within this time period will get the conversion value..
Using this data, Ulmart created a funnel based attribution model:
The 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 is, the more value gets the session in which a user passed this step. Only the sessions that led to an 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.
Step 5. Do the record linkage for channel groupings
All the traffic sources in GA 360 reports are by default formed into the following channel groupings: Direct, Organic, Email, Referral, Social, Display, CPC, and Other.
However, Ulmart’s 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 GA 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.
Step 6. Build reports
With the help of the OWOX BI analysts Ulmart created two reports. 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:
- Navigate to Smart Data and ask "How is the value of sources and mediums distributed among the funnel steps", and open the report.
Navigate to the top right corner and choose "Copy the SQL query to Clipboard"
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 advised 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 loaded. You can find more details on report configuration here.
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:
To learn more how to schedule the regular report update, follow this link.
For Ulmart, OWOX BI specialists modified the query and added dynamic parameters: the source and the analysis period.
Follow this link to find out more about dynamic parameters in queries.
As a result, Ulmart obtained the traffic analysis report that 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 Ulmart’s actual costs, revenue and ROAS on ad campaigns. Using this report, the company’s marketing specialists can find out which sources bring more revenue, and which ones don’t pay off:
- Along with the OWOX BI team, Ulmart’s specialists set up a correct flexible system of data collection.
- Using the OWOX BI and Google products, Ulmart automated the data collection process. All the information is now available in a single interface, in real time.
- The Funnel Based Attribution Model helped the company better evaluate the performance of ad campaigns and channels.
If you have any questions, feel free to leave us a comment ;)
Give the proper credit to all your marketing initiatives with OWOX BI Attribution.