Data Explorer with DAVE

This article explains the Data Explorer in Insights: what it is, how to write and run queries against your reporting data, how to turn results into charts, and how to save, share and export your work.

What is the Data Explorer?

The Data Explorer lets you query your StoreFeeder reporting data directly from your browser using SQL, without installing a database client. You can browse every table and column available to you, run your own queries, view the results as a table or a chart, and export them to CSV.

It reads from the same Reporting Database you can connect to with tools such as pgAdmin, so a query that works in one works in the other.

Who can use it

The Data Explorer is part of Insights Pro. To see Data explorer in the menu, both of the following must be true:

• Your StoreFeeder account has Insights Pro.
• You are logged in as an Admin user.

If you'd like to add Insights Pro to your account, contact our Sales team.

Opening the Data Explorer

Go to Insights > Data explorer.

The page has two tabs:

• Query — where you browse tables, write and run queries, and view results.
• Data dictionary — a searchable reference describing every table and column.

Switching between the tabs keeps your query, results and chart exactly as you left them.

Finding the data you need

The Tables list

The Tables panel on the left of the Query tab lists every table you can query, with the number of columns in each. From here you can:

• Search tables and columns — type at least the minimum number of characters shown under the search box.
• Expand a table to see its columns and their data types.
• Click a column name to insert it into the editor at your cursor.
• Click the eye icon (Select the first 100 rows) to preview a table straight away.
• Hover over the information icon to read the table's description.

The Data dictionary

The Data dictionary tab describes what each table and column means, which tables join to which, and a set of Business rules (applies to all tables) — for example, how particular values should be interpreted. Search it using Search tables, columns and descriptions, and click Query this table to jump back to the Query tab with that table ready to explore.

Some columns may show No description yet. The column names and data types are always read live from your Reporting Database, even where a description hasn't been written.

Query templates

If you're not sure where to start, click Query templates in the Query panel. This opens a searchable list of ready-made queries. Selecting one loads it into the editor and runs it, so you can adapt it to your needs.

Writing and running a query

Type your SQL into the Query editor. The editor suggests table and column names as you type. When you're ready, click Run or press Ctrl+Enter.

The Data Explorer uses PostgreSQL syntax. Your query must:

• Be a single SELECT statement (a WITH … SELECT is fine).
• Not contain a semicolon — remove any ; at the end.
• Only read data. Statements such as INSERT, UPDATE or DELETE are refused.
• Finish within 30 seconds.

Asking DAVE to write the SQL

If you'd rather not write SQL yourself, you can describe what you want in plain English and let DAVE write it:

1. In the Query panel, switch from SQL to Ask DAVE.
2. Type your question in What would you like to ask DAVE? — for example, "Which 10 products sold the most units in the last 30 days, and how much did they make?"
3. Click Write SQL with DAVE, or press Ctrl+Enter.
4. DAVE puts the SQL in the editor with a short explanation. Check it looks right, then click Run.

DAVE never runs the query for you — you always see the SQL first. Questions can be up to 1,000 characters. The more specific you are about dates, channels or the columns you want, the better the result.

Working with your results

Once your query runs, the Results panel shows how many rows came back and how long it took. Choose Table or Chart at the top of the panel to switch views.

In the Table view you can:

• Show 50, 100, 500 or 1,000 rows per page and page through the results.
• Click any column heading to sort all rows by that column, not just the page you're looking at.
• Use Search to show only rows containing your search term in any column.

Empty values are shown as null.

Building a chart

Choose Chart in the Results panel. The Data Explorer picks a sensible starting chart — a trend over time if your results contain a date, otherwise a ranking by the first text column — which you can then change.

Chart settings

• Chart type: Column, Bar, Line, Area, Donut or Heatmap.
• X axis: the column to plot along the bottom. For a date column, choose how to group it — Hour, Day, Week, Month, Quarter or Year, or a repeating pattern such as Day of week, Hour of day or Month of year. For a text column, choose whether to show the Top 5, 10, 20 or 50; everything else is grouped into Other.
• Values: what to measure — the number of rows, or the Sum, Average, Min, Max, Count or Count distinct of a column. You can add up to three values.
• Split by: break each bar or line down by another column, such as channel. A split works with one value and shows up to seven series, with the rest grouped as Other.
• Stacking: Side by side, Stacked or 100%.

Charts are calculated from every row your query returns (or every row matching your search), not just the rows on the current table page. Dates on a chart are shown in UK time.

Chart options

Use the chart's buttons to open it Full screen, Download PNG, Export chart data as a CSV of the plotted figures, or View chart SQL to see the query behind the chart — useful if you want to reuse it elsewhere.

Exporting results to CSV

Click Export in the Results panel to download your results as a CSV file. The export includes every row, not just the current page, and respects your current search and sort.

Exports are limited to 2,000,000 rows and two minutes. If an export goes over either limit, it stops and no file is saved — you won't be given an incomplete file.

Saving and sharing queries

To save a query so you can come back to it, open Saved queries in the Query panel and choose Save query. Give it a Name and, optionally, a Description. Tick Share with team to let the other Admin users on your account open it too.

• Your own queries appear under My queries; queries others have shared appear under Team queries.
• The open query's name is shown at the top of the editor. If you change it, an Unsaved changes badge appears.
• Only the person who saved a query can change or delete it. If you open someone else's shared query and want to change it, save it under a new name to keep your own copy.
• Use Manage saved queries to rename, delete, favourite, or stop sharing your queries.
• Each saved query has its own link, so you can bookmark it. If the query is shared, you can also send the link to another Admin user on your account.

You can save up to 500 queries, and names must be unique among your own saved queries.

Limits and things to watch for

• Queries time out after 30 seconds. Narrow the date range, filter earlier in the query, or add a LIMIT.
• Exports are capped at 2,000,000 rows and two minutes. If you need more, split the export by date range.
• Read-only. Only single SELECT queries are accepted.
• Ask DAVE can be wrong. Always check the SQL it writes, and the results, before relying on them.
• Admin users on Insights Pro accounts only. Other users on your account won't see the Data Explorer, or any queries shared from it.

Troubleshooting

I can't see Data explorer in the Insights menu
The Data Explorer is only available to Admin users on accounts with Insights Pro. Check that you're logged in as an Admin user. If you are and still can't see it, your account doesn't have Insights Pro — contact our Sales team to add it.

"Only SELECT queries are allowed."
Your query must start with SELECT (or WITH). The Data Explorer can't run statements that change data or the database.

"SQL must not contain a semicolon."
Remove any ; from your query, including one at the very end. You can only run one statement at a time.

"Only read-only queries are allowed; found …"
Your query contains a word that would change data, such as UPDATE or DELETE. Remove it. If the word is part of a text value you're searching for, put it inside single quotes.

My query times out after 30 seconds
Queries are stopped if they take longer than 30 seconds. Try narrowing the date range, filtering earlier or adding a LIMIT.

An error showing a line and column number
PostgreSQL couldn't understand part of your query. The message tells you where the problem is — check spelling of table and column names in the Tables list or the Data dictionary.

"The export stopped part way, so no file was saved."
The export went over 2,000,000 rows or two minutes. Use Search or a narrower query to reduce the rows, then export again.

"A working query couldn't be written for that question."
DAVE couldn't produce a query that runs. Rephrase the question, or name the table or columns you mean.

"Only the person who saved this query can change or delete it."
The query was shared by someone else. Save it under a new name to keep your own editable copy.

"You already have a saved query with this name."
Choose a different name, or open the existing query and save over it.

Related articles

• The new Insights Engine