Follow

Create Reports in Google Data Studio Using Lasso Data Export Service

Create Reports in Google Data Studio Using Lasso Data Export Service

In this article:

Get Started

To create your own reports using Google Data Studio, you will need to first connect Lasso’s automated data export to Google’s BigQuery data warehouse. Once the two are connected, you will be able to design and generate your own reports with the data available in the Lasso data export. Please note that the data export is in UTC time so there may be differences in date range counts when comparing with Lasso.

There are a few steps to get to the final destination of creating your own reports in Google Data Studio.

Initiate Lasso Data Export Service

  1. Create a Google account.
  2. Contact your Lasso Client Director to enable the Lasso data export service. (Please note there is a monthly fee for this service.)
  3. Provide the email address that is associated to your Google account to your Lasso Client Director.
  4. Obtain a Lasso data export ID from your Lasso Client Director.

Connect Lasso Data Export Service to BigQuery and Google Data Studio

  1. Add a Google billing account.
  2. Add a billing project.
  3. Connect the billing project to the Lasso data export project.
  4. Create and copy the data export project dataset.
  5. Set the start time for the data transfer. By default, BigQuery will run the data transfer to Google Data Studio only once. If the start time is not set, the data in your report will not update.

Each step is described in the following sections of this article:

If you would like to learn more, Google Analytics Academy offers a free beginner course. Click here to access. 

Add Google Billing Account

1 - Sign into your Google account.

 billingaccount4.jpg

2 - Go to the Manage billing accounts page in the Google console. 

3 - Click Create account.

billingaccount1.jpg

4 - Enter a Name for the billing account if desired.

5 - Select your Country.

6 - Click Continue.

billingaccount2.jpg

7 - Complete the required Customer info, How you pay, and Payment method details, and click Submit and enable billing.

billingaccount3.jpg

Add Billing Project

1 - Go to the Manage resources page in the Google Cloud Console.

2 - Click Create Project.

addbillingproject1.jpg

3 - In the New Project window that appears, enter a Project name and click Create.

addbillingproject2.jpg

Your new billing project will appear in the list.

addbillingproject3.jpg

Connect Billing Project to Lasso Data Export Project

1 - On the Google Data Studio home page, and click Create and select Data Source.

createds.jpg

Note: If this is the first time, you are logging into Google Data Studio, you will be prompted by the Welcome, Terms, and Preferences pages. You will need to click Get Started on the Welcome page and complete the Terms and Preferences pages.

gds7.jpg

2 - Begin to type BigQuery in the search box and then SELECT.

gds8.jpg

3 - If you are connecting to Google Data Studio for the first time, click AUTHORIZE to allow access to your account.

authorize.jpg

4 - Click SHARED PROJECTS.

5 - Under Billing Project, select the project you created.

6 - On the line under Shared project name, enter data-export-test-211420. This is the Lasso data export project. A Dataset column will appear.

7 - In the Dataset column, find your data export ID (provided to you by Lasso) in the list and click on it. A Table column will appear. This columns lists all the tables that are available in the Lasso dataset.

8 - In the Table column, select a table (e.g. Registrant).

9 - Click CONNECT.

connectbillingproject1.jpg

Do this for each table that you want to connect.

Note: To see the connected data sources, click on the Data Sources tab on the Google Data Studio home page.

datasources.jpg

Create and Copy Data Export Project Dataset

Next, you will need to copy the Lasso dataset from the Lasso data export project to your billing project.

1 - In Google Data Studio data source page , click on SHARED PROJECTS.

2 - Hover over your billing project and click on the box that appears.

 createcopy1.jpg

This takes you to the Console page. Make sure your billing project is selected at the top of the page. You will see your billing project ID under Resources.

createcopy2.jpg 

3 - Click +ADD DATA.

4 - Select Pin a project.

createcopy3.jpg 

5 - In the Pin a project window, under Enter a project name, enter data-export-test-211420.

6 - Click PIN.

 createcopy4.jpg

The data export project will now also show under Resources.

createcopy5.jpg

Next, you will transfer the data from the data export project to your new project.

7 - Under Resources, click on your billing project ID and click CREATE DATASET. The project ID will be displayed to the left.

createcopy6.jpg 8 - Under Dataset ID, enter a name for the dataset (e.g. Lasso) and click Create dataset.

createcopy7.jpg

Finally, you will copy the Lasso data export dataset to your project.

9 - Click data-export-test-211420 and then, scroll down the list of IDs and select the data export ID that matches the one provided to you by Lasso.

createcopy8.jpg

10 - Click COPY DATASET.

createcopy9.jpg

