This Public Data Set on Big Query: is an obtained analytics from a game app(Flood-it), which is used to analyze details about users’ behavior during playing the game.

I was curious to know more about the game users and their behavior during the game.

I analyzed it to obtain information about players' game behavior for micro-transactions.

User Engagement Analysis: Identified 2,463 unique users starting the game, with an average of 468 daily active users, showcasing effective user engagement tracking.

Geographical User Insights: Determined the top 10 user countries, with the USA leading, offering valuable insights for targeted marketing and localization strategies.

In-App Purchase Behavior: Uncovered that users spend an average of 25.59 seconds between free trials and micro-transactions, highlighting effective conversion tactics in the game.

User Retention and Demographics: Revealed that 1,247 users uninstalled the app within a week, and provided detailed data on user distribution across operating systems and languages, aiding in user retention strategies.

The available dataset covers the period between 12/06/2018 and 13/09/2018

User Geographics

1. First question was exploring how many unique users started the game over the covered period

This means filtering the properties column: intial_extra_steps = 20

And to get user data, I had to unnest the user_properties array,

filtered by _TABLE_SUFFIX /, user_properties.key, and user_properties.value.string_value = '20'

SELECT
  COUNT(DISTINCT user_pseudo_id) AS total_unique_users 
FROM `firebase-public-project.analytics_153293282.events_*` 
     , unnest (user_properties) as users_data
WHERE _TABLE_SUFFIX between "20180612" AND "20180913" 
 AND key ='initial_extra_steps' and value.string_value = '20'

The Answer was 2463 users

2. Then I wanted to know from where come most of our users

This means, what are the top 10 country users of this app?

from DB I got all the included countries and used the count() function, and ordered descendingly by Total users, and used the limit to get the top 10 countries.

SELECT   
  geo.country,
  COUNT(*) as total_user
FROM `firebase-public-project.analytics_153293282.events_*` 
group by 1
order by 2 desc
LIMIT 10

USA comes at the top

Top 10 countries where the users played the game

Behavior analysis