Yuma 4×4

Media and Communications

Facebook Ads Dashboard with Google Data Studio | Lesson 3

Facebook Ads Dashboard with Google Data Studio | Lesson 3

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

33 thoughts on “Facebook Ads Dashboard with Google Data Studio | Lesson 3

  1. 2 Quick Questions… 1. Is it possible to include Audience data from both Facebook and Google? Specifically the list size of custom audiences in FB and G? 2. Is it possible to include Ad copy and/or Ad Images from F and G in data studio? Loving the videos!

  2. Amazing job Julian! Quick question: In this example you pulled data from Facebook Ads and GA. What if i want to pull more data from sources like AppsFlyer how can I do that? Thank you!

  3. Love the tutorial, Julian! Is there any way you could share the Data Studio file as a template (not Google Sheets)?

    Again, awesome job. 🙂

  4. What a great video. Thanks for the help. Do you have any suggestions on where to find and hire someone who can help build these types of reports?

  5. Hi Julian thanks for the course, it means a lot for me.
    If you dont mind, I want to ask is it possible to add monthly target revenue to the dashboard? How can we do that?

  6. Amazing video. I saw also other videos from you and I your are easy, clear and the video quality is top. thanks a lot

  7. CTR = Clicks/Impressions but because you are using date ranges the function AVG() must be used for correct computations

  8. Hey Juliann, is there a way to display the "Pixel" count of a business' Facebook Pixel? And/or the Google Analytics count?
    I'm trying to display in Google Data Studio, a running count of the number of Pixeled Users there are for a client account, is that possible??

  9. Hi Julian, Thanks very much for the demo it's very useful…I had a question on the facebook insights connector – how would I go about combining facebook insight data with GA metrics? I'm looking to join the data together somehow, but I cant find a relevant connection between the two. I'm wanting to combine GA bounce and goal metrics with the facebook insights info for certain posts. The 'post url' and 'post link' metrics (from the facebook connector) dont actually give the shared link to the website, otherwise I could match that with the landing page in GA.

  10. my metrics doesnt show comarison date range, when i click on it, it just show None and Custom, in some of my dashboard it shows some , is there any date format issue? i applied dates as suggested by you GDS DATE YYYYMMDD and GDS Month YYYYMM ,please advise if possible why its not showing other options like Last Year , Quaters,Last Month etc

  11. Guy really, you deserve a statue. This is the best tutorial ever. Give me your paypal and I'll send you some $ kudo's

Leave comment

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