Skip to content

Instantly share code, notes, and snippets.

@ping-coder

ping-coder/daily_pulse.dashboard

Created April 24, 2023 08:49
Show Gist options
  • Save ping-coder/3ec7277d0c2714c8cf034b851755d7ea to your computer and use it in GitHub Desktop.
Save ping-coder/3ec7277d0c2714c8cf034b851755d7ea to your computer and use it in GitHub Desktop.
Download ZIP
AppDev/GCP lookml - firebase
Raw
daily_pulse.dashboard
- dashboard: daily_pulse
title: Daily Pulse
layout: newspaper
embed_style:
background_color: ''
show_title: true
title_color: "#5c4ac3"
show_filters_bar: true
tile_text_color: "#ff7725"
text_tile_text_color: ''
elements:
- title: New Tile
name: New Tile
model: gaming
explore: events
type: looker_line
fields: [events.number_of_new_users, events.event_date, events.number_of_users]
fill_fields: [events.event_date]
filters: {}
sorts: [events.event_date desc]
limit: 500
column_limit: 50
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{id: events.number_of_new_users,
name: Number of New Users, axisId: events.number_of_new_users}, {id: events.number_of_users,
name: Active Users, axisId: events.number_of_users}], showLabels: true,
showValues: true, unpinAxis: true, tickDensity: default, tickDensityCustom: 5,
type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: false
legend_position: center
series_types: {}
point_style: none
series_colors: {}
series_labels:
events.number_of_users: Active Users
events.number_of_new_users: New Installs
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
swap_axes: false
show_null_points: true
interpolation: linear
title_hidden: true
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 4
width: 4
height: 7
- title: Daily Active Users
name: Daily Active Users
model: gaming
explore: events
type: single_value
fields: [events.number_of_users, events.event_date]
fill_fields: [events.event_date]
filters: {}
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: daily_active_users, label: Daily Active Users,
expression: 'mean(${events.number_of_users})', value_format: '[>=1000000]0.00,,"M";[>=1000]0.00,"K";0',
value_format_name: !!null '', _kind_hint: measure, _type_hint: number}]
note_state: collapsed
note_display: hover
note_text: The average number of unique users per day who played at least one
session in the selected time period
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 0
width: 4
height: 4
- name: "<span class='fa fa-tachometer'> Topline KPIs</span>"
type: text
title_text: "<span class='fa fa-tachometer'> Topline KPIs</span>"
subtitle_text: Are we headed in the right direction?
body_text: ''
row: 0
col: 0
width: 24
height: 2
- name: "<span class='fa fa-smile-o'> Retention </span>"
type: text
title_text: "<span class='fa fa-smile-o'> Retention </span>"
subtitle_text: Are our games fun?
row: 19
col: 0
width: 24
height: 2
- name: "<span class='fa fa-users'> User Acquistion </span>"
type: text
title_text: "<span class='fa fa-users'> User Acquistion </span>"
subtitle_text: Are we acquiring players from the best places?
body_text: ''
row: 9
col: 0
width: 24
height: 3
- name: "<span class='fa fa-usd'> Monetization </span>"
type: text
title_text: "<span class='fa fa-usd'> Monetization </span>"
subtitle_text: <p> Are we building a sustainable business? Go to <a href="/dashboards/8e7moQwUXBZjCJjk5DDPnU"
target="_blank">monetization dashboard</a></p>
row: 28
col: 0
width: 24
height: 2
- title: Revenue
name: Revenue
model: gaming
explore: events
type: single_value
fields: [events.total_revenue]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
note_state: collapsed
note_display: hover
note_text: Total revenue (In-app Purhcases + Ad)
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 8
width: 4
height: 3
- title: ARPPU
name: ARPPU
model: gaming
explore: events
type: single_value
fields: [events.average_revenue_per_spender]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
note_state: collapsed
note_display: hover
note_text: |-
Average Revenue Per Paying User
(Revenue / IAP Paying Users)
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 12
width: 4
height: 3
- title: New Tile (copy)
name: New Tile (copy)
model: gaming
explore: events
type: looker_line
fields: [events.event_date, events.total_revenue, events.average_revenue_per_spender]
fill_fields: [events.event_date]
filters: {}
sorts: [events.event_date desc]
limit: 500
column_limit: 50
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{axisId: events.total_revenue,
id: events.total_revenue, name: Total Revenue}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, type: linear}, {label: !!null '',
orientation: right, series: [{axisId: events.average_revenue_per_spender,
id: events.average_revenue_per_spender, name: ARPPU (IAP)}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: false
legend_position: center
series_types:
events.average_revenue_per_paying_user: column
events.average_revenue_per_spender: column
point_style: none
series_colors: {}
series_labels:
events.number_of_users: Active Users
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
show_null_points: true
interpolation: linear
title_hidden: true
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 5
col: 8
width: 8
height: 4
- title: ROAS
name: ROAS
model: gaming
explore: events
type: single_value
fields: [events.return_on_ad_spend]
filters:
events.install_group: paid
limit: 500
query_timezone: America/Los_Angeles
note_state: collapsed
note_display: hover
note_text: |-
Return on Ad Spend (revenue/ad spend)
NOTE: only counts revenue from paid users
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 20
width: 4
height: 3
- title: Marketing Spend
name: Marketing Spend
model: gaming
explore: events
type: single_value
fields: [events.total_install_spend]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
hidden_fields: []
y_axes: []
note_state: collapsed
note_display: hover
note_text: Return on Ad Spend (revenue/ad spend)
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 16
width: 4
height: 3
- title: UA Overview
name: UA Overview
model: gaming
explore: events
type: looker_line
fields: [events.event_date, events.total_install_spend, events.return_on_ad_spend]
fill_fields: [events.event_date]
filters:
events.install_group: paid
sorts: [events.event_date desc]
limit: 500
column_limit: 50
stacking: ''
trellis: ''
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
show_value_labels: false
label_density: 25
legend_position: center
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
point_style: none
series_colors: {}
series_labels:
events.number_of_users: Active Users
series_types:
events.average_revenue_per_paying_user: column
events.return_on_ad_spend: column
limit_displayed_rows: false
y_axes: [{label: '', orientation: left, series: [{id: events.total_install_spend,
name: Total Install Cost, axisId: events.total_install_spend}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, type: linear}, {
label: !!null '', orientation: right, series: [{id: events.return_on_ad_spend,
name: ROAS, axisId: events.return_on_ad_spend}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, type: linear}]
y_axis_combined: true
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
x_axis_scale: auto
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
show_null_points: true
interpolation: linear
hidden_fields: []
title_hidden: true
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 5
col: 16
width: 8
height: 4
- title: CPI (paid only)
name: CPI (paid only)
model: gaming
explore: events
type: single_value
fields: [events.cost_per_install]
filters:
events.install_group: paid
limit: 500
column_limit: 50
note_state: collapsed
note_display: hover
note_text: Cost per Install
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 12
col: 0
width: 4
height: 2
- title: Install Sources
name: Install Sources
model: gaming
explore: events
type: table
fields: [events.install_source, events.return_on_ad_spend, events.total_revenue,
events.total_install_spend, events.number_of_new_users, events.device_platform]
pivots: [events.device_platform]
filters:
events.install_group: paid
sorts: [events.number_of_new_users desc 0, events.device_platform]
limit: 500
column_limit: 50
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
show_totals: true
show_row_totals: true
show_view_names: false
show_row_numbers: false
transpose: false
truncate_text: true
size_to_fit: false
series_labels:
events.number_of_users: Active Users
events.d7_revenue_per_retained_user: D7 Revenue
events.number_of_new_users: Installs
events.total_install_spend: Spend
series_cell_visualizations:
events.return_on_ad_spend:
is_active: true
table_theme: gray
limit_displayed_rows: false
enable_conditional_formatting: true
header_text_alignment: left
header_font_size: '10'
rows_font_size: '10'
conditional_formatting: [{type: along a scale..., value: !!null '', background_color: !!null '',
font_color: !!null '', color_application: {collection_id: 611da387-0e33-4239-aef0-a187c149cf88,
palette_id: 4f09bd77-8e34-41d6-a356-12c05cdb2de5, options: {steps: 5, reverse: true}},
bold: false, italic: false, strikethrough: false, fields: []}, {type: along
a scale..., value: !!null '', background_color: !!null '', font_color: !!null '',
color_application: {collection_id: 611da387-0e33-4239-aef0-a187c149cf88, palette_id: 4f09bd77-8e34-41d6-a356-12c05cdb2de5},
bold: false, italic: false, strikethrough: false, fields: []}]
conditional_formatting_include_totals: false
conditional_formatting_include_nulls: false
truncate_column_names: false
subtotals_at_bottom: false
hide_totals: false
hide_row_totals: false
stacking: ''
trellis: ''
show_value_labels: false
label_density: 25
legend_position: center
x_axis_gridlines: false
y_axis_gridlines: false
point_style: none
series_colors: {}
series_types: {}
y_axes: [{label: '', orientation: left, series: [{id: events.total_revenue, name: Total
Revenue, axisId: events.total_revenue}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, type: linear}, {label: !!null '',
orientation: right, series: [{id: events.average_revenue_per_paying_user,
name: ARPPU (IAP), axisId: events.average_revenue_per_paying_user}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, type: linear}]
y_axis_combined: true
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
x_axis_scale: auto
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
show_null_points: true
interpolation: linear
note_state: expanded
note_display: above
note_text: 'Recommended Action: Drill into Install Sources to see what campaigns
are working'
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 12
col: 10
width: 14
height: 7
- title: Installs by Day
name: Installs by Day
model: gaming
explore: events
type: looker_line
fields: [events.install_group, events.number_of_new_users, events.event_date]
pivots: [events.install_group]
fill_fields: [events.event_date]
filters: {}
sorts: [events.number_of_new_users desc 0, events.install_group]
limit: 500
column_limit: 50
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{id: cross - events.number_of_new_users,
name: cross, axisId: events.number_of_new_users}, {id: organic - events.number_of_new_users,
name: organic, axisId: events.number_of_new_users}, {id: paid - events.number_of_new_users,
name: paid, axisId: events.number_of_new_users}], showLabels: false, showValues: true,
unpinAxis: false, tickDensity: default, tickDensityCustom: 5, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: false
legend_position: center
series_types: {}
point_style: none
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
show_null_points: true
interpolation: linear
value_labels: labels
label_type: labPer
inner_radius: 60
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 12
col: 4
width: 6
height: 7
- title: Retention by Source
name: Retention by Source
model: gaming
explore: events
type: looker_line
fields: [events.retention_day, events.number_of_new_users, events.number_of_users,
events.install_source]
pivots: [events.install_source]
filters:
events.retention_day: "<=30"
sorts: [events.retention_day, events.install_source]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: still_active, label: "% still active", expression: "${events.number_of_users}/max(${events.number_of_new_users})",
value_format: !!null '', value_format_name: percent_2, _kind_hint: measure,
_type_hint: number}]
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{id: cross - still_active, name: cross,
axisId: still_active}, {id: organic - still_active, name: organic, axisId: still_active},
{id: paid - still_active, name: paid, axisId: still_active}], showLabels: true,
showValues: true, maxValue: 0.6, unpinAxis: false, tickDensity: default, type: log}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
x_axis_label: days since signup
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: true
limit_displayed_rows_values:
show_hide: hide
first_last: first
num_rows: '1'
legend_position: center
series_types: {}
point_style: none
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
show_null_points: false
interpolation: linear
value_labels: labels
label_type: labPer
inner_radius: 60
hidden_fields: [events.number_of_new_users, events.number_of_users]
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 21
col: 12
width: 12
height: 7
- title: D7 Retention
name: D7 Retention
model: gaming
explore: events
type: single_value
fields: [events.d7_retention_rate]
filters:
events.user_first_seen_date: 30 days ago for 30 days
limit: 500
column_limit: 50
note_state: collapsed
note_display: hover
note_text: D7 % = session on D7/ session on D0
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 24
col: 0
width: 4
height: 2
- title: D1 Retention
name: D1 Retention
model: gaming
explore: events
type: single_value
fields: [events.d1_retention_rate]
filters:
events.user_first_seen_date: 30 days ago for 30 days
limit: 500
column_limit: 50
note_state: collapsed
note_display: hover
note_text: D1 % = session on D1/ session on D0
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 21
col: 0
width: 4
height: 3
- title: D14 Retention
name: D14 Retention
model: gaming
explore: events
type: single_value
fields: [events.d14_retention_rate]
filters:
events.user_first_seen_date: 30 days ago for 30 days
limit: 500
column_limit: 50
note_state: collapsed
note_display: hover
note_text: D14 % = session on D14/ session on D0
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 26
col: 0
width: 4
height: 2
- title: "% New Paid Users"
name: "% New Paid Users"
model: gaming
explore: events
type: looker_donut_multiples
fields: [events.install_group, events.number_of_new_users]
pivots: [events.install_group]
filters: {}
sorts: [events.install_group]
limit: 500
column_limit: 50
show_value_labels: true
font_size: 12
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: bottom, series: [{axisId: paid - events.number_of_new_users,
id: paid - events.number_of_new_users, name: paid}, {axisId: organic -
events.number_of_new_users, id: organic - events.number_of_new_users,
name: organic}], showLabels: false, showValues: false, unpinAxis: false,
tickDensity: default, tickDensityCustom: 5, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: percent
limit_displayed_rows: false
legend_position: center
series_types: {}
point_style: none
label_density: 25
x_axis_scale: auto
y_axis_combined: true
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
value_labels: labels
label_type: labPer
inner_radius: 60
up_color: "#2196F3"
down_color: "#03d7f4"
total_color: "#00BCD4"
custom_color_enabled: true
custom_color: ''
show_single_value_title: true
show_comparison: false
comparison_type: value
comparison_reverse_colors: false
show_comparison_label: true
hidden_fields:
note_state: collapsed
note_display: hover
note_text: What percentage of users did we pay for (vs organic)?
title_hidden: true
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 14
col: 0
width: 4
height: 5
- title: D1& D7 Retention
name: D1& D7 Retention
model: gaming
explore: events
type: looker_line
fields: [events.d1_retention_rate, events.user_first_seen_date, events.d7_retention_rate]
filters:
events.user_first_seen_date: 30 days ago for 30 days
sorts: [events.user_first_seen_date desc]
limit: 500
column_limit: 50
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{id: events.d1_retention_rate,
name: D1 Retention Rate, axisId: events.d1_retention_rate}], showLabels: true,
showValues: true, maxValue: !!null '', minValue: !!null '', unpinAxis: false,
tickDensity: default, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: true
limit_displayed_rows_values:
show_hide: hide
first_last: first
num_rows: '1'
legend_position: center
series_types: {}
point_style: none
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
reference_lines: [{reference_type: line, range_start: max, range_end: min, margin_top: deviation,
margin_value: mean, margin_bottom: deviation, label_position: center, color: "#8BC34A",
line_value: '0.45', label: d1 goal 45%}, {reference_type: line, range_start: max,
range_end: min, margin_top: deviation, margin_value: mean, margin_bottom: deviation,
label_position: right, color: "#ff3b3b", line_value: '0.16', label: d7 goal
16%}]
show_null_points: false
interpolation: linear
value_labels: labels
label_type: labPer
inner_radius: 60
hidden_fields: []
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 21
col: 4
width: 8
height: 7
- title: LTV (0-30 days)
name: LTV (0-30 days)
model: gaming
explore: events
type: looker_area
fields: [events.retention_day, events.average_ad_revenue_per_user, events.average_iap_revenue_per_user]
filters:
events.retention_day: "<=30"
sorts: [events.retention_day]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: ltv_iap, label: LTV - IAP, expression: 'running_total(${events.average_iap_revenue_per_user})',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number},
{table_calculation: ltv_ads, label: LTV - Ads, expression: 'running_total(${events.average_ad_revenue_per_user})',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number},
{table_calculation: ltv_growth, label: ltv growth %, expression: "(${total}\
\ - ${prev_day})/${prev_day}", value_format: !!null '', value_format_name: percent_2,
_kind_hint: measure, _type_hint: number}, {table_calculation: total, label: total,
expression: "${ltv_ads}+ ${ltv_iap}", value_format: !!null '', value_format_name: usd,
_kind_hint: measure, _type_hint: number}, {table_calculation: prev_day, label: prev
day, expression: 'offset(${total},-1)', value_format: !!null '', value_format_name: usd,
_kind_hint: measure, _type_hint: number}]
stacking: normal
trellis: ''
trellis_rows: 5
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
show_value_labels: false
label_density: 25
legend_position: center
hide_legend: false
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
point_style: none
series_colors: {}
series_labels:
growth: ltv growth
series_types:
growth: column
ltv_growth: column
limit_displayed_rows: false
hidden_series: [ltv_total, ltv_growth]
y_axes: [{label: '', orientation: left, series: [{id: ltv_iap, name: LTV - IAP,
axisId: ltv_iap}, {id: ltv_ads, name: LTV - Ads, axisId: ltv_ads}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, type: linear}, {
label: '', orientation: right, series: [{id: ltv_growth, name: ltv growth
%, axisId: ltv_growth}], showLabels: true, showValues: true, unpinAxis: false,
tickDensity: default, type: linear}]
y_axis_combined: true
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
x_axis_label: Days since player start
show_x_axis_ticks: true
x_axis_scale: auto
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
reference_lines: [{reference_type: line, range_start: max, range_end: min, margin_top: deviation,
margin_value: mean, margin_bottom: deviation, label_position: left, color: "#8BC34A",
line_value: '1'}]
trend_lines: []
show_null_points: true
interpolation: linear
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
ordering: none
show_null_labels: false
hidden_fields: [events.average_iap_revenue_per_user, events.average_ad_revenue_per_user,
total, prev_day]
note_state: expanded
note_display: hover
note_text: How long does it take to make $1? (for all users)
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 30
col: 0
width: 12
height: 6
- title: "% Spenders"
name: "% Spenders"
model: gaming
explore: events
type: single_value
fields: [events.percent_spenders]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
series_types: {}
up_color: "#2196F3"
down_color: "#03d7f4"
total_color: "#00BCD4"
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 30
col: 12
width: 4
height: 6
- title: Revenue Breadkdown
name: Revenue Breadkdown
model: gaming
explore: events
type: looker_bar
fields: [events.total_iap_revenue, events.total_ad_revenue]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: normal
limit_displayed_rows: false
hide_legend: false
legend_position: center
font_size: '12'
series_types: {}
point_style: none
series_labels:
events.total_ad_revenue: Ad Revenue
events.total_iap_revenue: IAP revenue
show_value_labels: true
label_density: 25
x_axis_scale: auto
y_axis_combined: true
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
up_color: "#2196F3"
down_color: "#03d7f4"
total_color: "#00BCD4"
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 30
col: 16
width: 8
height: 6
- title: Daily Installs
name: Daily Installs
model: gaming
explore: events
type: single_value
fields: [events.event_date, events.number_of_new_users]
fill_fields: [events.event_date]
filters: {}
sorts: [events.event_date desc]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: daily_installs, label: Daily Installs, expression: 'mean(${events.number_of_new_users})',
value_format: '[>=1000000]0.00,,"M";[>=1000]0.00,"K";0', value_format_name: !!null '',
_kind_hint: measure, _type_hint: number}]
note_state: expanded
note_display: hover
note_text: Average daily new players
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 6
col: 0
width: 4
height: 3
filters:
- name: Date Range
title: Date Range
type: field_filter
default_value: 30 days ago for 30 days
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.event_date
- name: Drill Down
title: Drill Down
type: field_filter
default_value: device^_platform
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.drill_by
- name: Platform
title: Platform
type: field_filter
default_value: ''
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.device_platform
- name: Install Source
title: Install Source
type: field_filter
default_value: ''
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.install_source
- name: Country
title: Country
type: field_filter
default_value: ''
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.country
- name: Game
title: Game
type: field_filter
default_value: Lookerwood Farm
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.game_name
Raw
events.view
view: events {
sql_table_name: `gaming_demo_dev.events_sessionized` ;;
dimension: unique_event_id {
primary_key: yes
type: string
sql: ${TABLE}.unique_event_id ;;
}
dimension: ad_revenue {
type: number
sql: ${TABLE}.ad_revenue ;;
}
dimension: continent {
group_label: "Location"
type: string
sql: ${TABLE}.continent ;;
}
dimension: region {
group_label: "Location"
type: string
sql: ${TABLE}.region ;;
}
dimension: country {
group_label: "Location"
type: string
map_layer_name: countries
sql: ${TABLE}.country ;;
}
dimension: device_brand {
type: string
sql: ${TABLE}.device_brand ;;
}
dimension: device_language {
type: string
sql: ${TABLE}.device_language ;;
}
dimension: device_model {
type: string
sql: ${TABLE}.device_model ;;
}
dimension: device_os_version {
type: string
sql: ${TABLE}.device_os_version ;;
}
dimension: device_platform {
type: string
sql: ${TABLE}.device_platform ;;
}
dimension_group: event {
type: time
timeframes: [
raw,
time,
minute,
date,
month_name,
day_of_month,
week,
month,
quarter,
year
]
sql: ${TABLE}.event ;;
}
dimension: event_name {
type: string
sql: ${TABLE}.event_name ;;
}
dimension: game_name {
type: string
sql: ${TABLE}.game_name ;;
}
dimension: game_version {
type: string
sql: ${TABLE}.game_version ;;
}
dimension: iap_revenue {
type: number
sql: ${TABLE}.iap_revenue ;;
}
dimension: install_cost {
type: number
sql: ${TABLE}.install_cost ;;
}
dimension: ad_network {
type: string
sql: ${TABLE}.ad_network ;;
}
dimension: user_id {
type: string
sql: ${TABLE}.user_id ;;
}
dimension: player_level {
type: number
sql: ${TABLE}.player_level ;;
}
dimension: player_session_sequence {
type: number
sql: ${TABLE}.player_session_sequence ;;
}
dimension_group: user_first_seen {
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.user_first_seen ;;
}
# Session stuff
dimension: unique_session_id {
type: string
value_format_name: id
hidden: yes
sql: ${TABLE}.unique_session_id ;;
}
dimension: event_sequence_within_session {
type: number
value_format_name: id
sql: ${TABLE}.event_sequence_within_session ;;
}
dimension: inverse_event_sequence_within_session {
type: number
value_format_name: id
sql: ${TABLE}.inverse_event_sequence_within_session ;;
}
# Drill Selector
parameter: drill_by {
type: string
default_value: "device_platform"
allowed_value: { label: "Country" value: "country" }
allowed_value: { label: "Platform" value: "device_platform" }
allowed_value: { label: "Game" value: "game_name" }
allowed_value: { label: "Game Version" value: "game_version" }
allowed_value: { label: "Install Source" value: "install_source" }
}
dimension: drill_field {
hidden: yes
type: string
label_from_parameter: drill_by
sql:
{% case drill_by._parameter_value %}
{% when "'country'" %}
${country}
{% when "'device_platform'" %}
${device_platform}
{% when "'game_name'" %}
${game_name}
{% when "'game_version'" %}
${game_version}
{% when "'install_source'" %}
${install_source}
{% else %}
null
{% endcase %} ;;
}
# Dimensions
dimension: install_group {
description: "Where did the user originate? (organic or paid)"
type: string
sql: CASE
WHEN ${install_source} = 'organic' then 'organic'
ELSE 'paid'
END ;;
drill_fields: [install_source]
}
dimension: install_source {
type: string
sql: ${TABLE}.install_source ;;
drill_fields: [campaign_name]
}
dimension: campaign_id {
type: string
sql: ${TABLE}.campaign_id ;;
}
dimension: campaign_name {
link: {
label: "Manage this campaign in {{ events.install_source._value }}"
url: "http://{{events.install_source._value}}/manage/{{value}}"
}
sql: ${TABLE}.campaign_name ;;
}
dimension: campaign_type {
sql:
case
when ${campaign_name} LIKE '%video%' THEN 'video'
when ${campaign_name} LIKE '%playable%' THEN 'playable'
when ${campaign_name} LIKE '%low%' THEN 'banner - low density'
when ${campaign_name} LIKE '%high%' THEN 'banner - high density'
else 'banner - low density'
END ;;
drill_fields: [install_source,campaign_name]
}
dimension: is_first_session {
type: yesno
sql: ${player_session_sequence} = '1' ;;
}
# User Counts
measure: number_of_users {
group_label: "User Counts"
type: count_distinct
sql: ${user_id};;
value_format_name: large_number
drill_fields: [drill_field,number_of_users]
}
measure: number_of_new_users {
group_label: "User Counts"
description: "Start date = Play Date"
type: count_distinct
sql: ${user_id};;
filters: {
field: retention_day
value: "0"
}
value_format_name: large_number
drill_fields: [drill_field,number_of_new_users]
}
measure: number_of_paid_users {
group_label: "User Counts"
description: "User's acquired through paid channels"
type: count_distinct
sql: ${user_id};;
filters: {
field: install_group
value: "paid"
}
value_format_name: large_number
drill_fields: [drill_field,number_of_paid_users]
}
# Event Counts
measure: count {
group_label: "Event Counts"
label: "Number of Events"
type: count
value_format_name: large_number
drill_fields: [drill_field,count]
}
measure: number_of_ads_shown {
group_label: "Event Counts"
type: count
value_format_name: large_number
filters: {
field: event_name
value: "Ad_Watched"
}
drill_fields: [drill_field,count]
}
measure: number_of_level_ups {
group_label: "Event Counts"
type: count
value_format_name: large_number
filters: {
field: event_name
value: "Level_Up"
}
drill_fields: [drill_field,count]
}
measure: ads_shown_per_user {
group_label: "Event Counts"
description: "No. of Ads Shown / Number of Users"
type: number
value_format_name: decimal_2
sql: 1.0 * ${number_of_ads_shown} / NULLIF(${number_of_users},0) ;;
drill_fields: [drill_field,ads_shown_per_user]
}
measure: number_of_sesssions {
type: count_distinct
value_format_name: large_number
sql: ${unique_session_id} ;;
drill_fields: [drill_field,number_of_sesssions]
}
# Misc
dimension: user_time_bucketed {
hidden: yes
type: string
sql: CONCAT(${event_minute}, ${user_id} ) ;;
}
measure: number_of_time_buckets { # Counts the number of minute buckets for each user
hidden: yes
type: count_distinct
sql: ${user_time_bucketed} ;;
}
measure: approximate_usage_in_minutes { # Multiply number of buckets by 2 to get approximate usage in minutes
group_label: "Usage"
type: number
sql: ${number_of_time_buckets} ;;
}
measure: average_usage_minutes_per_user {
group_label: "Usage"
type: number
sql: 1.0*${approximate_usage_in_minutes}/nullif(${number_of_users},0) ;;
}
dimension_group: current {
hidden: yes
description: "the time right now"
type: time
sql: CURRENT_TIMESTAMP() ;;
}
dimension: days_since_user_signup {
type: number
description: "Days since first seen (from today)"
sql: DATE_DIFF(${current_date}, ${user_first_seen_date}, DAY);;
}
dimension: event_type {
description: "monetization,progression,onboarding,gameplay"
type: string
sql: case
when ${event_name} in ('Ad_Watched','IAP_Started','in_app_purchase') then 'monetization'
when ${event_name} in ('Level_Up','Skin_Unlocked','Gem_Spend') then 'progression'
when ${event_name} in ('FTUE_Stage_Complete','FTUE_Stage_Started') then 'onboarding'
when ${event_name} in ('Session_Started','Harvest_Done','Match_Ended','Match_Started') then 'gameplay'
else 'other'
end ;;
}
# Retention
dimension: retention_day {
group_label: "Retention"
description: "Days since first seen (from event date)"
type: number
sql: DATE_DIFF(${event_date}, ${user_first_seen_date}, DAY);;
}
# D1
measure: d1_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 1"
type: count_distinct sql: ${user_id} ;;
filters: {
field: retention_day
value: "1"
}
drill_fields: [drill_field,d1_retained_users]
}
measure: d1_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 0 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_user_signup
value: ">0"
}
}
measure: d1_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 0 days) that came back to play on day 1"
value_format_name: percent_2
type: number
sql: 1.0 * ${d1_retained_users}/ NULLIF(${d1_eligible_users},0);;
drill_fields: [drill_field,d1_retention_rate]
}
# D7
measure: d7_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 7"
type: count_distinct sql: ${user_id} ;;
filters: {
field: retention_day
value: "7"
}
drill_fields: [drill_field,d7_retained_users]
}
measure: d7_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 7 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_user_signup
value: ">7"
}
drill_fields: [drill_field,d7_eligible_users]
}
measure: d7_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 7 days) that came back to play on day 7"
value_format_name: percent_2
type: number
sql: 1.0 * ${d7_retained_users}/ NULLIF(${d7_eligible_users},0);;
drill_fields: [drill_field,d7_retention_rate]
}
# D14
measure: d14_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 14"
type: count_distinct sql: ${user_id} ;;
filters: {
field: retention_day
value: "14"
}
drill_fields: [drill_field,d14_retained_users]
}
measure: d14_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 14 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_user_signup
value: ">14"
}
drill_fields: [drill_field,d14_eligible_users]
}
measure: d14_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 14 days) that came back to play on day 14"
value_format_name: percent_2
type: number
sql: 1.0 * ${d14_retained_users}/ NULLIF(${d14_eligible_users},0);;
drill_fields: [drill_field,d14_retention_rate]
}
# D30
measure: d30_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 30"
type: count_distinct sql: ${user_id} ;;
filters: {
field: retention_day
value: "30"
}
drill_fields: [drill_field,d30_retained_users]
}
measure: d30_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 30 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_user_signup
value: ">30"
}
drill_fields: [drill_field,d30_eligible_users]
}
measure: d30_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 30 days) that came back to play on day 30"
value_format_name: percent_2
type: number
sql: 1.0 * ${d30_retained_users}/ NULLIF(${d30_eligible_users},0);;
drill_fields: [drill_field,d30_retention_rate]
}
# UA
measure: cost_per_install {
label: "CPI"
description: "Total install spend/number of new users"
group_label: "User Acquistion"
type: number
sql: ${total_install_spend}/ NULLIF(${number_of_new_users},0) ;;
value_format_name: large_usd
drill_fields: [drill_field,cost_per_install]
}
measure: total_install_spend {
group_label: "User Acquistion"
label: "Total Marketing Spend"
description: "Total spent to acquire users"
type: sum
sql: ${install_cost} ;;
value_format_name: large_usd
drill_fields: [drill_field,total_install_spend]
}
measure: return_on_ad_spend {
group_label: "User Acquistion"
label: "ROAS"
description: "Revenue (from paid users) / Cost (to acquire those users) "
type: number
sql: 1.0 * ${total_revenue_from_paid_users} / NULLIF(${total_install_spend},0) ;;
value_format_name: percent_2
html:
{% if value <= 1.0 %}
<a style="color: red; font-size:100%" href="#drillmenu" target="_self">{{ rendered_value }}</a>
{% elsif value > 1.0 %}
<a style="color: green; font-size:100%" href="#drillmenu" target="_self">{{ rendered_value }}</a>
{% endif %} ;;
drill_fields: [drill_field,total_install_spend,return_on_ad_spend,number_of_paid_users,total_revenue_from_paid_users,cost_per_install]
}
# Monetization
dimension: is_paying_user {
type: yesno
description: "Had an IAP purhcase in selected time period"
sql: ${iap_revenue} > 0 ;;
}
dimension: is_iap_purchase {
type: yesno
hidden: yes
sql: ${event_name} = 'in_app_purchase' ;;
}
dimension: iap_purchase_tier {
group_label: "Monetization"
label: "IAP Purchase Tier"
description: "How big was each purchase?"
type: tier
tiers: [0,10,20,30]
sql: ${iap_revenue} ;;
style: integer
value_format_name: usd_0
}
measure: number_of_iap_purchases {
group_label: "Monetization"
label: "Number of IAP Purchases"
type: count
filters: {
field: is_iap_purchase
value: "yes"
}
drill_fields: [drill_field,total_iap_revenue]
}
measure: transactions_per_spender {
type: number
description: "For paying users, how many transactions do they make"
group_label: "Monetization"
sql: 1.0 * ${number_of_iap_purchases}/nullif(${number_of_spenders},0) ;;
value_format_name: decimal_2
drill_fields: [drill_field,transactions_per_spender]
}
measure: total_iap_revenue {
label: "Total IAP Revenue"
group_label: "Monetization"
description: "Total Revenue from In-App Purchases"
type: sum
sql: ${iap_revenue} ;;
value_format_name: large_usd
drill_fields: [drill_field,total_iap_revenue]
}
measure: total_ad_revenue {
group_label: "Monetization"
description: "Total Revenue from Ads"
type: sum
sql: ${ad_revenue} ;;
value_format_name: large_usd
drill_fields: [drill_field,total_ad_revenue]
}
dimension: combined_revenue {
type: number
sql: IFNULL(${iap_revenue},0) + IFNULL(${ad_revenue},0) ;;
value_format_name: large_usd
}
measure: total_revenue {
group_label: "Monetization"
description: "IAP + Ad Revenue"
type: sum
sql: ${combined_revenue} ;;
value_format_name: large_usd
drill_fields: [drill_field,total_revenue]
}
measure: total_revenue_after_UA {
label: "Total Revenue After UA"
group_label: "Monetization"
description: "Revenue - Marketing Spend"
type: number
sql: ${total_revenue} - ${total_install_spend} ;;
value_format_name: large_usd
drill_fields: [drill_field,total_revenue_after_UA]
}
measure: total_revenue_from_paid_users {
group_label: "Monetization"
description: "IAP + Ad Revenue (for users acquiried by marketing)"
type: sum
sql: ${combined_revenue} ;;
filters: {
field: install_group
value: "paid"
}
value_format_name: large_usd
drill_fields: [drill_field,total_revenue]
}
measure: average_revenue {
group_label: "Monetization"
description: "IAP + Ad Revenue"
type: average
sql: ${combined_revenue} ;;
value_format_name: large_usd
drill_fields: [drill_field,average_revenue]
}
measure: number_of_spenders {
group_label: "Monetization"
description: "Number of users with IAP purchases"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: is_paying_user
value: "Yes"
}
drill_fields: [drill_field,number_of_spenders]
}
measure: percent_spenders {
group_label: "Monetization"
description: "% of users with IAP purchases"
type: number
sql: 1.0 * ${number_of_spenders} / NULLIF(${number_of_users},0) ;;
value_format_name: percent_2
drill_fields: [drill_field,percent_spenders]
}
measure: average_revenue_per_spender {
group_label: "Monetization"
label: "ARPPU (IAP)"
description: " Total Revenue / Number of IAP Paying Users"
type: number
sql: 1.0 * ${total_iap_revenue} / NULLIF(${number_of_spenders},0) ;;
value_format_name: large_usd
drill_fields: [drill_field,average_revenue_per_spender]
}
measure: average_revenue_per_user {
group_label: "Monetization"
label: "ARPU"
description: "(Average revenue per user) = Total Revenue (IAP + Ad) / Total Number of Users"
type: number
sql: 1.0 * ${total_revenue} / NULLIF(${number_of_users},0) ;;
value_format_name: large_usd
drill_fields: [drill_field,average_revenue_per_user]
}
measure: average_ad_revenue_per_user {
group_label: "Monetization"
label: "ARPU - Ads"
description: "(Average revenue per user) = Ad Revenue / Total Number of Users"
type: number
sql: 1.0 * ${total_ad_revenue} / NULLIF(${number_of_users},0) ;;
value_format_name: large_usd
drill_fields: [drill_field,average_revenue_per_user]
}
measure: average_iap_revenue_per_user {
group_label: "Monetization"
label: "ARPU - IAP"
description: "(Average revenue per user) = IAP Revenue / Total Number of Users"
type: number
sql: 1.0 * ${total_iap_revenue} / NULLIF(${number_of_users},0) ;;
value_format_name: large_usd
drill_fields: [drill_field,average_revenue_per_user]
}
measure: total_d1_revenue {
group_label: "Monetization"
description: "Revenue (ads + IAP) on day 1"
type: sum
sql: ${combined_revenue} ;;
filters: {
field: retention_day
value: "<=1"
}
filters: {
field: days_since_user_signup
value: ">0"
}
drill_fields: [drill_field,total_d1_revenue]
value_format_name: large_usd
}
measure: d1_revenue_per_retained_user {
group_label: "Monetization"
description: "Revenue per user (that are retained for 1 days)"
value_format_name: large_usd
type: number
sql: 1.0 * ${total_d1_revenue}/ NULLIF(${d1_retained_users},0);;
drill_fields: [drill_field,d1_revenue_per_retained_user]
}
measure: total_d7_revenue {
group_label: "Monetization"
description: "Revenue (ads + IAP) on day 7"
type: sum
sql: ${combined_revenue} ;;
filters: {
field: retention_day
value: "<=7"
}
filters: {
field: days_since_user_signup
value: ">7"
}
drill_fields: [drill_field,total_d7_revenue]
value_format_name: large_usd
}
measure: d7_revenue_per_retained_user {
group_label: "Monetization"
description: "Revenue per user (that are retained for 7 days)"
value_format_name: large_usd
type: number
sql: 1.0 * ${total_d7_revenue}/ NULLIF(${d7_retained_users},0);;
drill_fields: [drill_field,d7_revenue_per_retained_user]
}
measure: total_d14_revenue {
group_label: "Monetization"
description: "Revenue (ads + IAP) on day 14"
type: sum
sql: ${combined_revenue} ;;
filters: {
field: retention_day
value: "<=14"
}
filters: {
field: days_since_user_signup
value: ">14"
}
drill_fields: [drill_field,total_d14_revenue]
value_format_name: large_usd
}
measure: d14_revenue_per_retained_user {
group_label: "Monetization"
description: "Revenue per user (that are retained for 14 days)"
value_format_name: large_usd
type: number
sql: 1.0 * ${total_d14_revenue}/ NULLIF(${d14_retained_users},0);;
drill_fields: [drill_field,d14_revenue_per_retained_user]
}
measure: total_d30_revenue {
group_label: "Monetization"
description: "Revenue (ads + IAP) on day 30"
type: sum
sql: ${combined_revenue} ;;
filters: {
field: retention_day
value: "<=30"
}
filters: {
field: days_since_user_signup
value: ">30"
}
drill_fields: [drill_field,total_d30_revenue]
value_format_name: large_usd
}
measure: d30_revenue_per_retained_user {
group_label: "Monetization"
description: "Revenue per user (that are retained for 30 days)"
value_format_name: large_usd
type: number
sql: 1.0 * ${total_d30_revenue}/ NULLIF(${d30_retained_users},0);;
drill_fields: [drill_field,d30_revenue_per_retained_user]
}
measure: ltv {
label: "LTV"
description: "ARPU x days played "
type: number
sql: ${average_revenue_per_user} * ${days_played} ;;
value_format_name: usd
}
### For Calculating User Fact Table with Native Derived Table
measure: player_first_seen {
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
type: date_time
sql: min(${event_raw}) ;;
}
measure: player_last_seen {
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
type: date_time
sql: max(${event_raw}) ;;
}
measure: highest_level_reached {
type: max
sql: IFNULL(${TABLE}.player_level,0) ;;
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
}
measure: days_played {
type: count_distinct
sql: ${event_date} ;;
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
}
measure: most_commonly_used_device {
type: string
sql: max(${device_model}) ;;
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
}
measure: number_of_devices_used {
type: count_distinct
sql: ${device_model} ;;
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
}
measure: most_commonly_played_country {
type: string
sql: max(${country}) ;;
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
}
measure: number_of_countries_played_in {
type: count_distinct
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
sql: ${country} ;;
}
measure: first_event {
type: date_time
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
sql: min(${event_raw}) ;;
}
measure: latest_event {
type: date_time
group_label: "Fact Table"
description: "Not for direct use, use for NDT"
sql: max(${event_raw}) ;;
}
dimension_group: since_session_start {
type: duration
intervals: [second,minute]
sql_start: ${session_facts.session_start_at_raw} ;;
sql_end: ${event_raw} ;;
}
}
Raw
funnel_explorer.view
################################################################
# Funnel Explorer View
################################################################
view: funnel_explorer {
# In this query, we retrieve, for each session, the first and last instance of each event in our sequence. If,
# for each event, its first instance occurs before the last instance of the next event in the sequence, then
# that is considered a completion of the sequence.
derived_table: {
sql: SELECT
events_sessionized.unique_session_id
, events_sessionized.user_id
, session_facts.session_start AS session_start
, MIN(
CASE WHEN
{% condition event_1 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_1
, MIN(
CASE WHEN
{% condition event_2 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_2_first
, MAX(
CASE WHEN
{% condition event_2 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_2_last
, MIN(
CASE WHEN
{% condition event_3 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_3_first
, MAX(
CASE WHEN
{% condition event_3 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_3_last
, MIN(
CASE WHEN
{% condition event_4 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_4_first
, MAX(
CASE WHEN
{% condition event_4 %} event_name {% endcondition %}
THEN event
ELSE NULL END
) AS event_4_last
FROM ${events.SQL_TABLE_NAME} AS events_sessionized
LEFT JOIN ${session_facts.SQL_TABLE_NAME} as session_facts
ON events_sessionized.unique_session_id = session_facts.unique_session_id
WHERE {% condition event_time %} event {% endcondition %}
and {% condition game_name %} events_sessionized.game_name {% endcondition %}
GROUP BY 1,2,3
;;
}
parameter: event_1 {
suggest_dimension: events.event_name
suggest_explore: events
}
parameter: event_2 {
suggest_dimension: events.event_name
suggest_explore: events
}
parameter: event_3 {
suggest_dimension: events.event_name
suggest_explore: events
}
parameter: event_4 {
suggest_dimension: events.event_name
suggest_explore: events
}
filter: event_time {
type: date_time
}
filter: game_name {
type: string
suggest_dimension: events.game_name
suggest_explore: events
}
dimension: unique_session_id {
type: string
primary_key: yes
sql: ${TABLE}.unique_session_id ;;
link: {
label: "See session detail"
url: "/dashboards/Xb2IL2W022TXYLgHiOkAYV?Session%20ID={{value}}"
}
}
dimension: user_id {
type: string
sql: ${TABLE}.user_id ;;
}
dimension_group: session_start {
type: time
# hidden: TRUE
convert_tz: no
timeframes: [
time,
date,
week,
month,
year,
raw
]
sql: ${TABLE}.session_start ;;
}
dimension_group: event_1 {
description: "First occurrence of event 1"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_1 ;;
}
dimension_group: event_2_first {
description: "First occurrence of event 2"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_2_first ;;
}
dimension_group: event_2_last {
description: "Last occurrence of event 2"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_2_last ;;
}
dimension_group: event_3_first {
description: "First occurrence of event 3"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_3_first ;;
}
dimension_group: event_3_last {
description: "Last occurrence of event 3"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_3_last ;;
}
dimension_group: event_4_first {
description: "First occurrence of event 4"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_4_first ;;
}
dimension_group: event_4_last {
description: "Last occurrence of event 4"
type: time
convert_tz: no
timeframes: [raw,time]
hidden: yes
sql: ${TABLE}.event_4_last ;;
}
dimension: event1_before_event2 {
type: yesno
hidden: yes
sql: ${TABLE}.event_1 < ${TABLE}.event_2_last ;;
}
dimension: event1_before_event3 {
type: yesno
hidden: yes
sql: ${TABLE}.event_1 < ${TABLE}.event_3_last ;;
}
dimension: event1_before_event4 {
type: yesno
hidden: yes
sql: ${TABLE}.event_1 < ${TABLE}.event_4_last ;;
}
dimension: event2_before_event3 {
type: yesno
hidden: yes
sql: ${TABLE}.event_2_first < ${TABLE}.event_3_last ;;
}
dimension: event2_before_event4 {
type: yesno
hidden: yes
sql: ${TABLE}.event_2_first < ${TABLE}.event_4_last ;;
}
dimension: event3_before_event4 {
type: yesno
hidden: yes
sql: ${TABLE}.event_3_first < ${TABLE}.event_4_last ;;
}
dimension: reached_event_1 {
hidden: yes
type: yesno
sql: (${event_1_raw} IS NOT NULL)
;;
}
dimension: reached_event_2 {
hidden: yes
type: yesno
sql: (${event_1_raw} IS NOT NULL AND ${event_2_first_raw} IS NOT NULL AND ${event_1_raw} < ${event_2_last_raw})
;;
}
dimension: reached_event_3 {
hidden: yes
type: yesno
sql: (${event_1_raw} IS NOT NULL AND ${event_2_last_raw} IS NOT NULL AND ${event_3_last_raw} IS NOT NULL
AND ${event_1_raw} < ${event_2_last_raw} AND ${event_1_raw} < ${event_3_last_raw} AND ${event_2_first_raw} < ${event_3_last_raw})
;;
}
dimension: reached_event_4 {
hidden: yes
type: yesno
sql: (${event_1_raw} IS NOT NULL AND ${event_2_last_raw} IS NOT NULL AND ${event_3_last_raw} IS NOT NULL AND ${event_4_last_raw} IS NOT NULL
AND ${event_1_raw} < ${event_2_last_raw} AND ${event_1_raw} < ${event_3_last_raw} AND ${event_1_raw} < ${event_4_last_raw} AND ${event_2_first_raw} < ${event_3_last_raw} AND ${event_2_first_raw} < ${event_4_last_raw} AND ${event_3_first_raw} < ${event_4_last_raw})
;;
}
dimension: furthest_step {
label: "Furthest Funnel Step Reached"
case: {
when: {
sql: ${reached_event_4} = true ;;
label: "4th"
}
when: {
sql: ${reached_event_3} = true ;;
label: "3rd"
}
when: {
sql: ${reached_event_2} = true ;;
label: "2nd"
}
when: {
sql: ${reached_event_1} = true ;;
label: "1st"
}
else: "no"
}
}
measure: number_of_sessions {
type: count_distinct
drill_fields: [detail*]
sql: ${unique_session_id} ;;
}
measure: count_sessions_event1 {
label: "event 1"
label_from_parameter: event_1
type: count_distinct
sql: ${unique_session_id} ;;
drill_fields: [detail*]
filters: {
field: furthest_step
value: "1st,2nd,3rd,4th"
}
}
measure: count_sessions_event12 {
label: "event 2"
label_from_parameter: event_2
description: "Only includes sessions which also completed event 1"
type: count_distinct
sql: ${unique_session_id} ;;
drill_fields: [detail*]
filters: {
field: furthest_step
value: "2nd,3rd,4th"
}
}
measure: count_sessions_event123 {
label: "event 3"
label_from_parameter: event_3
description: "Only includes sessions which also completed events 1 and 2"
type: count_distinct
sql: ${unique_session_id} ;;
drill_fields: [detail*]
filters: {
field: furthest_step
value: "3rd, 4th"
}
}
measure: count_sessions_event1234 {
label: "event 4"
label_from_parameter: event_4
description: "Only includes sessions which also completed events 1, 2 and 3"
type: count_distinct
sql: ${unique_session_id} ;;
drill_fields: [detail*]
filters: {
field: furthest_step
value: "4th"
}
}
set: detail {
fields: [unique_session_id, user_id, session_start_time,session_facts.session_revenue,session_facts.minutes_session_length]
}
}
Raw
gaming.model
connection: "gaming_demo"
include: "/views/*.view.lkml"
include: "/derived_tables/*.view.lkml"
include: "/lookml_dashboards/*.dashboard.lookml" # include a LookML dashboard called my_dashboard
# Model Configuration
datagroup: events_raw { sql_trigger: SELECT max(event) FROM `se-pbl.gaming_demo_dev.raw_events` WHERE DATE(event) = CURRENT_DATE ;; }
named_value_format: large_usd { value_format: "[>=1000000]\"$\"0.00,,\"M\";[>=1000]\"$\"0.00,\"K\";\"$\"0.00" }
named_value_format: large_number { value_format: "[>=1000000]0.00,,\"M\";[>=1000]0.00,\"K\";0" }
# Explores
explore: events {
persist_with: events_raw
always_filter: {
filters: {
field: event_date
value: "last 7 days"
}
}
join: session_facts {
relationship: many_to_one
sql_on: ${events.unique_session_id} = ${session_facts.unique_session_id} ;;
}
join: user_facts {
view_label: "User Lifetime Values"
relationship: many_to_one
sql_on: ${events.user_id} = ${user_facts.user_id} ;;
}
join: top_countries {
sql_on: ${events.country} = ${top_countries.country} ;;
relationship: many_to_one
}
}
explore: funnel_explorer {
description: "Player Session Funnels"
persist_for: "48 hours"
always_filter: {
filters: {
field: event_time
value: "30 days"
}
filters: {
field: game_name
value: "Lookerwood Farm"
}
}
join: session_facts {
sql_on: ${funnel_explorer.unique_session_id} = ${session_facts.unique_session_id} ;;
relationship: many_to_one
}
join: user_facts {
sql_on: ${funnel_explorer.user_id} = ${user_facts.user_id} ;;
relationship: many_to_one
}
}
explore: session_facts {
label: "Sessions and Users"
description: "Use this to look at a compressed view of Users and Sessions (without event level data)"
join: user_facts {
relationship: many_to_one
sql_on: ${session_facts.user_id} = ${user_facts.user_id} ;;
}
join: lifetime_user_facts {
sql_on: ${session_facts.user_id} = ${lifetime_user_facts.user_id} ;;
relationship: many_to_one
}
}
explore: level_balancing {
description: "Are players able to progress through levels well? (last 30 days)"
join: user_facts {
relationship: many_to_one
sql_on: ${level_balancing.user_id} = ${user_facts.user_id} ;;
}
}
Raw
gaming_monetization.dashboard
- dashboard: game_monetization
title: Game Monetization
layout: newspaper
elements:
- name: "<span class='fa fa-coffee'> Ad Revenue </span>"
type: text
title_text: "<span class='fa fa-coffee'> Ad Revenue </span>"
subtitle_text: Are we doing well monetizing with ads while not affecting gameplay?
row: 18
col: 0
width: 24
height: 2
- name: "<span class='fa fa-credit-card'> IAP Revenue </span>"
type: text
title_text: "<span class='fa fa-credit-card'> IAP Revenue </span>"
subtitle_text: Are users finding our in-app products attractive?
body_text: ''
row: 9
col: 0
width: 24
height: 2
- name: "<span class='fa fa-usd'> Monetization </span>"
type: text
title_text: "<span class='fa fa-usd'> Monetization </span>"
subtitle_text: "<p> Are we building a sustainable business? </p>"
row: 0
col: 0
width: 24
height: 2
- title: Revenue
name: Revenue
model: gaming
explore: events
type: single_value
fields: [events.total_revenue]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
note_state: collapsed
note_display: hover
note_text: Total revenue (In-app Purhcases + Ad)
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 8
width: 4
height: 3
- title: ARPDAU - Ads
name: ARPDAU - Ads
model: gaming
explore: events
type: single_value
fields: [events.average_ad_revenue_per_user, events.event_date]
fill_fields: [events.event_date]
filters: {}
limit: 500
dynamic_fields: [{table_calculation: arpdau_ads, label: ARPDAU - Ads, expression: 'mean(${events.average_ad_revenue_per_user})',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number}]
query_timezone: America/Los_Angeles
hidden_fields: [events.average_ad_revenue_per_user]
note_state: collapsed
note_display: hover
note_text: Average Revenue Per Daily Active User - for ad revenue only
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 20
width: 4
height: 3
- title: Revenue After UA
name: Revenue After UA
model: gaming
explore: events
type: looker_column
fields: [events.event_date, events.total_revenue, events.total_install_spend]
fill_fields: [events.event_date]
filters: {}
sorts: [events.event_date desc]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: ua, label: UA, expression: "${events.total_install_spend}\
\ * -1", value_format: !!null '', value_format_name: usd_0, _kind_hint: measure,
_type_hint: number}]
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{axisId: events.total_revenue,
id: events.total_revenue, name: Total Revenue}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, type: linear}, {label: !!null '',
orientation: right, series: [{axisId: events.average_revenue_per_spender,
id: events.average_revenue_per_spender, name: ARPPU (IAP)}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: normal
limit_displayed_rows: false
legend_position: center
series_types: {}
point_style: none
series_colors: {}
series_labels:
events.number_of_users: Active Users
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
show_null_points: true
interpolation: linear
hidden_fields: [events.total_install_spend]
title_hidden: true
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 5
col: 8
width: 8
height: 4
- title: Revenue after UA
name: Revenue after UA
model: gaming
explore: events
type: single_value
fields: [events.total_revenue_after_UA]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
note_state: collapsed
note_display: hover
note_text: Revenue after UA (Revenue - Marketing Spend)
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 12
width: 4
height: 3
- title: ARPDAU - IAP
name: ARPDAU - IAP
model: gaming
explore: events
type: single_value
fields: [events.average_iap_revenue_per_user, events.event_date]
fill_fields: [events.event_date]
filters: {}
sorts: [events.event_date desc]
limit: 500
dynamic_fields: [{table_calculation: arpdau_iap, label: ARPDAU - IAP, expression: 'mean(${events.average_iap_revenue_per_user})',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number}]
query_timezone: America/Los_Angeles
hidden_fields: [events.average_iap_revenue_per_user]
y_axes: []
note_state: collapsed
note_display: hover
note_text: Average Revenue Per Daily Active User - for in-app purchases revenue
only
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 16
width: 4
height: 3
- title: IAP/Ad Revenue per Player
name: IAP/Ad Revenue per Player
model: gaming
explore: events
type: looker_column
fields: [events.event_date, events.average_ad_revenue_per_user, events.average_iap_revenue_per_user]
fill_fields: [events.event_date]
filters: {}
sorts: [events.event_date desc]
limit: 500
column_limit: 50
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: false
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{axisId: events.average_ad_revenue_per_user,
id: events.average_ad_revenue_per_user, name: ARPU - Ads}, {axisId: events.average_iap_revenue_per_user,
id: events.average_iap_revenue_per_user, name: ARPU - IAP}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: false
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: normal
limit_displayed_rows: false
legend_position: right
series_types: {}
point_style: none
series_colors: {}
series_labels:
events.number_of_users: Active Users
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
reference_lines: []
trend_lines: []
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
show_null_points: true
interpolation: linear
hidden_fields: []
title_hidden: true
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 5
col: 16
width: 8
height: 4
- title: LTV (0-7 days) Per Country
name: LTV (0-7 days) Per Country
model: gaming
explore: events
type: looker_column
fields: [events.retention_day, events.average_revenue_per_user, events.cost_per_install,
events.country]
pivots: [events.country]
filters:
events.retention_day: "<=7"
events.is_top_10_country: 'Yes'
sorts: [events.country 0, events.retention_day]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: ltv_total, label: LTV - Total, expression: 'running_total(${events.average_revenue_per_user})',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number},
{table_calculation: less_than_cpi, label: Less than CPI, expression: 'if(${ltv_total}
< ${cpi},${ltv_total},null)', value_format: !!null '', value_format_name: usd,
_kind_hint: measure, _type_hint: number}, {table_calculation: more_than_cpi,
label: More than CPI, expression: 'if(${ltv_total} >= ${cpi},${ltv_total},null)',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number},
{table_calculation: cpi, label: CPI, expression: 'running_total(${events.cost_per_install})',
value_format: !!null '', value_format_name: usd, _kind_hint: measure, _type_hint: number}]
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{id: events.total_revenue, name: Total
Revenue, axisId: events.total_revenue}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, tickDensityCustom: 5, type: linear},
{label: !!null '', orientation: right, series: [{id: ltv, name: LTV, axisId: ltv}],
showLabels: true, showValues: true, unpinAxis: false, tickDensity: default,
tickDensityCustom: 5, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
x_axis_label: Days since start (0-7)
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: pivot
stacking: normal
limit_displayed_rows: false
hide_legend: true
legend_position: left
trellis_rows: 5
series_types: {}
point_style: none
series_colors:
Brazil - less_than_1: "#FFEB3B"
Brazil - more_than_1: "#4CAF50"
France - less_than_1: "#FFEB3B"
France - more_than_1: "#4CAF50"
Germany - less_than_1: "#FFEB3B"
Germany - more_than_1: "#4CAF50"
Mexico - more_than_1: "#4CAF50"
Mexico - less_than_1: "#FFEB3B"
Poland - less_than_1: "#FFEB3B"
Poland - more_than_1: "#4CAF50"
Russia - less_than_1: "#FFEB3B"
Russia - more_than_1: "#4CAF50"
Turkey - less_than_1: "#FFEB3B"
Turkey - more_than_1: "#4CAF50"
United Kingdom - less_than_1: "#FFEB3B"
United Kingdom - more_than_1: "#4CAF50"
United States - more_than_1: "#4CAF50"
United States - less_than_1: "#FFEB3B"
Vietnam - less_than_1: "#FFEB3B"
Vietnam - more_than_1: "#4CAF50"
Brazil - less_than_cpi: "#CDDC39"
Brazil - more_than_cpi: "#009688"
France - less_than_cpi: "#CDDC39"
Germany - less_than_cpi: "#CDDC39"
Germany - more_than_cpi: "#009688"
Mexico - less_than_cpi: "#CDDC39"
Mexico - more_than_cpi: "#009688"
Poland - less_than_cpi: "#CDDC39"
Poland - more_than_cpi: "#009688"
Russia - less_than_cpi: "#CDDC39"
Russia - more_than_cpi: "#009688"
Turkey - less_than_cpi: "#CDDC39"
Turkey - more_than_cpi: "#009688"
United Kingdom - less_than_cpi: "#CDDC39"
United Kingdom - more_than_cpi: "#009688"
United States - less_than_cpi: "#CDDC39"
United States - more_than_cpi: "#009688"
Vietnam - less_than_cpi: "#CDDC39"
Vietnam - more_than_cpi: "#009688"
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
reference_lines: []
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
show_null_points: true
interpolation: linear
hidden_fields: [events.average_revenue_per_user, ltv_total, events.cost_per_install,
cpi]
note_state: collapsed
note_display: hover
note_text: How long does it take to make our CPI back?
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 31
col: 0
width: 24
height: 11
- title: "% Spenders"
name: "% Spenders"
model: gaming
explore: events
type: single_value
fields: [events.percent_spenders]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
series_types: {}
up_color: "#2196F3"
down_color: "#03d7f4"
total_color: "#00BCD4"
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 11
col: 10
width: 7
height: 2
- title: Revenue Breadkdown
name: Revenue Breadkdown
model: gaming
explore: events
type: looker_bar
fields: [events.total_iap_revenue, events.total_ad_revenue]
filters: {}
limit: 500
query_timezone: America/Los_Angeles
color_application:
collection_id: 611da387-0e33-4239-aef0-a187c149cf88
palette_id: f31efe28-e698-428c-8420-fcb37f2010aa
options:
steps: 5
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: normal
limit_displayed_rows: false
hide_legend: false
legend_position: center
font_size: '12'
series_types: {}
point_style: none
series_labels:
events.total_ad_revenue: Ad Revenue
events.total_iap_revenue: IAP revenue
show_value_labels: true
label_density: 25
x_axis_scale: auto
y_axis_combined: true
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
up_color: "#2196F3"
down_color: "#03d7f4"
total_color: "#00BCD4"
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 2
col: 0
width: 8
height: 7
- title: IAP Purchase Sizes
name: IAP Purchase Sizes
model: gaming
explore: events
type: looker_scatter
fields: [events.iap_purchase_tier, events.total_iap_revenue, events.number_of_iap_purchases]
filters:
events.iap_purchase_tier: "-Below 0,-Undefined"
sorts: [events.iap_purchase_tier]
limit: 500
column_limit: 50
total: true
dynamic_fields: [{table_calculation: of_total, label: "% of total", expression: "${events.total_iap_revenue}/\
\ ${events.total_iap_revenue:total}", value_format: !!null '', value_format_name: percent_2,
_kind_hint: measure, _type_hint: number}]
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{axisId: events.total_iap_revenue,
id: events.total_iap_revenue, name: Total IAP Revenue}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, tickDensityCustom: 5,
type: linear}, {label: !!null '', orientation: right, series: [{axisId: of_total,
id: of_total, name: "% of total"}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, tickDensityCustom: 5, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: false
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
size_by_field: events.number_of_iap_purchases
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: false
hide_legend: true
legend_position: center
series_types: {}
point_style: circle
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
show_null_points: true
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 11
col: 0
width: 10
height: 7
- title: Transactions per Spender
name: Transactions per Spender
model: gaming
explore: events
type: single_value
fields: [events.transactions_per_spender]
filters: {}
limit: 500
column_limit: 50
hidden_fields: []
series_types: {}
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 11
col: 17
width: 7
height: 2
- title: Retaining Spenders
name: Retaining Spenders
model: gaming
explore: events
type: looker_column
fields: [events.retention_day, events.percent_spenders, events.total_iap_revenue]
filters:
events.retention_day: "<=70"
sorts: [events.retention_day]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: total_iap_revenue, label: Total IAP Revenue,
expression: 'running_total(${events.total_iap_revenue})', value_format: !!null '',
value_format_name: usd_0, _kind_hint: measure, _type_hint: number}]
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{axisId: events.percent_spenders,
id: events.percent_spenders, name: Percent Spenders}], showLabels: true,
showValues: true, unpinAxis: false, tickDensity: default, tickDensityCustom: 5,
type: linear}, {label: !!null '', orientation: right, series: [{axisId: total_iap_revenue,
id: total_iap_revenue, name: Total IAP Revenue}], showLabels: true, showValues: true,
unpinAxis: false, tickDensity: default, tickDensityCustom: 5, type: linear}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: false
hidden_series: [transactions_per_spender]
legend_position: center
series_types:
total_iap_revenue: line
point_style: none
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
trend_lines: []
ordering: none
show_null_labels: false
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
hidden_fields: [events.total_iap_revenue]
note_state: expanded
note_display: hover
note_text: The "whales" don't become whales for a long time
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 13
col: 10
width: 14
height: 5
- title: Revenue per Ad Shown
name: Revenue per Ad Shown
model: gaming
explore: events
type: looker_map
fields: [events.country, events.number_of_ads_shown, events.total_ad_revenue]
filters:
events.number_of_ads_shown: ">1000"
sorts: [revenue_per_ad_shown desc]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: revenue_per_ad_shown, label: Revenue per
Ad Shown, expression: "${events.total_ad_revenue}/${events.number_of_ads_shown}",
value_format: "$#.000", value_format_name: !!null '', _kind_hint: measure,
_type_hint: number}]
map_plot_mode: points
heatmap_gridlines: false
heatmap_gridlines_empty: false
heatmap_opacity: 0.5
show_region_field: true
draw_map_labels_above_data: true
map_tile_provider: light
map_position: custom
map_latitude: 52.70468296296837
map_longitude: 23.807373046875004
map_zoom: 1
map_scale_indicator: 'off'
map_pannable: true
map_zoomable: true
map_marker_type: circle
map_marker_icon_name: default
map_marker_radius_mode: proportional_value
map_marker_units: meters
map_marker_proportional_scale_type: linear
map_marker_color_mode: fixed
show_view_names: false
show_legend: true
map_value_colors: ["#8BC34A"]
quantize_map_value_colors: false
reverse_map_value_colors: true
hidden_fields: [events.total_ad_revenue, events.number_of_ads_shown]
note_state: expanded
note_display: hover
note_text: Where are networks paying the most for the ads we show in the app?
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 20
col: 0
width: 10
height: 9
- title: Revenue per Ad
name: Revenue per Ad
model: gaming
explore: events
type: single_value
fields: [events.number_of_ads_shown, events.total_ad_revenue]
filters:
events.number_of_ads_shown: ">1000"
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: revenue_per_ad_shown, label: Revenue per
Ad Shown, expression: "${events.total_ad_revenue}/${events.number_of_ads_shown}",
value_format: "$0.000", value_format_name: !!null '', _kind_hint: measure,
_type_hint: number}]
map_plot_mode: points
heatmap_gridlines: false
heatmap_gridlines_empty: false
heatmap_opacity: 0.5
show_region_field: true
draw_map_labels_above_data: true
map_tile_provider: light
map_position: custom
map_latitude: 52.70468296296837
map_longitude: 23.807373046875004
map_zoom: 1
map_scale_indicator: 'off'
map_pannable: true
map_zoomable: true
map_marker_type: circle
map_marker_icon_name: default
map_marker_radius_mode: proportional_value
map_marker_units: meters
map_marker_proportional_scale_type: linear
map_marker_color_mode: fixed
show_view_names: false
show_legend: true
map_value_colors: ["#8BC34A"]
quantize_map_value_colors: false
reverse_map_value_colors: true
hidden_fields: [events.total_ad_revenue, events.number_of_ads_shown]
series_types: {}
note_state: expanded
note_display: hover
note_text: Each ad played is an annoyance for a player - are we getting the most
value out of it?
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 20
col: 10
width: 8
height: 2
- title: Ads Per Session
name: Ads Per Session
model: gaming
explore: events
type: single_value
fields: [events.number_of_ads_shown, events.number_of_sesssions]
filters:
events.number_of_ads_shown: ">1000"
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: ads_per_session, label: Ads per Session,
expression: "${events.number_of_ads_shown}/${events.number_of_sesssions}",
value_format: !!null '', value_format_name: decimal_1, _kind_hint: measure,
_type_hint: number}]
map_plot_mode: points
heatmap_gridlines: false
heatmap_gridlines_empty: false
heatmap_opacity: 0.5
show_region_field: true
draw_map_labels_above_data: true
map_tile_provider: light
map_position: custom
map_latitude: 52.70468296296837
map_longitude: 23.807373046875004
map_zoom: 1
map_scale_indicator: 'off'
map_pannable: true
map_zoomable: true
map_marker_type: circle
map_marker_icon_name: default
map_marker_radius_mode: proportional_value
map_marker_units: meters
map_marker_proportional_scale_type: linear
map_marker_color_mode: fixed
show_view_names: false
show_legend: true
map_value_colors: ["#8BC34A"]
quantize_map_value_colors: false
reverse_map_value_colors: true
hidden_fields: [events.number_of_ads_shown, events.number_of_sesssions]
series_types: {}
note_state: expanded
note_display: hover
note_text: Are we inundating players with ads?
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 20
col: 18
width: 6
height: 2
- name: "<span class='fa fa-money'> CPI Recovery </span>"
type: text
title_text: "<span class='fa fa-money'> CPI Recovery </span>"
subtitle_text: How quickly are we recovering our marketing costs?
body_text: ''
row: 29
col: 0
width: 24
height: 2
- title: Revenue by Network
name: Revenue by Network
model: gaming
explore: events
type: table
fields: [events.number_of_ads_shown, events.total_ad_revenue, events.ad_network]
filters:
events.number_of_ads_shown: ">1000"
sorts: [revenue_per_ad_shown desc]
limit: 500
column_limit: 50
dynamic_fields: [{table_calculation: revenue_per_ad_shown, label: Revenue per
Ad Shown, expression: "${events.total_ad_revenue}/${events.number_of_ads_shown}",
value_format: "$0.000", value_format_name: !!null '', _kind_hint: measure,
_type_hint: number}]
show_view_names: false
show_row_numbers: true
truncate_column_names: false
hide_totals: false
hide_row_totals: false
table_theme: gray
limit_displayed_rows: false
enable_conditional_formatting: true
conditional_formatting: [{type: along a scale..., value: !!null '', background_color: "#3EB0D5",
font_color: !!null '', color_application: {collection_id: b43731d5-dc87-4a8e-b807-635bef3948e7,
palette_id: 85de97da-2ded-4dec-9dbd-e6a7d36d5825}, bold: false, italic: false,
strikethrough: false, fields: [revenue_per_ad_shown]}, {type: along a scale...,
value: !!null '', background_color: "#3EB0D5", font_color: !!null '', color_application: {
collection_id: b43731d5-dc87-4a8e-b807-635bef3948e7, palette_id: 1e4d66b9-f066-4c33-b0b7-cc10b4810688,
options: {steps: 5, constraints: {mid: {type: middle}}, reverse: true, mirror: false}},
bold: false, italic: false, strikethrough: false, fields: [events.number_of_ads_shown]}]
conditional_formatting_include_totals: false
conditional_formatting_include_nulls: false
map_plot_mode: points
heatmap_gridlines: false
heatmap_gridlines_empty: false
heatmap_opacity: 0.5
show_region_field: true
draw_map_labels_above_data: true
map_tile_provider: light
map_position: custom
map_latitude: 52.70468296296837
map_longitude: 23.807373046875004
map_zoom: 1
map_scale_indicator: 'off'
map_pannable: true
map_zoomable: true
map_marker_type: circle
map_marker_icon_name: default
map_marker_radius_mode: proportional_value
map_marker_units: meters
map_marker_proportional_scale_type: linear
map_marker_color_mode: fixed
show_legend: true
map_value_colors: ["#8BC34A"]
quantize_map_value_colors: false
reverse_map_value_colors: true
hidden_fields:
series_types: {}
note_state: expanded
note_display: hover
note_text: Each ad played is an annoyance for a player - are we getting the most
value out of it?
listen:
Date Range: events.event_date
Drill Down: events.drill_by
Platform: events.device_platform
Install Source: events.install_source
Country: events.country
Game: events.game_name
row: 22
col: 10
width: 14
height: 7
filters:
- name: Date Range
title: Date Range
type: field_filter
default_value: 30 days ago for 30 days
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.event_date
- name: Drill Down
title: Drill Down
type: field_filter
default_value: device^_platform
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.drill_by
- name: Platform
title: Platform
type: field_filter
default_value: ''
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.device_platform
- name: Install Source
title: Install Source
type: field_filter
default_value: ''
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.install_source
- name: Country
title: Country
type: field_filter
default_value: ''
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.country
- name: Game
title: Game
type: field_filter
default_value: Lookerwood Farm
allow_multiple_values: true
required: false
model: gaming
explore: events
listens_to_filters: []
field: events.game_name
Raw
level_balancing.view
# If necessary, uncomment the line below to include explore_source.
# include: "gaming.model.lkml"
view: level_balancing {
derived_table: {
explore_source: events {
column: approximate_usage_in_minutes {}
column: player_level {}
column: game_name {}
column: user_id {}
filters: {
field: events.event_date
value: "60 days"
}
}
}
dimension: approximate_usage_in_minutes {
type: number
}
dimension: player_level {
type: number
}
dimension: user_id {}
dimension: game_name {}
dimension: prim_key {
primary_key: yes
type: string
hidden: yes
sql: CONCAT(${player_level},${user_id},${game_name}) ;;
}
measure: number_of_users {
type: count_distinct
sql: ${user_id} ;;
}
measure: users_spent_3_min_or_more {
description: "filtered user count: spent 3 min or more"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: approximate_usage_in_minutes
value: ">3"
}
}
dimension: usage_tiered {
type: tier
tiers: [0,1,2,3,4,5,6,7,8,9,10]
sql: ${approximate_usage_in_minutes} ;;
style: integer
}
}
Raw
session_facts.view
################################################################
# Session Facts View
################################################################
view: session_facts {
derived_table: {
sql: WITH session_facts AS
(SELECT
unique_session_id
, next_session_start
, event
, user_id
, event_name
, COALESCE(SUM((IFNULL(iap_revenue,0) + IFNULL(ad_revenue,0)) ), 0) as session_revenue
, SUM(iap_revenue) as session_iap_revenue
, SUM(ad_revenue) as session_ad_revenue
, COUNT(CASE WHEN (event_name = 'Ad_Watched') THEN 1 ELSE NULL END) AS number_of_ads_shown
, COUNT(CASE WHEN (event_name = 'Level_Up') THEN 1 ELSE NULL END) AS number_level_ups
, MAX(player_level) as highest_level_reached
, FIRST_VALUE (event) OVER (PARTITION BY unique_session_id ORDER BY event ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS session_start
, LAST_VALUE (event) OVER (PARTITION BY unique_session_id ORDER BY event ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS session_end
, FIRST_VALUE (event_name) OVER (PARTITION BY unique_session_id ORDER BY event ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS session_first_event
, LAST_VALUE (event_name) OVER (PARTITION BY unique_session_id ORDER BY event ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS session_last_event
FROM
${events.SQL_TABLE_NAME} AS events_sessionized
GROUP BY 1,2,3,4,5
ORDER BY unique_session_id asc
)
SELECT
session_facts.unique_session_id
, session_facts.next_session_start
, session_facts.user_id
, session_facts.session_start
, session_facts.session_end
, session_first_event
, session_last_event
, events_sessionized.device_platform
, events_sessionized.game_name
, MAX(events_sessionized.game_version) as game_version
, MAX(events_sessionized.country) as country
, MAX(session_facts.highest_level_reached) as highest_level_reached
, MAX(session_facts.session_revenue) as session_revenue
, MAX(session_facts.session_iap_revenue) as session_iap_revenue
, MAX(session_facts.session_ad_revenue) as session_ad_revenue
, SUM(session_facts.number_of_ads_shown) as number_of_ads_shown
, SUM(session_facts.number_level_ups) as number_of_level_ups
, ROW_NUMBER () OVER (PARTITION BY session_facts.user_id ORDER BY MIN(session_start)) AS session_sequence_for_user
, ROW_NUMBER () OVER (PARTITION BY session_facts.user_id ORDER BY MIN(session_start) desc) AS inverse_session_sequence_for_user
, count(1) as events_in_session
FROM session_facts
INNER JOIN
${events.SQL_TABLE_NAME} AS events_sessionized
ON events_sessionized.event = session_facts.session_start
AND events_sessionized.unique_session_id = session_facts.unique_session_id
GROUP BY 1,2,3,4,5,6,7,8,9
;;
datagroup_trigger: events_raw
partition_keys: ["session_start"]
cluster_keys: ["game_name"]
}
dimension: unique_session_id {
primary_key: yes
type: string
value_format_name: id
sql: ${TABLE}.unique_session_id ;;
link: {
label: "See session detail"
url: "/dashboards/Xb2IL2W022TXYLgHiOkAYV?Session%20ID={{value}}"
}
}
dimension: user_id {}
dimension: game_version {}
dimension: device_platform {}
dimension: country {}
dimension: game_name {}
dimension_group: session_start_at {
type: time
convert_tz: no
timeframes: [raw,time, date, week, month]
sql: ${TABLE}.session_start ;;
}
dimension_group: session_end_at {
type: time
convert_tz: no
timeframes: [raw,time, date, week, month]
sql: ${TABLE}.session_end ;;
}
dimension_group: next_session_start_at {
type: time
convert_tz: no
timeframes: [raw,time, date, week, month]
sql: ${TABLE}.next_session_start ;;
}
dimension_group: until_next_session {
type: duration
intervals: [day,week,month]
sql_start: ${session_start_at_raw} ;;
sql_end: CASE WHEN ${next_session_start_at_raw} = TIMESTAMP('6000-01-01 00:00:00') then NULL else ${next_session_start_at_raw} END ;;
}
dimension: retention_day {
group_label: "Retention"
description: "Days since first seen (from event date)"
type: number
sql: DATE_DIFF(${session_start_at_date}, ${user_facts.player_first_seen_date}, DAY);;
}
dimension: session_sequence_for_user {
type: number
sql: ${TABLE}.session_sequence_for_user ;;
}
dimension: inverse_session_sequence_for_user {
hidden: yes
type: number
sql: ${TABLE}.inverse_session_sequence_for_user ;;
}
dimension: is_first_session {
description: "Is this the first session for this user?"
type: yesno
sql: ${session_sequence_for_user} = 1 ;;
}
dimension: is_last_session {
description: "Is this the last session for this user?"
type: yesno
sql: ${session_sequence_for_user} = ${inverse_session_sequence_for_user} ;;
}
dimension: number_of_events_in_session {
type: number
sql: ${TABLE}.events_in_session ;;
}
dimension: number_of_level_ups {
description: "number of times the user has increased level within this session"
type: number
sql: ${TABLE}.number_of_level_ups ;;
}
dimension: highest_level_reached {
description: "highest level within this session"
type: number
sql: ${TABLE}.highest_level_reached ;;
}
dimension: session_first_event {
type: string
sql: ${TABLE}.session_first_event ;;
}
dimension: session_last_event {
type: string
sql: ${TABLE}.session_last_event ;;
}
dimension_group: session_length {
type: duration
intervals: [second,minute,hour]
sql_start: ${session_start_at_raw} ;;
sql_end: ${session_end_at_raw} ;;
}
dimension: session_length_tier {
alias: [session_length_minutes_tier]
type: string
sql:
case
when ${minutes_session_length} between 0 and 1 then '1. Bounce (<2 min)'
when ${minutes_session_length} between 2 and 5 then '2. Quick Sesh (2-5 min)'
when ${minutes_session_length} between 6 and 15 then '3. Average Sesh (6-15 min)'
when ${minutes_session_length} between 16 and 30 then '4. Deep Sesh (16-30 min)'
when ${minutes_session_length} > 30 then '5. Binge (>30 min)'
else 'other'
end
;;
}
dimension: session_revenue {
type: number
description: "IAP and Ad Revenue in Session"
sql: ${TABLE}.session_revenue ;;
value_format_name: usd
}
dimension: session_iap_revenue {
type: number
description: "IAP Revenue in Session"
sql: ${TABLE}.session_iap_revenue ;;
value_format_name: usd
}
dimension: session_ad_revenue {
type: number
description: "Ad Revenue in Session"
sql: ${TABLE}.session_ad_revenue ;;
value_format_name: usd
}
dimension: number_of_ads_shown {
type: number
sql: ${TABLE}.number_of_ads_shown ;;
}
measure: average_session_length_minutes {
type: average
sql: ${minutes_session_length} ;;
value_format_name: decimal_2
}
measure: total_ads_shown {
group_label: "Monetization"
type: sum
sql: ${number_of_ads_shown} ;;
}
measure: ads_shown_per_session {
group_label: "Monetization"
type: number
sql: ${total_ads_shown} / ${number_of_sessions};;
value_format_name: decimal_2
}
measure: number_of_sessions {
type: count
drill_fields: [detail*]
}
measure: total_level_ups {
group_label: "Level Ups"
type: sum
sql: ${number_of_level_ups} ;;
}
measure: average_level_ups {
group_label: "Level Ups"
type: average
sql: ${number_of_level_ups} ;;
value_format_name: decimal_2
}
measure: total_session_length {
hidden: yes
type: sum
sql: ${minutes_session_length} ;;
}
measure: total_revenue {
label: "Total Revenue"
group_label: "Monetization"
description: "Total Revenue from Ads + In-App Purchases"
type: sum
sql: ${session_revenue} ;;
value_format_name: large_usd
}
measure: total_iap_revenue {
label: "Total IAP Revenue"
group_label: "Monetization"
description: "Total Revenue from In-App Purchases"
type: sum
sql: ${session_iap_revenue} ;;
value_format_name: large_usd
}
measure: total_ad_revenue {
label: "Total Ad Revenue"
group_label: "Monetization"
description: "Total Revenue from Ad"
type: sum
sql: ${session_ad_revenue} ;;
value_format_name: large_usd
}
measure: average_revenue_per_user {
group_label: "Monetization"
label: "ARPU"
description: "(Average revenue per user) = Total Revenue (IAP + Ad) / Total Number of Users"
type: number
sql: 1.0 * ${total_revenue} / NULLIF(count(distinct ${user_id}),0) ;;
value_format_name: large_usd
}
set: detail {
fields: [
unique_session_id,
session_start_at_time,
session_end_at_time,
session_sequence_for_user,
inverse_session_sequence_for_user,
number_of_events_in_session,
session_first_event,
session_last_event,
session_revenue
]
}
}
Raw
session_funnel.dashboard
- dashboard: session_funnel
title: Session Funnel
layout: newspaper
elements:
- title: Session Funnel
name: Session Funnel
model: gaming
explore: funnel_explorer
type: looker_column
fields: [funnel_explorer.count_sessions_event1, funnel_explorer.count_sessions_event12,
funnel_explorer.count_sessions_event123, funnel_explorer.count_sessions_event1234,
session_facts.game_version]
filters:
funnel_explorer.event_time: 14 days
sorts: [session_facts.game_version]
limit: 500
query_timezone: America/Los_Angeles
x_axis_gridlines: false
y_axis_gridlines: true
show_view_names: false
y_axes: [{label: '', orientation: left, series: [{axisId: funnel_explorer.count_sessions_event1,
id: funnel_explorer.count_sessions_event1, name: '"Match_Started"'}, {
axisId: funnel_explorer.count_sessions_event12, id: funnel_explorer.count_sessions_event12,
name: '"Match_Ended"'}, {axisId: funnel_explorer.count_sessions_event123,
id: funnel_explorer.count_sessions_event123, name: '"Skin_Unlocked"'},
{axisId: funnel_explorer.count_sessions_event1234, id: funnel_explorer.count_sessions_event1234,
name: '"in_app_purchase"'}], showLabels: true, showValues: true, unpinAxis: false,
tickDensity: default, tickDensityCustom: 5, type: log}]
show_y_axis_labels: true
show_y_axis_ticks: true
y_axis_tick_density: default
y_axis_tick_density_custom: 5
show_x_axis_label: true
show_x_axis_ticks: true
y_axis_scale_mode: linear
x_axis_reversed: false
y_axis_reversed: false
plot_size_by_field: false
trellis: ''
stacking: ''
limit_displayed_rows: false
legend_position: center
point_style: none
show_value_labels: false
label_density: 25
x_axis_scale: auto
y_axis_combined: true
ordering: none
show_null_labels: false
show_dropoff: true
show_totals_labels: false
show_silhouette: false
totals_color: "#808080"
listen:
Date Range: funnel_explorer.session_start_date
Game Name: funnel_explorer.game_name
Game Version: session_facts.game_version
Event 1: funnel_explorer.event_1
Event 2: funnel_explorer.event_2
Event 3: funnel_explorer.event_3
Event 4: funnel_explorer.event_4
row: 0
col: 8
width: 16
height: 12
- name: "<span class='fa fa-filter'> Session Funnel </span>"
type: text
title_text: "<span class='fa fa-filter'> Session Funnel </span>"
body_text: "This flexible funnel tracks movement of players through sequences\
\ of events (defined in the filters). Identifying where the drops are and addressing\
\ retention issues. \n\n**Note, this funnel looks at events that occurred within\
\ a session (and in order), it can be modified in any way needed**"
row: 0
col: 0
width: 8
height: 12
filters:
- name: Date Range
title: Date Range
type: field_filter
default_value: 14 days
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: []
field: funnel_explorer.event_time
- name: Game Name
title: Game Name
type: field_filter
default_value: Lookup Battle Royale
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: []
field: funnel_explorer.game_name
- name: Game Version
title: Game Version
type: field_filter
default_value: 1.4.4,1.4.6,1.5.0
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: []
field: session_facts.game_version
- name: Event 1
title: Event 1
type: field_filter
default_value: '"Match_Started"'
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: [Game Name]
field: funnel_explorer.event_1
- name: Event 2
title: Event 2
type: field_filter
default_value: '"Match_Ended"'
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: [Game Name]
field: funnel_explorer.event_2
- name: Event 3
title: Event 3
type: field_filter
default_value: '"Skin_Unlocked"'
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: [Game Name]
field: funnel_explorer.event_3
- name: Event 4
title: Event 4
type: field_filter
default_value: '"in_app_purchase"'
allow_multiple_values: true
required: false
model: gaming
explore: funnel_explorer
listens_to_filters: [Game Name]
field: funnel_explorer.event_4
Raw
top_countries.view
# If necessary, uncomment the line below to include explore_source.
# include: "gaming.model.lkml"
view: top_countries {
view_label: "Events"
derived_table: {
explore_source: events {
column: country {}
column: total_revenue {}
bind_all_filters: yes
derived_column: country_rank {
sql: RANK() OVER (ORDER BY total_revenue desc) ;;
}
}
}
dimension: country {hidden:yes}
dimension: total_revenue { type: number hidden:yes }
dimension: country_rank {
group_label: "Location"
description: "(Based on Revenue, respects all applied filters)"
type:number}
dimension: is_top_10_country {
group_label: "Location"
description: "(Based on Revenue, respects all applied filters)"
type: yesno
sql: ${country_rank} <= 10 ;;
}
}
Raw
user_event_facts.view
#### User Fact Table ####
view: lifetime_user_facts {
drill_fields: [user_id]
derived_table: {
datagroup_trigger: events_raw
sql:
SELECT
user_id as user_id
-- First and Latest Seen
,MIN(event) AS first_seen
,MAX(event) AS latest_seen
-- Match_Started
,MIN(CASE WHEN event_name = 'Match_Started' THEN event ELSE NULL END) AS first_matchstarted
,MAX(CASE WHEN event_name = 'Match_Started' THEN event ELSE NULL END) AS latest_matchstarted
,COUNT(CASE WHEN event_name = 'Match_Started' THEN 1 ELSE NULL END) AS lifetime_matchstarted
-- Match_Ended
,MIN(CASE WHEN event_name = 'Match_Ended' THEN event ELSE NULL END) AS first_matchended
,MAX(CASE WHEN event_name = 'Match_Ended' THEN event ELSE NULL END) AS latest_matchended
,COUNT(CASE WHEN event_name = 'Match_Ended' THEN 1 ELSE NULL END) AS lifetime_matchended
-- Ad_Watched
,MIN(CASE WHEN event_name = 'Ad_Watched' THEN event ELSE NULL END) AS first_adwatched
,MAX(CASE WHEN event_name = 'Ad_Watched' THEN event ELSE NULL END) AS latest_adwatched
,COUNT(CASE WHEN event_name = 'Ad_Watched' THEN 1 ELSE NULL END) AS lifetime_adwatched
-- Level_Up
,MIN(CASE WHEN event_name = 'Level_Up' THEN event ELSE NULL END) AS first_levelup
,MAX(CASE WHEN event_name = 'Level_Up' THEN event ELSE NULL END) AS latest_levelup
,COUNT(CASE WHEN event_name = 'Level_Up' THEN 1 ELSE NULL END) AS lifetime_levelup
-- Session_Started
,MIN(CASE WHEN event_name = 'Session_Started' THEN event ELSE NULL END) AS first_sessionstarted
,MAX(CASE WHEN event_name = 'Session_Started' THEN event ELSE NULL END) AS latest_sessionstarted
,COUNT(CASE WHEN event_name = 'Session_Started' THEN 1 ELSE NULL END) AS lifetime_sessionstarted
-- FTUE_Stage_Complete
,MIN(CASE WHEN event_name = 'FTUE_Stage_Complete' THEN event ELSE NULL END) AS first_ftuestagecomplete
,MAX(CASE WHEN event_name = 'FTUE_Stage_Complete' THEN event ELSE NULL END) AS latest_ftuestagecomplete
,COUNT(CASE WHEN event_name = 'FTUE_Stage_Complete' THEN 1 ELSE NULL END) AS lifetime_ftuestagecomplete
-- Gem_Spend
,MIN(CASE WHEN event_name = 'Gem_Spend' THEN event ELSE NULL END) AS first_gemspend
,MAX(CASE WHEN event_name = 'Gem_Spend' THEN event ELSE NULL END) AS latest_gemspend
,COUNT(CASE WHEN event_name = 'Gem_Spend' THEN 1 ELSE NULL END) AS lifetime_gemspend
-- FTUE_Stage_Started
,MIN(CASE WHEN event_name = 'FTUE_Stage_Started' THEN event ELSE NULL END) AS first_ftuestagestarted
,MAX(CASE WHEN event_name = 'FTUE_Stage_Started' THEN event ELSE NULL END) AS latest_ftuestagestarted
,COUNT(CASE WHEN event_name = 'FTUE_Stage_Started' THEN 1 ELSE NULL END) AS lifetime_ftuestagestarted
-- IAP_Started
,MIN(CASE WHEN event_name = 'IAP_Started' THEN event ELSE NULL END) AS first_iapstarted
,MAX(CASE WHEN event_name = 'IAP_Started' THEN event ELSE NULL END) AS latest_iapstarted
,COUNT(CASE WHEN event_name = 'IAP_Started' THEN 1 ELSE NULL END) AS lifetime_iapstarted
-- Skin_Unlocked
,MIN(CASE WHEN event_name = 'Skin_Unlocked' THEN event ELSE NULL END) AS first_skinunlocked
,MAX(CASE WHEN event_name = 'Skin_Unlocked' THEN event ELSE NULL END) AS latest_skinunlocked
,COUNT(CASE WHEN event_name = 'Skin_Unlocked' THEN 1 ELSE NULL END) AS lifetime_skinunlocked
-- Harvest_Done
,MIN(CASE WHEN event_name = 'Harvest_Done' THEN event ELSE NULL END) AS first_harvestdone
,MAX(CASE WHEN event_name = 'Harvest_Done' THEN event ELSE NULL END) AS latest_harvestdone
,COUNT(CASE WHEN event_name = 'Harvest_Done' THEN 1 ELSE NULL END) AS lifetime_harvestdone
-- in_app_purchase
,MIN(CASE WHEN event_name = 'in_app_purchase' THEN event ELSE NULL END) AS first_inapppurchase
,MAX(CASE WHEN event_name = 'in_app_purchase' THEN event ELSE NULL END) AS latest_inapppurchase
,COUNT(CASE WHEN event_name = 'in_app_purchase' THEN 1 ELSE NULL END) AS lifetime_inapppurchase
FROM `gaming_demo_dev.events_sessionized`
GROUP BY user_id;;
}
#### Date Comparitor ####
dimension_group: comparitor {
view_label: "Date Comparisons"
type: duration
sql_start:
{% if first_date._parameter_value == 'CURRENT_TIMESTAMP' %}
CURRENT_TIMESTAMP
{% else %}
${TABLE}.{% parameter first_date %}
{% endif %};;
sql_end:
{% if second_date._parameter_value == 'CURRENT_TIMESTAMP' %}
CURRENT_TIMESTAMP
{% else %}
${TABLE}.{% parameter second_date %}
{% endif %};;
}
parameter: first_date {
type: unquoted
view_label: "Date Comparisons"
allowed_value: { label: "Today" value: "CURRENT_TIMESTAMP" }
allowed_value: { label: "First Match_Started" value: "first_matchstarted"}
allowed_value: { label: "First Match_Ended" value: "first_matchended"}
allowed_value: { label: "First Ad_Watched" value: "first_adwatched"}
allowed_value: { label: "First Level_Up" value: "first_levelup"}
allowed_value: { label: "First Session_Started" value: "first_sessionstarted"}
allowed_value: { label: "First FTUE_Stage_Complete" value: "first_ftuestagecomplete"}
allowed_value: { label: "First Gem_Spend" value: "first_gemspend"}
allowed_value: { label: "First FTUE_Stage_Started" value: "first_ftuestagestarted"}
allowed_value: { label: "First IAP_Started" value: "first_iapstarted"}
allowed_value: { label: "First Skin_Unlocked" value: "first_skinunlocked"}
allowed_value: { label: "First Harvest_Done" value: "first_harvestdone"}
allowed_value: { label: "First in_app_purchase" value: "first_inapppurchase"}
allowed_value: { label: "Latest Match_Started" value: "latest_matchstarted"}
allowed_value: { label: "Latest Match_Ended" value: "latest_matchended"}
allowed_value: { label: "Latest Ad_Watched" value: "latest_adwatched"}
allowed_value: { label: "Latest Level_Up" value: "latest_levelup"}
allowed_value: { label: "Latest Session_Started" value: "latest_sessionstarted"}
allowed_value: { label: "Latest FTUE_Stage_Complete" value: "latest_ftuestagecomplete"}
allowed_value: { label: "Latest Gem_Spend" value: "latest_gemspend"}
allowed_value: { label: "Latest FTUE_Stage_Started" value: "latest_ftuestagestarted"}
allowed_value: { label: "Latest IAP_Started" value: "latest_iapstarted"}
allowed_value: { label: "Latest Skin_Unlocked" value: "latest_skinunlocked"}
allowed_value: { label: "Latest Harvest_Done" value: "latest_harvestdone"}
allowed_value: { label: "Latest in_app_purchase" value: "latest_inapppurchase"}
}
parameter: second_date {
type: unquoted
view_label: "Date Comparisons"
allowed_value: { label: "Today" value: "CURRENT_TIMESTAMP" }
allowed_value: { label: "First Match_Started" value: "first_matchstarted"}
allowed_value: { label: "First Match_Ended" value: "first_matchended"}
allowed_value: { label: "First Ad_Watched" value: "first_adwatched"}
allowed_value: { label: "First Level_Up" value: "first_levelup"}
allowed_value: { label: "First Session_Started" value: "first_sessionstarted"}
allowed_value: { label: "First FTUE_Stage_Complete" value: "first_ftuestagecomplete"}
allowed_value: { label: "First Gem_Spend" value: "first_gemspend"}
allowed_value: { label: "First FTUE_Stage_Started" value: "first_ftuestagestarted"}
allowed_value: { label: "First IAP_Started" value: "first_iapstarted"}
allowed_value: { label: "First Skin_Unlocked" value: "first_skinunlocked"}
allowed_value: { label: "First Harvest_Done" value: "first_harvestdone"}
allowed_value: { label: "First in_app_purchase" value: "first_inapppurchase"}
allowed_value: { label: "Latest Match_Started" value: "latest_matchstarted"}
allowed_value: { label: "Latest Match_Ended" value: "latest_matchended"}
allowed_value: { label: "Latest Ad_Watched" value: "latest_adwatched"}
allowed_value: { label: "Latest Level_Up" value: "latest_levelup"}
allowed_value: { label: "Latest Session_Started" value: "latest_sessionstarted"}
allowed_value: { label: "Latest FTUE_Stage_Complete" value: "latest_ftuestagecomplete"}
allowed_value: { label: "Latest Gem_Spend" value: "latest_gemspend"}
allowed_value: { label: "Latest FTUE_Stage_Started" value: "latest_ftuestagestarted"}
allowed_value: { label: "Latest IAP_Started" value: "latest_iapstarted"}
allowed_value: { label: "Latest Skin_Unlocked" value: "latest_skinunlocked"}
allowed_value: { label: "Latest Harvest_Done" value: "latest_harvestdone"}
allowed_value: { label: "Latest in_app_purchase" value: "latest_inapppurchase"}
}
#### LookML Definitions ####
dimension:user_id {
hidden: yes
primary_key: yes
sql: ${TABLE}.user_id ;;
}
dimension_group: first_seen {
type: time
}
dimension_group: last_seen {
type: time
}
## Match_Started
dimension_group: first_matchstarted {
label: "First Match_Started"
group_label: "Match_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_matchstarted;;
}
dimension_group: latest_matchstarted {
label: "Latest Match_Started"
group_label: "Match_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_matchstarted;;
}
dimension: lifetime_matchstarted {
label: "Lifetime Match_Started"
group_label: "Match_Started"
type: number
sql: ${TABLE}.lifetime_matchstarted;;
value_format_name: decimal_0
}
dimension: used_matchstarted {
label: "Ever Used Match_Started?"
group_label: "Match_Started"
type: yesno
sql: ${TABLE}.lifetime_matchstarted > 0;;
}
dimension: tier_matchstarted {
label: "Tier Match_Started"
group_label: "Match_Started"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_matchstarted;;
style: integer
}
measure: number_of_users_matchstarted {
label: "Number of Users Match_Started"
group_label: "Match_Started"
type: count
filters: { field: used_matchstarted value: "yes" }
}
measure: percent_of_users_matchstarted {
label: "Percent of Users that ever Match_Started"
group_label: "Match_Started"
type: number
sql: 1.0 * ${number_of_users_matchstarted} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Match_Ended
dimension_group: first_matchended {
label: "First Match_Ended"
group_label: "Match_Ended"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_matchended;;
}
dimension_group: latest_matchended {
label: "Latest Match_Ended"
group_label: "Match_Ended"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_matchended;;
}
dimension: lifetime_matchended {
label: "Lifetime Match_Ended"
group_label: "Match_Ended"
type: number
sql: ${TABLE}.lifetime_matchended;;
value_format_name: decimal_0
}
dimension: used_matchended {
label: "Ever Used Match_Ended?"
group_label: "Match_Ended"
type: yesno
sql: ${TABLE}.lifetime_matchended > 0;;
}
dimension: tier_matchended {
label: "Tier Match_Ended"
group_label: "Match_Ended"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_matchended;;
style: integer
}
measure: number_of_users_matchended {
label: "Number of Users Match_Ended"
group_label: "Match_Ended"
type: count
filters: { field: used_matchended value: "yes" }
}
measure: percent_of_users_matchended {
label: "Percent of Users that ever Match_Ended"
group_label: "Match_Ended"
type: number
sql: 1.0 * ${number_of_users_matchended} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Ad_Watched
dimension_group: first_adwatched {
label: "First Ad_Watched"
group_label: "Ad_Watched"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_adwatched;;
}
dimension_group: latest_adwatched {
label: "Latest Ad_Watched"
group_label: "Ad_Watched"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_adwatched;;
}
dimension: lifetime_adwatched {
label: "Lifetime Ad_Watched"
group_label: "Ad_Watched"
type: number
sql: ${TABLE}.lifetime_adwatched;;
value_format_name: decimal_0
}
dimension: used_adwatched {
label: "Ever Used Ad_Watched?"
group_label: "Ad_Watched"
type: yesno
sql: ${TABLE}.lifetime_adwatched > 0;;
}
dimension: tier_adwatched {
label: "Tier Ad_Watched"
group_label: "Ad_Watched"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_adwatched;;
style: integer
}
measure: number_of_users_adwatched {
label: "Number of Users Ad_Watched"
group_label: "Ad_Watched"
type: count
filters: { field: used_adwatched value: "yes" }
}
measure: percent_of_users_adwatched {
label: "Percent of Users that ever Ad_Watched"
group_label: "Ad_Watched"
type: number
sql: 1.0 * ${number_of_users_adwatched} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Level_Up
dimension_group: first_levelup {
label: "First Level_Up"
group_label: "Level_Up"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_levelup;;
}
dimension_group: latest_levelup {
label: "Latest Level_Up"
group_label: "Level_Up"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_levelup;;
}
dimension: lifetime_levelup {
label: "Lifetime Level_Up"
group_label: "Level_Up"
type: number
sql: ${TABLE}.lifetime_levelup;;
value_format_name: decimal_0
}
dimension: used_levelup {
label: "Ever Used Level_Up?"
group_label: "Level_Up"
type: yesno
sql: ${TABLE}.lifetime_levelup > 0;;
}
dimension: tier_levelup {
label: "Tier Level_Up"
group_label: "Level_Up"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_levelup;;
style: integer
}
measure: number_of_users_levelup {
label: "Number of Users Level_Up"
group_label: "Level_Up"
type: count
filters: { field: used_levelup value: "yes" }
}
measure: percent_of_users_levelup {
label: "Percent of Users that ever Level_Up"
group_label: "Level_Up"
type: number
sql: 1.0 * ${number_of_users_levelup} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Session_Started
dimension_group: first_sessionstarted {
label: "First Session_Started"
group_label: "Session_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_sessionstarted;;
}
dimension_group: latest_sessionstarted {
label: "Latest Session_Started"
group_label: "Session_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_sessionstarted;;
}
dimension: lifetime_sessionstarted {
label: "Lifetime Session_Started"
group_label: "Session_Started"
type: number
sql: ${TABLE}.lifetime_sessionstarted;;
value_format_name: decimal_0
}
dimension: used_sessionstarted {
label: "Ever Used Session_Started?"
group_label: "Session_Started"
type: yesno
sql: ${TABLE}.lifetime_sessionstarted > 0;;
}
dimension: tier_sessionstarted {
label: "Tier Session_Started"
group_label: "Session_Started"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_sessionstarted;;
style: integer
}
measure: number_of_users_sessionstarted {
label: "Number of Users Session_Started"
group_label: "Session_Started"
type: count
filters: { field: used_sessionstarted value: "yes" }
}
measure: percent_of_users_sessionstarted {
label: "Percent of Users that ever Session_Started"
group_label: "Session_Started"
type: number
sql: 1.0 * ${number_of_users_sessionstarted} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## FTUE_Stage_Complete
dimension_group: first_ftuestagecomplete {
label: "First FTUE_Stage_Complete"
group_label: "FTUE_Stage_Complete"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_ftuestagecomplete;;
}
dimension_group: latest_ftuestagecomplete {
label: "Latest FTUE_Stage_Complete"
group_label: "FTUE_Stage_Complete"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_ftuestagecomplete;;
}
dimension: lifetime_ftuestagecomplete {
label: "Lifetime FTUE_Stage_Complete"
group_label: "FTUE_Stage_Complete"
type: number
sql: ${TABLE}.lifetime_ftuestagecomplete;;
value_format_name: decimal_0
}
dimension: used_ftuestagecomplete {
label: "Ever Used FTUE_Stage_Complete?"
group_label: "FTUE_Stage_Complete"
type: yesno
sql: ${TABLE}.lifetime_ftuestagecomplete > 0;;
}
dimension: tier_ftuestagecomplete {
label: "Tier FTUE_Stage_Complete"
group_label: "FTUE_Stage_Complete"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_ftuestagecomplete;;
style: integer
}
measure: number_of_users_ftuestagecomplete {
label: "Number of Users FTUE_Stage_Complete"
group_label: "FTUE_Stage_Complete"
type: count
filters: { field: used_ftuestagecomplete value: "yes" }
}
measure: percent_of_users_ftuestagecomplete {
label: "Percent of Users that ever FTUE_Stage_Complete"
group_label: "FTUE_Stage_Complete"
type: number
sql: 1.0 * ${number_of_users_ftuestagecomplete} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Gem_Spend
dimension_group: first_gemspend {
label: "First Gem_Spend"
group_label: "Gem_Spend"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_gemspend;;
}
dimension_group: latest_gemspend {
label: "Latest Gem_Spend"
group_label: "Gem_Spend"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_gemspend;;
}
dimension: lifetime_gemspend {
label: "Lifetime Gem_Spend"
group_label: "Gem_Spend"
type: number
sql: ${TABLE}.lifetime_gemspend;;
value_format_name: decimal_0
}
dimension: used_gemspend {
label: "Ever Used Gem_Spend?"
group_label: "Gem_Spend"
type: yesno
sql: ${TABLE}.lifetime_gemspend > 0;;
}
dimension: tier_gemspend {
label: "Tier Gem_Spend"
group_label: "Gem_Spend"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_gemspend;;
style: integer
}
measure: number_of_users_gemspend {
label: "Number of Users Gem_Spend"
group_label: "Gem_Spend"
type: count
filters: { field: used_gemspend value: "yes" }
}
measure: percent_of_users_gemspend {
label: "Percent of Users that ever Gem_Spend"
group_label: "Gem_Spend"
type: number
sql: 1.0 * ${number_of_users_gemspend} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## FTUE_Stage_Started
dimension_group: first_ftuestagestarted {
label: "First FTUE_Stage_Started"
group_label: "FTUE_Stage_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_ftuestagestarted;;
}
dimension_group: latest_ftuestagestarted {
label: "Latest FTUE_Stage_Started"
group_label: "FTUE_Stage_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_ftuestagestarted;;
}
dimension: lifetime_ftuestagestarted {
label: "Lifetime FTUE_Stage_Started"
group_label: "FTUE_Stage_Started"
type: number
sql: ${TABLE}.lifetime_ftuestagestarted;;
value_format_name: decimal_0
}
dimension: used_ftuestagestarted {
label: "Ever Used FTUE_Stage_Started?"
group_label: "FTUE_Stage_Started"
type: yesno
sql: ${TABLE}.lifetime_ftuestagestarted > 0;;
}
dimension: tier_ftuestagestarted {
label: "Tier FTUE_Stage_Started"
group_label: "FTUE_Stage_Started"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_ftuestagestarted;;
style: integer
}
measure: number_of_users_ftuestagestarted {
label: "Number of Users FTUE_Stage_Started"
group_label: "FTUE_Stage_Started"
type: count
filters: { field: used_ftuestagestarted value: "yes" }
}
measure: percent_of_users_ftuestagestarted {
label: "Percent of Users that ever FTUE_Stage_Started"
group_label: "FTUE_Stage_Started"
type: number
sql: 1.0 * ${number_of_users_ftuestagestarted} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## IAP_Started
dimension_group: first_iapstarted {
label: "First IAP_Started"
group_label: "IAP_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_iapstarted;;
}
dimension_group: latest_iapstarted {
label: "Latest IAP_Started"
group_label: "IAP_Started"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_iapstarted;;
}
dimension: lifetime_iapstarted {
label: "Lifetime IAP_Started"
group_label: "IAP_Started"
type: number
sql: ${TABLE}.lifetime_iapstarted;;
value_format_name: decimal_0
}
dimension: used_iapstarted {
label: "Ever Used IAP_Started?"
group_label: "IAP_Started"
type: yesno
sql: ${TABLE}.lifetime_iapstarted > 0;;
}
dimension: tier_iapstarted {
label: "Tier IAP_Started"
group_label: "IAP_Started"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_iapstarted;;
style: integer
}
measure: number_of_users_iapstarted {
label: "Number of Users IAP_Started"
group_label: "IAP_Started"
type: count
filters: { field: used_iapstarted value: "yes" }
}
measure: percent_of_users_iapstarted {
label: "Percent of Users that ever IAP_Started"
group_label: "IAP_Started"
type: number
sql: 1.0 * ${number_of_users_iapstarted} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Skin_Unlocked
dimension_group: first_skinunlocked {
label: "First Skin_Unlocked"
group_label: "Skin_Unlocked"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_skinunlocked;;
}
dimension_group: latest_skinunlocked {
label: "Latest Skin_Unlocked"
group_label: "Skin_Unlocked"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_skinunlocked;;
}
dimension: lifetime_skinunlocked {
label: "Lifetime Skin_Unlocked"
group_label: "Skin_Unlocked"
type: number
sql: ${TABLE}.lifetime_skinunlocked;;
value_format_name: decimal_0
}
dimension: used_skinunlocked {
label: "Ever Used Skin_Unlocked?"
group_label: "Skin_Unlocked"
type: yesno
sql: ${TABLE}.lifetime_skinunlocked > 0;;
}
dimension: tier_skinunlocked {
label: "Tier Skin_Unlocked"
group_label: "Skin_Unlocked"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_skinunlocked;;
style: integer
}
measure: number_of_users_skinunlocked {
label: "Number of Users Skin_Unlocked"
group_label: "Skin_Unlocked"
type: count
filters: { field: used_skinunlocked value: "yes" }
}
measure: percent_of_users_skinunlocked {
label: "Percent of Users that ever Skin_Unlocked"
group_label: "Skin_Unlocked"
type: number
sql: 1.0 * ${number_of_users_skinunlocked} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## Harvest_Done
dimension_group: first_harvestdone {
label: "First Harvest_Done"
group_label: "Harvest_Done"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_harvestdone;;
}
dimension_group: latest_harvestdone {
label: "Latest Harvest_Done"
group_label: "Harvest_Done"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_harvestdone;;
}
dimension: lifetime_harvestdone {
label: "Lifetime Harvest_Done"
group_label: "Harvest_Done"
type: number
sql: ${TABLE}.lifetime_harvestdone;;
value_format_name: decimal_0
}
dimension: used_harvestdone {
label: "Ever Used Harvest_Done?"
group_label: "Harvest_Done"
type: yesno
sql: ${TABLE}.lifetime_harvestdone > 0;;
}
dimension: tier_harvestdone {
label: "Tier Harvest_Done"
group_label: "Harvest_Done"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_harvestdone;;
style: integer
}
measure: number_of_users_harvestdone {
label: "Number of Users Harvest_Done"
group_label: "Harvest_Done"
type: count
filters: { field: used_harvestdone value: "yes" }
}
measure: percent_of_users_harvestdone {
label: "Percent of Users that ever Harvest_Done"
group_label: "Harvest_Done"
type: number
sql: 1.0 * ${number_of_users_harvestdone} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
## in_app_purchase
dimension_group: first_inapppurchase {
label: "First in_app_purchase"
group_label: "in_app_purchase"
type: time
timeframes: [raw,date]
sql: ${TABLE}.first_inapppurchase;;
}
dimension_group: latest_inapppurchase {
label: "Latest in_app_purchase"
group_label: "in_app_purchase"
type: time
timeframes: [raw,date]
sql: ${TABLE}.latest_inapppurchase;;
}
dimension: lifetime_inapppurchase {
label: "Lifetime in_app_purchase"
group_label: "in_app_purchase"
type: number
sql: ${TABLE}.lifetime_inapppurchase;;
value_format_name: decimal_0
}
dimension: used_inapppurchase {
label: "Ever Used in_app_purchase?"
group_label: "in_app_purchase"
type: yesno
sql: ${TABLE}.lifetime_inapppurchase > 0;;
}
dimension: tier_inapppurchase {
label: "Tier in_app_purchase"
group_label: "in_app_purchase"
type: tier
tiers: [0,5,10,25,50,100]
sql: ${TABLE}.lifetime_inapppurchase;;
style: integer
}
measure: number_of_users_inapppurchase {
label: "Number of Users in_app_purchase"
group_label: "in_app_purchase"
type: count
filters: { field: used_inapppurchase value: "yes" }
}
measure: percent_of_users_inapppurchase {
label: "Percent of Users that ever in_app_purchase"
group_label: "in_app_purchase"
type: number
sql: 1.0 * ${number_of_users_inapppurchase} / NULLIF(${number_of_users},0);;
value_format_name: percent_2
}
measure: number_of_users {
type: count
}
}
Raw
user_facts.view
#This is a native derived table created using this query:
#https://demoexpo.looker.com/explore/gaming/events?fields=user_facts.d1_retained_users,events.number_of_users,events.d1_retention_rate&fill_fields=user_facts.d1_retained_users&f[events.event_date]=&sorts=user_facts.d1_retained_users&limit=500&column_limit=50&vis=%7B%7D&filter_config=%7B%22events.event_date%22%3A%5B%7B%22type%22%3A%22anytime%22%2C%22values%22%3A%5B%7B%22constant%22%3A%227%22%2C%22unit%22%3A%22day%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&dynamic_fields=%5B%7B%22table_calculation%22%3A%22calculation_1%22%2C%22label%22%3A%22Calculation+1%22%2C%22expression%22%3A%22%24%7Bevents.number_of_users%7D%2Fsum%28%24%7Bevents.number_of_users%7D%29%22%2C%22value_format%22%3Anull%2C%22value_format_name%22%3A%22percent_2%22%2C%22_kind_hint%22%3A%22measure%22%2C%22_type_hint%22%3A%22number%22%7D%5D&origin=share-expanded
view: user_facts {
derived_table: {
explore_source: events {
column: user_id {}
column: days_played {}
column: number_of_level_ups {}
column: highest_level_reached {}
column: install_source {}
column: campaign_name {}
column: number_of_countries_played_in {}
column: most_commonly_played_country {}
column: number_of_devices_used {}
column: most_commonly_used_device {}
column: total_d1_revenue {}
column: total_d7_revenue {}
column: total_d14_revenue {}
column: total_d30_revenue {}
column: total_revenue {}
column: total_iap_revenue {}
column: total_ad_revenue {}
column: total_revenue_after_UA {}
column: number_of_sessions { field: session_facts.number_of_sessions }
column: total_session_length { field: session_facts.total_session_length }
column: cost_per_install {}
column: d1_retained_users {}
column: d7_retained_users {}
column: d14_retained_users {}
column: d30_retained_users {}
column: player_first_seen {}
column: player_last_seen {}
derived_column: is_roas_positive {
sql: case when total_revenue_after_UA > 0 then true else false end ;;
}
filters: {
field: events.event_date
value: ""
}
}
datagroup_trigger: events_raw
partition_keys: ["player_first_seen"]
}
dimension: user_id {
primary_key: yes
}
dimension: total_d1_revenue {
group_label: "LTV"
label: "D1 LTV"
description: "Revenue (ads + IAP) on day 1"
value_format_name: usd
type: number
}
dimension: total_d7_revenue {
group_label: "LTV"
label: "D7 LTV"
description: "Revenue (ads + IAP) on day 7"
value_format_name: usd
type: number
}
dimension: total_d14_revenue {
group_label: "LTV"
label: "D14 LTV"
description: "Revenue (ads + IAP) on day 14"
value_format_name: usd
type: number
}
dimension: total_d30_revenue {
group_label: "LTV"
label: "D30 LTV"
description: "Revenue (ads + IAP) on day 30"
value_format_name: usd
type: number
}
dimension: lifetime_revenue {
group_label: "LTV"
label: "Total Current LTV"
description: "IAP + Ad Revenue"
value_format_name: usd
type: number
sql: ${TABLE}.total_revenue ;;
}
dimension: lifetime_iap_revenue {
group_label: "LTV"
label: "Total IAP Revenue"
description: "Total Revenue from In-App Purchases"
value_format_name: usd
type: number
sql: ${TABLE}.total_iap_revenue ;;
}
dimension: is_spender {
type: yesno
sql: ${lifetime_iap_revenue} > 0 ;;
}
dimension: number_of_devices_used {
type: number
}
dimension: most_commonly_used_device {
label: "Device Model"
description: "(most common for user)"
type: string
}
dimension: number_of_countries_played_in {
type: number
}
dimension: most_commonly_played_country {
type: string
}
dimension: lifetime_spend_tier {
description: "Based on Lifetime LTV spend, are they Minnow/Dolphin/Whale"
type: string
sql: case
when not ${is_spender} then 'Non-Spender ($0)'
when ${lifetime_iap_revenue} BETWEEN 0 and 6 THEN 'Minnow ($0 to $6)'
when ${lifetime_iap_revenue} BETWEEN 6 and 50 THEN 'Dolphin ($6 to $50)'
when ${lifetime_iap_revenue} > 50 THEN 'Whale (>$50)'
else 'other'
end;;
drill_fields: [total_iap_revenue,user_facts.number_of_users]
}
dimension: lifetime_ad_revenue {
group_label: "LTV"
label: "Total Ad Revenue"
description: "Total Revenue from Ads"
value_format_name: usd
type: number
sql: ${TABLE}.total_ad_revenue ;;
}
dimension: total_revenue_after_UA {
group_label: "LTV"
label: "LTV Revenue After UA"
description: "Revenue - Marketing Spend"
value_format_name: usd
type: number
}
dimension: is_roas_positive {
description: "revenue > than acquisition cost"
type: yesno
sql: ${TABLE}.is_roas_positive;;
}
measure: number_of_users_roas_positive {
hidden: yes
type: count
filters: {
field: is_roas_positive
value: "yes"
}
filters: {
field: install_source
value: "-Organic"
}
}
measure: number_of_inorganic_users {
hidden: yes
type: count
filters: {
field: install_source
value: "-Organic"
}
}
measure: percent_roas_positive {
description: "What % of inorganic users are roas positive?"
type: number
sql: 1.0 * ${number_of_users_roas_positive} / NULLIF(${number_of_inorganic_users},0) ;;
value_format_name: percent_2
}
dimension: number_of_sessions {
label: "Lifetime Sessions"
type: number
drill_fields: [session_facts.unique_session_id,session_facts.minutes_session_length]
}
dimension: total_session_length {
label: "Lifetime Play Minutes"
type: number
}
dimension: cost_per_install {
label: "Acquisition Cost"
value_format_name: usd
type: number
}
dimension: days_played {
type: number
description: "Number of distinct days played"
}
measure: median_days_played {
type: median
sql: ${days_played} ;;
}
dimension: lifetime_level_ups {
type: number
sql: ${TABLE}.number_of_level_ups ;;
}
dimension: highest_level_reached {
type: number
sql: ${TABLE}.highest_level_reached;;
}
dimension: is_churned {
description: "Player hasn't been seen for 7 days"
type: yesno
sql: ${days_since_last_seen} > 7 ;;
}
dimension: d1_retained {
group_label: "Retention"
label: "D1 Retained"
type: yesno
description: "Number of players that came back to play on day 1"
sql: CAST(${TABLE}.d1_retained_users as bool) ;;
}
dimension: d7_retained {
group_label: "Retention"
label: "D7 Retained"
description: "Number of players that came back to play on day 7"
type: yesno
sql: CAST(${TABLE}.d7_retained_users as bool) ;;
}
dimension: d14_retained {
group_label: "Retention"
label: "D14 Retained"
description: "Number of players that came back to play on day 14"
type: yesno
sql: CAST(${TABLE}.d14_retained_users as bool) ;;
}
dimension: d30_retained {
group_label: "Retention"
label: "D30 Retained"
description: "Number of players that came back to play on day 30"
type: yesno
sql: CAST(${TABLE}.d30_retained_users as bool) ;;
}
dimension_group: player_first_seen {
description: "Not for direct use, use for NDT"
type: time
}
dimension_group: player_last_seen {
description: "Not for direct use, use for NDT"
type: time
}
dimension_group: since_last_seen {
intervals: [day,hour,week,month]
type: duration
sql_start: ${player_last_seen_raw} ;;
sql_end: CURRENT_TIMESTAMP ;;
}
dimension_group: since_first_seen {
type: duration
intervals: [day,hour,week,month]
sql_start: ${player_first_seen_raw} ;;
sql_end: CURRENT_TIMESTAMP ;;
}
dimension: install_source {}
dimension: campaign_name {}
measure: number_of_users {
type: count
drill_fields: [user_fact_drills*]
}
measure: total_revenue {
label: "Total Revenue"
group_label: "Monetization"
description: "Total Revenue from Ads + In-App Purchases"
type: sum
sql: ${lifetime_revenue} ;;
value_format_name: large_usd
}
measure: total_iap_revenue {
label: "Total IAP Revenue"
group_label: "Monetization"
description: "Total Revenue from In-App Purchases"
type: sum
sql: ${lifetime_iap_revenue} ;;
value_format_name: large_usd
}
measure: total_ad_revenue {
label: "Total Ad Revenue"
group_label: "Monetization"
description: "Total Revenue from Ad"
type: sum
sql: ${lifetime_ad_revenue} ;;
value_format_name: large_usd
}
# D1
measure: d1_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 1"
type: count_distinct sql: ${user_id} ;;
filters: {
field: d1_retained
value: "yes"
}
drill_fields: [d1_retained_users]
}
measure: d1_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 0 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_first_seen
value: ">0"
}
}
measure: d1_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 0 days) that came back to play on day 1"
value_format_name: percent_2
type: number
sql: 1.0 * ${d1_retained_users}/ NULLIF(${d1_eligible_users},0);;
drill_fields: [d1_retention_rate]
}
# D7
measure: d7_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 7"
type: count_distinct sql: ${user_id} ;;
filters: {
field: d7_retained
value: "yes"
}
drill_fields: [d7_retained_users]
}
measure: d7_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 7 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_first_seen
value: ">7"
}
drill_fields: [d7_eligible_users]
}
measure: d7_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 7 days) that came back to play on day 7"
value_format_name: percent_2
type: number
sql: 1.0 * ${d7_retained_users}/ NULLIF(${d7_eligible_users},0);;
drill_fields: [d7_retention_rate]
}
# D14
measure: d14_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 14"
type: count_distinct sql: ${user_id} ;;
filters: {
field: d14_retained
value: "yes"
}
drill_fields: [d14_retained_users]
}
measure: d14_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 14 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_first_seen
value: ">14"
}
drill_fields: [d14_eligible_users]
}
measure: d14_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 14 days) that came back to play on day 14"
value_format_name: percent_2
type: number
sql: 1.0 * ${d14_retained_users}/ NULLIF(${d14_eligible_users},0);;
drill_fields: [d14_retention_rate]
}
# D30
measure: d30_retained_users {
group_label: "Retention"
description: "Number of players that came back to play on day 30"
type: count_distinct sql: ${user_id} ;;
filters: {
field: d30_retained
value: "yes"
}
drill_fields: [d30_retained_users]
}
measure: d30_eligible_users {
hidden: yes
group_label: "Retention"
description: "Number of players older than 30 days"
type: count_distinct
sql: ${user_id} ;;
filters: {
field: days_since_first_seen
value: ">30"
}
drill_fields: [d30_eligible_users]
}
measure: d30_retention_rate {
group_label: "Retention"
description: "% of players (that are older than 30 days) that came back to play on day 30"
value_format_name: percent_2
type: number
sql: 1.0 * ${d30_retained_users}/ NULLIF(${d30_eligible_users},0);;
drill_fields: [d30_retention_rate]
}
### Level Ups
measure: min_highest_level_reached{
group_label: "Level Ups"
type: min
sql: ${highest_level_reached} ;;
value_format_name: decimal_2
}
measure: max_highest_level_reached{
group_label: "Level Ups"
type: max
sql: ${highest_level_reached} ;;
value_format_name: decimal_2
}
measure: median_highest_level_reached {
group_label: "Level Ups"
type: median
sql: ${highest_level_reached} ;;
value_format_name: decimal_2
}
measure: 75_percentile_highest_level_reached {
group_label: "Level Ups"
type: percentile
percentile: 75
sql: ${highest_level_reached} ;;
value_format_name: decimal_2
}
measure: 25_percentile_highest_level_reached {
group_label: "Level Ups"
type: percentile
percentile: 25
sql: ${highest_level_reached} ;;
value_format_name: decimal_2
}
set: user_fact_drills {
fields: [user_id,player_first_seen_date,player_last_seen_date,days_played,lifetime_revenue]
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment