Plattform
API
Anwendungsfälle
Ressourcen
Funktionen
Integrationen
Anwendungsfälle
Funktionen
Integrationen
Anwendungsfälle
Brands pay creators to post about their products. Inside a brand, one marketing team usually owns that budget and has to prove it worked. Modash Campaigns is where that team measures and reports the results: which creators posted, how each post performed, and what the whole campaign added up to.
A Modash campaign follows a set of creators, the keywords and mentions that tie a post to the campaign, and the dates it runs. Open one and you get three tabs: Overview with the campaign totals, Creators with a row of stats for each creator, and Content with the posts. Every query in this story serves one of those three views.
Three kinds of data sit behind that page. Configuration data is the campaign record: the creators list, keywords, and dates. Raw data is the post as the social platform returned it. Mapped data is the posts that match configuration data, plus the stats we aggregate from the raw data for the campaign: engagement rate, impressions, views count, likes count, comments count, and the rest of the numbers on the page. For years, a background job did that matching and aggregation ahead of time, and the page only read the result. Raw and mapped data moved between databases more than once. In the end we stopped storing mapped data at all.
Configuration data stayed in MongoDB the entire time. Here is where the raw and mapped data lived, and what pushed us to move each time:
The goal we had: a campaign with 5,000 creators should open as fast as one with 10. When the Lambda started timing out, we were nowhere near it. Today, a campaign with 2,300 creators loads in 9 seconds.
If you like the idea of swapping a database while customers are still using it, read on. Every row above has a story.
The shapes explain why the mapping was the expensive part. The examples are Instagram, trimmed, with made-up values.
A raw post is one row per post for each day we stored it. It knows nothing about campaigns:
{
"channel": "INSTAGRAM",
"user_id": "1234567",
"content_id": "C8xYz01",
"posted_at": "2026-05-14T18:02:00Z",
"snapshot_at": "2026-05-20T03:00:00Z",
"author_handle": "jane.doe",
"caption": "Morning routine with @brand #ad #skincare",
"mentions": ["brand"],
"hashtags": ["ad", "skincare"],
"collaborators_ids": ["7654321"],
"like_count": 3120,
"comment_count": 184,
"play_count": 91000,
"share_count": 240,
"raw": { "...": "the full response from the platform" }
}
Mapped data belongs to one campaign. It had three parts: the posts that matched the campaign, one stats row per creator, and one record of campaign totals.
A post, the same post as above once it matched the campaign, with its own stats:
{
"channel": "INSTAGRAM",
"contentId": "C8xYz01",
"pk": "1234567",
"userUniqueId": "jane.doe",
"createTime": 1778781720,
"productType": "clips",
"description": "Morning routine with @brand #ad #skincare",
"mentions": ["brand"],
"hashtags": ["ad", "skincare"],
"collaborators": [{ "handle": "sam.k", "influencerId": "7654321" }],
"postUrl": "<https://www.example.com/post/C8xYz01/>",
"isFrozen": false,
"stats": {
"likeCount": 3120,
"commentCount": 184,
"reelPlayCount": 91000,
"shareCount": 240,
"engagementRatePercentage": 6.9,
"reach": 20500,
"impressions": 29700,
"emv": 612.4
}
}
A creator row; everything that creator posted for the campaign, added up:
{
"userId": "1234567",
"username": "jane.doe",
"stats": {
"numberOfPosts": 3,
"numberOfReels": 2,
"numberOfStories": 4,
"likeCount": 9800,
"commentCount": 610,
"reelPlayCount": 240000,
"totalEngagement": 10410,
"followerCount": 48200,
"averageEngagementRate": 4.3,
"reach": 61000,
"impressions": 88000,
"emv": 1830.5,
"statsFromCollaborations": {
"likeCount": 2100,
"commentCount": 95,
"reelPlayCount": 48000,
"handles": ["sam.k"]
}
}
}
The overview, one row of totals for the whole campaign:
{
"numberOfPosts": 410,
"numberOfReels": 655,
"numberOfStories": 1320,
"likeCount": 1284000,
"commentCount": 76300,
"reelPlayCount": 31200000,
"shareCount": 41800,
"totalEngagement": 1402100,
"averageEngagementRate": 3.8,
"reach": 8100000,
"impressions": 11700000,
"emv": 241000,
"influencersPostedCount": 212,
"publishedContentCount": 2385,
"totalContentCount": 2410,
"postsToday": 14
}
Getting from the raw shape to the mapped took several steps for every campaign:
Campaign posts and influencers lived in PostgreSQL, on an RDS cluster. Back then, campaigns only covered Instagram. The ORM built very long queries to map the stats, some failed just because of their length, and saving a campaign with more than 500 creators timed out. TikTok campaigns had just been built on DynamoDB, so when Instagram needed a rewrite, we moved it onto the same setup.
Raw data and mapped stats both lived in DynamoDB.
The mapped table used a hash of the campaign's configuration as its partition key. Reading a campaign meant one query on one partition, and was very fast. Sorting the posts by likes was an entirely different story. In order do that, DynamoDB needs an index built for that exact sort, created before the query runs. A table gets a default of 20 of those indexes (GSIs).
Inside a partition, the sort key decides the order. Posts were stored with a sort key of <publish time>#<post id>, so they came back in date order and a date range filter was easy to query.
The creators tab needed one creator's posts, which that key could not give us. So we already had one GSI; influencerIndex: same partition key, sort key <creator handle>#<publish time>. Its projection was ALL, so DynamoDB kept a second copy of every post, captions, counts, URLs and all, and wrote to both on every update. Sorting by likes would have meant a third copy, keyed on the like count. And same for all the stats we show as columns in the creators table, since all the columns are sortable. And there are 30 of them.
We did consider keeping DynamoDB and putting OpenSearch beside it, fed by a stream and a Lambda. That meant a second copy of the stats, a pipeline to babysit forever, and the campaign configuration still living in a third place. The page would show numbers that had not caught up yet, and every schema change would mean a re-indexation. In a design note from September 2025 we listed ClickHouse as an option, turned it down as "one database too many", and moved the mapped data into MongoDB. Nine months later we were writing ClickHouse tables.
A mapper Lambda read raw data from DynamoDB, matched them to the campaign, computed the stats, and wrote four MongoDB collections: campaign_mapped_content for posts, campaign_mapped_influencer_stats for each creator, campaign_mapped_overview_stats for the totals, and campaign_mapped_status for whether the job had finished.
The shape changed on the way over. In DynamoDB, the creator stats for a campaign were one item holding a map from creator id to stats. In MongoDB, every creator got their own document. The filter hash (aka the DDB partition key) went away too. A mapped result was found by campaign id and date range. In DynamoDB, sorting creators meant loading the whole map and sorting it in JavaScript. In MongoDB, each creator is its own document.
Sorting by likes was finally just an index. A question across campaigns was finally just a query.
Then MongoDB + AWS Lambda became the bottleneck; AWS gives a Lambda 15 minutes. A large campaign needed more. Fifteen minutes is a long time to watch a spinner, and a short time to precompute thousands of creators in MongoDB. Mongo could simply not do it faster. Writing every slice ahead of time was never going to reach 5,000 creators.
We had four options. Query times are at about 10 million rows, numbers are estimates. We picked ClickHouse.
In April 2026 we went back to the database we had politely declined. This time it got the raw posts. There is no mapped collection to keep warm. When someone opens the page, the query filters the raw rows by the campaign's creators, keywords, and dates, then aggregates them. That query is the mapping. ClickHouse can carry it.
In ClickHouse, the schema decides most of the speed and it cannot be easily changed later. The table engine (MergeTree, ReplacingMergeTree, AggregatingMergeTree) is fixed when the table is created. The sort key decides which rows a query can skip. The partition key decides where every insert lands. Codecs decide how much disk storage years of snapshots will take. The tokenizer behind a text index is baked in when the index is built. Get one of them wrong and the fix is expensive: a new table/migration and a backfill.
So we took our time. The first tables went in on June 9, 2026. The final shape landed on July 23, after three rounds of changes. In between we wrote down every query the page makes and checked each one against the model.
The main table, raw_content_items, keeps a snapshot history of every post: one row per post for each day we stored it. We call it the history table. It is a ReplacingMergeTree, versioned by snapshot_at. Rows that share a sort key collapse to the newest snapshot whenever a background merge gets around to it. The sort key we went with:
ORDER BY (channel, user_id, snapshot_date, content_id)
It goes from low cardinality to high: three social channels, then the creator, then the day, then the post. Every query names a social channel and a creator, so it narrows to a few granules straight away.
snapshot_date is in the key on purpose. A post stored on ten different days is ten rows, which gives us history. It also makes it expensive to ask "what are the numbers right now?" Those reads were walking through every day we had ever stored for that creator.
Creators get a table of their own. raw_influencers keeps one row per creator for each day we stored their profile: handle, avatar, follower count, verified flag, country, etc. Same engine, same partitioning, and the same sort key as the history table, minus the post:
ORDER BY (channel, user_id, snapshot_date)
We added two tables next to the history table. No code writes to them directly. A materialized view fills each one on every insert into raw_content_items.
raw_content_items_latest fixes the "right now" problem. It holds only the newest snapshot of each post: the history table with the date taken out of the key.
ORDER BY (channel, user_id, content_id)
In progress campaigns read that table. Finished campaigns pin a snapshot time and read the history table. Yes, the raw_content_items_latest stores another copy. In exchange, an open campaign no longer rereads a creator's entire history on every load.
content_item_collaborators is another table we've created for optimization and keeps, for every Instagram post and reel, the full list of creators on it. A collab post is stored once per creator, and each snapshot can name a different set of collaborators. This table merges them all, so the campaign knows every creator on the post.
Storing every snapshot forever sounds expensive until you let the column types do the work. ClickHouse compresses each column in two steps. The first step is picked for the kind of data in the column. The second is a general compressor, like LZ4 or ZSTD, that shrinks what's left. Timestamps only move forward, so they get CODEC(DoubleDelta, LZ4). Likes and plays are counters whose high bits are mostly zero, so they get CODEC(T64, LZ4). Captions and URLs are text with plenty of repetition inside them, so they get ZSTD(3). Social channel, media type, and city have few distinct values and are LowCardinality.
The partition key is the one we got wrong. We started with one partition per week of snapshots:
PARTITION BY toMonday(snapshot_date)
Live inserts were happy with that, since a day's snapshots land in a single week. Then the DynamoDB backfill arrived. One insert from the export carried snapshots going back years, and ClickHouse refused it: it will not write into more than 100 partitions in a single insert. We switched to months:
PARTITION BY toYYYYMM(snapshot_date)
A hundred months is more than eight years, and the ClickHouse docs say you rarely need anything finer than a month anyway.
Every keyword question goes through one column. all_match_tokens is computed when the row is inserted. It collects the hashtags, the mentions, the post's keywords, every word of the caption and the title, and the accounts tagged in the post. A text index sits on top of it. Matching a post to a campaign then comes down to one function call:
hasAnyTokens(all_match_tokens, {keywords:Array(String)})
The tokenizer has to match the data. all_match_tokens is already an array of words, so its index uses the array tokenizer and takes each element as it is. Captions and titles get their own text index with splitByNonAlpha, which turns #fashion into fashion and @nike into nike.
Doing the tokenizing once, at insert, means the get request never parses a caption. It also means a bug in the tokenizer is written into every row until you rebuild the column. Hold that thought.
The stats queries also use ClickHouse's query cache for five minutes. A question asked twice in that window is answered from memory, and the cluster gets a short break.
We moved without a maintenance window, in five steps: write new posts to both databases, copy the history over, read from both sources and report differences, move teams to reading only from ClickHouse, cleanup.
History came in through S3, one social channel at a time:
DynamoDB can export a whole table to S3 from its point-in-time backup without touching live traffic. In production, YouTube (3 million items, 7 GB) took five minutes, TikTok (3.7 million items, 79 GB) took nine minutes, and Instagram (37.6 million items, 531 GB) took eight and a half hours. AWS charges by the gigabyte scanned, and a full YouTube export from production costs about the price of lunch.
On the ClickHouse side, a one-time ClickPipe read the exported files and wrote every line into a staging table, one per social channel: staging_instagram_raw, staging_tiktok_raw, staging_youtube_raw.
The staging table only exists to be inserted into, because that is what makes the materialized views fire. There is one view per social channel + record type; eight in total. Each view reads the DynamoDB JSON and writes clean rows into the final tables:
CREATE MATERIALIZED VIEW mv_instagram_post_to_content
TO raw_content_items
AS SELECT
'INSTAGRAM' AS channel,
JSONExtractString(Item, 'userId', 'S') AS user_id,
JSONExtractString(Item, 'code', 'S') AS content_id,
JSONExtractString(Item, 'caption', 'M', 'text', 'S') AS caption,
-- about 400 more lines of this
FROM staging_instagram_raw
WHERE JSONExtractString(Item, 'dataType', 'S') IN ('post', 'reel');
The backfill and the live dual-write ran into the same tables at the same time, and that was fine. Both land in a ReplacingMergeTree versioned by snapshot_at. A live snapshot is always newer than an exported one, so when both rows exist, the live one wins. Nobody had to coordinate the order.
The first was memory. The raw column (where we stored the original response as is) is ClickHouse's JSON type, and is expensive at insert time. At 16 GB memory, inserts failed with Code 241: MEMORY_LIMIT_EXCEEDED. We raised the floor to 64 GB for the load.
The second was the two tables that are fed by materialized views. Per ClickHouse docs, rows that a materialized view writes into a table are passed on to that table's own views. During the DynamoDB import, one staging insert went through two sibling views into raw_content_items, for example Instagram posts and Instagram stories. Only one sibling's rows reached the next views. content_item_collaborators ended up with 4.8 million out of 12.85 million Instagram posts. Live writes were fine, since they insert into raw_content_items directly. We filled both tables again with a plain INSERT ... SELECT from the history table and it worked.
The third was how lazy merges are. A ReplacingMergeTree drops duplicates only when a background merge gets around to them, and we were surprised how rarely that happens. Luckily, storage is cheap. We ended up running OPTIMIZE ... FINAL manually, one partition at a time.
The last customers moved over to ClickHouse on September 21, 2026. Zero downtime, zero issues reported. We changed the plane in flight, and the passengers noticed nothing, except that the plane was flying faster.
How much faster depends on the size of the campaign:
Small campaigns were already fast, and they stayed fast. The gain lands on the customers who run campaigns for years, with thousands of creators and posts. Of course, the numbers are the ClickHouse query time, it does not account for other layers of optimizations we have on top of it. The customer does not normally wait more than 3s on the page.
ClickHouse holds the raw posts. The page aggregates them and computes the stats on read. MongoDB still holds the campaign configuration data: creators list, keywords, dates. There is no mapped-stats collection and status polling in the path anymore.
The page stayed up because we didn't switch to the new database on day one. Every read ran on both databases, customers got the MongoDB answer, and we logged every difference. Each difference was a bug to fix before anyone saw it. Teams moved over in batches, only once the differences stopped.
That is the campaigns team at Modash. A page marketers have open while you change everything underneath it, a migration you roll out incrementally, always striving to find the best tools for the job, adapting as many times as needed and staying on top of the demands. The next hard query is already in the backlog: Campaigns Insights, one of the most requested Campaigns features for years, is being built on this foundation. We'll keep you posted.
