Как узнать время от просмотра товара до его покупки?

В результате выполнения запроса получаем сумму и накопленную сумму заказов в разбивке по источникам трафика и времени от просмотра карточки товара до оформления заказа.

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

  • startDate - дата начала анализируемого периода
  • endDate - дата окончания анализируемого периода
  • dataSetName - название dataset с данными сессионного Streaming
  • tableName - название таблицы с данными сессионного Streaming
  • trafficSourceMedium - источники трафика, которые необходимо проанализировать (указываются через регулярное выражение)

 

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

SQL-запрос

--считаем накопленную сумму заказов в разрезе источников трафика и времени
SELECT
  trafficSourceMedium,
  hoursFromViewToPurchase,
  totalPurchases,
  SUM(totalPurchases) OVER (PARTITION BY trafficSourceMedium ORDER BY hoursFromViewToPurchase) cumulativeTotalPurchases
FROM (
  --считаем сумму заказов в разрезе источников трафика и времени (группируем по часам)
  SELECT
    trafficSourceMedium,
    CASE WHEN timeToPurchase<0.5 THEN 0.5 
WHEN timeToPurchase<1 THEN 1
WHEN timeToPurchase<2 THEN 2
WHEN timeToPurchase<3 THEN 3
WHEN timeToPurchase<5 THEN 5
WHEN timeToPurchase<8 THEN 8
WHEN timeToPurchase<12 THEN 12
WHEN timeToPurchase<18 THEN 18
WHEN timeToPurchase<24 THEN 24
WHEN timeToPurchase<48 THEN 48
WHEN timeToPurchase<72 THEN 72
WHEN timeToPurchase<96 THEN 96
WHEN timeToPurchase<120 THEN 120
WHEN timeToPurchase<144 THEN 144
WHEN timeToPurchase<168 THEN 168
WHEN timeToPurchase>=168 THEN 169 END AS hoursFromViewToPurchase, COUNT(*) AS totalPurchases FROM ( --в таблицу purchases выбираем все заказы, где SKU товара и источник трафика не равны NULL SELECT purchases.clientId AS clientId, purchases.hits.product.productSku AS hits.product.productSku, trafficSource.source+' / '+trafficSource.medium AS trafficSourceMedium, ROUND((purchases.hits.time-detailViews.hits.time)/3660,1) AS timeToPurchase FROM ( SELECT clientId, MIN(hits.time) AS hits.time, hits.product.productSku FROM FLATTEN (( SELECT clientId, hits.hitId, hits.time, hits.eCommerceActionType, hits.product.productSku FROM TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-05-01" type="datetime"}'), TIMESTAMP('{endDate default="2016-05-17" type="datetime"}'))), hits.product) WHERE (hits.eCommerceActionType = 'purchase') AND (hits.product.productSku IS NOT NULL) AND hits.product.productSku != '(not set)' GROUP EACH BY clientId, hits.product.productSku) AS purchases JOIN EACH ( --в таблицу detailViews выбираемся первый просмотр каждого товара каждым пользователем. Запоминаем источник трафика у этого просмотра и время SELECT t1.clientId AS clientId, t1.hits.time AS hits.time, t1.hits.product.productSku AS hits.product.productSku, trafficSource.source, trafficSource.medium FROM ( SELECT clientId, MIN(hits.time) AS hits.time, hits.product.productSku FROM FLATTEN (( SELECT clientId, hits.hitId, hits.time, hits.eCommerceActionType, hits.product.productSku FROM TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))), hits.product) WHERE (hits.eCommerceActionType = 'detail') AND (hits.product.productSku IS NOT NULL) AND hits.product.productSku != '(not set)' GROUP EACH BY clientId, hits.product.productSku) AS t1 JOIN EACH ( SELECT clientId, trafficSource.source, trafficSource.medium, hits.time, hits.product.productSku FROM FLATTEN (( SELECT clientId, hits.hitId, hits.time, trafficSource.source, trafficSource.medium, hits.eCommerceActionType, hits.product.productSku FROM TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))), hits.product) WHERE (hits.eCommerceActionType = 'detail') AND (hits.product.productSku IS NOT NULL) AND hits.product.productSku != '(not set)' AND trafficSource.source IS NOT NULL AND trafficSource.medium IS NOT NULL GROUP BY clientId, hits.hitId, hits.time, trafficSource.source, trafficSource.medium, hits.eCommerceActionType, hits.product.productSku) AS t2 ON (t1.clientId=t2.clientId) AND (t1.hits.time=t2.hits.time) AND (t1.hits.product.productSku=t2.hits.product.productSku) GROUP BY clientId, hits.time, hits.product.productSku, trafficSource.source, trafficSource.medium) AS detailViews ON (purchases.clientId = detailViews.clientId) AND (purchases.hits.product.productSku = detailViews.hits.product.productSku) WHERE (purchases.hits.time > detailViews.hits.time)) GROUP BY trafficSourceMedium, hoursFromViewToPurchase ORDER BY trafficSourceMedium, hoursFromViewToPurchase) HAVING REGEXP_MATCH(trafficSourceMedium, r'{trafficSourceMedium default=".*" type="input"}')

 

 

Была ли эта статья полезной?
Пользователи, считающие этот материал полезным: 0 из 0
Еще есть вопросы? Отправить запрос

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

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