Skip to content

Instantly share code, notes, and snippets.

@jmcguirk

jmcguirk/gist:16368c90142c483ecd93

Created December 16, 2014 19:38
Show Gist options
  • Save jmcguirk/16368c90142c483ecd93 to your computer and use it in GitHub Desktop.
Save jmcguirk/16368c90142c483ecd93 to your computer and use it in GitHub Desktop.
Download ZIP
Funnel Example
Raw
gistfile1.txt
SELECT To_char(install.ingest_datetime, 'YYYY-MM-DD') AS install_date,
install.os,
install.country,
Count(DISTINCT install.device_tag) AS installs,
Count(DISTINCT( CASE
WHEN event.kingdom = 'FTUE.Comic.Start' THEN
event.device_tag
ELSE ''
end )) - 1 AS "FTUE.Comic.Start",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Scene.Open'
AND event.phylum = 'SelectHeroScene' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Select Hero Scene",
Count(DISTINCT( CASE
WHEN event.kingdom = 'FTUE.Commander.Load' THEN
event.device_tag
ELSE ''
end )) - 1 AS "FTUE.Commander.Load",
Count(DISTINCT( CASE
WHEN event.kingdom = 'FTUE.Commander.LoadComplete' THEN
event.device_tag
ELSE ''
end )) - 1 AS
"FTUE.Commander.LoadComplete",
Count(DISTINCT( CASE
WHEN event.kingdom = 'FTUE.Commander.Tapped' THEN
event.device_tag
ELSE ''
end )) - 1 AS "FTUE.Commander.Tapped"
,
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_ftue_1' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Start FTUE Map",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_ftue_1' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Start FTUE Map",
Count(DISTINCT( CASE
WHEN event.kingdom = 'FTUE.Arrow.Tapped' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Tapped Dungeon Arrow",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Battle.Start'
AND event.phylum = 'e_encounter_ftue_1_e3' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Begin First Battle",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Battle.Win'
AND event.phylum = 'e_encounter_ftue_1_e3' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Complete First Battle"
,
Count(DISTINCT( CASE
WHEN event.kingdom = 'Scene.Close'
AND event.phylum = 'BattleScene' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Battle Cleanup",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Battle.Start'
AND event.phylum = 'e_encounter_ftue_1_e6' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Begin FTUE Boss",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Battle.Win'
AND event.phylum = 'e_encounter_ftue_1_e6' THEN
event.device_tag
ELSE ''
end )) - 1 AS "End FTUE Boss",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_ftue_1' THEN
event.device_tag
ELSE ''
end )) - 1 AS "End Ftue Map",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Narrative.View'
AND event.phylum = 'e_narrative_ftue_welcome_home'
THEN
event.device_tag
ELSE ''
end )) - 1 AS "Opened Town Scene",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Dialogs.Show'
AND event.phylum = 'HeroRevealPopup' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Summoned First Hero",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Scene.Open'
AND event.phylum = 'ArmoryScene' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Opened Armory",
Count(DISTINCT( CASE
WHEN event.kingdom = 'FTUE.Research.ResearcherClicked'
THEN
event.device_tag
ELSE ''
end )) - 1 AS "Clicked Researcher",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Scene.Open'
AND event.phylum = 'ArmoryScene' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Opened Armory Scene",
Count(DISTINCT( CASE
WHEN event.kingdom = 'TriggeredEvent.SelectFollower'
THEN
event.device_tag
ELSE ''
end )) - 1 AS "Select Follower",
Count(DISTINCT( CASE
WHEN event.kingdom = 'MissionTap'
AND event.phylum = 'e_mission_1' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Tap World Map Mission"
,
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_m1_d1' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Start Map 1",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Battle.Start'
AND event.phylum = 'e_encounter_m1_d1_e1' THEN
event.device_tag
ELSE ''
end )) - 1 AS
"Start Map 1 First Combat",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m1_d1' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Complete Map 1",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Research.NewResearcherBought'
THEN
event.device_tag
ELSE ''
end )) - 1 AS "Buy New Researcher",
Count(DISTINCT( CASE
WHEN
event.kingdom = 'Train.commander_className.ability' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Trained Commander",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_FTUE_capture_tutorial'
THEN
event.device_tag
ELSE ''
end )) - 1 AS
"Start Capture Tutorial",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m1_d2' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Complete Map 2",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m1_d5' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Complete Map 5",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m4_d3' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Complete M4D3",
Count(DISTINCT( CASE
WHEN event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m10_d3' THEN
event.device_tag
ELSE ''
end )) - 1 AS "Complete M10D3"
FROM install
LEFT JOIN event
ON ( install.user_id = event.user_id
AND event.device_tag != 'missing'
AND event.ingest_datetime >= '2014-12-10'
AND event.ingest_datetime < '2014-12-11'
AND ( ( event.kingdom = 'FTUE.Comic.Start' )
OR ( event.kingdom = 'Scene.Open'
AND event.phylum = 'SelectHeroScene' )
OR ( event.kingdom = 'FTUE.Commander.Load' )
OR ( event.kingdom = 'FTUE.Commander.LoadComplete' )
OR ( event.kingdom = 'FTUE.Commander.Tapped' )
OR ( event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_ftue_1' )
OR ( event.kingdom = 'FTUE.Arrow.Tapped' )
OR ( event.kingdom = 'Battle.Start'
AND event.phylum = 'e_encounter_ftue_1_e3' )
OR ( event.kingdom = 'Battle.Win'
AND event.phylum = 'e_encounter_ftue_1_e3' )
OR ( event.kingdom = 'Scene.Close'
AND event.phylum = 'BattleScene' )
OR ( event.kingdom = 'Battle.Start'
AND event.phylum = 'e_encounter_ftue_1_e6' )
OR ( event.kingdom = 'Battle.Win'
AND event.phylum = 'e_encounter_ftue_1_e6' )
OR ( event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_ftue_1' )
OR ( event.kingdom = 'Narrative.View'
AND event.phylum =
'e_narrative_ftue_welcome_home' )
OR ( event.kingdom = 'Dialogs.Show'
AND event.phylum = 'HeroRevealPopup' )
OR ( event.kingdom = 'FTUE.Research.ResearcherClicked'
)
OR ( event.kingdom = 'Scene.Open'
AND event.phylum = 'ArmoryScene' )
OR ( event.kingdom = 'MissionTap'
AND event.phylum = 'e_mission_1' )
OR ( event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_m1_d1' )
OR ( event.kingdom = 'Battle.Start'
AND event.phylum = 'e_encounter_m1_d1_e1' )
OR ( event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m1_d1' )
OR ( event.kingdom = 'Research.NewResearcherBought' )
OR ( event.kingdom =
'Train.commander_className.ability' )
OR ( event.kingdom = 'Area.Start'
AND event.phylum = 'e_map_FTUE_capture_tutorial'
)
OR ( event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m1_d2' )
OR ( event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m1_d5' )
OR ( event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m4_d3' )
OR ( event.kingdom = 'Area.Complete'
AND event.phylum = 'e_map_m10_d3' ) ) )
WHERE install.device_tag != 'missing'
AND install.ingest_datetime >= '2014-12-10'
AND install.ingest_datetime < '2014-12-11'
GROUP BY 1,
2,
3
ORDER BY 1 DESC
LIMIT 1000;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment