data-export-formats

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Data Export Formats

数据导出格式

Use this skill when creating CSV exports for ad platforms or when you need to understand the project's data schemas.

当你需要为广告平台创建CSV导出文件，或需要了解项目的数据架构时，可以使用本技能。

Google Ads Export Formats

Google Ads导出格式

Enhanced Conversions CSV

增强转化CSV

For uploading offline conversion data to improve Smart Bidding.

Required columns:

Email,Phone,Conversion Name,Conversion Time,Conversion Value,Conversion Currency

Format requirements:

Email: SHA256 hash (32 hex chars, lowercase)
Phone: SHA256 hash (32 hex chars, lowercase)
Conversion Name: String matching your Google Ads conversion action
Conversion Time: ISO 8601 UTC format (
```
2024-11-15T14:32:00Z
```
)
Conversion Value: Numeric, no currency symbol
Conversion Currency: 3-letter code (
```
USD
```
,
```
EUR
```
, etc.)

Example:

csv

Email,Phone,Conversion Name,Conversion Time,Conversion Value,Conversion Currency
ed8e83c2f4cb7b9f43cdc75c148b0b09,628923b3c489bda7dd9ebba89cb5b46c,paid_subscription,2025-11-03T00:00:00Z,2388.0,USD
1bffb899c9248b28e37cda02dbd59444,0dd03c9dc463ab5efcd15f3343035ffa,paid_subscription,2025-10-31T00:00:00Z,2189.0,USD

Google Ads upload path:

Tools & Settings → Conversions → Upload conversions → Import

用于上传线下转化数据，优化智能出价策略。

必填列：

Email,Phone,Conversion Name,Conversion Time,Conversion Value,Conversion Currency

格式要求：

Email：SHA256哈希值（32位十六进制字符，小写）
Phone：SHA256哈希值（32位十六进制字符，小写）
Conversion Name：与Google Ads转化操作名称一致的字符串
Conversion Time：ISO 8601 UTC格式（
```
2024-11-15T14:32:00Z
```
）
Conversion Value：数值类型，不含货币符号
Conversion Currency：3位货币代码（
```
USD
```
,
```
EUR
```
等）

示例：

csv

Email,Phone,Conversion Name,Conversion Time,Conversion Value,Conversion Currency
ed8e83c2f4cb7b9f43cdc75c148b0b09,628923b3c489bda7dd9ebba89cb5b46c,paid_subscription,2025-11-03T00:00:00Z,2388.0,USD
1bffb899c9248b28e37cda02dbd59444,0dd03c9dc463ab5efcd15f3343035ffa,paid_subscription,2025-10-31T00:00:00Z,2189.0,USD

Google Ads上传路径：

工具与设置 → 转化 → 上传转化数据 → 导入

Customer Match CSV (Retargeting Audiences)

客户匹配CSV（再营销受众）

For uploading audience lists to Google Ads.

Minimal columns (email only):

Email

Extended columns (better match rate):

Email,Phone,First Name,Last Name,Country,Zip

Format requirements:

Email: SHA256 hash (32 hex chars, lowercase) OR plaintext (Google hashes it)
Phone: SHA256 hash OR E.164 format (
```
+14155551234
```
)
First Name / Last Name: Plaintext, lowercase, trimmed
Country: 2-letter ISO code (
```
US
```
,
```
GB
```
, etc.)
Zip: 5-digit US or local format

Example (hashed):

csv

Email,Phone
ed8e83c2f4cb7b9f43cdc75c148b0b09,628923b3c489bda7dd9ebba89cb5b46c
1bffb899c9248b28e37cda02dbd59444,0dd03c9dc463ab5efcd15f3343035ffa

Google Ads upload path:

Tools & Settings → Audience Manager → Customer Match → Email list

用于向Google Ads上传受众列表。

最简列（仅邮箱）：

Email

扩展列（匹配率更高）：

Email,Phone,First Name,Last Name,Country,Zip

格式要求：

Email：SHA256哈希值（32位十六进制字符，小写）或明文（Google会自动哈希）
Phone：SHA256哈希值或E.164格式（
```
+14155551234
```
）
First Name / Last Name：明文，小写，去除首尾空格
Country：2位ISO代码（
```
US
```
,
```
GB
```
等）
Zip：美国5位邮编或当地格式

示例（哈希版）：

csv

