The SQL Report Builder allows you to directly query your data warehouse, view the results, and quickly transform them into a chart. The best part about using SQL to build reports is that you don’t have to wait on update cycles to iterate on columns you create. If the results don’t look right, you can quickly edit and rerun the query until things match your expectations.
You must have Admin permissions to access the SQL Report Builder.
In this video:
If you are new to Commerce Intelligence, the Adobe Commerce Intelligence User Guide provides a full overview for driving better business insights and results through data aggregation, analysis, and visualization.
Hi, I’m Jay and I’m an analyst here at RJMetrics. In this video, I’m going to show you how to use the SQL Report Builder. Now the SQL Report Builder is only available for users who have admin level permissions but even if you don’t currently have that you still may find this video useful, let’s get started. So first we’ll open the SQL Report Builder. The SQL Report Builder uses Redshift syntax which is similar to Postgres and allows you to query your data warehouse using select statements including aggregate functions and joints. Enter your query into the SQL editor and refer to our guidelines for query optimization if needed.
If you’ve already written your query that’s great, but you can also use the buttons at the top of the sidebar to toggle between lists of tables, columns and metrics available for use in your queries. If you don’t see what you’re looking for you can also try searching for it using the search bar.
Keep in mind that existing metrics are available here for borrowing the existing logic but SQL reports aren’t dependent on the metrics themselves. So if you’ve updated a metric definition remember that the change won’t be reflected here. It helps if you have a specific query in mind when using the SQL Report Builder. For our example, let’s say I want to see the number of customers acquired segmented by UTM source. I’ll add my query in the SQL Report Builder text box and then click run.
You’ll notice that while the query is running a green dot and running query will appear on the right. When the query is completed, you’ll see information about its run time and the number of rows returned. The results are displayed in the table below.
If something looks wrong in the results you can easily edit the query and rerun it. After you finish editing, you can move on to either creating a visualization or saving your work to a dashboard. First, let’s save this table to a dashboard so we can access this data in tabular format in the future. Before you can save your work you’ll have to give the report a name. Remember to follow the best practice guidelines for naming and choose something that clearly conveys what the report is. I’ll call this report, Customers by UTM Source. Then click the save button and select the report type. Here we’ll want to save the table and then select the dashboard to save the report too. I’ll save this to sandbox and click the save to dashboard button. Next, let’s take a look at how to create a visualization of this query and save that report. First, click the chart tab in the results pain, by default everything that’s selected is shown. You can customize the report by selecting the series or the column you want to measure which in this case is the total.
The category or a column you want to use to further segment your data. We don’t need to use an additional category in this analysis, but here’s where you do it. And the labels for the data point which here is the UTM source name.
Once the chart is displaying the way you’d like you can use the same process to save the report. Remember that each report saves based on the title. So to save a version of the chart report we will need to give it a new name and save the report to the dashboard.
And that’s how you use the SQL Report Builder. For more information about this feature or about SQL in general, check out our help center. Thanks for watching. -