SQL查询以查找当天安装和卸载应用程序的用户 [英] SQL Query to find users that install and uninstall an App on the same day

问题描述

我正在尝试使用Google BigQuery中Firebase Analytics的数据查找当天安装和卸载该应用的用户

I am trying to find the users that install and uninstall the App on the same day using the data from Firebase Analytics in Google BigQuery

这是我到目前为止的去向. 我有一个查询，向我提供安装或卸载该应用程序的用户(或app_instance_id):

This is where I got so far. I have a query that gives me users (or app_instance_id) who install or uninstall the App:

SELECT event.date,
user_dim.app_info.app_instance_id,
event.name
FROM `app_name.app_events_20180303`,
UNNEST(event_dim) AS event
WHERE (event.name = "app_remove" OR event.name = "first_open")
ORDER BY app_instance_id, event.date

它给我以下结果，在其中我可以看到第1行和第2行是安装和卸载该应用程序的同一用户:

It gives me the following result where I can see that row 1 and 2 are the same user that installs and uninstalls the App:

我尝试使用

WHERE (event.name = "app_remove" AND event.name = "first_open")

给出:查询返回零条记录.

which gives: Query returned zero records.

您对如何实现此目标有任何建议吗?谢谢.

Do you have any suggestions on how to achieve this? Thanks.

推荐答案

首先，请注意 iOS无法产生 app_remove，因此此查询仅统计经过安装/卸载模式.

To start, it's worth noting that iOS does not yield app_remove, so this query only counts Android users who go through the install/uninstall pattern.

我创建了一个发出first_open和app_remove的用户子集，并对按日期分组的那些条目进行了计数.我只保留用户一天安装和删除该应用程序相同次数(大于零)的实例.

I created a sub-set of users who emitted first_open and app_remove, and counted those entries grouped by the date. I only kept instances where users installed and removed the app the same number of times in a day (greater than zero).

然后我统计了不同的用户.

Then I tallied the distinct users.

SELECT COUNT(DISTINCT(user_id)) as transient_user_count
FROM (
    SELECT event_date,
    user_id,
    COUNT(if(event_name = "first_open", user_id, NULL)) as user_first_open,
    COUNT(if(event_name = "app_remove", user_id, NULL)) as user_app_remove
    FROM `your_app.analytics_123456.events_*`
--     WHERE (_TABLE_SUFFIX between '20191201' and '20191211')
    GROUP BY user_id, event_date
    HAVING user_first_open > 0 AND user_first_open = user_app_remove
)

如果您不能依靠user_id，则文档建议您可以依靠user_pseudo_id

If you're not able to rely on user_id, then the documentation suggests that you may be able to rely on the user_pseudo_id

这篇关于SQL查询以查找当天安装和卸载应用程序的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！