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