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

KP Playbook - Analytics, SEO & Marketing Training

Подписаться 714

Просмотров 3,5 тыс.

50% 1

#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 RU-vid 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...

Опубликовано:

9 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 53

@JackHodgins-q3b 6 дней назад

Super, helpful!

@designbyjus Месяц назад

You are AMAZING. Thank you a million times over!

@kp_playbook 13 дней назад

You are so welcome! Happy to help.

@user-ep2ps6ug9k 5 месяцев назад

Unbelievably helpful. THANK YOU!

@kp_playbook 5 месяцев назад

You're very welcome!

@AndySimpson1969 8 месяцев назад

wow, just wow, thanks Dana!!🔥

@kp_playbook 8 месяцев назад

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

@AndySimpson1969 8 месяцев назад

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

@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?

@kyleburns1752 5 месяцев назад

Amazing Tutorial, thank you very much!

@kp_playbook 5 месяцев назад

Glad it was helpful!

@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!

@tombugeja7252 12 дней назад

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 11 дней назад

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.

@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!

@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

@shaunms328 6 месяцев назад

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

@kp_playbook 5 месяцев назад

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.

@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-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 6 месяцев назад

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

@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 13 дней назад

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 6 месяцев назад

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 5 месяцев назад

@@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 5 месяцев назад

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!

@user-xd3ds8dx8g 5 месяцев назад

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

@kp_playbook 5 месяцев назад

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 5 месяцев назад

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

@kp_playbook 5 месяцев назад

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.