small business sales report google data studio

How to Build a Sales Report for your Small Business in Google Data Studio

Small businesses are acquiring treasure troves of data.  Google Data Studio is a fantastic tool that makes it easy for small businesses to visualise this data, discover insights and make informed business decisions.

In this article I’ll show you how you can create a report to analyze the sales from your small business.  You can access the finished Google Data Studio report here, it is a demo Restaurant Sales report

Setup difficulty: Medium

Reading time: 10 minutes

Quick links: 

Who should use this Data Studio report?

This report is intended for those who want a more efficient, visually interesting way of monitoring their sales and taking a look at the data.

Instead of static reports on PDF or Powerpoint, Data Studio allows you to send live, interactive reports to co-workers. And if you still prefer static reports, Data Studio has a built in function to save the report as a PDF, as well as to schedule email updates!

And while the sales data in this report may not be in the exact same format you use, the same principles of how to connect and display your data apply.

Introduction

While Data Studio is commonly used to visualize information from connectors, such as Google Analytics and Google Search Console, there is a wide variety of information that can be displayed using the platform.

Because Data Studio is free to use, and relatively intuitive to use it is the perfect match for small business owners who may want to explore their data but don’t require (or cannot afford) complex visualization software.

In this blog post I’ll give you an example of how you can visualize sales and revenue using Google Sheets and Google Data Studio.

Looking at the Data

Google Data Studio connects with a multitude of connectors. These include products from Google’s marketing platform (Google Analytics, Google Search Console, Google Ads), YouTube Analytics, Twitter and Facebook Analytics (through third-party software) and more.

Data Studio can also connect directly to Databases, with MySQL and PostgreSQL connectors supported.

However, one of the most versatile ways of adding data to Google Data Studio is using Google Sheets.

Google Sheets allows you to add any type of data to Data Studio. 

n this blog post I’ll go through a step by step process showing how you can add Sales Data to Data Studio.

I’ll use data from a hypothetical restaurant to give an example of how you can turn data into attractive, easily updated and easily shareable reports. 

Here’s a link to the Google Sheet with our demo sales data.

Formatting our Data Sources

Let’s imagine that our restaurant business lists all its sales very simply.

Our Data Source: A Basic Sale Sheet

DateSales CodeNumber SoldTakeaway / Sit Down
1 January 2019S_B_0111takeaway
1 January 2019S_B_021sit down
1 January 2019S_B_0312takeaway
1 January 2019S_P_010takeaway
1 January 2019S_P_026sit down

They list the date, the Sales Code and the Number of Items sold. They also list whether or not the order was Takeaway or SitDown.

However, there are more details we’d like to find out about each of the items sold.

To  add these details, we’ll need to create a second sheet in Google Sheets.

Sales CodeItemTypeMeat, Vegetarian, VeganPrice
S_B_01BurgerBeef BurgerMeat10
S_B_02BurgerChicken BurgerMeat10
S_B_03BurgerVegan BurgerVegan10
S_P_01PizzaMargarita PizzaVegetarian12
S_P_02PizzaPepperoni PizzaMeat14
S_P_03PizzaTomato and Basil PizzaVegetarian14
S_S_01SaladGrilled Chicken SaladMeat10
S_S_02SaladCouscous saladVegan9
S_S_03SaladGreek SaladVegetarian9
S_D_01DessertChocolate CakeVegan6
S_D_02DessertCheese CakeVegetarian5
S_D_03DessertIce CreamVegetarian4

So we have our Sales sheet, with just the basic information. And we have our list of items with more detailed information.

We could combine these two data sets in Google Data Studio, using the Blend Data function. But I prefer the method of combining the data in Google Sheets.

To do this we will use some Google Sheets formulas. If you have ever used Microsoft Excel before then some of these formulas will be familiar to you.

The function we use is VLOOKUP.

Without getting into too much detail, VLOOKUP (also known as Vertical Lookup) allows you to search another table for details that match a cell in your table.

A standard VLOOKUP formula goes like this;

=VLOOKUP(The item you’re searching for, the range of cells, the column number, whether you’re looking for an exact match or not)

We can use the VLOOKUP function as part of a formula, as shown below.


=VLOOKUP($B2,’Menu Items’!$A$1:$F$13,2,FALSE)

This formula lets us bring in information from the Menu sheet to the Sales sheet. It means that just by having the Sales Code, we can add in lots of extra information.

For example, we can add in what type of food it was, whether it was vegetarian or vegan, how much revenue it brought in, and add in a link to an image of the food. This will come in handy when creating the Data Studio report.

Adding our data to Google Data Studio

After we’ve built our data sheet, we can now move onto the fun part of creating our Data Studio report!

Since we used the VLOOKUP formula to bring across data to our Sales sheet, we only need to connect the Sales sheet to Data Studio.

We can Choose Google Sheets as our Connector.

Then choose the Sales Sheet from the Spreadsheet.

We’re then able to see the fields.