Email,Phone
ed8e83c2f4cb7b9f43cdc75c148b0b09,628923b3c489bda7dd9ebba89cb5b46c
1bffb899c9248b28e37cda02dbd59444,0dd03c9dc463ab5efcd15f3343035ffa

Google Ads上传路径：

工具与设置 → 受众管理器 → 客户匹配 → 邮箱列表

Meta Custom Audiences CSV

Meta自定义受众CSV

For uploading to Meta Ads Manager.

Columns:

email,phone,fn,ln,country,zip

Format requirements:

email: SHA256 hash (lowercase hex) OR plaintext lowercase
phone: Digits only, no formatting (
```
14155551234
```
)
fn / ln: Lowercase, trimmed
country: 2-letter ISO lowercase (
```
us
```
)
zip: 5-digit

Example:

csv

email,phone,fn,ln,country,zip
ed8e83c2f4cb7b9f43cdc75c148b0b09,14155551234,john,doe,us,94105

用于上传至Meta广告管理器。

列：

email,phone,fn,ln,country,zip

格式要求：

email：SHA256哈希值（小写十六进制）或小写明文
phone：仅数字，无格式符（
```
14155551234
```
）
fn / ln：小写，去除首尾空格
country：2位小写ISO代码（
```
us
```
）
zip：5位数字

示例：

csv

email,phone,fn,ln,country,zip
ed8e83c2f4cb7b9f43cdc75c148b0b09,14155551234,john,doe,us,94105

Project Data Schemas

项目数据架构

users.csv (~5,000 records)

users.csv（约5000条记录）

User master file with acquisition data.

Field	Type	Description	Example
user_id	UUID	Unique identifier	`208763df-9843-4c82-b4f1-bc6382a44acf`
email	String	SHA256 hash (32 chars)	`0f9f75d98cacdbd135ccbf18f1aa2e54`
phone	String	SHA256 hash (32 chars)	`eb18808fce984c7887799fe9e45f3d66`
signup_date	Date	Registration date	`2024-11-15`
traffic_source	String	Acquisition channel	`organic` , `paid_search` , `paid_social` , `direct` , `referral`
utm_source	String	UTM source	`google` , `facebook` , `linkedin`
utm_medium	String	UTM medium	`cpc` , `organic` , `social` , `referral`
utm_campaign	String	Campaign ID	`google_ads_q4` , `fb_retargeting`

包含获客数据的用户主文件。

字段	类型	描述	示例
user_id	UUID	唯一标识符	`208763df-9843-4c82-b4f1-bc6382a44acf`
email	String	SHA256哈希值（32位字符）	`0f9f75d98cacdbd135ccbf18f1aa2e54`
phone	String	SHA256哈希值（32位字符）	`eb18808fce984c7887799fe9e45f3d66`
signup_date	Date	注册日期	`2024-11-15`
traffic_source	String	获客渠道	`organic` , `paid_search` , `paid_social` , `direct` , `referral`
utm_source	String	UTM来源	`google` , `facebook` , `linkedin`
utm_medium	String	UTM媒介	`cpc` , `organic` , `social` , `referral`
utm_campaign	String	活动ID	`google_ads_q4` , `fb_retargeting`

events.csv (~57,000 records)

events.csv（约57000条记录）

Event stream with funnel progression.

Field	Type	Description	Example
event_id	UUID	Unique event ID	`22794184-df27-4329-86b1-acccd60b79b2`
user_id	UUID	FK to users	`a0505dd5-4327-4f86-82cc-bf39ba62c92e`
event_name	String	Event type	`page_view` , `pricing_view` , `checkout_start` , `form_submit` , `conversion`
page_url	String	Page path	`/` , `/pricing` , `/checkout` , `/success`
timestamp	DateTime	ISO 8601 UTC	`2024-11-15T14:32:00Z`
session_id	UUID	Groups session events	`80ea1bd9-d628-4ef6-bf26-64d928490205`
conversion_value	Numeric	USD (conversions only)	`150.00` or empty

Funnel stages (event_name values):

```
page_view
```
- Landing page visit
```
pricing_view
```
- Viewed pricing page
```
checkout_start
```
- Started checkout
```
form_submit
```
- Submitted form
```
conversion
```
- Completed purchase

包含漏斗转化流程的事件流数据。

