Finding Out What Travel Company Needs

First, we needed to find out more about our client’s needs. As a popular travel company, they wanted us to take a look at who their customers were.

Gathering the Data

The next step was to gather the data. We sent about ten thousand emails to our client’s customers and asked them to fill out a survey using SurveyMonkey.

We also had free text questions, a text box allowing them to type information such as if they thought something was missing from the website, allowing more open responses to some key questions.

Once all submissions were received (about 5,000 surveys), we’re ready to prepare the data for the report.

Preparing the Data

The next step was to prepare the data before we could feed it into Data Studio. SurveyMonkey is able to export the results into an xls file, which is a format we could easily import into Google Sheets. After uploading the results to Google Sheets, we started cleaning up the data.

As discussed in Chapter 2, clean up entails ensuring that nothing in the data set is invalid. For instance, despite us using the company’s mailing list, presumably of people who wish to be contact about the service, we still received “junk” responses from people who did not want to be bothered. We also had to remove anything that is considered a null, or blank, response. We also needed to make sure that all columns have unique names. Don’t worry – every data set has issues.

In this case, we were dealing with a relatively small data set of about 5,000 surveys, and we can pull it directly from Google Sheets into Data Studio. If we had a larger data set, we could upload it into Google BigQuery first and pull it into Data Studio in a similar way.

After we cleaned up the data, we were then ready to pull it into Data Studio. Make sure you have access to the Google Sheet in the same account and the file name is appropriate.

In Data Studio, start by creating a report. It will then ask you to add a data source to the report. Simply click the Google Sheets option (Figure 4-1).

Designing the Report

Next, we need to design the report to make it useful and aesthetically pleasing. We can do this by structuring the presentation of data and making elements consistent throughout.

It’s always nice to have elements that are consistent through the report. This promotes a sense of cohesiveness, a connection to the company’s branding, and it is aesthetically pleasing.

Getting things looking consistent means that your clients will find the report easier to read.

Color Theme

Another thing you can do is set a color theme for the report so it is consistent throughout. If the company you are producing the report for has a color scheme, you can incorporate it into your report by extracting the color scheme from an image, which can be the company’s logo or even a screenshot of their website.

On the toolbar click Theme and Layout, and on the right pane click “Extract theme from image” (Figure 4-2). You can either upload the image from computer or by URL.

You can then select from three generated themes. If you like one, click it and press apply. If you find the output of the extraction doesn’t have enough color diversity or contrast, you can manually edit the color theme to make your report better fit your company or clients style. Click the customize button in the Theme and Layout pane.

Getting Data onto the Page

Next, we need to add data to the report. As we mentioned earlier, it is a good idea to start with overview data before drilling down into the details. A good way to do this is to start with a pie chart.

Although pie charts are often considered to be bad for users as it is hard to interpret exact values, you can use them (or a doughnut chart) at the top for key groups to be used as filters. Almost any chart can be used with a filter by setting the Interactions – Apply Filter to be enabled. This will mean that if you click a segment within the doughnut or pie, it will apply this to the data source.

Generally, we will apply this to all the other charts and tables – allowing them all to be able to filter the report as people click into rows or slices of data (see Figure 4-3).

We now have a very visual way of seeing the data within the report and being able to quickly drill downward across all tables and information from the same data source. If we have multiple data sources, it will normally only apply to elements from the same source (you can also use groups of charts if you do not want filters to apply to the entire report).

A list of this information alone is potentially useful to a marketing manager, but they probably already know most of this, and so to make it more useful, we combine dimensions together; it gives us a little more insight. We can do this using pivot tables or more graphically using stack charts. For example, if we use a stacked bar chart, we can combine our data on gender, marital status, and if they have kids. See Figure 4-4.

At this point now we can see that most of the respondents in this form are married and most are over 55 years old. This prompts us to consider: Is any bias in the data? Are these people more likely to fill in a survey?

A pivot table can also give us greater insights, especially if we use a heatmap. This combined with the questions that marketers can use to make decisions.

The pivot table heatmap (Figure 4-5) combines purchase frequency with demographic information. According to this, the marketing should target people who are more mature for frequent loyal customers.

You can combine this table with other relevant visualizations (Figure 4-6) and watch how when interacting with one it impacts another. These relationships with data can be quite hypnotic, and the easiest visualization for creating these interactions is pie charts. However, as mentioned before, we should try to avoid pie charts as humans are not very good at reading and comparing angles. Many experts suggest using either a treemap or a bar chart instead for simple filters. Despite that, pie charts are still a decent alternative to using a drop-down box.

Either type can be made to apply a filter, which means that they can be used alongside the more detailed information that is driving decisions. For instance, they can be used to understand the impact of a particular feature on a website.

Free Text Analysis

There were a number of free text boxes in the survey that we can look at in several different ways. If there are enough entries, it’s possible that there will be some overlap or essentially the same response. It can be a little challenging, but with a little analysis we can take the data a little further.

In the survey, we asked if there was anything missing in the product description. Figure 4-7 shows the top results.

Ultimately it would be good to do smart extraction using machine learning, which can be done cheaply and increasingly easily, but that’s the subject for a separate book. For now, we can work with the tools in Data Studio.

“Null” can be easily replaced by “no data” in the formatting (missing data), and while we are there, we can improve the text handling, changing it to “Wrap Text” for longer answers. See Figure 4-8.

We can also include additional classifications for common words. There are some word cloud visualizations within the community selectors, but none of these quite meet our needs. So the first part of this is almost by-hand analysis; when we spot common patterns, we can use the case statement – if there are particular themes we want to bring out, for instance – some mentioned images, some mentioned pools, and some mentioned kitchens.

In Figure 4-9, we can see how we can classify the text if it includes a specific word, which allows us to filter the report by people who entered a particular keyword such as in Figure 4-10.

One minor issue is that using this method, they can only fit into one theme, and so if it mentioned photos or images, then it won’t also be counted as being in pool; as such, the order/prioritization of the case rules is important. Besides, this does give us the ability to create a simple drop-down so you can spot and analyze common themes, and of course this drop-down will also apply to all other data in the report. See Figure 4-10.

Final Result

So now we have an interactive report based on survey data that allows a marketer to drill into this and develop insights of their own, the best thing – it is quite easy to tweak and improve. See Figure 4-11.

Recap

Google Data Studio isn’t just a front end for your analytics data. It can be a presentation layer for survey data, much like a tool like Excel can be, but makes the data online and accessible for everyone.

Data can be improved and cleaned within the tool using simple string functions, and additional transformations can be applied to create custom fields based on free text or by consolidating some dimensions. Ultimately Google Data Studio is a powerful way of taking data sets and presenting them in easy-to-understand ways. For some this is a far better front-end to complex back-end tools such as Google Sheets, but care must be taken to make sure the data is in the right format to start.