> ## Documentation Index > Fetch the complete documentation index at: https://docs.nekt.com/llms.txt > Use this file to discover all available pages before exploring further. # Instagram as a data source > Bring data from Instagram to Nekt. Instagram is a social media platform for sharing photos, videos, and stories. The Instagram Business API provides access to insights and analytics about your business account's performance and audience engagement. ## Requirements Before setting up Instagram as a data source, ensure your account meets these requirements: * The Instagram account must be a **Business** account (personal accounts are not supported) * The Instagram profile must have at least **100 followers** * The Instagram profile must be linked to a Facebook page * The user connecting the account must have one of these roles on the Facebook page: * Owner * Administrator * Editor **Instagram Limitations:** 1. Media insights (such as views and engagement metrics) are only available for content that has received at least 5 views. 2. Stories data is only available for 24 hours after posting. If your pipeline runs during this window, the data will be captured and stored in your destination, but new pipelines won't be able to fetch stories older than 24 hours. 3. Business Discovery (competitor data) only provides public profile information and post-level metrics (likes, comments). Private insights such as reach, impressions, saves, and follower demographics are not available for competitor accounts. ## Configuring Instagram as a Source In the [Sources](https://app.nekt.ai/sources) tab, click on the "Add source" button located on the top right of your screen. Then, select the Instagram option from the list of connectors. Click **Next** and you'll be prompted to add your access. ### 1. Add account access You'll need to add the following information to connect to Instagram: 1. Complete Facebook authorization with an account that has access to the page you want to get data from. 2. Select the Instagram Page you'd like to extract data from. 3. Define a date to be the starting point of the data retrival. 4. The lookback window parameter allows you to define the the number of days to look in the past for new updates in Media (such as Comments and Likes). What the lookback does is check data from X days ago to ensure it captures all new updates. You can use default value if you are not sure about it. 5. (Optional) **Competitor Usernames**: Add a list of Instagram usernames of competitor Business or Creator accounts you want to monitor. This uses the Instagram Business Discovery API to fetch public profile data and posts from those accounts using your own authentication. The competitor accounts must be public Business or Creator accounts. Click **Next**. ### 2. Select streams Choose which data streams you want to sync - you can select all streams or pick specific ones that matter most to you. > Tip: The stream can be found more easily by typing its name. Select the streams and click **Next**. ### 3. Configure data streams Customize how you want your data to appear in your catalog. Select a name for each table (which will contain the fetched data) and the type of sync. * **Table name**: we suggest a name, but feel free to customize it. You have the option to add a **prefix** and make this process faster! * **Sync Type**: you can choose between INCREMENTAL and FULL\_TABLE. * Incremental: every time the extraction happens, we'll get only the new data - which is good if, for example, you want to keep every record ever fetched. * Full table: every time the extraction happens, we'll get the current state of the data - which is good if, for example, you don't want to have deleted data in your catalog. Once you are done configuring, click **Next**. ### 4. Configure data source Describe your data source for easy identification within your organization, not exceeding 140 characters. To define your [Trigger](https://docs.nekt.com/get-started/core-concepts/triggers), consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times). Optionally, you can determine when to execute a [full sync](https://docs.nekt.com/get-started/core-concepts/types-of-sync#additional-full-sync). This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while. Once you are ready, click **Next** to finalize the setup. ### 5. Check your new source You can view your new source on the [Sources](https://app.nekt.ai/sources) page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog. For you to be able to see it on your [Catalog](https://app.nekt.ai/catalog), you need at least one successful source run. # Streams and Fields Below you'll find all available data streams from Instagram and their corresponding fields: Stream for basic account information about your Instagram Business account. **Key Fields:** * `id` - Account ID (Primary Key) * `ig_id` - Instagram internal numeric ID * `username` - Account username * `name` - Account display name **Profile Information:** * `biography` - Account bio text * `website` - Account website URL * `profile_picture_url` - Profile picture URL **Metrics:** * `follows_count` - Number of accounts being followed * `followers_count` - Number of followers * `media_count` - Total number of media posts Stream for posts, stories, and reels published on your account. **Key Fields:** * `id` - Media ID (Primary Key) * `ig_id` - Instagram internal ID * `caption` - Post caption text * `media_type` - Type of media (IMAGE, VIDEO, CAROUSEL\_ALBUM) * `media_product_type` - Product type (FEED, STORY, REELS) * `timestamp` - When the media was posted (Replication Key) **URLs and References:** * `media_url` - URL to the media content * `permalink` - Permanent link to the post * `thumbnail_url` - Thumbnail image URL (for videos) * `shortcode` - Short code for the media **Engagement Metrics:** * `comments_count` - Number of comments * `like_count` - Number of likes **Additional Information:** * `is_comment_enabled` - Whether comments are enabled * `is_shared_to_feed` - Whether shared to feed (for Reels) * `username` - Username who posted * `owner` - Owner object containing: * `id` - Owner account ID * `boost_eligibility_info` - Boost eligibility object: * `eligible_to_boost` - Whether the post can be boosted Stream for media performance metrics. Insights are only available for media with at least 5 views. **Key Fields:** * `media_id` - Media ID (Primary Key) * `media_type` - Type of media (IMAGE, VIDEO, CAROUSEL\_ALBUM) * `media_product_type` - Product type (FEED, STORY, REELS) * `timestamp` - When the media was posted (Replication Key) **Engagement Metrics:** * `comments` - Number of comments * `likes` - Number of likes * `shares` - Number of shares * `saved` - Number of saves * `total_interactions` - Total engagement count * `replies` - Number of replies (Stories) **Reach & Views:** * `reach` - Number of unique accounts that saw the post * `views` - Number of views * `follows` - Number of new follows from this media * `profile_visits` - Profile visits from this media **Reels-Specific Metrics:** * `ig_reels_avg_watch_time` - Average watch time in milliseconds * `ig_reels_video_view_total_time` - Total view time in milliseconds Stream for comments on your posts. Comments are extracted as a child stream of Media. **Key Fields:** * `id` - Comment ID (Primary Key) * `media_id` - ID of the media being commented on * `text` - Comment text content * `timestamp` - When the comment was posted **Engagement:** * `like_count` - Number of likes on the comment * `hidden` - Whether the comment is hidden Stream for replies to comments. Replies are extracted as a child stream of Comments. **Key Fields:** * `id` - Reply ID (Primary Key) * `parent_comment_id` - ID of the parent comment * `text` - Reply text content * `timestamp` - When the reply was posted **Engagement:** * `like_count` - Number of likes on the reply * `hidden` - Whether the reply is hidden Daily account-level insights with a 1-day rolling window (`insights_rolling_day`). **Key Fields:** * `date` - Date of the metrics (Primary Key, Replication Key) **Engagement Metrics:** * `reach` - Number of unique accounts reached * `total_interactions` - Total engagement count * `accounts_engaged` - Number of accounts that engaged * `likes` - Number of likes received * `shares` - Number of shares * `replies` - Number of replies **Profile Metrics:** * `follows_and_unfollows` - Net follow/unfollow activity * `profile_links_taps` - Profile link clicks * `website_clicks` - Website clicks * `profile_views` - Profile views Account-level insights with a 7-day rolling window (`insights_rolling_week`). **Key Fields:** * `date` - Date of the metrics (Primary Key, Replication Key) **Metrics:** Same metrics as Rolling Day, but aggregated over a 7-day rolling window: * `reach`, `total_interactions`, `accounts_engaged`, `likes`, `shares`, `replies` * `follows_and_unfollows`, `profile_links_taps`, `website_clicks`, `profile_views` Account-level insights with a 28-day rolling window (`insights_rolling_days28`). **Key Fields:** * `date` - Date of the metrics (Primary Key, Replication Key) **Metrics:** Same metrics as Rolling Day, but aggregated over a 28-day rolling window: * `reach`, `total_interactions`, `accounts_engaged`, `likes`, `shares`, `replies` * `follows_and_unfollows`, `profile_links_taps`, `website_clicks`, `profile_views` Lifetime follower demographics broken down by country (`follower_demographics_country_lifetime`). **Key Fields:** * `dimension_value` - Country code (e.g., "US", "BR", "GB") (Primary Key) * `value` - Number of followers from this country Lifetime follower demographics broken down by age range (`follower_demographics_age_lifetime`). **Key Fields:** * `dimension_value` - Age range (e.g., "18-24", "25-34", "35-44") (Primary Key) * `value` - Number of followers in this age range Lifetime follower demographics broken down by gender (`follower_demographics_gender_lifetime`). **Key Fields:** * `dimension_value` - Gender (e.g., "M", "F", "U") (Primary Key) * `value` - Number of followers of this gender Lifetime follower demographics broken down by city (`follower_demographics_city_lifetime`). **Key Fields:** * `dimension_value` - City name (e.g., "São Paulo, Brazil") (Primary Key) * `value` - Number of followers from this city Stream that tracks historical absolute follower counts (`follower_count_historical`). On the first run (no bookmark), this stream reconstructs the last 30 days of follower counts by querying exact daily follows and unfollows from the Instagram Insights API and working backwards from the current total. On subsequent incremental runs, it simply records today's current follower count as a snapshot, optimizing API usage. The historical reconstruction is limited to a maximum of **30 days** of data due to Instagram API constraints on the insights endpoint. Running regular incremental syncs allows you to build a continuous snapshot history beyond that limit. **Key Fields:** * `date` - Date of the follower count (Primary Key, Replication Key) * `followers_count` - Absolute number of followers on that date Stream for public profile data of competitor Instagram Business/Creator accounts (`business_discovery_profiles`). Requires `competitor_usernames` to be configured. This stream uses the Instagram Business Discovery API to fetch publicly available profile information from other Business or Creator accounts, authenticating with your own account. **Key Fields:** * `id` - Competitor account ID (Primary Key) * `ig_id` - Instagram internal numeric ID * `username` - Competitor account username * `name` - Competitor account display name **Profile Information:** * `biography` - Account bio text * `website` - Account website URL * `profile_picture_url` - Profile picture URL **Metrics:** * `follows_count` - Number of accounts being followed * `followers_count` - Number of followers * `media_count` - Total number of media posts Stream for public media posts of competitor Instagram Business/Creator accounts (`business_discovery_media`). This is a child stream of Business Discovery Profiles. Requires `competitor_usernames` to be configured. Paginates through competitor media from newest to oldest, stopping when reaching the configured `start_date`. Only publicly available metrics are returned for competitor posts. Private insights such as reach, impressions, saves, shares, and follower demographics are not available through the Business Discovery API. **Key Fields:** * `id` - Media ID (Primary Key) * `caption` - Post caption text * `media_type` - Type of media (IMAGE, VIDEO, CAROUSEL\_ALBUM) * `media_product_type` - Product type (FEED, REELS) * `timestamp` - When the media was posted (Replication Key) **URLs and References:** * `media_url` - URL to the media content * `permalink` - Permanent link to the post **Engagement Metrics:** * `comments_count` - Number of comments * `like_count` - Number of likes **Additional Information:** * `username` - Username of the competitor account ## Data Model The following diagram illustrates the relationships between the core data streams in Instagram. The arrows indicate the join keys that link the different entities. ```mermaid theme={null} graph TD; subgraph "Core Entities" UserDetails("User Details"); Media("Media"); end subgraph "Engagement" Comments("Comments"); CommentReplies("Comment Replies"); end subgraph "Analytics" MediaInsights("Media Insights"); UserInsightsDay("User Insights - Day"); UserInsightsWeek("User Insights - Week"); UserInsights28("User Insights - 28 Days"); end subgraph "Demographics" DemoCountry("Demographics - Country"); DemoAge("Demographics - Age"); DemoGender("Demographics - Gender"); DemoCity("Demographics - City"); end subgraph "Competitor Analysis" CompProfiles("BD - Competitor Profiles"); CompMedia("BD - Competitor Media"); end Media -- "owner.id" --> UserDetails; Comments -- "media_id" --> Media; CommentReplies -- "parent_comment_id" --> Comments; MediaInsights -- "media_id" --> Media; CompMedia -- "username" --> CompProfiles; ``` # Use Cases for Data Analysis This guide outlines valuable business intelligence use cases when consolidating Instagram data, along with ready-to-use SQL queries that you can run on [Explorer](https://app.nekt.ai/explorer). ## Content Performance Analysis ### 1. Media Performance Overview Track the overall performance of your posts, stories, and reels to identify top-performing content. **Business Value:** * Identify which content types drive the most engagement * Understand what content resonates best with your audience * Optimize your content strategy based on performance data * Track reach and engagement trends over time ```sql theme={null} WITH media_performance AS ( SELECT m.id AS media_id, m.media_type, m.media_product_type, m.caption, m.timestamp AS posted_at, m.like_count, m.comments_count, mi.reach, mi.shares, mi.saved, mi.views, mi.total_interactions, mi.ig_reels_avg_watch_time, CAST(mi.total_interactions AS DOUBLE) * 100.0 / NULLIF(mi.reach, 0) AS engagement_rate FROM nekt_raw.instagram_media m LEFT JOIN nekt_raw.instagram_media_insights mi ON m.id = mi.media_id WHERE DATE(m.timestamp) >= CURRENT_DATE - INTERVAL '30' DAY ) SELECT media_product_type, COUNT(*) AS total_posts, SUM(like_count) AS total_likes, SUM(comments_count) AS total_comments, SUM(reach) AS total_reach, SUM(shares) AS total_shares, SUM(saved) AS total_saves, SUM(views) AS total_views, SUM(total_interactions) AS total_interactions, ROUND(AVG(engagement_rate), 2) AS avg_engagement_rate, ROUND(AVG(ig_reels_avg_watch_time) / 1000.0, 2) AS avg_watch_time_seconds FROM media_performance GROUP BY media_product_type ORDER BY total_interactions DESC ``` ```sql theme={null} WITH media_performance AS ( SELECT m.id AS media_id, m.media_type, m.media_product_type, m.caption, m.timestamp AS posted_at, m.like_count, m.comments_count, mi.reach, mi.shares, mi.saved, mi.views, mi.total_interactions, mi.ig_reels_avg_watch_time, SAFE_DIVIDE(CAST(mi.total_interactions AS FLOAT64) * 100.0, mi.reach) AS engagement_rate FROM `nekt_raw.instagram_media` m LEFT JOIN `nekt_raw.instagram_media_insights` mi ON m.id = mi.media_id WHERE DATE(m.timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ) SELECT media_product_type, COUNT(*) AS total_posts, SUM(like_count) AS total_likes, SUM(comments_count) AS total_comments, SUM(reach) AS total_reach, SUM(shares) AS total_shares, SUM(saved) AS total_saves, SUM(views) AS total_views, SUM(total_interactions) AS total_interactions, ROUND(AVG(engagement_rate), 2) AS avg_engagement_rate, ROUND(SAFE_DIVIDE(AVG(ig_reels_avg_watch_time), 1000.0), 2) AS avg_watch_time_seconds FROM media_performance GROUP BY media_product_type ORDER BY total_interactions DESC ``` | media\_product\_type | total\_posts | total\_likes | total\_comments | total\_reach | total\_shares | total\_saves | total\_views | total\_interactions | avg\_engagement\_rate | avg\_watch\_time\_seconds | | -------------------- | ------------ | ------------ | --------------- | ------------ | ------------- | ------------ | ------------ | ------------------- | --------------------- | ------------------------- | | REELS | 12 | 8,420 | 342 | 156,890 | 1,245 | 890 | 234,560 | 10,897 | 6.94 | 8.45 | | FEED | 18 | 5,230 | 198 | 89,450 | 456 | 623 | NULL | 6,507 | 7.27 | NULL | | STORY | 45 | NULL | NULL | 45,230 | 234 | NULL | 67,890 | 234 | 0.52 | NULL | ### 2. Top Performing Content Identify your best-performing posts based on engagement metrics to replicate successful content strategies. **Business Value:** * Discover content patterns that drive high engagement * Identify optimal content formats and topics * Inform future content creation decisions * Benchmark new content against top performers ```sql theme={null} WITH ranked_content AS ( SELECT m.id AS media_id, m.media_product_type, SUBSTRING(m.caption, 1, 100) AS caption_preview, m.permalink, DATE(m.timestamp) AS posted_date, m.like_count, m.comments_count, mi.reach, mi.shares, mi.saved, mi.total_interactions, CAST(mi.total_interactions AS DOUBLE) * 100.0 / NULLIF(mi.reach, 0) AS engagement_rate, ROW_NUMBER() OVER ( PARTITION BY m.media_product_type ORDER BY mi.total_interactions DESC ) AS rank_by_type FROM nekt_raw.instagram_media m LEFT JOIN nekt_raw.instagram_media_insights mi ON m.id = mi.media_id WHERE DATE(m.timestamp) >= CURRENT_DATE - INTERVAL '90' DAY AND mi.reach > 0 ) SELECT media_product_type, caption_preview, posted_date, like_count, comments_count, reach, shares, saved, total_interactions, ROUND(engagement_rate, 2) AS engagement_rate_pct FROM ranked_content WHERE rank_by_type <= 5 ORDER BY media_product_type, total_interactions DESC ``` ```sql theme={null} WITH ranked_content AS ( SELECT m.id AS media_id, m.media_product_type, SUBSTR(m.caption, 1, 100) AS caption_preview, m.permalink, DATE(m.timestamp) AS posted_date, m.like_count, m.comments_count, mi.reach, mi.shares, mi.saved, mi.total_interactions, SAFE_DIVIDE(CAST(mi.total_interactions AS FLOAT64) * 100.0, mi.reach) AS engagement_rate, ROW_NUMBER() OVER ( PARTITION BY m.media_product_type ORDER BY mi.total_interactions DESC ) AS rank_by_type FROM `nekt_raw.instagram_media` m LEFT JOIN `nekt_raw.instagram_media_insights` mi ON m.id = mi.media_id WHERE DATE(m.timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND mi.reach > 0 ) SELECT media_product_type, caption_preview, posted_date, like_count, comments_count, reach, shares, saved, total_interactions, ROUND(engagement_rate, 2) AS engagement_rate_pct FROM ranked_content WHERE rank_by_type <= 5 ORDER BY media_product_type, total_interactions DESC ``` | media\_product\_type | caption\_preview | posted\_date | like\_count | comments\_count | reach | shares | saved | total\_interactions | engagement\_rate\_pct | | -------------------- | ------------------------------------------------------------------ | ------------ | ----------- | --------------- | ------ | ------ | ----- | ------------------- | --------------------- | | REELS | 🎬 Behind the scenes of our latest product launch! Watch how we... | 2024-10-15 | 2,340 | 89 | 45,670 | 456 | 234 | 3,119 | 6.83 | | REELS | Quick tutorial: 5 tips to boost your productivity 💡 #tips... | 2024-10-22 | 1,890 | 67 | 38,920 | 312 | 189 | 2,458 | 6.31 | | FEED | Introducing our newest collection ✨ Available now! Link in... | 2024-11-01 | 1,456 | 123 | 28,450 | 89 | 312 | 1,980 | 6.96 | | FEED | Thank you for 100K followers! 🎉 We couldn't have done it... | 2024-09-28 | 3,210 | 234 | 42,180 | 156 | 178 | 3,778 | 8.96 | | REELS | POV: When the coffee hits different on Monday ☕ #relatable... | 2024-10-08 | 1,234 | 45 | 32,890 | 234 | 145 | 1,658 | 5.04 | ## Audience Engagement Analysis ### 3. Account Growth and Engagement Trends Track your account's growth and engagement metrics over time to monitor performance trends. **Business Value:** * Monitor account health and growth trajectory * Identify periods of high or low engagement * Correlate engagement with content posting patterns * Track progress toward growth goals ```sql theme={null} WITH daily_insights AS ( SELECT DATE(date) AS insight_date, reach, total_interactions, accounts_engaged, likes, shares, replies, follows_and_unfollows, profile_views, website_clicks FROM nekt_raw.instagram_insights_rolling_day WHERE DATE(date) >= CURRENT_DATE - INTERVAL '30' DAY ), with_moving_avg AS ( SELECT *, AVG(reach) OVER (ORDER BY insight_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS reach_7day_avg, AVG(total_interactions) OVER (ORDER BY insight_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS interactions_7day_avg, SUM(follows_and_unfollows) OVER (ORDER BY insight_date) AS cumulative_net_followers FROM daily_insights ) SELECT insight_date, reach, ROUND(reach_7day_avg, 0) AS reach_7day_avg, total_interactions, ROUND(interactions_7day_avg, 0) AS interactions_7day_avg, accounts_engaged, follows_and_unfollows AS net_followers, cumulative_net_followers, profile_views, website_clicks FROM with_moving_avg ORDER BY insight_date DESC ``` ```sql theme={null} WITH daily_insights AS ( SELECT DATE(date) AS insight_date, reach, total_interactions, accounts_engaged, likes, shares, replies, follows_and_unfollows, profile_views, website_clicks FROM `nekt_raw.instagram_insights_rolling_day` WHERE DATE(date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) ), with_moving_avg AS ( SELECT *, AVG(reach) OVER (ORDER BY insight_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS reach_7day_avg, AVG(total_interactions) OVER (ORDER BY insight_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS interactions_7day_avg, SUM(follows_and_unfollows) OVER (ORDER BY insight_date) AS cumulative_net_followers FROM daily_insights ) SELECT insight_date, reach, ROUND(reach_7day_avg, 0) AS reach_7day_avg, total_interactions, ROUND(interactions_7day_avg, 0) AS interactions_7day_avg, accounts_engaged, follows_and_unfollows AS net_followers, cumulative_net_followers, profile_views, website_clicks FROM with_moving_avg ORDER BY insight_date DESC ``` | insight\_date | reach | reach\_7day\_avg | total\_interactions | interactions\_7day\_avg | accounts\_engaged | net\_followers | cumulative\_net\_followers | profile\_views | website\_clicks | | ------------- | ------ | ---------------- | ------------------- | ----------------------- | ----------------- | -------------- | -------------------------- | -------------- | --------------- | | 2024-11-27 | 12,450 | 11,234 | 892 | 845 | 456 | 23 | 342 | 234 | 45 | | 2024-11-26 | 11,890 | 10,980 | 823 | 812 | 412 | 18 | 319 | 198 | 38 | | 2024-11-25 | 10,234 | 10,567 | 756 | 798 | 389 | 12 | 301 | 187 | 32 | | 2024-11-24 | 8,920 | 10,123 | 654 | 776 | 312 | -5 | 289 | 145 | 28 | | 2024-11-23 | 9,456 | 9,890 | 712 | 754 | 345 | 8 | 294 | 156 | 31 | | 2024-11-22 | 14,230 | 9,678 | 1,023 | 732 | 523 | 45 | 286 | 312 | 67 | ### 4. Comment Engagement Analysis Analyze comment activity on your posts to understand audience sentiment and engagement patterns. **Business Value:** * Measure audience engagement depth * Identify posts that spark conversations * Track comment response rates * Monitor community health and sentiment ```sql theme={null} WITH comment_stats AS ( SELECT m.id AS media_id, m.media_product_type, SUBSTRING(m.caption, 1, 80) AS caption_preview, DATE(m.timestamp) AS posted_date, m.comments_count, COUNT(c.id) AS actual_comments, SUM(c.like_count) AS total_comment_likes, AVG(c.like_count) AS avg_likes_per_comment, SUM(CASE WHEN c.hidden = false THEN 1 ELSE 0 END) AS visible_comments, SUM(CASE WHEN c.hidden = true THEN 1 ELSE 0 END) AS hidden_comments FROM nekt_raw.instagram_media m LEFT JOIN nekt_raw.instagram_comments c ON m.id = c.media_id WHERE DATE(m.timestamp) >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY m.id, m.media_product_type, m.caption, m.timestamp, m.comments_count ) SELECT media_product_type, COUNT(*) AS total_posts, SUM(actual_comments) AS total_comments, ROUND(AVG(actual_comments), 1) AS avg_comments_per_post, SUM(total_comment_likes) AS total_comment_likes, ROUND(AVG(avg_likes_per_comment), 2) AS avg_likes_per_comment, SUM(hidden_comments) AS total_hidden_comments, ROUND( CAST(SUM(hidden_comments) AS DOUBLE) * 100.0 / NULLIF(SUM(actual_comments), 0), 2 ) AS hidden_comment_rate_pct FROM comment_stats GROUP BY media_product_type ORDER BY total_comments DESC ``` ```sql theme={null} WITH comment_stats AS ( SELECT m.id AS media_id, m.media_product_type, SUBSTR(m.caption, 1, 80) AS caption_preview, DATE(m.timestamp) AS posted_date, m.comments_count, COUNT(c.id) AS actual_comments, SUM(c.like_count) AS total_comment_likes, AVG(c.like_count) AS avg_likes_per_comment, SUM(CASE WHEN c.hidden = false THEN 1 ELSE 0 END) AS visible_comments, SUM(CASE WHEN c.hidden = true THEN 1 ELSE 0 END) AS hidden_comments FROM `nekt_raw.instagram_media` m LEFT JOIN `nekt_raw.instagram_comments` c ON m.id = c.media_id WHERE DATE(m.timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY m.id, m.media_product_type, m.caption, m.timestamp, m.comments_count ) SELECT media_product_type, COUNT(*) AS total_posts, SUM(actual_comments) AS total_comments, ROUND(AVG(actual_comments), 1) AS avg_comments_per_post, SUM(total_comment_likes) AS total_comment_likes, ROUND(AVG(avg_likes_per_comment), 2) AS avg_likes_per_comment, SUM(hidden_comments) AS total_hidden_comments, ROUND( SAFE_DIVIDE(CAST(SUM(hidden_comments) AS FLOAT64) * 100.0, SUM(actual_comments)), 2 ) AS hidden_comment_rate_pct FROM comment_stats GROUP BY media_product_type ORDER BY total_comments DESC ``` | media\_product\_type | total\_posts | total\_comments | avg\_comments\_per\_post | total\_comment\_likes | avg\_likes\_per\_comment | total\_hidden\_comments | hidden\_comment\_rate\_pct | | -------------------- | ------------ | --------------- | ------------------------ | --------------------- | ------------------------ | ----------------------- | -------------------------- | | REELS | 12 | 342 | 28.5 | 1,234 | 3.61 | 8 | 2.34 | | FEED | 18 | 198 | 11.0 | 567 | 2.86 | 3 | 1.52 | | STORY | 45 | 0 | 0.0 | 0 | 0.00 | 0 | 0.00 | ## Audience Demographics Analysis ### 5. Follower Geographic Distribution Understand where your followers are located to optimize content timing and targeting. **Business Value:** * Identify key markets and geographic audiences * Optimize posting times for primary time zones * Inform localization and content strategies * Support geo-targeted campaigns ```sql theme={null} WITH country_data AS ( SELECT dimension_value AS country_code, value AS follower_count FROM nekt_raw.instagram_follower_demographics_country_lifetime ), total_followers AS ( SELECT SUM(follower_count) AS total FROM country_data ) SELECT cd.country_code, cd.follower_count, ROUND( CAST(cd.follower_count AS DOUBLE) * 100.0 / tf.total, 2 ) AS percentage, SUM(cd.follower_count) OVER (ORDER BY cd.follower_count DESC) AS cumulative_followers, ROUND( CAST(SUM(cd.follower_count) OVER (ORDER BY cd.follower_count DESC) AS DOUBLE) * 100.0 / tf.total, 2 ) AS cumulative_percentage FROM country_data cd CROSS JOIN total_followers tf ORDER BY cd.follower_count DESC LIMIT 20 ``` ```sql theme={null} WITH country_data AS ( SELECT dimension_value AS country_code, value AS follower_count FROM `nekt_raw.instagram_follower_demographics_country_lifetime` ), total_followers AS ( SELECT SUM(follower_count) AS total FROM country_data ) SELECT cd.country_code, cd.follower_count, ROUND( SAFE_DIVIDE(CAST(cd.follower_count AS FLOAT64) * 100.0, tf.total), 2 ) AS percentage, SUM(cd.follower_count) OVER (ORDER BY cd.follower_count DESC) AS cumulative_followers, ROUND( SAFE_DIVIDE( CAST(SUM(cd.follower_count) OVER (ORDER BY cd.follower_count DESC) AS FLOAT64) * 100.0, tf.total ), 2 ) AS cumulative_percentage FROM country_data cd CROSS JOIN total_followers tf ORDER BY cd.follower_count DESC LIMIT 20 ``` | country\_code | follower\_count | percentage | cumulative\_followers | cumulative\_percentage | | ------------- | --------------- | ---------- | --------------------- | ---------------------- | | BR | 45,230 | 42.35 | 45,230 | 42.35 | | US | 18,920 | 17.72 | 64,150 | 60.07 | | PT | 8,450 | 7.91 | 72,600 | 67.98 | | AR | 6,230 | 5.83 | 78,830 | 73.81 | | MX | 4,890 | 4.58 | 83,720 | 78.39 | | GB | 3,450 | 3.23 | 87,170 | 81.62 | ### 6. Audience Demographics Summary Get a complete view of your audience demographics including age, gender, and location. **Business Value:** * Build comprehensive audience personas * Align content with audience preferences * Support ad targeting decisions * Identify growth opportunities in demographics ```sql theme={null} WITH age_data AS ( SELECT 'age' AS dimension_type, dimension_value, value AS count FROM nekt_raw.instagram_follower_demographics_age_lifetime ), gender_data AS ( SELECT 'gender' AS dimension_type, dimension_value, value AS count FROM nekt_raw.instagram_follower_demographics_gender_lifetime ), city_data AS ( SELECT 'city' AS dimension_type, dimension_value, value AS count FROM nekt_raw.instagram_follower_demographics_city_lifetime ), all_demographics AS ( SELECT * FROM age_data UNION ALL SELECT * FROM gender_data UNION ALL SELECT * FROM city_data ), totals AS ( SELECT dimension_type, SUM(count) AS total FROM all_demographics GROUP BY dimension_type ) SELECT ad.dimension_type, ad.dimension_value, ad.count AS follower_count, ROUND( CAST(ad.count AS DOUBLE) * 100.0 / t.total, 2 ) AS percentage, RANK() OVER ( PARTITION BY ad.dimension_type ORDER BY ad.count DESC ) AS rank_in_category FROM all_demographics ad JOIN totals t ON ad.dimension_type = t.dimension_type ORDER BY ad.dimension_type, ad.count DESC ``` ```sql theme={null} WITH age_data AS ( SELECT 'age' AS dimension_type, dimension_value, value AS count FROM `nekt_raw.instagram_follower_demographics_age_lifetime` ), gender_data AS ( SELECT 'gender' AS dimension_type, dimension_value, value AS count FROM `nekt_raw.instagram_follower_demographics_gender_lifetime` ), city_data AS ( SELECT 'city' AS dimension_type, dimension_value, value AS count FROM `nekt_raw.instagram_follower_demographics_city_lifetime` ), all_demographics AS ( SELECT * FROM age_data UNION ALL SELECT * FROM gender_data UNION ALL SELECT * FROM city_data ), totals AS ( SELECT dimension_type, SUM(count) AS total FROM all_demographics GROUP BY dimension_type ) SELECT ad.dimension_type, ad.dimension_value, ad.count AS follower_count, ROUND( SAFE_DIVIDE(CAST(ad.count AS FLOAT64) * 100.0, t.total), 2 ) AS percentage, RANK() OVER ( PARTITION BY ad.dimension_type ORDER BY ad.count DESC ) AS rank_in_category FROM all_demographics ad JOIN totals t ON ad.dimension_type = t.dimension_type ORDER BY ad.dimension_type, ad.count DESC ``` | dimension\_type | dimension\_value | follower\_count | percentage | rank\_in\_category | | --------------- | ----------------------- | --------------- | ---------- | ------------------ | | age | 25-34 | 38,450 | 36.01 | 1 | | age | 18-24 | 28,920 | 27.08 | 2 | | age | 35-44 | 21,340 | 19.98 | 3 | | age | 45-54 | 10,890 | 10.20 | 4 | | gender | F | 62,340 | 58.37 | 1 | | gender | M | 42,890 | 40.16 | 2 | | gender | U | 1,570 | 1.47 | 3 | | city | São Paulo, Brazil | 12,450 | 11.66 | 1 | | city | Rio de Janeiro, Brazil | 8,920 | 8.35 | 2 | | city | New York, United States | 4,560 | 4.27 | 3 | ## Time-Based Analysis ### 7. Best Days and Times for Posting Identify the best days of the week for engagement to optimize your posting schedule. **Business Value:** * Optimize posting schedule for maximum reach * Identify peak engagement periods * Improve content planning and scheduling * Maximize return on content creation efforts ```sql theme={null} WITH daily_performance AS ( SELECT DATE(date) AS insight_date, DATE_FORMAT(DATE(date), '%W') AS day_of_week, reach, total_interactions, accounts_engaged, profile_views FROM nekt_raw.instagram_insights_rolling_day WHERE DATE(date) >= CURRENT_DATE - INTERVAL '90' DAY ), day_averages AS ( SELECT day_of_week, AVG(reach) AS avg_reach, AVG(total_interactions) AS avg_interactions, AVG(accounts_engaged) AS avg_engaged, AVG(profile_views) AS avg_profile_views, COUNT(*) AS days_counted FROM daily_performance GROUP BY day_of_week ) SELECT day_of_week, ROUND(avg_reach, 0) AS avg_daily_reach, ROUND(avg_interactions, 0) AS avg_daily_interactions, ROUND(avg_engaged, 0) AS avg_accounts_engaged, ROUND(avg_profile_views, 0) AS avg_profile_views, days_counted, CASE WHEN avg_interactions >= (SELECT MAX(avg_interactions) * 0.8 FROM day_averages) THEN 'Peak Day' WHEN avg_interactions <= (SELECT MIN(avg_interactions) * 1.2 FROM day_averages) THEN 'Low Day' ELSE 'Average' END AS day_classification FROM day_averages ORDER BY CASE day_of_week WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 6 WHEN 'Sunday' THEN 7 END ``` ```sql theme={null} WITH daily_performance AS ( SELECT DATE(date) AS insight_date, FORMAT_DATE('%A', DATE(date)) AS day_of_week, reach, total_interactions, accounts_engaged, profile_views FROM `nekt_raw.instagram_insights_rolling_day` WHERE DATE(date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) ), day_averages AS ( SELECT day_of_week, AVG(reach) AS avg_reach, AVG(total_interactions) AS avg_interactions, AVG(accounts_engaged) AS avg_engaged, AVG(profile_views) AS avg_profile_views, COUNT(*) AS days_counted FROM daily_performance GROUP BY day_of_week ) SELECT day_of_week, ROUND(avg_reach, 0) AS avg_daily_reach, ROUND(avg_interactions, 0) AS avg_daily_interactions, ROUND(avg_engaged, 0) AS avg_accounts_engaged, ROUND(avg_profile_views, 0) AS avg_profile_views, days_counted, CASE WHEN avg_interactions >= (SELECT MAX(avg_interactions) * 0.8 FROM day_averages) THEN 'Peak Day' WHEN avg_interactions <= (SELECT MIN(avg_interactions) * 1.2 FROM day_averages) THEN 'Low Day' ELSE 'Average' END AS day_classification FROM day_averages ORDER BY CASE day_of_week WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 6 WHEN 'Sunday' THEN 7 END ``` | day\_of\_week | avg\_daily\_reach | avg\_daily\_interactions | avg\_accounts\_engaged | avg\_profile\_views | days\_counted | day\_classification | | ------------- | ----------------- | ------------------------ | ---------------------- | ------------------- | ------------- | ------------------- | | Monday | 10,234 | 756 | 389 | 187 | 13 | Average | | Tuesday | 11,450 | 823 | 412 | 198 | 13 | Average | | Wednesday | 12,890 | 892 | 456 | 234 | 13 | Peak Day | | Thursday | 13,120 | 945 | 478 | 245 | 13 | Peak Day | | Friday | 11,890 | 834 | 423 | 212 | 13 | Average | | Saturday | 8,920 | 654 | 312 | 145 | 13 | Low Day | | Sunday | 9,456 | 712 | 345 | 156 | 13 | Average | ## Competitor Analysis ### 8. Competitor Benchmarking Compare your account's performance against competitors using Business Discovery data. This requires `competitor_usernames` to be configured. **Business Value:** * Benchmark engagement rates against industry competitors * Identify competitor content strategies that drive high engagement * Track competitor follower growth over time * Discover gaps and opportunities in your content strategy ```sql theme={null} WITH competitor_stats AS ( SELECT username, media_product_type, COUNT(*) AS total_posts, SUM(like_count) AS total_likes, SUM(comments_count) AS total_comments, ROUND(AVG(like_count), 0) AS avg_likes_per_post, ROUND(AVG(comments_count), 0) AS avg_comments_per_post FROM nekt_raw.instagram_business_discovery_media WHERE DATE(timestamp) >= CURRENT_DATE - INTERVAL '90' DAY GROUP BY username, media_product_type ), own_stats AS ( SELECT username, media_product_type, COUNT(*) AS total_posts, SUM(like_count) AS total_likes, SUM(comments_count) AS total_comments, ROUND(AVG(like_count), 0) AS avg_likes_per_post, ROUND(AVG(comments_count), 0) AS avg_comments_per_post FROM nekt_raw.instagram_media WHERE DATE(timestamp) >= CURRENT_DATE - INTERVAL '90' DAY GROUP BY username, media_product_type ) SELECT * FROM own_stats UNION ALL SELECT * FROM competitor_stats ORDER BY media_product_type, avg_likes_per_post DESC ``` ```sql theme={null} WITH competitor_stats AS ( SELECT username, media_product_type, COUNT(*) AS total_posts, SUM(like_count) AS total_likes, SUM(comments_count) AS total_comments, ROUND(AVG(like_count), 0) AS avg_likes_per_post, ROUND(AVG(comments_count), 0) AS avg_comments_per_post FROM `nekt_raw.instagram_business_discovery_media` WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) GROUP BY username, media_product_type ), own_stats AS ( SELECT username, media_product_type, COUNT(*) AS total_posts, SUM(like_count) AS total_likes, SUM(comments_count) AS total_comments, ROUND(AVG(like_count), 0) AS avg_likes_per_post, ROUND(AVG(comments_count), 0) AS avg_comments_per_post FROM `nekt_raw.instagram_media` WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) GROUP BY username, media_product_type ) SELECT * FROM own_stats UNION ALL SELECT * FROM competitor_stats ORDER BY media_product_type, avg_likes_per_post DESC ``` | username | media\_product\_type | total\_posts | total\_likes | total\_comments | avg\_likes\_per\_post | avg\_comments\_per\_post | | ------------- | -------------------- | ------------ | ------------ | --------------- | --------------------- | ------------------------ | | competitor\_a | REELS | 24 | 156,000 | 4,800 | 6,500 | 200 | | your\_account | REELS | 18 | 45,200 | 1,890 | 2,511 | 105 | | competitor\_b | REELS | 12 | 28,400 | 960 | 2,367 | 80 | | competitor\_a | FEED | 15 | 89,000 | 2,400 | 5,933 | 160 | | your\_account | FEED | 22 | 52,300 | 2,640 | 2,377 | 120 | | competitor\_b | FEED | 8 | 12,800 | 480 | 1,600 | 60 | ## Implementation Notes ### Data Quality Considerations * Media insights are only available for content with at least 5 views * Stories data is only available for 24 hours after posting - ensure your pipeline runs frequently to capture this data * User insights have a maximum lookback of 2 years from the Instagram API * Follower demographics require a minimum of 100 followers * Historical follower counts are reconstructed up to 30 days initially due to API constraints, but running regular incremental syncs allows you to build a continuous daily snapshot history beyond that limit. ### API Limitations * The Instagram API has rate limits that may affect extraction times for accounts with large amounts of content * The lookback window parameter helps re-fetch updated metrics for recent content * Some metrics may not be available for all media types (e.g., Reels-specific metrics only apply to Reels) * Business Discovery (competitor data) only works with public Business or Creator accounts. Personal or private accounts cannot be queried * Competitor media only includes public engagement metrics (likes and comments). Private insights like reach, impressions, saves, and shares are not available ### Best Practices * Schedule extractions at least daily to capture Stories data before it expires * Use incremental sync for media and comments to efficiently track updates * Monitor the `reach` metric alongside engagement to understand true content performance * Consider the 28-day rolling insights for trend analysis over the daily insights for day-to-day fluctuations ## Skills for agents Instagram connector documentation as plain markdown, for use in AI agent contexts.