Calculation of revenue from advert blocks

As a result of the query you obtain income in context of advert blocks for the cases when the user clicked on the product in the block or added product to card from the block, and then bought the same product.

The query can also be used to calculate the profitability of different types of catalogs.

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
• sTableName - name of the table, where stored session Streaming data
• hTableName - name of the table, where stored hit Streaming data
• listNames - block name for which you want to calculate the income (defined as regular expressions, for example: BestSellers | SpecialOffers)
• attributionType - attribution type (can take value MAX - income will be assigned to the last block with which the user interacts, or MIN - income will be assigned to the first block with which the user interacts)
• dateType - date type (can be set to purchaseDate - date, numberOfWeek - week number, numberOfMonth - number of the month.)

SQL-query

SELECT
  {dateType default="purchaseDate" type="select" values="purchaseDate,numberOfWeek,numberOfMonth"},
  listName,
  SUM(revenue*quantity) AS totalRevenue
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
  (
  SELECT
    t2.sessionId AS sessionId,
    product.productSku,
    listName,
    {attributionType default="MAX" type="select" values="MAX,MIN"}(interactionTime) AS time FROM
    (
    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
    (
    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