Using these starter tips for superQuery, you'll see an immediate increase in your productivity.
Check them out below:
#1 — Give your query tabs and boards identifiable names
As you create more query tabs and boards, it will be easier for you to find them if you label them from the start.
The names should be descriptive enough that it is obvious to you what query the tab contains — or in the case of Boards, which queries the board contains.
#2 — Group related queries into boards
Because your query tabs are persistent — they won't disappear if you refresh your page or close your session — there is no need for Saved Queries.
However, there is a need for organizing your queries — and that's what Boards are for.
Boards are like folders, where you can organize your queries by topic / theme / project. Think of them as a filing & management system for your queries.
For example, if you're exploring user metrics for your company AND separately working on a personal project, you wouldn't place all those queries into one board.
One board would be called "User Metrics" and the other "Personal Project".
#3 — Visualize your query result using Charts before exporting to your BI tool
Rather than exporting to Google Sheets, you can instantly visualize your query result with one click using Charts.
This time-saving trick will help you quickly identify whether something is wrong with your results — helping you avoid going back-and-forth between BigQuery and your BI tool.
With Charts, you'll know with certainty that, when doing more complex analysis in your BI tool, your visualization won't have any anomalies
#4 — Use variables in your queries as much as possible
Variables are simply placeholders for values that can change. They make your queries more dynamic and allow you to perform quick edits on your query without modifying the SQL itself.
If you're constantly modifying field values (ex. item id, user id, user events, dates, etc.), variables will save you TONS of time.
Creating a variable is incredibly easy — in fact, there is no SQL required when creating variables in superQuery.
Simply choose a data type, and give your variable a name and initial value.
Then insert the variable into your query in place of a literal value. The variable "holds" whatever value you assigned it.
Changing your variable's value is also easy. Just enter a new value at the top, as shown below. Rather than performing a CTRL-F and selecting "Replace All", you just have to insert a variable and change its value at the top once.
#5 — Connect your GitHub repository to superQuery
For many, code management and tracking changes to queries is important. Most of the time, however, this is done manually.
Too much time is spent comparing code, trying to figure out what part of the query was changed.
Every time you execute a query, it will get backed up and automatically version-controlled. They are also catalogued for you in a simple hierarchy (Board > Tab).
Never lose a query again when connecting your GitHub repo to superQuery.
#6 — Export your SQL to Google Colab in one click
If you're a data scientist, or simply want to quickly analyze your query with python, keep reading on.
Normally, to explore BigQuery data with python you would have to:
- Use the BQ API in your Notebook environment
- Download your results as a CSV, then load the CSV from Google Cloud Storage, Drive, or other external sources
This can get a bit messy sometimes. Now you can do this with zero code and in just one click with superQuery.
To export to Colab, click on the "Jupyter" button above your results grid to export your SQL directly into Google Colab.
For a quick preview of the flow, check out the video below:
When Colab opens, you'll already have 5 pre-filled cells that, once run:
- Install and import the superQuery and pivot table module.
- Authenticate your superQuery account
- Execute your query
- Display your results in a pivot table
- Show various statistics of your query.
Once exported into Colab, you can hack your results however you like.
#7 — Connect your BI tool to superQuery for full query optimization
Queries executed via BI tools can account for up to 80% of your company's monthly BigQuery bill because your entire organization is repeatedly refreshing dashboards, which are each made up of many queries.
Most of that can be reduced by optimizing these queries — minimizing the data scanned without affecting the results.
Connecting your BI tool to superQuery's query optimization engine will do exactly this. It leverages AI and enhanced caching so that your team can take full advantage of BigQuery's power at peak efficiency.
The result? Your dashboards will refresh faster and your team will see a significant reduction in query costs (30-60%).
Select your BI tool below to start optimizing :
- Work seamlessly across multiple queries with Query Tabs
- Organize your queries with Boards
- Generate one-click visualizations with Charts
- Write dynamic queries using variables
- Export your Query to Google Colab
- Connect your BI Tool to superQuery
- Automatically backup and version-control your queries to GitHub