Because colourful photos make everything better, we will create a Photo field.

We can click on ‘Add a Field’ in the top right corner of the screen.

And then we can use the IMAGE function to create an image field.

We just need to choose our existing Image URL field in the formula. It’s also possible to add in Alternative Text info, by adding in the Item field after a comma.

Now that we’ve created our Image field we are good to go!

Building our tables and charts in Data Studio

Let’s go through a breakdown of some of the main charts on the page.

One thing we can see on the report is an image that changes depending on which item on the menu you select. As shown below if we click on an item its picture appears on the right.

To create the list of items table, we can create a table with the Dimension being “Type” (as in the type of food ordered) and the Metrics being the Number of items sold (Number sold) and Revenue. It is also important with this table to make sure we have the “Apply Filter” checkbox ticked under “Interactions”. This will allow us to filter the image table on the right.

We now need to create the Image table, which will show a single image of the food item selected. We can do this by creating a table with just “Photo” as our Dimension and no metrics at all. We also need to resize the table so that it displays just one image at a time.

The other charts in this report are fairly simple to set up, and we can go through a brief summary of each to see how it’s made.

For the Pie Chart showing a breakdown of Revenue of each type of food sold, we can setup the Dimension as Type and the Metric as Revenue. We can also enable filtering (by selecting the checkbox labeled “Apply Filter” under Interactions.


In terms of the style of the Pie Chart we can use one colour to keep it in the style of the rest of the report.

The time series line graph showing the number of items sold compared to the previous month is fairly simple to set up. Simply create a time series chart with the Time Dimension being Date and the Metric being Number Sold. 

In order to see a comparison to the previous period simply select click on Comparison rate range and choose Previous period. 

Next on the report we have three pie charts showing a breakdown of orders by various Dimensions. You’ll notice that I’ve decided to colour these pie charts with specific colours.

In order to choose which colours you want to appear in these charts open up the Style tab of the chart. Then click on “Manage dimension value colours”. You then have the open of changing the values to specific colours. 

For example, we can decide what colour “Pizza” should be. Our pie chart displaying what percentage of orders were “Pizza” then displays this colour.

The final chart on our report is a summary of revenue by month. 

To get this time series to display the revenue by Month (and not by the default Date) click on the Edit Option for the Date dimension. Then click “Show as” to display it as Month.

Summary

And that is how you can build a Sales dashboard for your small business! It’s important to note that the data you have from your CRM or from your point of sale might be in a different format than the examples given in this post.

However, if you’re able to spend some time putting your data into a format that Data Studio can understand, you’ll be able to create effective reports and dashboards to help understand your business’ performance. 

How to Track your Google Data Studio Report Usage with Google Analytics

Data Studio Inception! – Here’s a free report and our guide on how to set up Google Analytics tracking for your Google Data Studio Reports.

In this article, we’re going to show you how to setup GA tracking so that you can track the usage of each of your GDS reports.  Not only that, we will create a new GDS template that can display this data in an interactive report. So you will be tracking GDS templates with a GDS template!

Who should implement this?

If you’ve spent a lot of time setting up a dashboard in GDS it’s only natural that your going to want to know if people are using it.  So everyone should implement it!

The real usefulness is the ability to track multiple templates in one dashboard.  This is perfect for:

  1. A company like ours, marketing multiple dashboards and looking to understand performance
  2. Agencies creating multiple template & reports for clients, using this tracking you can see how your clients are utilizing your reports.
  3. In-house marketing teams that are generating reports for the rest of the company.  It’s insightful to see which areas of the company are making use of your dashboards.

Setup Google Analytics

The first step to setting the tracking is to create a Google Analytics account.  And then a new property within this account. Choose the website option and the url should be: datastudio.google.com

Under the ‘property settings’ GA should now provide you with a tracking ID

Add Tracking ID into Templates

This step is straightforward.  Navigate to the GDS template that you want to track.  Once you are in edit mode. Select file >> report settings.

This will open the right hand side bar, which has a section ‘Google Analytics Tracking ID’.  paste your tracking ID in there.

Repeat the same steps for all of the template you want to track using this same ID.

Note: using the same ID in all templates will allow you to see the data for all of your templates in one dashboard.  You can later separate them using segments or GA view filters, therefore this is recommended. If you did want to keep them separate you should create a new GA profile and new tracking ID for each template.

Create a GDS template – (already done for you)!

We’ve already created a GDS template to allow you to track multiple templates here.  All you need to do is make a copy of the template and connect it to your GA account and GA profile you used in the steps above.   Note that the demo template is connected to dummy GA data.

On the top right of the screen make a copy of the report and then follow the prompts to connect to your own Google Analytics account.

Charts in the report

The following is a quick overview of the filters and charts available in the report.

In the top section of the report there are 2 filters.  The first filter ‘page title’, allows you to filter by pages.  Pages in this sense is the actual report names. This filter allows you to filter all of the metrics and graphs in the report to focus on one or several individual templates.

Session Trends shows the number of sessions and users per day visiting your template(s).

Channel Sources show which source in general the traffic has come from.

Top Templates, lists the top templates by sessions and can also be sorted by the average session duration.  This is best used when no page filter is applied.

Traffic sources shows more detailed information then channel sources.  It shows source and medium combinations of where the traffic to the templates has originated from.

Devices shows which devices people are using to view the templates

Country shows where your users are viewing from.

June GDS Updates – New Home Dashboard & More

If you use Data Studio, there are two new features that you might be interested in knowing about!

Multiple Dimensions on Pivot Tables

Up until this point, you could only add two Dimensions to a pivot table. For example, below we have our two dimensions as Country and Device Category.

A new feature is being able to add multiple Dimensions to the Pivot Table. For example, we can now have rows for Country and City. We can also have columns for Device Category and Channel grouping.

This gives you much more flexibility to dig down into the nitty gritty of your data. 

New Home page

You might have noticed that Data Studio got a new home page layout.

The new design is arguably simpler, cleaner and easier to use. 

New Text Functions

Google Data Studio has added five new text functions for you to use with custom fields. 

The next text functions are;

  • CONTAINS_TEXT — Returns true if the specified text is found in the field or expression, otherwise returns false.
  • STARTS_WITH — Returns true if the field or expression begins with the specified text, otherwise returns false.
  • ENDS_WITH — Returns true if the field or expression ends with the specified text, otherwise returns false.
  • LEFT_TEXT — Returns a number of characters from the beginning of a specified string.
  • RIGHT_TEXT — Returns a number of characters from the end of a specified string.

You might be wondering how you could these, so here’s practical example. Let’s say you’re putting together a dashboard using your Search Console data. But instead of seeing all the individual queries you want to group your queries by topic. 

You can use the “Contains_Text” function to search for specific words and cagegorize them. You can click ‘Add a Field’ in Data Studio and create a CASE statement to create a new dimension.


See an example below;

We’ve now created a new dimension called Query Category using a CASE statement with the Contains_Text function.

Below is the type of data we can get and display in our Data Studio report.

Previously, if you wanted to search for types of text in a CASE statement you’d have to use Regular Expressions (Regex) which can get complicated. These new functions make that a whole lot simpler and quicker.

Hope you found this update useful and visit Data Studio Templates for more updates!

GDS June Updaate

June 2019 Google Data Studio Update – Improved Charts & Line Functionality

There have been several new features launched for Google Data Studio in June 2019. I’ll give you a breakdown of what these are and how you could use them to improve your Data Studio reporting!

1. Improved method of adding charts to a report

This is a subtle but significant improvement to the way Users can add and interact with charts in Data Studio.

Previously a new chart would appear at the top of the report, and the User would need to drag it into position.

Now when you add any element (including date ranges, filters, charts tables and other design elements) you can choose where it should go before it is added to your report.

2. Changing the Colour of Chart Headers

Chart Headers are a recent addition to Data Studio. One of the most useful features of Chart Headers is that they allow you to “Drill Down” or “Drill Up” into your data.

To allow drilling down into your Data, you’d need to enable the Hierarchy feature on your chart’s Data tab. In this case we’ve chosen the hierarchy to be;

– Continent
– Sub Continent
– Country
– City

The latest addition to the Chart Header feature is to change its colour. It’s a small change, but any change that allows more customization is a welcome one!

3. Adding Lines to your Report

Perhaps the most significant of the three recent changes was the introduction of a Line feature to Data Studio.

Up until now users have been able to add square and circle shapes to their reports, but not a line. Lines come in three styles; Linear, Elbow and Curved Elbow.

You can change and manipulate the direction and angle of the line, as well as adding different types of pointers.

These arrows have several practical applications, such as labeling parts of your data.

In a way, they allow you to create annotations to describe patterns or anomalies in your data.

I hope you founds it useful to read up on these new features, and enjoy adding them to your reports!

How to Use Calculated Fields in Google Data Studio

In this article, we are going to explain how to use calculated fields in Google Data Studio.

A ‘field’  is a specific metric used in a data visualisation report. A metric is a measure used to quantitatively track or assess progress, performance, quality and more.

In GDS, once a data source has been added, all of the fields within that data source are available to use in a report. By using ‘calculated fields’, it is possible to create new, custom metrics which derive from your data source. Calculated fields are essentially user-generated metrics.

So why would you want to create new custom metrics? Well, sometimes the pre-existing metrics are just not sufficient. Calculated fields are useful if you cannot answer certain questions with the available fields associated with your data. Some examples from Google Analytics could be Events per user which is total Events / Users or Product View per Session which is Product Detail Views / Sessions

In this article, we are going to explain the process of creating a new metric in 10 steps.

1) Calculated Fields

For this example, we are looking at two metrics, tax rates and product prices:

We want to be able to calculate the amount of tax that an individual customer would pay when they purchase a specific product. To work this out, the below calculation is used:

‘Tax paid by the customer’ is the new metric that we want to use in our data visualisations.

2) GDS supports basic math functions

Addition (+), Subtraction (-), Division (/) and Multiplication (*). The operators (and formulas) used in GDS are the same as Microsoft Excel or Google Sheets:

3) Creating a new Metric

We are going to create a new metric called ‘Tax paid by customer’ using the data from our example data source. To begin, it is necessary to export the spreadsheet into GDS. In order to connect Google Sheets to GDS, a new data source needs to be created by selecting the ‘+’ in the bottom right-hand corner of the page in GDS.

4) Add Data Connections

You can revisit our previous article on how to connect Google Sheets to Google Data Studio.

5) Editing your data

You have the choice to edit the data in your existing report before exporting it to GDS, or it is also possible to edit your data source in the GDS home screen after you have exported it.

In the GDS home screen you will see a list of your metrics in blue, and your dimensions in green. To create a new metric select the ‘+ Add Field’ on the right-hand side of the screen.

6) Creating a new metric

To start, enter a name for your metric. For this example we are using ‘Tax paid by customer’. GDS will then assign a Unique ID to your new metric – this does not need to be changed.

7) Calculating your metric using formulas

The next step is to input the formula for your new metric in the formula field:

When entering a new formula, GDS generates suggested metrics. Once you are finished inputting your formula, click save.

8) Check the new formula is saved

Your new metric will then display in your list highlighted in blue, with a small icon ‘fx’ indicating that it’s a calculating field.

9) Select the data format type

Next, select the data type for this new field. For this example, it is ‘currency’. Once complete, select ‘done’.

Your new metric will then be available to add to your data visualisation.

10) Duplicating a metric

Once you have created a new calculated metric, you have the ability to use it when creating additional ones. Below we have created another custom metric, ‘Average price + tax’, by referencing ‘Tax Paid by Customer’.

You can create custom metrics for any type of data source to assist you in enhancing your existing datasets. This is a great way to complement your dashboard.

We hope this article was helpful and has assisted you in improving your knowledge in Google data studio.

Using Regex Expressions in Google Data Studio Dashboards

Google Data Studio is a great data visualization tool. It can be used to visualize data from Google Analytics, YouTube, Search Console, raw data in spreadsheets and much more.

GDS does, however, have limitations. For example, say you wanted to analyze your search keywords separated into long-tail and short-tail keywords. There’s no inbuilt filter to separate by  the number of words in a phrase. Using simple regex formulas can overcome many of these limitations and make GDS a far more powerful analysis tool.

In this article, I’m going to show you how to use regex/regular expressions to filter between long-tail and short-tail keywords. This is not an exhaustive  tutorial on regex expressions. Rather, I want to provide a concrete example of how using regex can stretch the capabilities of Google Data Studio reporting.

Data Studio offers many more functions other than regex, and you can see them here.

What is a Regular Expression?

A regular expression, also known as regex or regexp, is a special text string that describes a search pattern. You may have seen and used wildcard characters like “*.txt” to find all files ending in “.txt”.

Think of regular expressions as wildcards on steroids. There are many great tutorials on Regex expressions online.

Worked Example: Separating Short-Tail Vs. Long-Tail Keywords

The focal point of this case study is two questions which address the performance of long-tail and short-tail keywords.. The first question in the dashboard shown above addresses the number of impressions and the cost of long-tail keywords. The second question addresses the impressions and the cost of short-tail keywords.

According to Vertical Response, a short-tail keyword contains one or two words, while a long-tail keyword has three or more words. This is the criteria that we are going to use to define the regex expressions in this article. Also, search keywords may contain numbers, so we will include those as well.

There are many regex testing tools which can help you create and test regex expressions. For this article, we have used: https://regex101.com/.

When using regex101, first, you need to write the regex expression in the field labelled “Regular Expression”. Next, you need to write the content that you want to test in the field labelled “Test String”.

Short Tail Keywords

The regex expression: ^([a-zA-Z\d]+\s?\b){1,2}$

Let’s dissect the regex expression above to see how it works:

  1. ^ – Tells the regex expression to start at the beginning of the line.
  2. [a-zA-Z\d] – Means that we want all lowercase letters (a-z), all uppercase letters (A-Z) and numbers (\d)
  3. +\s – Means that the characters must be followed by whitespace.
  4. ? – Indicates that there can be one or more words before the current space.
  5. \b – Word boundary means that after the whitespace there should be something.
  6. {1,2} – This pattern should repeat once or twice. In this case, we are simply saying that for this regex expression to pass, there must be one or two words and nothing more.
  7. $ – Asserts position at the end of the line. This means that there should be nothing after the second word if this regex expression is to pass.

Let’s test our Regex expression to see if it works.

When you enter a single word, the short-tail keyword regex expression receives a match. This is indicated by the now highlighted test word – “hello,” as well as the text above the “Regular Expression” field that reads “1 match, 10 steps.”

When a second word is added, the regex expression again receives a match. This is because we specified that the pattern should repeat one or two times for the regex expression to pass.

When a third word is entered in the text string, the regex expression returns false and therefore no highlighted words appear. You will also see that the field above “Regular Expression” reads “no match.”

Long Tail Keywords

The regex expression: ^([a-zA-Z\d]+\s?\b){3,}$

Again let’s dissect the regex expression above to see how it works:

  1. ^ – Tells the regex expression to start at the beginning of the line.
  2. [a-zA-Z\d] – Means that we want all lowercase letters (a-z), all uppercase letters (A-Z) and numbers (\d).
  3. +\s – Means that our characters must be followed by whitespace.
  4. ? – Indicates that there can be one or more words before the current space.
  5. \b – Word boundary. This means that after the whitespace there should be something.
  6. {3, } – This pattern should be repeated at least three times to get a match. If we try only one or two words, we won’t get a match.
  7. $ – Asserts position at the end of the line. Since we haven’t specified a maximum number as the second argument in {3, }, this expression returns true for any keyword with a length of at least 3 words.

Let’s test our Regex expression to see if it works.

As you can see, entering 3 words in the test string returns a match. If an indefinite number of words are added, we’ll still get a match. The only condition is that there are three or more words in the sequence.

You can see that if the test string is made up of only two words, the regex test fails and no highlighted words appear.  You can also see “no match” above the field labelled “Regular Expression.”

Inputting Short-Tail Regex in Data Studio

Now that we have a working regex expression, we need to use it in the Google Data Studio dashboard. Once you have opened the dashboard, select the preferred chart or table that you want to apply the short-tail keyword length filter to.

Once you have selected the correct chart, scroll down through the panel on the right-hand side of the page until you see the “Add a Filter” button.

Once you have selected “Add a Filter” you will be taken to a list of options to use on your dashboard. If there are existing filters in your dashboard, they will be displayed in the list. In order to create a new filter, which in this example we want to do, you need to select the button at the very bottom of the list – “Create a Filter”.

In order to create the regex expression, you need to use the pull-up dialogue box that appears. You first need to input the name of your regex expression in the “name” value box. It is important to note that the fields need to be set to “Include” and “Search Keyword”.

Next, a condition needs to be provided. From the list, select “RegExp Match” as shown below.

Finally, the short-tail regex expression needs to be added, as shown in the image below.

Inputting Long-Tail Regex Tail Regex in Data Studio

Now that we have a working long-tail keyword regex expression, we need to use it in Google Data Studio dashboard. Once you have opened the dashboard, select the preferred chart/table that you want to apply the keyword length filter to.

Once you have selected the correct chart, scroll down through the panel on the right-hand side of the page until you come across the “Add a Filter” button.

Once you have selected the “Add Filter” button, you will be taken to a list of options to use on your dashboard. If you already have existing filters in your dashboard, they will be displayed on the list. If you want to create a new filter, as we do in this example, you need to select the button at the very bottom of the list – “Create a Filter”.

In order to create the regex expression, you will need to use the pull up dialogue box. First, it is necessary to name your regex. You can enter “Long-Tail Filter” into the “name” value box. It is important to note that the fields need to be set to “include” and  “Search Keyword”.

Next, a condition needs to be provided. From the list, select “RegExp Match” as shown below.

Finally, the long-tail regex expression needs to be added, as shown in the image below.

The Result: Tables and Graphs Filtered by Word Length

As  shown in the above screenshot, with Google Data Studio we can use our regex expressions to correctly filter search keywords. Search keywords with only one or two words have been identified as short-tail keywords, and those with three or more words as long tail keywords.

Conclusion

Although Google Data Studio has limitations, with the correct knowledge and tools it is possible to add versatility to your reporting and use the program to gain a more in-depth insight into your data.

At https://datastudiotemplates.com/, we create custom dashboards designed to visualize all kinds of marketing data. We have everything from YouTube, Google Analytics, Facebook Ads, LinkedIn Ads, Google Ads and a combination of all these and more. Check out our templates here.

The Best Free Data Studio Templates of 2019

Datastudiotemplates.com is one of the only sites on the web dedicated to providing great templates for Google Data Studio. We devote time and resources to researching and building templates that we feel will help you understand, analyse and monitor your marketing campaigns more effectively.

In this article, we have reviewed seven of the best free templates that are available online.  The free dashboard templates that we have reviewed allow for a quick analysis of performance and enable you to see and interact with your data in ways that are most helpful to you.

Aside from some great free templates that we’ve found, we also provide a number of premium paid templates which cover SEO, SEM, Social Media, E-commerce and Traffic.

Connecting GDS’ Dashboards to Data Sources

It is possible to connect a GDS dashboard to a huge range of third-party data sources as well as other Google platforms. GDS provides free connectors, but other premium or community connectors have also been built because of certain limitations associated with the default connectors. The templates we have reviewed in this article use only GDS’ default connectors and analyse data from Google accounts, making them 100% free to use.

  • You can access a list of all the connectors GDS has on offer here
  • You can access a list of premium connectors here.

