Combining data from Google Analytics and MySQL

Hi all,

I'm currently evaluating Metabase as a possible replacement for Chartio. One issue that I ran into today is that I don't seem to be able to add a funnel to a dashboard that's based on data from Google Analytics (e.g., new users) combined with data from our own MySQL database (e.g., user registrations and user purchases).

Accessing the GA data by itself works alright and I can also build a 2-step funnel from the MySQL data but I haven't found a possibility to combine the two. In the SQL editor I seem to be forced to choose which data source I want to access, so I cannot combine data from GA and MySQL and combining two saved questions (both in the , format) also doesn't seem to work (the GA based one doesn't even show up in the "add series" dialog for the MySQL visualization and when trying to add the MySQL one to the GA based visualization it just says that the "that question isn't compatible").

Any advice or hints would be much appreciated. Thanks in advance.

Hi @ChristophD

You would have to add your different database to the same data warehouse.

It is only possible to combine via the overlaying data visually:

It's one of the most requested features - and also the most difficult to implement: - upvote by clicking :+1: on the first post

@flamber, thanks for the quick reply. So you're saying that even for the visual overlay option the data needs to come from the same source?

Because essentially all I'd need is to be able to combine three values for a given timeframe:

Google Analytics: new users
MySQL: registrations
MySQL: purchases

So for this usecase I wouldn't even have to join across the different tables or sources (as is mentioned in the issue you linked to).

@ChristophD No, I'm saying just the opposite. You can combine any databases with the overlay, like I linked to. But the format of each question has to be similar (the same column structure and types), otherwise they cannot overlay.

@flamber, okay, thanks. Then I'll need to play around more with my queries to align the data formats better. :slight_smile:

@flamber, I played around with this some more and still cannot get a Google Analytics question to serve as an overlay to a MySQL question. :confused:

The Google Analytics results table consists of two columns:

Date: Day, Users (e.g., December 12, 2021: 987)

The MySQL results table also consists of two columns:

date_format(subscription_start,'%M %d, %Y'), count(*) (e.g., December 12, 2021: 123)

I can link the MySQL results to a date filter on the dashboard so Metabase seems to see the first column as a date type, yet it refuses to allow me to do an overlay with the GA data. Linking to MySQL results with the same data types works though.

Any ideas as to what I might be doing wrong here?

@ChristophD It's very important to understand the difference between column types and displayed formatting.
Metabase applies displayed formatting on e.g. date column, so they look like "December 12, 2021", but underneath it's a database column date DATE, which only has the format 2021-12-12.

So you need to return data with the same types - not trying to play with formatting.

Hence MySQL should be select date(subscription_start), count(*) from some_table

1 Like

@flamber, thanks so much, that did the trick. I was under the assumption that date_format also returns a date type object type since Metabase does see it as compatible to a date filter.

@flamber, one more question: The multi series charting doc is focused on combining line and bar charts from different questions. Is it also possible to do that for funnels? In the dashboard's edit overlay I'm not seeing a possibility to add edit the series of a funnel and haven't found anything in the documentation or forums.
As mentioned in the original post, my ultimate goal is to build a funnel visualization that used data from Google Analytics and MySQL.

@ChristophD Currently it's only possible to combine Line+Bar+Area (aka Combo) charts. We have been looking into trying to implement other visualizations, but it quickly becomes complicated and is only possible to do on dashboards, which has limited editing options. It's complicated to say at least.

Generally most are combining their data in a warehouse, which then also allows Metabase to join across the databases. That gives you most options, but is not two-clicks-and-you're-done.

Okay, makes sense.

@philippevotresucces Yeah, that's what we'll end up doing. However, it's another integration and potentially another 3rd party service that we have to use and maintain and I was really hoping that the standard GA integration in Metabase would be able to take care of our (very limited) requirements for GA data...