Valiotti Analytics

Goal

Worldcoin, an international startup aiming to provide equal economic opportunity, needed a custom solution to collect and visualize data for tracking project progress and making data-driven decisions. Box solutions were inadequate due to the need for tracking dynamic user actions by cohort.

We had experience building various dashboards using SQL queries in Metabase, including Devices, Main, Mini-app, and Operations dashboards. These featured graphs on retention rates, average payments per user, and account creation across different time periods, countries, and applications, with filters for easy data analysis.

Worldcoin approached us to build several dashboards based on their databases, including:

• Main Dashboard with Overall, Access, Verification, Payments, Lifecycle, and User Profile Metrics
• Vesting Model Experiment Dashboard with General, Variant C Mechanics, and Behavioral Effects Metrics
• Product Metrics Dashboard with Overall, Distributor, Individual Distributor, Consumer, and Launch Metrics
• Location Dashboard

Results

1. Project Immersion

Before starting, we studied Worldcoin's project in detail. Understanding the client's database entities was crucial, so we built an ER-diagram to map the system's elements and their relations.

2. Task Refinement

Worldcoin provided a detailed description of the desired outcomes and types of data visualizations. This clarity helped us achieve better results. We analyzed Worldcoin's files, suggested changes to some charts, and added new variables.

3. Data Visualization

We assembled over 10 dashboards in Metabase, enabling Worldcoin to make strategic decisions. These dashboards helped the product team evaluate the business state, track product metrics, analyze the audience, and make data-driven decisions.

Examples of these dashboards include those tracking overall metrics, specific experiments, product performance, and location-based data.

Goal

• Visualize data on Tableau dashboards for enhanced analytics and decision-making.
    - Track credit issuance to identify discrepancies and fraud.
    - Visualize insurance plan allocation, ARPU, and total revenue by state and zip code to refine marketing efforts.
    - Provide insights on shipment allocation to optimize the supply chain across the US.
• The client lacked the expertise to build these dashboards and chose Valiotti as a reliable data analytics expert.

Results

1. Data-rich dashboards in Tableau

Based on the acquired information and queried databases, we built several dashboards in Tableau.

• The Owed and Applied Credits dashboard shows the number of credits issued, including the applied percentage and unique credits, per month

The dashboards also provide information on specific users. They can be analyzed by user type, the cost of credit per user, the data range, and the outlier ratio for the Client to provide a comprehensive outlook of credit issuing and user purchasing behavior.

• The Plan Allocation dashboards cover subscription details and allocation, ARPU, and Revenue.

• Insurance dashboards showcase the number of insurance contract additions, cancellations, and active insurance contracts on a daily, weekly, and monthly basis. What is more, we analyzed insurance plan allocation by revenue, number of subscriptions, and users for different insurance plans and networks.

2. Actionable data-based insights into top shipment hubs

We built a heatmap of shipments and transit times, analyzing warehouse results. This revealed potential new hubs and cities with low transit times, including some distant cities with surprisingly short delivery times.

We also analyzed the supply chain across US cities and states to identify the best ship-from locations with minimal transit times.

As a result, shipment dashboards helped the client identify optimal shipment locations, pinpoint regions with the fastest transit times, and determine a new warehouse location to reduce delivery times.

Goal

The Client sought top-tier data engineering professionals with deep expertise in cloud providers and cloud databases to build a customized data pipeline. Without in-house expertise, they needed a remote expert with vast hands-on cloud data stack experience. They chose Valiotti for our years of experience in setting up cloud databases and implementing data engineering solutions. Additionally, the Client wanted to visualize raw data in Looker to help customers track telephony services and improve the end-user experience.

Results

Mapping and Implementing the Data Pipeline

We analyzed the Client’s telephony sector, interviewed key employees for raw data storage parameters, and investigated their AWS environment and S3 Data Lake Structures. We then converted raw data from the AWS S3 bucket into an Amazon Redshift database, tuning AWS Glue to work with S3 Datalake and building a data pipeline using Python and AWS Glue to transform the data into Redshift. The data was then sent to Looker for visualization.

Building a LookML Model for Redshift

To enable real-time inbound analytics in Looker, we set up SQL Runner for the Redshift data model. This allows Aircall customers to access flexible dashboards in Looker covering:

• Missed Calls: Number of missed calls, reasons for missed calls (e.g., agent unavailability), and when customers abandoned calls (e.g., during the welcome message, IVR, or waiting time). This helps Aircall investigate incidents and reduce missed calls.
• Missed Calls by Time: Scope of missed calls by day or hour.
• Total Missed Calls: Overall count of missed calls.

Looker dashboards are customizable and can be adjusted according to the Client’s preferences or needs.

Goal

The Client aimed to foster data-based decision-making to enhance their sales and product development but lacked internal resources to build a data analytics infrastructure, select the right tools, and set up processes. Refocus sought a reliable partner with extensive expertise and attention to business goals. Valiotti met these requirements and was hired for the task.

During the project, we faced a challenge when Refocus’s team modified data sources without notifying us, causing issues for our data analysts. We resolved this by requesting Refocus to avoid altering any data that might be streamed into the data pipeline.

Results

1. Scalable Data Analytics Infrastructure

After analyzing the Client’s business goals and data collection requirements, we developed a tech stack that included:

• Python scripts
• Hetzner server for storing data collection scripts
• Airflow for workflow management
• Google BigQuery as a data warehouse
• Tableau for data visualization

Using Airflow and Python scripts, data is exported, processed, and stored in Google BigQuery via API. Airflow, based on a virtual machine, manages the workflows. All data is visualized in Tableau reports.

2. Numerous Regularly Updated Dashboards for Actionable Insights

The Client can build both generic and specialized reports to make data-driven decisions. Key dashboards include:

• Overview Student Dashboard: Shows dynamics of student enrollment and other crucial metrics.
• Optimized Lead Allocation: Analyzed lead funnels and reallocated them to increase the conversion rate, speeding up conversions from new deals to sales.
• Refund Analysis: Identifies top reasons for refunds, allowing the Client to address issues and minimize refund requests.
• Performance Monitoring: Dashboards illustrating student performance help monitor progress and identify difficult tasks and lessons, providing insights for data-based product development to increase course completion rates.

Goal

SkyCoach sought product insights to improve their service by creating Power BI reports to monitor metrics across various time periods and games. Serving over 20 online games, they needed a partner to set up the analytical infrastructure and guide their team post-project due to the absence of an in-house data analytics team.

Results

1. Setting up Airbyte on a Remote Server for Data Integration

The Valiotti team set up Airbyte, a data integration platform, on a remote server to connect two databases:

- Source: The production database from which data is copied.
- Destination: The final database containing tables for reports.

Our team examined the source database, selected tables, wrote SQL requests, and scheduled table updates according to SkyCoach’s business needs. Later, SQL requests were written.

2. Building Data-Rich Reports for Enhanced Monitoring and Decision-Making

Analyzing SkyCoach’s requests and needs, we drafted future reports with key visualization types and data to be analyzed. After refining the drafts, we created Power BI reports. SkyCoach received the following reports:

- Active Pro Gamers: Number of active pro gamers by period, and by game to understand specific service demand.
- Pro-Player Selection Speed: Analysis by timespan and game to identify and address pain points.
- Ratings Overview: Comprehensive ratings by managers, pro gamers, and users, with average figures, to analyze service quality by game.
- Auction Insights: Percentage of orders accepted on the first try, order duration at the auction stage, and number of orders in an auction.
- Dispute Analysis: Dispute percentages by outcomes (lost, won, others), analyzed by game and timespan to identify bottlenecks and improve service.
- Refund Analysis: Percentage of refunds by type (full and partial).

We also provided extensive documentation to enable SkyCoach’s team to carry out analytical processes independently.

Goal
The Client’s project, Cloudista, which is used for data harvesting through cloud technologies, failed to perform correctly. The Python-based project is an ETL that exports marketing data from Scalista users’ accounts to GCP BigQuery. The data should be used to build Tableau reports, but the process kept failing.
Scalista’s specialist left the company, and they had no more resources to solve the task. The Client turned to Valiotti because of our experience in the tech stack (Python, Google BigQuery, Google Sheets) and process automatization.

During the project, two new tasks were set: to QA Cloudista’s performance and revise the ETL project.

Results
1. We designed a new approach to data transfer from BigQuery to Tableau via Google Sheets.

Firstly, we conducted research to find out the root of the problem—why it was impossible to connect BigQuery to Tableau. The reason was that the system used a custom request with a lot of calculated fields to enable the connection. It failed to deliver due to a limited request length.

To solve the problem, we developed a new approach: with the help of a custom request, the data from BigQuery was regularly exported to Google Sheets. Then, Google Sheets was easily connected to Tableau, which allowed for building analytical reports.

2. The Cloudista performance was refined

During the project, we discovered some ETL-related problems. Since the project is coded in Python, and the versions of the used libraries were updated, we needed to make the appropriate changes. After studying the code, project documentation, technologies, and the libraries’ changelogs, we edited the code for flawless performance.

What’s more, we compared the data from Facebook and Google Ads with the data sourced by the ETL project. Some modification of data processing was required.

Goal

betPawa, a sports betting company, managed extensive OLTP data using a DWH system with 24/7 ETL processes. However, they faced issues such as time-consuming support, missing OLTP data in reports, and inflexibility for new business processes. ETL processes sometimes ran overnight but failed to deliver timely results, and reports relied on outdated DWH data, hindering in-depth analysis. To address these challenges, betPawa enlisted Valiotti Analytics for our expertise in BI reporting, DWH, and ETL design.

Results

Flexible & Scalable DWH System

After analyzing betPawa's DWH system, data structure, and processes, we redesigned with:

- Extended DWH: New fact tables for varied data sources.
- Data Flow: Shifted to Kafka for real-time updates; used MaxWell’s Daemon for MySQL integration.
- Data Quality: Validated ETL rows; tracked inconsistencies via ETL logs in Redash.
...and more

Data Transfer Methods

We tested four OLTP data transfer methods:

1. Materialized MySQL: Replicates MySQL tables into Clickhouse but incompatible with Percona Update tool.
2. Full and Incremental Load: Directly accesses MySQL data via Clickhouse SQL, ideal for small tables or indexed incremental loads.
3. Kafka Topics: Sends corporate data to Kafka in Google’s Protocol Buffers format for extraction by Clickhouse’s Kafka Engine.
4. MySQL Transactions to Kafka Events: Captures data from MySQL transaction logs (binlog), pushing events to Kafka.

Improved Data Accuracy and Real-Time Insights

By utilizing Kafka and MaxWell’s Daemon tool, we achieved standardized processing and real-time updates. This enhanced processing for fact and dimensional tables prevented data duplication, ensuring data accuracy.

A New BI Tool – Redash

We tested, trained, and deployed Redash in production. Reports initially created in Tableau were recreated in Redash. Following extensive testing and bug-fixing, the Client accessed improved data visualization and reporting capabilities.

Goal
MentorShow, an Edtech provider of exclusive online courses, had already set up data processing and analytical processes. However, the quality and accuracy of the calculations left much to be desired. The client set several project objectives:

To revise the existing KPIs and improve their quality
To find new optimized ways to obtain necessary data
To analyze a set of new metrics
To revise the logic of data processing
To build informative dashboards
The EdTech startup lacked the expert internal resources needed to complete the task. Hence, they turned to Valiotti, a Tableau expert with extensive experience in setting up SQL and ETL processes.

Results

1. Refined data processing

After Valiotti’s team immersed itself in the Client’s subject area, we got acquainted with the available data, processes, and product features. At this stage, we worked both independently and together with the startup’s team.

We developed new raw data processing logic without using legacy code and created Python and SQL scripts. They run daily and allow the Client to obtain the necessary data for analysis (business KPIs and data tables). We also revised and updated the existing scripts for seamless performance.

2. A variety of data-rich dashboards for comprehensive decision-making

After schematization of data obtained from new sources, we built several dashboards with accurate calculations. They are regularly updated and allow for a comprehensive outlook. Previously, the Client needed to gather data from several reports manually. Now, the Client can track changes in indicators effectively to promptly make decisions on product development strategies based on meaningful insights.

This Edtech case study includes just a few examples of dashboards:

• Retention report to analyze planned vs. actual subscription renewals, retention rates, and churn rates over time. For example, the dashboard gave the Client deep insights into when users churn and some possible reasons for it. Based on this data, the Client can make data-based decisions to improve user retention.
• Marketing KPIs report to analyse revenue, marketing expenses by platform, revenue and expenses per pass, ROI, CAC, and other KPIs.
• Investor’s dashboard with such data as the number of subscriptions, including new ones, AOV/pass, CAC, LTV/CAC, payback time, retention, marketing expenditures, new signatures, courses, shootings, etc.
• Dashboard with information about subscribers, including their active periods, user name, email, ID, etc.
• A dashboard for a cohort analysis (Client consumption analysis)