SQL for Marketers and Growth Hackers

Everything you need to know about using SQL for analytics, marketing and growth

hacking

What is SQL?

SQL is simply a language that makes it easy to pull data from your application’s database.

What can you do with SQL?

Answer questions!

Life without SQL

Without SQL…. •  You annoy technical people on your

team

•  Hack together rough approximations for hours

Life with SQL

“Starting in 2013, after the CMO realizes that he/she does not have the skill sets in place for data analytics proficiency, 50% of new marketing hires will have technical backgrounds.” - quote from IDC research of CMOs

Why is SQL important to me?

SQL will help you see, understand and improve your company’s metrics.

Why is SQL important to me?

SQL will help you see, understand and improve your company’s metrics. It allows you to access data other analytics tools cannot (Kissmetrics, Mixpanel, Google Analytics, etc.)

Do you know the answers to some very basic questions about your business?

Take a Minute

Do you know the answers to some very basic questions about your business? – Are you getting more customers or less this

month compared to previous?

Take a Minute

Do you know the answers to some very basic questions about your business? – Are you getting more customers or less this

month compared to previous? – What percentage of customers are active

every week on your platform?

Take a Minute

Do you know the answers to some very basic questions about your business? – Are you getting more customers or less this

month compared to previous? – What percentage of customers are active

every week on your platform? – How many users leave each week (churn)?

Is this trending up or down?

Take a Minute

Do you know the answers to some very basic questions about your business? – Are you getting more customers or less this

month compared to previous? – What percentage of customers are active

every week on your platform? – How many users leave each week (churn)?

Is this trending up or down? – Who are your 100 most active users? Who

are your 100 most valuable customers?

Take a Minute

What can you do with SQL?

•  Make data-driven decisions

•  Run profitable campaigns using data

•  Understand every metric in your business

•  Get superpowers

How we improved churn with the help of SQL

Early Win for You!

Let’s take a second and have you do 2 things to get the most from this course: 1.  Get read-only access to your database

Early Win for You!

Let’s take a second and have you do 2 things to get the most from this course: 1.  Get read-only access to your database

2.  Ask someone technical how to run queries against this database

A WORD OF CAUTION

Only run queries on a read-only or “slave” database

How SQL Works A database works a lot like an Excel spreadsheet. It will contain multiple tables – all with unique names, like “Customers” – with each table containing rows and columns

Why SQL is Better than Excel •  Auto-updating information

•  Easier to query

•  Can visualize data automatically

Example Query

*See  how  this  query  works  by  checking  out  the  example  database  I  pulled  together  at  -­‐  h"p://bitly.com/udemysql  

Example Query

*See  how  this  query  works  by  checking  out  the  example  database  I  pulled  together  at  -­‐  h"p://bitly.com/udemysql  

SQL Queries Select — This command tells SQL what data you want to see. This data needs to be a part of the table you’re accessing.

SQL Queries From — this command tells SQL where to pull the data from. What’s the name of the table you’re using to capture data? Put that after the “from” statement.

SQL Queries Join — This command temporarily joins two tables we’d like to search, so that we can pull data from multiple queries. “Join” tells SQL which table we’d like to join, and the “on” modifier tells SQL which columns in those tables should map to each other.

SQL Queries Where — This statement tells SQL how to access the right data. In this case, we’re telling it to show us users who have gotten errors in production AND who have signed up within a certain time period.

SQL Queries Group by – This statement tells SQL how to group your data when it returns the query result. In this case, data will be grouped by name in the ‘users’ table. Think of this as a massive sort function in Excel.

SQL Queries Order by – This statement tells SQL how to order your data. This can be useful when pulling massive amounts of information from the database.

SQL Queries Limit – This tells SQL how much data to return. In our case, we’ll only receive the first 15 results that fit the results of this query.

SQL Queries Functions •  Avg() – returns the average value

SQL Queries Functions •  Avg() – returns the average value •  Count() – returns the number of rows

SQL Queries Functions •  Avg() – returns the average value •  Count() – returns the number of rows •  First() – returns the first value of a

column or row

SQL Queries Functions •  Avg() – returns the average value •  Count() – returns the number of rows •  First() – returns the first value of a

column or row •  Last() – returns the last value

SQL Queries Functions •  Avg() – returns the average value •  Count() – returns the number of rows •  First() – returns the first value of a

column or row •  Last() – returns the last value •  Max() – returns the largest value

SQL Queries Functions •  Avg() – returns the average value •  Count() – returns the number of rows •  First() – returns the first value of a

column or row •  Last() – returns the last value •  Max() – returns the largest value •  Min() – returns the smallest value

SQL Queries Functions •  Avg() – returns the average value •  Count() – returns the number of rows •  First() – returns the first value of a

column or row •  Last() – returns the last value •  Max() – returns the largest value •  Min() – returns the smallest value •  Sum() – returns the sum

You know SQL!

Where to Write Queries?

Depends on your stack:

Where to Write Queries?

Depends on your stack: •  phpMyAdmin

Where to Write Queries?

Depends on your stack: •  phpMyAdmin •  Heroku Postgres Dataclips

Where to Write Queries?

Depends on your stack: •  phpMyAdmin •  Heroku Postgres Dataclips

Where to Write Queries?

Depends on your stack: •  phpMyAdmin •  Heroku Postgres Dataclips •  Eclipse SQL Explorer

Where to Write Queries?

Depends on your stack: •  phpMyAdmin •  Heroku Postgres Dataclips •  Eclipse SQL Explorer •  SQuirreL

Where to Write Queries?

Depends on your stack: •  phpMyAdmin •  Heroku Postgres Dataclips •  Eclipse SQL Explorer •  SQuirreL •  Oracle SQL Developer

Finding Table and Column Names Connect to the database: •  mysql  [-­‐u  username]  [-­‐h  hostname]  database-­‐name  

List all databases (type into MySQL prompt): •  show  databases    Choose the database you want: •  use  <database-­‐name>  

List all tables in the database: •  show  tables  

Describe a table: •  describe  <table-­‐name>  

Example: Finding repeat buyers

Test SQL Database - http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

STOP

How would you construct this query? Try writing it in on your own now.

Example: Finding repeat buyers

   

Understand Your User Demographics

Let’s say you want to answer the question “what countries are most of my users from?” Write a query that pulls that information from the test W3schools database.

Answer: Understand Your User Demographics

   

Cohort Analysis

See how groups of customers use your product

Cohort Analysis

Cohort analysis leads to interesting, actionable data like the below

Closing

That’s it!

Get the SQL creator spreadsheet, downloadable queries and additional

resources at justinmares.com/sql