Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications
Hi, I struggled to find an easy explanation on how I can easily display my own shapes on a map and then match data with it and display them on a looker viz. So to remember how I did it, I amgoing to document it here. This is detailing the 2 posts from here and here to create the shape and here about how to put it in looker. I am just putting them together to explain more in details.First thing is to create the shape on the map. For this, I used this tool http://geojson.io/ just draw the shapes or dots you need on a map: then the important step is to attach a unique code to each shape so that you can link each shape to a value in Looker’s dimensions. For this, just click on your shape and then add the name of dimension you will be using and then the value that is the one you would get in your database: if I click on another shape, I will also put “id” but with a different value: once you have created all your shapes, jsut download them as topojson:next, you upload the file in Looker:y
Very often, I work on subscription data, with a typical contract table with a start date, an end date, an amount and a contract length. How can you use this to understand your monthly recurring revenue, churn rate, and all these typical metrics typical of the ever increasing subscription world?I used this article as a base, but expanding a bit and going into a bit more detailsto make this work, we need to have a table where, on for every month, I need a row for every contract id from our contract table, the amount paid for this month, the amount paid the previous month and the amount paid the next month. first step, you need to generate a date series table, as explained here: view: calendar { derived_table: { sql: SELECT date as calendar FROM UNNEST( GENERATE_date_ARRAY(DATE('2018-01-01'), DATE('2023-01-01'), INTERVAL 1 DAY) ) AS date group by calendar ;; } dimension_group: calendar { type: time timeframes: [date,month_num,quarter,quarter_of_year,year,ra
When I am working on cRM data, I always use the crm block form keboola with a bit of the salesforce block. I was working on hubspot data but there is no snapshot data with the fivetran connector, only this deal_stage table which basically records the timestamp when the deal stage changes. In order to make the historical_snapshot view from keboola to work, I need to refactor this table so that I can use the great dashboard from the block, which I did using the following derived table (bigquery): view: deal_stage_refactored { derived_table: { sql: WITH dates AS (SELECT day FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2020-06-01'), CURRENT_DATE(), INTERVAL 1 DAY)) AS day ) , deal_stage_refactored AS (SELECT deal_id,date_entered,value, case when lead(date_entered) OVER (partition by deal_id ORDER BY date_entered ASC) is null then date_add(date_entered, INTERVAL 1 DAY) else lead(date_entered) OVER (partition by deal_id ORDER BY date_entered ASC)
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