11- Click on the dropdown arrow in the Project name field and select your project.

12 - Click on the dropdown arrow in the Dataset name field and select the dataset you created for your project

13 - Check the Overwrite destination tables checkbox.

14 - Click COPY.

createcopy10.jpg

Refresh the page. Click on your billing project ID and then the dataset. The Lasso data tables will be listed.

createcopy11.jpg

Set Data Transfer Schedule

By default, once you’ve connected the data source, BigQuery runs the data transfer from Lasso only once. This means your reports will not be updated with new data. You need to set the start time so that the data transfer will continue to run daily.

1 - On the Console page, select your billing project and click Transfers.

setdatatransfer1.jpg 

2 - Click on the dataset.

setdatatransfer2.jpg

3 - Click EDIT.

setdatatransfer3.jpg

4 - Under Schedule options, select Start at set time.

Note: The default will be Start now. This must be change to Start at set timeThe Repeats field will indicate Daily. Leave this as is.

setdatatransfer4.jpg

5 - If you would like to change the time of the daily data transfer, click on the calendar button and adjust the time as desired.

setdatatransfer5.jpg

6 - Scroll down to the bottom of the page and click Save.

setdatatransfer6.jpg

Click the CONFIGURATION tab. You should see None beside the End date (UTC).

setdatatransfer7.jpg

Create Report 

Once you have connected your data source, you can begin to create your report.

1 - To access a blank report:

a) On the Google Data Studio home page, click Create and select Report.

createdsreport1-2.jpg

OR

b) On the Google Data Studio home page, click on the Reports tab and click Blank Report.

createdsreport1-3.jpg

OR

c) If you want to edit the data source fields prior to creating the report, click on the Data Sources tab and select the Data Source.

createdsreport1-4.jpg

Make your edits and click CREATE REPORT.

editds4.jpg

2 - Click on Untitled Report in the top left and give your report a name.

givename.jpg

3 - Under Select Data Source, select the data source needed for your report.

datasource1.jpg

4 – Click ADD TO REPORT.

Note: Check the box beside Don’t show me this again if you don’t want to see this message in the future.

datasource2.jpg

5 - Drag the fields that you want under Available Fields and drop them under Data Source. They will appear on the report.

drag.jpg

Note: When comparing the data in Lasso reports with data in Google Data Studio, there may be discrepancies due to time zone differences. Lasso standard reports recognize the user's time zone while Google Data Studio is UTC standard time zone unless adjusted.

Adjust Report Size

To adjust the size of the report, drag the points on the frame.

drag2.jpg

Adjust Report Column Width

To adjust the width of the columns in the report:

  • Drag the column lines.

OR

  • Right-click in the report. Select Resize columns and then Fit to data or Distribute evenly.

columnwidth.jpg

Update/Edit Data Source Fields

Adjustments can be made to Data Source fields such as changing the field name, counting only unique records, changing a date field format, aggregating the data using various statistics, and adding a description.

1 - Depending on whether you want your data field changes to be reflected in all reports that use the data source or only a specific report, access data source fields as follows:

a) To make changes to the data fields that will be reflected in all reports that use the data source:

  • On the Google Data Studio home page, click on the Data Sources tab.

gdsds1.jpg

  • Click on the Data Source that contains the fields you want to change.

gdsds2.jpg

OR

b) To make changes to the data fields that will be reflected in only a specific report:

  • In the report, click on the pencil icon beside the Data Source.

changedatasourcename.jpg

2 - Edit the fields as needed:

  • Under Field, click on the name to edit.
  • Under Field, click the dropdown beside the name to change the way the records are counted.
  • Under Type, click the dropdown associated to the field to change the format.
  • Under Default Aggregation, click the dropdown to change the way the records are aggregated.
  • Under Description, click on the blank space associated to the field to add a description.

changes.jpg3 - Click DONE when you have completed your changes.

editfields2.jpg

Field names can also be changed in the report by hovering to the right of the field under DATA and clicking on the pencil icon that appears.

changename1.jpg

Enter the new Name on the blank line.

changename2.jpg

Add Date Range Filter

Note: Google Data Studio uses UTC standard time.

There are four types of date ranges to choose from:

  • Auto - defaults to the entire date range in the sheet
  • Fixed - specify exact beginning and ending dates
  • Preset - select from options such as Last 7 days, This Month, This week (starts Sunday), etc.
  • Advanced - create flexible custom rolling dates

For comparison periods, you can also select:

  • Previous period - compares the currently selected period to the previous matching period
  • Previous year - compares the current selected period to the same period last year

1 - Click into the report and click on the calendar icon at the top.

2 - A box will appear. Drag it to wherever you want it placed on the report.

daterangefilter.jpg

3 - Click into the date range box and then click on the dropdown arrow beside Auto data range to select a date range option other than the default.

daterangefilter1-2.jpg

4 - Select a date range option from the list and click APPLY.

adddatefilter3.jpg

You can also edit the date range by hovering on the calendar icon beside the date range field in the Date Range Dimension section under DATA and clicking on the pencil icon that appears.

daterangedimension1.jpg

Click on the dropdown arrow to the right of Show as and select the date range.

daterangedimension2.jpg

Include/Exclude Data via Data Filter

If you want to include or exclude specific data from your dataset so that your users do not have to filter the data themselves, you can apply filters directly to the dataset. For example, you may want to exclude registrants with a realtor or import rating, or you may want include only registrants who have signed up online or have completed an appointment.

1 - Under DATA to the right of the report, click ADD A FILTER.

addfilter1.jpg

2 - Select Include or Exclude, depending on whether you want to include or exclude specific data.

3 - Select the field that contains the criteria you want to include/exclude.

4 - Select the condition that applies to the criteria.

5 - Enter the value and SAVE.

Note: You can add multiple conditions which can be joined by OR or AND:

  • OR - Returns data that meets any of the conditions
  • AND - Returns only data that meets all conditions

createfilter.jpg

Your filters will be listed under DATA in the Filter section.

addfilter3.jpg

You can also access and manage filters by clicking on Resource at the top of the report and selecting Manage filters.

addfilter4.jpg

Tip: To include or exclude specific values within a field, use In as the condition and list the values separated by a comma.

groupdata.jpg

Combine Data Sources

If you find that there are fields you want on your report that come from different data sources, you can combine the data sources to create a blended data source. To created a blended data source, each data source in the blend have a common field, known as a join key. For example, a field that might be common to both data sources is Personal ID or Project Name. You can blend a maximum of 5 data sources.

The easiest way to blend data sources is to simultaneously select charts that have already been created, right-click, and select Blend data. The charts must contain a common field to be used as a join key. If a join key is not automatically recognized, you will get an error message and will need to try to manually blend the data sources.

Automatic Blend

1 - Select the first chart.

2 - Hold down the Shift key and select the second chart.

Both charts will be outlined in blue.

blenddata1.jpg

3 - Right-click and select Blend data.

blenddata2.jpg

If a common field was recognized as a Join key, the charts will be combined into one Blended Data source. You will see this under the DATA tab. You may need to adjust the columns and/or rows depending on how you want the data represented in your chart.

Click on the edit pencil beside Blended Data to view/adjust the blended data source.

blenddata3.jpg

4 - Enter a name for your blended data source in the Data source name field and SAVE

5 - Adjust as needed. Add Join keys, drag and drop additional fields, rename fields, and SAVE.

blenddata4.jpg

If you get the following error message when you try to blend the data sources, try blending them manually.

blenddata5.jpg

Manual Blend

1 - Under DATA, click BLEND DATA.

combineds1.jpg

2 - Click ADD ANOTHER DATA SOURCE.

combineds2.jpg

3 - Click on the data source that you want to add.

combineds3.jpg

4 - In order to combine data sources, there must be a common field between data sources, called a join key field. Join keys show the relationship between data sources. Drag at least one common field from the Available Fields under each Data source to the Join keys so that you have matching fields from both data sources as join keys.

5 - Under each Data source, drag the fields you want on your report from the Available Fields section to the Dimensions, Metrics, and Date range sections, as needed. The fields that you drag over will be listed on the far right.

6 - Enter a name for your blended data source in the Data source name field and SAVE.

combineds4.jpg

Change Report Visualization

When you first create a new report, the data will be represented in the form of a Table, which is simply a list where each column represents a Dimension (a data category such as Rating) or Metric (an aggregated value such as counts of each Rating) and each row is one record. However, there are several options availabe for visualizing your report data, including Pivot Tables, Pie Charts, Bar Charts, and Line Charts. Keep in mind that the format you choose is dependent on the fields in the dataset and how they’ve been customized for report use.

To change the report visualization, click the dropdown arrow to the far right of Chart. This will reveal the various options that are available for representing your data.

chart1.jpg

Table

Tables are best for representing data in a list,  where each column represents a Dimension (a data category such as Rating) or Metric (an aggregated value such as counts of each Rating) and each row is one record.

There are three types of tables to choose from:

  • Standard with columns and rows
  • With bars to show counts, etc.
  • With a heatmap

chart2.jpg 

Table Example

chart3.jpg

Pivot Table

Pivot tables take the rows in a standard table and pivot them so they become columns, alllowing you to group and summarize the data. For example, you can show ratings by community.

There are three types of tables to choose from:

  • Standard with columns and rows
  • With bars to show counts, etc.
  • With a heatmap

chart4.jpg

Pivot Table Example

chart5.jpg

Other options include Times Series, Line Charts, Bar and Column Charts, Pie Charts, Scorecards, and Stacked Charts.

chart6.jpg

chart10.jpg

chart8.jpg

chart9.jpg

Blend Scorecards to Display Conversion Rates

If you use scorecards to display total counts for specific data such as total leads, appointments, and purchasers, you can easily blend them to show conversion rates.

The following example displays scorecards for total leads, appointments set, appointments kept, and purchasers for a specific date range.

scorecard1.jpg

What’s the conversion for appointments set to appointments kept?

1 - Select the scorecard that you are using as the dividend. In this case, it will be the Appts Kept scorecard. The scorecard will be outlined in blue.

scorecard2.jpg

2 - Hold down the Shift key and select the Appts Set scorecard. This is your divisor. Both scorecards will be outlined in blue.

scorecard3.jpg

3 - With both scorecards selected, right-click and select Blend data.

scorecard4.jpg

This will add a new scorecard showing the conversion rate (Appts Kept divided by Appts Set).

 scorecard5.jpg

You can show various conversion rates this way. Simply select the scorecard containing the dividend first, followed by the scorecard containing the divisor. Then Blend data.

 scorecard6.jpg

To add a time comparison metric to the scorecard, please refer to the next section.

Add Scorecard Time Comparison Metric

Adding a time comparison metric to a scorecard allows you to quickly see whether the count has gone up or down compared to a specific time period. For example, if your scorecard is counting the total leads for the last 7 days, you can show whether the count is up or down compared to the previous 7 days.

1 - Select the scorecard.

scorecard7.jpg

2 - Select the DATA tab.

3 - Find the Default date range section and click on the dropdown under Comparison date range.

scorecard8.jpg

4 - Click on the dropdown beside None.

scorecard9.jpg

5 - Select the desired option from the list and click APPLY.

scorecard10.jpg

This will add the comparison metric to the scorecard, showing the percentage that it is has gone up or down. If the metric has gone up, the font will be green. Conversely, if the metric has gone down, the font will be red.

scorecard11.jpg

Convert Field Format from Number to Date

You may notice that some date fields coming from the BigQuery export are not in date format but rather in number format. The number format is the number of milliseconds since January 1, 1970. This can be converted to date format with the addition of a new field and the use of TODATE syntax. 

1 – In the data source, click ADD A FIELD.

gdsdate1.jpg

2 – On the line under Field Name, enter a name for the new field.

3 – In the Formula space, enter the below syntax, replacing ‘date’ with the name of the field you want to reformat. For example, if you are reformatting the Registration Date field, replace ‘date’ with ‘registrationDate’ as in the example.

Syntax: TODATE(date, 'MILLIS', '%Y%m%d')

Note: To include hours and minutes, the syntax is TODATE(date, 'MILLIS', '%Y%m%d%H%M')

4 – Once you have entered the syntax, a green checkmark should appear in the bottom left. This means the syntax is correct. Click SAVE.

Note: If you do not see this checkmark but an error message instead, you will need to correct the syntax.

 5 – Click CLOSE.

6 – Note your new field and click DONE.

gdsdate4.jpg

7 – Click CLOSE and go to your report.

gdsdate5.jpg

8 – Under the DATA tab, drag your new date field into the report as needed.

 Update/Edit Report

1 - To make changes to an existing report, on the Google Data Studio home page, click on the Reports tab and select the report Name.

edit1-1.jpg 

2 - Click Edit.

editgds1.jpg

3 - Click into the report to access the DATA fields.

editgds2.jpg

Share Report

Note: You can only share the report with email addresses managed by Google.

1 - In Edit mode of the report, click Share.

sharereport5.jpg

2 - Enter the email address(es) of the person(s) with whom you want to share the report.

 sharereport4.jpg

3 - If you want to the person to be able to make changes to the report, click the dropdown arrow beside Can view and select Can edit. If you only want to allow the person to view the report, leave the default Can view.

sharereport3.jpg

4 - Click Send.

sharereport2.jpg

This will send an email to the recipient inviting them to view or edit the report.

 sharereport1.jpg

Google Data Studio Functions

There are a number of functions available in Google Data Studio that use syntax to transform the data. Functions allow you to group data, perform calculations, and modify date data, among other things. Here is a link to the available functions - https://support.google.com/datastudio/topic/7019880?hl=en&ref_topic=7570421