How to Blend Universal Analytics (UA) and Google Analytics 4 (GA4) Data in Looker Studio

Рет қаралды 3,566

KP Playbook - Analytics, SEO & Marketing Training

Күн бұрын

#lookerstudio #dataanalytics #ga4 #googleanalyticstips
Learn how to blend UA and GA4 data together in Looker Studio (formerly Google Data Studio) so that you can present your metrics in one graph, combined between the two properties. In this video, Dana DiTomaso will walk you through how to use Looker Studio to blend Universal Analytics and Google Analytics 4 data sets together!
Important Note! Metrics will not be exactly the same between UA and GA4. Please make sure to watch our video on 8 key differences between UA and GA4 for more information.
When GA4 was first launched there was a lot of confusion as a result of Google using phrases like “migrate from UA to GA4”. Migration does imply that you could move your data, so it’s no wonder that many people wanted to know “how can I import my Universal Analytics (UA) data into GA4?” Unfortunately, the answer is that you can’t. Importing is impossible. UA and GA4 use completely different data models and importing isn’t possible.
But you can blend and combine your data from UA to GA4. A very important caveat to this blending of data is that common metrics like users and sessions are measured differently in UA and GA4. Your data will not match perfectly between the two, so it might be misleading to combine the two in a single graph.
The formulas used in this video can be found on our website:
kpplaybook.com/blend-data-tra...
(Unfortunately KZfaq doesn't allow special characters in video descriptions)
Subscribe to our channel: / @kp_playbook
View the full transcript on our website: kpplaybook.com/blend-data-tra...
*** TIMESTAMPS ***
0:00 - Intro
1:01 - How to Blend UA and GA4
1:58 - Display both UA and GA4 in One Table
4:32 - Create Calculated Fields
6:04 - What Are the Calculated Fields Doing?
7:34 - Display UA and GA4 in a Time Series Chart
8:32 - Filter - Only Include Organic Search Data from UA and GA4
⭐ FEATURED RESOURCES ⭐
➡️ Analytics for Agencies Course - kpplaybook.com/blend-data-enr...
➡️ GA4 vs UA: 8 Key Differences Explained - kpplaybook.com/blend-data-key...
➡️ Need help with your reporting? Get in touch with our experts - kpplaybook.com/blend-data-con...
👍 LET’S GET SOCIAL 👍
Twitter - / kp_playbook
LinkedIn - / kick-point-playbook
Newsletter - kpplaybook.com/blend-data-new...

Пікірлер: 54

@JackHodgins-q3b 20 күн бұрын

Super, helpful!

@kp_playbook 13 күн бұрын

Glad it was helpful!

@user-ep2ps6ug9k 5 ай бұрын

Unbelievably helpful. THANK YOU!

@kp_playbook 5 ай бұрын

You're very welcome!

@designbyjus Ай бұрын

You are AMAZING. Thank you a million times over!

@kp_playbook 27 күн бұрын

You are so welcome! Happy to help.

@kyleburns1752 6 ай бұрын

Amazing Tutorial, thank you very much!

@kp_playbook 6 ай бұрын

Glad it was helpful!

@AndySimpson1969 9 ай бұрын

wow, just wow, thanks Dana!!🔥

@kp_playbook 9 ай бұрын

Thank you so much! We're glad you like it!

@AndySimpson1969 9 ай бұрын

@@kp_playbook too late now to play with but there goes my Tuesday!!

@thomasmann7457 4 ай бұрын

Thanks a lot Dana! Really helpful :)

@kp_playbook 4 ай бұрын

So glad we could help!

@susannap.8621 5 ай бұрын

This tutorial is saving me, thanks so so much!!

@susannap.8621 5 ай бұрын

Question: Is there a way to rename the new SUM Metric? I have it set as "Blended Sessions" like you but what if we'd like to change that so it shows up differently in the chart and legend?

@kp_playbook 5 ай бұрын

If you mouse over the metric name, you should see a little pencil icon -- click that and then you can rename it!

@benstewart6707 4 ай бұрын

This is honestly one of the best tutorials ever! The only issue i'm personally experiencing is that the user numbers and comparison percentages are wrong? For example, in February, there was 5,500 visitors, but the blended scorecard shows it as 5,200. Why would this be?

@kp_playbook 4 ай бұрын

I wonder if the leap day would cause any problems? Do the numbers work for January?

@user-zy4ef3cw9b 5 ай бұрын

Amazing video thank you so much! Is it possible to create the date cutoff point in a table if it doesn't contain the date column? For example I'm trying to blend UA and GA4 data to show a list of page titles with sessions, new users etc., but there's an overlap in UA and GA4 gathering data so coalesce is adding them together. When I add a date filter to the blended data table it's only pulling from one of the sources rather than both.

@kp_playbook 5 ай бұрын

Make sure that your date filter is using a calculated coalesced date field instead of the individual field from one of the data sources in your blend. That will fix it!

@user-zy4ef3cw9b 5 ай бұрын

thank you!@@kp_playbook

@tombugeja7252 26 күн бұрын

Great video. Thank you so much for posting this. So i have a question: I've successfully followed the steps and everything works - except pulling UA data from google sheets as they're about to cut off acess to UA data ! - and am now trying to present my data by say Year Month but it won't aggregate the session data. Is this something to do with the session formula? Would love to get your help on this 🙏

@kp_playbook 25 күн бұрын

Are you blending with Google Sheets instead of with UA? We'll be making a new video showing how to do that since we're almost at the UA sunset date.

@shaunms328 6 ай бұрын

Such a great tutorial - What will you be doing when the UA data is no longer available?

@kp_playbook 6 ай бұрын

Great question! We'll update this tutorial as people move their data into other places, but you can do this blend with exported UA data in Google Sheets or BigQuery as well, same process.

@user-wd9pf5yf9i 7 ай бұрын

Hey, I get user configuration error with this blend. I tried it exactly as you showed, just with sessions to test, but it gives this error

@kp_playbook 7 ай бұрын

Can you provide more details as to what the error is? We can take a look.

@frankberrocal 3 ай бұрын

Question. Following the same procedure, the blended table always indicates no data, though I have data in both GA4 and UA tables. In your example it only happens, but I did also pageviews and get the same result always: an empty table. Any suggestions please? Thank you .

@kp_playbook 3 ай бұрын

Double-check the join for the blend to make sure it's set up the same as the video, and check that the date is matching in both. Otherwise, if you'd like us to take a look, share the dashboard (edit access please) with playbook@kickpoint.ca and we can check it out!

@frankberrocal 3 ай бұрын

Answer: I was missing the date control field! Amazing video. Thank you.

@kp_playbook 3 ай бұрын

@@frankberrocal Oh good, I'm glad it was an easy fix! You're very welcome!

@RichieProth Ай бұрын

I'm looking at doing a Time Series chart for Transactions. It will show the last 30 days on GA4 and then the previous period last year on UA. Are you able to do different time frames? Data just shows as nothing

@kp_playbook Ай бұрын

A comparison to the previous year should absolutely be working. Are you using the built-in compare function on the chart, or are you showing the two time periods in separate charts?

@RichieProth Ай бұрын

@@kp_playbook is there a way I can send you a screen recording via email. It would save my life!

@RichieProth Ай бұрын

@@kp_playbook is there a way I can screen record and email you? It would save my life!

@kp_playbook 27 күн бұрын

Yes, email us at playbook@kickpoint.ca!

@user-vs5mg2jt4m 7 ай бұрын

This is Awesome. What would the modification be to use Months of Year instead of Date?

@kp_playbook 7 ай бұрын

You would still use the date field for the blend itself, but towards the end where Dana changes the date grouping to weeks - just change it to months instead.

@EmilyRitterPC 6 ай бұрын

@@kp_playbookCan you point to where this is in the video? I'm not seeing where she changes the date grouping to weeks.

@kp_playbook 6 ай бұрын

Sorry, our mistake -- that part got edited out for time. Here is what to do: click on the little date icon to the left of the date dimension (you should see a pencil icon). Then in the "data type" drop down on the window that opens, select the grouping that you want (in your case Date & Time > Year Month is probably the best fit). The data will change right away, and you can click out of the data type selection box. If you run into more problems, let us know!

@ChadSolomon 3 ай бұрын

This is a super helpful video. Thank you. Could you display the line graph with the dimension month and drill down the dimension year so you can see each year visually from January to December? Or do I need to modify the formula? The following isn't working. Dimension Date Type: Month Display Format: Month Formula: COALESCE(GA3 Date,GA4 Date) Breakdown Dimension: Date Type: Year Display Format: Year Formula: COALESCE(GA3 Date,GA4 Date)

@kp_playbook 3 ай бұрын

Try "Year Month" for the first dimension instead of just "Month". I just tried that myself and was able to get both Date Types/Display Formats to return data.

@ChadSolomon 3 ай бұрын

@@kp_playbookThank you; that is great. However, it creates a continuous line by "Month Year", so the x-axis has Jan-22 through Mar-24. I'm looking to create multiple lines by Year. So, the x-axis is fixed to Jan-Dec, and each line on the y-axis is the Year (2022, 2023, 2024).

@kp_playbook 3 ай бұрын

@@ChadSolomon Ah I see! I was able to reproduce this now. The issue seems to be with the UA data only -- if I use the date as a breakdown for GA4 data only, it works, but when I change it to UA it doesn't. This could have something to do with how dates are set in UA data vs GA4. What I would recommend is export your UA data -- which you should be doing anyway, sooner rather than later -- and then try this blend again with your exported UA data and see if that fixes it. We'll be releasing a follow up video showing how to update this blend for exported data sources as we get closer to the UA deletion date. Do you have a plan for exporting your UA data yet?

@ChadSolomon 3 ай бұрын

@@kp_playbook@kp_playbook, that's a great shout; no, I have no plan to export the UA data. Do you have a video on that, or is it coming?

@kp_playbook 3 ай бұрын

@@ChadSolomon We will definitely have a video coming on that. Right now we're trying out the export service by Analytics Canvas.

@user-xd3ds8dx8g 6 ай бұрын

hi! i'm having issues with the initial right click > blend stage... after doing that i get only one row of data!

@kp_playbook 6 ай бұрын

Yes, the initial blend is incorrect -- follow the instructions (or better yet, the video, you can see we have that happen too!) on how to fix it.

@user-xd3ds8dx8g 6 ай бұрын

yes it all worked out in the end... thank you!@@kp_playbook

@kp_playbook 6 ай бұрын

Glad to hear it!

@user-sz3cb6it3w 8 ай бұрын

top top top

@kp_playbook 8 ай бұрын

Thank you!

@MrArsyan1 8 ай бұрын

triend copy your blended code but show this Invalid formula - Function "FORMAT_DATETIME" doesn't support FORMAT_DATETIME(TEXT, NUMBER). Function "FORMAT_DATETIME" supports FORMAT_DATETIME(TEXT, DATE), FORMAT_DATETIME(TEXT, DATETIME).

@kp_playbook 8 ай бұрын

It isn't recognizing the COALESCE(UA Date,GA4 Date) part of the formula as a date. Make sure that your UA Date and GA4 Date fields have a type of "Date". You can check that by going into the data source itself. If you're still getting stuck, feel free to share what you have with playbook@kickpoint.ca or email us there! We're happy to help.