JSON Structure Description

Have you ever wondered what is happening in the complex code of a connector? Read more to crack this shell open.

Let's split the JSON into three parts to understand the logic from beginning to the end. We won't describe every given parameter as some of the parameters are more important to us than to you. If you want to customise your queries by yourself than you should read and understand this.

First Part of the Query

Every template starts with defining the query:

• naming and scheduling the connector

• what the connector does and where the data will be stored (outputCode and Storage)

• and other parameters, that are less important for you and more important to us

Second Part of the Query

The magic starts to happen from the second part of the code, because the source, dimensions and metrics of the query are defined here.

The source is defining what platform the query will target.

Parameters specify exactly what dimensions and metrics will be downloaded. In this case, there is the: Date, Source/Medium, Campaign and Ad Group dimensions and from metrics there are: Sessions, Hits, Entrances, Bounces, Pageviews, Users, New Users, Conversions and Conversion Value.

In other words, you are listing which metrics from the API's platform you want to download.

Note: The structure must follow the naming conventions of the platform.

Third Part of the Query

The third part of the query is very dependent on the second. In the second you specified what you want to download from API, but in the third, you need to define what will ROIVENUE do with the data and how it will manage it.

You can imagine this step as building columns in an Excel file. See this example:

Shortened third part of JSON

You can see the repetitive structure of every column. Look at one of the most typical metrics from Google Analytics (sessions)a below.

• With code you are telling the JSON to create a column with the name sessions.

• The datatype is what kind of data are you storing in the cells. Long is a value, that tells the JSON to store integers in it.

• The conceptType is saying if the field is metric or dimension.

• SemanticType tells you how and in which format the field should be threaded.

• The source parameter is referring to the source parameter from the second part of the query. The connector checks if the value in the source field refers to the same field from the second part and if yes, it populates the column with that data.

Note: You need to refer to the source parameter without the "ga:" prefix. Otherwise, the connector would fail.

The Whole Query

Now you should be able to understand the logic of your JSON queries. The skeleton of every JSON stays the same, but the parameters can vary from platform to platform.

[
	{
		"name": "GASessionsDailyQuery",
		"comment": "Ga Sessions daily",
		"info": {
			"schedule": {
				"startDate": "0001-01-01T00:00:00",
				"mode": "oneTime"
			},
			"outputCode": "GaSessionsDaily",
			"output": {
				"partitionKeys": [
					"date"
				],
				"deleteEnabled": true,
				"writeFileWhenNoData": false,
				"format": "csv",
				"storage": "azureBlob",
				"gzip": false,
				"relativePath": "connector/ga/gasessionsdaily_{startDate}-{endDate}_at_{execTime}{extension}",
				"dateColumn": "date",
				"isSortedByDate": true
			},
			"source": {
				"type": "Source.GoogleAnalyticsSource",
				"parameters": {
					"dimensions": [
						"ga:date",
						"ga:sourceMedium",
						"ga:campaign",
						"ga:adGroup"
					],
					"metrics": [
						"ga:sessions",
						"ga:hits",
						"ga:entrances",
						"ga:bounces",
						"ga:pageviews",
						"ga:users",
						"ga:newUsers",
						"ga:%%conversions_metric%%",
						"ga:%%conversionValue_metric%%"
					],
					"sort": "ga:date"
				}
			},
			"transforms": [
				{
					"type": "Transform.Columns",
					"parameters": {
						"columns": [
							{
								"code": "date",
								"dataType": "datetime",
								"conceptType": "dimension",
								"semanticType": "date",
								"expression": "ParseGaDateToDate(date)"
							},
							{
								"code": "webSource",
								"dataType": "string",
								"conceptType": "dimension",
								"semanticType": "dimension",
								"expression": "sourceMedium.Split(new [] {'/'}).First()"
							},
							{
								"code": "webMedium",
								"dataType": "string",
								"conceptType": "dimension",
								"semanticType": "dimension",
								"expression": "sourceMedium.Split(new [] {'/'}).Skip(1).First()"
							},
							{
								"code": "webCampaign",
								"dataType": "string",
								"conceptType": "dimension",
								"semanticType": "dimension",
								"source": "campaign"
							},
							{
								"code": "webAdgroup",
								"dataType": "string",
								"conceptType": "dimension",
								"semanticType": "dimension",
								"source": "adGroup"
							},
							{
								"code": "sessions",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "sessions"
							},
							{
								"code": "entrances",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "entrances"
							},
							{
								"code": "bounces",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "bounces"
							},
							{
								"code": "hits",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "hits"
							},
							{
								"code": "pageViews",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "pageviews"
							},
							{
								"code": "users",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "users"
							},
							{
								"code": "newUsers",
								"dataType": "long",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "newUsers"
							},
							{
								"code": "conversions",
								"dataType": "decimal",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "%%conversions_metric%%"
							},
							{
								"code": "conversionValue",
								"dataType": "decimal",
								"conceptType": "metric",
								"semanticType": "metric",
								"source": "%%conversionValue_metric%%"
							},
							{
								"code": "currencyCode",
								"dataType": "string",
								"conceptType": "currencyCode",
								"semanticType": "currencyCode",
								"source": "currencyCode"
							}
						]
					}
				}
			]
		}
	}
]

We currently provide tutorials for adjusting JSON in templates to these platforms:

• Google Ads
• Facebook Business Manager