Setting up advanced analytics for a CS:GO trading site

Standard analytics tools are tailored to simple business models. If you have a common e-commerce project with one or two conversion types and a simple sales funnel, there are usually no difficulties with reporting. But what about companies with a more complex business model?

One of our clients used OWOX BI to set up advanced analytics for a platform that allows customers to quickly and safely exchange, buy, and sell CS:GO and Dota 2 items.

Like most companies that accumulate lots of data as they grow, our client realized that their data could be used to make better decisions. Our client’s main product is a platform for exchanging and purchasing virtual items that is used by more than five million people in around 100 countries.

At some point, the company outgrew ad-hoc reports in Excel and simple analytical dashboards in which data was imported directly from various systems. They wanted to set up a data warehouse (DHW) with all their data, build understandable data marts and analytical business intelligence (BI) reporting on top of that data warehouse, and automate this process to live their best life.

With the help of OWOX BI and Google BigQuery, the company wanted to accomplish three main tasks:

• Automate the importing of data from various systems
• Build analytical data marts
• Improve data quality

The company faced several challenges in the field of data analysis. The first was that standard analytics tools are tailored to less complex business models than our client’s. The company’s service combines the functionality of a store and a marketplace. In addition, buyers can pay both with money and with in-game items. That’s why the company has several types of transactions and several types of revenue. Consequently, our client needed customized reports to analyze user behavior, conversions, and marketing campaign ROI.

Two other challenges were poor data quality and data discrepancies across systems. The client often faced the fact that the same metric had one meaning in Amplitude, another in Google Analytics, and yet another in the transactional database. To avoid this, they decided to start working with a data warehouse and, in the future, to build analytical reporting based on data from the DHW and not from individual sources.

When choosing a DWH, the company settled on Google BigQuery due to the expertise of the analytics and development team, the suitable pricing model, and the ease of getting started with Google Cloud Platform.

Initially, the client used OWOX products to create a DWH in Google BigQuery and upload raw data from Google Analytics and ad accounts for further analysis by the marketing team. But over time, this became the primary storage location, and now it is used by analysts, product managers, marketers, some developers, and even the customer support team.

How data is merged for reports:

1. Using OWOX BI Pipeline, the company automatically imports cost data from advertising services (Facebook, Instagram, Bing, etc.) into Google BigQuery.
2. OWOX BI Streaming collects user behavior data from the site and transfers it to BigQuery.
3. Using its own solutions, the company loads data into BigQuery from several MySQL databases, Amplitude, and various ad hoc files and Google Docs. The uploading is orchestrated using Google Cloud Composer. They also have multiple streaming inserts of data.
4. Data is stored in layers. All the data mentioned above enters the storage in its raw form in the staging layer. Ideally, this layer should be one dataset with many tables, but so far the client has separate datasets for data from different systems (Google Analytics, Amplitude). As the DWH develops, the company will put this in order. The formation of this layer is also orchestrated from Google Cloud Composer.
5. Further, based on the data from the staging layer, a storage layer is built in which the data undergoes some transformation — all fields have the appropriate types, time stamps are brought into the same time zone, some tables are intentionally denormalized, additional keys are added for the ease of linking tables, etc. This layer is optimized for SQL queries used by analysts and product managers.
6. There is also a data_marts layer. It stores data marts for later use in various reports and downloads. Data marts are built using data from both the storage and staging layers.
7. The company builds BI reporting in Google Data Studio and Power BI.

In addition, the client has separate datasets for machine learning and sandboxes for individual teams in which they can create tables and views for their internal tasks without the help of the DWH team.

Thanks to the solution described here, our client was able to significantly reduce the workload for the analytics department. Employees no longer collect and clean data from various sources to compile marketing reports. The culture of working with data has improved, and data has become more available to all employees.

The number of employees who actively use Google BigQuery to find answers to their questions (write queries and create visualizations in Google Data Studio) has grown to 11 (from four departments). Prior to the creation of the repository, only three analysts could do this.

Previously, the company mainly used three data sources for analytical needs: Google Analytics, a MySQL database, and separate expense documents. Now they have more than 10 data sources, and the volume of data processed has grown as well.

With OWOX BI, the marketing department received convenient tools for rapidly evaluating its work. Now marketers can more accurately track the cost of attracting users and, most importantly, ROI. Also, cooperation with partners using the revenue share model has become more transparent and understandable for both parties.

The client continues to use OWOX BI products to solve their current problems. In the future, when switching to Google Analytics 4, the company plans to enlist the OWOX team to analyze the exported data and the construction of session tables.