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 thewhere
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, textfrom twitter_user_mentionwhere user_id = '1318177503995985921' -- @steampipeioorder by created_at desclimit 5;
select id, textfrom twitter_user_mentionwhere user_id = '1318177503995985921' -- @steampipeioorder by created_at desclimit 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, textfrom twitter_user_mention as twhere t.user_id in ( select id from twitter_user where username = 'steampipeio' );
select id, textfrom twitter_user_mention as twhere t.user_id in ( select id from twitter_user where username = 'steampipeio' );
Via join:
select t.id, t.textfrom twitter_user_mention as t, twitter_user as uwhere t.user_id = u.id and u.username = 'steampipeio';
select t.id, t.textfrom twitter_user_mention as t, twitter_user as uwhere t.user_id = u.id and u.username = 'steampipeio';
Schema for twitter_user_mention
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
attachment_polls | jsonb | Polls attached to the Tweet. | |
attachments | jsonb | Specifies the type of attachments (if any) present in this Tweet. | |
author | jsonb | Author of the Tweet. | |
author_id | text | Unique identifier of the author of the Tweet. | |
cashtags | jsonb | List of cashtags (e.g. $TWTR) mentioned in the Tweet. | |
context_annotations | jsonb | Contains context annotations for the Tweet. | |
conversation_id | text | The Tweet ID of the original Tweet of the conversation (which includes direct replies, replies of replies). | |
created_at | timestamp with time zone | Creation time of the Tweet. | |
entities | jsonb | Contains details about text that has a special meaning in a Tweet. | |
geo | jsonb | Contains details about the location tagged by the user in this Tweet, if they specified one. | |
hashtags | jsonb | List of hashtags (e.g. #sql) mentioned in the Tweet. | |
id | text | Unique identifier of this Tweet. | |
in_reply_to_user_id | text | If this Tweet is a Reply, indicates the user ID of the parent Tweet's author. | |
in_reply_user | jsonb | User the Tweet was in reply to. | |
lang | text | Language of the Tweet, if detected by Twitter. Returned as a BCP47 language tag. | |
mentions | jsonb | List of users (e.g. steampipeio) mentioned in the Tweet. | |
mentions_obj | jsonb | Users mentioned in the Tweet. | |
place | jsonb | Place where the Tweet was created. | |
possibly_sensitive | boolean | Indicates if this Tweet contains URLs marked as sensitive, for example content suitable for mature audiences. | |
public_metrics | jsonb | Engagement metrics for the Tweet at the time of the request. | |
quoted | text | If this Tweet is a Quote Tweet, indicates the ID of the original Tweet. | |
referenced_tweets | jsonb | Tweets referenced in this Tweet. | |
replied_to | text | If this Tweet is a Reply, indicates the ID of the Tweet it is a reply to. | |
retweeted | text | If this Tweet is a Retweet, indicates the ID of the orginal Tweet. | |
source | text | The name of the app the user Tweeted from. | |
text | text | The content of the Tweet. | |
urls | jsonb | List of URLs (e.g. https://steampipe.io) mentioned in the Tweet. | |
user_id | text | = | ID of the user the tweets are related to. |
withheld | jsonb | Contains 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