Customer Journey Analytics BI extension
The Customer Journey Analytics BI extension enables SQL access to the data views that you have defined in Customer Journey Analytics. Your data engineers and analysts might be more familiar with Power BI, Tableau, or other business intelligence and visualization tools (further referred to as BI tools). They can now create reporting and dashboards based on the same data views that Customer Journey Analytics users are using when creating their Analysis Workspace projects.
Adobe Experience Platform Query Service is the SQL interface to data available in the data lake of Experience Platform. With the Customer Journey Analytics BI extension enabled, the functionality of Query Service is extended to see your Customer Journey Analytics data views as tables or views in a Query Service session. As a result, business intelligence tools that use Query Service as their PostgresSQL interface benefit seamlessly from this extended functionality.
The main benefits are:
- No need to recreate an equivalent representation of Customer Journey Analytics data views within the BI tool itself.
See Data views for more information on the functionality of data views to understand what must be recreated. - Greater consistency in reporting and analysis between BI tools and Customer Journey Analytics.
- Combine Customer Journey Analytics data with other data sources already available in BI tools.
Prerequisites
To use this functionality, you must have:
- Granted access to Experience Platform and Customer Journey Analytics.
- Granted Product admin access to Customer Journey Analytics, so you can view, edit, update, or delete connections and data views.
- Granted access to the data views you want to access.
- Granted access to the CJA BI extension.
- Use expiring on non-expiring credentials to connect BI tools to the Customer Journey Analytics BI extension. The Credentials guide provides more information on setting expiring credentials or non-expiring credentials.
See Customer Journey Access Control for more information, specifically the Product Admin additional permissions and Customer Journey Analytics Permissions in the Admin Console.
Usage
To use the Customer Journey Analytics BI extension functionality, you can either use SQL directly or use the drag and drop experience available in the specific BI tool.
SQL
You can use the functionality directly in SQL statements using either the Query Editor or a standard PostgresSQL command-line interface (CLI) client.
In Adobe Experience Platform:
-
Select Queries from DATA MANAGEMENT in the left rail.
-
Select Create query.
-
Select the
cja
Database. -
To execute the query, type your SQL statement and select the button (or press
[SHIFT]
+[ENTER]
).
-
Look up and copy your PostgresSQL credentials in Adobe Experience Platform:
-
Select Queries from the left rail (under DATA MANAGEMENT).
-
Select Credentials from the top bar.
-
Select the
cja
Database. -
To copy the command string, use in the PSQL command section.
-
-
Open a command or terminal window.
-
To log in and start executing your queries, paste the command string in your terminal.
See the Query Editor UI guide for more information.
BI tools
Currently, the Customer Journey Analytics BI extension is supported and tested for Power BI and Tableau only. Other BI tools using the PSQL interface might work as well, but are not yet supported officially.
-
Look up the details of your PostgresSQL credentials in Adobe Experience Platform:
-
Select Queries from the left rail (under DATA MANAGEMENT).
-
Select Credentials from the top bar.
-
Select the
cja
Database. -
Use to copy each of the Postgres credentials parameters (Host, Port, Database, Username, and others) when needed in Power BI.
-
-
In Power BI:
-
In the main window, select Get data from the top toolbar.
-
Select More… in the left rail.
-
In the Get Data screen, search for
PostgresSQL
and select the PostgresSQL database from the list. -
In the PostgressSQL database dialog:
-
Paste the Host parameter from Experience Platform Queries Credentials in the Server text field.
-
Paste the Database parameter from Experience Platform Queries Credentials in the Database text field.
Add
?FLATTEN
to the Database parameter, so it reads likeprod:cja?FLATTEN
for example. See Flatten nested data structures for use with third-party BI tools for more information. -
When prompted for Data Connectivity mode, select DirectQuery.
-
You are prompted for Username and Password. Use the equivalent parameters from Experience Platform Queries Credentials.
-
-
After successful login, the Customer Journey Analytics data view tables appear in Power BIs Navigator.
-
Select the data view tables that you want to use and select Load.
All dimensions and metrics associated with one or more selected tables appear in the right pane, ready to be used in your visualizations.
See Connect Power BI to Query Service for more information.
-
-
Look up the details of your PostgresSQL credentials in Adobe Experience Platform:
-
Select Queries from the left rail (under DATA MANAGEMENT).
-
Select Credentials from the top bar.
-
Select the
cja
Database. -
Use to copy each of the Postgres credentials parameters (Host, Port, Database, Username, and others) when needed in Tableau.
-
-
In Tableau:
-
Select More from To a Server in the left rail.
-
Select PostgresSQL from the list.
-
In the PostgresSQL dialog:
-
Paste the Host parameter from Experience Platform Queries Credentials into the Server text field.
-
Paste the Port parameter from Experience Platform Queries Credentials into the Port text field.
-
Paste the Database parameter from Experience Platform Queries Credentials into the Database text field.
Add
%3FFLATTEN
to the Database parameter, so it reads likeprod:cja%3FFLATTEN
for example. See Flatten nested data structures for use with third-party BI tools for more information. -
Select Username and Password from Authentication list.
-
Paste Username parameter from Experience Platform Queries Credentials into Username text field.
-
Paste the Password parameter from Experience Platform Queries Credentials into the Password text field.
-
Select the Sign In.
-
-
Customer Journey Analytics data views show up as tables in the Table list.
-
Drag the tables that you want to use on the canvas.
You can now work with the data from the data view tables to build your reports and visualizations.
See Connect Tableau to Query Service for more information.
-
See Connect clients to Query Service for an overview of and more information on the various tools available.
Functionality
By default, your data views have a table-safe name generated from their friendly name. For example, the data view named My Web Data View has the view name my_web_data_view
. You can define a preferred name to use in your BI tool for your data view. See Data view settings for more information.
If you want to use the data view IDs as the table names, you can add the optional CJA_USE_IDS
setting to your database name when connecting. For example, prod:cja?CJA_USE_IDS
shows your data views with names like dv_ABC123
.
Data governance
The data governance-related settings in Customer Journey Analytics are inherited from Adobe Experience Platform. The integration between Customer Journey Analytics and Adobe Experience Platform Data Governance allows for labeling of sensitive Customer Journey Analytics data and enforcement of privacy policies.
Privacy labels and policies that were created on datasets consumed by Experience Platform can be surfaced in the Customer Journey Analytics data views workflow. Therefore, data queried using the Customer Journey Analytics BI extension show appropriate warnings or errors when not complying with the privacy labels and policies defined.
List data views
In the standard PostgreSQL CLI, you can list your views using \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
Nested versus flattened
By default, the schema of your data views uses nested structures, just like the original XDM schemas. The integration also supports the FLATTEN
option. You can use this option to force the schema for the data views (and any other table in the session) to be flattened. Flattening allows for easier use in BI tools that don’t support structured schemas. See Working with nested data structures in Query Service for more information.
Defaults and limitations
The following additional defaults and limitations apply when using the BI Extenion:
-
The BI extension requires a row limit for the query results. The default is 50, but you can override this in SQL using
LIMIT n
, wheren
is 1 - 50000. -
The BI extension requires a date range to limit the rows used for calculations. The default is the last 30 days, but you can override this in your SQL
WHERE
clause using the specialtimestamp
ordaterange
columns. -
The BI extension requires aggregate queries. You can’t use SQL like
SELECT * FROM ...
to get the raw, underlying rows. At a high level, your aggregate queries should use:-
Select totals using
SUM
and/orCOUNT
.
For example,SELECT SUM(metric1), COUNT(*) FROM ...
-
Select metrics broken down by a dimension.
For example,SELECT dimension1, SUM(metric1), COUNT(*) FROM ... GROUP BY dimension1
-
Select distinct metric values.
For example,SELECT DISTINCT dimension1 FROM ...
See for more details Supported SQL.
-
Supported SQL
See Query Service SQL reference for the full reference on what type of SQL is supported.
See the table below for examples of the SQL you can use.
table 0-row-2 1-row-2 2-row-2 3-row-2 4-row-2 5-row-2 6-row-2 7-row-2 8-row-2 9-row-2 10-row-2 layout-auto | |
---|---|
Pattern | Example |
Schema discovery |
|
Ranked or Breakdown |
|
HAVING clause |
|
Distinct, top dimension values |
|
Metric totals |
|
Multi-dimension breakdowns and top-distincts |
|
Subselect: Filter additional results |
|
Subselect: Querying across data views |
|
Subselect: Layered source, filtering, and aggregation |
Layered using subselects:
Layers using CTE WITH:
|
Selects where the metrics come before or are mixed with the dimensions |
|
Dimensions
You can select any of the dimensions available by default or defined in the data view. You select a dimension by its ID.
Metrics
The metrics available to select are:
- Any of the metrics available by default;
- Defined in the data view;
- Calculated metrics that are compatible with the data view that the user has access to.
You select a metric by its ID wrapped in a SUM(metric)
expression just like you would do with other SQL sources.
You can use:
SELECT COUNT(*)
orCOUNT(1)
to get the occurrences metric.SELECT COUNT(DISTINCT dimension)
orSELECT APPROX_COUNT_DISTINCT(dimension)
to count the approximate distinct values of a dimension. See details in Counting distinct values.- Inline calculations to combine metrics on the fly and/or doing math on them.
Counting distinct values
Due to the underlying nature of how Customer Journey Analytics works, the only dimension you can get an exact distinct count for is the adobe_personid
dimension. The following SQL statements SELECT COUNT(DISTINCT adobe_personid)
or SELECT APPROX_COUNT_DISTINCT(adobe_personid)
return the value of the default persons metric, which is the count of distinct people. For other dimensions, an approximate distinct count is returned.
Conditional metrics
You can embed an IF
or CASE
clause in the SUM
or COUNT
functions to add additional filtering that is specific to a selected metric. Adding these clauses is similar to applying a filter to a metric column in a Workspace report table.
Examples:
SUM(IF(dim1 = 'X' AND dim2 = 'A', metric1, 0)) AS m1
SUM(CASE WHEN dim1 = 'X' AND dim2 = 'A' THEN metric1 END) AS m1
Inline calculations
You can apply additional math to metric expressions in your SELECT
. This math can be used instead of defining the math in a calculated metric. The following table lists what types of expressions are supported.
+
, -
, *
, /
, and %
-X
or +X
PI()
POSITIVE
, NEGATIVE
, ABS
, FLOOR
, CEIL
, CEILING
, EXP
, LN
, LOG10
, LOG1P
, SQRT
, CBRT
, DEGREES
, RADIANS
, SIN
, COS
, TAN
, ACOS
, ASIN
, ATAN
, COSH
, SINH
, and TANH
MOD
, POW
, POWER
, ROUND
, LOG
Special columns
Timestamp
The timestamp
special column is used to provide the date ranges for the query. A date range can be defined with a BETWEEN
expression or a pair of timestamp
>
, >=
, <
, <=
checks AND
ed together.
The timestamp
is optional and if no full range is provided, defaults are used:
- If only a minimum is provided (
timestamp > X
ortimestamp >= X
), the range is from X to now. - If only a max is provided (
timestamp < X
ortimestamp <= X
), the range is from X minus 30 days to X. - If nothing is provided, the range is from now minus 30 days to now.
The timestamp range is converted to a date range global filter in the RankedRequest.
The timestamp field can also be used in date/time functions to parse or truncate the event timestamp.
Date range
The daterange
special column works similar to timestamp
; however the filtering is limited to full days. The daterange
is also optional and has the same range defaults as timestamp
.
The daterange
field can also be used in date/time functions to parse or truncate the event date.
The daterangeName
special column can be used to filter your query using a named date range like Last Quarter
.
daterange
metrics that are less than a day (hour, 30 minute, 5 minute, etc.).Filter ID
The filterId
special column is optional and is used to apply an externally defined filter to the query. Applying an externally defined filter to a query is similar to dragging a filter on a panel in Workspace. Multiple filter IDs can be used by AND
-ing them.
Along with filterId
, you can use filterName
to use a filter’s name instead of ID.
Where clause
The WHERE
clause is handled in three steps:
-
Find the date range from the
timestamp
,daterange
, ordaterangeName
special fields. -
Find any externally defined
filterId
s orfilterName
s to include in the filtering. -
Turn the remaining expressions into ad-hoc filters.
The handling is done by parsing the first level of AND
s in the WHERE
clause. Each top-level AND
-ed expression must match one of the above. Anything deeper than the first level of AND
s, or, if the WHERE
clause uses OR
s at the top level, is handled as an ad-hoc filter.
Sorting order
By default, the query sorts the results by the first selected metric in descending order. You can overwrite the default sorting order by specifying ORDER BY ... ASC
or ORDER BY ... DESC
. If you use ORDER BY
, you must specify ORDER BY
on the first selected metric.
You can also flip the order by using -
(minus) in front of the metric. Both statements below result in the same ordering:
ORDER BY metric1 ASC
ORDER BY -metric1 DESC
General function support
CAST(`timestamp` AS STRING)
or`timestamp`::string
CAST
function is ignored.WHERE `timestamp` >= TIMESTAMP('2022-01-01 00:00:00') AND `timestamp` < TIMESTAMP('2022-01-02 00:00:00')
WHERE
clause.WHERE `timestamp` >= TO_TIMESTAMP('01/01/2022', 'MM/dd/yyyy') AND `timestamp` < TO_TIMESTAMP('01/02/2022', 'MM/dd/yyyy')
WHERE
clause, optionally providing a format for that time string.WHERE `timestamp` >= DATE('2022-01-01') AND `timestamp` < DATE('2022-01-02')
WHERE
clause.WHERE `timestamp` >= TO_DATE('01/01/2022', 'MM/dd/yyyy') AND `timestamp` < TO_DATE('01/02/2022', 'MM/dd/yyyy')
WHERE
clause, optionally providing a format for that date string.Dimension function support
These functions can be used on dimensions in the SELECT
, WHERE
clause, or in conditional metrics.
String functions
SELECT LOWER(name) AS lower_name
Date-time functions
SELECT DAYOFWEEK(`timestamp`)
SELECT DAYOFYEAR(`timestamp`)
SELECT HOUR(`timestamp`)
SELECT EXTRACT(MONTH FROM `timestamp`)
Supported parts are:
- Keywords:
YEAR
, MONTH
, DAYOFMONTH
, DAYOFWEEK
, DAYOFYEAR
, WEEK
, QUARTER
, HOUR
, MINUTE
.- Strings:
'YEAR'
, 'Y'
, 'MONTH'
, 'M'
, 'DAYOFMONTH'
, 'DAY'
, 'D'
, 'DAYOFWEEK'
, 'DOW'
, 'DAYOFYEAR'
, 'DOY'
, 'WEEK'
, 'WOY
’, 'W'
, 'QUARTER'
, 'QOY'
, 'Q'
, 'HOUR'
, or 'MINUTE'
.SELECT DATE_PART('month', `timestamp`)
Supported string parts are:
'YEAR'
, 'Y'
, 'MONTH'
, 'M'
, 'DAYOFMONTH'
, 'DAY'
, 'D'
, 'DAYOFWEEK'
, 'DOW'
, 'DAYOFYEAR'
, 'DOY'
, 'WEEK'
, 'WOY
’, 'W'
, 'QUARTER'
, 'QOY'
, 'Q'
, 'HOUR'
, or 'MINUTE'
.SELECT DATE_TRUNC('quarter', `timestamp`)
Supported string granularities are:
'YEAR'
, 'Y'
, 'MONTH'
, 'M'
, 'DAYOFMONTH'
, 'DAY'
, 'D'
, 'DAYOFWEEK'
, 'DOW'
, 'DAYOFYEAR'
, 'DOY'
, 'WEEK'
, 'WOY
’, 'W'
, 'QUARTER'
, 'QOY'
, 'Q'
, 'HOUR'
, or 'MINUTE'
.Partial Support
Some SQL functionality is only partially supported with the BI extension and does not return the same results you see with other databases. This specific functionality is used in SQL generated by various BI tools, for which the BI extension does not have an exact match. As a result, the BI extension focuses on a limited implementation that covers the minimum BI tool usage without throwing errors. See the table below for more details.
MIN(daterange)
orMAX(daterange)
MIN()
on timestamp
, daterange
, or any of the daterangeX
like daterangeday
will return 2 years ago.MAX()
on timestamp
, daterange
, or any of the daterangeX
like daterangeday
will return the current date/time.MIN()
or MAX()
on any other dimmension, metric, or expression will return 0.