For the last two quarters, I’ve been on a mission to solve a very specific and frankly exhausting pain point for our marketing and sales ops teams: the weekly sales performance report. You know the drill—every Monday morning, someone (usually me) was manually pulling data from our CRM (we use Salesforce), stitching it together with campaign attribution data from our marketing automation platform (Marketo), and then trying to format it all into something presentable in Google Slides. It was error-prone, took half a day, and was deeply unsatisfying.
I finally decided to architect a proper solution using **Flux** as the central orchestration and data transformation layer, with **Looker** serving as the final visualization and delivery endpoint. The goal was a fully automated pipeline that runs every Sunday night, preparing a fresh, interactive report for the leadership team by 6 AM Monday. The transformation Flux handles is the real magic, and I wanted to share a detailed breakdown of the stack and the workflow.
Here’s the core architecture of the solution:
* **Data Sources:** Salesforce (Opportunity, Lead, Contact objects), Marketo (Program Member, Activity logs), and a PostgreSQL database holding our product usage events.
* **Orchestration & Transformation Layer:** **Flux**. This is where the heavy lifting happens. Flux connects to the APIs and databases, performs the joins, and applies the business logic.
* **Data Warehouse:** BigQuery. Flux loads the transformed and cleaned datasets here as scheduled tables.
* **BI & Visualization:** **Looker**. It connects directly to the BigQuery tables Flux prepares. We built a dedicated dashboard that uses these tables as its primary source.
* **Delivery:** Looker’s scheduled email feature sends the dashboard as a PDF to a distribution list. We also have a Slack alert from Flux confirming the pipeline run succeeded.
The most critical piece is the Flux configuration that creates the unified dataset. It’s not just a simple data dump; it’s where we calculate key metrics like lead-to-opportunity conversion rate by campaign, weighted pipeline, and marketing-sourced revenue—all according to our specific attribution rules (first-touch for leads, multi-touch for opportunities). Flux handles the messy joins between Salesforce Campaign IDs and Marketo Program IDs, which was previously a manual VLOOKup nightmare.
The result? The Monday morning report is now a zero-touch artifact. More importantly, because the data flows through Flux into a structured BigQuery table, the Looker dashboard is now interactive. Leadership can drill down into specific time periods, campaigns, or sales reps without requesting a new manual report. The pipeline runs reliably, and we’ve repurposed those half-days into more valuable analysis work.
For anyone considering a similar project, the key insight was using Flux not just as a scheduler, but as the *logic engine* for our most important derived metrics. This ensures that our single source of truth for these calculations is defined in code (the Flux config) and not in a fragile spreadsheet or a labyrinth of Looker-derived tables. It has made the entire data chain more maintainable and transparent. I'm happy to dive deeper into any specific part of this setup if there's interest.
null
Oh man, I've been there with the Monday morning scramble. That "half a day of manual work" is such a productivity killer. Your architecture using Flux as the orchestration glue makes perfect sense, especially for that Marketo-to-Salesforce data stitching, which can get messy fast.
I'm curious about the transformation piece. Did you run into any major data type mismatches or timestamp alignment headaches when joining those streams in Flux? I've had to write some gnarly time-windowed joins for similar pipelines.
Dashboards or it didn't happen.
You're spot on about those joins. The timestamp alignment was the first major hurdle, especially since Marketo's activity timestamps and Salesforce's opportunity close dates live in different granularities. We ended up creating a normalized "reporting week" dimension in Flux to bridge that gap before the join.
A less obvious issue was handling null campaign IDs in the Marketo feed, which would silently break the attribution link. Our solution was a two-stage transformation: first, a simple filter to remove truly orphaned records, then a default "Direct / Unknown" campaign assignment for any remaining gaps. It added a bit of complexity but made the final Looker dataset much more reliable.
Has your team settled on a standard windowing strategy for these kinds of pipelines, or does it vary case by case?
null
The "Direct / Unknown" fallback is such a good pattern. We do something similar for unassigned leads in our sales funnel, but we push it even earlier. We actually wrote a small Lambda that sanitizes the raw Marketo webhook payloads before they ever hit the S3 bucket Flux reads from. It adds a default attribution tag there, which keeps the Flux script cleaner.
On windowing, it's pretty case-by-case for us, but we've started leaning into **event time windows with a fixed late-arrival allowance** for most reporting pipelines. Our sales data pipeline uses a 36-hour window to account for Salesforce batch updates from integrations. The Flux looks roughly like this:
```javascript
|> window(every: 1w, period: 1w, offset: 0h, start: 2023-01-01T00:00:00Z)
|> aggregateWindow(every: 1w, fn: sum, timeSrc: "_stop", createEmpty: false)
```
The `timeSrc: "_stop"` bit is crucial for our use case - it aligns everything to the window's end boundary, not the erratic event timestamps. It made our weekly Looker dashboards finally stop jumping around. Does that late-arrival period feel too generous, or are you dealing with similar delays?
YAML is not a programming language, but I treat it like one.
Pushing sanitization to a Lambda is just adding another moving part and another cost line. Now you've got to monitor and maintain that too.
A 36-hour late arrival window feels like you're papering over a data freshness problem, not solving it. If your Salesforce batch updates are that slow, fix the batch job.
Also, you're building this whole pipeline to feed Looker. Why not just use Looker's native ETL or a simple dbt model? You've built a complex real-time pipeline for a weekly report.
Simplicity is the ultimate sophistication
Pushing sanitization earlier is usually the right call. The Lambda is indeed another piece to manage, but it centralizes the logic and keeps the transformation code pure. It's a trade-off, but calling it just "another cost line" ignores the maintenance cost of ugly, defensive Flux scripts.
Your point on the weekly report is valid. People consistently over-engineer for perceived future needs. But native Looker ETL can't handle the time-windowed joins they described. dbt could, but then you're scheduling it. This pipeline likely runs on event triggers, which is why it exists.
Fixing the batch job is the ideal, but sales ops teams rarely own those integrations. You build the late window because you can't change the source system's SLA. That's the reality for most of us.
Ask me about the cancellation process.
Oh, I feel this post so much, that Monday morning scramble is a special kind of dread. I love that you're using Flux as the transformation layer - I've had great results with it for exactly this kind of multi-source stitching.
I'm really curious about the Postgres part of your stack. Is that acting as your final data warehouse before Looker, or more of a staging area? In my last migration project, I tried piping similar cleaned data directly into Looker's PDTs but hit some performance walls with complex joins. We eventually landed on using a dedicated PostgreSQL instance as the "reporting mart," which Flux writes to, and then Looker points there. It added a step, but the query speed improvement was massive for the end users.
Also, what's your strategy for handling historical data corrections? Like, when sales ops goes back and updates a Salesforce opportunity status two weeks later? That one always trips me up. Do you have your Flux job doing a full refresh of the window, or are you using some kind of upsert logic?
Backup first.
That's exactly why we used Postgres as the final reporting mart too. PDTs are fine for simple aggregations, but they fall apart with the multi-way joins you need for attribution. The performance hit isn't worth it for a team waiting on a Monday report.
For historical corrections, we run a full backfill of the affected reporting window. Our Flux job is idempotent; it truncates and repopulates the entire week's data in Postgres for any week where we get a late-arriving or updated record. It's brute force, but it keeps the logic simple. We tried upsert logic, but tracking the state of what changed across the joined datasets became a nightmare.
The trade-off is compute cost and time, but since it's a weekly batch for a limited window, it finishes fast enough. If you had to do this daily, you'd need a smarter incremental approach.
Automate everything. Twice.