steampipe plugin install twitter

Table: twitter_user_mention - Query Twitter User Mentions using SQL

Twitter User Mentions are instances when a Twitter user includes another user's Twitter handle in their tweet. This is often done to draw the mentioned user's attention to the tweet or to engage them in a conversation. User mentions are a key part of the interactive nature of Twitter, allowing users to engage with each other and fostering discussions on the platform.

Table Usage Guide

The twitter_user_mention table provides insights into Twitter User Mentions, specifically the details of the users who have mentioned the authenticated user in their tweets. As a social media analyst, explore user-specific details through this table, including the frequency of mentions, the context of mentions, and associated metadata. Utilize it to uncover information about user interactions, such as the most active users, the nature of interactions, and the reach of your tweets.

Important Notes

  • The user_id field must be set in the where clause.

Examples

Last 5 tweets that mention the user

Explore the most recent social media mentions of a specific user to stay updated with the latest conversations involving them. This can be particularly useful for monitoring brand reputation, tracking user engagement, or responding to customer feedback.

select
id,
text
from
twitter_user_mention
where
user_id = '1318177503995985921' -- @steampipeio
order by
created_at desc
limit
5;
select
id,
text
from
twitter_user_mention
where
user_id = '1318177503995985921' -- @steampipeio
order by
created_at desc
limit
5;

Get mention timeline by username

Discover the specific instances where a particular username is mentioned on Twitter. This can be useful in monitoring brand mentions, tracking conversations involving a specific user, or understanding user engagement trends. Via subselect:

select
id,
text
from
twitter_user_mention as t
where
t.user_id in (
select
id
from
twitter_user
where
username = 'steampipeio'
);
select
id,
text
from
twitter_user_mention as t
where
t.user_id in (
select
id
from
twitter_user
where
username = 'steampipeio'
);

Via join:

select
t.id,
t.text
from
twitter_user_mention as t,
twitter_user as u
where
t.user_id = u.id
and u.username = 'steampipeio';
select
t.id,
t.text
from
twitter_user_mention as t,
twitter_user as u
where
t.user_id = u.id
and u.username = 'steampipeio';

Schema for twitter_user_mention

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
attachment_pollsjsonbPolls attached to the Tweet.
attachmentsjsonbSpecifies the type of attachments (if any) present in this Tweet.
authorjsonbAuthor of the Tweet.
author_idtextUnique identifier of the author of the Tweet.
cashtagsjsonbList of cashtags (e.g. $TWTR) mentioned in the Tweet.
context_annotationsjsonbContains context annotations for the Tweet.
conversation_idtextThe Tweet ID of the original Tweet of the conversation (which includes direct replies, replies of replies).
created_attimestamp with time zoneCreation time of the Tweet.
entitiesjsonbContains details about text that has a special meaning in a Tweet.
geojsonbContains details about the location tagged by the user in this Tweet, if they specified one.
hashtagsjsonbList of hashtags (e.g. #sql) mentioned in the Tweet.
idtextUnique identifier of this Tweet.
in_reply_to_user_idtextIf this Tweet is a Reply, indicates the user ID of the parent Tweet's author.
in_reply_userjsonbUser the Tweet was in reply to.
langtextLanguage of the Tweet, if detected by Twitter. Returned as a BCP47 language tag.
mentionsjsonbList of users (e.g. steampipeio) mentioned in the Tweet.
mentions_objjsonbUsers mentioned in the Tweet.
placejsonbPlace where the Tweet was created.
possibly_sensitivebooleanIndicates if this Tweet contains URLs marked as sensitive, for example content suitable for mature audiences.
public_metricsjsonbEngagement metrics for the Tweet at the time of the request.
quotedtextIf this Tweet is a Quote Tweet, indicates the ID of the original Tweet.
referenced_tweetsjsonbTweets referenced in this Tweet.
replied_totextIf this Tweet is a Reply, indicates the ID of the Tweet it is a reply to.
retweetedtextIf this Tweet is a Retweet, indicates the ID of the orginal Tweet.
sourcetextThe name of the app the user Tweeted from.
texttextThe content of the Tweet.
urlsjsonbList of URLs (e.g. https://steampipe.io) mentioned in the Tweet.
user_idtext=ID of the user the tweets are related to.
withheldjsonbContains withholding details for withheld content.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- twitter

You can pass the configuration to the command with the --config argument:

steampipe_export_twitter --config '<your_config>' twitter_user_mention