This page provides information about how to use the Interactive Query feature for the following scenario:

• Data engineers want to use the free-form SQL for advanced analytics by exploring the Customer Data Platform (CDP) data model.

Data engineers can use the following tasks to navigate the data model, thoroughly explore data, create advanced queries, and refine analytics strategies to inform decision-making:

Creating queries

To create a query:

1. Click Interactive Queries.

2. Click Create > Query.

3. Enter a name for your query.

   

   CDP lists the tables and fields on the left and provides a query command line on the right for SQL commands.

   

4. To create a basic query, use the following SQL command:

   

Querying the data

The two approaches to query the data are:

1. Selecting from tables:

   1. Click the Customer table from the list to view its fields.

   2. Locate and hover over the email field, and then click the forward (>>) icon to add to the query.

   3. Repeat steps a through c for additional fields.

2. Direct command-line entry:

   1. Type field names directly in the command line.

   2. Use auto-fill for suggestions.

   3. Add SQL lines like AND email = '[email protected]' for refinement. 

      
   4. Replace the placeholder email with an active user's email before execution as the sample email address fails your queries execution.

Running the queries

Use the following options to execute your queries:

• Execute: Click to run all queries.
• Execute Selected: Select and highlight and select for specific queries.
• Save: Click to save for future use.

Debugging the queries

Use the following steps to troubleshoot your queries:

1. Identify failed queries by highlighting and using Execute Selected for specific results.
2. View the selected fields and values based on the query execution.
3. Publish queries to enable scheduling or dashboard inclusion as CDP does not process unpublished queries.

Adding visualizations

Use the following steps to add and customize visualizations:

1. Click Execute to run the query.

   CDP displays the selected fields and their values based on the query execution. By default, visualization is displayed in a table view.

2. Click + New Visualization to create various visualizations using the Visualization Type, based on query results.

   

3. Click Save.

   CDP adds a new table beside your default table visualization.

4. Click Editing Visualization to modify a visualization tab. 

   

5. Click the ellipsis menu to access additional features:

   1. Add to Dashboard: Add it directly to a Dashboard.

   2. Embed Elsewhere: Embed the visual in another query.

   3. Download as CSV File: Download and export as a CSV file.

   4. Download as Excel File: Download and export as an Excel file. 

      

Scheduling a refresh query report

Use the following steps to set up a refresh schedule:

1. For Refresh Schedule, click Never. 

   

2. Select an option from Refresh Every to schedule updates hourly, daily, or weekly. 

   

3. Set On-time by selecting the required execution time. 

   
4. Select an end option: Never or On.

For more information, click ? to access the Knowledge Base.

Creating a dashboard

Use the following steps to create a dashboard:

1. Click Interactive Queries.
2. Click Create > Dashboard.

3. Enter a name for your dashboard. 

   

4. Click Save.

   Select Use Dashboard Level Filters to apply filters across the dashboard, simplifying data analysis. From your Home Dashboard, click Add Widget to add saved queries and organize or search reports. Use Add Textbox for context or group details.

Adding text boxes

Use the following steps to add text boxes:

1. Click Add Textbox.

2. Name the reporting session and add a list of query reports.

   For example, Report 1.

   Refer to the Support Markdown for guidance to style your text using Basic Syntax or Extended Syntax.

3. Type in the textbox to see a preview in the Preview section. 

   

4. Click Add to Dashboard to save.

Adding widgets

Use the following steps to add widgets:

1. Click Add Widget.

2. Search for your query.

3. Select the required query name. 

4. Choose a visualization from the available query report options, such as Table (default).  

   

5. Click Add to Dashboard.

6. Click Done Editing.

Managing dashboard functions

Use the following steps to manage dashboard functions:

1. Click the ellipsis menu to:

   • Edit: Edit the dashboard.

   • Archive: Archive the dashboard. 

     

2. To set a refresh schedule, click the arrow and choose from the dropdown options:

   • 12 hours: Recurring refresh every 12 hours

   • 1 day: Daily recurring refresh 

     

3. Click Publish to share the dashboard or make it visible to others.

   For more information, click the Help (?) icon to check the Knowledge Base.

Creating an alert

Use the following steps to create an alert:

1. Click Interactive Queries.
2. Click Create > Alert.

3. Search for your query. 

   
4. Configure your alert using the settings panel.

   1. Name: Enter a name for the alert.

   2. Value Column: Select the column in your query result to monitor.

   3. Op: Select greater than, less than, or equal to.

   4. Reference: Enter the absolute value to compare against your Value Column. The most recent query result auto-populates as the Value.

   5. Rearm seconds: Enter a value to set how frequently you receive notifications when your query meets the Alert criteria and remains unchanged.

      For a query executing every 24 hours, setting Rearm Seconds between 1 and 86,400 results in daily notifications if the Alert status is TRIGGERED.

   6. Click Save. 

      
5. Set the notification email:
   1. Add or remove email addresses as required.
   2. Click Save to apply changes and receive a confirmation notification.
   3. Navigate to the Mail Alerts page to view all your alerts.

For more information, Click ? to check the Knowledge Base.

Improve query performance

This section explains using mastercustomerid2 in CDP to optimize queries for anonymous transactions in transactionsummary.

• In the Customer Data Platform (CDP), the transactionsummary table includes mastercustomerid and mastercustomerid2 attributes.
• For anonymous transactions, mastercustomerid is set to -1.
• Higher numbers of anonymous customers can degrade query performance when joining transactionsummary and customersummary tables.
• To improve performance, use mastercustomerid2 instead of mastercustomerid.
• The mastercustomerid2 attribute is a copy of mastercustomerid, but it is NULL when mastercustomerid is -1.
• For better query results, use mastercustomerid2 with a FULL OUTER JOIN to include anonymous transactions.
• In queries, use mastercustomerid2 = NULL instead of mastercustomerid = "-1" in WHERE clauses to target anonymous transactions.

Sample SQL query

SELECT
  customersummary.gender AS "customersummary.gender",
  productsummary.brandname AS "productsummary.brand",
  COUNT(DISTINCT customersummary.mastercustomerid ) AS "all_individuals_count",
  COUNT(DISTINCT transactionsummary.mastercustomerid ) AS "buyer_count",
  COUNT(DISTINCT transactionsummary.transactionid ) AS "transaction_count",
  FROM customersummary AS customersummary
  FULL OUTER JOIN transactionsummary AS transactionsummary
     ON transactionsummary.mastercustomerid2 = customersummary.mastercustomerid
  LEFT JOIN productsummary AS productsummary
     ON transactionsummary.productid = productsummary.id
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

Using Interactive Queries

This page provides information about how to use the Interactive Query feature for the following scenario:

• Data engineers want to use the free-form SQL for advanced analytics by exploring the Customer Data Platform (CDP) data model.

Data engineers can use the following tasks to navigate the data model, thoroughly explore data, create advanced queries, and refine analytics strategies to inform decision-making:

No	Steps	Description
1	Sign in to Acquia CDP	Sign in to the CDP user interface
2	Create queries	Creating queries Querying the data Running the queries Debugging the queries Adding visualizations Scheduling a refresh query report
3	Create a dashboard	Creating a dashboard Adding text boxes Adding widgets Managing dashboard functions
4	Create an alert	Creating an alert
5	Ways to improve query performance	Improve query performance

Creating queries

To create a query:

1. Click Interactive Queries.

2. Click Create > Query.

3. Enter a name for your query.

   

   CDP lists the tables and fields on the left and provides a query command line on the right for SQL commands.

   

4. To create a basic query, use the following SQL command:

   

Querying the data

The two approaches to query the data are:

1. Selecting from tables:

   1. Click the Customer table from the list to view its fields.

   2. Locate and hover over the email field, and then click the forward (>>) icon to add to the query.

   3. Repeat steps a through c for additional fields.

2. Direct command-line entry:

   1. Type field names directly in the command line.

   2. Use auto-fill for suggestions.

   3. Add SQL lines like AND email = '[email protected]' for refinement. 

      
   4. Replace the placeholder email with an active user's email before execution as the sample email address fails your queries execution.

Running the queries

Use the following options to execute your queries:

• Execute: Click to run all queries.
• Execute Selected: Select and highlight and select for specific queries.
• Save: Click to save for future use.

Debugging the queries

Use the following steps to troubleshoot your queries:

1. Identify failed queries by highlighting and using Execute Selected for specific results.
2. View the selected fields and values based on the query execution.
3. Publish queries to enable scheduling or dashboard inclusion as CDP does not process unpublished queries.

Adding visualizations

Use the following steps to add and customize visualizations:

1. Click Execute to run the query.

   CDP displays the selected fields and their values based on the query execution. By default, visualization is displayed in a table view.

2. Click + New Visualization to create various visualizations using the Visualization Type, based on query results.

   

3. Click Save.

   CDP adds a new table beside your default table visualization.

4. Click Editing Visualization to modify a visualization tab. 

   

5. Click the ellipsis menu to access additional features:

   1. Add to Dashboard: Add it directly to a Dashboard.

   2. Embed Elsewhere: Embed the visual in another query.

   3. Download as CSV File: Download and export as a CSV file.

   4. Download as Excel File: Download and export as an Excel file. 

      

Scheduling a refresh query report

Use the following steps to set up a refresh schedule:

1. For Refresh Schedule, click Never. 

   

2. Select an option from Refresh Every to schedule updates hourly, daily, or weekly. 

   

3. Set On-time by selecting the required execution time. 

   
4. Select an end option: Never or On.

