Я работаю аналитиком данных в команде роста в Эхо, где ключевая цель — повысить конверсию регистрации. Мы используем наборы данных воронки привлечения, чтобы получить представление о том, где пользователи уходят в процессе регистрации. Написание вручную всех SQL-запросов, необходимых для этого, потребовало бы много времени и было бы сложным в управлении. Использование комбинации SQL и JavaScript значительно упрощает этот процесс.

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

Этот пост призван объяснить шаги по созданию этого набора данных.

Шаг 1. Определите шаги воронки

Во-первых, нам нужно настроить структуру данных, которая определяет каждый этап воронки.

В файле с именем funneldata.js создается список, который будет содержать по одной записи для каждой воронки. Каждая воронка имеет имя (например, signup_funnel) и список шагов (например, Landing, Start_Signup, Verify_Email). У каждого шага есть имя и список треков, которые указывают, что пользователь достиг этого этапа воронки. В нашем случае мы используем определенные треки внешнего интерфейса, которые мы получаем от Segment. У некоторых шагов есть несколько путей, потому что есть несколько способов, которыми пользователь может достичь этого шага — например, на целевом шаге пользователи могут перейти на главную страницу, в Интернете или установить приложение на мобильном устройстве.

const funnels = [
  {
    name: "signup_funnel",
    steps: [
      {
        name: "landing",
        events: ["application_installed", "home_page"]
      },
      {
        name: "start_signup",
        events: ["signup_started"]
      },
      {
        name: "verify_email",
        events: ["email_verified"]
      },
      {
        name: "signup_complete",
        events: ["signup_complete"]
      }
    ]
  }
];
module.exports = funnels;

Шаг 2: Создайте одну таблицу за шаг

Далее мы создаем еще один файл с именем funnel_steps.js. Это создает одну таблицу для каждого шага воронки, которая имеет одну строку для каждого пользователя, и записывает, когда они впервые достигли этого шага воронки.

  1. Во-первых, мы ссылаемся на файл funneldata.js, созданный на предыдущем шаге.
const funnels = require("./funneldata.js");

2. Затем мы используем функцию ForEach JavaScript для прохода по каждой воронке (на данный момент у нас есть только одна, но мы можем добавить больше позже), а затем цикл для каждого шага воронки.

funnels.forEach(funnel => {
    funnel.steps.forEach((step, i) => {

3. Для каждого шага мы используем функции публикации и настройки (функции, специфичные для формы данных), чтобы создать таблицу в нашем хранилище данных с уникальным именем (объединенное имя воронки и имя шага).

funnels.forEach(funnel => {
    funnel.steps.forEach((step, i) => {

4. Мы добавляем переменную в SQL-запрос, что означает, что для каждого шага после первого предложение where будет иметь дополнительный фильтр для идентификатора пользователя, находящегося на предыдущем шаге. Это гарантирует, что воронка является последовательной, то есть пользователи должны пройти первый шаг, чтобы быть записанным на втором шаге, и так далее. Синтаксис ${ctx.ref( )} — это еще одна особенность, характерная для Dataform, которая гарантирует, что таблица предыдущего шага будет опубликована до текущей таблицы, чтобы обеспечить актуальность данных.

let where = "";
if (i > 0) {
  let previousStep = `${funnel.name}_${funnel.steps[i - 1].name}`;
  where = `and user_id in (SELECT user_id from ${ctx.ref(previousStep)})`;
}

5. Наконец, мы пишем запрос SQL, подключая переменные. Основная часть запроса жестко закодирована, так как она одинакова для каждого шага воронки. Переменные — это части, которые могут меняться для каждого шага: имя шага, номер шага, внешние треки для шага, которые мы определили в funneldata.js, и необязательный дополнительный фильтр с использованием предложения where.

return `
            SELECT * EXCEPT(row_number) FROM (
                SELECT
                user_id,
                "${step.name}" as step_name,
                ${i} as step_number,
                timestamp as step_started_at,
                row_number() OVER (PARTITION BY user_id ORDER BY timestamp asc) as row_number
                FROM ${ctx.ref("segment")}
                where lower(event) in ("${step.events.join('", "')}")
                ${where}
                GROUP BY 1,2,3,4
                  )
            where row_number = 1
                `
            }
        );
    });
}

Это SQL, который генерирует JavaScript для первого шага:

SELECT * EXCEPT(row_number) FROM (
  SELECT
    user_id,
    "landing" as step_name,
    0 as step_number,
    timestamp as step_started_at,
    row_number() OVER (PARTITION BY user_id ORDER BY timestamp asc) as row_number
  FROM `api.segment`
  WHERE lower(event) in ("application_installed", "home_page")
  GROUP BY 1,2,3,4
  )
where row_number = 1

Это пример SQL, сгенерированного JavaScript для последующих шагов:

SELECT * EXCEPT(row_number) FROM (
    SELECT
      user_id,
      "start_signup" as step_name,
      1 as step_number,
      timestamp as step_started_at,
      row_number() OVER (PARTITION BY user_id ORDER BY timestamp asc) as row_number
    FROM `api.segment`
    where lower(event) in ("signup_started")
        and user_id in (SELECT user_id from `funnel_data.signup_funnel_landing`)
    GROUP BY 1,2,3,4
    )
where row_number = 1

Шаг 3: объедините данные в одну воронкообразную таблицу

Наконец, мы объединяем данные из каждой таблицы шагов в одну таблицу, которая обобщает воронку — это та, которую мы запрашиваем для анализа. Мы делаем это в файле с именем funnel_combine.js.

Опять же, мы начинаем со ссылки на файл funneldata.js. Затем мы используем функцию ForEach для прохода по каждой воронке (на данный момент у нас есть только одна). Создается оператор select * для каждого шага, и они объединяются с помощью union all. Функции publish и config снова используются для создания таблицы на складе.

const funnels = require('./funneldata.js');
funnels.forEach(funnel => {
    publish(`${funnel.name}`, {
        type: "table",
        schema: "funnel_test"
    })
    .query(ctx =>
      funnel.steps
      .map(step => `select * from ${ctx.ref(`${funnel.name}_${step.name}`)}`)
      .join(`\n union all \n`)
    );
})

Это SQL, который генерирует Javascript:

select * from `funnels.signup_funnel_landing`
  union all
select * from `funnels.signup_funnel_start_signup`
  union all
select * from `funnels.signup_funnel_verify_email`
  union all
select * from `funnels.signup_funnel_signup_complete`

Запуск Javascript в финальном файле публикует таблицу, которая выглядит следующим образом, в хранилище данных:

Этот набор данных можно использовать для быстрого получения информации об активности во время регистрации, например о том, какой процент пользователей, запускающих воронку, доходят до каждого шага и сколько времени пользователи тратят на каждый шаг. Будущие воронки также могут быть легко созданы — все, что нужно сделать, это определить шаги в файле funneldata.js и запустить скрипт funnel_combine.js. Любой, кто хочет создать воронкообразный набор данных, может сделать это сейчас — для этого не требуется предварительный опыт работы с SQL!

Dataform позволяет очень легко использовать JavaScript для определения простых скриптов, констант или макросов, которые можно повторно использовать в вашем проекте, экономя ваше время на написание длинных SQL-запросов. Подпишитесь на бесплатную учетную запись Dataform здесь.

Первоначально опубликовано на https://dataform.co.