Google Analytics Integration
SAS_IntegrationNotRESTIntroduction
You can integrate third-party systems with Service Apps in many ways. The method you select will depend on business requirements, architectural and security constraints as well as the characteristics specific to each third-party application or service. You can use the REST, CSV, Online CSV data sources and the HTML Script widget.
This document describes a standard integration process that has already been implemented in a customer context.
To find out more about this integration, please do not hesitate to contact your EasyVista contact, or your service provider and integrator.
Integration Summary
This integration is used to retrieve all available data analyzing traffic to your websites and to apps created using Service Apps via the Google Analytics REST API and the Google Analytics Spreadsheet Add-on module in Google Sheets.
You want to create an Service Apps app that displays dashboards containing charts, diagrams and trends so that users can have a quick view of all relevant metrics tracking your websites and apps.
Integration Data Feed (direction) | Authentication Type | Integration Connector Options | ||
---|---|---|---|---|
Service Apps |
N/A |
Datasource CSV Datasource Online CSV |
SaaS Version of Third-Party Product |
On-premise Version of Third-Party Product |
---|---|
Detailed description of the API
Step-by-step integration process
Note: In Service Apps versions 2016.3.x and earlier, there is no integrated REST data source for integrating Google Analytics data directly using REST architecture and OAuth 2.0 authentication, as is the case for other Google services such as Google Gmail, Google Contacts, Google Calendar, etc.
However, you can use the CSV and Online CSV data sources by integrating CSV files created from the online export or publication of Google Sheets spreadsheets, and using the Google Analytics Spreadsheet Add-on module available for Google Sheets.
You can integrate Google Analytics in Service Apps in four easy steps:
- Step 1: Check the list of requirements.
- Step 2: Create a Google Sheets spreadsheet containing Google Analytics reports.
- Step 3: In Service Apps, create the data sources linked to Google Analytics data.
- Step 4: Create widgets to display Google Analytics data.
See
- Detailed description of the API for useful links to help you perform the integration.
- Best practice
- Examples of Google Sheets spreadsheets with Google Analytics reports
- Use case
Step 1: Check the list of requirements.
- You must have a Google account. If this is not the case, use the form for creating your account.
- Your Google account is associated with a Google Analytics account that you will use for collecting audience analysis statistics for your Web pages (corporate website, business applications, etc.) and Service Apps apps using the tracking IDs provided by Google Analytics, e.g. UA-XXXXXXXX-X.
If this is not the case, log in to your Google account. In the same Web browser window, follow the procedure for creating a Google Analytics account for analyzing website traffic. - In the Web pages you want to analyze using Google Analytics, you have already integrated a JavaScript tracking snippet> containing a unique tracking ID obtained when a property is created.
- You have installed the Google Analytics Spreadsheet Add-on module in Google Sheets. If this is not the case, create or open a Google Sheets spreadsheet and select Add-ons > Get Add-ons from the menu.
See the detailed procedure:
- Google Analytics Spreadsheet Add-on Implementation Guide: Installing the add-on
- Google Analytics Spreadsheet Add-on introduction (start: 00:40/05:38)
Step 2: Create a Google Sheets spreadsheet containing Google Analytics reports.
See the detailed procedure:
- Google Analytics Spreadsheet Add-on introduction (start: 01:16/05:38)
- Google Analytics Spreadsheet Add-on detailed presentation (start: 01:56/14:31)
1. Create a Google Sheets spreadsheet.
2. Create the Google Analytics reports you want in the Report Configuration sheet. Select Add-ons > Google Analytics > Create a New Report.
See the detailed procedure: Google Analytics Spreadsheet Add-on Implementation Guide: Creating reports
Note:
- The Report Configuration sheet will automatically be created once the first report is created.
- Each column in the sheet represents a different report.
- To create new reports quickly, copy and paste the column of an existing report and change the parameter values as required.
3. Configure report parameters:
- List of parameters available: Google Analytics Spreadsheet - Configuration Parameter Reference
- Depending on the API used, the detailed list of values is available for the main parameters, Metrics and Dimensions:
4. Run the spreadsheet reports by selecting Add-ons > Google Analytics > Run Reports from the menu.
See the detailed procedure: Google Analytics Spreadsheet Add-on Implementation Guide: Running reports
Note:
- The module will create as many sheets as there are reports (columns) in the Report Configuration sheet.
- All sheets will have the same structure. See
below:
- The first 15 rows contain information on the report configuration.
- The following rows contain the report data.
5. Create one or more sheets as required in order to organize and consolidate (using basic formulas) report data generated by the Google Analytics Spreadsheet Add-on module.
Note: The consolidation sheets will be the source files used by the CSV and Online CSV data sources linked to your Service Apps app.
This is because the first 15 rows of each sheet contains information on the report configuration. These rows cannot be read by the CSV and Online CSV data sources because they can only read CSV data from the first row down.
You must therefore retrieve data from row 16 down for each sheet. To do this, use the Sheet<n>!B16 formula where Sheet<n> is the name of your Google Sheets spreadsheet whose data you want to retrieve.
See the use case.
In your Google Sheets spreadsheet, create a new sheet called Consolidated Stats and move this sheet to the position before the Report Configuration sheet.
To retrieve Sheet 4 data from row 16 down, use the Sheet4!B16 formula. See the screen.
![]() |
(1): First 15 rows generated for the Sessions Last Week report, cannot be read by (2): Data from the Sessions Last Week report that can be retrieved in the consolidation sheet |
6. Generate the CSV files to be integrated in your Service Apps app using a data source.
- If you are using a dynamic data source, Online CSV, you should schedule the report to run automatically. Click Add-ons > Google Analytics > Schedule Reports.
See the detailed procedure: Google Analytics Spreadsheet Add-on Implementation Guide: Scheduling reports to run automatically
- If you are using a static data source, CSV, you should generate reports manually whenever required.
- Select File > Download as > Comma-separated values (.csv, current sheet).
- Save the CSV file in the folder you want.
Step 3: In Service Apps, create the data sources linked to Google Analytics data.
Based on your requirements:
- You can use two types of data sources: CSV and Online CSV.
- Use the static CSV data source if your Google Analytics data does not change over time (e.g. statistics for past weeks, months or years) and if you do not want statistics for any period after the date on which you import the CSV file, or if you want to check the data in the source file before importing it manually to your app.
- Use the dynamic Online CSV data source if your Google Analytics data changes over time. This enables you to set up automatic reporting processes.
Daily, weekly or yearly reporting
- You can create one or more data sources using data from the same source file or from different source files.
- Data sources can use different columns via the Data Transformer.
See the detailed description of data sources:
CSV
Online CSV
Step 4: Create widgets to display Google Analytics data.
For this integration, you use widgets from the Dashboards category in order to display Google Analytics visually in your Service Apps app.
See the widget library.
Note: You must unselect the First Row Contains Dimension Elements property for each widget.
Best Practice
- If you plan to create dashboards using statistics provided by website analysis tools such as Google Analytics Reports, you will probably make use of pie charts, line charts or bar charts. You must first be clear as to what you want to display in these charts and define the metrics and dimensions of the Google Analytics API to be used in the x-axis, y-axis or in pie segments.
- Google Analytics provides numerous metrics on website analysis. Based on your requirements, you can create one Google Sheets spreadsheet for each theme.
See Google Analytics Core Reporting API (V4) - Dimensions & Metrics Explorer.
Create the following Google Sheets spreadsheets:
- My Website_GoogleAnalytics_Page Tracking.gsheet ==> Type of metric: Page Tracking
- My Website_GoogleAnalytics_Sessions.gsheet ==> Type of metric: Session
- My Website_GoogleAnalytics_Site Performance.gsheet ==> Type of metric: Site Speed
- My Website_GoogleAnalytics_Users.gsheet ==> Type of metric: User
- My Website_GoogleAnalytics_Platform or device.gsheet ==> Type of metric: Platform or Device
- You can combine Dimensions parameters from different categories in a given Google Sheets spreadsheet.
- In the Report Configuration sheet, arrange columns in a logical order for optimal organization and retrieval. Give a short, clear and descriptive name to each of your reports in the column header.
- Using the statistics provided by Google Analytics reports, create data consolidation sheets in the Google Sheets spreadsheet. This data can be directly retrieved to define dashboards and charts quickly (e.g. pie charts, line charts or bar charts) in your Service Apps app.
See step 2.5 and the use case.
- Give a short, clear and descriptive name to each of your reports in the column header. This will help you identify the data source columns you want to display in your charts using the Data Transformer for widgets in the Dashboards category.
- If you have numerous reports in these sheets, you can add empty columns to differentiate groups of similar reports.
See the use case > step 5.
- Ensure that columns are correctly arranged from left to right according to your requirements. This is the order in which the names of columns will be displayed in the Data Transformer.
See the use case > step 7.
Examples of Google Sheets spreadsheets with Google Analytics reports
The Google Sheets spreadsheets below (converted to Microsoft Excel XLSX format) contains examples of Google Analytics reports with audience analysis statistics for the EasyVista wiki website (based on Xwiki). You can use them as sample files to help you understand how to create Google Analytics reports, reuse Google Sheets formulas and functions, as well as useful Google Analytics metrics and dimensions, etc.
The reports enable you to define dashboards with statistics and metrics:
- Yearly consolidated data per month over a period of three years (current year, Y-1 and Y-2)
- Monthly consolidated data per week over a rolling two-month period (current month and M-1)
- Weekly consolidated data per day (Monday to Sunday) over a rolling two-week period (current week and W-1)
Note:
- To use the sample files with Google Sheets, convert them to GSHEET format.
- In your Google Drive account, for example, right-click an XLSX file.
- Select Open with and Google Sheets. A new GSHEET file will be created in the same folder.
See the screen.
- The View (Profile) ID in reports in the Report Configuration sheet has become anonymous.
- This number is automatically assigned by the Google Analytics Spreadsheet Add-on module when a report is created.
- The value of this number is linked to the Google Analytics account and to the property selected when the report was created.
- To reuse all or some of the reports included in the sample files (to be converted to GSHEET format), you should:
- Generate a new Google Analytics report using the Google Analytics Spreadsheet Add-on module by selecting your Google Analytics account and the property you want to analyze.
- Copy the View (Profile) ID of the new report and paste it in existing reports to replace ga:XXXXXXXX.
- Delete the column of the new report.
- Delete the existing sheets corresponding to the reports configured in the initial Report Configuration sheet and subsequently deleted.
- Modify the consolidation sheets, e.g. Consolidated Stats By Year, based on modifications made in the Report Configuration sheet. These modifications will affect all report sheets created in Google Sheets. These data consolidation sheets contain formulas published on the Web or saved in CSV format so that they can be reused by the CSV and Online CSV data sources.
Use case
How to create a dashboard to compare the number of user sessions per month over a period of three years (current year, Y-1 and Y-2) in order to display trends?
- You can use the Bar Chart widget to obtain a similar result to the chart below.
- You can also use the sample file called My Website_GoogleAnalytics_Sessions.gsheet.xlsx.
1. Create a Google Sheets spreadsheet called My Website_GoogleAnalytics_Sessions.gsheet.
2. In the Report Configuration sheet, add different reports for defining your dashboard. Each column represents one of the reports: Sessions Last Week, Sessions This Week, etc.
3. Configure the report parameters. See the step-by-step integration process > step 2.3.
4. Run the report in the Google Analytics Spreadsheet Add-on module. One sheet is created for each report. See the structure of each sheet: step-by-step integration process > step 2.4.
5. Create a new sheet called Consolidated Stats for consolidating data and move this sheet to the position before the Report Configuration sheet.
6. In the consolidation sheet, define the data that will be directly retrieved by the data sources of your Service Apps app.
- Manually add data that is not from reports, such as the Week column (A), Month column (D), and Year column (G).
- Use the Sheet<n>!B16 formula to retrieve the data you want in the other sheets from row 16 down. If you modified the default name of the sheet, you must replace Sheet<n> with the new name, as shown in our example.
- Add empty columns to improve the readability of the sheet.
![]() |
|
7. Generate the CSV files you want to use in your Service Apps app. See the step-by-step integration process > step 2.6:
- Generate CSV data sources manually
- Schedule Online CSV data sources to run automatically
8. Open your Service Apps app and configure the chart you want to display on your dashboard using the Bar Chart widget.
- Add a data source that will read the data in Consolidated Stats.
- Add the Bar Chart widget.
- Select the data source and unselect the First Row Contains Dimension Elements option.
- Using the Data Transformer, select the columns according to the order in which they should appear in the chart.
![]() |
Data Transformer displaying all columns in Consolidated Stats |
![]() |
Select the four columns used for the Bar Chart.
|
Analyzing Service Apps applications using Google Analytics
Factor 1
Service Apps apps are apps that have a single Web page (Single Page Application – SPA). Their objective is to eliminate the loading of new pages with each action and streamline user experience. Certain apps can emulate browsing by changing the URL displayed in the Web browser, but new pages are never fully loaded.
The JavaScript tracking snippet inserted in your Web pages for the analytics.js library and for collecting audience analysis statistics works well with traditional websites and apps because the code is run each time users load a page. As such, with the official Google Analytics analytics.js library, it is difficult to collect detailed statistics for each page of your app. For example, you can obtain the total number of visitors or user sessions by country for the entire app. However, you cannot obtain the total number of visitors or user sessions for a specific page.
Factor 2
Service Apps apps with a single page have the following type of URL: https://yourcompanyname-apps.easyvista.com/index.php?timestamp=1463741371186&name=com.yourcompanyname.573eeb6962823|571e2218d3f5f&showapp=0.
In Google Analytics, you cannot create new properties if the website URL contains arguments, as is the case by default. If you try to, the following error message will appear.
To avoid this:
- Create one property for each app whose audience analysis statistics you want to collect in order to have a different tracking code for each of them.
- For each of these properties, enter a different website name, e.g. My Company Service Store, and enter the same root URL corresponding to your Service Apps platform, e.g. yourcompanyname-apps.easyvista.com.
- In each app page, insert a JavaScript tracking snippet with the tracking ID generated when you created the property, e.g. My Company Service Store, My University.
Note: Google authorizes the creation of 50 properties per Google Analytics account. If you have more than 50 apps on your Service Apps platform and you want to collect audience analysis statistics for all of them, you should contact your account manager or use another Google Analytics account.
How to add a JavaScript tracking snippet in an Service Apps app page?
- Configure a property in your Google Analytics account to generate a tracking ID.
See Set up a property to obtain a tracking ID, e.g. UA-XXXXXXXX-X.
- Copy the tracking code associated with the tracking ID.
See Find your tracking code, tracking ID, and property number.
- Paste the tracking code in a HTML Script widget.
- Add the widget to the app page.
- Paste the tracking code in the HTML Code field.
- Select Theme > Widget Height and specify 15, which is the minimum height of a widget.
- Click Border > Style and select none. In execute mode, this enables you to remove the frame surrounding the widget if a theme is used in the app. In this way, the Google Analytics tracking code will not be displayed.