Customer Journey Analytics BI 扩展
创建对象:
- 管理员
通过 Customer Journey Analytics BI extension,SQL 可访问您在 Customer Journey Analytics 中定义的数据视图。您的数据工程师和分析师可能更熟悉 Power BI、Tableau Desktop 或其他商业智能和可视化工具(也称为 BI 工具)。他们现在可以基于 Customer Journey Analytics 用户在创建 Analysis Workspace 项目时使用的相同数据视图来创建报告和仪表板。
Adobe Experience Platform Query Service 是 Experience Platform 数据湖中可用数据的 SQL 接口。启用 Customer Journey Analytics BI extension 后,Query Service的功能将得到扩展,以便在Query Service会话中以表或视图的形式查看 Customer Journey Analytics 数据视图。因此,将Query Service用作其 PostgresSQL 接口的商业智能工具可以无缝地从这项扩展的功能中受益。
主要好处是:
- 无需在 BI 工具中重新创建 Customer Journey Analytics 数据视图的等效表示形式。
有关数据视图功能的更多信息,请参阅数据视图,从而了解必须重新创建的内容。 - BI 工具和 Customer Journey Analytics 之间的报告和分析具有更高的一致性。
- 将 Customer Journey Analytics 数据与 BI 工具中已有的其他数据源结合起来。
先决条件
要使用此功能,您可以使用过期型或非过期型的凭据将 BI 工具连接到 Customer Journey Analytics BI extension。凭据指南提供了有关如何设置过期型凭据或不过期型凭据的更多信息。
以下是设置 CJA 权限的其他步骤
过期型凭据
要使用过期型凭据,您可以:
- 授予对 Experience Platform 和 Customer Journey Analytics 的访问权限。
- 授予产品管理员对 Customer Journey Analytics 的访问权限,以便您可以查看、编辑、更新或删除连接和数据视图。
或者您可以:
- 授予对您想要访问的数据视图的访问权限。
- 授予对 Customer Journey Analytics BI 扩展的访问权限。
非过期型凭据
要使用非过期型凭据:
- 在 Experience Platform 中创建一个非过期型凭据。
- 按照过期型凭据中提及的步骤授予对非过期型凭据的访问权限。
请参阅 Customer Journey 访问控制,以了解更多信息,特别是产品管理员附加权限和 Admin Console 中的 Customer Journey Analytics 权限。
使用情况
要使用 Customer Journey Analytics BI extension 功能,您可以直接使用 SQL,也可以使用特定 BI 工具中提供的拖放体验。
SQL
您可以通过查询编辑器或标准 PostgresSQL 命令行界面 (CLI) 客户端直接在 SQL 语句中使用此功能。
在 Adobe Experience Platform 中:
-
在左边栏中的 数据管理 中,选择 查询。
-
选择
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
要执行查询,请键入 SQL 语句并选择
[SHIFT]
+[ENTER]
)。
-
在 Adobe Experience Platform 中,找到并复制您的 PostgresSQL 凭据:
-
从左边栏中(数据管理 下),选择 查询。
-
从顶部栏中选择 凭据。
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
若要复制命令字符串,请使用 PSQL 命令 部分中的
-
-
打开命令或终端窗口。
-
要登录并开始执行查询,请将命令字符串粘贴到终端中。
有关更多信息,请参阅查询编辑器 UI 指南。
BI 工具
目前,对于下列工具,Customer Journey Analytics BI extension 已得到支持和测试。其他使用 PSQL 接口的 BI 工具也能工作,但尚未获得正式支持。
-
在 Adobe Experience Platform 中,查找 PostgresSQL 凭据的详细信息:
-
从左边栏中(数据管理 下),选择 查询。
-
从顶部栏中选择 凭据。
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
如果需要,使用
-
-
在 Power BI 中:
-
在主窗口中,从顶部工具栏中选择 获取数据。
-
选择左边栏中的 更多…。
-
在 获取数据 屏幕中,搜索
PostgresSQL
,并从列表中选择 PostgresSQL 数据库。 -
在 PostgressSQL 数据库 对话框中:
-
将 Experience Platform 查询凭据中的 主机 参数粘贴到 服务器 文本字段中。
-
将 Experience Platform 查询凭据中的 数据库 参数粘贴到 数据库 文本字段中。
将
?FLATTEN
添加到 数据库 参数,例如,使其显示内容为prod:cja?FLATTEN
。有关更多信息,请参阅扁平化处理嵌套数据结构以用于第三方 BI 工具。 -
当提示输入 数据连接 模式时,请选择 DirectQuery。
-
系统会提示您输入 用户名 和 密码。使用 Experience Platform 查询凭据中的等效参数。
-
-
成功登录后,Customer Journey Analytics 数据视图表将显示在 Power BI 的 导航器 中。
-
选择要使用的数据视图表,然后选择 加载。
与一个或多个选定表关联的所有维度和量度都显示在右窗格中,可供您在可视化图表中使用。
有关更多信息,请参阅将 Power BI 连接到 Query Service。有关详细示例,另参见 BI 扩展用例。
-
-
在 Adobe Experience Platform 中,查找 PostgresSQL 凭据的详细信息:
-
从左边栏中(数据管理 下),选择 查询。
-
从顶部栏中选择 凭据。
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
如果需要,使用
-
-
在 Tableau Desktop 中:
-
从左边栏中的 至服务器 中选择 更多。
-
从列表中选择 PostgresSQL 。
-
在 PostgresSQL 对话框中:
-
将 Experience Platform 查询凭据中的 主机 参数粘贴到 服务器 文本字段中。
-
将 Experience Platform 查询凭据中的 端口 参数粘贴到 端口 文本字段中。
-
将 Experience Platform 查询凭据中的 数据库 参数粘贴到 数据库 文本字段中。
将
%3FFLATTEN
添加到 数据库 参数,例如,使其显示内容为prod:cja%3FFLATTEN
。有关更多信息,请参阅扁平化处理嵌套数据结构以用于第三方 BI 工具。 -
从 身份验证 列表中选择 用户名和密码。
-
将 Experience Platform 查询凭据中的 用户名 参数粘贴到 用户名 文本字段中。
-
将 Experience Platform 查询凭据中的 密码 参数粘贴到 密码 文本字段中。
-
选择 登录。
-
-
Customer Journey Analytics 数据视图显示为 表 列表中的表。
-
将要使用的表拖动到画布上。
您现在可以使用数据视图表中的数据来构建报告和可视化图表。
有关更多信息,请参阅将 Tableau 连接到 Query Service。有关详细示例,另参见 BI 扩展用例。
-
-
在 Adobe Experience Platform 中,查找 PostgresSQL 凭据的详细信息:
-
从左边栏中(数据管理 下),选择 查询。
-
从顶部栏中选择 凭据。
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
如果需要,使用
-
-
在 Looker 中:
- 从左侧边栏中选择 管理员。
- 选择 连接。
- 选择 添加连接。
- 在 将数据库连接到 Looker 屏幕上,在设置新连接时粘贴相应的值。确保选择 PostgreSQL 9.5+ 作为方言。
- 选择 测试 来测试您的连接。
- 成功后,选择 更新 来保存您的连接。
您现在可以使用数据视图表中的数据来构建报告和可视化图表。
有关更多信息,请参阅将 Looker 连接到查询服务。有关详细示例,另参见 BI 扩展用例。
-
在 Adobe Experience Platform 中,查找 PostgresSQL 凭据的详细信息:
-
从左边栏中(数据管理 下),选择 查询。
-
从顶部栏中选择 凭据。
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
如果需要,使用
-
-
在 Jupyter Notebook 中:
- 确保您使用所需的库。
- 在建立和执行连接时使用相应的值。
- 通过执行一项相关的查询来测试您的连接。
成功后,您可以使用数据来构建报告和可视化图表。
有关更多信息,请参阅将 Jupyter Notebook 连接到查询服务。有关详细示例,另参见 BI 扩展用例。
-
在 Adobe Experience Platform 中,查找 PostgresSQL 凭据的详细信息:
-
从左边栏中(数据管理 下),选择 查询。
-
从顶部栏中选择 凭据。
-
从 数据库 下拉菜单的数据库列表中选择沙盒的
cja
数据库。 例如:prod:cja
。 -
如果需要,使用
-
-
在 RStudio 中:
- 确保您使用所需的库。
- 在建立和执行连接时使用相应的值。
- 通过执行一项相关的查询来测试您的连接。
成功后,您可以使用数据来构建报告和可视化图表。
有关更多信息,请参阅将 RStudio 连接到查询服务。有关详细示例(该示例使用的是 RPostgres 包),另请参见 BI 扩展用例。
请参阅将客户端连接到查询服务,以获取可用工具的概述和更多信息。
请参阅使用案例,了解如何使用 Customer Journey Analytics BI 扩展来完成多个用例。
功能
默认情况下,您的数据视图具有从其友好名称生成的表安全名称。例如,名为我的 Web 数据视图的数据视图的视图名称为 my_web_data_view
。您可以定义一个在 BI 工具中用于数据视图的首选名称。请参阅数据视图设置,以了解更多信息。
如果要使用数据视图 ID 作为表名称,可以在连接时将可选的 CJA_USE_IDS
设置添加到数据库名称。例如,prod:cja?CJA_USE_IDS
显示具有 dv_ABC123
等名称的数据视图。
数据管理
Customer Journey Analytics 中与数据管理相关的设置继承自 Adobe Experience Platform。Customer Journey Analytics 和 Adobe Experience Platform 数据管理之间的集成允许标记敏感的 Customer Journey Analytics 数据和实施隐私政策。
在 Experience Platform 使用的数据集上创建的隐私标签和政策可以在 Customer Journey Analytics 数据视图工作流中显示。因此,在未遵循定义的隐私标签和政策时,使用 Customer Journey Analytics BI extension 查询的数据会显示相应的警告或错误。
列出数据视图
在标准 PostgreSQL CLI 中,可以使用 \dv
列出视图
prod:all=> \dv
List of relations
Schema | Name | Type | Owner
--------+--------------------------------------------+------+----------
public | my_web_data_view | view | postgres
public | my_mobile_data_view | view | postgres
嵌套与扁平化处理
默认情况下,数据视图架构使用嵌套结构,就像原始 XDM 架构一样。该集成还支持 FLATTEN
选项。您可以使用此选项实施要扁平化处理的数据视图(以及会话中的任何其他表)的架构。通过扁平化处理,可以更轻松地在不支持结构化架构的 BI 工具中使用。有关更多信息,请参阅在 Query Service 中使用嵌套数据结构。
默认设置和限制
以下附加默认值和限制在使用 BI 扩展时适用:
-
BI 扩展要求对查询结果的行数进行限制。默认值为 50,但您可以在 SQL 中使用
LIMIT n
覆盖此值,其中n
为 1 - 50000。 -
BI 扩展需要一个日期范围来限制用于计算的行数。其默认值为最近 30 天,但您可以使用特殊的
WHERE
或timestamp
列在 SQLdaterange
子句中覆盖它。 -
BI 扩展需要聚合查询。您不能使用类似于
SELECT * FROM ...
的 SQL 来获取原始的底层行。从宏观层面来看,您的聚合查询应使用:-
使用
SUM
和/或COUNT
选择总数。
例如,SELECT SUM(metric1), COUNT(*) FROM ...
-
选择按维度细分的量度。
例如,SELECT dimension1, SUM(metric1), COUNT(*) FROM ... GROUP BY dimension1
-
选择不同的度量值。
例如,SELECT DISTINCT dimension1 FROM ...
参阅有关支持的 SQL 的更多信息。
-
SELECT * FROM dv1 WHERE 1=0
SELECT dim1, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim1
SELECT dim1, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02' AND
filterId = '12345'
GROUP BY dim1
SELECT dim1, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02' AND
AND (dim2 = 'A' OR dim3 IN ('X', 'Y', 'Z'))
GROUP BY dim1
HAVING
子句SELECT dim1, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim1
HAVING m1 > 100
SELECT DISTINCT dim1 FROM dv1
SELECT dim1 AS dv1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim1
SELECT dim1 AS dv1
FROM dv1
WHERE `timestamp` >= '2022-01-01' AND `timestamp` < '2022-01-02'
GROUP BY dim1
ORDER BY SUM(metric1)
LIMIT 15
SELECT SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
SELECT dim1, dim2, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim1, dim2
SELECT dim1, dim2, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY 1, 2
ORDER BY 1, 2
SELECT DISTINCT dim1, dim2
FROM dv1
SELECT dim1, m1
FROM (
SELECT dim1, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim1
)
WHERE dim1 in ('A', 'B')
SELECT key, SUM(m1) AS total
FROM (
SELECT dim1 AS key, SUM(metric1) AS m1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim1
UNION
SELECT dim2 AS key, SUM(m1) AS m1
FROM dv2
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY dim2
)
GROUP BY key
ORDER BY total
使用子选择进行分层:
SELECT rows.dim1, SUM(rows.m1) AS total
FROM (
SELECT _.dim1,_.m1
FROM (
SELECT * FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
) _
WHERE _.dim1 in ('A', 'B', 'C')
) rows
GROUP BY 1
ORDER BY total
使用 CTE WITH 的层:
WITH rows AS (
WITH _ AS (
SELECT * FROM data_ares
WHERE `timestamp` BETWEEN '2021-01-01' AND '2021-02-01'
)
SELECT _.item, _.units FROM _
WHERE _.item IS NOT NULL
)
SELECT rows.item, SUM(rows.units) AS units
FROM rows WHERE rows.item in ('A', 'B', 'C')
GROUP BY rows.item
SELECT SUM(metric1) AS m1, dim1
FROM dv1
WHERE `timestamp` BETWEEN '2022-01-01' AND '2022-01-02'
GROUP BY 2
维度
您可以选择默认可用或在数据视图中定义的任何维度。可通过维度 ID 选择维度。
量度
可选择的量度为:
- 默认情况下可用的任何量度;
- 在数据视图中定义;
- 与用户有权访问的数据视图兼容的计算量度。
可通过包含在 SUM(metric)
表达式中的量度 ID 选择量度,就像处理其他 SQL 源一样。
您可以使用:
计算不同的值
由于 Customer Journey Analytics 工作方式的基本特性,您可以获得准确的不同计数的唯一维度是 adobe_personid
维度。以下 SQL 语句返回默认人员量度的值,即不同人员的计数:SELECT COUNT(DISTINCT adobe_personid)
或 SELECT APPROX_COUNT_DISTINCT(adobe_personid)
。对于其他维度,会返回近似的非重复计数。
条件量度
可以将 IF
或 CASE
子句嵌入 SUM
或 COUNT
函数中,以添加对于选定量度特定的其他分段。添加这些子句类似于在工作区报告表的量度列中应用区段。
示例:
SUM(IF(dim1 = 'X' AND dim2 = 'A', metric1, 0)) AS m1
SUM(CASE WHEN dim1 = 'X' AND dim2 = 'A' THEN metric1 END) AS m1
内联计算
您可以将额外的数学运算应用到 SELECT
中的度量表达式中。可以使用此数学运算,而无需在计算量度中定义数学运算方法。下表列出了支持的表达式的类型。
+
、-
、*
、/
和 %
-X
或 +X
PI()
POSITIVE
、NEGATIVE
、ABS
、FLOOR
、CEIL
、CEILING
、EXP
、LN
、LOG10
、LOG1P
、SQRT
、CBRT
、DEGREES
、RADIANS
、SIN
、COS
、TAN
、ACOS
、ASIN
、ATAN
、COSH
、SINH
和 TANH
MOD
, POW
, POWER
, ROUND
, LOG
特殊列
时间戳
timestamp
特殊列用于提供查询的日期范围。可使用 BETWEEN
表达式或 timestamp
>
、>=
、<
、<=
检查 AND
对来定义日期范围。timestamp
是可选的,如果未提供完整范围,则使用默认值:
- 如果仅提供最小值(
timestamp > X
或timestamp >= X
),则范围为从 X 到当前日期。 - 如果仅提供最大值(
timestamp < X
或timestamp <= X
),则范围为从 X 减去 30 天到 X。 - 如果未提供任何内容,则范围为从当前日期减去 30 天到当前的日期。
时间戳范围被转换为 RankedRequest 中的日期范围全局区段。
时间戳字段也可以在日期/时间函数中用于解析或截断事件时间戳。
日期范围
daterange
特殊列的工作方式与 timestamp
的类似,但是分段仅限于完整天数。daterange
也是可选的,并具有与 timestamp
相同的范围默认值。daterange
字段也可以在日期/时间函数中用于解析或截断事件日期。
daterangeName
特殊列可用于通过已命名的日期范围(例如 Last Quarter
)将查询分段。
daterange
量度(小时、30 分钟、5 分钟等)。区段 ID
filterId
特殊列是可选的,用于在查询中应用外部定义的区段。在查询中应用外部定义的区段类似于将区段拖动到工作区的面板上。可通过 AND
多个区段 ID 来使用它们。
除了 filterId
,您可以用 filterName
来使用区段的名称而不是 ID。
WHERE 子句
通过三个步骤处理 WHERE
子句:
-
从
timestamp
、daterange
或daterangeName
特殊字段中查找日期范围。 -
查找要包含在区段中的任何外部定义的
filterId
或filterName
。 -
将剩余的表达式转变为临时区段。
通过解析 WHERE
子句中的 AND
的第一层来进行处理。每个采用 AND
的顶层表达式必须与上述某个表达式匹配。比 AND
的第一层更深入的任何项,或者如果 WHERE
子句在顶层使用 OR
,都将被作为临时区段。
排序顺序
默认情况下,查询按第一个选定量度以降序顺序对结果进行排序。可以通过指定 ORDER BY ... ASC
或 ORDER BY ... DESC
来覆盖默认排序顺序。如果使用的是 ORDER BY
,则必须在第一个选定量度上指定 ORDER BY
。
还可以在量度前面使用 -
(减号)来颠倒顺序。下面的两个语句都将产生相同的顺序:
ORDER BY metric1 ASC
ORDER BY -metric1 DESC
一般函数支持
WHERE `timestamp` >= TIMESTAMP('2022-01-01 00:00:00') AND `timestamp` < TIMESTAMP('2022-01-02 00:00:00')
WHERE
子句中使用。WHERE `timestamp` >= TO_TIMESTAMP('01/01/2022', 'MM/dd/yyyy') AND `timestamp` < TO_TIMESTAMP('01/02/2022', 'MM/dd/yyyy')
WHERE
子句中使用,(可选)并提供该时间字符串的格式。WHERE `timestamp` >= DATE('2022-01-01') AND `timestamp` < DATE('2022-01-02')
WHERE
子句中使用。WHERE `timestamp` >= TO_DATE('01/01/2022', 'MM/dd/yyyy') AND `timestamp` < TO_DATE('01/02/2022', 'MM/dd/yyyy')
WHERE
子句中使用,(可选)并提供该日期字符串的格式。维度函数支持
这些函数可用于 SELECT
、WHERE
子句或条件量度中的维度。
String 函数
Date-Time 函数
SELECT EXTRACT(MONTH FROM `timestamp`)
支持的部分为:
- 关键词:
YEAR
、MONTH
、DAYOFMONTH
、DAYOFWEEK
、DAYOFYEAR
、WEEK
、QUARTER
、HOUR
、MINUTE
。- 字符串:
'YEAR'
、'Y'
、'MONTH'
、'M'
、'DAYOFMONTH'
、'DAY'
、'D'
、'DAYOFWEEK'
、'DOW'
、'DAYOFYEAR'
、'DOY'
、'WEEK'
、'WOY
、'W'
、'QUARTER'
、'QOY'
、'Q'
、'HOUR'
或 'MINUTE'
。SELECT DATE_PART('month', `timestamp`)
支持的字符串部分为:
'YEAR'
、'Y'
、'MONTH'
、'M'
、'DAYOFMONTH'
、'DAY'
、'D'
、'DAYOFWEEK'
、'DOW'
、'DAYOFYEAR'
、'DOY'
、'WEEK'
、'WOY
、'W'
、'QUARTER'
、'QOY'
、'Q'
、'HOUR'
或 'MINUTE'
。SELECT DATE_TRUNC('quarter', `timestamp`)
支持的字符串粒度为:
'YEAR'
、'Y'
、'MONTH'
、'M'
、'DAYOFMONTH'
、'DAY'
、'D'
、'DAYOFWEEK'
、'DOW'
、'DAYOFYEAR'
、'DOY'
、'WEEK'
、'WOY
、'W'
、'QUARTER'
、'QOY'
、'Q'
、'HOUR'
或 'MINUTE'
。部分支持
BI 扩展插件仅部分支持某些 SQL 功能,并且不会返回与其他数据库相同的结果。 此特定功能用于由各种 BI 工具生成的 SQL,而 BI 扩展插件无法与之完全匹配。因此,BI 扩展主要聚焦于一个有限的实施,该实施仅涵盖最低限度的 BI 工具使用情况,且不会引发错误。有关更多详细信息,请参阅下表。
MIN(daterange)
或MAX(daterange)
MIN()
对 timestamp
、daterange
或类似的 daterangeX
(如 daterangeday
)进行操作,将返回两年前的结果。使用
MAX()
对 timestamp
、daterange
或类似的 daterangeX
(如 daterangeday
)进行操作,将返回当前的日期/时间。使用MIN()
或 MAX()
对任何其他维度、量度或表达式的操作将返回 0。