Exporting Social Media Data to CSV and BigQuery for Analysis

Pulling social data from an API is the easy part. The part that trips teams up is turning nested, per-platform JSON into something an analyst can pivot in a spreadsheet or a data team can query in a warehouse. Nested objects, arrays of posts, fields named differently on every platform, none of that drops cleanly into a table by itself. This walks through the flattening and loading step, so the data you pull actually becomes analyzable.

The core problem: nested JSON to flat rows

Social API responses are nested by nature, a profile contains a stats object, a posts response contains an array of items, each with its own nested caption and stats. A CSV or a warehouse table wants flat rows with simple columns. So the real work is a deliberate flattening step: decide which nested fields become columns, and pull them up.

Doing this explicitly (rather than dumping raw JSON) is what makes the export usable. It also forces you to read fields defensively, which you should be doing anyway.

Step 1: Pull and flatten to rows

Grab the data, then map each record to a flat dictionary of exactly the columns you want. Base URL https://api.sociavault.com/v1, x-api-key header, 1 credit per call, payload under data:

import os, csv, requests

API_KEY = os.environ["SOCIAVAULT_API_KEY"]
BASE = "https://api.sociavault.com/v1"

def get(path, **params):
    r = requests.get(f"{BASE}{path}", headers={"x-api-key": API_KEY},
                     params=params, timeout=60)
    r.raise_for_status()
    return r.json().get("data")

def flatten_tiktok_videos(handle):
    data = get("/scrape/tiktok/videos", handle=handle, amount=30)
    items = data.get("aweme_list", []) if isinstance(data, dict) else []
    rows = []
    for v in items:
        stats = v.get("statistics", {}) or {}
        rows.append({                       # flat = one column per key
            "handle": handle,
            "video_id": v.get("aweme_id"),
            "created_unix": v.get("create_time"),
            "plays": stats.get("play_count"),
            "likes": stats.get("digg_count"),
            "comments": stats.get("comment_count"),
            "shares": stats.get("share_count"),
        })
    return rows

Note the defensive .get() everywhere and the fixed, explicit column set. TikTok video stats live under aweme_list[].statistics with play_count, digg_count, etc., log one raw response to confirm before trusting field names. A stable schema you define beats dumping whatever the API returned.

Step 2: Write clean CSV

With flat rows, CSV is trivial, and doing it right (consistent headers, UTF-8, quoting) saves your analyst grief:

def write_csv(rows, path):
    if not rows:
        return
    fields = list(rows[0].keys())
    with open(path, "w", newline="", encoding="utf-8") as f:
        w = csv.DictWriter(f, fieldnames=fields)
        w.writeheader()
        w.writerows(rows)

rows = flatten_tiktok_videos("mrbeast")
write_csv(rows, "tiktok_videos.csv")

Two things that prevent headaches downstream: keep the column set identical across runs (so appended files stay consistent), and store timestamps as raw Unix values plus, if you like, an ISO string, don't bake in a timezone and confuse everyone later.

Step 3: Load into BigQuery

For anything beyond a spreadsheet, a warehouse is where this belongs. The clean path is: define a schema once, then stream or batch-load your flat rows. Using the BigQuery Python client:

from google.cloud import bigquery

def load_to_bigquery(rows, table_id):
    client = bigquery.Client()
    # explicit schema keeps types stable across loads
    schema = [
        bigquery.SchemaField("handle", "STRING"),
        bigquery.SchemaField("video_id", "STRING"),
        bigquery.SchemaField("created_unix", "INTEGER"),
        bigquery.SchemaField("plays", "INTEGER"),
        bigquery.SchemaField("likes", "INTEGER"),
        bigquery.SchemaField("comments", "INTEGER"),
        bigquery.SchemaField("shares", "INTEGER"),
    ]
    job = client.load_table_from_json(
        rows, table_id,
        job_config=bigquery.LoadJobConfig(
            schema=schema,
            write_disposition="WRITE_APPEND",  # append, keep history
        ),
    )
    job.result()  # wait for the load

WRITE_APPEND is deliberate: you want history in the warehouse so you can run trend queries later. Define the schema explicitly rather than autodetecting, so a stray null or a new field doesn't silently change your column types between loads.

Making it a pipeline

Wrap pull, flatten, load into a scheduled job and you've got a real data pipeline: fresh social metrics landing in BigQuery daily, ready for whatever your analysts build on top. Add a loaded_at column so you can tell snapshots apart, and lean on the credit-saving habits so a big backfill doesn't surprise you. If a warehouse is overkill, the Sheets/Airtable route is the lighter version of the same idea.

The honest limits

• Flattening is a decision, not a default. You choose which nested fields become columns; there's no automatic "correct" flat shape. Design the schema on purpose.
• Field paths differ per platform. A TikTok row and an Instagram row won't share a schema by default. Either keep per-platform tables or map to a common schema deliberately.
• Explicit schemas beat autodetect. Letting the warehouse guess types invites silent breakage when a field is null or changes. Define types once.
• Public metrics only. You're exporting public data, not private reach/impressions. The warehouse doesn't change what the API can and can't see.
• Watch backfill costs. Loading months of data means many API calls, each a credit. Cap and schedule backfills; don't loop unbounded.

Frequently Asked Questions

How do I turn nested social API JSON into a CSV?

Flatten it first: map each nested record to a flat dictionary with one key per column (pulling values up from nested stats/caption objects), then write those rows with a CSV writer. Defining an explicit, stable column set is what makes the export usable.

Can I load social data into BigQuery?

Yes. Flatten the API response to rows, define an explicit BigQuery schema, and load with the Python client using WRITE_APPEND to preserve history. Explicit schemas keep column types stable across loads and avoid silent drift.

Why not just dump the raw JSON?

Because nested JSON is painful to analyze in a spreadsheet or SQL, and raw dumps drift in shape over time. Choosing your columns deliberately gives analysts a stable, queryable table and forces the defensive field-reading you want anyway.

How do I handle different platforms in one warehouse?

Either keep a table per platform (simplest) or map each platform's fields into a shared common schema on purpose. Platforms name fields differently, so there's no automatic unified shape, decide it explicitly.

How should I store timestamps?

Keep the raw Unix value (and optionally an ISO string), and avoid baking in a timezone. That keeps timestamps unambiguous across tools and prevents the classic "off by X hours" confusion later.

Will a large historical export cost a lot of credits?

It can, each API call is 1 credit, so backfilling months of data is many calls. Cap your pagination, schedule backfills, and monitor usage. You start with 50 free credits and no card to test the pipeline first.

---

Want clean social data flowing into your warehouse? Start free with 50 credits, no card required and build your export pipeline today.