3 Simple Steps To Get Started With Social Media Analytics: A Step-by-Step SQL Case Study With Example Code

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:

1. Setting up your database and tables
2. Loading your data
3. 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:

​You can follow along using this interactive SQL session.

-- 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, users, and interactions.

The 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 users, posts, and 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:

1. View all past issues of my newsletter here.
2. Land your dream job in data by creating your online data analytics portfolio using my 14-day Data Analytics Portfolio Playbook.​
3. If you want actionable data analytics advice on your specific situation, book a 1:1 coaching session with me today.