Skip to content

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

License

Notifications You must be signed in to change notification settings

StyleShare/bigquery-firebase-funnel-builder

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

1 Commit
 
 
 
 
 
 
 
 

Repository files navigation

BigQuery Firebase Funnel Builder

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

example-funnel

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.

About

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

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages