Tag Archives: Google Analytics

Enter Data Source Schema

ga-enter-schemaEntering the Google Analytics data source schema into Analytics Edge makes it easier to upload data by automatically populating the target column names to match the schema.

Copy the schema from the Google Analytics Admin interface for the specific data source, and paste it into the box provided.

Upload Data into Google Analytics

ga-upload-dataCAUTION: read the information in Google Analytics Help regarding data upload before using the Analytics Edge upload feature.

To upload data into Google Analytics, select the Property and Custom Data Source (see Google Analytics help for details about custom data sources and uploading data).

You can enter the schema for the data source by clicking the button provided. You can also import any custom dimension and metric definitions to make it easier to map the columns.

Select the source of the data to be uploaded, then map the various columns from the source to the columns in the schema. NOTE: you need to match the schema of your data source; by default, Analytics Edge will list all possible fields. if you use fields that are not part of the selected data source schema, the extra columns will not be uploaded.

Once you have mapped all of the fields, click Finish. A dialog will appear giving you a 5-second countdown to cancel the upload.  To delete a upload after it has been completed, you will need to do so from the Google Analytics Admin interface.

Management Lists

ga-management-listsThe Management Lists wizard allows you to download information about your account(s), web properties and views. Select the Login to use for the query, then pick the Account, Web Property and View (Profile) to use for the query. Then select the report to run from the list at the left.

If you select ALL for the account, property or view, you will get listings for all your accounts, properties and/or views that match.

Analytics Reporting – Dynamic Segments

dynamic-segmentsAnalytics Edge makes it possible to create dynamic segments using a a wizard interface similar to that in the web version of Google Analytics. Select the category to the left, then select the options you want in your segment to the right.

You can add multiple conditions in a single segment, and all conditions must be met for a session to be included in the condition.

It is also possible to enter a Custom expression to pass direct to the API &segment= parameter (must be properly formatted). This can entered directly into the wizard, or an Excel cell reference can be used to a worksheet location.

Analytics Reporting – Options

optionsThe Options tab provides access to several options that affect how the Google Analytics data is presented in Excel.

Rates/Percents: By default, Analytics Edge will convert rate and percentage metrics into fractions of 1 (50% = 0.5) so that the cells can be formatted using Excel’s % formatting. If you would rather see 50 in the cell, select Number.

Dates: Analytics Edge automatically converts dates into Excel dates. If you would rather get the non-standard response (20140725) format, select String/Number.

Numeric Dimensions: By default, all dimensions are delivered to Excel in text-formatted cells. Some dimensions, like Count of Sessions are numeric in nature. If you would like to see these “numeric” dimensions presented as numbers, select Number.

Time Metrics: by default, time metrics like Time on Page and Sessions Duration are expressed in seconds. Select Days to make it possible to format the cell in Excel using a Time format to get hh:mm:ss formatted times.

Include Empty Rows: by default the GA API returns rows in some queries with zero results. You can suppress these rows with this option.

Sampled Data Options 

If your query is subject to data sampling, you can choose to be warned of this happening by checking the box ‘warn if results contain sampled data‘.

You can minimize sampling errors by reducing the time-span of the query, or include a time dimension and check the box to ‘minimize sampling‘. Doing this will cause Analytics Edge to make queries for the smallest time dimension in your query. If you include Date ( or Day Index, or Day of Month) then it will query for each day. Similarly for any Week, ISO Week, Month or Year dimension. If you include more than one time dimension, the smallest one will determine the query resolution. Note that there is no restriction or special handling required with other dimensions or metrics in your query. Be aware that there is a daily limit of 10,000 requests per View.

minimize-sampling

 

Convert Excellent Analytics

[Only available in the Free Google Analytics connector]

Use this wizard to convert existing queries built with Excellent Analytics into Analytics Edge queries. Select the cell below the Excellent Analytics query cell and open the wizard.

convert-excellent-analytics-1

The Excellent Analytics query will be detected and displayed. Select the Google Analytics Login that has access to the web property in the query, and click Convert.

convert-excellent-analytics-2

With the Analytics Edge Basic Addin

analytics-edge-conversion-of-excellent-analyticsProgress of the conversion will be displayed. If successful, Analytics will run the query and update the results in the worksheet when you click the Close button.

To edit your query, select the cell with the comment and click the Edit Query button on the Analytics Edge ribbon bar.

With the Analytics Edge Core Addin

convert-excellent-analytics-4Progress of the conversion will be displayed. If successful, Analytics will run the query and update the results in a temporary worksheet when you click the Close button.  From the Analytics Edge ribbon bar, click File – Write to Current Cell and the results will be put into your report.

 

Google Analytics Accounts

Use this wizard to log in to your Google Analytics account and authorize Analytics Edge to access your data. Enter a name for the account you want to login with, then click the Add Account button.

new-account

Multiple accounts can be stored, and you can change which account gets used by default. If your reports make multiple queries and always refer to the ‘Default account’, you can switch the default account to report on on different account.

[Pro version: if you want to be able to Upload Data into the account or Save Filters, check the box below the reference name before you Add the account to obtain elevated authorizations necessary.]

Select an account from the list, select a web property and view, and click Make Default to set that view to use as default for that login.

Use the ‘Save Default in Macro’ button to force a specific workbook macro to always use a specific account as the default.

MANUAL LOGIN

