Условие

В FlowWOW есть несколько источников данных в BigQuery:

adjust_client — события приложения от Adjust; покупки = event_name = 'purchase'.
ga_events — события сайта (GA4); покупки = event_name = 's2s_ecommerce_purchase_paid'.
crm — заказы (включая отменённые).
order_sku — позиции заказов (product_type ∈ {main, delivery, additional}).
categories, subcategories — словари.

Соберите витрину по совершённым заказам. Каждая строка = один main-товар в заказе. Поля (в порядке агрегации):

Дата (YYYY-MM-DD), Номер заказа, Категория, Подкатегория, Название товара (только main), Выручка (см. правило ниже), Город доставки, Город заказа, Платформа, Источник привлечения (первое касание), Кампания привлечения (первое касание).

Правило выручки: позиции с product_type ∈ {delivery, additional} распределяются равномерно на main-позиции того же заказа. То есть выручка main-позиции = cost_main + (Σ cost_delivery+additional / count_main).

Решение

Подход

Из crm берём только успешные заказы.
Из order_sku группируем по order_id сумму delivery+additional и количество main, потом распределяем.
Атрибуция «первое касание» — найти первое событие пользователя в adjust_client или ga_events (по user_id) и взять его source/campaign. Если пользователь начал на сайте, потом заказал в приложении — first-touch на сайте.
JOIN с категориями.

Реализация (BigQuery dialect)

WITH
-- 1) Аггрегаты заказа: суммы по типам и счёт main
order_agg AS (
  SELECT order_id,
         SUM(IF(product_type = 'main',         price, 0)) AS sum_main,
         SUM(IF(product_type IN ('delivery', 'additional'), price, 0)) AS sum_extra,
         SUM(IF(product_type = 'main', 1, 0))             AS cnt_main
  FROM `flowwow.order_sku`
  GROUP BY order_id
),
 
-- 2) Приведение каждой main-позиции
mart_lines AS (
  SELECT os.order_id,
         os.product_id,
         os.product_name,
         os.category_id,
         os.subcategory_id,
         os.price + SAFE_DIVIDE(oa.sum_extra, oa.cnt_main) AS revenue
  FROM `flowwow.order_sku` os
  JOIN order_agg oa USING (order_id)
  WHERE os.product_type = 'main'
),
 
-- 3) First-touch атрибуция (объединяем оба источника)
all_touches AS (
  SELECT user_id, event_ts, source, campaign, 'app' AS platform_touch
  FROM `flowwow.adjust_client`
  UNION ALL
  SELECT user_id, event_ts, source, campaign, 'web'
  FROM `flowwow.ga_events`
),
first_touch AS (
  SELECT user_id, source, campaign, platform_touch
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts ASC) AS rn
    FROM all_touches
  )
  WHERE rn = 1
),
 
-- 4) Финальный JOIN
final AS (
  SELECT
    DATE(c.created_at)              AS order_date,
    c.order_id,
    cat.name                        AS category_name,
    sub.name                        AS subcategory_name,
    ml.product_name                 AS product_name,
    ROUND(ml.revenue, 2)            AS revenue,
    c.delivery_city                 AS delivery_city,
    c.order_city                    AS order_city,
    c.platform                      AS platform,
    ft.source                       AS first_source,
    ft.campaign                     AS first_campaign
  FROM `flowwow.crm` c
  JOIN mart_lines ml USING (order_id)
  LEFT JOIN `flowwow.categories`    cat ON cat.id = ml.category_id
  LEFT JOIN `flowwow.subcategories` sub ON sub.id = ml.subcategory_id
  LEFT JOIN first_touch ft ON ft.user_id = c.user_id
  WHERE c.status = 'completed'
)
 
SELECT * FROM final
ORDER BY order_date, order_id;

Cohort-анализ модерации (задание 2)

Дополнительно: на таблице shops_data(shop_id, date_reg, date_mod, source) нужно сделать когортный анализ: доля shopов, прошедших модерацию, в разрезе month(date_reg) × source`.

SELECT DATE_TRUNC(date_reg, MONTH) AS reg_month,
       source,
       COUNT(*)                                                 AS regs,
       SUM(IF(date_mod IS NOT NULL, 1, 0))                      AS moderated,
       SUM(IF(date_mod IS NOT NULL, 1, 0)) * 1.0 / COUNT(*)     AS mod_rate
FROM `flowwow.shops_data`
GROUP BY reg_month, source
ORDER BY reg_month, source;

Для тренда добавьте средний delay = DATE_DIFF(date_mod, date_reg, DAY).

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

Первое касание неоднозначно при равных таймстампах в двух источниках. Проверяйте ROW_NUMBER с детерминированным tie-breaker (например, event_ts ASC, source ASC).
SAFE_DIVIDE обязателен — иначе при cnt_main = 0 (заказ без main) — DIVIDE-by-zero. Возможно, такие заказы вообще игнорируются в правиле.
Возвраты/cancellations — crm.status обязателен фильтр.
Дубли заказов в crm (миграция, retry) → убирайте через QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1.
First-touch: «первое касание» по user_id только до даты первого заказа? Условие зависит от продуктового определения. Часто — «первое касание перед заказом», иначе можно потерять смысл атрибуции.
Round/денормализация: распределение sum_extra по делителю даёт нецелое; решите, округлять до копейки и где, чтобы суммы сходились по чекам.

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

CTE 1 — агрегаты по заказу (sum extra, count main); CTE 2 — main-позиции с пересчётом цены; CTE 3 — first-touch через UNION ALL и ROW_NUMBER; финальный JOIN с CRM и словарями. Сохранить как VIEW.

FlowWOW — витрина данных по заказам с пересчётом цены main-позиций