SQL Insights for extended app reporting

Last update: October 10, 2024

CREATED FOR:

  • Developer
  • User

Use custom SQL queries to effectively extract insights from diverse structured datasets. Technical people can use query pro mode to perform complex analysis with SQL and then share this analysis with non-technical users through charts on your custom dashboard or export them in CSV files. This method of insight creation is well-suited for tables with clear relationships and allows for a greater degree of customization within your insights and filters that can suit niche use cases.

IMPORTANT
Query pro mode is only available to users who have purchased the Data Distiller SKU.

To generate insights from SQL, you must first create a dashboard.

Create a custom dashboard

To create a custom dashboard, select Dashboards from the left navigation panel to open the Dashboards workspace. Next, select Create dashboard.

The Dashboard inventory with Create dashboard highlighted.

The Create dashboard dialog appears. There are two options from which to choose your dashboard creation method. To create your insights you can either use an existing data model with the Guided design mode or your own SQL with the Query pro mode.

Using an existing data model has the benefits of providing a structured, efficient, and scalable framework tailored to your specific business needs. To learn how to create insights from an existing data model, refer to the custom dashboard guide.

Insights generated from SQL queries offer far greater flexibility and customization. Technical people can use query pro mode to perform complex analysis on SQL and then share this analysis with non-technical users through this dashboard capability. Select Query pro mode followed by Save.

NOTE
Once you make a selection, you cannot change this selection within that dashboard. Instead, you must create a new dashboard with a different dashboard creation method.

The Create dashboard dialog with Query pro mode and Save highlighted.

Query pro mode overview

Query pro mode is an SQL editor-based workflow that guides you through the process of generating insights with custom SQL queries in the Adobe Experience Platform UI. Before you can generate insights with custom SQL queries, you must first create a dashboard.

Compose SQL

Once you have chosen to create a dashboard with query pro mode, the Enter SQL dialog appears. Select a database (insights data model) to query from the dropdown menu, and input a suitable query for your dataset in the query pro editor.

NOTE
Query pro mode is only available to users who have purchased the Data Distiller SKU. The Guided design mode is available to all users to create insights from an existing data model.

See the Query Editor user guide for information on its UI elements.

The Enter SQL dialog with the dataset dropdown menu and run icon highlighted, The dialog has a populated SQL query and the query parameters tab displayed.

Query parameters

To include global or date filters your query must use query parameters. When composing your statement in query pro mode, you must provide sample values if your query uses query parameters. The sample values allow you to execute the SQL statement and build the chart. Note that the sample values you provide when composing your statement are replaced by the actual values you select for the date or global filter at runtime.

IMPORTANT
If you want to use a global filter, you must place a query parameter in your SQL and then link that query parameter to the global filter in the widget composer. In the screenshot below, CONSENT_VALUE_FILTER is used in the SQL as a query parameter for a global filter. See the global filter documentation for more information on how to do this.

To execute your query, select the run icon ( The run icon. ). The Query Editor displays the results tab. Next, to confirm your configuration and open the widget composer, select Select.

TIP
If your query uses query parameters, run the query once to pre-populate all the query parameter keys used. The query will fail but the UI automatically displays the Query parameters tab and lists all included keys. Add the appropriate values for your keys.

The Enter SQL dialog with SQL input, the results tab displayed, and Select highlighted.

Populate widget

The widget composer is now populated with the columns from the executed SQL. The type of dashboard is indicated in the top left, in this case it is Manual SQL Entry. Select the pencil icon ( A pencil icon. ) to edit the SQL at any point.

TIP
The available attributes are columns taken from the executed SQL.

To create your widget, use the attributes listed in the Attributes column. You can use the search bar to look for attributes or scroll the list.

The widget composer with the creation method and attribute column highlighted.

Add attributes

To add an attribute to your widget, select the plus icon ( A plus icon. ) next to an attribute name. The dropdown menu that appears allows you to add an attribute to the chart from the options determined by your SQL. Different chart types have different options, such as an X and Y axis dropdown.

In this donut chart example, the options are size and color. Color breaks down the donut chart results, and the size is the actual metric used. Add an attribute to the Color field to split the results into different colors based on their composition of that attribute.

TIP
Select the up and down arrow icon ( The up and down arrow icon. ) to switch the arrangement of the X and Y axis on bar or line charts.

The widget composer with the add-icon dropdown and switch arrows highlighted.

To change the type of graph or chart of your widget, select from the available options of the Marks dropdown. The options include Line, Donut, Big number, and Bar. Once selected, a preview visualization of your widget’s current settings is generated.

The widget composer with the widget preview highlighted.

Advanced table attributes

To apply automatic sorting capabilities for any or all columns in your tables, select Edit to edit your entire dashboard.

A custom dashboard with Edit highlighted.

Select the ellipsis (...) in the table chart where you want to add column sorting, then select Edit.

A table showing the ellipsis menu with Edit highlighted.

To enable sorting for any column, check the Sortable boxes.

Table edit page with sortable check boxes highlighted.

Select the properties icon ( The properties icon. ) in the right rail to open the Properties panel. In the Properties panel, use the drop down to select the Default sort column, then use the drop down to select the Sort direction. Finally, select Save and close.

The widget composer with the properties icon, default sort, sort direction and save and close highlighted.

To learn more about using the sort, resizing columns, and pagination features, refer to View more.

Widget properties

Select the properties icon ( The properties icon. ) in the right rail to open the properties panel. In the Properties panel, enter a name for the widget in the Widget title text field. You can also rename various aspects of your chart.

NOTE
The specific fields available in the properties sidebar vary depending on the chart type you are editing.

The widget composer with the properties icon and Widget title field highlighted.

Save your widget

Saving in the widget composer saves the widget locally to your dashboard. If you wish to save your work and resume later, select Save. A tick icon underneath the widget name indicates that the widget has been saved. Alternatively, when you are satisfied with your widget, select Save and close to make the widget available to all other users with access to your dashboard. Select Cancel to abandon your work and return to your custom dashboard.

The widget composer with Save, Widget saved, and Save and close highlighted.