1

I'm currently trying to count unique users for my custom Firebase events in BigQuery. While I've been able to get to the figures in aggregation by using the APPROX_COUNT_DISTINCT function, I'm still stuck to get the correct (unique) count when SELECTING and adding a number of different dimensions to the table.

The following resource about using HLL_COUNT.INIT has brought me a step closer, but I haven’t figured out how to use the HLL_COUNT.MERGE function within the same table so I can get event + unique user count with applying filters in datastudio

enter image description here

Query I’ve used so far:

SELECT
 (SELECT x.date) AS event_date, 
 (SELECT x.name) AS name,
 (SELECT params.value.string_value FROM x.params WHERE params.key = 'grade') AS vl_grades,
 user_dim.geo_info.region as user_region,
  user_dim.geo_info.city as user_city,
  user_dim.device_info.user_default_language as user_language,
   (SELECT user_prop.key) AS user_prop_key,
   (SELECT user_prop.value.value.string_value) AS user_prop_string_value,
    COUNTIF(user_prop.key = "first_open_time") as event_count ,
    APPROX_COUNT_DISTINCT(user_dim.app_info.app_instance_id) as unique_user,
    HLL_COUNT.INIT(user_dim.app_info.app_instance_id) as sketch
FROM `project.info_project_TOTAL.TOTAL_results_jobs` ,
UNNEST (user_dim.user_properties) AS user_prop,
UNNEST(event_dim) AS x
WHERE x.name = 'Zlag_Click'

GROUP BY date,user_prop_key,user_prop_string_value,name,fr_grades,vl_grades,style,item_category,indoor_outdoor,boulder_route,item_name,user_dim.geo_info.country,user_dim.app_info.app_platform,user_dim.geo_info.region,user_dim.geo_info.city,user_dim.device_info.user_default_language,location

ORDER BY event_count desc

Does anyone have any ideas on how I could get to the point, where the table allows me to answer questions like 
- How many unique users from Germany have triggered an event in the last x days? - How many unique users have triggered an event with the difficulty level 5 in the last x days? - How many unique users have been requesting the follwing resource in the last x days?



Thanks

Peter P
  • 491
  • 4
  • 14

1 Answers1

1

Let project.table_results be the table you've saved the results from the query to. Since I don't see columns corresponding to all of the questions you want to answer, I'm going to make some assumptions about the columns that you can make available.

To count the number of users that have triggered an event_count in the last 5 days:

SELECT HLL_COUNT.MERGE(sketch) approx_cnt
FROM `project.table_results`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)
AND event_count > 0

If there's a difficulty level field; event_diff_level, add

AND event_diff_level == 5

To get the number of unique users requesting a specific resource, add

AND <resource_column_name> == <resource>

Also, you do not need to do (SELECT x.<struct_field>) as <struct_field>. simply x.<struct_field> as <struct_field> should work. I hope that helps.

Lisa Yin
  • 131
  • 5
  • Thanks Lisa, When trying SELECT HLL_COUNT.MERGE(sketch) approx_cnt FROM `project.table_results` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY) AND event_count > 0 I do get an Unrecognized name: date error. May it be related to the fact, that date is stores as STRING by default? thanks – Peter P May 17 '18 at 14:28
  • I think you need to wrap the date with PARSE_DATE, for example: `PARSE_DATE('%x', date)` as seen https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#parse_date – Lisa Yin May 17 '18 at 20:53