If you experience a problem with the login, click the Manual link in the dialog. A new browser window will open. Login to your Google Analytics account and Accept the access request. You will be taken to a web page on the Analytics Edge web site, and there should be a code in the URL of the browser. Copy the code part of the URL, close the browser, and paste the code into the dialog box provided.

ga-manual-auth

 

Analytics Reporting – Segments

segmentsThis Analytics Edge wizard is used to select a predefined Google Analytics segment from your account, or to define a dynamic one to be used solely for this query.

All system-provided segments are listed first, followed by your custom segments, alphabetically sorted.

When you select DYNAMIC from the drop-down, a second wizard will open to guide you through the next steps. To edit a dynamic segment, click the button provided.

 

MCF Reporting – Dates

ga-mcf-datesUse this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Analytics Reporting – Sort

sortUse this Analytics Edge wizard to select the sort order to use for the query. Additionally, you can restrict the query to download a limited number of rows i.e. the top 10.

To sort on multiple columns, select each column in turn and click either Ascending or Descending. Columns will be sorted in the order entered.

Although Google’s API returns only 10,000 rows per query, Analytics Edge will transparently make multiple queries to get all your results. You can limit the query by entering a number other than zero.

Analytics Reporting – Dates

datesUse this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Finally, you can select to use the date range chosen, or use the same duration period previous to the chosen date range, or use the same period from the previous year.

Analytics Reporting – Filters

filtersUse this Analytics Edge wizard to define filters to use with the query.

Select a Dimension or Metric, then pick a Comparison operator and enter a Value. Click the Add button. The filter will appear in the list at the bottom.

Use AND or OR logic to join multiple conditions in a single filter.

Note that all the metrics and dimensions used must be valid when used in combination with the metrics and dimensions of the query itself.

Use a Cell Reference

For advanced users, you can use a filter expression stored in a worksheet, BUT the expression must be encoded exactly as the API expects it to be.  See the API documentation for details. For example, to filter for City equals Ottawa, the expression would be ‘ga:city%3D%3DOttawa’.

To use a cell reference, click the button to the right of the entry box, then click the cell in the worksheet where the filter expression is stored.

 

Analytics Reporting – Fields

fieldsUse this Analytics Edge wizard to select the dimensions and metrics to be retrieved in the query.

Add/Remove Fields: Select a field in the listing on the left and click the Add button, or double-click on the field name to add it to the selected field list on the right. Dimensions and metrics are managed separately. You can add up to 7 dimensions and 10 metrics in a query.

Invalid Combinations: Not all combinations are valid, and Analytics Edge will warn you of invalid combinations by graying out some entries as you add fields.

Deprecated: Items marked with an asterisk (*) are deprecated by Google: this means that while they still work for now, they will stop working at a future date. By default, deprecated fields are hidden but they can be included by selecting the check box.

Descriptions: Selecting any field will show the associate API field name and description at the bottom.

Filter: to filter the list of dimensions and metrics, simply type a few characters into the filter box provided. The filter can match the display name or the actual API field name.

Pivot (Pro Connector Only): After selecting your dimensions for the query, you can select one of those dimensions to segment the results into columns. For example, you can select the Date dimension to obtain one column per Date in the results.

Note that the columns will be created in the order in which they appear in the query, so changing your sort order may affect the order of the columns. Also, there will be one column for each unique value, so absolute column position may change from query to query.

For more control over pivoted resulted, use the Pivot function in the Analytics Edge Core Add-in.

 

Analytics Reporting – Views

viewsUse this Analytics Edge wizard to select the Login account and the reporting View to use for the query.

You can enter a view (profile) id directly, or click the cell reference button and select a cell in the workbook to get the value from. View ID’s are the numbers displayed in the View (profile) selector. e.g. 76063423.

[Pro version: you can also enter an Analytics Edge [range] reference (Core Add-in required) to a column of view id values, and the connector will query multiple views and deliver the results in a single response, prefixed by a View ID and View Name column.]

* Google Analytics Help

google-analytics-pro-menuThe Analytics Edge connector for Google Analytics comes in 2 variants: Free and Pro. They share the same account and configuration files, so it is possible to switch between them or to start with the Free version and upgrade to the Pro without having to rebuild your reports.

Before you can make a query, you must log in through the Accounts wizard.

The Free version has some amazing capabilities, but it cannot do some things. The Pro version can also:

  • perform multi-view queries
  • access the Management Lists
  • access Multi-Channel Funnel data
  • Upload data
  • Save Filters
  • has full support of custom dimensions and metrics
  • can access the extra custom dimensions and metric available to GA Premium users

 

Configuration – Accounts

Before you can use the connector, you MUST log in to a valid a Google Analytics identity. Both connectors support multiple logins, and you can mix queries from different logins in the same workbook. One of the logins is designated as a Default, and is identified with an asterisk (*).

Select Accounts from the connector’s menu to open the Accounts wizard.

Enter a Reference Name for the account and click the Add Account button. You will step through a Google Analytics login sequence to authorize Analytics Edge to access your account. *

new-account

Select your login in the list, then pick an Account/Property/View combination and click the Make Default button.

* A note about security: your account credentials are stored, strongly encrypted, in a file on your computer and are sent direct to Google’s servers using an encrypted https link. Your credentials are not stored in your workbooks, making them safe to share. Analytics Edge (the company) has no access to your account information.

Configuration – Purchased License

google-analytics-pro-licenseThe connector will automatically register. The Pro version will operate free for 30 days, after which time a license must be purchased.

To enter a license code, select License from the connector’s menu and enter your code.