I am trying to figure this out have not been able to find it out easily yet.

Using LDS with a Google Sheet as the data source.

Sheet 1 has a list of employees and a bunch of metadata
Sheet 2 has a list of employees and their manager

I'd like to be able to use the drop down control so the department head can come and filter by manager to see the meta data for all the employees under that manager.

The manager information is not on Sheet 1

In the past I have done this by adding a column to Sheet 1 and doing a vlookup to Sheet 2 but this is extra work every time we get the updated data.

I am sure this is pretty easy, just haven't nailed it yet.

Thx

My girlfriend and I are moving to Singapore soon. In my research I found a government website with all actual lease price data. I found that the listings on popular apartment rental websites often varied from the actual prices people paid.

So I downloaded 95,000 real leases from the database and built this dashboard.

Though you might find it useful if you're looking for an apartment in Singapore

https://lookerstudio.google.com/reporting/b3e74ba3-88ec-4a44-b17b-0dd886b090cc

https://preview.redd.it/pcolf7t63lzc1.png?width=1220&format=png&auto=webp&s=30e7b01d7e5b9d94a1bbfc2fa37707dc74b54e15

I have an advanced filter control on a table with bigquery as the source and want to use regex

.(foo). doesn’t work, but it does work if I filter the table using a chart filter Regexp Contains rather than advanced filter control.

Can anybody help?

i really need help on this. I tried all the formulas i could i couldn't find a solution.

I need to use looker studio to convert values like this one: 1699562040000 to a date format. I already converted it using https://www.epochconverter.com/ and it converted to Thursday, 9 November 2023 20:34:00 and this date is correct.

Those values comes from a hubspot report, and i'd like to use looker studio to convert it to a date.

Since timestamp formula doesn't work anymore, i couldn't find a solution for it using calculated fields?

Thank you!

I tried Several formulas using timestamp, cast, parse-datetime like: PARSE_DATETIME("%s", CAST(your_field_name AS STRING)), // Assuming your field name is 'ms_since_epoch' FORMAT_DATETIME( DIVIDE(ms_since_epoch, 1000), // Divide by 1000 for seconds "%Y-%m-%d %H:%M:%S", // Adjust format string for desired output "GMT" // Specify desired time zone ), TIMESTAMP_SECONDS(CAST(your_field AS FLOAT64) / 1000) , just can't think of anything else.

I am trying to take a Google Form response sheet with likert scales and make a stacked bar chart that lists each question with a 100% stacked bar based on responses similar to image (I have tried converting the numerical responses to text and it did not work). However, I cannot figure out how to make it work.

What I want

https://preview.redd.it/cqhi6myh1gzc1.png?width=1410&format=png&auto=webp&s=11db88614e3a8ae02c835ce8ee6f4b05bf15c8b8

What I can get

https://preview.redd.it/s6edibtn1gzc1.png?width=1591&format=png&auto=webp&s=d1d2a470abe99eeec30a5886f7c6eccf70eb13ca

Here is data sheet

Hi,

I have encountered an issue with null values.

When building a table, I use nary_max to get it cleaned up and when summary row looks ok. Even if I add some multiplier due to taxes it still works great.

Total Cost = nary_max (cost, 0)*tax and it is okay.

But, if I want to divide (nary_max (cost, 0)*tax) / nary_max something, 0) I get very random numbers (way to high). Manually, value should be 50, and I get 600 / 700.

Is there a way to do this properly when there are nulls present?

I have a text string that goes Site/Building/Floor/Room. How can I split out each part into a separate field. Note that sometimes there is only site/building or site/building/floor.

Thanks in advance.

Hello,

probably a very basic question, but I've been stuck for 3 days now...

A community connector developed by me returns entries with these fields:

https://preview.redd.it/253fahj4y5zc1.png?width=203&format=png&auto=webp&s=6c6957188208149f553742cb5a13f7c0a6d2e3d9

where members always has the value 1.

I can, for example, display the data in a table (grouped by joined), and count/sum members per date.

https://preview.redd.it/m50zpiqgy5zc1.png?width=769&format=png&auto=webp&s=0fdc447995bb74037ff30a56e8e00cd4096c2a70

However, I can't display the total number of members (either count all rows received from the connector or sum the counted members that were grouped by joined, as displayed in the table above).

Example 1: scorecard with count all members without date range dimension (with date range dimension, it would give the same error)

https://preview.redd.it/expbkaquz5zc1.png?width=713&format=png&auto=webp&s=618d4e1c51748718fa4a033f18db3a8da02244d4

Example 2: using a calculated field, as SUM(members). This one is basically copied from an official LookerStudio tutorial on calculated fields.

https://preview.redd.it/ousahpye06zc1.png?width=871&format=png&auto=webp&s=d9e451ed266211d83e0991cafec94b504d20a75f

The message for both errors is:

The number of columns received in the data returned from the community connector doesn't match...

That's strange, because I can still display the data in a table or chart (as shown initially).... Do you know what I'm doing wrong?

Edit: example of line chart that works using my connector.

https://preview.redd.it/4ecqflord8zc1.png?width=1474&format=png&auto=webp&s=4840ba70636c7c8f9337eca331457cce78b57c93

I am newish to Looker Studio and I have created a simple dashboard for a few of my clients. I pull information from our shopping cart using GA4 integration and report the results in Looker Studio but the numbers can not be accurate.

Everything we sell is in increments of $5...so $5, 10, 15, 20 etc. but the report shows revenue aggregations of $1011, $3013 which is impossible if every sale was an increment of $15. Any thoughts on how this is happening or how I can fix?

I looked for an answer to this in previous posts but did not see it.

My goal: Monthly report that is automatically emailed on the first of the month showing total number of followers for Facebook as of the end of the previous month.

The metric I am using is Lifetime Total Follows - Lifetime.

Ideas on how to set this up? Is there a way to configure the date control to do this? Should I revisit the cumulative metric of Lifetime total followers?

TIA

Getting familiar with Looker Studio for a new job and had a quick question that I can't seem to figure out! We currently have all of our data in some inherited Google Sheets. They are set up mostly well, with headers and data in rows/columns. However, there are totals and other random information listed underneath, and this is inflating my values when I try to make Scorecards for columns.

My question: Is it possible to select only a specific range of cells from my Google Sheet to be included in the scorecard (ex A2:A12, excluding A13)?

During April all traffic that was reported in my looker studio dropped with over 90% even in my GA4 account everything looks like it dropped 25-98% depending on region.

The only thing that our agency has been doing is concent v2 and we went from 400 daily users to 7 in one region..

Please help!

I tried to write a 'combined field' to combine Facebook sources in a GA4 report, but it does not appear to be working. Any suggestions?

CASE WHEN Hostname IN ('facebook.com', 'm.facebook.com', 'mobile.facebook.com', 'l.facebook.com', 'lm.facebook.com', 'web.facebook.com', 'touch.facebook.com')

THEN 'facebook.com'

ELSE Hostname

END

Hey everyone,

I'm trying to calculate the number of unique logged in users that checked a promotional page.

To do this, I first created a new custom field called "Unique Logged in Players":

https://preview.redd.it/gp6x9eixutyc1.png?width=944&format=png&auto=webp&s=de0aef5fe8e16c64c082d1046f6a2f71f0ca3141

Then I created a filter that filters for the event_name = page_view and page_location = promo_page.

https://preview.redd.it/wttldh7dvtyc1.png?width=1127&format=png&auto=webp&s=86de0b15047ae646fa8b9b53185b77be29bfa825

I have done similair stuff in the past but only filtering for event_name, but adding the page_location seems to break the report as the numbers highly depend on the date selected, as shown below:

https://preview.redd.it/wenh0nyjvtyc1.png?width=536&format=png&auto=webp&s=a4eae7fbaeee4c7de985f1ee72d482a61822d168

https://preview.redd.it/nh4tu94nvtyc1.png?width=522&format=png&auto=webp&s=75214c4ec5d61a1ffe2aa65fc0723bb85046d545

The longer the range the lowest the values for each day. Anybody knows why this is happening? Clearly I'm missing something.

Thank you in advance.

Hey!

