This article documents how to accomplish a number of use cases using the Customer Journey Analytics BI extension. Each use case explains the Customer Journey Analytics functionality, followed with details for each of the BI tools supported:
Power BI Desktop. The version used is 2.137.1102.0 64-bit (October 2024).
Tableau Desktop. The version used is 2024.1.5 (20241.24.0705.0334) 64-bit.
Looker. Online version 25.0.23, available through looker.com
Jupyter Notebook. The version used is 7.3.2.
RStudio. The version used is 2024.12.0, build 467.
The connect use case focuses on how to connect BI tools using the Customer Journey Analytics BI extension.
The report and analysis use cases instruct how to accomplish similar Customer Journey Analytics visualizations in the BI tools currently supported.
The understand use cases provide more details on:
Transformations that occur when you use a BI tools to report and analyze.
Visualization similarities and differences between Customer Journey Analytics and BI tools.
Caveats of each of the BI tools you should be aware of.
Connect and validate
This use case sets up the connection from the BI tool to Customer Journey Analytics, lists the available data views, and selects a data view to use.
Customer Journey Analytics
The instructions refer to an example environment with the following objects:
Data view: C&C - Data View π .
Dimensions: Product Name π and Product Category π .
Metrics: Purchase Revenue π and Purchases π .
Filter: Fishing Products π .
{modal="regular"}
When you go through the use cases, replace these example objects with objects that are appropriate for your specific environment.
BI tools
tabs
Power BI Desktop
Access the required credentials and parameters from the Experience Platform Query Service UI.
Navigate to your Experience Platform sandbox.
Select
Queries from the left rail.
Select Credentials tab in the Queries interface.
Select prod:cja from the Database dropdown menu.
{modal="regular"}
Start Power BI Desktop.
From the main interface, select Get data from other sources.
In the Get Data dialog:
{modal="regular"}
Search for and select PostgreSQL database.
Select Connect.
In the PostgreSQL database dialog:
{modal="regular"}
Use
to copy and paste the Host and Port values from the Experience Platform QueryExpiring Credentials panel, separated by : as the value for Server. For example: examplecompany.platform-query.adobe.io:80.
Use
to copy and paste the Database value from the Experience Platform QueryExpiring Credentials panel. Add ?FLATTEN to the value that you paste. For example, prod:cja?FLATTEN.
Select DirectQuery as the Data connectivity mode.
Select OK.
In the PostgreSQL database - Database dialog:
{modal="regular"}
Use
to copy the Username and Password values from the Experience Platform QueryExpiring Credentials panel in the User name and Password fields. If you are using a non-expiring credential, use the password of your non-expiring credential.
Ensure that the dropdown menu for Select which level to apply these settings to is set to the Server that you have defined earlier.
Select Connect.
In the Navigator dialog, the data views are retrieved. This retrieval can take some time. Once retrieved, you see the following in Power BI Desktop.
{modal="regular"}
Select public.cc_data_view from the list in the left panel.
You have two options:
Select Load to continue and finish the setup.
Select Transform Data. You see a dialog where you can optionally apply transformations as part of the configuration.
{modal="regular"}
Select Close & Apply.
After a while, public.cc_data_view is displayed in the Data pane. Select
to show dimensions and metrics.
{modal="regular"}
To FLATTEN or not
Power BI Desktop supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.
Access the required credentials and parameters from the Experience Platform Query Service UI.
Navigate to your Experience Platform sandbox.
Select
Queries from the left rail.
Select Credentials tab in the Queries interface.
Select prod:cja from the Database dropdown menu.
{modal="regular"}
Start Tableau.
Select PostgreSQL from the left rail underneath To a Server. If not available, select More⦠and select PostgreSQL from the Installed Connectors. {modal="regular"}
In the PostgreSQL dialog, in the General tab: {modal="regular"}
Use
to copy and paste the Host from the Experience Platform QueryExpiring Credentials panel to the Server.
Use
to copy and paste the Port from the Experience Platform QueryExpiring Credentials panel to the Port.
Use
to copy and paste the Database from the Experience Platform QueryExpiring Credentials panel to the Database. Add %3FFLATTEN to the value that you paste. For example: prod:cja%3FFLATTEN.
Select Username and Password from the Authentication dropdown menu.
Use
to copy and paste the Username from the Experience Platform QueryExpiring Credentials panel to the Username.
Use
to copy and paste the Password from the Experience Platform QueryExpiring Credentials panel to the Password. If you are using a non-expiring credential, use the password of your non-expiring credential.
Ensure that Require SSL is checked.
Select Sign In.
You see a Progressing Request dialog while Tableau Desktop validates the connection.
In the main window, you see in the Data Source page, in the left pane:
The name of the connection, underneath Connections.
The name of the database, underneath Database.
A list of tables, underneath Table.
{modal="regular"}
Drag the cc_data_view entry and drop the entry on the main view that reads Drag tables here.
The main window displays details of the cc_data_view data view. {modal="regular"}
To FLATTEN or not
Tableau Desktop supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.
Access the required credentials and parameters from the Experience Platform Query Service UI.
Navigate to your Experience Platform sandbox.
Select
Queries from the left rail.
Select Credentials tab in the Queries interface.
Select prod:cja from the Database dropdown menu.
{modal="regular"}
Log in to Looker
Select Admin from the left rail.
Select Connections.
Select Add Connection.
In the Connect your database to Looker screen.
{modal="regular"}
Enter a Name for your connection, for example Example Looker Connection.
Ensure All Projects is selected as the Connection Scope.
Select PostgreSQL 9.5+ as the Dialect.
Use
to copy and paste the Host value from the Experience Platform QueryExpiring Credentials panel, as the value for Host. For example: examplecompany.platform-query.adobe.io.
Use
to copy and paste the Port value from the Experience Platform QueryExpiring Credentials panel, as the value for Port. For example: 80.
Use
to copy and paste the Database value from the Experience Platform QueryExpiring Credentials panel as the value for Database. Add %3FFLATTEN to the value that you paste. For example, prod:cja%3FFLATTEN.
Use
to copy and paste the Username value from the Experience Platform QueryExpiring Credentials panel as the value for Username.
Use
to copy and paste the Password value from the Experience Platform QueryExpiring Credentials panel as the value for Password.
Select Expand all at Optional Settings.
Set Max connections per node to 5.
Ensure SSL is enabled.
Select Test to test the connection. You should see a banner appear at the top of the screen with a message like Success, can connect JDBC β¦.
Select Connect to establish and save the connection.
You see the new connection in the Connections interface.
Select β from Admin to go to main navigation in the left rail.
Select Develop.
Select Projects.
Select New Model in LookML Projects.
To ensure you do not affect other users. select Enter Development Mode, when prompted.
In the Create Model experience:
In β Select Database Connection:
Select your database connection in Select database connection. For example: example_looker_connection.
Name your project in Create a new LookML Project for this model. For example: example_looker_project.
Select Next.
In β Select Tables:
Select public and then ensure your Customer Journey Analytics data view is selected. For example:
cc_data_view.
Select Next.
In β Select Primary Keys:
Select Next.
In β Select Explores to Create:
Ensure you select your view. For example: cc_data_view.view.
Select Next.
In β Enter Model Name:
Name your model. For example: example_looker_model.
Select Complete and Explore Data.
You are redirected to the Explore interface of Looker, ready to explore the data.
To FLATTEN or not
Looker supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.
In a new cell, enter the config parameters for your connection. Use
to copy and paste values from the Experience Platform QueryExpiring Credentials panel to the values required for the config parameters. For example:
Use
to copy and paste the password from the Experience Platform QueryExpiring Credentials panel to the Password field in Jupyter Notebook.
{modal="regular"}
In a new cell, enter the statements to load the SQL extension, the required library and connect with Customer Journey Analytics.
code language-python
%load_ext sql
from sqlalchemy import create_engine
%sql postgresql://{config_username.value}:{config_password.value}@{config_host.value}:{config_port.value}/{config_db.value}?sslmode=require
Execute the shell. You should see no output but the cell should execute without any warning.
{modal="regular"}
In a new call, enter the statements to get a list of available data views based on the connection.
code language-python
%%sql
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
AND c.relname NOT LIKE '%test%'
AND c.relname NOT LIKE '%ajo%'
ORDER BY 1,2;
Execute the shell. You should see output simular to the screenshot below.
{modal="regular"}
You should see the cc_data_view in the list of data views.
To FLATTEN or not
Jupyter Notebook supports the following scenarios for the FLATTEN parameter. See Flatten nested data for more information.
In your first chunk, enter the following statements between ```{r} and ```. Use
to copy and paste values from the Experience Platform QueryExpiring Credentials panel to the values required for the various parameters, like host, dbname, and user. For example:
In this use case, you want to display a table and simple line visualization that shows a daily trend of occurrences (events) from January 1, 2023 up until January 31, 2023.
You see a table displaying the occurrences for the current month. For better visibility, enlarge the visualization.
In the Filters pane:
Select the daterangeday is (All) from Filters on this visual.
Select Advanced filtering as the Filter type.
Define the filter to Show items when the valueis on or after1/1/2023Andis before2/1/2023. You can use the calendar icon to pick and select dates.
Select Apply filter.
You see the table updated with the applied daterangeday filter.
In the Visualizations pane, select the Line chart visualization.
A line chart visualization replaces the table while using the same data as the table. Your Power BI Desktop should look like below.
{modal="regular"}
On the Line chart visualization:
Select
.
From the context menu, select Show as a table.
The main view is updated to show both a line visualization and a table. Your Power BI Desktop should look like below.
{modal="regular"}
Tableau Desktop
Select the Sheet 1 tab at the bottom to switch from the Data source view. In the Sheet 1 view:
Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.
In the Filters Field [Daterange] dialog, select Range of Dates and select Next >.
In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 01/02/2023.
{modal="regular"}
Drag and drop Daterangeday from the Tables list in the Data pane and drop the entry in the field next to Columns.
Select Day from the Daterangeday dropdown menu, so that the value is updated to DAY(Daterangeday).
Drag and drop Occurrences from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Occurrences).
Modify Standard to Entire View from the Fit dropdown menu in the toolbar.
Your Tableau Desktop should look like below.
{modal="regular"}
Select Duplicate from the Sheet 1 tab context menu to create a second sheet.
Select Rename from the Sheet 1 tab context menu to rename the sheet to Graph.
Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Data.
Ensure that the Data sheet is selected. In the Data view:
Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the Data view to a table.
Select Swap Rows and Columns from the toolbar.
Modify Standard to Entire View from the Fit dropdown menu in the toolbar.
Your Tableau Desktop should look like below.
{modal="regular"}
Select the New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:
Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.
Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.
Select the Data sheet in the view and modify Entire View to Fix Width.
Your Tableau Desktop should look like below.
{modal="regular"}
Looker
In the Explore interface of Looker, ensure you do have a clean setup. If not, select
Remove fields and filters.
Select + Filter underneath Filters.
In the Add Filter dialog:
Select β£ Cc Data View
From the list of fields, select β£ Daterange Date then Daterange Date.
{modal="regular"}
Specify the Cc Data View Daterange Date filter as is in range2023/01/01until (before)2023/02/01.
From the Cc Data View section in the left rail,
Select β£ Daterange Date, then Date from the list of DIMENSIONS.
Select Count underneath MEASURES in the left rail (at the bottom).
Select Run.
Select β£ Visualization to display the line visualization.
You should see a visualization and table similar as shown below.
{modal="regular"}
Jupyter Notebook
Enter the following statements in a new cell.
code language-python
import seaborn as sns
import matplotlib.pyplot as plt
data = %sql SELECT daterangeday AS Date, COUNT(*) AS Events \
FROM cc_data_view \
WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \
GROUP BY 1 \
ORDER BY Date ASC
df = data.DataFrame()
df = df.groupby('Date', as_index=False).sum()
plt.figure(figsize=(15, 3))
sns.lineplot(x='Date', y='Events', data=df)
plt.show()
display(data)
Execute the cell. You should see output similar to the screenshot below.
{modal="regular"}
RStudio
Enter the following statements between ```{r} and ``` in a new chunk.
Power BI does not understand how to handle date-time fields, so dimensions like daterangehour and daterangeminute are not supported.
Tableau Desktop
Select the Sheet 1 tab at the bottom to switch from Data source. In the Sheet 1 view:
Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.
In the Filters Field [Daterange] dialog, select Range of Dates and select Next >.
In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 02/01/2023.
{modal="regular"}
Drag and drop Daterangehour from the Tables list in the Data pane and drop the entry in the field next to Columns.
Select More > Hours from the Daterangeday dropdown menu, so that the value is updated to HOUR(Daterangeday).
Drag and drop Occurrences from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Occurrences).
Modify Standard to Entire View from the Fit dropdown menu in the toolbar.
Your Tableau Desktop should look like below.
{modal="regular"}
Select Duplicate from the Sheet 1 tab context menu to create a second sheet.
Select Rename from the Sheet 1 tab context menu to rename the sheet to Graph.
Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Data.
Ensure that the Data sheet is selected. In the Data view:
Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the Data view to a table.
Drag HOUR(Daterangeday) from Columns to Rows.
Modify Standard to Entire View from the Fit dropdown menu in the toolbar.
Your Tableau Desktop should look like below.
{modal="regular"}
Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:
Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.
Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.
Select the Data sheet in the view and modify Entire View to Fix Width.
Your Dashboard 1 view should look like below.
{modal="regular"}
Looker
In the Explore interface of Looker, ensure you do have a clean setup. If not, select
Remove fields and filters.
Select + Filter underneath Filters.
In the Add Filter dialog:
Select β£ Cc Data View
From the list of fields, select β£ Daterange Date then Daterange Date.
{modal="regular"}
Specify the Cc Data View Daterange Date filter as is in range2023/01/01until (before)2023/01/02.
From the Cc Data View section in the left rail,
Select β£ Daterangehour Date, then Time from the list of DIMENSIONS.
Select Count underneath MEASURES in the left rail (at the bottom).
Select Run.
Select β£ Visualization to display the line visualization.
You should see a visualization and table similar as shown below.
{modal="regular"}
Jupyter Notebook
Enter the following statements in a new cell.
code language-python
import seaborn as sns
import matplotlib.pyplot as plt
data = %sql SELECT daterangehour AS Hour, COUNT(*) AS Events \
FROM cc_data_view \
WHERE daterange BETWEEN '2023-01-01' AND '2023-01-02' \
GROUP BY 1 \
ORDER BY Hour ASC
df = data.DataFrame()
df = df.groupby('Hour', as_index=False).sum()
plt.figure(figsize=(15, 3))
sns.lineplot(x='Hour', y='Events', data=df)
plt.show()
display(data)
Execute the cell. You should see output similar to the screenshot below.
{modal="regular"}
RStudio
Enter the following statements between ```{r} and ``` in a new chunk.
You see a table displaying the occurrences for the current month. For better visibility, enlarge the visualization.
In the Filters pane:
Select the daterangemonth is (All) from Filters on this visual.
Select Advanced filtering as the Filter type.
Define the filter to Show items when the valueis on or after1/1/2023Andis before1/1/2024. You can use the calendar icon to pick and select dates.
Select Apply filter.
You see the table updated with the applied daterangemonth filter.
In the Visualizations pane:
Select the Line chart visualization.
A line chart visualization replaces the table while using the same data as the table. Your Power BI Desktop should look like below.
{modal="regular"}
On the Line chart visualization:
Select
.
From the context menu, select Show as a table.
The main view is updated to show both a line visualization and a table. Your Power BI Desktop should look like below.
{modal="regular"}
Tableau Desktop
Select the Sheet 1 tab at the bottom to switch from Data source. In the Sheet 1 view:
Drag the Daterange entry from the Tables list in the Data pane and drop the entry onto the Filters shelf.
In the Filters Field [Daterange] dialog, select Range of Dates and select Next >.
In the Filter [Daterange] dialog, select Range of dates and specify a period of 01/01/2023 - 01/01/2024.
{modal="regular"}
Drag and drop Daterangeday from the Tables list in the Data pane and drop the entry in the field next to Columns.
Select MONTH from the Daterangeday dropdown menu, so that the value is updated to MONTH(Daterangeday).
Drag and drop Occurrences from the Tables (Measure Names) list in the Data pane and drop the entry in the field next to Rows. The value is automatically converted to SUM(Occurrences).
Modify Standard to Entire View from the Fit dropdown menu in the toolbar.
Your Tableau Desktop should look like below.
{modal="regular"}
Select Duplicate from the Sheet 1 tab context menu to create a second sheet.
Select Rename from the Sheet 1 tab context menu to rename the sheet to Graph.
Select Rename from the Sheet 1 (2) tab context menu to rename the sheet to Data.
Ensure that the Data sheet is selected. In the Data view:
Select Show me at the top right and select Text table (upper left top visualization) to modify the content of the Data view to a table.
Drag MONTH(Daterangeday) from Columns to Rows.
Modify Standard to Entire View from the Fit dropdown menu in the toolbar.
Your Tableau Desktop should look like below.
{modal="regular"}
Select New Dashboard tab button (at the bottom) to create a new Dashboard 1 view. In the Dashboard 1 view:
Drag and drop the Graph sheet from the Sheets shelf onto the Dashboard 1 view that reads Drop sheets here.
Drag and drop the Data sheet from the Sheets shelf below the Graph sheet onto the Dashboard 1 view.
Select the Data sheet in the view and modify Entire View to Fix Width.
Your Tableau Desktop should look like below.
{modal="regular"}
Looker
In the Explore interface of Looker, ensure you do have a clean setup. If not, select
Remove fields and filters.
Select + Filter underneath Filters.
In the Add Filter dialog:
Select β£ Cc Data View
From the list of fields, select β£ Daterange Date then Daterange Date.
{modal="regular"}
Specify the Cc Data View Daterange Date filter as is in range2023/01/01until (before)2024/01/01.
From the left Cc Data View rail,
Select β£ Daterangemonth Date, then Month from the list of DIMENSIONS.
Select Count underneath MEASURES in the left rail (at the bottom).
Select Run.
Select β£ Visualization to display the line visualization.
You should see a visualization and table similar as shown below.
{modal="regular"}
Jupyter Notebook
Enter the following statements in a new cell.
code language-python
import seaborn as sns
import matplotlib.pyplot as plt
data = %sql SELECT daterangemonth AS Month, COUNT(*) AS Events \
FROM cc_data_view \
WHERE daterange BETWEEN '2023-01-01' AND '2024-01-01' \
GROUP BY 1 \
ORDER BY Month ASC
df = data.DataFrame()
df = df.groupby('Month', as_index=False).sum()
plt.figure(figsize=(15, 3))
sns.lineplot(x='Month', y='Events', data=df)
plt.show()
display(data)
Execute the cell. You should see output similar to the screenshot below.
{modal="regular"}
RStudio
Enter the following statements between ```{r} and ``` in a new chunk.
