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. 

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!

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.