Creating charts by querying more than one table at a time

Hi Guys,

Really enjoying Metabase so far. Love the simplicity of it.
We’re using an open source CRM system and we want to determine our current and historic conversion rate. The way that would be done is to query the “leads” table and the “opportunities” table as new enquiries are recorded as leads and sales closing information is recorded in the Opportunites table (as “sales won”).

However, while it’s really easy to slice and splice the data on one table at a time – So I can, for example, see how many leads we’re getting on a month to month basis, or separately how many opportunites there are – I am unable to compares leads vs opportunities.

Anyone know a way that I might be able to do this?

Here’s a link to the DB Schema for reference:

1 Like

At the moment, we don’t support comparisons between two time series. We’ve optimized on ease of use for a single time series.

It’s pretty easy to drop down into SQL to do something like this, especially as the leads table has an FK to opportunities. So you get access to the opportunity fields in the query builder centered on leads.

Also, I’ve set up a demo instance of sugar.

If you give me concrete comparisons, I can play around with the schema and either figure out a way to get it out of the current system or spec out new features.

That’s true except most of us working on this are non-tech folk. Im just consulting with the company and they need some answers on sales data. I’ve been training them on using Metabase in-house. I think getting them to drop down into SQL is probably going to be a bit of a far stretch unfortunately, although I totally get where you’re coming from on this.

When you say “concrete comparisons” , what do you mean exactly?

We need to work out:

How many leads are converting into opportunities on a month by month basis both as a number and as a percentage.
We also, need to work out how many leads are converting into won opportunities.

Does that help?

Things like that. Looking to get a list of important questions/queries we can use as benchmarks for how powerful our query interface is.

You can do conversions by filtering the leads on “leads.opportunity_id is NOT NULL”, though we don’t do percentages out of the box yet. For won opportunities you can add an additional filter based on the status flag of opportunities.

I did find that the demo DB didn’t set FK constraints properly and I had to manually add FK definitions in the metadata. Not entirely sure whether that was just an error in demo data generation or if production deployments of Sugar have the same problem.