Steps involved in social media data analysis using SQL

There are mainly 3 steps that are required for data analysis. Here is how SQL can help solve social media marketing analytics problems quickly. The 3 steps are:

1. Creating your database and tables
2. Loading your data
3. Analyzing your data using SQL queries

Step 1: Creating your Database and Tables

The first step involves, creating or setting up a database and creating the necessary tables to store your social media data. Or, load the pre existing database.

Here is an example where we have created 3 tables of posts, users, and interactions.

• The posts table contains information on specific social media posts, such as the content and the amount of likes, comments, and shares received.
• The user’s table contains information about individual social media users, including their name, username, and number of followers.
• The interactions table maintains information regarding user-post interactions, such as likes, comments, or shares.

-- 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
);

Step 2: Loading your data

There are several different methods for loading data into a database. Here, we will use the SQL approach, although you may alternatively upload from CSV or Excel.

Load the information about social media into the database tables you just constructed. You may accomplish this with INSERT queries 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),
('Bob', 'bob456', '2022-01-02 14:00:00', 200),
('Charlie', 'charlie789', '2022-01-03 15:00:00', 150),
('David', 'david101', '2022-01-04 16:00:00', 300);

-- 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),
       ('Had a great day at the beach!', '2022-01-02 14:30:00', 30, 5, 2),
       ('Just finished reading an amazing book!', '2022-01-03 15:30:00', 45, 8, 3),
       ('Excited for the weekend!', '2022-01-04 16:30:00', 60, 12, 6);

-- 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'),
       (2, 2, 'L', '2022-01-02 15:00:00'),
       (3, 3, 'C', '2022-01-03 16:00:00'),
       (4, 1, 'S', '2022-01-04 17:00:00');

Once you have all of your data loaded up, now you can finally analyze your data by using SQL queries.

Step 3: Analyzing your data using SQL queries

Posts with the most likes:

SELECT content, likes
FROM posts
ORDER BY likes DESC;

Output:

The top 3 users with the most followers:

SELECT name, followers
FROM users
ORDER BY followers DESC
LIMIT 3 ;

Output:

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;

Output:

Social media has enormous data possibilities for corporations, marketers, and researchers. SQL effectively extracts and alters data for analysis. Customer behavior and market trends were among the insights gained. SQL’s strength resides in being capable of querying relational databases, thereby facilitating the extraction of information and manipulation.

Social networking analysis with SQL allows for a better grasp of how brands are perceived and audience involvement. This tool promotes making decisions and strategy formulation. Analysts may extract significant information from large social media databases by using SQL. It gives you a competitive advantage by helping you understand audience dynamics and optimize your online presence. SQL, when used with visualization tools, facilitates efficient transmission of findings. Proficiency in Mysql for social media data analysis is critical for managing the changing digital world.