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.
Comments