About this integration

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.

Detailed description of the API

Step-by-step integration process

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:

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:
  • Google Analytics Core Reporting API (V4) - Dimensions & Metrics Explorer
  •  Analytics Multi-Channel Funnels Reporting API (V3) - Dimensions & Metrics Reference

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.

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 data sources (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.

• 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)

• My Website_GoogleAnalytics_Page Tracking.gsheet.xlsx
• My Website_GoogleAnalytics_Sessions.gsheet.xlsx
• My Website_GoogleAnalytics_Site Performance.gsheet.xlsx
• My Website_GoogleAnalytics_Users.gsheet.xlsx
• My Website_GoogleAnalytics_Platform or device.gsheet.xlsx

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.

(A) Week: Days of the week from Monday to Sunday *(D) Month: Week numbers in a month, never more than five weeks *(G) Year: Months in a calendar year from January to December *(K) empty: To differentiate groups of similar reports

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. The Year column is used in the x-axis. The three other columns are used in the y-axis to display trends in chronological order.

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.