Please enable Javascript to view the contents

基于 BigQuery 原始数据的指标体系

 ·  ☕ 4 分钟

本文基于 Firebase（原始数据）-> GCS（云存储）-> BigQuery（数仓）-> Looker Studio（可视化）。

几点说明：

  • 共两个阶段会对已有字段（以下称为列）进行加工：
    • 在 Looker Studio 连接 BigQuery 数据源时：加在原有列的基础上；
    • 在 Looker Studio 可视化查询时：加在已导入列的基础上，即实时可视化查询时；
  • 示例的 SQL 语句省略了除0的情况；
  • BigQuery 支持窗口函数；

统计原则

一个 ID，两个时间戳

  • user_pseudo_id：用户唯一标识；
  • user_first_touch_timestamp：首次打开的时间戳；
  • event_timestamp：事件发生的时间戳；
时区说明：
event_date：导出至 BigQuery 设置中的时区；
event_timestamp/user_first_touch_timestamp：时间戳类型全部为 UTC 时区；

统计次数

没有使用 COUNT(*)，是为了事件去重。

1

COUNT(DISTINCT event_timestamp)

统计人数

使用的是 Firebase/BigQuery 的匿名用户标识 user_pseudo_id.

1

COUNT(DISTINCT user_pseudo_id)

统计频次

次数 / 人数。

1

COUNT(DISTINCT event_timestamp) / COUNT(DISTINCT user_pseudo_id)

新增计算列

days_x

✍ 同期群分析，本质上是围绕 event_timestamp 和 user_first_touch_timestamp 之间相差的天数展开的。

1
2
3
4
5
6
7
8

-- days_x
CAST(TIMESTAMP_DIFF(TIMESTAMP_MICROS(event_timestamp), TIMESTAMP_MICROS(user_first_touch_timestamp), DAY) AS INT64) AS days_x

-- hours_x
CAST(TIMESTAMP_DIFF(TIMESTAMP_MICROS(event_timestamp), TIMESTAMP_MICROS(user_first_touch_timestamp), HOUR) AS INT64) AS hours_x

-- minutes_x
CAST(TIMESTAMP_DIFF(TIMESTAMP_MICROS(event_timestamp), TIMESTAMP_MICROS(user_first_touch_timestamp), MINUTE) AS INT64) AS minutes_x

media_source

用于区分流量来源（归因）。
⚠️ 需要按需修改：实际接入的流量源。

👉 指路我的另外一篇文章 使用 Play Install Referrer API 解密 Facebook Campaign

1
2
3
4

CASE traffic_source.source
    WHEN 'apps.facebook.com' THEN 'Facebook Ads'
    ELSE 'Organic'
END AS media_source

revenue_kind

用于区分收入类型。
⚠️ 需要按需修改：收入事件名称。

1
2
3
4
5
6

CASE event_name
    WHEN 'ad_revenue' THEN 'Ad'
    WHEN 'purchase' THEN 'IAP'
    WHEN 'subscription' THEN 'Subscription'
    ELSE 'unknown'
END AS revenue_kind

revenue

用于统一计算所有类型的收入：广告、内购（一次性）、订阅。
⚠️ 需要按需修改：实际接入的聚合平台、收入事件名称。

1
2
3
4
5

CASE 
    WHEN event_name = 'ad_revenue' AND event_params.key = 'ad_revenue' THEN event_params.value.double_value
    WHEN event_name IN ('purchase', 'subscription') AND event_params.key = 'price' THEN event_params.value.float_value 
    ELSE 0 
END AS revenue

基础指标

newUser

新增。

1

COUNT(DISTINCT CASE WHEN event_name = 'first_open' THEN user_pseudo_id END)

DAU

关于活跃的定义：

1
2
3
4
5

-- Firebase 定义的活跃
COUNT(DISTINCT CASE WHEN event_name = 'user_engagement' THEN user_pseudo_id END)

-- 自定义的活跃
COUNT(DISTINCT CASE WHEN event_name = 'login' THEN user_pseudo_id END)

ARPDAU

活跃用户的 ARPU，其中活跃使用上述自定义的。

1

SUM(revenue) / DAU

ARPU (New)

新用户的 ARPU.

1

SUM(revenue) / newUser

同期群指标

RR

留存率。与上述活跃定义取齐，留存率计算公式：Rx = Dx活跃 / D0活跃。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 0D
COUNT(DISTINCT CASE WHEN event_name = 'user_engagement' AND days_x = 0 THEN user_pseudo_id END)

-- 1D
COUNT(DISTINCT CASE WHEN event_name = 'user_engagement' AND days_x = 1 THEN user_pseudo_id END)

