Airbook & Amplitude Analytics: 5 Essential SQL Metrics for User Engagement Analysis

Rishikesh Ranjan
November 27, 2023
Metrics

Introduction

Today understanding your users is more important than ever. This is where user engagement metrics come into play, serving as the compass guiding you through the complexity of user behavior and preferences. By tracking how users interact with your product or service, these metrics offer invaluable insights, helping you refine your strategies, enhance user experience, and ultimately drive growth.

Enter Amplitude and Airbook - two pivotal tools in data analytics. Amplitude Analytics shines as a beacon in user behavior analytics or Product Analytics, offering a comprehensive view of how users engage with your products. It is a high-powered microscope that zooms into the user journey, revealing patterns and trends that might otherwise go unnoticed. Whether it’s measuring the frequency of app usage or tracking the journey of a user through a website, Amplitude provides the granular data necessary for informed decision-making.

Complementing Amplitude, Airbook stands as a versatile and powerful ally. It's not just a tool; it's a canvas where data transforms into insights. Airbook’s usecase lies in its ability to seamlessly integrate with platforms like Amplitude Analytics, enabling teams to go deeper into data through advanced SQL capabilities. With Airbook, the raw data from Amplitude changes from just numbers and charts to a story - a narrative about your users, their needs, and their behaviors.

In this blog about user engagement metrics, we'll see how Amplitude Analytics and Airbook together unlock a treasure chest of insights. Whether you're a seasoned data analyst or just starting out, understanding and using these tools is a game-changer in how you perceive and react to your user's needs.

What is Amplitude?

Amplitude is a cutting-edge analytics platform designed to track and analyze user behavior across digital products. At its core, Amplitude Analytics excels in uncovering how users interact with your app or website, providing deep insights into their actions, preferences, and journey patterns. It's offers a clarity on what drives engagement and retention.

Key features of Amplitude Analytics include real-time analytics, comprehensive user segmentation, and detailed event tracking. These capabilities allow you to observe user interactions at a granular level – from a simple button click to complex navigation paths. This insight is crucial for optimizing product features, tailoring user experiences, and ultimately fostering a more engaging and satisfying interaction for the users.

What is Airbook?

Airbook is a dynamic data analysis and business application tool, designed to transform raw data into actionable insights. It has an ability to integrate and analyze data from various sources, including platforms like Amplitude Analytics. With its robust SQL capabilities, Airbook helps you to perform complex data queries, visualize results, and create comprehensive reports with ease.

The synergy between Airbook and tools like Amplitude Analytics is where its true strength lies. By harnessing the detailed user behavior data from Amplitude, Airbook enables deeper dives into analysis. This combination allows you to not only understand what users are doing but also to explore the 'why' behind their actions. It's this powerful blend of data collection and analysis that makes Airbook an indispensable asset for businesses looking to leverage data for strategic decisions.

User Engagement Metrics to Track

Understanding user engagement is pivotal in tailoring your product to meet user needs. By leveraging Amplitude Analytics data with Airbook, you can track key metrics to gain insights into user behavior. Here's a look at some essential user engagement metrics and their significance, along with SQL queries to retrieve them:

Daily Active Users (DAU)

Definition: The number of unique users who engage with the app in a day.

DAU is a fundamental metric to gauge the daily engagement level of your app. It helps in understanding the daily reach and consistency of user interaction with your product. A healthy DAU indicates a compelling daily value for users.

SQL Query
SELECT COUNT(DISTINCT user_id) AS daily_active_users
FROM amplitude.event
WHERE DATE(client_event_time) = 'YYYY-MM-DD';

Weekly Active Users (WAU)

Definition: The number of unique users who engage with the app in a week.

WAU extends the engagement analysis to a weekly perspective. This metric is essential for understanding user retention and the week-over-week stickiness of your app. It's particularly useful for identifying weekly usage patterns and trends.

SQL Query
SELECT COUNT(DISTINCT user_id) AS weekly_active_users
FROM amplitude.event
WHERE DATE(client_event_time) BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'; 
-- Replace with start and end dates of the week
    

Monthly Active Users (MAU)

Definition: The number of unique users who engage with the app in a month.

MAU provides a broader view of user engagement over a month. This metric is crucial for long-term strategic planning and understanding monthly user growth or decline. It gives insights into the monthly loyalty and adoption of your app.

SQL Query
SELECT COUNT(DISTINCT user_id) AS monthly_active_users
FROM amplitude.event
WHERE DATE(client_event_time) BETWEEN 'YYYY-MM-01' AND 'YYYY-MM-31'; 
-- Replace 'YYYY-MM' with the specific month and year
    

Average Session Length

Definition: The average length of a user session, from the first to the last event in each session.

This metric sheds light on user engagement depth. Longer sessions typically indicate higher engagement and user interest. It's a vital indicator of how captivating and user-friendly your app is.

