Как рассчитать доход с блоков мерчандайзинга?

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

 
Запрос также можно использовать для расчета доходности различных типов каталогов.

Динамические параметры

  • startDate - дата начала анализируемого периода
  • endDate - дата окончания анализируемого периода
  • dataSetName - название dataset с данными Streaming
  • sTableName - название таблицы с данными сессионного Streaming
  • hTableName - название таблицы с данными хитового Streaming
  • listNames - названия блоков для которых нужно рассчитать доход (указывать в виде регулярного выражения, например: BestSellers|SpecialOffers)
  • attributionType - тип атрибуции (может принимать значения MAX - доход будет присваиваться последнему блоку с которым взаимодействовал пользователь, или MIN - доход будет присваиваться первому блоку с которым взаимодействовал пользователь)
  • dateType - тип даты (может принимать значение (purchaseDate - дата, numberOfWeek - номер недели, numberOfMonth - номер месяца.)

Пример визуализации результатов расчетов

SQL-запрос

 
SELECT
  {dateType default="purchaseDate" type="select" values="purchaseDate,numberOfWeek,numberOfMonth"},
  listName,
  SUM(revenue*quantity) AS totalRevenue
  --TabPurchase - считаем количество продажи по sessionId, purchaseDate, productSKU, hitId, numberOfWeek, numberOfMonth
FROM (
  SELECT
    sessionId,
    date AS purchaseDate,
    hits.product.productSku AS productSKU,
    hits.hitId AS hitId,
    INTEGER(CEIL((DAYOFYEAR(TIMESTAMP(INTEGER(hits.time*1000000)))-4)/7-0.01+1)) AS numberOfWeek,
    MONTH(hits.time+1000) AS numberOfMonth,
    hits.time AS time,
    hits.product.productPrice AS revenue,
    hits.product.productQuantity AS quantity
  FROM
    FLATTEN ((
      SELECT
        sessionId,
        date,
        hits.product.productSku,
        hits.eCommerceActionType,
        hits.time,
        hits.product.productPrice,
        hits.product.productQuantity,
        hits.hitId
      FROM
        TABLE_DATE_RANGE({dataSetName default="OWOXBIStreaming" type="input"}.{sTableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-02-08" type="datetime"}'), TIMESTAMP('{endDate default="2016-02-08" type="datetime"}'))), hits.product)
  WHERE
    (hits.eCommerceActionType = 'purchase')
    AND (hits.product.productSku IS NOT NULL)
  GROUP EACH BY
    sessionId,
    purchaseDate,
    productSKU,
    hitId,
    numberOfWeek,
    numberOfMonth,
    time,
    revenue,
    quantity ) AS TabPurchase
JOIN EACH
  --TabClick - добавляем к хитам добавления товаров в корзину и клики по товарам из блоков параметр sessionId
  (
  SELECT
    t2.sessionId AS sessionId,
    product.productSku,
    listName,
    {attributionType default="MAX" type="select" values="MAX,MIN"}(interactionTime) AS time FROM
    --t1 - выбираем добавления товаров в корзину и клики по товарам из блоков
    (
    SELECT
      hitId,
      time AS interactionTime,
      product.productSku,
      eCommerceAction.list AS listName
    FROM
      FLATTEN ((
        SELECT
          hitId,
          product.productSku,
          time,
          eCommerceAction.list,
          eCommerceAction.action_type
        FROM
          TABLE_DATE_RANGE({dataSetName default="OWOXBIStreaming" type="input"}.{hTableName default="streaming_" type="input"}, TIMESTAMP('{startDate default="2016-02-08" type="datetime"}'), TIMESTAMP('{endDate default="2016-02-08" type="datetime"}'))), product)
    WHERE
      (eCommerceAction.action_type IN ('click',
          'add'))
      AND (product.productSku IS NOT NULL)
      AND REGEXP_MATCH(eCommerceAction.list, r'{listNames default="bestSellers|SpecialOffers" type="input"}')) AS t1
  JOIN EACH
    --t2 - выбираем все идентификаторы сессий и хитов из таблиц сессионного Streaming
    (
    SELECT
      sessionId,
      hits.hitId
    FROM
      FLATTEN ((
        SELECT
          sessionId,
          hits.eCommerceActionType,
          hits.hitId,
          hits.product.productSku
        FROM
          TABLE_DATE_RANGE({dataSetName default="OWOXBIStreaming" type="input"}.{sTableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-02-08" type="datetime"}'), TIMESTAMP('{endDate default="2016-02-08" type="datetime"}'))), hits)
    WHERE
      (hits.eCommerceActionType IN ('click',
          'add')
        AND (hits.product.productSku IS NOT NULL))) t2
  ON
    t1.hitId=t2.hits.hitId
  GROUP EACH BY
    sessionId,
    product.productSku,
    listName ) AS TabClick
ON
  (TabClick.sessionId = TabPurchase.sessionId)
  AND (TabClick.product.productSku = TabPurchase.productSKU)
WHERE
  TabClick.time<TabPurchase.time
GROUP EACH BY
  {dateType},
  listName
ORDER BY
  {dateType} ASC
Была ли эта статья полезной?
Пользователи, считающие этот материал полезным: 0 из 0
Еще есть вопросы? Отправить запрос

0 Комментарии

Войдите в службу, чтобы оставить комментарий.