Combine Data Sources in Google Data Studio | Lesson 2

By | November 5, 2019

In this lesson we’re going to set up our data sources in Google Sheets with the help of Super metrics so we’re going to pull the data from Facebook ads and Google analytics and then prepare it so it’s Ready to be used as a source within Google Data studio now all the more coming up right into this Welcome back to this course so in this lesson. We’re going to prepare our data sources in Google Sheets now why do we go the route of Google sheets as a source rather than all the other data connectors that are available within Data studio Well there are multiple advantages But the first reason being that Google Data – doesn’t have a connector for Facebook ads yet I don’t know if they’re going to implement it at some point. I hope so, but it’s a Google product So maybe they’re going to leave themselves some time with the Facebook ads But there is actually a tool that we can use in order to pull at the data in and that is Google sheets in the combination with Super Metrics now Super Metrics is a third Party tool an add-on that you can install to your Google sheets account And then you can pull data in from various sources they have many more data connectors than even Google Data Studio has it’s very Versatile tool, and we’ll get to into that in this lesson But that’s not the only reason Google sheets gives us the opportunity to work with our raw data We get from the tool like Facebook ads or google analytics before we then import it. We can actually change the data around we can clean it up and Most important, we can combine data sources So that’s not possible within Google Data studio itself to combine different data sources and then calculate metrics From them so really with a sheet we have much greater control about the data that actually goes into our dashboard and If you run into any issues we can investigate our data sources first and find out the error there and that’s really why I love using Google sheets as this bridge between our raw data that comes from the tool and Google’s Data Studio that we will use for our visualization all right enough As we get still lots to cover and all to make this work, so let’s dive into today all right, we’ll start out with a new Google sheet and Let’s think about what we actually need to make this dashboard work. We need our metrics and dimensions And just recap Metrics are basically the numbers that you will have in your table later on and the dimensions are the different properties that you want to divide your data by in a table metrics would be represented by the different rows here and the metrics that would be in those rows and the dimensions would be on top and represent the different Columns That’s important to think about so we pull the data correctly next up where do we pull the data from? Let’s go through a little bit of an exercise here let’s look at these metrics and decide where we would get them from and the impressions would come from Facebook the clicks as Well from Facebook then the cTR would actually be calculated, so this will be a calculated metric the ad spent as well from Facebook The CTC as well calculated as well as the Cp. Oh Then we have the revenue which would actually Come from Google analytics and the conversions also from Google analytics Now this is actually an attribution topic here if you trust more the attribution of Google analytics Then you want to pull it in to your dashboard and we will do this in our example Next up the return on adspend which is also calculated at the end the ad budget which will be put in manually as Well as the Target’s epo and the Target CPC So now we have a good overview of what metric comes from where and what data we have to pull let’s talk about Briefly about the dimensions because Dimensions are the different columns that we want to put now these are obviously heavily based on our Facebook campaign we have our date campaign name ad set and ad name But as we have seen we want to also pull data from our google analytic account now How would that be represented in Google analytics? We may have the date Available, but not the campaign name ad set and ad name at least by default in Google analytics And that’s why we need to actually connect both tools together In order to have all these dimensions in both tools And that is done through utm parameters now if you’re not familiar with utm parameters. We have another video on that basically you need to make sure that when you input your Campaigns into Facebook you need to be clear on the campaign name Campaign content and campaign term that you would choose Beforehand then you can go ahead and tag your link so in this sheet We have a link that we prepared in order for our Campaigns to be tagged correctly and then when the user clicks on that link or goes to that url He will be automatically registered in Google analytics with these campaign parameters, so make sure in Facebook and your ads campaign that your campaign name your ad set name and your ad name actually is Tagged up correctly so there’s an option for that Which is called utm parameters you need to have these? UTm parameters attached to your link in order for Google analytics to register this Correctly so once the user clicks on the link it will be registered in Google analytics Itself for once you go to your source medium reports we have here Facebook CPC, and then we would have our Campaign name, so let’s put it in as a secondary dimension This is called campaign in Google analytics Then we would have our ad content which is the utM content parameter so here we have interest brands and We would need to have our keyword Which is our utm term that we also fill with these parameters so again you need to make sure you tag them up correctly and Send a user to the right link in order for this data that we Want to break our? Metrics down by is available also in Google analytics so this actually only works if we have these dimensions in both Google, analytics and Facebook now in Facebook this might be called date campaign name and set an ad name in Google analytics Itself this would be called date campaign Ad content and Keyword different naming convention, but Essentially we should get the same data in Facebook when we query for this then in Google analytics for these different parameters So be sure you have the same data. So you can connect it in your google sheets later on Alright now that we are clear on what we need to pull from where with what dimensions? Let’s go ahead and prepare our data sources. I will open up a new sheet here and Call this Facebook Data and a second sheet called Google analytics Data Now we can go ahead and pull our data with the help of Super metrics now if you’re not familiar with Super metrics, you can actually install it by going under the add-ons and go to get Add-ons and simply Enter Super metrics and You can install it to your account now the capabilities. We were used with in Super metrics are a paid feature So you will need to upgrade your account but for anybody who’s trying this out? You can use the pro features for 30 days so once you have it installed you can go to add-ons and then on the Super Metrics launch the sidebar and Now you’ll be able to Configure your query Alright first up we’ll choose our data source now Super Metrics has many data sources available and therefore it is more powerful than what the building capabilities of Google would you actually provide So you can pull in a host of other data into your dashboard if you choose so we will go obviously with our Facebook ads account Now we need to enter our Facebook details. So you need to go through the process of registering this with Super metrics so you can pull the data into your account once you have that you can choose your account and then Select the account if you have multiple accounts under your login then you can choose your different data sources and different accounts that you want to select and We go on to actual date selection now How much data should be pulled here there are different options like today yesterday? the month to Date options the Year-to-date options but also custom date ranges now if you want your dashboard to be kept up to date and Periodically be updated then you might want to use the last days or weeks feature Because that will ensure that Your dashboard will always update itself with the available data to that day now we will be looking back for one month, so let’s go with this last month option and We want to go back just the last 1 month you can click including this month or just the last month, so we will just go with the last month you can even choose a comparing option to the last year that’s something we won’t do for our data export and Then we’ll get to the different metrics and dimensions I’m just going to get rid of the last data. We pulled here start out new Again, what do we want to pull let’s go back here. We saw we wanted to pull our Impressions clicks and add spent so let’s just enter that here impressions and It gives us a quick overview on the available metrics, so we will go with impressions or not social impressions or the frequency We want to get our clicks so put in our clicks now They’re different clicks actually and if you go with the all option this will also count your social interactions Like the like button on your ad so we just want to have the link clicks that actually lead to our actual page and We want to have our ad spend so we’ll just query for our spend so here We have the amount spent that should do it Let’s go ahead and choose our split buy option and these are all about the different splits We want to have in our roads but also our dimensions that we want to pull in and this is where our different dimensions come in so let’s go ahead and pull in the date You campaign name the ad set actually called ad set name and The actual ad name so here we go Now we have that an order, but there is a restriction on the number of Rows that you can pull in So if you have a lot of data, you might need to split it up into multiple Data exports in order to make this work for our case this will actually do You can also use the sorting but this is mostly done by date Let’s just click on the date And we don’t have to split by any kind of columns You’ll see how the data export will look in a second you also don’t need to Choose any kind of filtering now this would be an option that is more helpful in our Google analytics export as we will see in a second, but if you wanted to filter out certain campaigns You could do this here as well, and then we’ll get to the options and those are really important because we are Preparing our data for Google Data studio, so be the important options You need to tick is the format results for Google Data Studio Also show all time values is an important option in order to ensure that your data stays Consistent throughout, you updating the Data as well So let’s keep that turn on and before I start this data export we want to go to the right sheet again and the right column and Let’s click on get that table Alright, so here. We go. This should be our data and now this data is actually a Bit of Dummy data that I have loaded up here in reality You would probably have some holes in your data and need to figure out whether these should be taken into account for your Facebook ads dashboard but since we don’t have much data in our Facebook account I still wanted to take the opportunity to show this off correctly so now we have this data in here first of all we see here these are our dates and then we have our campaign name our ad set and Our ad name and the different impression clicks and the amount that was spent on these clicks So I think you can also see as you could calculate for example the cost per click by dividing these two numbers Now there’s a lot of data in here because this is actually just the first campaign if we go down we see that here comes the next campaign here’s the next campaign we have different ad sets and We have different ads Within those ad sets so this amounts to a lot a lot of data But this is the raw data that then Google Data studio will take in order to do and visualize our analysis So this worked out fine. Let’s go ahead and go over to our Google Data sheet and here We are going to pull Data from Google analytics So again, we’ll go through the steps of choosing our data source selecting our metrics Okay, that would be transaction and the transaction Revenue and Then we want to split that by the Rows of Data again and Again now if you go back and look at our google analytics campaign values that need to match up with the Facebook data You’ll go with campaign name this would be campaign the ad content and The Keyword And then we can choose how many routes we want to fetch again? I’m going with the maximum right here. I? Don’t need to split that data now I only want to actually pull data that comes from Facebook, so I could choose a segment that I have pre-built in Google analytics But we didn’t do that, so I will just go with the filter option here And I can add a filter and as we looked at our google analytics reports you can just use the source medium depending on how you have Marked up your uTM parameters And you can say that our source is Facebook and our medium was CPC So we only pull the data from this source I hope that makes sense in the next option we can actually make sure that again. We want to Prepare our data for Google Data Studio And there’s another option right here that can try to avoid Google’s data sampling now if you don’t know about sampling in Google analytics it’s basically a way for Google analytics to do the computation of the metrics faster by leaving out some Values of your raw data set when you export the data via the reporting api you can actually avoid data sampling Even though you are not on a premium account by pulling a small data set and doing multiple of these Exports in order to get clean and raw Data that is not sampled So this is something that Super Metrics can do it might take a little bit longer, but it’s worth it so you get actual clean and sample Data you’re also going to turn our show all time values again on and We are ready to go let’s go back to our data sheet and get the data So here we go well successfully pull the data in the transaction and the transaction revenue and you can also see that the convention up here the Dimensions have changed but it’s basically the same data that we have pulled in our Facebook export as well so this data needs to match up in order for us to be able to aggregate both of these sheets together so you need to have both data points in order to be able to use both of these Metrics within Google Data Studio later on Now the next step is to actually combine the data because in Google Data Studio We can only use one sheet within a google spreadsheet as a data source, and we wouldn’t be able to do any kind of calculated metrics that are spanning over two separate data sources Within Google Data studio So we need to aggregate the data. So let’s go ahead and build a new data sheet which we’ll call Facebook plus Ga and Let’s do some Google Spreadsheet work. Let’s close our Super Metrics panel here and combine this data Now first of all I want to pull in this Facebook data sheet We could do this several ways the form that I actually Prefer would be to use query functions if you don’t want know what query functions are a bit more complicated But there’s great material by David from coding and for losers Or by Ben Collins that we will link up in the description below as well, so right now I’m going to write a little formula a query formula that pulls and the data from the Facebook sheet With the select segment this is mostly the query language of sequel so if you’re familiar with that you can use it right away, so now we have the data in here and it’s Automatically updated if we have any kind of changes in the Facebook data and now we want to be able to import our transaction and revenue values here, so I’m going to just Copy this over and now we need to match it up between our two sheets Now it might be that the Data we see in Facebook and Google analytics is Not in the right order when we pulled it from Facebook then from Google analytics So we need to make sure that we are choosing the right row here in order to pull the right date and the right campaign and the right app set and the right ad name into our Aggregate sheet here and the different formulas again to do this a lot of people use something like an Index match but I get really complicated not that my solution is less complicated, but I again use the query function in order to query the right transactions here, so I’m going to start out by inputting our query and choosing first of all the Data set so our data set would be Inside of the Ga sheet without the header here. I’m just going to mark this here and Extend that would be 990 all right, and Now to hold all the data from the sheet now that’s something we don’t want so we input a query So in our case that would be you only want to select The Column let’s see which Column it is e That should be go up here the transaction so select only Column e and We just get the different transactions now These actually need to be matched up to the actual campaign name at set at name and the date. So there’s another formula I use here. We’re not going to bore you with the details so this is the actual formula, but really what it does it just Compares first of all the date range with the date that we have on the other sheet So a should equal this a two Right here our b Column should be the same as this column our C Column our d column and so on that will be Compared to our ga datasheet, and then if there’s a match it will pull in our transactions So let’s see what it does It actually only shows the transactions for this actual field now if I double click right here It should pull that formula down to the bottom Unfortunately, I forgot something I actually need to make this fixed, so let’s do that enter here double click again and Now we get all our different data points in here now You saw that there were some that were missing actually and it might be that there was no traffic actually registered by Google analytics So these would need to be 0 now we get an error because you can find this actually in the ga data sheet so we’re just going to change our query here and put an if statement and Say if there’s an error if error we can fill this with a zero All right so let’s correct that We could also put it on to the whole Column here, but I won’t do that now I’m going to do the same thing for the transaction revenue, so I’m going to just kind of copy this formula And put that here, but this time I want to select A column f from our tree a datasheet put that if error statement in front of it as well and That looks like to fill the other rows out take a while But we get it all in and now we have the different transactions and the different Transaction amounts matched up to our different campaigns ad groups and ad sets So this data would be now ready to be imported into our Google Data studio account before we go on to the next lesson. We can actually with the help of Super Metrics schedule this also under Super metrics. We also have a Schedule and refresh emailing option that lets you configure when you want your queries to be updated so under this action we can say we want to refresh us hourly if We look at our dashboard very often I would say daily is enough Let’s go with the daily option when you want to process that That seems fine two o’clock at night. Let’s saw the trigger and now our data will be kept up-to-date by refreshing our Queries that we have configured in Super metrics every day at 2:00 a.m. so now that we have our Combined sheet ready. We can start with importing that data into Google Data Studio which we’ll do in the next lesson Alright, so now we have our data prepared in sheet That was a bit more work, and you might expect, but it ensures really that we understand the data That is underlying our visualizations later on and can fix any error now You can take a look at the data sheet that we have just built in the description below I will link it up there, and this is a sheet that will utilize to build our dashboard in the next lesson So if that’s already available you can click that video over there our if you haven’t yet Then consider subscribing down there because we’ll bring you new lessons. Just like these every week now. My name is Julian Fiona next lesson

