googleads.g.doubleclick.net
参数 | 说明 | 描述 |
---|---|---|
广告系列来源 (utm_source) | 必填 | 使用 utm_source 来标识 搜索 引擎、简报名称或其他来源。 示例:utm_source=google |
广告系列媒介 (utm_medium) | 必填 | 使用 utm_medium 来标识 电子邮件 或每次点击 费用等媒介。 示例:utm_medium=cpc |
广告系列字词 (utm_term) | 用于付费搜索 | 使用 utm_term 来注明此广告的关键字。 示例: utm_term=running+shoes |
广告系列内容 (utm_content) | 用于A/B测试和以网页内容定位的 广告 | 使用 utm_content 区分指向同一网址的 广告或链接。 示例:utm_content=logolink 或 utm_content=textlink |
广告系列名称 (utm_campaign) | 必填,用于关键字分析 | 使用 utm_campaign 来标识特定的产品促销活动或战略性广告系列。 示例:utm_campaign=spring_sale |
Step1: 进入页统计
a. 取会话的第一条记录
b. 分析referer
utm_source:
1. referer中存在utm_source,使用utm_source 2. 否则,使用域名http://[utm_source]/
utm_medium:
1. referer中存在utm_medium,使用utm_medium(cpc/ppc/cpa/email/...) 2. 否则,根据域名匹配organic/referal/direct/...
Code:
SELECT * FROM access_log WHERE created_date = '2014-01-26' ORDER BY site, session_id, created_at; $pre_site = NULL; $pre_session_id = NULL; foreach ($result as $row) { if ((!$pre_site && !$pre_session_id) || $pre_site != $row['site'] || $pre_session_id != $row['session_id']) { $pre_site = $row['site']; $pre_session_id = $row['session_id']; //utm_source & utm_medium $ret[] = $row; } }
Step2: 统计
Visits:
SELECT utm_source, COUNT(*) FROM entry_page WHERE site='fashion.tinydeal.com' AND create_at = '2014-01-28' GROUP BY utm_source
Bounce Rate:
bounce visits:
SELECT source, session_id FROM entry_page LEFT JOIN bounce_visits USING(session_id);
total visits:
SELECT COUNT(DISTINCT session_id) FROM entry_page WHERE site = 'fashion.tinydeal.com' and source = '{$source}'
Order:
SELECT COUNT(*), SUM(product_total_price) FROM entry_page LEFT JOIN order USING(session_id)
需要解决的问题:
1. 分页
2. 排序
hbase?
流量 | 行为 | 转化 | ||||
访问来源(Source) | 渠道(Channels) | 媒介(Medium) | 访问次数 | 跳出率 | 订单数量 | 订单金额 |
直接流量(direct) | Direct | (none) | ||||
推介网站(other) | Referal | referal | ||||
搜索引擎(search) | 自然搜索(Organic Search) | organic | ||||
搜索引擎(search) | 付费搜索(Paid Search) | 付费推广(ppc) | ||||
付费推广(cpc) | ||||||
付费推广(cpa) | ||||||
广告链接? | 广告(Other Advertising) | |||||
邮件推广(Email) | ||||||
社交网络(Social) | ||||||
bing | cpa | |||||
yandex | ppc | |||||
newsletter | ||||||
Step1:进入页统计 | ||||||
site | ||||||
type | ||||||
ip | ||||||
agent | ||||||
url | ||||||
referer | ||||||
uid | ||||||
session_id | ||||||
created_at | ||||||
source | ||||||
utm_source | ||||||
utm_medium | ||||||
utm_term | ||||||
utm_content | ||||||
utm_campaign | ||||||
Step2:访问来源统计 | ||||||
site | ||||||
source | 来源 | |||||
channel | 渠道(Channels) | |||||
medium | 媒介 | |||||
visits | 访问次数 | |||||
bounce_rate | 跳出率 | |||||
order_count | 订单数量 | |||||
order_total_price | 订单金额 | |||||
created_at | ||||||