Условие

Дан публичный датасет в BigQuery bigquery-public-data.google_analytics_sample.ga_sessions_*. Период — 2 недели.

Выгрузите таблицу из BigQuery (через сохранение в Google Sheets, BQ API или экспорт в Drive).
На Python сформируйте последовательности источников визитов пользователя, которые привели к транзакции.
Источник = utm_source + '_' + utm_medium. Транзакция = hits.transaction.transactionId соответствует регулярному выражению .*\d+.*.

Ожидается Jupyter-ноутбук с комментариями + 2 таблицы:

(а) Для каждой транзакции — сформированная цепочка касаний.
(б) Топ-10 самых популярных цепочек.

Решение

Шаг 1. SQL-запрос в BigQuery

GA-сессии хранятся пошардно по дням; нам нужны 14 дней и плоские строки на пользователя/сессию. Поля для нас:

fullVisitorId — уникальный пользователь.
visitId — id сессии.
visitStartTime — UNIX-время начала сессии (в секундах).
trafficSource.source, trafficSource.medium — источник и канал визита (UTM).
Внутри hits (массив) — hits.transaction.transactionId.

DECLARE start_date DATE DEFAULT DATE '2017-08-01';
DECLARE end_date   DATE DEFAULT DATE '2017-08-14';
 
WITH sessions AS (
  SELECT
    fullVisitorId AS user_id,
    visitId,
    visitStartTime AS ts,
    CONCAT(IFNULL(trafficSource.source, '(none)'), '_', IFNULL(trafficSource.medium, '(none)')) AS channel,
    -- Список transactionId всех hits сессии, отфильтрованный регуляркой
    ARRAY(
      SELECT t.transactionId
      FROM UNNEST(hits) AS h, UNNEST([h.transaction]) AS t
      WHERE t.transactionId IS NOT NULL
        AND REGEXP_CONTAINS(t.transactionId, r'.*\d+.*')
    ) AS tx_ids
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN
        FORMAT_DATE('%Y%m%d', start_date)
    AND FORMAT_DATE('%Y%m%d', end_date)
)
SELECT user_id, visitId, ts, channel, tx_ids
FROM sessions
ORDER BY user_id, ts;

Это даёт по сессии: пользователя, время, канал, и список транзакционных id (если есть).

Шаг 2. Загрузка в Python

from google.cloud import bigquery
client = bigquery.Client(project="my-project")
df = client.query(SQL).to_dataframe()

Шаг 3. Цепочки до каждой транзакции

Для каждой транзакции нужно взять все её предшествующие сессии того же пользователя в порядке времени. Стандартный подход:

import pandas as pd
 
# 1. Развернуть транзакции
tx_rows = df.explode("tx_ids").dropna(subset=["tx_ids"])
tx_rows = tx_rows.rename(columns={"tx_ids": "transactionId"})
# Какому пользователю и в какое время произошла транзакция
tx_rows = tx_rows[["user_id", "transactionId", "ts"]].drop_duplicates()
 
# 2. Для каждого user_id отсортировать сессии по времени
df_sorted = df.sort_values(["user_id", "ts"]).reset_index(drop=True)
 
# 3. Собрать цепочки до момента каждой транзакции
chains = []
for _, tx in tx_rows.iterrows():
    user_sessions = df_sorted[
        (df_sorted["user_id"] == tx["user_id"]) &
        (df_sorted["ts"] <= tx["ts"])
    ]
    chain = " => ".join(user_sessions["channel"].tolist())
    chains.append({"transactionId": tx["transactionId"], "chain": chain})
 
chain_df = pd.DataFrame(chains)

Для больших данных — векторно:

df_sorted["__rank"] = df_sorted.groupby("user_id")["ts"].rank(method="dense").astype(int)
# ... merge_asof по (user_id, ts)

Шаг 4. Топ-10 цепочек

top10 = (
    chain_df["chain"]
    .value_counts()
    .head(10)
    .rename_axis("chain")
    .reset_index(name="count")
)

Шаг 5. Что отдать

Ноутбук со SQL-запросом, шагами обработки и комментариями.
Таблица 1: transactionId, chain.
Таблица 2: chain, count (топ-10).

Подводные камни

Поле transaction пустое в большинстве сессий. Поэтому фильтр в SQL по регулярке — обязателен, иначе при EXPLODE-обработке вы либо потеряете сессии без транзакций (а они нужны для цепочек!), либо получите много мусора. Лучший способ — оставлять все сессии, а транзакции — отдельный список внутри.
utm_source = NULL или (direct). Стандартизуйте: IFNULL(..., '(none)').
Регулярка .*\d+.* просто требует наличия хотя бы одной цифры — учтите, что transactionId == NULL тоже не подходит.
Дубликаты транзакций. transactionId может встретиться в разных сессиях (если страница «спасибо за заказ» открыта повторно). drop_duplicates обязателен.
Очень длинные цепочки. Для одного пользователя с десятками сессий цепочка получится огромной. На практике обрезают до 5–10 последних касаний.
Считать только источники после первой сессии. Условие говорит «привели к транзакции» — обычно учитывают все сессии до момента транзакции, включая первую.
Сравнение ts <= tx.ts. Сама транзакционная сессия включена в цепочку — это правильно: её источник тоже был «касанием».

Эталонный ответ

(а) Таблица transactionId → chain — построенная по описанному алгоритму.

(б) Топ-10 — типичные цепочки в GA-датасете обычно начинаются с (direct)_(none), либо google_organic, конкретные значения зависят от периода.

Главное — показать процесс: SQL → выгрузка → векторная обработка → агрегация → отдача в формате таблиц.

BigQuery + Python — цепочки касаний пользователя до транзакции