In no particular order, here is our pick of the best free Data Studio Templates available right now.

1) Paid Channel Mix

The Paid Channel Mix dashboard is simple to read and understand and makes it easy to compare the performance of all your paid marketing channels. This dashboard displays an overview of your top-level data from Adwords, Facebook, Twitter and Bing, enabling you to compare your results at a glance.

Within this dashboard, you can take full control of your date range as well as being able to select your metrics. This dashboard includes handy features like trending charts and scorecards.

View Template: The Paid Channel Mix  (Created by Supermetrics)

Developed By: https://supermetrics.com/product/data-studio

2) Adwords 1 Page Report

This 1-page report provides a useful overview of how your Google Ads have performed over a certain date range. It displays the clicks, cost, CPC and CTR of each campaign in clear, eye-catching scorecards. It also enables an insight into the demographics of your audience as well as the profitability of your keywords. Its uncomplicated design allows for a quick and easy analysis which makes this dashboard effective for companies looking to review their Google Ads performance on a daily basis.

View template: Adwords 1 page report

Developed by: datastudiotemplates.com

3) Blog Content Performance

This interactive dashboard provides an insight into the performance of your blog content. By revealing the blog content that has generated the most traffic to your website, it makes it easy to identify the topics and authors that resonate the most with your users and drive the most value. This single page report provides a clear overview of your content’s performance which helps when developing a content strategy.

The Blog Content Dashboard also enables you to understand more about your content cohorts by changing the date range and comparing different topics within the same time frame, or the same topic over a different period of time. You can also compare categories of content which have the same topic in the blog’s title.

View template: Blog Content template

Developed by Alberto Grande head of marketing at X-Team

4) Youtube Channel Report

This dashboard displays your Youtube metrics in a visual presentation which helps you understand how your videos are performing. The Youtube dashboard showcases your best performing content and highlights the total number of views, user’s average watching time, number of subscriptions and videos shared. This dashboard is easy to use and allows for a quick but effective analysis of your Youtube content and the videos that perform well with your users.

Use template: Youtube Google data studio template

View Template: Youtube

Developed by: Google Data Studio Team

5) Content Performance Report

This Data Studio template provides a 1-page report on the performance of your content. The uncomplicated top-level scorecards clearly display the following metrics; Users, Sessions, Page Views, Bounce Rate and Av. Session Duration. The report also breaks down your authors and categories of your content by traffic (sessions, bounce rate and av.session duration). This dashboard’s design is vibrant and coherent and it is simple to set up.

View template: Content performance

Developed by: datastudiotemplates.com

6) Website Performance Overview

This dashboard provides a very easy to read overview of your website’s performance, on one single page. It includes all the necessary Key Performance Indicators, as well as useful scorecards that display total sales, revenue, average order value and conversion rate. The design is simple and as well as providing an effective overview of performance and traffic sources, some aspects of this dashboard enable you to gain a more in-depth understanding of your data.

Use Template: Website performance overview

Developed by: Alligator Interactive

7) Dashboard for Non Profit Web Data

This comprehensive report provides all the tools to enable you to effectively analyse the performance of your not for profit campaigns. It is functional and clear and includes an overview, breakdown of your audience, information on your Adwords and more. It also provides helpful instructions for connecting your own data.  

Use template: Dashboard for non profit web data

Developed by: https://www.wholewhale.com/

Free templates are a great way to start using GDS to better understand your data and create professional level reports. We also offer a range or premium paid templates which will enable you to create custom, visually compelling views of your data. You can access our premium templates here.

How to Assign User Permissions within Google Analytics in Order to Connect to Google Data Studio

Within Google Analytics, user permissions govern what users can do and the data that they can access. In this article, we are going to explore the structure of Google Analytics, user permissions and their privileges and how to assign the necessary permissions to enable a user to connect GA to GDS.

This report is a continuation of our previous report, ‘How to: Connect Google Analytics to Google Data Studio.

This report consists of 4 sections:

  1. The Structure of Google Analytics
  2. Google Analytics’ Hierarchy and User Permissions
  3. User Permissions
  4. Assigning User Permissions

Google Analytics allows only the account owner to assign user permissions at 3 different levels. These levels reflect the hierarchical structure of every organisation within Google Analytics. An organisation is the collection of products and users, and every Google Analytics organisation is made up of:

  1. An account – the access point to Google Analytics. There can be multiple accounts within an organisation.
  2. A property – a website, devise or mobile application. An account can contain multiple properties.
  3. A view – an access point for reports and each property can contain multiple views.

Users can be added to Google Analytics at an account, property or view level and this determines their user permissions.

At the account level, the user is assigned the greatest permissions and has access to every property and view in that account.

At the property level, users have access to every view within that property, but that access is limited to one specific property.

At the view level, users only have access to that specific view within the individual property.

There are 4 main user permissions within Google Analytics and therefore 4 levels of access:

  1. Manage Users
  2. Edit Permission
  3. Collaborate Permission
  4. Read & Analyze Permission

1) Manage Users (account level access)

In order to assign a user the ability to manage other users within an organisation, they need to be added at an account level. This permission enables users to add or delete other users, as well as assign permissions.

*This permission does not include privileges that come with ‘edit’ or ‘collaborate’.

2) Edit Permission (account, property or view level access)

A user can be assigned editing permission at any level within the organisation but it is important to note that this user permission entails different privileges at each level. For example, a user with edit permission at an account level can manage account settings, manage filters, create new properties and more.

A user with edit permissions at a property level can import data, create new views, customise tracking and more but they cannot do anything that a user with the same permission at an account level can do.

*This permission includes privileges that come with ‘collaborate’ and ‘read & analyze’ but not ‘manage users’.

3) Collaborate Permission (account, property or view level access)

Users that are assigned this permission have the ability to create, delete and share personal assets as well as collaborate on them.

*This permission includes privileges that come with ‘reading & analyze’ but not ‘edit’ or ‘manager users’.

4) Read & Analyze (account, property or view level access)

This permission is assigned to users that only need to view data. It allows users to create reports, dashboards and customs segments but not edit data in any way.  

*This permission doesn’t include any privileges that come with the other user permissions.

In order to connect Google Analytics with GDS, a user needs to be assigned at least the Read & Analyze permission.

For this example, we are going to add a new user at the view level to assign them the read & analyze permission.

1) The first step is to select the ‘admin’ cog on the bottom left-hand side of the screen in the Google Analytics dashboard.

Next, select ‘user management’ in the View level.

2) Then, click on the ‘+’ sign in the top right-hand corner of the screen and input the new user’s email address.

3) You will then be prompted to select whether or not you would like the new user to be notified via email. Below this, the 4 user permissions will display.

4) Select the check box for ‘read & analyze’.

In order to modify an existing user’s permissions, select the desired user from the list displayed in your user management panel.

Once a user has been assigned the appropriate permissions, they are able to create a new data source. This information has been covered in our previous article – ‘How to: Connect Google Analytics to Google Data Studio.

How to connect Google Sheets to Google Data Studio

In this article, we are going to learn how to connect Google Sheets to Google Data Studio. We will explain how to correctly set up Google Sheets, before going on to explore how GDS imports information into a report.

There are two main scenarios when connecting Google Sheets to GDS may be useful:

  1. When there is no connector available to pull in data directly from your platform.  An example of this might be offline sales.
  2. When only paid connectors are available, for example, Supermetrics which is a paid connector for platforms like Facebook.  Using Google Sheets allows you to export and import data, allowing you to get the data into GDS for free, but it’s a more involved process.

We recommend reviewing the previous articles in our ‘how to’ series to give you a basic understanding of Google Data Studio.

This article consists of 2 sections:

Here are 15 steps to follow in order to create a report with GDS using imported data from Google Sheets.

Preparing Data in Google Sheets

1) It is possible to power a GDS report with a range of different data connectors, one of which is Google sheets – Google’s version of Excel.

2) In order to create a Google Sheet, click on this link and sign into your Google account.

3) Google Sheets is a web-based application which allows users to create and edit data stored in a spreadsheet, which is shared live online.

To ensure that GDS imports the information from Google sheets correctly, it is necessary to display the data in a specific format. Below you will find an example of a Google Sheets table showing results from a merchandise store.

The data in Google Sheets needs to be stored in a table format. In the first row of the table, each column needs to contain a header.

4) In this example, we have used the following headers; date, product type, country the product was sold in, average price, product revenue and tax rate. We are working with 4 main types of data: date, text, currency value, and percentage. It’s necessary to check that each type of data is correctly formatted.

Date example:

  • The date column must be formatted correctly by selecting the ‘format’ drop-down menu and then selecting ‘number’ and then ‘date’. Dates must be in the following format: dd/mm/yyyy.

Text example:

  • All data displayed as text is aligned to the left.

Currency example:

  • Data displayed as currency needs to be formatted correctly by selecting the column, clicking on the ‘format’ drop-down menu and selecting ‘currencies’.

Percentage example:

  • Data displayed as a percentage must also be formatted correctly by following the same steps as above and selecting ‘percentage’.

5) It is important to note that Google Sheets Connector can only connect to one sheet at a time. So, it is necessary to ensure that all the information that needs to be exported to GDS is displayed in a single sheet.

6) GDS cannot import data that is displayed in charts or graphs so it is necessary to ensure that all the information in Google Sheets is presented in a table format.

7) On the table in Google Sheets, a ‘total’ row displaying the sum of each column’s data cannot be included because this will result in double counting. The sum of each column can be added in GDS if it is required.

Connecting Google Sheets to Google Data Studio

