Как сделать Lookalike Audience в SQL

Зачем Lookalike Audience

Lookalike («похожая аудитория») — это аудитория, похожая на ваших лучших клиентов. Идея простая: если найти пользователей, которые по своим характеристикам напоминают тех, кто уже платит много, то и рекламу на них крутить выгоднее — конверсия обычно выше, чем по холодной аудитории.

В рекламных кабинетах Facebook и Google lookalike строится на эмбеддингах и закрытых ML-моделях платформы. Но аналитику часто нужен свой lookalike — например, чтобы выгрузить сегмент для email-рассылки или push, или когда рекламной платформы под рукой нет. В SQL это делается через сопоставление признаков: берём профиль ценных клиентов и ищем непохожих на них похожих. На собеседовании маркетинг-аналитика такую задачу дают, чтобы проверить, умеете ли вы формализовать нечёткое «найди похожих» в конкретный запрос.

Базовая идея

Алгоритм всегда один и тот же, меняется только способ измерения сходства:

1. Определить seed — «затравку», то есть эталонную группу ценных клиентов.
2. Посчитать профиль этой группы: средние значения и распределение признаков.
3. Оценить остальных пользователей по степени сходства с этим профилем.
4. Взять топ X% самых похожих — это и есть lookalike-аудитория.

Дальше разберём каждый шаг с запросами.

Определяем seed-когорту

Seed — фундамент всего. Если сюда попадут не те люди, похожие на них тоже окажутся не теми. Обычно в seed берут клиентов с высокой ценностью — например, тех, кто суммарно принёс больше определённого порога выручки:

WITH seed AS (
    SELECT
        user_id,
        age,
        country,
        device,
        signup_source
    FROM users
    WHERE user_id IN (
        SELECT user_id FROM transactions
        WHERE status = 'paid'
        GROUP BY user_id
        HAVING SUM(amount) >= 500  -- порог высокой ценности
    )
)
SELECT
    age,
    country,
    AVG(amount) AS avg_revenue
FROM seed
GROUP BY age, country;

Порог (500 в примере) подбирают под бизнес — это может быть топ-10% клиентов по выручке или те, кто оформил больше N заказов.

Сопоставление по признакам

Самый простой способ найти похожих — сопоставить пользователей по совпадению ключевых признаков: страна, устройство, возрастная группа, источник привлечения. Строим профиль seed по этим признакам и джойним к нему остальных пользователей:

WITH seed AS (
    SELECT *
    FROM users
    WHERE has_paid_premium = TRUE
),
seed_profile AS (
    SELECT
        country,
        device,
        AGE_BUCKET(age) AS age_bucket,
        signup_source,
        COUNT(*) AS seed_count
    FROM seed
    GROUP BY 1, 2, 3, 4
)
SELECT
    u.user_id,
    u.email,
    sp.seed_count AS lookalike_score
FROM users u
JOIN seed_profile sp ON sp.country = u.country
  AND sp.device = u.device
  AND sp.age_bucket = AGE_BUCKET(u.age)
  AND sp.signup_source = u.signup_source
WHERE u.user_id NOT IN (SELECT user_id FROM seed)
ORDER BY sp.seed_count DESC;

Логика такая: чем больше ценных клиентов имеют такую же комбинацию признаков, тем выше lookalike_score у пользователя с этой же комбинацией. Возраст здесь разбивают на группы (AGE_BUCKET), потому что точное совпадение по годам почти никогда не встречается — сопоставлять нужно по интервалам.

Косинусное сходство (продвинутый вариант)

Сопоставление по точному совпадению признаков грубое: пользователь либо попал в комбинацию, либо нет. Точнее — представить каждого пользователя вектором числовых признаков и мерить, насколько его вектор близок к «центру» seed-группы. Ниже упрощённое косинусное сходство (в предположении, что признаки уже нормализованы):

WITH seed_centroid AS (
    SELECT
        AVG(feature_1) AS f1_mean,
        AVG(feature_2) AS f2_mean,
        AVG(feature_3) AS f3_mean
    FROM user_features
    WHERE is_seed = TRUE
)
SELECT
    u.user_id,
    -- Косинусное сходство (упрощённо, признаки нормализованы)
    (u.feature_1 * s.f1_mean + u.feature_2 * s.f2_mean + u.feature_3 * s.f3_mean) AS similarity_score
FROM user_features u, seed_centroid s
WHERE u.is_seed = FALSE
ORDER BY similarity_score DESC
LIMIT 10000;

Здесь seed_centroid — усреднённый «портрет» ценного клиента, а similarity_score тем выше, чем ближе вектор пользователя к этому портрету. Это уже даёт ранжирование, а не бинарное «похож / не похож».

