Allocation of sales by categories depending on category of the landing page

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

As a result of the query you get orders by categories depending on the product category of the landing page. The calculations involved only those sessions in which the user has landed on the product card and bought something. Moreover, if the user bought several items from different categories, the order will be assigned to each of the categories.

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

SQL-query

SELECT
  landingProductCategory,
  purchasedProductCategory,
  SUM(purchases) AS totalPurchases FROM
  (
  SELECT
    sessionId,
    hits.product.productCategory AS landingProductCategory
  FROM
    FLATTEN ( (
      SELECT
        sessionId,
        hits.product.productCategory,
        hits.eCommerceActionType,
        hits.isEntrance
      FROM
        TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-01-11" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-11" type="datetime"}'))), hits.product)
  WHERE
    hits.isEntrance = 1
    AND hits.eCommerceActionType = 'detail'
    AND NOT hits.product.productCategory IN ('(not set)', '')) t1
JOIN EACH
  (
  SELECT
    sessionId,
    hits.product.productCategory AS purchasedProductCategory,
    SUM(hits.product.productQuantity) AS purchases
  FROM
    FLATTEN ( (
      SELECT
        sessionId,
        hits.product.productCategory,
        hits.eCommerceActionType,
        hits.product.productQuantity
      FROM
        TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-01-11" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-11" type="datetime"}'))), hits.product)
  WHERE
    hits.eCommerceActionType = 'purchase'
    AND hits.product.productQuantity > 0
    AND NOT hits.product.productCategory IN ('(not set)', '')
  GROUP BY
    sessionId,
    purchasedProductCategory) t2
ON
  t1.sessionId=t2.sessionId
GROUP BY
  landingProductCategory,
  purchasedProductCategory