-- 7D
COUNT(DISTINCT CASE WHEN event_name = 'user_engagement' AND days_x = 7 THEN user_pseudo_id END)

-- R1
1D / 0D

-- R7
7D / 0D

LTV

给定周期内的总收入。

1
2
3
4
5

-- LT7
SUM(CASE WHEN days_x <= 6 THEN revenue END)

-- LT14
SUM(CASE WHEN days_x <= 13 THEN revenue END)

广告变现指标

Imps

广告展示次数。

1

COUNT(DISTINCT CASE WHEN event_name = 'ad_play_ok' THEN event_timestamp END)

DAV

广告展示人数。

1

COUNT(DISTINCT CASE WHEN event_name = 'ad_play_ok' THEN user_pseudo_id END)

eCPM

1

SUM(CASE WHEN revenue_kind = 'Ad' THEN revenue END) / Imps * 1000

Imps per DAU

活跃用户，平均广告展示次数。

1

Imps / DAU

Imps per DAV

看到广告的用户，平均广告展示次数。

1

Imps / DAV

附：原始数据结构

以下为自定义事件 sign_up 的原始数据，有助于理解数据结构。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

[{
  "event_date": "20230116",
  "event_timestamp": "1673858401132002",
  "event_name": "sign_up",
  "event_params": [{
    "key": "firebase_screen_class",
    "value": {
      "string_value": "UnityPlayerActivity",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "method",
    "value": {
      "string_value": "Android",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "ga_session_id",
    "value": {
      "string_value": null,
      "int_value": "1673858394",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "ga_session_number",
    "value": {
      "string_value": null,
      "int_value": "2",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "firebase_screen_id",
    "value": {
      "string_value": null,
      "int_value": "-5164663614086310235",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "firebase_event_origin",
    "value": {
      "string_value": "app",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "engaged_session_event",
    "value": {
      "string_value": null,
      "int_value": "1",
      "float_value": null,
      "double_value": null
    }
  }],
  "event_previous_timestamp": "1673780157003002",
  "event_value_in_usd": null,
  "event_bundle_sequence_id": "22",
  "event_server_timestamp_offset": "2492698",
  "user_id": null,
  "user_pseudo_id": "8d59ce7133e03f6170eadbce40174c91",
  "privacy_info": {
    "analytics_storage": "Yes",
    "ads_storage": "Yes",
    "uses_transient_token": "No"
  },
  "user_properties": [{
    "key": "ga_session_id",
    "value": {
      "string_value": null,
      "int_value": "1673858394",
      "float_value": null,
      "double_value": null,
      "set_timestamp_micros": "1673858394853000"
    }
  }, {
    "key": "first_open_time",
    "value": {
      "string_value": null,
      "int_value": "1673780400000",
      "float_value": null,
      "double_value": null,
      "set_timestamp_micros": "1673777018672000"
    }
  }, {
    "key": "ga_session_number",
    "value": {
      "string_value": null,
      "int_value": "2",
      "float_value": null,
      "double_value": null,
      "set_timestamp_micros": "1673858394853000"
    }
  }, {
    "key": "player_match_level",
    "value": {
      "string_value": "8",
      "int_value": null,
      "float_value": null,
      "double_value": null,
      "set_timestamp_micros": "1673780429774000"
    }
  }],
  "user_first_touch_timestamp": "1673777018672000",
  "user_ltv": null,
  "device": {
    "category": "mobile",
    "mobile_brand_name": "Xiaomi",
    "mobile_model_name": "M2104K10AC",
    "mobile_marketing_name": "Redmi K40 Gaming Edition",
    "mobile_os_hardware_model": "M2104K10AC",
    "operating_system": "Android",
    "operating_system_version": "Android 11",
    "vendor_id": null,
    "advertising_id": "",
    "language": "zh-cn",
    "is_limited_ad_tracking": "No",
    "time_zone_offset_seconds": "28800",
    "browser": null,
    "browser_version": null,
    "web_info": null
  },
  "geo": {
    "continent": "Asia",
    "country": "China",
    "region": "",
    "city": "",
    "sub_continent": "Eastern Asia",
    "metro": "(not set)"
  },
  "app_info": {
    "id": "PACKAGE_NAME",
    "version": "1.2.8",
    "install_store": null,
    "firebase_app_id": "1:65595447720:android:aa82859441a614a0aba59d",
    "install_source": "com.miui.packageinstaller"
  },
  "traffic_source": {
    "name": "(direct)",
    "medium": "(none)",
    "source": "(direct)"
  },
  "stream_id": "3607414280",
  "platform": "ANDROID",
  "event_dimensions": null,
  "ecommerce": null,
  "items": []
}]
分享
Molly Wang
作者
Molly Wang
一个数据产品人的自我修养