What checkout steps have the highest exit rate?

The query shows exit rate for each step of checkout.

Dynamic parameters

• dataSetName — name of Google BigQuery dataset with Google Analytics sessions data,
• tableName — name of a table with the data,
• startDate — start date of the analyzed period,
• endDate — end date of the analyzed period.

Visualization example

SQL Query

SELECT
  checkoutStep,
  date,
  exitsCount/pageviews AS exitRate
FROM (
  SELECT
    t1.checkoutStep AS checkoutStep,
    t1.date AS date,
    t1.exitsCount AS exitsCount,
    t2.pageviews AS pageviews
  FROM (
    SELECT
      hits.eCommerceAction.step AS checkoutStep,
      date,
      count (hits.eCommerceAction.step) AS exitsCount
    FROM
      TABLE_DATE_RANGE({dataSetName default="OWOXBI_Streaming" type="input"}.{tableName default="session_streaming_" type="input"}, TIMESTAMP('{startDate default="2016-08-10" type="datetime"}'), TIMESTAMP('{endDate default="2016-08-13" type="datetime"}'))
    WHERE
      hits.isExit=1
      AND hits.eCommerceAction.step IS NOT NULL
    GROUP BY
      checkoutStep,
      date) AS t1
  JOIN EACH (
    SELECT
      hits.eCommerceAction.step AS checkoutStep,
      date,
      COUNT(DISTINCT hits.hitId) AS pageviews
    FROM
      TABLE_DATE_RANGE({dataSetName}.{tableName}, TIMESTAMP('{startDate}'), TIMESTAMP('{endDate}'))
    WHERE
      hits.eCommerceAction.step IS NOT NULL
    GROUP BY
      checkoutStep,
      date) AS t2
  ON
    t1.checkoutStep=t2.checkoutStep and t1.date=t2.date
  GROUP BY
    checkoutStep,
    date,
    exitsCount,
    pageviews)
GROUP BY
  checkoutStep,
  date,
  exitRate