8) In order to connect Google Sheets to GDS, it is necessary to create a new data source by selecting the ‘+’ in the bottom right-hand corner of the page in GDS.

The following screen will then appear:

9) Next, select the Google Sheets data connector and authorize the account.

10) Once GDS is able to access the Google Drive folder where the Google Sheet is stored, it can connect to the sheet in one of three ways:

  • Selecting the relevant Google Sheets from a list of saved ones
  • The URL of the spreadsheet
  • Google Drive explorer

11) Select the option that best suits you. It’s important to make sure that the ‘Use first row headers’ checkbox is selected. Finally, click connect.

12)There are two important things to check before connecting the Google Sheet with GDS:

  • Has GDS identified the correct ‘type’ of data for each column in the table on Google Sheets? GDS highlights columns that contain numerical values in blue, and ones that contain texts in green. If GDS doesn’t give you the option to select the data type, this indicates that the data in Google Sheets isn’t in the correct format.
  • Should GDS ‘sum’, ‘average’ or ‘count’ the values in the columns that require these functions?

13) When you select a data visualization from the drop-down menu, you will see from the data tab that the dimensions and metrics are now available to use in your report. Add in a table to confirm that GDS is displaying the correct information with the correct data ‘types’ from Google Sheets.

14) It is possible to build a report by following the same steps outlined in our previous articles. To see a preview of the report, select ‘view’ in the top right-hand corner of the page.

15) Finally, in order to edit any information in the GDS report, amend the Google Sheets accordingly and select the refresh button at the top of the page.

We hope this article was helpful and has assisted you in improving your knowledge in Google data studio. Don’t miss the next article in our ‘how to’ series guide.

Advanced Data Visualisation with Google Data Studio

In this article, we are going to learn how to implement specific data visualizations to allow you to interpret data using charts and tables. It is recommended that you read the previous articles in our ‘how to’ series before moving onto this on:.

In this article, we will be exploring 3 types of charts on Google Data Studio.

Scatter Charts
Bullet Charts
Tables

This article consists of 11 steps to demonstrate how to use these charts to visualize your data in an interactive way.

Scatter Charts

Scatter charts show how one variable is affected by another. This relationship is known as ‘correlation’.

An example of this might be a comparison of clicks per campaign or total spend per day. The position of the individual dots on a scatter chart enables you to see the relationship between two different campaigns or daily expenditures. One dot is plotted along the ‘X’ axis, and the other along the ‘Y’ axis showing you how one variable is affected by another.

1) The first step is to select the ‘scatter chart’ option from the ‘add a chart’ drop-down menu and position it on your report.


For this example, we will explore the relationship between the total number of sessions and the total number of page views.

2) The scatter chart is set to default which displays the total number of sessions on the ‘X’ axis, and total page views on the ‘Y axis’. In order to change these metrics, simply click on the metrics and select one from the list.

3) In order to identify which data points refer to a certain category of information, it is necessary to add in the product category name as a label. To do this, select the ‘style tab’ then click the checkbox ‘show data labels’. Finally, you have the option of changing the colour of the data points.

4) In order to view data from previous weeks, months or even years, switch to view mode and set the date range of your report. The charts are interactive so it is possible to see the data points as you move your cursor over the graphs.

Bullet Charts

5) Bullet charts are typically used to display performance data and display progress towards a certain goal. Ie: Revenue, expenses, volume. Bullet charts can be both vertical and horizontal.To add a bullet chart, simply click ‘add chart’ in the quick links and select ‘bullet’.

6) On the right hand side of the page, there are options to set the metrics that you want to display. The range limits set the lower, middle and upper targets for the metric.

For this example, the bullet chart is set to default which displays the total number of sessions.

It is important to note that the date range needs to be adjusted accordingly.

Below the following values have been set: Target value: 4200, Range 1: 1800, Range 2: 3600, Range 3: 5400

7) It is also possible to change the colour of the metric bar and the range of the bullet chart.

8) To view your bullet chart, select ‘view’ mode in the top right hand corner of your screen.

Tables

A table is used to organize and display information in columns and rows.

9) To add a table with bars, select ‘add chart’ from the dropdown menu in the ‘quicklinks’ and select ‘table with bars’.

On the right-hand side of the page there are options to select different metrics and dimensions. For this example, we have added ‘month of the year’ as an additional dimension and ‘users’ and ‘new users’ as additional metrics.

10) It is possible to view months in descending order in order to display the latest data at the top of the table. It is important to note that the date range needs to be adjusted accordingly.

It is also possible to remove the row numbers from the ‘style tab’.

11) In the ‘style tab’ you will see a list of options for each column in the table. A number, bar or heatmap can be selected. Similar to the bullet chart, it is also possible to set a target value. The target value line will appears once the target goal has been set. It is also possible to change the colour of the bars.

All of the above options can be applied to each column of the table. Select your desired style based on the information you would like to display. In this example, we used the following options: Column 1: Bar, Target value: 950, Column 2: Heatmap, Column 3: Heatmap.