Dependence of revenue on the number of JS-errors

As a result of the query you obtain income, number of orders and average proportion of JS-error  for 1 hit for user groups. The calculation is performed as follows: for each user for analyzed period was calculated "Share JS-errors on one hit," as the sum of JS errors / total number of hits.

Further all users have been divided into 10 groups, depending on the indicator "Share JS - errors on one hit." Group 1 - includes users with the lowest indicator "Share JS - errors on one hit" (ie, those users frequency of JS-error is minimal). In the 10-th group respectively included users, who often meet with JS-error (regardless of their activity on the site). It is worth noting that number of users in all groups is the same.

Then income and the number of orders is calculated for each group. Generally, the more likely a user is found with JS-errors on the site, the lower the income he brings.

Notice! Calculations were involved only those users, who faced JS-mistake at least once in review period.

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
• numberOfGroups - number of categories to be analyzed
• eventCategoryName - event category with JS-errors

SQL-query

SELECT
  JSErrorGroup,
  SUM(TotalTransactions) AS TotalTransactions,
  SUM(TotalRevenue) AS TotalRevenue,
  AVG(ErrorsPerHit) AS avgErrorsPerHit
FROM (
  SELECT
    t3.clientId AS ClientId,
    JSErrorsQTY,
    TotalTransactions,
    TotalRevenue,
    TotalHits,
    JSErrorsQTY/TotalHits AS ErrorsPerHit,
    NTILE({numberOfGroups default="10" type="select" values="5,10,15,20"}) OVER (ORDER BY ErrorsPerHit ASC) AS JSErrorGroup
  FROM (
    SELECT
      t1.clientId AS ClientId,
      JSErrorsQTY,
      TotalTransactions,
      TotalRevenue
    FROM (
      SELECT
        clientId,
        COUNT(type) AS JSErrorsQTY
      FROM (TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="streaming_" type="input"}, TIMESTAMP('{startDate default="2015-11-14" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-27" type="datetime"}')))
      WHERE
        eventInfo.eventCategory = '{eventCategoryName default="JS Errors" type="input"}'
      GROUP BY
        clientId) AS t1
    LEFT JOIN EACH (
      SELECT
        clientId,
        COUNT(transaction.transactionId) AS TotalTransactions,
        SUM(transaction.transactionRevenue) AS TotalRevenue
      FROM (
        SELECT
          clientId,
          transaction.transactionId,
          transaction.transactionRevenue
        FROM (TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="streaming_" type="input"}, TIMESTAMP('{startDate default="2015-11-14" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-27" type="datetime"}')))
        WHERE
          eCommerceAction.action_type = 'purchase'
        GROUP BY
          clientId,
          transaction.transactionId,
          transaction.transactionRevenue)
      GROUP BY
        clientId) AS t2
    ON
      (t1.clientId = t2.clientId)) AS t3
  JOIN EACH (
    SELECT
      clientId,
      COUNT(type) AS TotalHits
    FROM (TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="streaming_" type="input"}, TIMESTAMP('{startDate default="2015-11-14" type="datetime"}'), TIMESTAMP('{endDate default="2016-01-27" type="datetime"}')))
    GROUP BY
      clientId) AS t4
  ON
    (t3.clientId = t4.clientId))
GROUP BY
  JSErrorGroup
ORDER BY
  JSErrorGroup