How to design your ad data schema with Claude

Explore Meta and TikTok with Claude

With the MCP running, the first move is exploring the data sources the datasets will be built from. Claude analyzes all the available accounts (in the demo they’re anonymized and some are duplicated; with your real sources that won’t happen).

The goal of the exercise: an architecture to store, say, three Meta accounts and three TikTok accounts, the typical case of an agency that needs ad-level data for many clients.

The sampling prompt

To design the database well you need to know which metrics and dimensions are relevant. In the video, Claude is asked to work it out by sampling real data:

Let’s sample 30 accounts: take 30 from each source. I want to design historical storage for ad-level data, and I want to know which metrics are relevant in each account. Let’s run account-level pulls with metrics to see what each one actually uses, and from that knowledge extract which metrics matter so we can then create the table groups in Detrics, with those metrics and every relevant ad-level dimension.

What Claude does with that request

Claude orchestrates the sampling: it fires queries in parallel, in batches of 10 accounts per platform. If a platform returns an error (incompatible fields, for example), it takes the error and fixes the query on its own until the data comes back.

Once it knows which metrics are compatible and in use at account level, it moves to the interview phase: it asks about your preferences for the schema and, before touching anything, proposes the design (core ad-level dimensions and metrics, daily aggregation for Meta and TikTok) and asks for confirmation. This takes a while; while it runs, the next chapter explains how the Detrics warehouse works under the hood.