In this post, we will cover one of the popular AppExchange Apps called 'Query Studio.'
In Salesforce Marketing Cloud, Queries are one of the methods used for segmentation purposes.
When we create a 'Query Activity' under the Automation Studio and run it, the results get stored in a Data Extension. But each time the query is to be modified or if it needs to be run for testing or troubleshooting purposes, we have to go back to the Data extension to check the results, which might be time-consuming. Also, there is no proper way to resolve the errors (if any) and track if the query has completed the run.
But what if we can write a new query or open an existing one and then run the query to check the results on the same page, without actually storing the data in a Data Extension?
This is where Query studio comes in.
It is an Appexchange app, which is free of cost and can be installed in just a few seconds.
Image Source: Salesforce
Install the App
To install, hover over the 'AppExchange' in your Marketing Cloud account and click on the AppExchange, which will redirect to the site. Search for Query Studio and click on 'Get it now.'
You can also go to this link directly and click on 'Get it now.' Make sure that you are logged in to the right Marketing Cloud account before installing.
Enable the access
Access to Query Studio can be enabled to a specific user or all the users within the Business Unit.
After installing it from the Appexchange, Go to Setup -> Installed Packages -> Query Studio. Under the 'License' tab, select the business unit and select the user(s) as needed.
After setting it up, if your users still don't see the app then suggest they refresh the page or try logging in again.
Open the App
To open it, hover over the AppExchange and select Query Studio.
Features:
1. Run - Once the Query is developed, run it by clicking the 'Run' button or by pressing ctrl+Enter.
2. Time - How much time did the Query take to complete the run. Sometimes the query might time out before 30 min while running it in Query Studio. In such cases, try to re-run.
3. Save As - Once your query is ready, click on the 'Save As' button after which you will see the options:
Enter the Query Name
Choose the Data Extension or enter the Data Extension Name
Select Data Action (Overwrite, Update, Append) to be performed after each query run
Click Submit. This will save the query under Automation Studio -> Activities Tab -> SQL Query
Note - Query can also be copied and pasted in a new query activity created under Automation Studio
4. Open - If you want to open an existing query then click on the 'Open' button wherein you can choose or type in the Query Name. Once selected, the data extension and the data action will be auto-populated.
5. Save - When an existing query is opened, this option would be enabled. You can make the changes (if any) as needed and click on the 'Save' button.
6. Object Explorer - You can choose or type in the Data Extension name to display the field name and its data type. This is very useful while writing new queries and to make sure the exact field names are being used.
7. Export in Contact Builder - After the query run, you will notice this option in the result section. When clicked, it will redirect to the temporary data extension under which the query results are stored.
8. Validate Data - Users have the option of validating their query against a target data extension. This will cross-check for required fields, field lengths, and mismatched field data types.
Things to Note:
1. Fields that contain spaces cannot be the first field after the SELECT keyword in Query Studio.
SELECT Gender, [First Name] - Supported
SELECT [First Name], Gender - Not Supported
2. Sometimes an error might occur due to the cache problems after running a query. If it does, then re-run your query again or refresh the page. Make sure to copy the query before refreshing the page or navigating to another app.
3. SELECT * and COUNT(*) statements are not supported in Query Studio.
4. If you are using 'Salesforce Data Entry Source' in Journey Builder, a Data Extension is automatically created where the fields might be of a format: Object: FieldName OR Object:RelatedObject: FieldName
Consider the Case Object that has a field called 'Case: CaseNumber'
Writing a query in the below format does not work in Query Studio.
SELECT Case: CaseNumber FROM DEName
But as a workaround in the Query Studio for testing, you can use the NEWID() function.
Modified Query:
SELECT NEWID(), Case:CaseNumber FROM DEName
5. QueryStudioResults folder will be created in your account to hold temporary data extensions.
6. The data extensions have data retention set to auto-delete after 24 hours. But the retention settings can be overridden from the 'Contact Builder.'
7. Every Query Studio user will also have a query activity created for them and is used for running one-off queries. It will contain 'InteractiveQuery' in the name.
8. Each time the query is run:
It will create a new temporary data extension automatically with the field names specified in the query
Auto-populate the description with 'Auto-generated by Query Studio. Auto deletes after 24 hours.'
The External Key will be the same as the random data extension name.
The target data extension for the query will be updated.
9. Query Studio results can be exported from the Data Extension and the data can also be imported into it.
NOTE - All the above steps are only limited to Query Studio and not the 'Query Activity' under the Automation studio.
BONUS:
Query Studio is built using the 'Ace Editor.' If you want to learn some cool tricks, then check out this link.
Hope you enjoyed it! Please feel free to contact me with any feedback/suggestions,
See you in the next one!