FROM table_name
SHOW metric1, metric2, ...
[WHERE condition]
[GROUP BY dimension1, dimension2, ...]
[TIMESERIES time_dimension]
[WITH TOTALS, GROUP_TOTALS, PERCENT_CHANGE]
[HAVING condition]
[SINCE date_offset]
[UNTIL date_offset]
[COMPARE TO date_offset]
[ORDER BY column ASC|DESC]
[LIMIT number]
[VISUALIZE column TYPE visualization_type]
📋 关键规则
必需关键字: FROM 和 SHOW 是必需的
关键字顺序: 必须按上述顺序排列
指标 vs 维度:
指标0 可以直接 SHOW,会自动聚合
维度
必须在nullGROUP BY 中声明null4. 时间序列: 使用 TIMESERIES day/week/month 自动填充日期
过滤条件: WHERE 用于过滤,值必须用单引号 '
聚合后过滤: HAVING 用于过滤聚合后的结果null## 四、常用查询示例
1️⃣ 基础时间序列查询
FROM sessions
SHOW sessions, online_store_visitors
WHERE human_or_bot_session = 'human'
TIMESERIES day
SINCE -30d
UNTIL today
ORDER BY day ASC
LIMIT 1000
2️⃣ 按来源分组
FROM sessions
SHOW sessions, online_store_visitors, referrer_source
GROUP BY referrer_source
WHERE human_or_bot_session = 'human'
SINCE -30d
UNTIL today
ORDER BY sessions DESC
LIMIT 100
null### 3️⃣ 按设备类型和国家分组
FROM sessions
SHOW sessions, nullonline_store_visitors,
session_device_type,
session_country
GROUP BY session_device_type, session_country
WHERE human_or_bot_session = 'human'
SINCE -30d
UNTIL today
ORDER BY sessions DESC
LIMIT 100
null### 4️⃣ 转化漏斗分析
FROM sessions
SHOW sessions,
sessions_with_cart_additions,
sessions_that_reached_checkout,
sessions_that_completed_checkout,
conversion_rate
WHERE human_or_bot_session = 'human'
TIMESERIES day
SINCE -30d
UNTIL today
ORDER BY day ASC
LIMIT 1000
5️⃣ UTM 营销分析
FROM sessions
SHOW sessions,
online_store_visitors,
utm_source,
utm_medium,
utm_campaign
GROUP BY utm_source, utm_medium, utm_campaign
WHERE utm_source IS NOT NULL
SINCE -30d
UNTIL today
ORDER BY sessions DESC
LIMIT 100
6️⃣ 多维度时间序列
FROM sessions
SHOW sessions,
day,
referrer_source,
session_device_type
GROUP BY day, referrer_source, session_device_type
WHERE human_or_bot_session = 'human'
TIMESERIES day
SINCE -7d
UNTIL today
ORDER BY day ASC, sessions DESC
LIMIT 1000
7️⃣ 带同比的查询
FROM sessions
SHOW sessions, online_store_visitors
WHERE human_or_bot_session = 'human'
TIMESERIES day
WITH TOTALS, PERCENT_CHANGE
SINCE startOfDay(-7d)
UNTIL today
COMPARE TO previous_period
ORDER BY day ASC
LIMIT 1000
五、常见错误及修正
null❌ 错误写法
✅ 正确写法
说明
SHOW timestamp
TIMESERIES day
使用时间序列而非时间戳
SHOW location_country
SHOW session_country
字段名前缀是nullsession_
SHOW device_type
SHOW session_device_type
设备字段需要 session_ 前缀
SHOW referrer_source (无 GROUP BY)
GROUP BY referrer_source
维度必须在 GROUP BY 中
WHERE is_bot = true
WHERE human_or_bot_session = 'bot'
使用正确的字段名和值
SINCE "2026-01-20"
SINCE -30d 或 SINCE startOfDay(-30d)
使用相对日期
null## 六、最佳实践
✅ 过滤机器人流量: 始终使用 WHERE human_or_bot_session = 'human'
✅ 使用 TIMESERIES: 自动填充缺失日期,避免数据断层
✅ 限制结果数量: 使用 LIMIT 避免查询超时
✅ 合理使用 GROUP BY: 只在需要分组时使用维度
✅null注意字段前缀: Sessions 表的地理和设备字段都有 session_ 前缀
✅ 使用相对日期: -30d, today, startOfDay(-7d) 等
这份参考指南应该能帮助您正确构建 Sessions 查询!如果有任何疑问,请随时告诉我。null
❓ 延伸问答与排错指南(FAQ)
Shopify Sessions 表里哪些字段能直接 SHOW?
文中列出的指标类字段可直接 SHOW,系统会自动聚合,不需要 GROUP BY。比如 sessions、online_store_visitors、pageviews、average_session_duration、bounce_rate、conversion_rate,以及加购、到达结账、完成结账等转化指标都属于这一类。
为什么维度字段查询时要加 GROUP BY?
因为维度字段用于切分数据,不加 GROUP BY 往往只会得到汇总结果或报错。像 day、session_country、session_device_type、utm_source、page_path 这类字段,通常要和指标一起分组,才能按时间、地区、设备或来源看趋势。