Struggling with real-time marketing attribution in custom dashboards?

Hey everyone,

We're running a B2B SaaS platform, and while our core metrics dashboards are solid, we're hitting a wall with advanced custom reporting, specifically around multi-touch marketing attribution. Our current setup gives us good top-level insights, but drilling down into the true ROI of specific channels and campaigns is proving incredibly complex.

• Current Stack: We pull data from various sources including Google Analytics 4, Salesforce, HubSpot, and our own internal PostgreSQL database (for product usage). This data is currently aggregated and visualized via a custom-built dashboard using React for the frontend and Python/FastAPI for the backend, querying a data warehouse (initially Redshift, now considering BigQuery).
• The Core Problem: We need to move beyond last-click attribution and implement more sophisticated models (e.g., linear, time decay, position-based, or even custom algorithmic models) directly within our custom dashboard. The challenge lies in accurately stitching together user journeys across disparate platforms, handling session vs. user-level data, and ensuring data freshness for near real-time insights. The data volume is growing, making efficient querying and model re-calculation critical for effective marketing attribution.
• What We've Tried:
  • Custom SQL Views: We've built complex SQL views in Redshift to attempt to model marketing attribution, but these quickly become unwieldy, slow, and hard to maintain as our models evolve. They also struggle with historical data recalculations without significant ETL overhead.
  • External BI Tools (Tableau/Looker): While these offer some attribution capabilities, they often abstract away the underlying logic too much, making it difficult to implement truly custom, proprietary attribution models tailored to our specific customer journey and product lifecycle. Integrating them seamlessly into our existing custom dashboard for a unified user experience is also a hurdle.
  • Segment.io/Rudderstack: We use these for event collection, which helps with data unification, but the actual attribution logic still needs to be built and managed downstream.
• Specific Questions:
  • What architectural patterns (e.g., data lakehouse, streaming ETL with Kafka/Kinesis, specific data vault methodologies) have you found most effective for handling complex, real-time marketing attribution modeling at scale?
  • Are there specific open-source libraries or frameworks (Python/Java/Go) that are robust for building custom attribution models that can ingest data from various sources and run calculations efficiently?
  • For those using BigQuery or Snowflake for this purpose, what are your best practices for schema design and query optimization when dealing with multi-touch attribution data?
  • How do you manage the trade-off between data freshness and computational cost when recalculating complex attribution models, especially for historical data?

Thanks in advance for any insights or shared experiences!