Jul 13, 2024
⏱️ 70% Time Reduced in Data Discovery
🗄️ 670+ Folders Saved in Sherloq
📁 1600+ Queries Saved in Sherloq
Executive Summary
Core locations: USA, Israel, London, France, Thailand, Japan, China, India
Headquarters: San Francisco, US
Industry: Mobile Attribution and
#of Employees: 1,000+
#of Data users: 150+
Business Model: B2B2C
Data stack: BigQuery, Athena S3, Looker, Tableau, Jenkins
Main Points:
Challenge:
The main challenge was that two different data analysts would provide two different answers to the same business question. Managing over 10,000 tables led to an absence of standardization, a lack of knowledge, and reliance on tribal knowledge. Additionally, team collaboration was hindered as each analyst worked independently rather than together, leading to a lack of cooperation between analysts and resulting in inconsistency and productivity issues that were significantly time-consuming.Initial Solutions:
Tried an internal “Wikipedia” for data assets and external tools like GitHub and Google Docs, which proved inadequate due to maintenance burdens and poor workflow integration.
Implementation of Sherloq: Quickly integrated into AppsFlyer’s data systems, streamlining query management and encouraging the saving and reusing of queries.Results:
Achieved a 70% reduction in time spent searching for data assets, organized 1,600+ queries into 670+ folders, and facilitated rapid adoption across various departments.
Impact:
One source of truth for data analysts’ daily SQL work, resulting in improved productivity, creating data alignment, and reducing SQL errors. Additionally, the number of tables created has decreased, and analysts are increasingly using the same tables, promoting consistency and efficiency.
Sherloq has become a vital tool, driving efficiency and alignment across the organization.
Background
AppsFlyer helps brands make good choices for their business and their customers with its advanced measurement, data analytics, deep linking, engagement, fraud protection, data clean room and privacy preserving technologies. Built on the idea that brands can increase customer privacy while providing exceptional experiences, AppsFlyer empowers thousands of creators and 10,000+ technology partners to create better, more meaningful customer relationships.
To learn more, visit www.appsflyer.com.
More than 10,000+ tables, 2M queries per day
The Challenge
AppsFlyer, a data-intensive company, managed an overwhelming array of over 10,000 tables spread across multiple databases such as BigQuery and Athena S3. This complex data landscape posed several operational challenges for the company’s sizable team of more than 100 data users. Data users frequently struggled to identify the most relevant tables for their projects, and even when identified, they lacked the necessary context to use these tables effectively. “Every new project felt like starting from scratch.” said one of AppsFlyer’s data analysts.
This often resulted in users spending excessive time figuring out the business terms and logic required to manipulate SQL queries to get the data they needed.
Moreover, the absence of data ownership and proper documentation meant that routine queries turned into extensive, time-consuming discussions. AppsFlyer had established a dedicated Slack channel intended to facilitate quick help among the data users; “Who owns this table?” and “How can I query this table for a specific data type without constraints?” were part of these questions. That triggered multiple interactions, causing frequent context switches among team members. This not only slowed down individual productivity but also led to a cycle of repetitive and inefficient work.
The situation was further complicated by the fact that there was no standardized method for saving queries and the absence for one source of truth for the data. Data analysts found themselves repeatedly solving the same problems or answering similar ad-hoc queries. The lack of a centralized, easily accessible system for storing and retrieving past queries meant that even when solutions were found, they were often not recorded in a way that was easy to reuse. Consequently, precious time was lost as analysts would have to recreate queries from scratch, leading to further delays and disruptions in their workflow.
“Even having worked at a smaller company before joining AppsFlyer, with just over 50 employees, we faced the same frustrating delays in our workflows” said one of their data analysts.
This environment of inefficiency and frustration was exacerbated by a general lack of trust in the data. Without reliable documentation at the table, field, or query level, even well-designed dashboards were viewed with skepticism by stakeholders.
Last but not least – the lack of collaboration within the data team meant that many analysts were duplicating efforts. Analysts often worked in silos, unaware that the queries they were constructing had already been created by others. This lack of communication and collaboration led to significant productivity losses, with an estimated 30% of AppsFlyer analysts’ time spent on redundant tasks. Over a year, this added up to thousands of hours wasted, further compounding the inefficiency and slowing down the entire data operation.
Wiki Pages and Workarounds
Previous Attempts and Solutions
Initially, AppsFlyer tackled their data management challenges by attempting to create an internal tool, conceptualized as a sort of “Wikipedia” for data assets. The vision was ambitious: to craft a wiki page for each data asset, incorporating guidelines on best practices for querying tables and maintaining updated data entries. This internal wiki was intended to be a central repository where data users could find all relevant information about each table, field, and query. However, the project soon encountered significant hurdles. The development of such a comprehensive tool required substantial resources, including manual continuous updates and active contributions from all data users to keep the content current. Despite the initial enthusiasm, the effort required to build and maintain this system proved too burdensome for the team.
Moreover, in parallel with the wiki initiative, AppsFlyer experimented with other tools such as GitHub and Google Docs to manage their data queries and documentation. They hoped that these platforms would enable easier sharing and versioning of queries among the team. However, these solutions also fell short of expectations. The primary issue was that neither GitHub nor Google Docs was integrated into the data users’ daily workflows. Users found these tools to be external to their core operations, which created an additional layer of complexity. Instead of streamlining the process, these tools added steps and fragmented the users’ focus, ultimately leading to poor adoption and continued inefficiencies.
Solution
Integrated Into Their Workflow: Less Data Context Switch, More Centralized Data
Results and Implementation
Sherloq is the one place for all the orgs SQL queries.
Our AI-powered platform offers a GitHub-like SQL repository for data teams. It enables saving, sharing, and versioning SQL queries directly from the user’s existing workspace. For larger organizations, Sherloq also offers complimentary metadata management that enables auto-documentation for all data assets, allowing users to see assets usage and statistics, and ensuring a single source of truth.
AppsFlyer quickly integrated Sherloq into their data stack, significantly streamlining their query management within weeks. Initially, adapting to Sherloq involved a cultural shift towards a centralized approach to managing queries. Sherloq introduced gamification to encourage saving and reusing queries, making the tool engaging and demonstrating immediate benefits like time savings. Its intuitive interface and powerful search capabilities made it easy for users to adopt and difficult to abandon. Over time, Sherloq became central to AppsFlyer’s operations, fundamentally changing how their data teams managed and interacted with queries.
Since implementing Sherloq, AppsFlyer has seen a significant boost in productivity and efficiency. Data analysts have reduced the time spent searching for data assets by 70%.* In less than a year, AppsFlyer users generated more than 1,600 queries organized into over 670 folders within Sherloq, achieving an organization level previously unattainable. *This 70% reduction was estimated based on user feedback and time-tracking data collected before and after Sherloq’s implementation, highlighting the time saved in locating and managing queries.
Initially adopted by data analysts, Sherloq is now also used by product managers, support teams, and other departments to stay aligned with their data. This rapid adoption across various teams underscores Sherloq’s value and user-friendly nature.
This tool has not only improved query management but also transformed the onboarding process for new analysts. New hires now quickly familiarize themselves with company data tables, reducing training time and increasing immediate productivity. The support team highlighted that more than 15 business users actively use Sherloq, demonstrating its user-friendly nature.
