We've been thinking a lot about this exact issue over the last few years and have come to the same conclusion: when dealing with a platform that provides a ton of value out of the box, you need to be somewhat opinionated, and you can't expect to cover every use case. Different customers can have very different questions about their data and unique insights that they want to discover.
To address that, we offer direct SQL access to the raw data in Amazon Redshift. While Redshift isn't the best solution for powering high-performance, realtime dashboards, its main benefit is that it supports most of the SQL standard, which means that you can answer close to any question about the data. This duality of the fast, easy-to-use dashboards that provide immediate value and the flexibility of Amazon Redshift for deeper, customized queries has helped a lot of our customers overcome the problems you describe.
Hope this makes sense, and we'd love to hear your feedback!
Edit: Also just saw you guys just recently raised a round, congrats!
Jeffrey's the VP of Engineering at Amplitude who built the core of our current infrastructure.
If you ever get to that point, you can use raw Redshift access to do whatever queries you want. I used to use Zynga's pretty state of the art analytics infrastructure, and Amplitude dashboard was the only thing that covered most of my edge cases. For the rest you can always go raw SQL.
I guess what I'm trying to say is that, while services like these are great, I find that eventually after a certain size startups will almost always need to have in-house analytics infrastructure to cover all use cases and needs specific to the business. Not that services like Amplitude don't have their place, which they do, I just wish there was an universal solution to that problem. But I suppose you can't help rolling your own infrastructure given specific enough needs.