So I'm having a lot of issues with setting up my dashboard. I'd like to combine a set of different GA properties together into a set of tables. To make the example simple, the core chart I want is just views by site name (I'm using Stream name to act as this for now).

When I create merges, which I'm merging by Date at the moment (I'm sure there's a better pick), the issue seems to be that it creates separate dimension/metrics for each GA property.

Here is what I want:

Stream Name | Views
{Site name} | {number of views} 
{Site name} | {number of views}

Here is what I'm getting:

Stream Name (Table 1) | Stream Name (Table 2) | Views (Table 1) | Views (Table 2)
{Site name} | {Site name} | {number of views} | {number of views}

Any idea on how to set this up for what I'm looking for?

Hey everyone,

I wanted to share my past experience interviewing for a Data Analyst role at Google. Here's how it all went down:

1. Recruiter Call: It started like any other recruiter chat—role details, level discussions, the usual drill. The only hiccup? The reluctance to dive into compensation and leveling details, which left me a tad frustrated. (Lasted 30 mins)
2. Hiring Manager Round: This part was all about flexing those problem-solving muscles. We kicked off with general problem-solving scenarios, which I found quite engaging. Then came the twist—Google-specific products like Chrome. They threw me curveballs, asking how can you measure spam or unfair use of Chrome. It was a weird problem statement to be honest. But nonetheless I answered whatever I could. (Lasted around 50 mins)
3. Technical Round: SQL enthusiasts, listen up! This round was SQL only. Started easy, moved swiftly into Window Functions territory. Honestly, I was expecting it to be much harder but I guess I was well prepared for it. I had questions involving self joins, running sum and percentiles. (Lasted around 60 mins)
4. Googlyness Round (Bar Raiser): This was the vibe check round. They delved deep into my past experiences, asking about handling disagreements with managers, dealing with underperforming team members, all that jazz. (Lasted around 45 mins)

After maneuvering through these rounds, I got the final call in just a week—a mix of excitement and nerves because, well, it's Google! But here's the kicker—the compensation offer was a bit underwhelming. Long story short, I made the tough call to switch to Amazon after a year for better prospects.

Let me know in comments if you have any questions regarding this. Ill try to answer as many as possible. P.S: I am sharing my resume template that helped me get into Google, Amazon and Uber here https://www.analystnextdoor.com/resume-template

Cheers!

Hi Everyone! I have a bit of a complicated question. I had some GA4 data messed up by a website code change that resulted in inflated numbers for engaged sessions and engagement rate for about 2 months. The average increase above what the numbers should have been was about 25%.

I'm wondering if there's some way to adjust the numbers in Looker Studio by reducing them by 25% for just that time period (I care more about engagement rate than engaged sessions, so if I were to pick just one, that would be it).

I tried using calculated fields to create a calculation that said if first session date is equal to (date range of inflated engagement), then "engaged sessions" * 0.75, if not, "engaged sessions" (leave the engaged sessions value the same).

This didn't work because Looker Studio does not let you mix dimensions (non-aggregated values) and metrics (aggregated values) in the same calculated field. I also tried creating a formula that would return Yes or No depending on if the first session duration was in my target date range or not and then using that calculated formula for the formula with engaged sessions, but that returned the same error.

I know there might be a way to do this with LookerML but I don't have access to Looker/LookerML. Can anyone think of any other creative workarounds? Could I somehow create a new data source with just the dates in the affected range, create a calculated field that reduces engaged sessions/engagement rate by 25%, and then somehow blend that data with my original data source on my reports? Any ideas are welcome!

I work for a business that uses the Wix bookings app to make appointments and manage schedules. We want to be able to create our own reports on bookings data in Looker but are struggling to get there. What's the easiest (and preferably free) way to get the live/regularly refreshed data into Looker?

Using Wix automations, webhooks or tools like Zapier isn't an option where we need the entire bookings table. Rows get updated when things like attendance status change. There also isn't an easy way to get all columns (like booking ID) through the Wix front end.

I've tried getting the data into Sheets with KPIbees.com and Make.com which each have their own issues. I'd call myself a mid-level user with little coding experience. Welcome any advice!

I am using looker studio for some work order management software. I export to excel and then convert to google sheet. I have created a very extensive dashboard in looker and users love it.

My question is what is the best way to update the data every week? I deleted data and then pasted new data into the sheet but its getting harder and harder since there are over 120,000 rows and 15 columns. Sometimes it jams up my pc. Can I just save the new sheet as the same name as the old sheet? will that replace it? Thanks

I've got a blended data source (GA4 mixed in with Google sheets).

I only had 4 charts/visuals drawing from this:

• 1 scorecard
• 1 time series chart
• 2 tables

They all worked when I built them but pretty much after loading successfully that first time they stopped all loading together (i.e. on one attempt say 2 would load the rest had errors, I'd refresh and the other 2 would load while the rest had errors etc.)

The only thing I could think of as a cause/ find online for inconsistent function was maybe that too many things were on 1 page....even though I was no where near the limit of 50 I figured maybe blends just struggle more. So I split them all out onto literally 1 page each. It worked beautifully for nearly an entire day.

Now we're back to intermittent loading.

Any ideas on how to overcome this?

Edit: Just to add- previously things like reconnecting to the data source has worked to get everything to load again but it seems to be a one hit wonder and goes funky again if you refresh the page. Additionally, this report is to be presented by some of our policy team (so, not data people) to some external stakeholders on a day when I am not in work. So ideally I won't be giving them access to try and reconnect themselves. I had got screenshots (PDF download just had error messages even though the charts had loaded on screen) in a power point to give them a static overview as back up. (Yes, I am giving up on Looker studio ever working for me smoothly).

I got three data sources: AAA, BBB and CCC. All of them follow the same column structure, data format and so on

BBB and CCC has of course different data inside

This is the blend I made from them where I use FULL OUTER joins on "Account Name" and "Date"

I named the blend as \"ALL\"

Now I created scorecard and a drop-down from the blended data "ALL"

I also made myMETRIC FIELD and CALCULATED FIELD so I can represent the combined dataset and use them. The "Data" panel in the right side does NOT show me the final dataset after the blend

https://preview.redd.it/8jadxmeduxxc1.png?width=1109&format=png&auto=webp&s=506e068df314823a4cb0ad1bbe18366c64d66df9

https://preview.redd.it/ykixs17euxxc1.png?width=1437&format=png&auto=webp&s=18af83d80a5c869d8efdc08affeb55c5b1584e0f

PROBLEM: I CANNOT make this dynamic connection to work.

The context of the data is we have various sources, and I want to represent them ALL in one to see how we did for the day, week, month etc. Same goes for other metrics.

I understand I can simply make different page per data source including their metrics where its interactive. I did and I was able to do that too without much problem.

But for blended data is giving me some headache. Any help will be appreciated.

https://preview.redd.it/z0a4r89vywxc1.png?width=831&format=png&auto=webp&s=9cfed89bc5ee06a2d8200738e35867d95253bd90

It's been 3 days and i've been having the same issue, anyone know why? Gotta hand in this report asap since it's May already 😭

Hi I believe Studio is following UTC but I am a bit confused with its time zone.

So this one, I've selected yesterday which shows May 1 already. I've checked google and it is showing May 1 5pm currently in UTC. but why does this show May 1 as yesterday when it supposed to be April 30.

https://preview.redd.it/11vpmqbwmuxc1.png?width=666&format=png&auto=webp&s=2f7dde8e1079b2df1f89968ef7602f6e8a2aaf58

I have also created a custom field to show the current date of google studio and this is what showed up

https://preview.redd.it/i6atptfknuxc1.png?width=416&format=png&auto=webp&s=1a981f7638e2cb667f02a2c213a1258d5e621909

Trying to figure out how really the date control works because it messes up our data when I am using default selection in date control

Thank you in advance!

My report's data source is a google sheet.
The report is a "monthly" report - so it shows us data for the current month.
Having said that, whenever the month changes, the data in the google sheet clears/refreshes - except the headers as filter formula is in row 2.

Both the google sheet and the GDS report are owned by me.
No changes whatsoever.

Why does the report show errors whenever the month changes?
Error on every single chart/table:

Data Set Configuration Error

Looker Studio cannot connect to your data set.

There was a problem fetching data from the underlying Google Sheet.

When I try refreshing the fields, it either shows:

1. It lists all my columns as missing fields, and lists all new columns which are exactly the same as the missing fields.
2. No connection changes were found

With (1):
If I click on a chart and then change the source to something else, and then change it back, half the time this solves the issue.

With (2):
I have to select and reapply EVERY dimension/metric on my charts/reports as it thinks all my source columns are different

Please help!!
(note: cross posting on r/LookerStudio)

Google Ads

I want to know how does someone can keep track of doing optimisation of campaign during busy period of christmas for a retail brand.

Any creative ideas or a tracker sheet or any suggestion. The real issue is I dont want to miss out on budget underspend.

Is it possible to implement automatic data filtering based on the logged-in user so that each individual only has access to their own data and information within the dashboard?

How do you all look at your transactions and revenue - by session based attribution or last click event based or data driven event based?

Thanks!

Hey guys,

I have a dashboard created in Looker Studio with 21 pages. It has some "buttons" that are interlinking throughout the pages to other pages..

But - and this has never happened to me - the links are always redirecting to the first page of the dashboard. its making me go nuts. Did it ever happen to anyone here??

I have a formula that requires the end date from the data range control to calculate pacing, however, I don't see any formula or variables that allows me to pull the date from the range control. Is this doable directly or is some work around required?

https://preview.redd.it/nk6ggon1kmxc1.png?width=412&format=png&auto=webp&s=de23f5fa0a16fb6465d0d4f7d5f0199fa9c2566e