Traffic on out-of-stock product card

As a result of the query you get number and share of sessions with landing page  product cards depending on the stock status ("available," "in reserve", "not available").

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
• dateType - date type (can be set to date - date, numberOfWeek - week number, numberOfMonth - number of the month.)
• dimensionIndex - index of hit-scope custom dimension that stores information about the status of the products.

SQL-query

SELECT
  {dateType default="date" type="select" values="date,numberOfWeek,numberOfMonth"},
  productAvailability,
  numberOfSessions,
  shareOfTotalSessions FROM
  (
  SELECT
    t3.date AS date,
    t3.numberOfWeek AS numberOfWeek,
    t3.numberOfMonth AS numberOfMonth,
    t3.productAvailability AS productAvailability,
    t3.totalSessions AS numberOfSessions,
    t3.totalSessions/t4.totalSessions AS shareOfTotalSessions FROM
    (
    SELECT
      date,
      numberOfWeek,
      numberOfMonth,
      productAvailability,
      COUNT(t2.hitId) AS totalSessions FROM
      (
      SELECT
        date,
        hits.hitId,
        INTEGER(CEIL((DAYOFYEAR(TIMESTAMP(INTEGER(hits.time*1000000)))-4)/7-0.01+1)) AS numberOfWeek,
        MONTH(hits.time+1000) AS numberOfMonth
      FROM
        FLATTEN ((
          SELECT
            date,
            hits.eCommerceActionType,
            hits.time,
            hits.hitId,
            hits.isEntrance
          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 = 'detail'
        AND hits.isEntrance = 1
      GROUP EACH BY
        hits.hitId,
        date,
        numberOfWeek,
        numberOfMonth ) AS t1
    JOIN EACH
      (
      SELECT
        hitId,
        customDimensions.value AS productAvailability
      FROM
        FLATTEN ((
          SELECT
            sessionId,
            date,
            eCommerceAction.action_type,
            hitId,
            customDimensions.index,
            customDimensions.value
          FROM
            TABLE_DATE_RANGE({dataSetName}.{hTableName default="streaming_" type="input"}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))), customDimensions)
      WHERE
        eCommerceAction.action_type = 'detail'
        AND customDimensions.index = {dimensionIndex default="20" type="input"}
        AND customDimensions.value IS NOT NULL
        AND customDimensions.value != '(not set)'
      GROUP EACH BY
        hitId,
        productAvailability) AS t2
    ON
      (t1.hits.hitId=t2.hitId)
    GROUP EACH BY
      date,
      numberOfWeek,
      numberOfMonth,
      productAvailability) AS t3
  LEFT JOIN EACH (
    SELECT
      date,
      numberOfWeek,
      numberOfMonth,
      COUNT(t2.hitId) AS totalSessions
    FROM (
      SELECT
        date,
        hits.hitId,
        INTEGER(CEIL((DAYOFYEAR(TIMESTAMP(INTEGER(hits.time*1000000)))-4)/7-0.01+1)) AS numberOfWeek,
        MONTH(hits.time+1000) AS numberOfMonth
      FROM
        FLATTEN ((
          SELECT
            date,
            hits.eCommerceActionType,
            hits.time,
            hits.hitId,
            hits.isEntrance
          FROM
            TABLE_DATE_RANGE({dataSetName}.{sTableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))), hits)
      WHERE
        hits.eCommerceActionType = 'detail'
        AND hits.isEntrance = 1
      GROUP EACH BY
        hits.hitId,
        date,
        numberOfWeek,
        numberOfMonth ) AS t1
    JOIN EACH (
      SELECT
        hitId,
        customDimensions.value AS productAvailability
      FROM
        FLATTEN ((
          SELECT
            sessionId,
            date,
            eCommerceAction.action_type,
            hitId,
            customDimensions.index,
            customDimensions.value
          FROM
            TABLE_DATE_RANGE({dataSetName}.{hTableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))), customDimensions)
      WHERE
        eCommerceAction.action_type = 'detail'
        AND customDimensions.index = {dimensionIndex}
        AND customDimensions.value IS NOT NULL
        AND customDimensions.value != '(not set)'
      GROUP EACH BY
        hitId,
        productAvailability) AS t2
    ON
      (t1.hits.hitId=t2.hitId)
    GROUP EACH BY
      date,
      numberOfWeek,
      numberOfMonth) AS t4
  ON
    (t3.date=t4.date))