数仓中应该出现的所有表格及其逻辑
1.ods_app_log(app日志贴源表)
计算:详情请见数据预处理整体代码实现
源表:原始数据
+--------------+------------------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+-------------+
| a.guid | a.eventid | a.event | a.uid | a.imei | a.mac | a.imsi | a.osname | a.osver | a.androidid | a.resolution | a.devicetype | a.deviceid | a.uuid | a.appid | a.appver | a.release_ch | a.promotion_ch | a.longtitude | a.latitude | a.carrier | a.nettype | a.cid_sn | a.ip | a.sessionid | a.timestamp | a.province | a.city | a.district | a.dt |
+--------------+------------------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+-------------+
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598312000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598354000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598472000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598680000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598973000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599083000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"1","keyword":"vIl","isHistoryWordUsed":"0"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599350000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"1","keyword":"tuo","isHistoryWordUsed":"1"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599525000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"1","keyword":"wqEwD","isHistoryWordUsed":"1"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599753000 | 未知 | 未知 | 未知 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"0","keyword":"qcgjHL","isHistoryWordUsed":"0"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599932000 | 未知 | 未知 | 未知 | 2020-01-13 |
+--------------+------------------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+-------------+
2.dwd_apl_glb_dtl(app日志全局明细表)
计算:就是贴源层规整了一下时间
源表:ods_app_log(app日志贴源表)
+--------------+------------------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+-------------+
| a.guid | a.eventid | a.event | a.uid | a.imei | a.mac | a.imsi | a.osname | a.osver | a.androidid | a.resolution | a.devicetype | a.deviceid | a.uuid | a.appid | a.appver | a.release_ch | a.promotion_ch | a.longtitude | a.latitude | a.carrier | a.nettype | a.cid_sn | a.ip | a.sessionid | a.timestamp | a.province | a.city | a.district | a.year | a.month | a.day | a.datestr | a.dt |
+--------------+------------------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+-------------+
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598312000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-11-06 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598354000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-12-06 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598472000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-14-06 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598680000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-18-06 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575598973000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-22-06 | 2020-01-13 |
| -1146132555 | viewConentDetailEvent | {"pgId":"405","contentID":"00824","contentTag":"","contentType":"","contentTile":"","contentChannel":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599083000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-24-06 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"1","keyword":"vIl","isHistoryWordUsed":"0"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599350000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-29-06 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"1","keyword":"tuo","isHistoryWordUsed":"1"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599525000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-32-06 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"1","keyword":"wqEwD","isHistoryWordUsed":"1"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599753000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-35-06 | 2020-01-13 |
| -1146132555 | searchEvent | {"existResult":"0","keyword":"qcgjHL","isHistoryWordUsed":"0"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575599932000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-38-06 | 2020-01-13 |
+--------------+------------------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+-------------+
3.dim_pg_info(页面信息维表)
计算:无计算
源表这是个给定的维表
+---------+------------+-------------+--------------------------------+
| a.pgid | a.channel | a.category | a.url |
+---------+------------+-------------+--------------------------------+
| 675 | 篮球频道 | 频道页 | http://doitedu.cn/006599.html |
| 467 | 足球频道 | 详情页 | http://doitedu.cn/003810.html |
| 296 | 羽球频道 | T | http://doitedu.cn/004881.html |
| 691 | 社会新闻 | Q | http://doitedu.cn/007402.html |
| 125 | 热点新闻 | W | http://doitedu.cn/002582.html |
| 944 | 热点推荐 | P | http://doitedu.cn/008115.html |
| 853 | 关注推荐 | M | http://doitedu.cn/006645.html |
| 451 | h | N | http://doitedu.cn/008540.html |
| 800 | j | Y | http://doitedu.cn/004172.html |
| 666 | c | D | http://doitedu.cn/002676.html |
+---------+------------+-------------+--------------------------------+
4.dwd_apl_tfc_dtl(app日志流量事件明细表)
计算:dwd_apl_glb_dtlapp(日志全局明细表) join dim_pg_info( 页面信息维表)
源表:dwd_apl_glb_dtlapp(日志全局明细表) ,dim_pg_info( 页面信息维表)
说明:dwd_apl_tfc_dtl(app日志流量事件明细表)可概括为浏览量事件称为pv事件
+--------------+--------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+---------------------+---------------------+------------+------------+---------------+----------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+------------+-------------+--------------------------------+-------------+
| a.guid | a.eventid | a.event | a.uid | a.imei | a.mac | a.imsi | a.osname | a.osver | a.androidid | a.resolution | a.devicetype | a.deviceid | a.uuid | a.appid | a.appver | a.release_ch | a.promotion_ch | a.longtitude | a.latitude | a.carrier | a.nettype | a.cid_sn | a.ip | a.sessionid | a.timestamp | a.province | a.city | a.district | a.year | a.month | a.day | a.datestr | a.channel | a.category | a.url | a.dt |
+--------------+--------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+---------------------+---------------------+------------+------------+---------------+----------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+------------+-------------+--------------------------------+-------------+
| -1146132555 | pgviewEvent | {"pgid":"056","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/abi/pg405","url":"http://www.doitedu.cn/abi/pg056","utm_term":"","utm_content":"","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601844000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | pgviewEvent | {"pgid":"011","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/abi/pg056","url":"http://www.doitedu.cn/aaa/pg011","utm_term":"","utm_content":"","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575603180000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-33-06 | p | V | http://doitedu.cn/009702.html | 2020-01-13 |
| -1146132555 | pgviewEvent | {"pgid":"194","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/aaa/pg011","url":"http://www.doitedu.cn/aaf/pg194","utm_term":"","utm_content":"","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575603224000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-33-06 | x | S | http://doitedu.cn/008741.html | 2020-01-13 |
| -1146132555 | pgviewEvent | {"pgid":"919","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/aaf/pg194","url":"http://www.doitedu.cn/acg/pg919","utm_term":"","utm_content":"","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575604330000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-52-06 | a | G | http://doitedu.cn/008062.html | 2020-01-13 |
| -1146132555 | pgviewEvent | {"pgid":"276","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/acg/pg919","url":"http://www.doitedu.cn/abh/pg276","utm_term":"","utm_content":"","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575604756000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-59-06 | l | Z | http://doitedu.cn/004561.html | 2020-01-13 |
| -1224944964 | pgviewEvent | {"pgid":"170","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.sina.com","url":"http://www.doitedu.cn/ace/pg170","utm_term":"","utm_content":"","title":""} | 216706 | 9834242632485962 | fa-19-77-87-d5-25-d6 | 4956368432140834 | macos | 6.0 | | 1366*768 | SAMSUNG | iCCZaL | BV7d2H3xQbhEBbAT | com.51doit.mall | 1.0.0 | 安卓市场 | 08 | 107.57810721984012 | 30.888374437819035 | ISP07 | N | 730230206357 | 46.151.115.47 | sid-70149217-a534-4fa2-b278-bd19ccc15ed6 | 1575522423000 | 未知 | 未知 | 未知 | 2019 | 12 | 5 | 2019-07-05 | d | O | http://doitedu.cn/002856.html | 2020-01-13 |
| -1224944964 | pgviewEvent | {"pgid":"747","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/ace/pg170","url":"http://www.doitedu.cn/acd/pg747","utm_term":"","utm_content":"","title":""} | 216706 | 9834242632485962 | fa-19-77-87-d5-25-d6 | 4956368432140834 | macos | 6.0 | | 1366*768 | SAMSUNG | iCCZaL | BV7d2H3xQbhEBbAT | com.51doit.mall | 1.0.0 | 安卓市场 | 08 | 107.57810721984012 | 30.888374437819035 | ISP07 | N | 730230206357 | 46.151.115.47 | sid-70149217-a534-4fa2-b278-bd19ccc15ed6 | 1575523052000 | 未知 | 未知 | 未知 | 2019 | 12 | 5 | 2019-17-05 | j | E | http://doitedu.cn/009342.html | 2020-01-13 |
| -1224944964 | pgviewEvent | {"pgid":"782","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/acd/pg747","url":"http://www.doitedu.cn/abe/pg782","utm_term":"","utm_content":"","title":""} | 216706 | 9834242632485962 | fa-19-77-87-d5-25-d6 | 4956368432140834 | macos | 6.0 | | 1366*768 | SAMSUNG | iCCZaL | BV7d2H3xQbhEBbAT | com.51doit.mall | 1.0.0 | 安卓市场 | 08 | 107.57810721984012 | 30.888374437819035 | ISP07 | N | 730230206357 | 46.151.115.47 | sid-70149217-a534-4fa2-b278-bd19ccc15ed6 | 1575525658000 | 未知 | 未知 | 未知 | 2019 | 12 | 5 | 2019-00-05 | y | H | http://doitedu.cn/002268.html | 2020-01-13 |
| -1224944964 | pgviewEvent | {"pgid":"438","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/abe/pg782","url":"http://www.doitedu.cn/aah/pg438","utm_term":"","utm_content":"","title":""} | 216706 | 9834242632485962 | fa-19-77-87-d5-25-d6 | 4956368432140834 | macos | 6.0 | | 1366*768 | SAMSUNG | iCCZaL | BV7d2H3xQbhEBbAT | com.51doit.mall | 1.0.0 | 安卓市场 | 08 | 107.57810721984012 | 30.888374437819035 | ISP07 | N | 730230206357 | 46.151.115.47 | sid-70149217-a534-4fa2-b278-bd19ccc15ed6 | 1575527941000 | 未知 | 未知 | 未知 | 2019 | 12 | 5 | 2019-39-05 | a | T | http://doitedu.cn/008315.html | 2020-01-13 |
| -1224944964 | pgviewEvent | {"pgid":"626","utm_medium":"","referrer":"","utm_source":"","utm_campaign":"","referrer_host":"http://www.doitedu.cn/aah/pg438","url":"http://www.doitedu.cn/abo/pg626","utm_term":"","utm_content":"","title":""} | 216706 | 9834242632485962 | fa-19-77-87-d5-25-d6 | 4956368432140834 | macos | 6.0 | | 1366*768 | SAMSUNG | iCCZaL | BV7d2H3xQbhEBbAT | com.51doit.mall | 1.0.0 | 安卓市场 | 08 | 107.57810721984012 | 30.888374437819035 | ISP07 | N | 730230206357 | 46.151.115.47 | sid-70149217-a534-4fa2-b278-bd19ccc15ed6 | 1575528102000 | 未知 | 未知 | 未知 | 2019 | 12 | 5 | 2019-41-05 | v | M | http://doitedu.cn/006869.html | 2020-01-13 |
+--------------+--------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+---------------------+---------------------+------------+------------+---------------+----------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+------------+-------------+--------------------------------+-------------+
5.dwd_apl_itr_dtl(app日志交互事件明细表)
计算:模糊查询 eventid ! like ‘as%’ and eventid ! like ‘pgviewEvent’
源表:dwd_apl_glb_dtl(app日志全局明细表)
说明:
所有的事件总共分为三种,所有的事件中,除了广告事件,浏览事件,剩下的全部属于交互事件!!!
pageview 简称pv事件 浏览事件:页面没有因为我们的浏览而产生变化,页面没有因为浏览而产生实质性的变化!
advertising 简称ad事件 广告事件:页面给用户提供广告,单数用户并没有理会,用户也没有因为广告的推送产生实质性的变化
太多的无法标识 交互事件:点击,收藏,注册会员,评论,消费, 我们的努力是为了多产生交互事件!
+--------------+----------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+------------+-------------+--------------------------------+-------------+
| a.guid | a.eventid | a.event | a.uid | a.imei | a.mac | a.imsi | a.osname | a.osver | a.androidid | a.resolution | a.devicetype | a.deviceid | a.uuid | a.appid | a.appver | a.release_ch | a.promotion_ch | a.longtitude | a.latitude | a.carrier | a.nettype | a.cid_sn | a.ip | a.sessionid | a.timestamp | a.province | a.city | a.district | a.year | a.month | a.day | a.datestr | a.channel | a.category | a.url | a.dt |
+--------------+----------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+------------+-------------+--------------------------------+-------------+
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601846000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601847000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601848000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601849000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601850000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601851000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"056","url":"http://www.doitedu.cn/abi/pg056","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601852000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | enterTabEvent | {"pgid":"056","tabName":"26"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601853000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | enterTabEvent | {"pgid":"056","tabName":"27"} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601854000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-10-06 | d | A | http://doitedu.cn/004224.html | 2020-01-13 |
| -1146132555 | webStayEvent | {"pgid":"011","url":"http://www.doitedu.cn/aaa/pg011","title":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575603182000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-33-06 | p | V | http://doitedu.cn/009702.html | 2020-01-13 |
+--------------+----------------+----------------------------------------------------+---------+-------------------+-----------------------+-------------------+-----------+----------+-------------------+---------------+---------------+-------------+-------------------+------------------+-----------+---------------+-----------------+--------------------+---------------------+------------+------------+---------------+---------------+-------------------------------------------+----------------+-------------+---------+-------------+---------+----------+--------+-------------+------------+-------------+--------------------------------+-------------+
6.0 dim_ad_info(广告描述维表)
计算: 无
源表: 无
说明:是媒体产生的维表
+-------------------+---------------------+-----------------------+
| dim_ad_info.adid | dim_ad_info.medium | dim_ad_info.campaign |
+-------------------+---------------------+-----------------------+
| adid | medium | campaign |
| 7 | meduim_2 | campain_88 |
| 3 | meduim_1 | campain_77 |
| 8 | meduim_2 | campain_87 |
| 0 | meduim_3 | campain_89 |
| 5 | meduim_5 | campain_74 |
| 6 | meduim_4 | campain_80 |
| 9 | meduim_5 | campain_79 |
| 1 | meduim_4 | campain_80 |
| 4 | meduim_2 | campain_85 |
| 2 | meduim_5 | campain_69 |
+-------------------+---------------------+-----------------------+
7 dwd_apl_adv_dtl (app日志广告信息事件表)
计算
源表: dwd全局明细表 & 页面信息维表 & 广告信息维表
说明:
+-----------------------+--------------------------+----------------------------------------------------+----------------------+-----------------------+-----------------------+-----------------------+-------------------------+------------------------+----------------------------+-----------------------------+-----------------------------+---------------------------+-----------------------+------------------------+-------------------------+-----------------------------+-------------------------------+-----------------------------+---------------------------+--------------------------+--------------------------+-------------------------+---------------------+-------------------------------------------+----------------------------+---------------------------+-----------------------+---------------------------+-----------------------+------------------------+----------------------+--------------------------+--------------------------+---------------------------+--------------------------------+-------------------------+---------------------------+---------------------+
| dwd_apl_adv_dtl.guid | dwd_apl_adv_dtl.eventid | dwd_apl_adv_dtl.event | dwd_apl_adv_dtl.uid | dwd_apl_adv_dtl.imei | dwd_apl_adv_dtl.mac | dwd_apl_adv_dtl.imsi | dwd_apl_adv_dtl.osname | dwd_apl_adv_dtl.osver | dwd_apl_adv_dtl.androidid | dwd_apl_adv_dtl.resolution | dwd_apl_adv_dtl.devicetype | dwd_apl_adv_dtl.deviceid | dwd_apl_adv_dtl.uuid | dwd_apl_adv_dtl.appid | dwd_apl_adv_dtl.appver | dwd_apl_adv_dtl.release_ch | dwd_apl_adv_dtl.promotion_ch | dwd_apl_adv_dtl.longtitude | dwd_apl_adv_dtl.latitude | dwd_apl_adv_dtl.carrier | dwd_apl_adv_dtl.nettype | dwd_apl_adv_dtl.cid_sn | dwd_apl_adv_dtl.ip | dwd_apl_adv_dtl.sessionid | dwd_apl_adv_dtl.timestamp | dwd_apl_adv_dtl.province | dwd_apl_adv_dtl.city | dwd_apl_adv_dtl.district | dwd_apl_adv_dtl.year | dwd_apl_adv_dtl.month | dwd_apl_adv_dtl.day | dwd_apl_adv_dtl.datestr | dwd_apl_adv_dtl.channel | dwd_apl_adv_dtl.category | dwd_apl_adv_dtl.url | dwd_apl_adv_dtl.medium | dwd_apl_adv_dtl.campaign | dwd_apl_adv_dtl.dt |
+-----------------------+--------------------------+----------------------------------------------------+----------------------+-----------------------+-----------------------+-----------------------+-------------------------+------------------------+----------------------------+-----------------------------+-----------------------------+---------------------------+-----------------------+------------------------+-------------------------+-----------------------------+-------------------------------+-----------------------------+---------------------------+--------------------------+--------------------------+-------------------------+---------------------+-------------------------------------------+----------------------------+---------------------------+-----------------------+---------------------------+-----------------------+------------------------+----------------------+--------------------------+--------------------------+---------------------------+--------------------------------+-------------------------+---------------------------+---------------------+
| -1146132555 | adShowEvent | {"pgId":"405","adId":"2","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601109000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-58-06 | x | O | http://doitedu.cn/006329.html | meduim_5 | campain_69 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"405","adId":"6","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601174000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-59-06 | x | O | http://doitedu.cn/006329.html | meduim_4 | campain_80 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"405","adId":"5","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601338000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-02-06 | x | O | http://doitedu.cn/006329.html | meduim_5 | campain_74 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"405","adId":"4","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601475000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-04-06 | x | O | http://doitedu.cn/006329.html | meduim_2 | campain_85 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"405","adId":"8","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575601592000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-06-06 | x | O | http://doitedu.cn/006329.html | meduim_2 | campain_87 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"056","adId":"0","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575602279000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-17-06 | d | A | http://doitedu.cn/004224.html | meduim_3 | campain_89 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"056","adId":"8","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575602307000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-18-06 | d | A | http://doitedu.cn/004224.html | meduim_2 | campain_87 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"056","adId":"1","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575602530000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-22-06 | d | A | http://doitedu.cn/004224.html | meduim_4 | campain_80 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"056","adId":"9","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575602777000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-26-06 | d | A | http://doitedu.cn/004224.html | meduim_5 | campain_79 | 2020-01-13 |
| -1146132555 | adShowEvent | {"pgId":"056","adId":"7","adSource":"","adTitle":"","adResourceID":"","adType":"","adPosition":""} | 416687 | 4914733564681748 | 2a-53-14-00-2d-05-0c | 5182110896884591 | android | 8.2 | 9bcac6961103ed62 | 1356*768 | HUAWEI_MATE2 | | tDyOPpuuiUjiCt1v | com.51doit.mall | 1.8.2 | 车载安卓市场 | 10 | 104.9005577982516 | 25.095148055926884 | ISP08 | 4G | 290123973051 | 172.58.18.27 | sid-d85d3ac6-b20b-41e1-9e80-d01059680566 | 1575602848000 | 未知 | 未知 | 未知 | 2019 | 12 | 6 | 2019-27-06 | d | A | http://doitedu.cn/004224.html | meduim_2 | campain_88 | 2020-01-13 |
+-----------------------+--------------------------+----------------------------------------------------+----------------------+-----------------------+-----------------------+-----------------------+-------------------------+------------------------+----------------------------+-----------------------------+-----------------------------+---------------------------+-----------------------+------------------------+-------------------------+-----------------------------+-------------------------------+-----------------------------+---------------------------+--------------------------+--------------------------+-------------------------+---------------------+-------------------------------------------+----------------------------+---------------------------+-----------------------+---------------------------+-----------------------+------------------------+----------------------+--------------------------+--------------------------+---------------------------+--------------------------------+-------------------------+---------------------------+---------------------+
8.dws_apl_tfc_ags(流量会话聚合表)
将app日志事件明细表轻聚合
+-----------------------+-------------------------------------------+-----------------------------+---------------------------+--------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
| dws_apl_tfc_ags.guid | dws_apl_tfc_ags.sessionid | dws_apl_tfc_ags.start_time | dws_apl_tfc_ags.end_time | dws_apl_tfc_ags.pv_cnts | dws_apl_tfc_ags.province | dws_apl_tfc_ags.city | dws_apl_tfc_ags.district | dws_apl_tfc_ags.devicetype | dws_apl_tfc_ags.osname | dws_apl_tfc_ags.osver | dws_apl_tfc_ags.release_ch | dws_apl_tfc_ags.promotion_ch | dws_apl_tfc_ags.dt |
+-----------------------+-------------------------------------------+-----------------------------+---------------------------+--------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
| -2140990168 | sid-a9f3a9f7-ca7f-48c1-b9d0-159bd8282854 | 1575522243000 | 1575633134000 | 33 | 未知 | 未知 | 未知 | HONGMI_8 | android | 12.0 | 百度手机助手 | 04 | 2020-01-13 |
| -2137329215 | sid-64b63cf0-4559-443c-988a-877f058aba6e | 1575522543000 | 1575543338000 | 10 | 未知 | 未知 | 未知 | MEIZU_ML7 | macos | 6.5 | 酷市场-CoolMar | 11 | 2020-01-13 |
| -2136134593 | sid-ef6dfff5-ea4e-48b9-94b1-5a85ed78f65c | 1575522483000 | 1575551424000 | 13 | 未知 | 未知 | 未知 | XIAOLAJIAO | macos | 7.0 | 安软市场 | 09 | 2020-01-13 |
| -2132783903 | sid-f9d230dd-8205-4ac9-990d-7d80598c0fba | 1575522303000 | 1575534501000 | 4 | 天津市 | 天津市 | 和平区 | ZX | macos | 10.05 | 最美应用 | 10 | 2020-01-13 |
| -2127774672 | sid-d0dcd37a-0d42-4ad0-bfd4-14f2bcaa31f5 | 1575522483000 | 1575557017000 | 18 | 未知 | 未知 | 未知 | MEIZU_ML7 | android | 9.0 | 纽扣助手 | 04 | 2020-01-13 |
| -2127756201 | sid-3d77dda0-2d9b-4685-ae75-bf65f2b539e0 | 1575522423000 | 1575594585000 | 27 | 未知 | 未知 | 未知 | IPHONE_6PLUS | windows | 7.0 | 金山手机助手 | 01 | 2020-01-13 |
| -2127613434 | sid-25bb13e6-f6bd-462d-ac18-4b2047ac5209 | 1575522183000 | 1575581048000 | 23 | 未知 | 未知 | 未知 | ZX | macos | 7.0 | 纽扣助手 | 04 | 2020-01-13 |
| -2124886435 | sid-c76cf576-e2f0-4ff1-b391-48b5209f2714 | 1575522183000 | 1575592278000 | 27 | 未知 | 未知 | 未知 | ZX | ios | 12.0 | 安卓市场 | 02 | 2020-01-13 |
| -2122725907 | sid-516cf6fd-ad8e-4620-b99e-05721d1e280f | 1575522423000 | 1575581362000 | 20 | 未知 | 未知 | 未知 | MI_MIX1 | macos | 10.05 | 华为应用市场 | 07 | 2020-01-13 |
| -2122376442 | sid-71164732-488d-4484-a0cf-c10d89ab40b7 | 1575522123000 | 1575547828000 | 13 | 未知 | 未知 | 未知 | SAMSUNG | macos | 10.0 | 最美应用 | 03 | 2020-01-13 |
+-----------------------+-------------------------------------------+-----------------------------+---------------------------+--------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
9.dws_apl_tfc_ags(流量用户聚合表)
将流量会话聚合表 DWS_APL_TFC_AGS 继续聚合
+-----------------------+-------------------------------------------+-----------------------------+---------------------------+--------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
| dws_apl_tfc_ags.guid | dws_apl_tfc_ags.sessionid | dws_apl_tfc_ags.start_time | dws_apl_tfc_ags.end_time | dws_apl_tfc_ags.pv_cnts | dws_apl_tfc_ags.province | dws_apl_tfc_ags.city | dws_apl_tfc_ags.district | dws_apl_tfc_ags.devicetype | dws_apl_tfc_ags.osname | dws_apl_tfc_ags.osver | dws_apl_tfc_ags.release_ch | dws_apl_tfc_ags.promotion_ch | dws_apl_tfc_ags.dt |
+-----------------------+-------------------------------------------+-----------------------------+---------------------------+--------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
| -2140990168 | sid-a9f3a9f7-ca7f-48c1-b9d0-159bd8282854 | 1575522243000 | 1575633134000 | 33 | 未知 | 未知 | 未知 | HONGMI_8 | android | 12.0 | 百度手机助手 | 04 | 2020-01-13 |
| -2137329215 | sid-64b63cf0-4559-443c-988a-877f058aba6e | 1575522543000 | 1575543338000 | 10 | 未知 | 未知 | 未知 | MEIZU_ML7 | macos | 6.5 | 酷市场-CoolMar | 11 | 2020-01-13 |
| -2136134593 | sid-ef6dfff5-ea4e-48b9-94b1-5a85ed78f65c | 1575522483000 | 1575551424000 | 13 | 未知 | 未知 | 未知 | XIAOLAJIAO | macos | 7.0 | 安软市场 | 09 | 2020-01-13 |
| -2132783903 | sid-f9d230dd-8205-4ac9-990d-7d80598c0fba | 1575522303000 | 1575534501000 | 4 | 天津市 | 天津市 | 和平区 | ZX | macos | 10.05 | 最美应用 | 10 | 2020-01-13 |
| -2127774672 | sid-d0dcd37a-0d42-4ad0-bfd4-14f2bcaa31f5 | 1575522483000 | 1575557017000 | 18 | 未知 | 未知 | 未知 | MEIZU_ML7 | android | 9.0 | 纽扣助手 | 04 | 2020-01-13 |
| -2127756201 | sid-3d77dda0-2d9b-4685-ae75-bf65f2b539e0 | 1575522423000 | 1575594585000 | 27 | 未知 | 未知 | 未知 | IPHONE_6PLUS | windows | 7.0 | 金山手机助手 | 01 | 2020-01-13 |
| -2127613434 | sid-25bb13e6-f6bd-462d-ac18-4b2047ac5209 | 1575522183000 | 1575581048000 | 23 | 未知 | 未知 | 未知 | ZX | macos | 7.0 | 纽扣助手 | 04 | 2020-01-13 |
| -2124886435 | sid-c76cf576-e2f0-4ff1-b391-48b5209f2714 | 1575522183000 | 1575592278000 | 27 | 未知 | 未知 | 未知 | ZX | ios | 12.0 | 安卓市场 | 02 | 2020-01-13 |
| -2122725907 | sid-516cf6fd-ad8e-4620-b99e-05721d1e280f | 1575522423000 | 1575581362000 | 20 | 未知 | 未知 | 未知 | MI_MIX1 | macos | 10.05 | 华为应用市场 | 07 | 2020-01-13 |
| -2122376442 | sid-71164732-488d-4484-a0cf-c10d89ab40b7 | 1575522123000 | 1575547828000 | 13 | 未知 | 未知 | 未知 | SAMSUNG | macos | 10.0 | 最美应用 | 03 | 2020-01-13 |
+-----------------------+-------------------------------------------+-----------------------------+---------------------------+--------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
10.ADS_APL_TFC_OVW (流量概况报表)
整理所有流量的数据信息报表
+-------------------------+-------------------------+-------------------------+------------------------------+-----------------------------+---------------------------+---------------------------+----------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
| ads_apl_tfc_ovw.pv_amt | ads_apl_tfc_ovw.uv_amt | ads_apl_tfc_ovw.se_amt | ads_apl_tfc_ovw.time_avg_se | ads_apl_tfc_ovw.time_avg_u | ads_apl_tfc_ovw.se_avg_u | ads_apl_tfc_ovw.pv_avg_u | ads_apl_tfc_ovw.rbu_ratio | ads_apl_tfc_ovw.province | ads_apl_tfc_ovw.city | ads_apl_tfc_ovw.district | ads_apl_tfc_ovw.devicetype | ads_apl_tfc_ovw.osname | ads_apl_tfc_ovw.osver | ads_apl_tfc_ovw.release_ch | ads_apl_tfc_ovw.promotion_ch | ads_apl_tfc_ovw.dt |
+-------------------------+-------------------------+-------------------------+------------------------------+-----------------------------+---------------------------+---------------------------+----------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
| 4327 | 203 | 203 | 5.9686266009852216E7 | 5.9686266009852216E7 | 1.0 | 21.31527093596059 | 0.0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 29 | 1 | 1 | 9.7251E7 | 9.7251E7 | 1.0 | 29.0 | 0.0 | 上海市 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 6 | 1 | 1 | 2.8071E7 | 2.8071E7 | 1.0 | 6.0 | 0.0 | 云南省 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 14 | 1 | 1 | 4.4968E7 | 4.4968E7 | 1.0 | 14.0 | 0.0 | 内蒙古自治区 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 4 | 1 | 1 | 1.2198E7 | 1.2198E7 | 1.0 | 4.0 | 0.0 | 天津市 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 58 | 4 | 4 | 3.311025E7 | 3.311025E7 | 1.0 | 14.5 | 0.0 | 安徽省 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 64 | 2 | 2 | 9.722E7 | 9.722E7 | 1.0 | 32.0 | 0.0 | 山西省 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 33 | 1 | 1 | 1.02876E8 | 1.02876E8 | 1.0 | 33.0 | 0.0 | 广东省 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 35 | 1 | 1 | 6.8329E7 | 6.8329E7 | 1.0 | 35.0 | 0.0 | 广西壮族自治区 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
| 48 | 3 | 3 | 4.4646333333333336E7 | 4.4646333333333336E7 | 1.0 | 16.0 | 0.0 | 新疆维吾尔自治区 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2020-01-13 |
+-------------------------+-------------------------+-------------------------+------------------------------+-----------------------------+---------------------------+---------------------------+----------------------------+---------------------------+-----------------------+---------------------------+-----------------------------+-------------------------+------------------------+-----------------------------+-------------------------------+---------------------+
11.dws_apl_dau_rec(日活记录表)
一定要详细了解!
+-----------------------+---------------------------+-----------------------+---------------------------+-------------------------+-----------------------------+---------------------+
| dws_apl_dau_rec.guid | dws_apl_dau_rec.province | dws_apl_dau_rec.city | dws_apl_dau_rec.district | dws_apl_dau_rec.appver | dws_apl_dau_rec.devicetype | dws_apl_dau_rec.dt |
+-----------------------+---------------------------+-----------------------+---------------------------+-------------------------+-----------------------------+---------------------+
| -2140990168 | 未知 | 未知 | 未知 | 1.8.2 | HONGMI_8 | 2020-01-13 |
| -2137329215 | 未知 | 未知 | 未知 | 1.9.0 | MEIZU_ML7 | 2020-01-13 |
| -2136134593 | 未知 | 未知 | 未知 | 2.2.8 | XIAOLAJIAO | 2020-01-13 |
| -2132783903 | 天津市 | 天津市 | 和平区 | 2.1.9 | ZX | 2020-01-13 |
| -2127774672 | 未知 | 未知 | 未知 | 1.0.0 | MEIZU_ML7 | 2020-01-13 |
| -2127756201 | 未知 | 未知 | 未知 | 2.1.9 | IPHONE_6PLUS | 2020-01-13 |
| -2127613434 | 未知 | 未知 | 未知 | 2.2.8 | ZX | 2020-01-13 |
| -2124886435 | 未知 | 未知 | 未知 | 2.1.9 | ZX | 2020-01-13 |
| -2122725907 | 未知 | 未知 | 未知 | 1.9.0 | MI_MIX1 | 2020-01-13 |
| -2122376442 | 未知 | 未知 | 未知 | 1.9.0 | SAMSUNG | 2020-01-13 |
+-----------------------+---------------------------+-----------------------+---------------------------+-------------------------+-----------------------------+---------------------+
12.dws_apl_hsu_rec(历史访问记录表)
源表:dws_apl_dau_rec
+-----------------------+---------------------------+--------------------------+---------------------+
| dws_apl_hsu_rec.guid | dws_apl_hsu_rec.first_dt | dws_apl_hsu_rec.last_dt | dws_apl_hsu_rec.dt |
+-----------------------+---------------------------+--------------------------+---------------------+
| -1009327659 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1010908056 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1012058592 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1032089856 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1034343389 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1035253422 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1045196280 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1061257959 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1085879444 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
| -1091460989 | 2020-01-13 | 2020-01-13 | 2020-01-13 |
+-----------------------+---------------------------+--------------------------+---------------------+
13.dws_apl_dnu_rec(日新用户记录表)]
源表:dws_apl_dau_rec dws_apl_hsu_rec
+-----------------------+---------------------------+-----------------------+---------------------------+-------------------------+-----------------------------+---------------------+
| -2140990168 | 未知 | 未知 | 未知 | 1.8.2 | HONGMI_8 | 2020-01-13 |
| -2137329215 | 未知 | 未知 | 未知 | 1.9.0 | MEIZU_ML7 | 2020-01-13 |
| -2136134593 | 未知 | 未知 | 未知 | 2.2.8 | XIAOLAJIAO | 2020-01-13 |
| -2132783903 | 天津市 | 天津市 | 和平区 | 2.1.9 | ZX | 2020-01-13 |
| -2127774672 | 未知 | 未知 | 未知 | 1.0.0 | MEIZU_ML7 | 2020-01-13 |
| -2127756201 | 未知 | 未知 | 未知 | 2.1.9 | IPHONE_6PLUS | 2020-01-13 |
| -2127613434 | 未知 | 未知 | 未知 | 2.2.8 | ZX | 2020-01-13 |
| -2124886435 | 未知 | 未知 | 未知 | 2.1.9 | ZX | 2020-01-13 |
| -2122725907 | 未知 | 未知 | 未知 | 1.9.0 | MI_MIX1 | 2020-01-13 |
| -2122376442 | 未知 | 未知 | 未知 | 1.9.0 | SAMSUNG | 2020-01-13 |
+-----------------------+---------------------------+-----------------------+---------------------------+-------------------------+-----------------------------+---------------------+
14.ads_apl_dau_cube(日活多维分析报表)
源表:dws_apl_dau_rec
15.ads_apl_dnu_cube(日新多维分析报表)
源表:dws_apl_dnu_rec
16.ADS_APL_NRT_REC(新用户留存记录表)
源表:dws_apl_hsu_rec
17.ADS_APL_UFS_REC(活跃用户新鲜度报表)
源表: 新用户留存明细表 + 日活报表