# Build Your Own Bluesky Analytics Dashboard
_Are you using Bluesky and want to stay on top of what's happening?
Are you curious how you can use Shaper to pull data from APIs and build interactive dashboards, all in a single tool and using just SQL?_
Let's automate pulling posts data from the Bluesky API to track topics we are interested in,
and then create a data dashboard that visualizes activity around these topics.
You will get a dashboard that looks like this:
## Let's Get Started
1. Let's open a terminal, create a new directory, and change into it:
```bash
mkdir bluesky-dashboard && cd bluesky-dashboard
```
1. You will need credentials to authenticate with the Bluesky API.
Create a [Bluesky App Password](https://bsky.app/settings/app-passwords) and save it together with your handle as `bluesky_credentials.json`:
```bash
echo '{ "identifier": "", "password": "" }' > bluesky_credentials.json
```
3. Now let's run Shaper. The easiest way is to run it via Docker or NPM:
```bash
docker run --rm -it -p5454:5454 \
-v ./bluesky_credentials.json:/bluesky_credentials.json \
-v ./data:/data taleshape/shaper \
--init-sql "INSTALL http_client FROM community; LOAD http_client;"
```
```bash
npx @taleshape/shaper -d ./data
```
4. Open http://localhost:5454 in your browser and click on __New__.
Now let's create a Task to fetch posts from Bluesky and store them in a database table.
Select __Task__ in the dropdown at the top of the page and paste in the following SQL code:
```sql
SELECT (date_trunc('hour', now()) + INTERVAL '1h')::SCHEDULE;
CREATE SCHEMA IF NOT EXISTS bsky;
CREATE TABLE IF NOT EXISTS bsky.posts (
topic VARCHAR,
created_at TIMESTAMP,
cid VARCHAR,
author_handle VARCHAR,
url VARCHAR,
text VARCHAR,
like_count INT,
reply_count INT,
quote_count INT,
repost_count INT,
loaded_at TIMESTAMP DEFAULT now(),
);
SET VARIABLE access_jwt = http_post(
'https://bsky.social/xrpc/com.atproto.server.createSession',
headers => MAP {
'Content-Type': 'application/json',
'Accept': 'application/json',
},
body => (SELECT c FROM './bluesky_credentials.json' c)
) ->> 'body' ->> 'accessJwt';
WITH topics AS (
SELECT col0 AS topic, col1 AS query_string FROM (
VALUES
('DuckDB', 'duckdb'),
('Data Engineering', '"data-engineering" "data engineering" "dataengineering"'),
('#databs', '#databs'),
)
),
topics_with_ts AS (
SELECT
topic,
query_string,
coalesce(max(loaded_at), (now() - INTERVAL '30 days')::TIMESTAMP) as last_loaded_at,
FROM topics LEFT JOIN bsky.posts USING(topic)
GROUP BY ALL
),
json_posts AS (
SELECT
topic,
(http_get(
'https://bsky.social/xrpc/app.bsky.feed.searchPosts',
headers => MAP {
'Accept': 'application/json',
'Authorization': concat('Bearer ', getvariable('access_jwt')),
},
params => MAP {
'q': query_string,
'limit': '100',
'since': strftime(last_loaded_at, '%Y-%m-%dT%H:%M:%SZ'),
}
) ->> 'body' -> '$.posts[*]').unnest() AS p
FROM topics_with_ts
)
INSERT INTO bsky.posts BY NAME (
SELECT
topic,
(p ->> '$.record.createdAt')::TIMESTAMP AS created_at,
p ->> 'cid' AS cid,
p ->> '$.author.handle' AS author_handle,
concat('https://bsky.app/profile/', author_handle, '/post/', split_part(p ->> 'uri', '/', -1)) AS url,
p ->> '$.record.text' AS text,
(p -> 'likeCount')::INT AS like_count,
(p -> 'replyCount')::INT AS reply_count,
(p -> 'quoteCount')::INT AS quote_count,
(p -> 'repostCount')::INT AS repost_count,
FROM json_posts
);
```
The task is configured to run every hour and fetch new posts for the topics "DuckDB", "Data Engineering", and "#databs".
Replace the topics with your own topics.
Then click __Run__ to try out the task. If the task runs successfully, click __Create__ and save it as `Fetch Bluesky Posts`.
5. With the first data loaded, we can now create a dashboard to visualize the data.
Click on __New__ again and paste in the following SQL code:
```sql
SELECT 'Bluesky Analytics'::SECTION;
SELECT
min(created_at)::DATE::DATEPICKER_FROM AS start_date,
max(created_at)::DATE::DATEPICKER_TO AS end_date,
FROM bsky.posts;
SELECT 'Topics'::LABEL;
SELECT distinct topic::DROPDOWN_MULTI AS topics FROM bsky.posts;
CREATE TEMP VIEW posts AS (
FROM bsky.posts
WHERE topic in getvariable('topics')
AND created_at BETWEEN getvariable('start_date')
AND getvariable('end_date')
);
SELECT concat('bluesky_posts_', today())::DOWNLOAD_CSV AS CSV;
SELECT * FROM posts;
SELECT count(distinct cid) AS 'Total Posts Overall' FROM posts;
SELECT
count() AS 'Total Posts',
topic AS Topic,
FROM posts GROUP BY topic ORDER BY ALL DESC;
SELECT 'Posts per Day'::LABEL;
SELECT
topic::CATEGORY,
date_trunc('day', created_at)::XAXIS,
count()::BARCHART_STACKED,
FROM posts GROUP BY ALL ORDER BY ALL;
SELECT ''::SECTION;
SELECT 'Top Posters'::LABEL;
FROM (
SELECT
count(distinct cid)::BARCHART AS "Total Posts",
author_handle::YAXIS,
FROM posts GROUP BY ALL ORDER BY ALL DESC LIMIT 10
) ORDER BY ALL;
SELECT 'Likes By Time of Day'::LABEL;
SELECT
topic::CATEGORY,
date_trunc('hour', created_at)::TIME::XAXIS,
sum(like_count)::BARCHART_STACKED,
FROM posts GROUP BY ALL ORDER BY ALL;
```
Now click __Create__ and save the dashboard as `Bluesky Analytics`.
6. Click on __View Dashboard__ in the top right corner to have a better look at the whole dashboard.
And you are done!
Please reach out, ask questions and I would love to see what you built.
_Shaper is open source and free to use. It's simple to run on your own server and so you can easily share dashboards with others. Find out more on Github:_