Social media analytics is one of the key marketing problems that businesses need to solve.
And using SQL a great way to unlock the value in your social media data. Most people stick with just the built in social media dashboards for Facebook, Twitter, Instagram, etc.
But what if you need to combine that data together? What if you want to get a full picture of your analytics across different platforms or campaigns.
That’s where SQL comes in.
Unfortunately, many digital marketers don’t learn SQL because it seems overwhelming.
SQL for digital marketing doesn’t need to be overwhelming.
You can get started with simple building blocks and grow from there. This case study will walk you through the entire process from start to finish. By the end, you’ll have a firm grasp on how SQL can help solve social media marketing analytics problems quickly.
Here are the 3 steps we’ll cover in this SQL case study:
- Setting up your database and tables
- Loading your data
- Analyzing your data using SQL queries
All of the code you need to get started is included so you can follow along step by step.
Step 1: Setting up your database and tables
First, you’ll need to set up a database and create the necessary tables to store your social media data. Here’s an example of how you might do this in PostgreSQL:
-- Create a new database named 'social_media' CREATE DATABASE social_media; -- Create a table to store data about social media posts CREATE TABLE posts ( post_id SERIAL PRIMARY KEY, content TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL, likes INTEGER NOT NULL, comments INTEGER NOT NULL, shares INTEGER NOT NULL ); -- Create a table to store data about social media users CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name TEXT NOT NULL, username TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL, followers INTEGER NOT NULL ); -- Create a table to store data about user interactions with posts CREATE TABLE interactions ( interaction_id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(user_id), post_id INTEGER REFERENCES posts(post_id), type CHAR(1) NOT NULL CHECK (type IN ('L', 'C', 'S')), created_at TIMESTAMP WITH TIME ZONE NOT NULL
his SQL code creates a new database named
social_media and three tables within it:
posts table stores data about individual social media posts, such as the content of the post and the number of likes, comments, and shares it has received. The
users table stores data about individual social media users, such as their name, username, and number of followers. The
interactions table stores data about interactions between users and posts, such as whether the interaction was a like, comment, or share.
Here’s an ERD showing the relationships between these tables:
n this ERD, the
users table is connected to the
interactions table by a one-to-many relationship, meaning that each user can have multiple interactions with posts. Similarly, the
interactions table is connected to the
posts table by a one-to-many relationship, meaning that each post can have multiple interactions with users.
Nice work! You’re now ready to load up your social media data.
Step 2: Loading your data
There are a few different ways to load data into a database. For this case study, we’re going to show the SQL method, but you can also import from CSV or Excel.
To load your social media data into the tables you just created. You can do this using INSERT statements like this:
-- Insert a row into the 'users' table INSERT INTO users (name, username, created_at, followers) VALUES ('Alice', 'alice123', '2022-01-01 12:00:00', 100); -- Insert a row into the 'posts' table INSERT INTO posts (content, created_at, likes, comments, shares) VALUES ('Check out this cool new product!', '2022-01-01 13:00:00', 50, 10, 5); -- Insert a row into the 'interactions' table INSERT INTO interactions (user_id, post_id, type, created_at) VALUES (1, 1, 'L', '2022-01-01 13:30:00');
This SQL code inserts one row into each of the
interactions tables. The first INSERT statement inserts a row into the
users table for a user named Alice with 100 followers. The second INSERT statement inserts a row into the
posts table for a post with 50 likes, 10 comments, and 5 shares. The third INSERT statement inserts a row into the
interactions table for a like by Alice on the post.
You can repeat these INSERT statements for as many users, posts, and interactions as you want to load into your database.
Once you have all of your data loaded up, you are ready to analyze your data using SQL.
Step 3: Analyzing your data using SQL queries
You can use SQL queries to analyze it and answer questions about your social media performance. Here are a few examples of queries you might use:
The top 10 users with the most followers:
SELECT name, followers FROM users ORDER BY followers DESC LIMIT 10;
Posts with the most likes:
SELECT content, likes FROM posts ORDER BY likes DESC;
Percentage of likes, comments, and shares for each post:
SELECT content, ROUND(likes / (likes + comments + shares)::numeric * 100, 2) || '%' AS likes, ROUND(comments / (likes + comments + shares)::numeric * 100, 2) || '%' AS comments, ROUND(shares / (likes + comments + shares)::numeric * 100, 2) || '%' AS shares FROM posts;
These are just a few examples of the kinds of analyses you can do with SQL. By using different queries and filtering, grouping, and aggregating your data, you can uncover all sorts of insights about your social media performance.
That’s it for today – I hope you enjoyed it.
See you again next week!
Whenever you’re ready, here are 3 ways I can help you: