--- title: "Using Interactive Queries" date: "2024-10-16T02:21:29+00:00" summary: "Explore CDP data with interactive queries. Create advanced analytics, visualizations, and dashboards for data-driven insights." image: type: "page" url: "/customer-data-platform/using-interactive-queries" id: "62bd85c2-7cde-4fb2-80f8-33643e9ad6ba" --- 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](/customer-data-platform/getting-started/signin-cdp) 2 Create queries * [Creating queries](#creating-queries) * [Querying the data](#querying-data) * [Running the queries](#running-queries) * [Debugging the queries](#debugging-queries) * [Adding visualizations](#adding-visualizations) * [Scheduling a refresh query report](#scheduling-refresh-query-report) 3 Create a dashboard * [Creating a dashboard](#creating-dashboard) * [Adding text boxes](#adding-text-boxes) * [Adding widgets](#adding-widgets) * [Managing dashboard functions](#managing-dashboard-functions) 4 Create an alert [Creating an alert](#creating-alerts) 5 Ways to improve query performance [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_new-query-rename.png](https://acquia.widen.net/content/fe5dc4b5-365c-4c21-8de6-805c53b3867e/web/cdp_new-query-rename.png) CDP lists the tables and fields on the left and provides a query command line on the right for SQL commands. ![cdp_query.png](https://acquia.widen.net/content/24f59e7b-5263-4b4c-a9c6-e93f831708ec/web/cdp_query.png) 4. To create a basic query, use the following SQL command: ![cdp_simple-query.png](https://acquia.widen.net/content/fa6bcfbf-e003-4eeb-8e99-cb79767a29f9/web/cdp_simple-query.png) 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 = 'user@acquia.com'` for refinement.  ![cdp_query-execution.png](https://acquia.widen.net/content/a8f8d6f8-ebcb-4aa9-b2e0-8128359cecd0/web/cdp_query-execution.png?w=220&itok=DfHsV0iv) 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. ![cdp_new-visualization.png](https://acquia.widen.net/content/e7d0e6c3-536c-422f-8427-27c865ab3d36/web/cdp_new-visualization.png?w=220&itok=2RL8pjsv) 3. Click **Save**. CDP adds a new table beside your default table visualization. 4. Click **Editing Visualization** to modify a visualization tab.  ![cdp_editing-visualization.png](https://acquia.widen.net/content/e9166574-28c4-43f1-8a6c-76dfe071d364/web/cdp_editing-visualization.png?w=220&itok=CXawW9a4) 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.  ![cdp_visualization-ellipsis-menu.png](https://acquia.widen.net/content/6b7eebcd-63d7-48e4-bb8c-bf93d500cacc/web/cdp_visualization-ellipsis-menu.png?w=220&itok=_vArP6ZG) Scheduling a refresh query report --------------------------------- Use the following steps to set up a refresh schedule: 1. For **Refresh Schedule**, click **Never**.  ![cdp_refresh-schedule.png](https://acquia.widen.net/content/faf552f1-123c-4a8f-8ae5-41cfc11c5722/web/cdp_refresh-schedule.png?w=220&itok=bajAxfT3) 2. Select an option from **Refresh Every** to schedule updates hourly, daily, or weekly.  ![cdp_ref-schedule.png](https://acquia.widen.net/content/e3b02cd3-3381-49a4-b058-38a003ea4c23/web/cdp_ref-schedule.png?w=220&itok=g0xluxVU) 3. Set **On-time** by selecting the required execution time.  ![cdp_ref-schedule-time.png](https://acquia.widen.net/content/d45815f5-eca6-4a04-87db-7045d27c547c/web/cdp_ref-schedule-time.png?w=220&itok=29IkHLC3) 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.  ![cdp_new-dashboard.png](https://acquia.widen.net/content/e1f4ef59-4e4c-45ef-ba62-0cfc36558e76/web/cdp_new-dashboard.png?w=220&itok=9tv4liyG) 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. ![cdp_dashboard-test.png](https://acquia.widen.net/content/c389427e-9dbe-4c85-b3a8-05fe587359b6/web/cdp_dashboard-test.png) 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](https://www.markdownguide.org/cheat-sheet/#basic-syntax "https://www.markdownguide.org/cheat-sheet/#basic-syntax") or [Extended Syntax](https://www.markdownguide.org/cheat-sheet/#extended-syntax "https://www.markdownguide.org/cheat-sheet/#extended-syntax"). 3. Type in the textbox to see a preview in the **Preview** section.  ![cdp_add-textbox.png](https://acquia.widen.net/content/79a62d1f-2ee9-4b31-b220-90de5d2a9aef/web/cdp_add-textbox.png?w=480&itok=IPlev1ii) 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).   ![cdp_add-widget.png](https://acquia.widen.net/content/d17d8c09-2f72-44b3-8062-de8cfe229bbc/web/cdp_add-widget.png?w=480&itok=Di_-RdBl) 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.  ![cdp_dashboard-function.png](https://acquia.widen.net/content/d460f1f8-a41e-4e9d-adf4-0d3fe0620c6e/web/cdp_dashboard-function.png?w=220&itok=_SFDisnl) 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  ![cdp_set-refresh-schedule.png](https://acquia.widen.net/content/6e41acfe-80a5-418a-bf93-4aa1ae35692e/web/cdp_set-refresh-schedule.png?w=220&itok=QX7UzW4v) 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.  ![cdp_new-alert.png](https://acquia.widen.net/content/f59b463a-397f-4da3-878d-a61af8df1e71/web/cdp_new-alert.png?w=480&itok=TxN6HywC) 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.**  ![cdp_configure-alerts.png](https://acquia.widen.net/content/f5a386b9-b82c-4a19-9350-d2af3dc75d77/web/cdp_configure-alerts.png?w=480&itok=6lOvU01K) 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. 4. Click the alert name to edit alerts.  ![cdp_notifications.png](https://acquia.widen.net/content/4e04648a-6bb0-4345-9efd-9aec92a7a43f/web/cdp_notifications.png?w=1090&itok=cPRcNLTb) **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 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