字段	类型	描述	示例
event_id	UUID	唯一事件ID	`22794184-df27-4329-86b1-acccd60b79b2`
user_id	UUID	关联users表的外键	`a0505dd5-4327-4f86-82cc-bf39ba62c92e`
event_name	String	事件类型	`page_view` , `pricing_view` , `checkout_start` , `form_submit` , `conversion`
page_url	String	页面路径	`/` , `/pricing` , `/checkout` , `/success`
timestamp	DateTime	ISO 8601 UTC格式	`2024-11-15T14:32:00Z`
session_id	UUID	会话事件分组标识	`80ea1bd9-d628-4ef6-bf26-64d928490205`
conversion_value	Numeric	美元金额（仅转化事件）	`150.00` 或空值

漏斗阶段（event_name取值）：

```
page_view
```
- 着陆页访问
```
pricing_view
```
- 查看定价页
```
checkout_start
```
- 开始结账
```
form_submit
```
- 提交表单
```
conversion
```
- 完成购买

daily_metrics.csv (~60 records)

daily_metrics.csv（约60条记录）

Daily aggregated metrics with engineered anomalies.

Field	Type	Description
date	Date	Metric date
sessions	Integer	Daily sessions
users	Integer	Unique users
conversions	Integer	Daily conversions
revenue	Numeric	Daily revenue (USD)
conversion_rate	Numeric	Conversions / users
avg_order_value	Numeric	Revenue / conversions

Engineered anomalies:

Nov 15: -63% sessions (signup flow bug)
Nov 21: +99% conversions (onboarding improvement)
Nov 28-30: -72% conversion rate (activation issue)

包含预设异常的每日聚合指标。

字段	类型	描述
date	Date	指标日期
sessions	Integer	每日会话数
users	Integer	独立用户数
conversions	Integer	每日转化数
revenue	Numeric	每日收入（美元）
conversion_rate	Numeric	转化率（转化数/用户数）
avg_order_value	Numeric	客单价（收入/转化数）

预设异常：

11月15日：会话数下降63%（注册流程bug）
11月21日：转化数上升99%（新用户引导优化）
11月28-30日：转化率下降72%（激活环节问题）

trial_users.csv (~500 records)

trial_users.csv（约500条记录）

Trial user conversion data (Demo 4).

Field	Type	Description
user_id	UUID	Unique identifier
signup_date	Date	Trial start date
plan_type	String	Always `free_trial`
converted	Boolean	Whether converted to paid
conversion_date	Date	When converted (nullable)
days_to_convert	Integer	Days from signup to conversion

试用用户转化数据（Demo 4）。

字段	类型	描述
user_id	UUID	唯一标识符
signup_date	Date	试用开始日期
plan_type	String	固定为 `free_trial`
converted	Boolean	是否转化为付费用户
conversion_date	Date	转化日期（可为空）
days_to_convert	Integer	从注册到转化的天数

feature_usage.csv (~2,500 records)

feature_usage.csv（约2500条记录）

Feature adoption events (Demo 4).

Field	Type	Description
user_id	UUID	FK to trial_users
feature_name	String	Feature used
first_used_date	Date	First usage date
usage_count	Integer	Total uses
days_since_signup_first_use	Integer	Days from signup to first use

Key features:

```
create_form_onboarding
```
- Created first form
```
publish_form
```
- Published a form
```
embed_form
```
- Embedded form on site
```
configure_integration
```
- Set up integration (aha moment)
```
view_analytics
```
- Viewed form analytics

Aha moment pattern: Users who

configure_integration

within 3 days convert at 70% vs 19% baseline (3.7x lift).

功能使用事件数据（Demo 4）。

字段	类型	描述
user_id	UUID	关联trial_users表的外键
feature_name	String	使用的功能
first_used_date	Date	首次使用日期
usage_count	Integer	总使用次数
days_since_signup_first_use	Integer	从注册到首次使用的天数

核心功能：

```
create_form_onboarding
```
- 创建首个表单
```
publish_form
```
- 发布表单
```
embed_form
```
- 在网站嵌入表单
```
configure_integration
```
- 设置集成（Aha moment）
```
view_analytics
```
- 查看表单分析数据

Aha moment规律： 在3天内完成

configure_integration

的用户转化率为70%，而基准转化率为19%（提升3.7倍）。

utm_data.csv (~12 records)

utm_data.csv（约12条记录）

Campaign UTM data with intentional inconsistencies (Demo 5).

Field	Type	Description
url	String	Landing page URL
utm_source	String	Source parameter
utm_medium	String	Medium parameter
utm_campaign	String	Campaign parameter
session_count	Integer	Sessions with this UTM

