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
For more infomation >> Digital Insights - First page of Google - Duration: 4:12. 
For more infomation >> Google Developer Student Club- Lead Role - Duration: 0:53. 
For more infomation >> Facebook, Amazon, Google - geht die Rallye weiter? - Duration: 16:46. 
For more infomation >> Sales force to Google Spreadsheet and Gmail - Duration: 2:45. 
For more infomation >> Idris Elba Google-Translates Hits on 'Jimmy Fallon': 'Baby Got Back" Becomes 'The Baby Escaped' - Duration: 2:13. 
For more infomation >> Google Imagens - Como pesquisar? - Duration: 5:14. 
For more infomation >> ¿Cuáles son las ventajas de utilizar Google Drive? - Duration: 2:43. 
Không có nhận xét nào:
Đăng nhận xét