For more information, click ? to access the Knowledge Base.

Creating a dashboard

Use the following steps to create a dashboard:

1. Click Interactive Queries.
2. Click Create > Dashboard.

3. Enter a name for your dashboard. 

   

4. Click Save.

   Select Use Dashboard Level Filters to apply filters across the dashboard, simplifying data analysis. From your Home Dashboard, click Add Widget to add saved queries and organize or search reports. Use Add Textbox for context or group details.

Adding text boxes

Use the following steps to add text boxes:

1. Click Add Textbox.

2. Name the reporting session and add a list of query reports.

   For example, Report 1.

   Refer to the Support Markdown for guidance to style your text using Basic Syntax or Extended Syntax.

3. Type in the textbox to see a preview in the Preview section. 

   

4. Click Add to Dashboard to save.

Adding widgets

Use the following steps to add widgets:

1. Click Add Widget.

2. Search for your query.

3. Select the required query name. 

4. Choose a visualization from the available query report options, such as Table (default).  

   

5. Click Add to Dashboard.

6. Click Done Editing.

Managing dashboard functions

Use the following steps to manage dashboard functions:

1. Click the ellipsis menu to:

   • Edit: Edit the dashboard.

   • Archive: Archive the dashboard. 

     

2. To set a refresh schedule, click the arrow and choose from the dropdown options:

   • 12 hours: Recurring refresh every 12 hours

   • 1 day: Daily recurring refresh 

     

3. Click Publish to share the dashboard or make it visible to others.

   For more information, click the Help (?) icon to check the Knowledge Base.

Creating an alert

Note

The Alert function is not intended to generate email notification reports. It is designed to notify users about the changes in monitored dataset values based on predefined criteria.

Use the following steps to create an alert:

1. Click Interactive Queries.
2. Click Create > Alert.

3. Search for your query. 

   
4. Configure your alert using the settings panel.

   1. Name: Enter a name for the alert.

   2. Value Column: Select the column in your query result to monitor.

   3. Op: Select greater than, less than, or equal to.

   4. Reference: Enter the absolute value to compare against your Value Column. The most recent query result auto-populates as the Value.

   5. Rearm seconds: Enter a value to set how frequently you receive notifications when your query meets the Alert criteria and remains unchanged.

      For a query executing every 24 hours, setting Rearm Seconds between 1 and 86,400 results in daily notifications if the Alert status is TRIGGERED.

   6. Click Save. 

      
5. Set the notification email:
   1. Add or remove email addresses as required.
   2. Click Save to apply changes and receive a confirmation notification.
   3. Navigate to the Mail Alerts page to view all your alerts.

Alert criteria Status	Details
TRIGGERED	Your query met the criteria set in your alert configuration. If your alert is configured to activate when the value of "cats" exceeds 1500, it remains triggered as long as it stays above 1500.
OK	The most recent query execution did not meet your alert's criteria. This does not imply that the alert was never triggered before. If the "cats" value is now 1470, your alert status is OK.
UNKNOWN	Redash does not have enough data to evaluate the alert criteria. You see this status immediately after creating your alert until the query executes. This status also appears if there is no data in the query result or if the most recent query result does not contain the configured Value Column.

For more information, Click ? to check the Knowledge Base.

Improve query performance

This section explains using mastercustomerid2 in CDP to optimize queries for anonymous transactions in transactionsummary.

• In the Customer Data Platform (CDP), the transactionsummary table includes mastercustomerid and mastercustomerid2 attributes.
• For anonymous transactions, mastercustomerid is set to -1.
• Higher numbers of anonymous customers can degrade query performance when joining transactionsummary and customersummary tables.
• To improve performance, use mastercustomerid2 instead of mastercustomerid.
• The mastercustomerid2 attribute is a copy of mastercustomerid, but it is NULL when mastercustomerid is -1.
• For better query results, use mastercustomerid2 with a FULL OUTER JOIN to include anonymous transactions.
• In queries, use mastercustomerid2 = NULL instead of mastercustomerid = "-1" in WHERE clauses to target anonymous transactions.

Sample SQL query

copy

SELECT
  customersummary.gender AS "customersummary.gender",
  productsummary.brandname AS "productsummary.brand",
  COUNT(DISTINCT customersummary.mastercustomerid ) AS "all_individuals_count",
  COUNT(DISTINCT transactionsummary.mastercustomerid ) AS "buyer_count",
  COUNT(DISTINCT transactionsummary.transactionid ) AS "transaction_count",
  FROM customersummary AS customersummary
  FULL OUTER JOIN transactionsummary AS transactionsummary
     ON transactionsummary.mastercustomerid2 = customersummary.mastercustomerid
  LEFT JOIN productsummary AS productsummary
     ON transactionsummary.productid = productsummary.id
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500