Engineered issues:

Source fragmentation:
```
linkedin
```
vs
```
LinkedIn
```
vs
```
LINKEDIN
```
Typos:
```
product_upd_dec
```
instead of
```
product_update_dec
```

包含故意不一致的活动UTM数据（Demo 5）。

字段	类型	描述
url	String	着陆页URL
utm_source	String	UTM来源参数
utm_medium	String	UTM媒介参数
utm_campaign	String	UTM活动参数
session_count	Integer	该UTM对应的会话数

预设问题：

来源碎片化：
```
linkedin
```
、
```
LinkedIn
```
、
```
LINKEDIN
```
并存
拼写错误：
```
product_upd_dec
```
应为
```
product_update_dec
```

Common Export Patterns

常见导出模板

High-Value Converters (Enhanced Conversions)

高价值转化用户（增强转化）

sql

SELECT
  u.email AS Email,
  u.phone AS Phone,
  'paid_subscription' AS "Conversion Name",
  e.timestamp AS "Conversion Time",
  e.conversion_value AS "Conversion Value",
  'USD' AS "Conversion Currency"
FROM users u
JOIN events e ON u.user_id = e.user_id
WHERE e.event_name = 'conversion'
  AND e.conversion_value > 100
ORDER BY e.conversion_value DESC

sql

SELECT
  u.email AS Email,
  u.phone AS Phone,
  'paid_subscription' AS "Conversion Name",
  e.timestamp AS "Conversion Time",
  e.conversion_value AS "Conversion Value",
  'USD' AS "Conversion Currency"
FROM users u
JOIN events e ON u.user_id = e.user_id
WHERE e.event_name = 'conversion'
  AND e.conversion_value > 100
ORDER BY e.conversion_value DESC

Retargeting Audience (Customer Match)

再营销受众（客户匹配）

sql

WITH pricing_views AS (
  SELECT user_id, COUNT(*) as view_count
  FROM events
  WHERE event_name = 'pricing_view'
  GROUP BY user_id
  HAVING COUNT(*) >= 2
),
checkout_starters AS (
  SELECT DISTINCT user_id FROM events WHERE event_name = 'checkout_start'
),
converters AS (
  SELECT DISTINCT user_id FROM events WHERE event_name = 'conversion'
)
SELECT u.email AS Email, u.phone AS Phone
FROM users u
JOIN pricing_views pv ON u.user_id = pv.user_id
JOIN checkout_starters cs ON u.user_id = cs.user_id
LEFT JOIN converters c ON u.user_id = c.user_id
WHERE c.user_id IS NULL

sql

WITH pricing_views AS (
  SELECT user_id, COUNT(*) as view_count
  FROM events
  WHERE event_name = 'pricing_view'
  GROUP BY user_id
  HAVING COUNT(*) >= 2
),
checkout_starters AS (
  SELECT DISTINCT user_id FROM events WHERE event_name = 'checkout_start'
),
converters AS (
  SELECT DISTINCT user_id FROM events WHERE event_name = 'conversion'
)
SELECT u.email AS Email, u.phone AS Phone
FROM users u
JOIN pricing_views pv ON u.user_id = pv.user_id
JOIN checkout_starters cs ON u.user_id = cs.user_id
LEFT JOIN converters c ON u.user_id = c.user_id
WHERE c.user_id IS NULL

File Locations

文件位置

File	Location	Records
users.csv	`data/users.csv`	~5,000
events.csv	`data/events.csv`	~57,000
daily_metrics.csv	`data/daily_metrics.csv`	~60
trial_users.csv	`data/trial_users.csv`	~500
feature_usage.csv	`data/feature_usage.csv`	~2,500
utm_data.csv	`data/utm_data.csv`	~12

文件	路径	记录数
users.csv	`data/users.csv`	约5000
events.csv	`data/events.csv`	约57000
daily_metrics.csv	`data/daily_metrics.csv`	约60
trial_users.csv	`data/trial_users.csv`	约500
feature_usage.csv	`data/feature_usage.csv`	约2500
utm_data.csv	`data/utm_data.csv`	约12

BigQuery Location

BigQuery位置

Project:

agents-webinar-2025

Dataset:

webinar_demos

Tables:

users

events

daily_metrics

trial_users

feature_usage

项目：

agents-webinar-2025

数据集：

webinar_demos

表：

users

events

daily_metrics

trial_users

feature_usage