23 thoughts on “Combine Data Sources in Google Data Studio | Lesson 2

  1. Valdemar Biondo Jr. Post author

    Hi Julian, the information you gave to your 17: 06s from your video does not work. We are having serious issues with a customer because we need to bring real data from within the Google Analytics API (after all, it's actual sales data, properly collected by the platform, not sampled) and he insists on bringing false data when we run the queries In Supermetrics, even if we use this "try to avoid" functionality.

    After two weeks of waiting after proving that the data was not coming correctly the Supermetrics staff washed their hands indicating that they could do nothing and asked us to complain to Google, which in turn does not support Google Analytics (wich is free), only to Analytics 360.


  2. faceoffland Post author

    Thank you Julian! Im a big fan of u after this video! 🙂 keep it up man!

  3. Kevin Lavery - GrowOnline Post author

    thanks Julian – great video – keep it coming

  4. Juanra Ortiz Post author

    Thanks Julian. Nice tutorial but I found Supermetrics very expensive.US$50/month is too much for personal use. They need a tier for non agency business. Do you know another platform to pull data from GA and FB?

  5. marta_krawczyk Post author

    Hi Julian! As always, great video:) I have one question. You are using Supermetrics to create a feed. This is not a free tool, and, after the trial, it's rather expensive. Can you recommend another way of creating the feed in Google Sheets (that might require a little more work, but still be relatively easy)?
    Simple data export from Facebook Ads doesn't seem easy to format. Maybe you have an idea? Thank you:)

  6. Dávid Forgáč Post author

    Hi Julian, how did you solve this problem in 20:11 with #REF!? I cant figure out how to do it 🙁

  7. Naiara Canizares Post author

    Hi Julian! my supermetrics is not giving me correct data, it always give to me zeroes in all the queries, with google API works well, what is happening? how can I fix it? thanks

  8. Mike Murphy Post author

    Julian, I appreciate your videos very much. Thank you for taking the time to train us on these processes! I have a question for you. I set up a tab to contain aggregated data so that I can pull it into the Google Data Studio. I have the SuperMetrics tabs scheduled to update on a daily basis. Those work fine but how can I get the query function to update on a daily basis? I'm specifically talking about the query you ran at 19:50 in the video with the asterisk to get all the data. Is there a way to run that query on a daily basis so that the aggregated tab has all the new data?

  9. Rômulo Gomes Post author

    Thanks a lot for this tutorials, Julian. Quick question: I'm trying to unite many ad channels (facebook, adwords, criteo, etc) on one sheet. The problem is that on a same day I have multiple channels… What do I do? Do I repeat the days to fit all channels? This is really bugging me 🙁

  10. inQbrands marketing Post author

    The dashboard plan link works, but the email you receive does not contain a link or an attachment.

  11. GoAlex Marketing Labs Post author

    Julian, Great stuff! Any thoughts about how to match late conversions, which register in GA on a different date? That FB campaign could no longer exist, or have zero spend.. and we'll miss conversions.
    Thanks in advance!

  12. Calico Majik Post author

    Fantastic presentation! You have an ability to make things understandable. I'm very new to this, and seeking tutorials. Finding this has been a blessing! Wow! and Wow! again! I am inspired to keep searching and learning. Thank you Julian. ….be well…

  13. d_safko Post author

    Is there a way to find out the meaning of all the different metrics? For example, at 11:16, you see 6 different "click" metrics… how can I find out the difference between these and other metrics that appear to be so similar?

  14. Boo Umaly Post author

    anyone have a FREE alternative to Super Metrics for importing Facebook data into Sheets?

  15. Joe Alder Post author

    Now that there is a Facebook connector on Data Studio, would you still use google sheets first?

  16. Rory McGonigle Post author

    Hey Julian! I've been loving your youtube channel and was curious if you are using a green screen for most of your backgrounds like the one in this video?

  17. Rotem Kraif Post author

    ' I followed all steps but the website i'm trying to get the data from isn't E-commerce therefore "Transaction" etc are not working for me, any suggestions for a different other than "session" I can use?

  18. Sharkshavesharpteeth Post author

    Great vid! Question, in order to match the rows in FB+GA, the campaign names (etc) needs to match. How do you make sure to always keep the FB campaign names equivalent to GA? do you use the utm_campaign as campaign name in FB?


  19. vinayak bhosale Post author

    Hi,site to pull current stock prices from India or source to pull stock prices from India?

  20. Matt Hojsik Post author

    is it possible to create custom calculated fields from two different data sources? E.G. Facebook_Ads_Metric/Google_Sheets_Metric


Leave a Reply

Your email address will not be published. Required fields are marked *