A field report from building a full clickstream analytics pipeline for a nonprofit donation platform, from a browser click all the way to a live Power BI dashboard, including every real bug we hit along the way.
The Problem We Were Actually Solving
We run a donation platform for a nonprofit (I'll call it xyz_foundation throughout this post) built on Gatsby (React) with a serverless AWS backend (Amplify Gen2 / CDK). The donation flow is a multi-step form: pick a donation type, an amount, a recurrence, who's giving, a region, a payment method, then complete payment via Stripe or PayPal.
The problem: we had zero visibility into how people actually used that form. We didn't know which donation amounts were most popular, where people dropped off in the funnel, whether people preferred one-time or recurring gifts, or which payment method won out. Our own UX team was designing changes to this flow based on guesswork, not data.
We needed:
- Real click and page-view tracking across the whole site, especially the donation funnel.
- A way to query and aggregate that data cheaply, without building a whole data engineering team's worth of infrastructure.
- A dashboard a non-technical UX designer could actually use to answer real questions ("what's the most popular donation amount?", "where do people abandon the form?").
This post walks through the entire stack we ended up with, why we made each decision, and every bug we hit building it, since those are the parts nobody writes about in the clean architecture diagrams.
The Architecture, End to End
Every piece of this is serverless: no servers to patch, no clusters to manage, and (as I'll show at the end) genuinely cheap at low-to-moderate scale.
Part 1: Getting Data Off the Browser
The first design decision we got wrong
Our original plan was the "obvious" one: use the AWS Amplify Analytics SDK to write directly from the browser to Kinesis Firehose, using temporary AWS credentials issued via a Cognito Identity Pool.
This is a complete dead end for anonymous visitors, and it's worth explaining why, because it's a trap a lot of teams fall into. Cognito Identity Pools have a setting called "unauthenticated identities," and if it's disabled (which is common, sometimes for security/compliance reasons, sometimes because someone else disabled it), anonymous visitors cannot get AWS credentials at all. No credentials means no way to directly write to Firehose. This isn't a code bug, it's a live account-level setting, and in our case, changing it wasn't an option (it was a shared setting affecting other parts of the platform).
We only found this out after building the whole direct-write path and testing it, which cost real time. Lesson: verify your identity provider's anonymous-access settings before designing anything that depends on unauthenticated AWS credentials.
What we built instead: an API Gateway proxy
Since the browser can't hold AWS credentials, we put a plain HTTPS endpoint in front of Firehose:
The Lambda is the only thing in the whole system with permission to write to Firehose. The browser never touches AWS directly; it just POSTs a small JSON envelope to a public URL. This is a strictly better architecture than the credentials-in-the-browser approach anyway, even setting aside the Cognito issue: it means we can validate, rate-limit, and reject malformed payloads server-side before anything reaches the data lake, and revoking access is as simple as changing one Lambda's IAM policy, not credentials that were previously pushed to every browser that ever visited the site.
// Simplified shape of the ingest handler
export const handler = async (event) => {
const body = event.body;
if (!body || body.length > MAX_BODY_BYTES) {
return { statusCode: 400, body: "Invalid request" };
}
const envelope = JSON.parse(body);
const data = Buffer.from(`${JSON.stringify(envelope)}\n`, "utf-8");
await firehose.send(new PutRecordCommand({
DeliveryStreamName: STREAM_NAME,
Record: { Data: data }
}));
return { statusCode: 202, body: "Accepted" };
};
Two details worth calling out:
The trailing \n is not optional. Firehose concatenates record bytes in S3 with no separator by default. If you don't manually add a newline between JSON records, multiple events landing in the same S3 file produce a single invalid JSON blob, silently breaking every downstream query engine that expects newline-delimited JSON (NDJSON). This is the kind of thing that works perfectly in testing (one record per file) and breaks the moment real traffic causes multiple records to batch together.
Always return success, even on internal failure. A broken analytics pipe should never surface as a visible error to a real visitor trying to donate. Our handler always returns 202 Accepted to the browser, logging failures internally instead of propagating them. Analytics is fire-and-forget from the product's point of view, it should never be allowed to block or visibly break the actual product.
The frontend tracking utility
A tiny, dependency-free utility handles sending events:
export const trackEvent = (eventName, payload = {}) => {
if (typeof window === "undefined" || !API_URL) return;
const envelope = {
eventName,
payload,
timestamp: new Date().toISOString(),
sessionId: getOrCreateSessionId()
};
fetch(API_URL, {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify(envelope),
keepalive: true
}).catch(() => {});
};
Two things worth explaining:
keepalive: true ensures the request completes even if the user navigates away immediately (e.g., clicking a link that triggers a page transition). Without this, the browser can cancel in-flight requests during navigation, silently dropping events right when they matter most (like the final "Donate" click).
The session ID is generated once per browser tab (stored in sessionStorage, not localStorage) so events from the same visit can be grouped into one journey, without ever identifying who the person actually is. This one field turned out to be one of the most valuable things in the whole pipeline, it's what makes every funnel and drop-off analysis possible later.
The bug that cost us the most debugging time: a missed page view
We instrumented page views using our frontend framework's route-change lifecycle hook. It turns out that hook doesn't fire on the very first page load, only on subsequent client-side navigations. Any visitor who lands directly on a deep page (a shared link, a bookmark, a direct URL to the donation form) and interacts with it without navigating anywhere else first was completely invisible to our page-view tracking.
We only found this by cross-referencing real session data: some sessions had form-selection events but no page views at all. The fix was a second, separate hook that fires once on initial app load, complementing the route-change hook rather than replacing it. Lesson: framework "route change" lifecycle hooks are not the same thing as "page load"; check both explicitly.
Part 2: Storage and Cataloging
Partitioning: the single most important cost lever
Firehose writes into S3 automatically organized by year/month/day/hour (in UTC, always, and it trips people up if your team isn't in UTC). This partition structure is the foundation of everything downstream, because of one core Athena/Presto concept:
A partition key is a column whose value lives in the file's storage path, not inside the file's content. A normal column filter (WHERE eventname = 'x') requires Athena to open and read every file, then discard non-matching rows, the expensive part happens before the filter helps at all. A partition filter (WHERE year = 2026 AND month = 7 AND day = 21) lets Athena skip entire folders it can prove don't match, without opening a single byte inside them. Since Athena bills per data scanned, this is a direct, sometimes dramatic cost and speed lever, not a micro-optimization.
We used partition projection (a Glue Data Catalog feature) instead of manually registering partitions. Traditionally, adding new partitioned data means running MSCK REPAIR TABLE or manually registering each new partition before Athena will look at it. Partition projection instead tells Athena to derive partition values mathematically from a defined range (e.g., "hour is always between 0 and 23"), so new data becomes queryable the instant it lands, with zero maintenance:
{
"projection.enabled": "true",
"projection.year.type": "integer",
"projection.year.range": "2024,2035",
"projection.hour.type": "integer",
"projection.hour.range": "0,23",
"storage.location.template": "s3://bucket/${year}/${month}/${day}/${hour}/"
}
One real gotcha: if you query this kind of table without a partition filter, and your projection covers a huge window (multiple years, all months/days/hours), Athena may need to check tens of thousands of hypothetical partition paths against S3 before it can return anything, turning a query that should take one second into one that takes minutes. We hit this directly: a SELECT * LIMIT 10 with no WHERE clause took nearly three minutes, while the identical query with a date filter took about a second. Partition projection is a phenomenal tool, but it is not a substitute for filtering by partition columns in every query.
A real, subtle JSON parsing bug: case sensitivity
We used the org.openx.data.jsonserde.JsonSerDe library to let Athena parse our raw JSON. This SerDe has a setting, case.insensitive, that defaults to true, meaning it silently lowercases every JSON key while parsing, including keys nested inside sub-objects.
This caused a genuinely confusing bug: a field named optionLabel (camelCase) in our raw JSON came back as null every time we tried to extract it in a query, even though we could see the correctly-cased data sitting in the raw S3 files with our own eyes. The SerDe was silently transforming optionLabel into optionlabel during parsing, and our extraction query was looking for the camelCase version, which no longer existed post-parse.
The fix required setting case. insensitive: false, but that alone broke something else: our top-level fields (eventName, sessionId in the raw JSON) didn't match our Glue table's column names (eventname, sessionid, lowercase by Hive convention), and turning off case-insensitivity meant those top-level fields stopped resolving too. The complete fix needed explicit mapping.* SerDe properties for every field with inconsistent casing:
{
"mapping.eventname": "eventName",
"mapping.sessionid": "sessionId",
"case.insensitive": "false"
}
Lesson: if you're using a JSON SerDe with mixed-case source keys, don't rely on the default behavior; it silently mangles nested fields in ways that are hard to spot, since the query still "succeeds," it just returns null instead of erroring.
Part 3: Querying and the Materialization Pattern
Views vs. tables, and why we needed both
An Athena view is a saved question, re-computed fresh every single time it's queried; no data is stored under the view's name. This is perfect for ad-hoc, cheap, always-current exploration:
CREATE OR REPLACE VIEW clean_events AS
SELECT
eventname,
sessionid,
CAST(from_iso8601_timestamp(event_time) AS TIMESTAMP) AS event_time,
json_extract_scalar(payload, '$.label') AS button_label,
json_extract_scalar(payload, '$.field') AS selection_field,
json_extract_scalar(payload, '$.optionLabel') AS selection_option,
year, month, day, hour
FROM events;
But views have no fixed output location; nothing outside Athena can point at a stable file path and expect to find this data. For that, we needed a genuinely materialized (physically written) table, refreshed on a schedule:
INSERT INTO clean_events_export
SELECT * FROM clean_events
WHERE year = {y} AND month = {m} AND day = {d} AND hour = {h};
A small Lambda, triggered hourly by an EventBridge Scheduler, computes "the previous complete hour" with one query. Because each run only ever targets one specific, never-repeated hour, there's no risk of duplicate data, the schedule itself acts as the deduplication mechanism, with no separate watermark or state tracking needed.
A subtle type mismatch that only shows up in "Direct Lake" style engines
We stored event_time as a plain string in our export table for a long time, since it started life as raw ISO-8601 text. This worked fine for ordinary querying, but broke the moment we tried to use it as a real datetime column in a "Direct Lake"-style BI engine (Microsoft Fabric's zero-copy semantic layer, discussed more below). The error was precise and worth understanding:
Direct Lake-style engines read raw Parquet bytes directly, with no transformation layer in between, whereas a traditional "Import" pipeline can freely reinterpret a column's type during its own copy step. A zero-copy engine cannot, the semantic layer's declared type must physically match what's stored in the file, or it fails outright rather than silently coercing. The fix had to happen upstream, at the query that produces the Parquet file, casting the string into a genuine timestamp before it's ever written:
CAST(from_iso8601_timestamp(event_time) AS TIMESTAMP) AS event_time
Lesson: if you're feeding a zero-copy/Direct-Lake-style BI layer, get your column types right upstream; you can't paper over a physical type mismatch downstream in the BI tool.
Part 4: Getting Data Into a BI Tool
This is the part with the least documentation online, and the most trial and error.
Three ways to connect a cloud data warehouse to a BI tool, and their real tradeoffs
A direct live connector (e.g., Athena's native BI connector): the cleanest conceptually, but in our experience with Microsoft Fabric specifically, this connector required an on-premises data gateway even when used from Fabric's web-based authoring surface, meaning a Windows machine has to live somewhere, permanently. If your team is Mac-only with no Windows infrastructure, this is a hard blocker, not a minor inconvenience.
A "shortcut" (Fabric's term; other platforms have similar zero-copy external-reference features): a live pointer into cloud object storage, with no gateway required, since it uses direct cloud-to-cloud authentication instead of an ODBC-driver-on-a-gateway-machine model. This turned out to be great for ad-hoc exploration.
A scheduled copy job: periodically pulls data from object storage into the BI tool's own storage. It's also gateway-free (cloud-to-cloud), and unlike a live connector, lets the BI layer read from its own optimized native table format afterward, rather than querying external files on every interaction.
We ended up using option 3 for the production dashboard, since it let us land data as the BI tool's native table format (better performance, native type support) while still being entirely gateway-free.
Real bugs we hit setting up the Copy Job
Bug 1: wrong region in the signing header. The authorization header is malformed; the region 'us-east-1' is wrong; expecting 'ap-southeast-2'. This happened because the S3 endpoint URL we configured was the generic global endpoint, not a region-and-bucket-specific one. Fix: use the bucket-specific endpoint URL (https://bucket-name.s3.region.amazonaws.com), which unambiguously encodes the signing region.
Bug 2: SSL certificate trust failure, immediately after fixing bug 1: Could not establish trust relationship for the SSL/TLS secure channel. The remote certificate is invalid. This one turned out to be caused by the fix for bug 1; the connector apparently also appended the bucket name itself, so combining it with an already bucket-specific URL produced a malformed, doubled hostname that didn't match S3's actual certificate. Fix: revert to the generic endpoint at the connection level, and let the bucket name be specified only once, in the copy activity's own path configuration, not baked into the connection URL too.
Bug 3: wrong file format assumed. ErrorCode=DelimitedTextBadDataDetected ... CsvHelper.BadDataException. Athena writes Parquet output files with no file extension by default, just a random ID as the filename. Some connectors infer file format from the extension; with no extension to go on, ours silently defaulted to treating our binary Parquet data as CSV text, producing exactly the kind of garbled parsing error you'd expect from feeding binary data through a text parser. Fix: explicitly set the file format to Parquet in the copy job's configuration; never rely on extension-based auto-detection against Athena-written output.
Bug 4: a genuinely wasteful funnel bug caught by a UX sanity check, not a technical error. After getting real data flowing, our conversion funnel showed more sessions reaching a later stage than an earlier one, logically impossible for a proper funnel, and a giveaway that something was wrong even before we knew the cause. Tracing it down to actual session-level data revealed the true cause: some sessions had donation-form interactions with no corresponding page-view event at all. This was the same "missed initial page load" bug described in Part 1, showing up again downstream, this time as a funnel-integrity problem rather than a raw-data gap. Lesson: a funnel that doesn't monotonically shrink is a data-quality signal, not just an aesthetic issue; trace it back to root cause rather than adjusting the chart to hide it.
The permanent-bucket-name fix
One more practical wrinkle: if your infrastructure-as-code tool auto-generates a globally-unique bucket name (a common pattern, since S3 names must be unique across all AWS accounts everywhere, not just your own), tearing down and redeploying your stack produces a new bucket name every time, breaking any external tool (like a BI connector) that saved a reference to the old one.
The fix: pin the bucket name to something both fixed and guaranteed-unique, by baking your account ID into it:
bucketName: `xyz-clickstream-${accountId}`
Account IDs are permanent and globally unique, so this name survives infinite teardown/redeploy cycles without colliding with another AWS account, and without ever needing the downstream BI connection reconfigured again.
Part 5: The Dashboard Layer
Semantic layer, DAX measures, and a real funnel chart
On top of the synced table, we built a small set of reusable calculated measures (DAX, in Power BI) rather than manually configuring aggregations inside every chart:
Unique Visitors = DISTINCTCOUNT(clean_events[sessionid])
Donate Conversion Rate = DIVIDE([Donate Clicks], [Unique Visitors])
Sessions Reached Form = CALCULATE(
DISTINCTCOUNT(clean_events[sessionid]),
clean_events[eventname] = "page_view",
CONTAINSSTRING(clean_events[page_path], "/donate/")
)
Four such "stage" measures (reached the form â made a selection â clicked the main CTA â picked a payment method) plugged directly into a dedicated funnel chart visual gives a UX team exactly the shrinking-funnel-with-drop-off-percentages view they actually want, computed live from real data instead of guesswork.
A consolidation trick worth knowing
Rather than building one bar chart per form field (8+ separate visuals for donation type, region, payment method, etc.), we found combining them into a single stacked bar chart X-axis: field name, Legend: option chosen, Value: count - gave a UX designer the entire form's preference landscape in one glance, with each bar clickable to drill into that specific field. Far less canvas space, same information density.
Keeping it current automatically
Modern BI platforms increasingly support zero-copy live sync (Fabric's "Direct Lake" automatically), continuously watching the underlying storage layer for changes and updating without any explicit refresh schedule at all. We layered this with a traditional scheduled refresh as a backup safety net (four times a day, offset 15â25 minutes after the upstream copy job's own schedule, giving it time to finish writing first). Belt and suspenders: the automatic detection does the real work, while the scheduled refresh guarantees a worst-case bound on staleness even if the automatic path ever misbehaves.
Part 6: Timezone Discipline Across the Whole Chain
Worth its own section because it's the kind of bug that doesn't throw an error; it just quietly produces wrong-looking numbers that someone eventually notices and can't explain.
- S3/Firehose partitioning: always UTC, not configurable.
- The export Lambda: explicitly written using UTC-based date functions, matching Firehose.
- The scheduler triggering it: a pure interval (every 1 hour), with no wall-clock time at all, so no timezone ambiguity.
- The downstream BI copy job's schedule: this is where confusion crept in; the scheduling surface here allowed an offset (UTC+5) matched to a different reference city than our own team's location, purely coincidentally sharing the same numeric offset. Functionally correct, but worth double-checking explicitly rather than assuming a label matches your actual timezone.
Lesson: audit every single scheduling surface in the pipeline individually for its timezone assumption; don't assume consistency just because the data underneath is uniformly UTC.
Part 7: Testing With Realistic, Synthetic Traffic
Once the pipeline was live, we needed realistic-looking data to validate the dashboard; real usage takes time to accumulate, and manually clicking through a form a dozen times doesn't produce enough volume or variety to sanity-check charts.
We used a headless browser automation tool (Playwright) to drive many independent, randomized simulated visitors against a local dev instance of the actual site, not mocked requests, a real browser running real frontend code:
- Each simulated visitor got its own isolated browser context (own cookies, own
sessionStorage), so distinct session IDs, not one session repeated. - Agents were launched with staggered random start times across a real multi-minute window, so timestamps spread naturally instead of clustering into one instant.
- At every decision point, each agent randomly chose whether to proceed, which option to pick, and whether to complete or abandon the funnel, deliberately unbiased, no hardcoded "always pick X".
- Interactions targeted the literal visible text of real UI options rather than brittle CSS selectors, since visible copy is far more stable than internal class names.
One bug worth mentioning even here: our bot initially had a near-zero success rate clicking the main call-to-action button. The cause: the button's exact text also appeared elsewhere on the page as an unrelated heading, and a plain text-based click locator sometimes matched the decorative heading rather than the actual interactive button. The fix was switching to a role-scoped locator (matching only genuine <button> elements with that accessible name), which eliminated the ambiguity entirely. Lesson: when automating clicks by visible text, scope by element role/type too, duplicate text elsewhere on a real page is more common than you'd expect.
Dashboard
Cost, Honestly
At the traffic volumes this post describes (a nonprofit donation platform, not a high-traffic consumer app), the actual monthly AWS cost for this entire pipeline, API Gateway, Lambda, Firehose, S3, Glue, Athena, came out to well under a few dollars a month, even accounting for the services whose free tier is a permanent "always free" tier regardless of account age (one of a small handful of AWS services with that property, distinct from the more commonly known 12-months-only free tier).
The main cost lever, by far, is Athena's per-query, per-terabyte-scanned pricing, and partition filtering (Part 2) is what keeps that number small. Skip partition filtering at real scale, and this is exactly where a bill quietly grows.
What We'd Tell a Team Building This From Scratch
- Verify your identity provider's anonymous-access posture before designing anything that assumes browsers can hold direct cloud credentials. It's a common, easy-to-miss blocker.
- A proxy Lambda in front of your ingestion service isn't just a workaround; it's arguably the better design anyway, since it gives you a validation and rate-limiting choke point you wouldn't otherwise have.
- Partition your data by time from day one, and always filter by it. The cost and performance difference is anything but subtle.
- You don't need a full ETL pipeline just to aggregate data. A query engine that can aggregate on demand (Athena, and equivalents on other clouds) removes the need for a separate transform step; build the heavier pipeline only when you hit a genuine, specific wall (BI tool connector limitations, or real performance ceilings), not preemptively.
- Case sensitivity in JSON parsing libraries is a silent, not a loud, failure mode. Test with real mixed-case field names early.
- Zero-copy/Direct-Lake-style BI engines require real, physically correct column types; you cannot fix a type mismatch downstream in the BI tool alone.
- A funnel that doesn't monotonically shrink is a bug, not a chart formatting issue. Trace it to root cause.
- Give any auto-generated, globally-unique resource name (like an S3 bucket) a stable, fixed alias if anything external depends on referencing it; account ID suffixes are a simple, reliable way to do this.
- Synthetic test traffic is worth automating properly. But automate the clicking robustly (scope by role, not just text), or you'll draw the wrong conclusions from your own test data.
This pipeline is fully serverless, requires no dedicated infrastructure team to operate, and as shown above, costs a genuinely small amount of money at moderate scale. If your team is facing a similar "we have no idea how people use our product" problem, this stack (or an equivalent on whichever cloud you're on) is a reasonable, low-commitment way to get real answers.
Top comments (0)