Combining Disparate Data Sources on a Card

pstrauss
pstrauss Member
edited January 2023 in Charting

I know it's not possible to create a card that pulls in data from multiple databases unless you combine them with a dataflow / ETL. But I have a scenario where that approach doesn't make sense as the data is VERY different in structure.

Basically, one database has detailed individual end-user records which I'm totaling with COUNT (DISTINCT()) and the other database is totaled at the Client level, which is hierarchically one level above End-User in our world.

See below for what I'm trying to do:

Desired Report (grouped by Client)

Number of Calls| Number of Chats | Number of Webinar Attendees | Total

Client 1 5 10 5 20

Client 2 3 8 3 14

Client 3 1 0 11 12

-Number of Calls and Number of Chats come from one database tracked at the individual interaction level and there is a column with the Client name and User ID

-Number of Webinar Attendees comes from a Google Sheet which lists the TOTAL number of attendees by date, along with a Client name (which matches the Client Name in the other database)

While it is certainly possible to join the Webinar database to the Call/Chat database on the Client Name field, that causes every existing row to store the TOTAL of webinar attendees for the Client since we don't have it broken down to the individual User level. This approach creates some very confusing data, plus there's not really an easy way that I can think of to count up the total of webinar attendees only once per Client.

Any ideas would be welcome.

Tagged: