Every team that says "we want video data for trend analysis" starts the same way. Someone points a scraper at three platforms - YouTube, TikTok, and Shorts - pulls a few thousand rows, and everything looks fine in the notebook. Three weeks later the pipeline starts lying. Not loudly. It merges rows, drops rows, or produces per-platform engagement numbers that look real but aren't comparable.
The scraper wasn't the problem. The schema was.
Why three "video" objects don't fit
Each platform has an internally consistent model of what a video is, and the differences don't line up cleanly.
Identity. YouTube keys on videoId (11-char base64-ish string) with channelId for the creator. TikTok keys on a numeric-string video.id with author.secUid and author.uniqueId. Shorts are technically YouTube videos, but a video only becomes a Short if it's vertical and under three minutes - the same videoId can appear in a Shorts feed and the main feed, and each surface has its own URL pattern and a different engagement velocity. Any "cross-platform" table you build ends up needing a canonical concept-level id plus an alias table, not one id.
Time. YouTube's snippet.publishedAt is RFC 3339 UTC with second precision. TikTok's create_time is a Unix-seconds integer with no timezone attached, and the same value looks different for a video first posted via a scheduler. Shorts inherit the underlying YouTube publish time even if the video was re-cut and re-uploaded as a Short a month later. Duration is worse: YouTube returns ISO-8601 via contentDetails.duration (PT1M32S, which will bite you at PT0S for live streams and at P1DT2H for marathons), while TikTok returns integer seconds.
Engagement. The set of counters you can pull differs. YouTube exposes views, likes, comment count. TikTok exposes plays, likes, comments, shares, and saves - and, since it removed public dislikes, no dislikes at all. Shorts expose "plays" that behave differently from a YouTube "view" even for the same underlying asset. Once you ask "engagement per hour since publish" across the three, you can't naively divide one number into another.
Text. YouTube splits title, description, and tags (which are separate from the hashtags a creator drops in the description). TikTok puts everything in one caption field with hashtags embedded, plus duetInfo / stitchInfo flags. Shorts inherit the YouTube text model, but hashtags matter disproportionately in that feed.
Audio. TikTok exposes music.id, music.original, music.duration - a sound-driven medium. YouTube has no equivalent field. A viral TikTok clip is often one sound replicated across thousands of videos; without a sound_id in your normalized row, you can't cluster them.
Aspect ratio and length bucket. 9:16 vs 16:9 is not a cosmetic column. If the dataset feeds a vision model, you'll bucket by aspect ratio before you resize. Mixing 4K landscape with 720p vertical without a bucket column silently degrades training.
The shape of a normalized row
After enough rounds of patches, the row I keep has roughly these fields. It isn't universal - the point is that it's stable enough to write queries against.
canonical_video_id string stable pipeline-assigned id
platform enum youtube | tiktok | shorts
platform_native_id string videoId / video.id
creator_id string channelId / secUid
creator_handle string display handle
publish_ts int64 unix seconds, UTC
duration_s int32
aspect_ratio float e.g. 0.5625 for 9:16
title string caption on TikTok
hashtags array<string>
description string empty on TikTok
play_count int64 views on YT, plays on TT/Shorts
like_count int64
comment_count int64
share_count int64 null on YouTube (not exposed)
save_count int64 null except TikTok
sound_id string TikTok only
is_sound_original bool TikTok only
first_seen_ts int64 when your pipeline saw this row
last_seen_ts int64
Anything the platform doesn't give you is null, never zero. Zero on share_count means "we observed zero shares"; null means "the platform doesn't expose this". If you skip that rule, per-platform comparisons come out backwards.
A normalize function that survives review
import re, hashlib
from datetime import datetime
from isodate import parse_duration # pip install isodate
def canonical_id(platform: str, native_id: str) -> str:
"""Stable id for a row that may be observed on multiple surfaces (Shorts + YouTube)."""
return hashlib.sha1(f"{platform}:{native_id}".encode()).hexdigest()[:16]
def norm_youtube(item: dict) -> dict:
sn = item["snippet"]
cd = item.get("contentDetails", {})
st = item.get("statistics", {})
pub = datetime.fromisoformat(sn["publishedAt"].replace("Z", "+00:00"))
dur_iso = cd.get("duration")
duration_s = int(parse_duration(dur_iso).total_seconds()) if dur_iso else 0
desc = sn.get("description", "") or ""
hashtags = re.findall(r"#(\w+)", desc)
return {
"platform": "youtube",
"platform_native_id": sn["resourceId"]["videoId"],
"creator_id": sn["channelId"],
"creator_handle": sn.get("channelTitle", ""),
"publish_ts": int(pub.timestamp()),
"duration_s": duration_s,
"aspect_ratio": None, # not reliably derivable from v3 API
"title": sn["title"],
"hashtags": hashtags,
"description": desc,
"play_count": int(st.get("viewCount", 0)),
"like_count": int(st["likeCount"]) if "likeCount" in st else None,
"comment_count": int(st["commentCount"]) if "commentCount" in st else None,
"share_count": None,
"save_count": None,
"sound_id": None,
"is_sound_original": None,
}
def norm_tiktok(item: dict) -> dict:
stats = item.get("stats", {})
ts = int(item.get("create_time", 0))
caption = item.get("desc", "") or ""
hashtags = re.findall(r"#([\w\u4e00-\u9fff]+)", caption)
video = item.get("video", {})
w = video.get("width") or 0
h = video.get("height") or 0
music = item.get("music") or {}
return {
"platform": "tiktok",
"platform_native_id": str(item["id"]),
"creator_id": item["author"]["secUid"],
"creator_handle": item["author"]["uniqueId"],
"publish_ts": ts,
"duration_s": int(video.get("duration", 0) // 1000), # ms -> s
"aspect_ratio": (h / w) if (h and w) else None,
"title": caption.split("#", 1)[0].strip(),
"hashtags": hashtags,
"description": "",
"play_count": int(stats.get("playCount", 0)),
"like_count": int(stats.get("diggCount", 0)),
"comment_count": int(stats.get("commentCount", 0)),
"share_count": int(stats.get("shareCount", 0)),
"save_count": int(stats["collectCount"]) if "collectCount" in stats else None,
"sound_id": str(music.get("id")) if music else None,
"is_sound_original": bool(music.get("original", False)) if music else None,
}
def to_row(n: dict, first_seen: int) -> dict:
row = dict(n)
row["canonical_video_id"] = canonical_id(n["platform"], n["platform_native_id"])
row["first_seen_ts"] = first_seen
row["last_seen_ts"] = first_seen
return row
Small, and not exhaustive. The point is that each norm_* maps a platform-specific response into the same field names and uses None for what the platform doesn't expose. Anything downstream (dedup, engagement curves, model input) reads row["like_count"] and doesn't care where it came from.
The aliasing that turns platform_native_id into canonical_video_id is where most pipelines eventually break. Rule of thumb: keep two ids - one for the row you observed on a given surface, one for the concept ("this piece of content") that may show up on two or three surfaces. A YouTube Short's videoId is the same as its main-feed videoId, but you still want those two observations to be joinable at concept level, not deduped into oblivion.
Things that break in week three
Timezones. TikTok's create_time is nominally UTC-seconds, but tooling sometimes interprets it in the creator's local zone. Fix it at ingest, not at query time.
Deleted / private videos. A row you scraped last week is 404 today. Keep first_seen_ts and last_seen_ts on every row, and never overwrite the earliest first_seen. Your "engagement over time" charts need both columns.
Play-count ceilings. TikTok's public playCount is known to saturate around the 600k mark in some response paths. Treat values at that magnitude as a floor, not a true count.
Shorts inheriting YouTube comments. If a creator re-edits a landscape video into a vertical Short with the same underlying asset, the comment stream is shared. Dedup comments by comment id, not by row.
duration at PT0S and P1DT2H. Both are technically valid ISO-8601 durations, and both will crash a naive parse_duration(d).seconds. Use .total_seconds(), and bucket live vs VOD before aggregating.
Tags are not hashtags. YouTube tags are metadata the creator picks (often keyword-stuffed); hashtags in the description are what viewers actually see. Do not merge them into one column or your topic model will drift.
Aspect ratio isn't in the YouTube v3 API. You can infer verticality from duration under 3 minutes plus a channel that ships Shorts, but inference is not measurement. If the pipeline depends on a real aspect ratio, either scrape the player metadata, key it from a video-download pass, or leave it null.
Buy or scrape?
If you need today's videos, or one specific creator's corpus, scrape it - the field mapping above is essentially the whole job. If you need a historical window across thousands of creators ("all videos that mention brand X in the last 90 days, with their engagement curves"), scraping row-by-row is slower, blockier, and more IP-hungry than you'd want. That's the case where a pre-built multi-platform dataset is a better shape: fewer requests, more coverage per dollar, and you start from a normalized table instead of an ETL job.
I work with Thordata on their video products (partner disclosure - this is not a neutral review), and their multi-platform video datasets and video data scraper sit on either side of that trade. What I'd actually recommend, though, is running the normalize function above on a small sample from whatever source you end up with - buy, scrape, or a mix - before you commit to a schema. The mapping step is the fragile part. Fetch is a boring choice you can make later on cost per row.
Top comments (0)