📊 Shopify Sessions 表完整字段参考

一、指标 (Metrics) – 可直接 SHOW

这些字段会自动聚合,不需要 GROUP BY:

二、维度 (Dimensions) – 需要 GROUP BY

📅 时间维度

🌍 地理位置维度

📱 设备维度

🔗 流量来源维度

📊 UTM 营销参数

📄 页面维度

🔍 搜索相关维度

✅ 基本语法结构

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]

📋 关键规则

1. 必需关键字: FROM 和 SHOW 是必需的
2. 关键字顺序: 必须按上述顺序排列
3. 指标 vs 维度:
   • 指标0 可以直接 SHOW,会自动聚合
   • 维度
     • 必须在nullGROUP BY 中声明null4. 时间序列: 使用 TIMESERIES day/week/month 自动填充日期
4. 过滤条件: WHERE 用于过滤,值必须用单引号 '
5. 聚合后过滤: 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

五、常见错误及修正

1. ✅ 过滤机器人流量: 始终使用 WHERE human_or_bot_session = 'human'
2. ✅ 使用 TIMESERIES: 自动填充缺失日期,避免数据断层
3. ✅ 限制结果数量: 使用 LIMIT 避免查询超时
4. ✅ 合理使用 GROUP BY: 只在需要分组时使用维度
5. ✅null注意字段前缀: Sessions 表的地理和设备字段都有 session_ 前缀
6. ✅ 使用相对日期: -30d, today, startOfDay(-7d) 等

这份参考指南应该能帮助您正确构建 Sessions 查询!如果有任何疑问,请随时告诉我。null