SQL Query
SELECT AVG(session_length) AS avg_session_length
FROM (
    SELECT session_id, 
           TIMESTAMPDIFF(SECOND, MIN(client_event_time), MAX(client_event_time)) AS session_length
    FROM amplitude.event
    GROUP BY session_id
) AS session_durations;
    

Event Frequency Per User

Definition: The average number of events triggered by each user.

This metric helps in understanding how actively users are interacting with specific features of your app. A higher event frequency can imply more engaged and invested users. It’s crucial for identifying which features are most and least engaging.

SQL Query
SELECT AVG(event_count) AS avg_event_per_user
FROM (
    SELECT user_id, 
           COUNT(*) AS event_count
    FROM amplitude.event
    GROUP BY user_id
) AS user_events;
    

Step-by-Step Guide: Generating Metrics using Airbook and SQL

Diving into data analytics with Airbook and SQL to extract valuable user engagement metrics from Amplitude Analytics data is a powerful way to gain insights. Follow this detailed step-by-step guide to navigate through the process.

Sign into Your Airbook Account:

Start by logging into your Airbook account. If you don’t have an account yet, you can easily sign up for one by clicking the "Try for free->" button in the footer of this page.

signin to Airbook

Navigate to Connections

Once logged in, head over to the 'Connections' section in the Airbook interface. This is where you'll find external data sources to link to.

Airbook Connections Page

Connect to Amplitude

In the 'Connections' section, search for Amplitude. Select it to begin the process of linking Amplitude Analytics with Airbook. You will need to provide the necessary credentials and configurations for a successful connection.

Search for Amplitude

Add Schema Name

During the connection setup, you’ll be prompted to add a schema name. This is important for organizing and accessing your data in Airbook.

Name Your Schema

Let the Amplitude Data Sync

After setting up the connection, the Amplitude Analytics data will begin to sync with Airbook. This process can take anywhere from 5 to 30 minutes, depending on the volume of data in your Amplitude Analytics account. Patience is key here! You will see the update on the bottom right that your "Amplitude is Syncing" or "Amplitude Sync is completed"

Create a Project and an Airbook

Once the data is synced, it’s time to create a new project in Airbook. Within this project, you will create an 'Airbook' – your workspace for analysis and visualization. Once you click on "+ Add Project", it will also create and add a new Airbook Page under that Project.

Add new project

Add an SQL Block

In your Airbook, add an SQL block. This is where you’ll write or paste and execute your SQL queries. Select the data source as the "amplitude.event" data you’ve synced.

example code from demo data

Start Querying

Now, use the SQL queries for the metrics mentioned earlier in the blog. Paste the SQL code into the SQL block. Remember, you may need to optimize the SQL query to align with your specific data analytics needs. Adjust the date ranges and other parameters in the query to fit your data.

By following these steps, you'll be able to effectively use Airbook and SQL to generate insightful user engagement metrics from your Amplitude Analytics data. This process not only simplifies the data analysis workflow but also empowers you with the ability to make data-driven decisions.

Conclusion

In this blog, we've discussed the crucial role of user engagement metrics in understanding user behavior and the synergistic power of using Amplitude Analytics and Airbook Analytics to generate these insights. We started by introducing Amplitude, a robust tool for tracking user interactions, and Airbook, which enhances data analysis through its advanced SQL capabilities. The discussion then highlighted key metrics like Daily Active Users (DAU), Weekly Active Users (WAU), Monthly Active Users (MAU), Average Session Length, and Event Frequency Per User – each offering unique insights into user engagement.

The step-by-step guide provided a practical pathway to harness these tools, demonstrating how to seamlessly integrate Amplitude Analytics data into Airbook and extract meaningful metrics using SQL queries. This process not only simplifies but also amplifies your data analysis capabilities.

As you start your journey with these powerful tools, I encourage you to experiment and explore beyond the metrics discussed here. Amplitude's data, when analyzed through Airbook, will yield a multitude of other metrics such as user retention rates, conversion rates, and user lifetime value. Each of these metrics unlock further understanding of your users and contribute to more informed, data-driven decision-making.

The world of data analytics is vast and ever-evolving. By using the combined strengths of Amplitude and Airbook, you're well-equipped to tap into this world, uncover valuable insights, and drive your business strategies forward with confidence.

FAQs

What is Amplitude Analytics and how does it help in user engagement analysis?
How does Airbook complement Amplitude's capabilities?
What are some key user engagement metrics that can be tracked using Amplitude Analytics and Airbook?
Can I customize the SQL queries provided in the blog for my specific data analysis needs?
How long does it typically take for Amplitude Analytics data to sync with Airbook?
Are there any other metrics I can analyze using Amplitude Analytics data on Airbook?
Similar Blog Posts

All Blog Categories