Google Analytics Source Component

The Google Analytics Source component is an SSIS data flow pipeline component that can be used to read/retrieve data from Google Analytics.

The component includes the following two pages to configure how you want to read data from Google Analytics.

  • General
  • Filters
  • Columns

General

The General page of Google Analytics Source component allows you to specify the general settings of the component.

General page: Google Analytics Reporting API

SSIS google analytics source

Connection Manager

The Google Analytics Source Component requires a connection in order to connect to Google Analytics. The Connection Manager drop-down will show a list of all connection managers that are available in your current SSIS package.

Analytics View

The Analytics View option specifies the Google Analytics view to select. Click the eclipse button (‘…’) to open up a Google Analytics Web Profile dialog to select a view.

Metadata Mode

The Metadata Mode drop-down contains a list of commonly-used queries in Google Analytics, each mode indicates a configuration that defines a combination of Metrics, Dimensions, Filters and Sort parameters, so you can select and use them directly.

Note: This option is only available to Google Analytics Reporting API.

Date Ranges (v8.0)

The Date Ranges option allows you to specify start date and end date for fetching Google Analytics data. The Date value can be for a specific date by using the DateTimePicker controls or can be selected from the drop-down list, which contains several pre-defined date value, including Today, Yesterday, NDaysAgo, Beginning/End of Current/Last Week/Month/Year.

Metrics

The Metrics option allows you to specify the metrics from Google Analytics. This option is required.

Dimensions

The Dimensions option allows you to specify the dimensions to break down the metrics you specified. This option is optional.

Sort By

The Sort By option contains a list of metrics and dimensions indicating the sorting order for the returned data. Specify the sorting fields and directions so the result will be sorted. This option is optional.

Segments

The Segments option allows you to choose a segment in Google Analytics from the drop-down. This option is optional.

Sampling Mode

The Sampling Mode option specifies the sampling level to query data from Google Analytics. There are three types available for a V3 connection:

  • Default: Returns response with a sample size that balances speed and accuracy.
  • FASTER: Returns a fast response with a smaller sample size.
  • HIGHER_PRECISION: Returns a more accurate using a large sample size, but this may result in the response being slower.

There are three types available for a V4 connection:

  • SMALL: Returns a fast response with a smaller sampling size.
  • LARGE: Returns a more accurate but slower response.
  • DEFAULT: Returns a response that balances speed and accuracy.
Page Size

The Page Size option allows you to specify how many records you want to retrieve each time.

Include Empty Rows

If the Include Empty Rows option is disabled, rows where all metric values are zero will be omitted. This option is enabled by default.

General page: Google Analytics Data API

SSIS google analytics source - Data API
Report Type

Specify the Report Type  of the Google Analytics Data API. Available options are:

1. Google Analytics Data API Report

2. Google Analytics Data API Realtime Report

Currency Code

Specify the Currency Code for Google Analytics Data API  in ISO4217 format, such as "AED", "USD", "JPY". 

Refresh Component

Clicking the Refresh Component button to retrieve the latest metadata and update each field to its most recent metadata.

Expression fx Button

Click the fx button to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Button

Click the Generate Documentation button to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Filters

The Filters page of the Google Analytics Source component allows you to build the filter condition when retrieving data from Google Analytics Reporting API.

SSIS google analytics source - Filers Page

Metrics Filters

The Metrics Filters grid allows you to specify the filtering condition for metrics.

Dimensions Filters

The Dimensions Filters grid allows you to specify the filtering condition for dimensions.

Filters

The Filters option shows the filter query that is auto-generated by the Metrics Filters and Dimensions Filters.

Columns

The Columns page of the Google Analytics Source Component shows you all available fields from the metrics and dimensions that you specified on the General page.

SSIS google analytics source - columns page

On the top left of the grid, the checkbox can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns Page grid consists of:

  • Google Analytics Field: Column that will be retrieved from Google Analytics.
  • Data Type: The data type of this field.
  • Properties window for the field listed
    • Name: specify the column name.
    • Data type: the data type can be changed according.
    • Length: Specify the Length of the fields. If the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
    • Precision: Specify the number of digits in a number.
    • Scale: Specify the number of digits to the right of the decimal point in a number.
    • CodePage: Specify the Code Page of the field.

Metric Filter

The Metric Filter  page of the Google Analytics Source component allows you to build the filter clause of metrics when retrieving data from Google Analytics Data API. The page comes with a condition builder on the top which you may use to create filter clauses of the metrics,

The condition builder support various operators, including "Equal", "Between", "Greater than", among many others. Once you have built such matching conditions, the component should display the query that will be used during runtime. 

Switching the query designer to Custom Query Mode will allow you to freely edit the query text. This can help in some cases where certain advanced queries may not be supported in the condition builder.

SSIS google analytics source - Filers Page

Dimension Filter

The Dimension Filter  page of the Google Analytics Source component allows you to build the filter clause of dimensions when retrieving data from Google Analytics Data API. The page comes with a condition builder on the top which you may use to create filter clauses of the dimensions,

The condition builder support various operators, including "Equal", "Between", "Greater than", among many others. Once you have built such matching conditions, the component should display the query that will be used during runtime. 

Switching the query designer to Custom Query Mode will allow you to freely edit the query text. This can help in some cases where certain advanced queries may not be supported in the condition builder.

SSIS google analytics source - Filers Page

Cohort Spec

The Cohort Spec  page of the Google Analytics Source component allows you to configure the cohort group associated with this GA4 report request. 

SSIS google analytics source - Filers Page

Cohorts

Defines the selection criteria to group users into cohorts.

Cohort Range Granularity

The Cohort Range Granularity is used to interpret the startoffset and endoffset for the extended reporting date range for a cohort report.

Cohorts Range Start Offset

Specify the start date of the extended reporting date range for a cohort report.

Cohort Range End Offset

Specify the end date of the extended reporting date range for a cohort report.

Accumulate

If true, accumulates the result from first touch day to the end day.