Instagram data in BigQuery

CREATE SCHEMA IF NOT EXISTS instagram;

CREATE TABLE IF NOT EXISTS instagram.profile_snapshots (
  snapshot_date DATE NOT NULL,
  user_id STRING NOT NULL,
  username STRING,
  full_name STRING,
  followers INT64,
  following INT64,
  posts INT64,
  is_verified BOOL,
  category STRING
) PARTITION BY snapshot_date;

CREATE TABLE IF NOT EXISTS instagram.posts (
  fetched_at TIMESTAMP NOT NULL,
  post_id STRING NOT NULL,
  shortcode STRING,
  username STRING,
  type STRING,          -- photo | video | carousel
  product_type STRING,  -- feed | clips (reels)
  caption STRING,
  taken_at TIMESTAMP,
  like_count INT64,
  comment_count INT64,
  view_count INT64
) PARTITION BY DATE(fetched_at);

# pip install requests google-cloud-bigquery
import os, datetime, requests
from google.cloud import bigquery

API = "https://api.profilequery.com/v1"
HEADERS = {"Authorization": f"Bearer {os.environ['INSTAGRAMAPI_KEY']}"}
ACCOUNTS = ["nike", "adidas", "puma"]

def get(path, **params):
    r = requests.get(f"{API}/{path}", headers=HEADERS, params=params, timeout=60)
    r.raise_for_status()
    return r.json()["data"]

now = datetime.datetime.now(datetime.timezone.utc)
profiles, posts = [], []

for handle in ACCOUNTS:
    p = get("profile", handle=handle)
    profiles.append({
        "snapshot_date": now.date().isoformat(),
        "user_id": p["id"], "username": p["username"], "full_name": p["full_name"],
        "followers": p["followers"], "following": p["following"], "posts": p["posts"],
        "is_verified": p["is_verified"], "category": p["category"],
    })
    for m in get("profile/posts", handle=handle)["items"]:
        posts.append({
            "fetched_at": now.isoformat(), "post_id": m["id"], "shortcode": m["shortcode"],
            "username": handle, "type": m["type"], "product_type": m["product_type"],
            "caption": m["caption"], "taken_at": m["taken_at"],
            "like_count": m["like_count"], "comment_count": m["comment_count"],
            "view_count": m["view_count"],
        })

bq = bigquery.Client()
append = bigquery.LoadJobConfig(write_disposition="WRITE_APPEND")
bq.load_table_from_json(profiles, "instagram.profile_snapshots", job_config=append).result()
bq.load_table_from_json(posts, "instagram.posts", job_config=append).result()
print(f"Loaded {len(profiles)} profiles, {len(posts)} posts")

gcloud run jobs deploy instagram-to-bq --source . --region us-central1 \
  --set-secrets INSTAGRAMAPI_KEY=instagramapi-key:latest

gcloud scheduler jobs create http instagram-to-bq-daily \
  --location us-central1 --schedule "0 6 * * *" \
  --uri "https://run.googleapis.com/v2/projects/$PROJECT/locations/us-central1/jobs/instagram-to-bq:run" \
  --http-method POST --oauth-service-account-email "$SA_EMAIL"

WITH latest_posts AS (
  SELECT * FROM instagram.posts
  WHERE taken_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY fetched_at DESC) = 1
),
followers AS (
  SELECT username, followers FROM instagram.profile_snapshots
  WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM instagram.profile_snapshots)
)
SELECT
  p.username,
  COUNT(*) AS posts_30d,
  ROUND(AVG(p.like_count + p.comment_count) / ANY_VALUE(f.followers) * 100, 3) AS engagement_rate_pct
FROM latest_posts p JOIN followers f USING (username)
GROUP BY p.username
ORDER BY engagement_rate_pct DESC;

SELECT
  username,
  snapshot_date,
  followers,
  followers - LAG(followers, 7) OVER (PARTITION BY username ORDER BY snapshot_date) AS gained_7d
FROM instagram.profile_snapshots
ORDER BY username, snapshot_date DESC;

SELECT
  EXTRACT(HOUR FROM taken_at) AS hour_utc,
  COUNT(*) AS posts,
  ROUND(AVG(like_count + comment_count)) AS avg_engagement
FROM instagram.posts
QUALIFY ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY fetched_at DESC) = 1
GROUP BY hour_utc
ORDER BY avg_engagement DESC;

SELECT LOWER(tag) AS hashtag, COUNT(*) AS uses, ROUND(AVG(like_count)) AS avg_likes
FROM instagram.posts, UNNEST(REGEXP_EXTRACT_ALL(caption, r'#(\w+)')) AS tag
GROUP BY hashtag
HAVING uses >= 3
ORDER BY avg_likes DESC
LIMIT 25;