Скоринг похожих

Ещё один подход — считать не абсолютное число совпадений, а вероятность: какая доля людей с такой комбинацией признаков попадает в seed. Так признаки, характерные именно для ценных клиентов, получают больший вес:

WITH features AS (
    SELECT
        user_id,
        country,
        device,
        AGE_BUCKET(age) AS age_bucket
    FROM users
),
seed_probabilities AS (
    SELECT
        country,
        device,
        AGE_BUCKET(age) AS age_bucket,
        COUNT(*) FILTER (WHERE is_seed) AS seed_count,
        COUNT(*) AS total
    FROM users
    GROUP BY 1, 2, AGE_BUCKET(age)
)
SELECT
    f.user_id,
    s.seed_count::NUMERIC / NULLIF(s.total, 0) AS lookalike_likelihood
FROM features f
JOIN seed_probabilities s USING (country, device, age_bucket)
WHERE NOT EXISTS (SELECT 1 FROM users WHERE user_id = f.user_id AND is_seed)
ORDER BY lookalike_likelihood DESC;

lookalike_likelihood — это доля seed внутри сегмента с данной комбинацией признаков. Если среди пользователей с определённым набором (страна + устройство + возраст) 40% оказались ценными клиентами, то и новый пользователь с таким набором получит скор 0.4. NULLIF(s.total, 0) защищает от деления на ноль в пустых сегментах.

Частые ошибки

Ошибка 1. Слишком узкий seed. 50 клиентов — слишком маленькая база: профиль получится шумным и нестабильным. Для устойчивого lookalike нужно хотя бы 500 клиентов в seed, лучше больше.

Ошибка 2. Устаревший seed. Обновлять seed раз в год — нормально, если бизнес стабилен. А вот пересобирать его каждый день — плохо: модель начнёт переобучаться на случайные всплески и терять устойчивость.

Ошибка 3. Сырые признаки. Признаки вроде «зарегистрировался во вторник, а не в среду» — это шум, а не сигнал. В сопоставление стоит брать только осмысленные признаки, которые реально связаны с ценностью клиента.

Ошибка 4. Lookalike поверх разных стран. «Кит» из США и «кит» из Индии — совершенно разные профили по чеку и поведению. Смешивать их в один seed нельзя: lookalike нужно строить отдельно по крупным сегментам (страна, платформа).

Ошибка 5. Пересечение с уже охваченными. Если не исключить из lookalike тех, кто уже клиент или уже в рекламном таргетинге, вы будете тратить бюджет на людей, которых и так охватываете. Обычно уже сконвертированных и текущих клиентов из lookalike убирают.

Связанные темы

• Как сделать customer segments в SQL
• Как посчитать propensity score в SQL
• Как посчитать RFM segmentation в SQL
• Как посчитать LTV в SQL

FAQ

Что такое lookalike-аудитория?

Это аудитория, похожая на заранее выбранную группу ценных клиентов (seed). Приём используют в маркетинге для таргетинга: вместо холодной аудитории рекламу показывают людям, которые по характеристикам напоминают тех, кто уже приносит деньги, — за счёт этого конверсия обычно выше.

Какой минимальный размер seed?

Для устойчивых паттернов лучше 500–1000 клиентов. Facebook формально позволяет строить lookalike от 100 человек в seed, но чем больше и однороднее база, тем стабильнее результат. На маленьком seed профиль легко «поймает» случайные признаки.

Чем отличается lookalike в Facebook Ads от SQL-подхода?

Facebook строит lookalike на эмбеддингах и внутренних ML-моделях — это заметно точнее, потому что платформа видит поведение пользователей, которого у вас в базе нет. SQL-подход — быстрая и простая альтернатива для случаев, когда рекламной платформы нет или нужно выгрузить сегмент под свою рассылку.

Как часто обновлять lookalike?

Обычно раз в месяц — этого хватает, чтобы учесть новых ценных клиентов и не переобучаться на шум. Для сезонного бизнеса разумнее пересобирать раз в квартал, привязываясь к сезонам спроса.

Чем lookalike отличается от ретаргетинга?

Ретаргетинг работает с людьми, которые уже взаимодействовали с продуктом (заходили на сайт, бросили корзину), — задача вернуть их. Lookalike, наоборот, ищет новых людей, похожих на клиентов, но ещё не контактировавших с продуктом. Это разные этапы воронки: ретаргетинг — тёплые, lookalike — холодные, но перспективные.

Тренируйте SQL и продуктовую аналитику — откройте тренажёр с 1500+ вопросами для собесов.