如何分析/显示原始网络分析数据？

发布于 2024-08-16 17:44:14 字数 424 浏览 11 评论 0原文

我创建了一个 Web 跟踪系统，只需将事件信息（点击或页面视图）插入到一个简单的 SQL Server 表中：

 Column    |  Type       | NULL?
-------------------------------------
RequestId  | bigint      | NOT NULL
PagePath   | varchar(50) | NOT NULL
EventName  | varchar(50) | NULL
Label      | varchar(50) | NULL
Value      | float       | NULL
UserId     | int         | NOT NULL
LoggedDate | datetime    | NOT NULL

如何获取/分析/显示此原始信息？

原文

I've created a web tracking system that simply insert an event information (click or page view) into a simple SQL server table:

 Column    |  Type       | NULL?
-------------------------------------
RequestId  | bigint      | NOT NULL
PagePath   | varchar(50) | NOT NULL
EventName  | varchar(50) | NULL
Label      | varchar(50) | NULL
Value      | float       | NULL
UserId     | int         | NOT NULL
LoggedDate | datetime    | NOT NULL

How can I harvest/analayze/display this raw information?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷离° 2024-08-23 17:44:15

首先确定您最感兴趣的趋势。也许查看一些现有的网络分析软件（有免费软件可用）来看看存在哪些选项。

如果您的需求很简单，那么您就有足够的数据。如果您想详细了解哪些国家/地区正在访问您的网站，您需要记录 IP 地址并获取将 IP 范围与国家/地区联系起来的数据库 - 这些并不是 100% 可靠，但会给您带来相当好的准确性。

您可以使用当前数据进行一些简单的报告示例：

每小时、每天、每周、每月的点击次数
前 20 个访问页面
热门用户
访问网站的用户数量
每小时、每天、每周、每月等

。可以使用 group by 子句和日期函数通过单个 SQL 查询进行拉取。

实现每日点击次数的 MS SQL Server 查询示例（未经测试）：

SELECT COUNT(RequestID) AS NumberOfHits, 
  YEAR(LoggedDate) AS EventYear, 
  MONTH(LoggedDate) AS EventMonth, 
  DAY(LoggedDate) AS EventDay
FROM MyTable
GROUP BY YEAR(LoggedDate), MONTH(LoggedDate), DAY(LoggedDate)
ORDER BY YEAR(LoggedDate), MONTH(LoggedDate), DAY(LoggedDate)

First decide what trends you are most interested in. Perhaps looking at some existing web analytics software - there is free software available - to see what options exist.

If your requirements are simple, you have enough data. If you want a breakdown of which countries are accessing your website, you need to log IP addresses and get a database that ties IP ranges to countries - these are not 100% reliable but will get you fairly good accuracy.

Some simple examples of reporting you can do with your current data:

Number of hits per hour, day, week, month
Top 20 accessed pages
Top Users
Number of users accessing the site per hour, day, week, month
etc.

Most of these you can pull with a single SQL query using the group by clause and date functions.

Example MS SQL Server query to achieve hits per day (untested):

SELECT COUNT(RequestID) AS NumberOfHits, 
  YEAR(LoggedDate) AS EventYear, 
  MONTH(LoggedDate) AS EventMonth, 
  DAY(LoggedDate) AS EventDay
FROM MyTable
GROUP BY YEAR(LoggedDate), MONTH(LoggedDate), DAY(LoggedDate)
ORDER BY YEAR(LoggedDate), MONTH(LoggedDate), DAY(LoggedDate)

回复收藏 0 原文