Skip to main content

Google Analytics Pack

Ceré Rettig
  • Updated

Previously We Had. . .

GoogleAnalytics.png

v1.0a, v1.0b (1.0a had it's own database, 1.0b was changed to utilize your datastore with v10+)

Now We Have. . .

mceclip0.png

v1.1 (still utilizes the datastore database, but also some newer features since v11.3)

The HTML5 Google Analytics Pack is a set of 4 pre-built dashboards for you to use with some common Google Analytics metrics that will connect to YOUR Google Analytics account.  When loaded,  the dashboards will query Google Analytics data in real time, returning the most up to date metrics.

Please Note:  HTML5 refers to v10.0 and above.  You will also need to be a Data Admin to set this up. 

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

Dashboards: Traffic Sources, Site Analysis, Audience Attribute Details, GA - Site Analysis

KPIs

Please Note: All KPIs show past 30 days unless changed by Customer.

  • Ads Campaigns, Average Page Views per Session, Average Session Duration, Bounce Rate, Bounces, Campaign Traffic, Campaign Traffic by Source, Campaign Traffic by Campaign
  • Direct Traffic, Direct Traffic by Country, Direct Traffic by Landing Page, Keyword Ads Campaigns
  • Organic Search Traffic, Organic Search Traffic by Keyword, Organic Search Traffic by Source, Organic Search Traffic by Source (Top 10 by Visits)
  • Paid Search Traffic, Paid Search Traffic by Source, Paid Search Traffic by Keyword, Page Hits by Country, Page Hits by Country SVG Map, Page Views, Percent New Sessions
  • Referral Traffic, Referral Traffic by Source, Referral Traffic by Landing Page, Sessions, Sessions by Weekday, Sessions per Day
  • Top 10 Organic Search Sources by Visit, Top 10 Search Traffic by Social Media, Top 12 Pages by Sessions, Top 50 Organic Search Keywords by Visits
  • Unique Page Views, Users by Country, Users by Industry, Users by Mobile Device, Visits by Browser, Visits by Country, Visits by Keyword, Visits by Landing Page, Visits by Top 10 Mobile Devices

24 Hour View KPIs for LCD Display

  • Average Session Duration, Page Hits by Location GeoPlot Map, New Viewers, Percent New Session, Unique Pageviews, User Sessions

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

Google Analytics API Installation Instructions - Creating a SQL Database

Setting SQL Permissions - Running the SQL Script

Adding Your Data Source to iDashboards - Setting a Global ID for Your Data Source

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

What you will need:

  • Google Analytics Login
  • Google Analytics account
  • Google Analytics developers account (it's free)
  • iDashboards Data Admin Login
  • iDashboards http://<server:port>idashboards/
    • For your idb_archive
  • DataHub http://<server:port>/idbdata/
    • For your etl_archive
  • SQL Server Login (the one used for your iDashboards setup)
  • SQL Sever (MS SQL, MySQL, Oracle, PostgreSQL)
  • The GoogleAnalytics.zip downloaded onto your computer and unzipped.

What is in the GoogleAnalytics.zip?

There are: two zip files, the etlarchive which has pre-made ETL Jobs and Charts, and the idbarchive which has pre-made Dashboards, Charts and Picklists.  A Google Analytics MS SQL script, and a world-countries.svg that will need to be inserted into the Google Analytics: Site Analysis dashboard for four fully functional dashboards to connect to YOUR Google Analytics.

Google Analytics API Installation Instructions

Follow the Google Analytics API instructions for the section: Connecting to Google Analytics API.  This is the very first section of the article.  For this pack you will not need to create data sets or ETL jobs.  They come pre-made.  We will be importing them once the setup is complete.

Setting SQL Permissions - Running the SQL Script

Adding Your Data Source to iDashboards - Setting a Global ID for Your Data Source

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

Creating a SQL Database

We need to create your database in MS SQL (or MySQL, Oracle, PostgreSQL).  Your Data needs a place to live within the iDashboards structure.

  • Open MS SQL Server Management Studio (SSMS)
  • Connect to your server. ConnectToServerJPG.JPG
  • Right click on Databases
  • Select New Database
  • In the Database name box, type in idb_datastore
    • Please Note: You may have noticed in the gif, I used idb_GoogleAnalytics, so I had to change it for this tutorial.  Make sure you use idb_datastore or it will not connect correctly.
    • If idb_datastore already exists please move on to Setting SQL Permissions.
  • Click OK
  • You've now created the database where your Google Analytics information will live.CreateDatabase.gif

Google Analytics API Installation Instructions - Running the SQL Script

Adding Your Data Source to iDashboards - Setting a Global ID for Your Data Source

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

 

Setting SQL Permissions

  • Now we need to make sure your login used to connect SQL and your dashboards has the right permissions.
  • Open the Security folder
  • Open the Logins folder
  • Select your iDashboards SQL user login
  • Right Click on the user and select Properties
  • In the top left Select User Mapping
  • Click on idb_idbdatastore and make sure it is mapped to db _owner by clicking the box next to db_owner in the list below.
  • You've now set your SQL Permissions for the Google Analytics database.SQLPermissions_2020-04-23.gif

Google Analytics API Installation Instructions - Creating a SQL Database

Adding Your Data Source to iDashboards - Setting a Global ID for Your Data Source

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

Running the SQL Script

  • Go to File
  • Click on Open
  • Select File again
  • Select the Google Analytics Pack folder
  • Select the GoogleAnaltycis.sql file
  • Click the Execute button
  • The data, and ETL jobs, will now connect with your charts, picklists, and dashboards.GoogleAnalyticsSQL.gif

Google Analytics API Installation Instructions - Creating a SQL Database

Setting SQL Permissions - Setting a Global ID for Your Data Source

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

Adding Your Data Source to iDashboards

We need to add your data source. 

  • Log into your iDashboards Admin as Data Admin at your link:: http://<server:port>/idashboards
  • Click the Admin Icon
  • Go to Data Sources
  • Click New you will add it as a MS SQL Server (Microsoft Driver).  If you are using a different SQL database then please choose the appropriate database for YOUR SQL source. 
  • The picture below is how it will look:mceclip2.png

With your information input here, it will look similar to this picture below.  Instead of idb_GoogleAnalytics it will be idb_datastoremceclip3.png

Click Save and your Data Source will be added. 

It should look exactly like the outlined data source in red in the picture below. 

The asterisk (*) shows that it is connected, at this time there will not be any charts or data sets linked to it.  Once we are done setting up the Google Analytics pack you will be able to come here and see the linked Charts, Picklists, and Data Sets. 

DataSources_GA.png

Google Analytics API Installation Instructions - Creating a SQL Database

Setting SQL Permissions - Running the SQL Script

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

Setting a Global ID for Your Data Source

We will need to set a Global ID for your Data Source

  • Go to the iDashboards Data Admin console (Not Data Hub)
  • Go to the System drop down menu
  • Click on Import/Export 
  • Select Manage Global IDs
  • Click on Data Source
  • Type in IDB DATASTORE next to your Google Analytics Data Source you created in the previous step.
  • It will automatically save it.  *Those from Flash versions, yes, there is no longer an update button for each individual Global ID.DataSourceGloabal.gif

Google Analytics API Installation Instructions - Creating a SQL Database

Setting SQL Permissions - Running the SQL Script

Adding Your Data Source to iDashboards - Importing Data Sets and ETL Jobs

Importing Charts and Dashboards (idb_archive)

We can Import your Charts and Dashboards

  • Go to the iDashboards Data Admin console (Not Data Hub)
  • Go to the System drop down menu
  • Click on Import/Export 
  • Use the drop down menu that says Manage Global IDs to select Import Charts and Dashboards
  • Click the Choose File button
  • Go to where the Google Analytics Pack is located on your computer and Select the idb_archive zip file.
  • Click Open
  • Click Submit
  • Check to make sure all items show a Green Check Mark GreenCheck.JPG next to them
  • Click Import 
  • You will then see a message appear that your import was successful!ImportChartsDashboards.gif

Google Analytics API Installation Instructions - Creating a SQL Database

Setting SQL Permissions - Running the SQL Script

Adding Your Data Source to iDashboards - Setting a Global ID for Your Data Source

Importing Data Sets and ETL Jobs (etl_archive)

  • Log into your Data Hub as Data Admin at your link: http://<server:port>/idbdata
  • Click on the System drop down menu
  • Select Import/Export
  • From the Operation: drop down menu select Manage Global IDs, you will see the Google Analytics Data Source you created.
  • Click on API Accounts
  • Type in GOOGLE_ANALYTICS next to your Google Analytics API you created in a previous step.
  • Use the drop down menu that says Manage Global IDs to select Import Charts and Dashboards
  • Click the Choose File button
  • Go to where the Google Analytics Pack is located on your computer and Select the etl_archive zip file.
  • Click Open
  • Click Submit
  • Check to make sure all items show a Green Check Mark GreenCheck.JPG next to them
  • Click Import 
  • You will then see a message appear that your import was successful!
  • Congratulations!  You've successfully Installed the Google Analytics Pack! 

chrome_ii6c3oImxo.gif

 

Please Note: If your dashboards and charts do NOT show data, you may need to run your ETL jobs.  All ETL jobs in this pack are currently scheduled to run: Monday - Friday at 7:00am and 1:00pm.  These times can be changed to fit your business needs.

The Google Analytics Pack is delivered as-is. There will be no modification of the dashboards by the Support Services team. The TruOI Support Team has put a lot of effort into making sure that the dashboards contain as much information as can be provided. By downloading and installing the Google Analytics Pack the dashboard Administrator assumes responsibility for its usage and for understanding the data presented.

NO FURTHER DEVELOPMENT of the Google Analytics Pack WILL BE MADE by the TruOI Support Team.

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

Google Analytics API Installation Instructions - Creating a SQL Database

Setting SQL Permissions - Running the SQL Script

Adding Your Data Source to iDashboards - Setting a Global ID for Your Data Source

Importing Charts and Dashboards - Importing Data Sets and ETL Jobs

Comments

0 comments

Please sign in to leave a comment.