Skip to content

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: postsusers, 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 usersposts, 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.

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

  1. Hi Brian,

    Thank you for your effort to help juniors Data analyst

    Really I hope if you can be my Mentor in Analytics.

    My Question

    if i am data analyst in company Like Talabat and I want to take these data from (Google analytics, Fiber, and Online Ads).
    AS I will be looking at
    – Clicks
    – Downloads
    – Registration
    – First Order
    – Retention

    How Can I do it ?

    Thank You in Advance.

Leave a Reply

Your email address will not be published. Required fields are marked *

Tableau & Google Sheets Checklist

This one-page checklist PDF will help you learn how to upload your data into Google Sheets and build an awesome Tableau dashboard without wasting time.

You have Successfully Subscribed!

4 Things For Every Tableau Dashboard Creator

This free 4-page eBook PDF will walk you through the 4 most important steps for creating a Tableau dashboard

You have Successfully Subscribed!

Massive List of Tableau Tools

Tableau is even more powerful with these 20 tools you might not know about. The 2-page PDF includes a description and helpful links for each tool to get you started.

You have Successfully Subscribed!

Career Boosting Business Analytics Trends

Use this list as a road map to take you from boring data job to the GO-TO PERSON in your company that knows the answers to all the questions.

All set! Check your email :)

Massive List of Free Data Viz Books

List of 10 Awesome Data Visualization Books

All set! Check your email to download.

Tableau Dashboards
Beginner’s Guide

Get the full 2-page PDF with the 5 steps outlined in this guide + 3 bonuses to help you along the way

BONUS #1: 5 Beginner mistakes to avoid

BONUS #2: Video walkthrough

BONUS #3: Links to the the Google Sheet and final Dashboard

All set! Check your email :)

How to Create 6 Different Tableau Bar Charts (Free Guide)

Get the step-by-step guide to create all 6 different types of Tableau Bar Charts (with copy-and-paste calculated fields included) for free.

 

All set! Check your email :)

Massive List of Tableau Web Data Connectors

Download the list of 21 different Web Data Connectors for Tableau in this Google Sheet (save a copy for yourself, too!) 

All set! Check your email :)

Slope Graph Examples PDF + Dashboard Walkthrough Video

4-page PDF showing 10 slope graph examples + a video walkthrough

All set! Check your email to download the bonus materials.

Tableau Tutorial Chart Redesign

Download the Tableau Tutorial PDF Checklist & Workbook

The step-by-step checklist for fixing busted charts with Tableau PLUS the full workbook with all of my formatting and calculations for free.

All set! Check your email to download.

Download the TableauHighlight Tables PDF Guide

The step-by-step guide for creating Tableau highlight tables
(plus direct links to the YouTube video walkthrough and link to final Tableau workbook)

You have Successfully Subscribed!

Tableau Scatter Plots: The Complete Guide

Get the step-by-step checklist guide to create scatter plots with Tableau

 

All set! Check your email :)

Rebuild a Busted Chart with Tableau

Enter your best email to get the PDF version of this tutorial + the companion Tableau workbook for free

All set! Check your email to download.

Download the Bonus Materials

Enter your best email to get the PDF checklist version of this guide and the companion Tableau workbook for free.

All set! Check your email :)