5 Ways to Get Started Using SQL for App Optimization

rishi sethiNews3 Comments

As we discussed last week, rapid growth to your product requires custom analytics. Custom analytics allows you to answer almost any question that you have about your data and users and enables you to maximize the value of your data.

Getting direct SQL access to your customer data (also known as event data) is particular powerful because you can pull unparalleled insight as to how customers navigate your product. You can identify bottlenecks in your entire customer funnel, from when a user

Getting started with custom analytics for app optimization can be a bit challenging. We’ll outline 5 places to getting started once you’ve decided to enable custom analytics.

Where to Learn

Patrycja Dybka of Vertabelo has a great article on where to learn SQL online.

We strongly recommend the Stanford Database Course and Learn SQL The Hard Way but any of the other courses that Dybka mentions will give you a solid grasp of the basic concepts of SQL. With a little bit of effort, you can be writing simple SQL queries in a day and more complex queries in a week.

Where to Practice

There are dozens of SQL editors online, but we find SQL Fiddle the easiest place to quickly create your own schema and upload sample data. The editor also allows you to see the differences between various versions of SQL.

Where to Find Sample Queries

Querying behavioral data has a few idiosyncrasies compared to querying a typical dataset. Behavioral data is often sequential, and analysts must use the date function quite frequently in order to study behavior over a given time period. It also requires a solid understanding of joins, a more advanced function of SQL.

Fortunately, Amplitude has created a fantastic resource that gives analysts dozens of examples about how to query event data and explanations about how the queries work. We particularly recommend reading over the queries in the Funnels and User Properties sections.

How to Speed Up Queries

A moderately popular app can generate an enormous amount of event data. If the app has 10000 daily active users and the average user does 50 events per day, the events table will grow by 15,000,000 rows per month. Writing queries against all this data can take a long time.

Here are some tips on speeding up queries:

  • Use a dedicated data warehouse. Products like Amazon Redshift, Google BigQuery and Vertica are orders of magnitude faster for querying than MySQL or Postgres.
  • Do not write a query that returns all the rows in your database! If you are writing a query to return a list of users make sure the query selects the correct subset of your users.
  • Utilize subqueries. The team at Persicope has written a great blog post about how to do this.

What BI Tools to Use

SQL gives you an extreme amount of expressive power, but in order to visualize your queries you have to use a third party tool. There are dozens of visualization tools but we’ll outline five common tools that visualize SQL queries:

  • Tableau – Tableau is the best known BI tool, and it enables you to write custom SQL queries.
  • Periscope – A great tool for analysts who are comfortable with SQL, Periscope enables powerful, embeddable visualizations.
  • Mode Analytics – Another SQL visualization tool, Mode also enables developers to analyze their SQL queries in Python easily.
  • Looker – Looker is known for its own querying language, but allows you to write SQL as well.
  • Wagon – Wagon is a SQL editor with a free native Mac or PC app.

The above guide should get you started to implement custom analytics for your data. With a little bit of practice, you will be able to query and visualize your data to pull whatever insights you need.

Please email us if you have any questions implementing custom analytics.