How I Solve Business Problems with SQL

A common question I get asked is how I use analytical tools like SQL to make a business impact. I’m the only data analyst at a small casino gaming startup so I get a lot of experience working with various departments.  In this article, I'll dive into specific, real-world problems I solve using SQL. These examples are based on the complex queries I handle daily. The goal is to show how SQL is used to drive decisions and improve business operations.

Note: While all examples in this blog post use fictional data for educational purposes, they are based on the real requests and projects I’ve done. In reality, the databases are much larger and the queries are more complex (e.g. contains CTEs, subqueries, etc.) but I've simplified it here to focus on teaching core concepts.

Buisness Problems

Below I've given three types of questions or things I have to analyze frequently. Each question belongs to a different department that I work with. The departments I work with are:

• Product

• Marketing

• User Acquisition

All these SQL problems are written using Google BigQuery, which has its own unique SQL syntax.  We use BigQuery exclusively because we use Google Cloud Platform. 

Product 

One of our main goals is to understand how our players behave and the impact of different product features. Specifically like how new games perform or new features in a game.

Question 

What are the most popular games people play within 24 hours of creating an account on our site? For the month of March?  

SQL Approach

First I’d retrieve user IDs and their account creation dates from the users table. Then perform an inner join with the games table on user_id to determine which games are most played within the first 24 hours of account creation. I use COUNT to tally plays per game, highlighting the top three games.

SQL Query 

SELECT 
 plays.game_name, 
 COUNT(plays.id) AS play_count
FROM plays
 INNER JOIN users ON plays.user_id = users.user_id
WHERE 
 TIMESTAMP_DIFF(plays.play_time, users.account_creation, HOUR) <= 24
 AND DATE(users.account_created) BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY 
 game_name
ORDER BY 
 play_count DESC
LIMIT 3;

Query Results

game_name	play_count
Lucky Jackpot	150
Buffalo Jackpot	100
Lucky 7 Slots	50

These results help us understand player preferences and guide feature development and game updates.

Marketing / Monetization 

One of the goals is to get more people to make purchases and generate revenue. This means understanding and creating better marketing campaigns to our customers.

Question

Was our recent targeted campaign where we segmented our users (e.g. by zip code) successful in driving revenue?

SQL Approach

To determine the success of our marketing campaign, I first get the campaigns table to identify users targeted in a specific period. By joining this with the transactions table on campaign_id, I can find which users participated in the campaign and measure the revenue generated. I use the `SUM` function to aggregate total revenue and COUNT(DISTINCT) to determine the unique number of participating users.

SQL Query

SELECT  
 SUM(transaction.amount) AS total_revenue, 
 COUNT(DISTINCT user_id) AS users_participated
FROM campaigns
 INNER JOIN transactions ON campaigns.id = transactions.campaign_id
WHERE 
 campaigns.campaign_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY 
 total_revenue DESC;

Query Results

total_revenue	users_participated
12000	300

The results provide insights into the financial impact of the campaign, to help guide future marketing strategies. 

User Acquisition 

Definition of user acquisition. For us it's focusing on bringing in more players to play our game.

Question

Which UTM source brings in the most valuable users in terms of revenue? 

SQL Approach

To find the most valuable UTM sources, I query the users table to retrieve user sign-up dates and UTM sources. I then use a left join with the `transactions` table on user_id to gather data on total purchases made by each user. The SUM function is used to calculate the total Lifetime Value (LTV) from these transactions, and results are grouped by UTM source to identify which channels are most effective.

SQL Query

The LEFT JOIN ensures we consider all users, even those who haven't made a purchase, which is vital for a complete overview of our user acquisition efforts.

SELECT 
 utm_source, 
 SUM(transactions.amount) AS total_ltv
FROM users
 LEFT JOIN transactions ON users.user_id = transactions.user_id
WHERE 
 users.account_creation BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY utm_source
ORDER BY total_ltv DESC;

Query Results

utm_source	total_ltv
google_ads	50000
facebook_ads	30000
organic_search	15000

This data helps us refine our advertising strategies based on the most profitable channels, and can guide the future budget allocations. 

Conclusion 

Hopefully with these examples, you can see how use SQL to drive results. And the importance of combining technical skills with deep business knowledge. It's not just about writing error-free SQL queries; it’s about understanding which queries to run, how to structure them effectively, and how to interpret their results to inform business decisions. Remember, the tools SQL, Excel, Python, are just tools and the real goal is to drive results and make business decisions.