How to connect Monday.com to Looker Studio using Fivetran & BigQuery?
Ever wondered how to report your Monday.com data into Looker Studio? Search no more, we’ve got the solution (it has to do with BigQuery!)
Recently, a new lead (that became a client :) ) came to us asking: how on earth can I make reports based on my Monday.com data (the ones they provide… mweh)? Easy answer: not that easy! There are only a few apps in their store offering replication of boards to Google Sheets; pricey and sometimes innacurate as they don’t manage that well subitems. There are also a couple of direct connectors from Monday.com to Looker Studio, but again, doesn’t seem they’re doing the job as they can’t manage historical changes, they only offer live data (and same issue with subitems, not great). So, in this article, we’ll show you how to connect Monday.com to Looker Studio using Fivetran & Bigquery (spoiler: you’ll need some serious SQL skills. Or, you can skip the reading and contact us to see how we can best help you ^^).
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
Set-up a Google Cloud project, a Fivetran account and start replicating your raw data
First thing first: you’ll need a BigQuery dataset to store data. To do so, proceed to https://cloud.google.com/, create a new project and enable BigQuery. Then, go to https://fivetran.com and open an account (you’ve got a free trial period).
Follow the indication to set a destination to Bigquery; follow the indication to set a Monday.com connector (make sure you replicate your data once every 24 hours), and you should start seeing a dataset getting populated with your raw data.
Good new, Fivetran offers a free plan under a certain usage, so this step might not cost you anything!
Now, write some SQL queries to retrieve your data
At this stage, you should see a set of tables in your recently created dataset that stores all of your Monday.com information:
They are organized in a way that potentially, you’ll only need 3 tables:
board: stores your boards
items: stores all your items and subitems. An item is linked to a board by its board_id. A subitem is linked to its parent item by its parent_item_id
column_value: stores all your items info (aka the columns, that’s right captain obvious). They are linked by the item_id
And that’s pretty much all you need to know to start playing with your data. Please find below a SQL script example we wrote to get all the most important fields from all the items and subitems of a given board (we first scanned the board table to identify the id we were looking for). Steal this code, play with it and make some magic:
WITH
leads_from_leads_board AS
(
SELECT
i.id AS item_id
, i.name AS item_name
FROM
`XXX.monday.item` i
WHERE
i.board_id='123456789'
AND
i.state='active'
AND
i._fivetran_deleted IS FALSE
)
,
leads_not_won AS
(
SELECT
l.*
, CAST(d.date AS DATE) AS date_first_contact
, n.text AS nature
, c.text AS channel
FROM
leads_from_leads_board l
INNER JOIN
`XXX.monday.column_value` s ON l.item_id=s.item_id AND s.title='Status' AND s.label<>'Won' AND s._fivetran_deleted IS FALSE
LEFT JOIN
`XXX.monday.column_value` d ON l.item_id=d.item_id AND d.title='Date 1st Contact' AND d._fivetran_deleted IS FALSE
LEFT JOIN
`XXX.monday.column_value` n ON l.item_id=n.item_id AND n.title='Nature' AND n._fivetran_deleted IS FALSE
LEFT JOIN
`XXX.monday.column_value` c ON l.item_id=c.item_id AND c.title='Channel' AND c._fivetran_deleted IS FALSE
)
,
subitems_table AS
(
SELECT
lnw.*
, i.id As product_id
, p.display_value AS product
, s.number AS size
, pr.number AS price
FROM
leads_not_won lnw
INNER JOIN
`XXX.monday.item` i ON lnw.item_id=i.parent_item_id AND i._fivetran_deleted IS FALSE
LEFT JOIN
`XXX.monday.column_value` p ON i.id=p.item_id AND p.title='Product' AND p._fivetran_deleted IS FALSE
LEFT JOIN
`XXX.monday.column_value` s ON i.id=s.item_id AND s.title='Size' AND s._fivetran_deleted IS FALSE
LEFT JOIN
`XXX.monday.column_value` pr ON i.id=pr.item_id AND pr.title='Price' AND pr._fivetran_deleted IS FALSE
)
SELECT
*
FROM
subitems_table
A couple of things here:
You’ll notice we’ve extensively used the title column from column_value, but in all fairness we should have used the id column to make sure we’re looking at the right info (in our case, we made the dev so we know the structure is right, and we prefer to use the title that is self explanatory rather than a date__3 id, that doesn’t tell you much)
We are on a “simple” structure, but you may want to join your column_value to your items AND to your board_id in case there is a risk of confusion
From there, you should be able to get ANYTHING from your Monday.com boards, and then send them to Looker Studio for proper datavizs and analysis. You’re very welcome :)
Use scheduled query to create an historical table
Remember, we’re still working with live data here. By default, Fivetran will replicate your data once a day. A good way of creating an historical table would be to create a scheduled query that also runs once a day to store daily status of your items, and then you can start identifying status change dates, values changes over time, … anything you need to report on.
Again, this will require some strong SQL knowledge, so if you’re not sure what you’re doing here, best thing would be to contact professionals, but there are nice things that can be built to make your life easier.
Thanks for reading that far, and we hope you found this helpful.
PROBLEM SOLVED
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):