Grab free access to all the OWOX BI features for 14 days

How can you calculate time between product detail view and purchase?

As a result of the query you obtain the sum and the accumulated sum of orders by traffic sources and time between product detail view and purchase.

Report Example

Dynamic parameters

  • startDate - start date of the analyzed period
  • endDate - end date of the analyzed period
  • dataSetName - name of dataset, where stored session streaming table
  • tableName - name of the table, where stored session Streaming data
  • trafficSourceMedium - list of traffic sources for analysis (use RegExp)

 

SQL-query


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 ( 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 (
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"}')

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.