Kolesa Group solved marketing attribution using behaviour scoring model
Learn how the leading online classifieds company in Kazakhstan solved the complex problem of marketing analytics and attribution using Google BigQuery and SegmentStream.Request a demo
“The product developed by the SegmentStream team was an excellent choice.”— Kolesa Group
Kolesa Group is the company behind the four largest classifieds in Kazakhstan and CIS, which are used by more than 12 million people every month. With more than 350 employees, it is one of the leading and fastest growing IT companies in the region.
To support its business growth while ensuring operational efficiency, Kolesa Group partnered with SegmentStream to solve its most important marketing analytics challenges.
1. Behavioural data collection
Kolesa Group has four different classifieds, which help people to buy and sell cars, real estate and various goods. Yet there is no single place to collect and analyse user behaviour data across multiple websites.
Also, Kolesa Group websites have over 12 million unique users per month. With this amount of data in Google Analytics, sampling occurs even in weekly reports. When sampling, Google Analytics builds reports not on complete data but on a partial sample, in which there can be no more than 500 thousand sessions. For product and marketing analytics such reports are not suitable.
2. Marketing report preparation
For the Kolesa Group team it is important to analyse the performance of their marketing channels and campaigns on a daily basis. Yet it takes a huge amount of time and energy to manually collect the data from all marketing sources and unify it into a single report. This process has to be automated.
Unfortunately, APIs of ad platforms change quite often, and it is not commercially reasonable for Kolesa Group to build and maintain automated API connectors with dozens of ad platforms using their own development resources.
3. Attribution problem
On classifieds, unlike e-commerce websites, it’s not possible to define a single key conversion, such as a purchase. For sellers, the key conversion is creating a posting, while for buyers it is a click on a phone number or a sent message. Because of this, the process of evaluating ad campaign performance becomes extremely difficult.
To make effective budget decisions it is crucial to understand the proper value of each session depending on the user actions on the website.
- Collect website behaviour data across all classifieds into a single data warehouse. Ensure that all the data is collected without any sampling or limits.
- Save time for the marketing team by automating the whole process of report preparation. All advertising cost data from multiple platforms should be automatically imported on a daily basis and unified into a single dashboard with all important marketing KPIs.
- Solve the attribution problem by calculating the proper value of each user’s session on a website.
The overall architecture of the solution
To be able to build an automated marketing report, all data from multiple sources should be collected into one place first.
Kolesa Group has already used Google BigQuery data warehouse for other business activities and decided to use it for marketing analytics as well.
The overall implementation process took about 2 weeks for each Kolesa Group website and comprised the six steps described below.
Step 1. Definition of key conversions for further campaign scoring
Each user action on the classifieds website has a different business value. For example, creating a posting is more important than viewing a posting. Therefore, to compare the effectiveness of marketing campaigns, we introduced different weights for each type of conversion and started to score all user sessions based on these weights.
As the first step of the solution, it was important to choose key conversions on the website for different audiences: for sellers and for buyers. Together with the Kolesa Group team, we selected four key conversions:
- Ad placement has been started;
- Ad has been successfully placed;
- A message has been sent to the seller or buyer;
- A seller phone number has been viewed.
Step 2. Behavioural data collection into Google BigQuery
As soon as the tracker was implemented on the Kolesa Group websites, real-time events and additional data about the user, website and other contextual parameters started to flow directly into Google BigQuery.
Step 3. Cost data import automation
As the next step, Kolesa Group automated cost data import from all of their advertising platforms into Google BigQuery using turnkey SegmentStream API connectors. Cost data is required to calculate campaign performance metrics such as ROAS, CPA and others.
Connected data sources in SegmentStream interface
At any time, Kolesa Group can automate cost data import from any other advertising platform they might use in the future. SegmentStream has dozens of pre-built API connectors with the most popular marketing platforms. In addition, it is possible to import cost data from platforms that do not have an API via Google Sheets.
The SegmentStream platform automatically transforms raw data from multiple ad platforms into a single report grouped by five UTM parameters. This makes it possible to combine advertising costs with website sessions, as well as import cost data to Google Analytics.
In addition, there is no need to mark the campaigns with specific UTM parameters to stitch campaign data with sessions on the website. SegmentStream can parse the UTM markup that the client is using, including complex dynamic substitutions.
Step 4. Currency conversion
Configuration of currency conversion in SegmentStream interface
It is important to Kolesa Group to see cost data in their marketing reports in a single currency.
SegmentStream automatically converts US dollars (USD) and Hungarian forints (HUF) to Kazakhstan tenges (KZT) using daily exchange rates.
Step 5. Automated data transformation
Using standard Google BigQuery tools such as “Scheduled Queries” it is impossible to set the order in which SQL queries should be executed.
In addition, it is not possible to predict in advance how long the SQL query will take to be executed. For example, quite often the APIs of ad platforms are non-responsive. In this case, when using “Scheduled Queries”, the cost data will not be updated in the table with user sessions, and therefore the data will be missing in the final marketing reports.
To solve this challenge, the SegmentStream platform has a built-in automated ETL Workflow Management System, which is responsible for running a series of SQL queries in a proper sequence with all the dependencies.
Daily data transformations on the Kolesa Group website
Data transformation samples:
All the behavioural data is collected in a raw format in a hit table, which might contain a gigabyte of data just for one day. Hit table queries process large amounts of data, increasing the cost of using BigQuery.
To solve this problem, SegmentStream transforms raw behavioural data into a pre-aggregated session table that is much cheaper to work with. Sessions are formed according to rules similar to Google Analytics: a session is interrupted after 30 minutes of user inactivity, when the date changes or when the traffic source changes.
Cost data stitched together with user sessions according to five UTM parameters: source, medium, campaign, term and content.
Website sessions are combined with online or offline conversions. The value of each session is calculated according to the chosen attribution model. It might be a simple single-channel attribution model such as First Click, Last Click or Last Non-Direct Click. Or it might be an advanced multi-touch attribution model such as SegmentStream AI-driven attribution.
The final data transformation prepares a flat table with all the necessary metrics and dimensions fully ready for visualization. This table can now be connected to any BI tool such as Google Data Studio, Power BI, or Tableau.
Step 6. Marketing report preparation
Sample of the report in Google Data Studio with campaign performance data. Data modified for demonstration purposes.
The final marketing report contains the basic campaign level data:
- Cost (KZT) - advertising costs in a single currency;
- Cost per Click (KZT).
As well as advanced campaign performance data:
- Viewed Phone Number - number of seller’s phone number views;
- Started Ad Creation - number of clicks on the button to start ad placement;
- Approved Ad - number of successfully placed ads;
- Used Thread - number of messages sent to the seller or buyer;
- Total Score - total value of sessions based on the scoring rules;
- Cost per Score (KZT) - synthetic metric to evaluate the campaign performance
Based on these metrics, the value of each session is determined. Summarized value is contained in the Total Score metric. The metric summarizes the number of events multiplied by the conversion weight coefficient xn :
Total Score = event1 * x1 + event2 * x2 + ... + eventn * xn
Different weights were chosen for different conversions depending on how valuable these events were to the business. The greater the number of valuable actions a user performed during the session, the higher the Total Score. The higher the Total Score, the higher the value of the campaign.
By calculating session values, Kolesa Group can now compare the true performance of each marketing channel, campaign, keyword or piece of content. With the Total Score and Cost per Score metrics it is easier to make better decisions about the budget allocation between multiple marketing channels and campaigns.
The implementation of SegmentStream made it possible to evaluate the campaign performance in real time when previously this process took hours each week.
Also, there is no data sampling in marketing reports. It is now possible to analyse complete data for any time period. All the necessary data across multiple data sources and websites is now collected in a single data warehouse and used in both marketing and product analytics.
And most importantly, Kolesa Group doesn’t need to maintain all of this technical infrastructure. It’s all fully covered by the SegmentStream platform.
For a very long time, we wanted to build our product and marketing analytics on Google BigQuery and were looking for a solution that would satisfy our needs. In particular, it was important for us that the product withstood high loads, since we have four websites with a multi-million audience on each.
The product developed by the SegmentStream team was an excellent choice, thanks to which we were able to collect raw behavioral data from all our websites into Google BiQuery without sampling or any other limits, as well as automatically collect data from all our advertising platforms to build advanced analytics in our BI tools.
We have reached a new level of product analytics and now we know about our users many times more than before. Based on the collected data, we built a hundred dashboards for the various business needs, established monetization processes, launched many A/B tests, developed dozens of different ML models to improve product performance.
Thanks to SegmentStream, we now save a lot of resources on the preparation of such reports, we can make better product decisions as quickly as possible, and we can optimize our marketing budget in the most efficient way. Separately, I want to note the top-notch SegmentStream support that quickly helps with answers to any of our questions.
We actively monitor how the SegmentStream platform is developing and discuss the possibilities of expanding our cooperation. We wish them success!
— Petr Tsarenko, Chief Data Officer, Kolesa Group
You might also be interested in
Eventim automated complex marketing reporting using Google BigQueryLearn more
MebelVia decreased the Cost of Sale by 11.5% using AI-driven multi-touch attributionLearn more
Get started with SegmentStream
Request a personalized demo with our team or try SegmentStream for free today!