User-defined reports are custom reports where you, as the user, can select the dimensions and metrics you want to retrieve from your Google spreadsheet.
Please ensure your initial Google spreadsheet meets the formatting requirements. Detailed explanations can be found here.
When you select a user-defined report, you will see additional fields in the interface.
To build this type of report, you need to define the required dimensions, metrics, and spreadsheet tab yourself. Here is some guidance on how to do that.
1. How to define dimensions and metrics
At least one dimension or metric should be defined for the connector to function properly.
To define dimensions and metrics, let's look at this sample Google sheet:
The first input you will be asked for is the columns in the Google sheet that you would categorise as 'Dimensions'. A dimension is usually a text value but could also be anything else that is not a metric. For example: date. Any column you include in this list will be parsed as a dimension by the connector, so you will see it as a STRING when it reaches BigQuery.
Following the example above, we can define 'Market' and 'Quarter' as dimensions:
In this case, you need to fill in the corresponding field for dimensions with the following values:
The second input you will be asked for is the columns in the Google sheet that you would categorise as 'Metrics'. A metric is usually a numerical value. Any column you include in this list will be parsed as such by the connectors, so you will see it as a FLOAT when it reaches BigQuery.
Following the example above, we can define 'Budget' and 'Target ROAS' as metrics:
In this case, you need to fill in the corresponding field for metrics with the following values:
For both dimensions and metrics, you should input the names exactly as they appear in the first row of your Google spreadsheet. We will parse them and make them 'BigQuery friendly', so they might not have the exact same name when you see them in the output table. For example, we replace non-alphanumeric characters with '_' and convert everything to lowercase.
2. How to identify a spreadsheet tab
The third and final input you will be asked for is the Spreadsheet tab where you would like the connectors to look for your data.
To identify the name of the tab in your spreadsheet where the data is located, simply select the required tab from a dropdown menu:
This is an optional field. If you leave it empty, we will simply grab the data from the first tab in the spreadsheet.
In the initial example above, there are two tabs in the spreadsheet:
Thus in the interface you will see the drop down menu with the following options:
With the example data above, the configuration values used in the guide above would in turn result in a BigQuery table that looks like this:
