- Alright, so now that we have prepared our data
in Google Sheets, we are ready to visualize it.
We'll first calculate our metrics,
then lay out our dashboard, and then, finally,
implement our data in Google Data Studio.
All and more coming up right after this.
(upbeat music)
Today our journey starts in Google Data Studio
where we're finally gonna visualize our data
in our dashboard.
Let's start out by clicking on a blank sheet here,
blank new sheet,
and then choosing our data sources.
So we can create a new data source
and we'll choose our Google Sheet.
Now, from our Google Drive account,
we'll pick the sheet that we had prepared
in our last lesson.
Let's do that.
Now we can see that we can actually
choose the different worksheets as a source.
This is actually something that is unfortunate
because you can only choose one worksheet
as one data source.
So you couldn't say okay, I wanna choose
the GA data from here and the Facebook data from here,
and combine it all into one data source.
No, you need to have them on one sheet
in order to make this work.
That's why we prepared the data this way.
Okay, let's go over to Google Sheets
and choose our combined datasheet,
and then we can choose under the options
to use the first row as headers.
That is fine with me.
If you look at the datasheet,
this would describe our data,
so we can pull that in.
And we can include any kind of or filtered cells,
which is not the case in our sheet,
so I can untick this.
You can also choose an optional range
if you have data on that sheet in a special range.
This will be all for us under the configuration.
Let's connect this all.
And we will pull in our different dimensions
and the different metrics.
Now, as you might remember, in our last lesson
we actually prepared that and looked
into the metrics and dimensions
that we want to pull.
Unfortunately, first of all, these are
not exactly the same name that we want
to display them on the actual dashboard,
and therefore I would recommend
to actually rename them right here.
It's pretty easily done.
For example, link clicks should just be clicks.
You can just click on that and change the name around here.
The amount spent will just be our ad spent.
Our transactions are just our conversions,
and our transaction revenue is actually the revenue.
The campaign name, let's rename that in just campaign,
and the ad set, and the ad name can stay the same.
All right.
So now we have renamed our dimensions and metrics,
but we also wanted to calculate some metrics.
So for example, here's the CTR which is
impressions divided by our clicks,
and we can easily input that by going into Google Data.
So we're clicking on this Plus button,
and it will let us input a name,
which in our case was the CTR, the click-through rate,
and then the formula would be impressions,
and it already pops up, our impressions
from down here, divided by our clicks.
Alright, let's create this field,
and it's now part of our dataset.
With calculating metrics, these are custom,
so Google Data Studio doesn't always know
what kind of type it is, and this is actually
a number but it's actually a percentage.
So you need to make sure that you choose that correctly.
We'll auto-aggregate that, that's actually
something we can't really change.
So let's go ahead and do our other calculated metric, CPC.
CPC is the cost per click.
So let's click on the Plus button here,
CPC, and this would be the ad spent
divided by the clicks.
Alright, create this field.
This is a number, that's correct,
but it's also a currency, so we can choose that as well.
Let's pick US dollar here.
And we have all CPC metric.
Let's go ahead and calculate the CPO,
the cost per order.
So let's click on the Plus button,
cost per order, that would be the ad spent
divided by the order transactions.
We called it conversions.
All right.
Create this field.
And we have one more left, return on ad spent.
Return on ad spent.
This would be our ad spent divided by the revenue.
Alright, that should do it.
Now, we have a few more left
that are actually manually inputted.
This is something we could put into our sheet
but it's actually easier to update it manually
in our sheet.
Because sometimes Google Data Studio
can actually pull the data from a sheet
for certain cells.
Okay, we have our data sources ready,
let's add this to our report.
And here we go, now we have that available
in our data sources if we choose to visualize anything.
Right we you see our data range
and our metrics, these are now available here.
Okay, before we start out visualizing anything,
let's get clear on what we are trying to do.
Fortunately, we have prepared a wireframe
in our first lesson, so we know
what we want to build and have an idea
of what elements should be on our dashboard.
So on this dashboard we have kind of like
a three column layout here, and three columns here.
Let's mock that up really quickly in Data Studio,
and we can choose the rectangle tool to do all this,
just so we can get the proportions right.
Because we don't wanna mess with all the layout
later on once we have our data together.
This will make this whole process a whole lot easier.
So let's just mock this up.
So now we have laid out our information,
what goes where, and this is actually also
a great template that we could use later on
if you would like to build a new dashboard.
So you can go ahead and actually make a copy of this
and rename this in some kind of fashion to template
so you have that available later on as well.
Okay, let's continue here with our report.
Let's fill this all with data now.
So we'll go ahead and fill out these panels.
Up here we would have our controls,
so that would be our campaign but also our date picker.
So let's put that in place.
Date picker and filter control.
Right here there's actually a new filter control.
This is for the different views of Google Analytics.
That's not something that we would use here.
Okay, so we can get rid of the panels in the background,
and this should give us capabilities of showing us
the right campaign and the right date.
Let's view this and here we can see
our different campaigns.
There's null.
That is something we would investigate
with our raw data again.
And then we have our date picker,
so that is all working as expected.
Alright, let's continue with these metrics groups here.
Before we start out, I want to actually select
the date range so we are working with dates
that actually make sense for us,
and in our case it would be the July date range.
So I'm just gonna change this to this week here,
and that will make all our data appear
in that realm, and if anything goes wrong here,
we can also adjust it later on.
So let's go ahead and build these metric groups.
Now in our wireframe we see that we have
these groups of acquisition, cost and conversion,
and inside of them we have
these different metrics that are represented
by the actual impressions, the CTR and the clicks,
and then also the comparison if it went up or down
over the last period.
So how do you implement this?
You can do this with the scorecard elements.
So we'll just draw on the canvas here
our scorecard element and choose the right metric,
so in our case it would be impressions.
And we can actually, this is a pretty big number,
go into Style here and press on Compact Numbers,
and this will make this a bit more compact
so we can actually read it.
We want to have our comparison metric,
and for that to actually appear we need
to implement this second date range
so to compare date range, and this should
compare to the previous period.
This will give us this little number.
Now you don't see this actually,
so I'm just gonna change this around
and make the inside transparent.
Okay.
And before we move on and copy this over,
let's style this a bit so we don't have
to do this later on.
We can easily copy it over
and change the data around.
So we want to have our CTR.
That doesn't make any sense so maybe
there's something wrong in our metrics.
Let's create a new metric or go
to this menu and we have here our CTR.
This should be not clicks, impressions,
but clicks divided by impressions.
Okay.
Let's update this field.
And we see our data also changes.
Now we also need to change
that this is actually percentage.
Alright, nice.
So we have that data point and let's copy this over,
and we want to have our clicks.
Alright.
We can go ahead and, again, style this a bit more.
Already have our invisible box here.
Let's style this a bit.
And we have built our first metric group.
Now we can go ahead and just copy that over.
And change labeling and also the metrics.
Here I see we don't have our conversion rate yet.
That's actually something that we overlooked,
so we'd need to go and create a new metric,
and this should be our conversion rate.
So this would be conversions divided by our clicks.
Let's create this field.
And we can choose it in our metric picker.
Oops, we forgot to change the field type.
That would be a percentage.
And here we want to have our revenue.
Notice that the actual panels here have
the description name of the actual metric,
and that's why it was a good idea
to actually rename them at the beginning.
Alright, so let's proceed to these three panels here.
Now the first panel being a goal section
where we would have our ad budget, CPC target,
and CPO target in here and the respective goal reach.
This is a little bit of a manual approach.
Unfortunately you can't make it too dynamic
because you can't actually change the targets
inside of these sections
via Google Data Studio dynamically.
Let's go back here and we'll choose
our bullet chart here that we can just draw in,
and that's about what it will look like.
We can style it a little bit more,
and give the whole thing a name.
And then we can style our bullet chart.
So here's the data point we want to have, our spent,
and we can choose the ranges as well.
So let's do that.
We have three ranges available.
I will just use two ranges here,
first one being from zero
to our respective budget that we wanna hit.
If it goes overboard, so in our case
that would be 15K, and then we can choose
our target, and we have a target of 10K.
That's our monthly budget.
That's why we put it up here as well.
Now we can style this again with a few colors.
We can show where is right now
and the background of that, if it's in a green area,
and just use this green bar.
Unfortunately, you can't change this dynamically
if it is still under the 10K or over the 10K.
Okay, let's build the same thing
for our CPC target and CPO target.
We choose our current CPC as the metric.
Obviously we need to change the scales.
And this is way over budget
so let's change the style here to red,
and give this whole section a name.
And voila, we have our monthly targets now.
If we try this out in the View section,
we could change the date range here,
and we can see for the week what is our budget,
how much did we spend, are we on target here,
is the CPC under our target CPC of 80 cents,
and as we go overboard with the actual acquisition cost
on our orders, which is a bit too high,
so that's something we would need to work on.
Okay, let's go on, we're back here,
and implement our two graphics
for our click versus CPC
and the spent versus the revenue.
This will be a mixed bar and line chart,
so we can choose it up here.
First of all let's choose the metrics.
We had clicks and CPC.
Let's get rid of these metrics,
and that would be clicks and CPC.
And then we choose the scales.
So as a dimension we want to have our date,
and then we just need to style it.
So the first one would be a bar chart,
and the second one would be a line chart.
Right here, now the scales are off
so we choose here that we want to have this,
no, we want to have this on the right,
and now we can see already that it takes shape.
Last but not least we wanna put on again
this title here we have built before,
so let's just mock that
and transfer it.
And again, this should be viewable here
and we can see, when we hover over them,
that what was our CPC and our clicks,
so does that actually correlate,
if we go higher with the CPC, do we get more clicks.
Apparently that's not the case.
Okay, let's go ahead and build our last chart here,
which is our spent versus revenue chart.
This will, again, be a bar chart with a line chart
so we can just copy that over,
and change the metrics around.
I'm gonna change the styling here a bit.
We actually have the spent as a line,
and the revenue as our bars,
and I'm gonna mark them green so we see
okay, this is the money that we made,
and red is the money that we spent.
That looks good.
Now let's put the title on top of that.
The axis here a bit off so, let's see,
let's put this to the right,
and this to the left.
We only need one really, on the left, yeah.
That should be the US dollars here,
and here's the date range.
I would like to have a number so we actually
see the return on ad spent for this week.
So we could just take a panel from here
and change the metrics around.
We want to have the return on ad spent.
We have that right here.
So 0.9.
Hold on, that can actually not quite be
because we have a revenue here of 2.2
and ad spent of 1.9, so it should be actually positive.
Probably I have messed up the metric again.
So let's just go back into our metrics here
and see, that should actually be
revenue divided by ad spent.
So update this field.
This should not be a number but a currency.
No, number's right.
Let's finish this.
And we have an ad spent of 1.2.
Not too good but something that we want to have
on a chart, and this really shows us nicely
how much ad spent we have and how the revenue
went up and down according to the ad spent as well.
Now ideally our revenue would be
much more above this line and the ad spent
would go down accordingly.
So that's not the case but something
we could see in this chart really easily.
Alright, let's go into our last two tables here.
We wanted to have tables
about the campaign overview and the actual ad set.
Whereas I would say this would be better
to make this as an ad set and actually break down
the ads because that's the dimension we would have.
So let me adjust this in our plan right now
and put in our breakdown charts.
So first of all we would again have a title.
This would be our ad set breakdown.
And we would draw in a table here.
Now what data do we want to have in those tables?
As the campaign we want to have actually
the ad set, so let's put that in,
and then we can put in the metrics as well.
We want to know how much clicks
were generated, what conversion rate was,
and then also the orders
and how much money was made.
So we have that now all in one table.
We can adjust a bit of the rows here.
Could even put in more of these metrics.
Also adjust a bit of the styling.
So if you wanted to have some styling in the rows,
you can adjust that here.
Okay.
And so we have a nice ad breakdown
from the ad set basis, so which ad set
performed well and where do we have to adjust.
The same we'll be doing with the ads.
So let's copy over this,
put that into place as well.
Now here we'll actually have ad breakdown.
But with not the ad set, but the actual ad name.
Okay, so these are much more but again,
we can see the conversion rate, the clicks, and so on.
If you wanted to reorder these
'cause actually you wanna see the clicks first,
you can always do it by dragging and dropping that.
So here we go, we have the clicks,
conversion rate, CPO, revenue.
Now, if you want to have a little bit of analysis
in here, you can actually put in some indicators.
For example, if you wanted to know
what is a good conversion rate,
or, let's say, if you wanted to have
instead of the conversion rate actually the CTR,
let's get rid of that and type in our CTR.
Move that upwards.
So we have CTR, the clicks, the CPO and the revenue.
We wanted to know what is good CTR.
We can actually mock that up in our dataset here.
So for the first column,
that's actually the second column,
the first column, we wanted to have a heat map.
So this would mark it up with our default color.
Since the CTR should be something positive,
I would go with green.
To compare that you actually need to have
the impressions as well, so let's put that in.
So here we have impressions that would need to go up.
Alright.
The impressions should not be heat mapped,
but actually the second column here.
Alright.
Let's go with the green color
to indicate positiveness.
And then, we can also put in some
bar charts, as you see in here, bar or pub.
Let's do this for the revenue, so our last column.
A little bit of a comparison.
We can also show the numbers if you choose
or if you have more space here.
But this would give you a quick overview
on what ad is performing well and makes also
a lot of money, and that way you can also
dig deeper into the ad campaign.
This should also work if you go to our view here,
with our actual campaign picker.
So if you only wanted to look at one campaign
and only go on Only, this will change
all the numbers on the whole dashboard
and, hopefully, also our ad set breakdown here,
so we would have that data only on the ad set.
And we could obviously change this around.
Now this is all dependent on the target audience
and how they are using the dashboard.
If they go through and say
I want to dig deeper into my campaign,
we would break it down in this way.
Okay, that's it with the data set up.
Now, we can change a bit around, make this
a bit nicer, so let me do this really quickly.
And we have our completed dashboard.
Now we'll give this all a name.
Share it to the people you want to share it with.
And you are now ready to view this on your account.
(upbeat music)
Alright, so we have made it and now we have
our dashboard ready, outlined.
Isn't that a good feeling?
If you wanna take a look at the finished product
that we have just produced, then you can head over
to MeasureSchool.com/facebookdashboard.
We also have that link up in the description below
and we'll send you a link where you can access that,
copy it, and then implement your own data.
Now in the next lesson we are actually
gonna answer your questions.
So please leave them in the comments below.
We're also gonna recap the course and I'm gonna
give you some more resources to dig into.
Now, if you haven't yet, then you can
consider subscribing right over there
because we'll bring you new videos
just like this one every week.
My name's Julian.
See you in the next lesson!
Không có nhận xét nào:
Đăng nhận xét