Last active
March 30, 2017 19:56
-
-
Save rex-lin/e6160d765c3e49fabfa54c862cf77b9e to your computer and use it in GitHub Desktop.
Branch Dashboard
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Install and Referred Sessions Events | |
| with user_mapping as ( | |
| SELECT branch_identity_id, developer_identity as user_id | |
| FROM branchio.event a | |
| where developer_identity is not null | |
| and developer_identity != '' | |
| group by 1,2 | |
| ) | |
| select event_date, device_os as os, event_name, session_referring_link_channel as channel, session_referring_link_feature as feature, session_referring_link_campaign as campaign, session_referring_link_tags as tags, branch_identity_id | |
| from ( | |
| SELECT b.user_id, | |
| case when name = 'install' then 'install' when (name = 'open' and json_extract_path_text(metadata,'reinstall') = 'true') then 'reinstall' when | |
| (name = 'open' and json_extract_path_text(metadata,'reinstall') = 'false' and json_extract_path_text(metadata,'referred')= 'true' ) then 're open' end as event_name, | |
| a.timestamp::date as event_date, a.branch_identity_id, developer_identity as user_id, session_referring_link_channel, session_referring_link_feature, session_referring_link_campaign, session_referring_link_tags, session_referring_link_stage, device_os, | |
| ROW_NUMBER() OVER (PARTITION BY a.branch_identity_id, case when name = 'install' then 'install' when (name = 'open' and json_extract_path_text(metadata,'reinstall') = 'true') then 'reinstall' when | |
| (name = 'open' and json_extract_path_text(metadata,'reinstall') = 'false' and json_extract_path_text(metadata,'referred')= 'true' ) then 're open' end ORDER BY a.timestamp asc) row_num | |
| FROM branchio.event a | |
| left join user_mapping b on a.branch_identity_id=b.branch_identity_id | |
| where a.timestamp >= CURRENT_DATE - (60 * INTERVAL '1 DAY') | |
| and (name = 'install' or (name = 'open' and json_extract_path_text(metadata,'reinstall') = 'true') or (name = 'open' and json_extract_path_text(metadata,'reinstall') = 'false' and json_extract_path_text(metadata,'referred')= 'true' )) | |
| order by row_num desc | |
| ) | |
| where row_num =1 | |
| --- Clicks | |
| select click_timestamp::date as event_date, os, link_channel as channel, link_feature as feature, link_campaign as campaign, link_stage as stage, count(distinct branch_device_id) as device_count, count(distinct branch_link_click_id) as link_click_count | |
| from ( | |
| SELECT case when branch_device_fingerprint_id = '' then branch_browser_fingerprint_id else branch_device_fingerprint_id end as branch_device_id , os, click_timestamp, link_channel, link_feature, link_campaign, link_stage, link_tags, branch_link_click_id | |
| FROM branchio.click a | |
| where click_timestamp >= CURRENT_DATE - (60 * INTERVAL '1 DAY') | |
| ) | |
| group by 1,2,3,4,5,6 | |
| --- DAU BQ | |
| SELECT | |
| DATE(TIMESTAMP_SECONDS(visitStartTime)) AS event_date, device.operatingSystem as os, | |
| count (DISTINCT fullVisitorId) AS DAU, | |
| sum(CASE WHEN ea.eventInfo.eventCategory = 'Get' THEN 1 ELSE 0 END) AS get_clicks, | |
| count(distinct CASE WHEN ea.eventInfo.eventCategory = 'Get' THEN fullVisitorId ELSE null END) AS get_DAU | |
| FROM | |
| `big-query-project-1050.60408208.*`, | |
| UNNEST(hits) ea | |
| WHERE | |
| DATE(TIMESTAMP_SECONDS(visitStartTime)) >= DATE_ADD(CURRENT_DATE(), INTERVAL -60 DAY) | |
| GROUP BY | |
| 1,2 | |
| ORDER BY | |
| 1 ASC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment