styleshare/bigquery-firebase-funnel-builder

A Python script that builds a funnel for Google BigQuery with Firebase Analytics.

BigQuery Firebase Funnel Builder

A Python script that builds a funnel for Google BigQuery with Firebase Analytics.

Usage

$ python funnel.py
USAGE:
    python funnel.py table_name event_name_0 event_name_1 ...

EXAMPLE:
    python funnel.py com_myapp_IOS.app_events_* sign_up add_to_cart purchase

Here is an example query generated by the script:

#standardSQL

-- Generated by BigQuery Firebase Funnel Builder
-- https://github.com/StyleShare/bigquery-firebase-funnel-builder

WITH
data AS (
  SELECT
    user_dim.first_open_timestamp_micros AS session,
    event.timestamp_micros AS timestamp,
    (CASE event.name WHEN "sign_up" THEN event.timestamp_micros END) AS step_0_timestamp,
    (CASE event.name WHEN "add_to_cart" THEN event.timestamp_micros END) AS step_1_timestamp,
    (CASE event.name WHEN "purchase" THEN event.timestamp_micros END) AS step_2_timestamp
  FROM
    `com_myapp_IOS.app_events_*`,
    UNNEST(event_dim) as event,
    UNNEST(event.params) as params
),

funnel AS (
  SELECT
    session,
    timestamp,
    LAST_VALUE(step_0_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_0_funnel,
    LAST_VALUE(step_1_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_1_funnel,
    LAST_VALUE(step_2_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_2_funnel
  FROM data
)

SELECT
  "1_sign_up" AS step,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
    THEN step_0_funnel END
  ) AS count
  FROM funnel
UNION ALL SELECT
  "2_add_to_cart" AS step,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel
UNION ALL SELECT
  "3_purchase" AS step,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
      AND step_2_funnel IS NOT NULL AND step_1_funnel < step_2_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel
ORDER BY step
;

LICENSE

BigQuery Firebase Funnel Builder is written by Suyeol Jeon and available under MIT license. See the LICENSE file for more info.