Чи знаєте ви, що найкраще робити різдвяним ранком? Вивчення SQL.

Останнє оновлення цього року від мене, і, можливо, найважливіше – SQL!

Сьогодні я ділюся коротким оглядом SQL зі своїми рішеннями для звичайних проектів аналітики даних, через які мені доводилося багато разів проходити протягом мого перебування в якості аналітика. Сподіваюся, це допоможе вам просунутися та заощадить час. Читайте нижче:

• SQL для отримання змішаного утримання та когортного утримання

• SQL для рейтингу найпопулярніших функцій продукту

• SQL для аналізу частоти використання

• SQL для аналізу використання продукту в робочі дні

• SQL для отримання середнього входу на користувача на день

• SQL для отримання суміжної персони користувача

• SQL для отримання випадкового набору користувачів для ML і аналітики

• SQL для зведення JSON

• Як моделювати таблиці для звітності про підписку

• Як об’єднати 2 таблиці, не пов’язані зовнішнім ключем

У мене є близько 4-5 різних запитів SQL для збереження, залежно від структури таблиці, визначення збереження та продукту. Більшість команд аналітики створюють моделі для збереження в певному форматі. Наприклад, зазвичай створюють активний_користувач таблиці та призначте позначки кожному користувачеві для R(D1), R(D7), R(День 30) тощо. Наявність такої консолідованої таблиці спрощує отримання даних про збереження, оскільки вам не потрібно виконувати обчислення або використовувати оператори CASE в кожному запиті SQL, щоб отримати KPI збереження. Однак для створення цих переглядів потрібен час.

Ось деякі з моїх прикладів SQL для змішаного та когортного утримання – Як виміряти утримання когорти.

Ви не можете просто зайти й виконати запит, щоб раптово отримати найпопулярніші функції. Вам потрібно, щоб активність користувача вже відображалася в таблиці функцій, що не є тривіальним, коли ви отримуєте дані про діяльність із потоків подій. Трансляція необроблених подій у визначені дії користувача (наприклад, завершення реєстрації, використання інформаційної панелі, журналювання, перегляд елементів тощо) потребує часу. Крім того, об’єднати та зіставити ці дані з підписками, DAU або атрибуцією ще складніше.

Найчастіше команди просто зберігають дії користувача (такі як cta_click, screen_view, profile_updated тощо) як проксі для функцій. Ви можете мати стіл з вид_діяльності що включає реєстрацію, покупку, вхід, home_screen_view, profile_updated та іншу деталізацію у вигляді необроблених подій і дій, які користувачі створюють під час використання вашого продукту. Але користувач діяльність не те саме, що a особливість продукту. Діяльність може містити підказки про те, до яких батьківських функцій користувач натискає та переглядає. Якщо вам пощастить менше, вам потрібно буде зіставити наявні у вас дані про дії з функціями. У будь-якому випадку, ви повинні почати з отримання вищого типу активності, а потім працювати над їх зіставленням з функціями.

Ось як я це роблю разом із моїм рішенням SQL для отримання часто використовуваних функцій – Рейтинг найпопулярніших функцій продукту.

Ваш продукт може бути призначений для епізодичного використання замість щоденного (Airbnb, Lyft, Tinder, Dropbox, LinkedIn), тому ваше співвідношення DAU/MAU може бути невеликим незалежно від великого обсягу використання, що не дуже корисно для ілюстрації вашого справжнього залучення користувачів . Швидше за все, у вас є кілька невеликих груп досвідчених користувачів, які керують щоденним використанням, тоді як інші персони продукту переважають у двотижневому або місячному використанні.