Integrating Google Analytics Data into a Dashboard

The ComponentOne 2020v3 release enhances the number of datasources supported by .NET Standard service library DataConnectors, which provides a set of connectors that can be used to connect to different types of data sources. Now, users can connect to six data sources, namely OData, Microsoft Dynamics 365 Sales, Salesforce Data, Kintone, QuickBooks Online, and Google Analytics, using an ADO.NET provider for each type. These providers have the same ADO.NET architecture as the native .NET data providers for SQL Server and OLEDB.

Hence, they ease the complexity of accessing data by letting users follow a similar set of classes available in the .NET architecture. These providers also have advantageous features such as authentication, caching, and SQL/LINQ query support. To add up to these features, the DataConnector also includes an Entity Framework (EF) Core provider for each type of datasource, which makes this library useful even when working with Entity Framework Core.

This blog will help users understand how to use the ADO.NET provider for Google Analytics to connect to the Google Analytics data. It will further explain the basic features, including authentication, querying, and caching, followed by describing the utilization of Google Analytics data to create a dashboard.

To get started, install the C1.DataConnector and C1.AdoNet.C1GoogleAnalytics packages from NuGet:

Explore all types of DataConnector and their features using Desktop DataConnector Explorer and Web-based DataConnector Explorer. In addition, download and install the ComponentOne DataConnectors service component from here and find the installed samples at the following location: 'C:\Users\xxxx\Documents\ComponentOne Samples\ServiceComponents\DataConnector'.

Connection

The first step towards working with the provider is to establish a connection to the Google Analytics data. The DataConnector service library includes the C1GoogleAnalyticsConnection class, just like the ADO.NET DataConnection class. It is used to establish a connection to the data by creating the connection object and passing the connection string as a parameter to the class constructor. The connection string is either predefined or generated using the C1GoogleAnalyticsConnectionStringBuilder class.

The ADO.NET provider for Google Analytics currently supports authorization using service account credentials. The service account credentials downloaded as a JSON file must be provided in the connection string using the KeyFile property. The ViewId for the Google Analytics view should be set through the ViewId property to make authorized access to Google Analytics data. Refer to the documentationfor details.

Here is a sample code snippet depicting how to generate a connection string and create a connection object:

//Configure connection string  
C1GoogleAnalyticsConnectionStringBuilder builder = new C1GoogleAnalyticsConnectionStringBuilder();  
builder.KeyFile = KeyFile;  
builder.ViewId = ViewId;  

//Fetch data  
C1GoogleAnalyticsConnection conn = new C1GoogleAnalyticsConnection(builder.ConnectionString))

Fetching Data

The ADO.NET provider for Google Analytics allows users to query against the data source using standard SQL syntax. The provider supports most SQL functionalities, including join queries, functions, and summaries. The Google Analytics data can be fetched either in the connected mode or disconnected mode using the C1GoogleAnalyticsCommand and C1GoogleAnalyticsDataAdapter objects. The sample code below depicts the READ operation using the C1GoogleAnalyticsDataAdapter object:

//Define command  
string sql = "SELECT Country, Sessions FROM Traffic WHERE Sessions > 500 AND StartDate = '14DaysAgo' AND EndDate = 'Today'";  

//Fetch data  
using (var con = new C1GoogleAnalyticsConnection(gaConnection))  
{  
   con.Open();  

   //Using C1GoogleAnalyticsDataAdapter  
   C1GoogleAnalyticsDataAdapter gaAdapter = new C1GoogleAnalyticsDataAdapter(con, sql);  
   DataTable dt = new DataTable();  
   gaAdapter.Fill(dt);  

   //Display fetched data  
   foreach (DataRow row in dt.Rows)  
   {  
     Console.WriteLine("{0}\t{1}", row["Country"], row["Sessions"]);  
   }  
}

Caching

The performance of the ADO.NET provider for Google Analytics can be enhanced by accessing the data from the cache when the user performs similar operations repeatedly. To support this type of functionality, the DataConnector library provides in-built caching support. We would need to configure the connection object to set the cache properties and enable cache for a connection. The UseCache property must be set to true, while customizing the default cache location by setting the CacheLocation property. The CacheTolerance property is used to set the cache tolerance time in seconds, with the default value set to 600 seconds.

Here is a sample code snippet which depicts how to enable caching by setting all the cache properties in the connection object:

//Configure Connection string  
string gaConnection = string.Format(@"Key File={0};View Id={1};Use Cache=true; Cache Tolerance=6; Cache Location='C:\temp\gacache.db';", KeyFile, ViewId);  

//Define command  
string sql = "SELECT Country, Sessions FROM Traffic WHERE Sessions > 500 AND StartDate = '14DaysAgo' AND EndDate = 'Today'";  

//Fetch data  
using (var con = new C1GoogleAnalyticsConnection(gaConnection))  
{  
  Console.WriteLine("Start Time " + DateTime.Now);  
  con.Open();  
  var command = con.CreateCommand();  
  command.CommandText = sql;  
  var reader = command.ExecuteReader();  
  while (reader.Read())  
  {  
     Console.WriteLine(String.Format("\t{0} --> \t\t{1}", reader["Country"], reader["Sessions"]));  
  }  
  Console.WriteLine("End Time " + DateTime.Now);  
}

Incremental Caching

The ADO.NET provider for Google Analytics provides another caching strategy known as Incremental Caching, which updates the cache data periodically to add new records from the source entities without refreshing the complete cache data. This technique relies on a unique field and a timestamp field which records the last time the record was updated, or a new record was inserted. Hence, any table which needs to implement incremental caching must have a Timestamp column. Google Analytics has a default timestamp column "Updated". The "IncrementalUpdate" and "IncrementalUpdateColumn" properties of C1GoogleAnalyticsConnectionStringBuilder class must be set to enable Incremental caching. For more details, refer to the documentation topic for Incremental caching.

The steps above have demonstrated how simple it is to connect to a new type of data source with a common interface based on a known data access technology such as ADO.NET. So, let's get our hands on something new by using something old.

For more detailed information, kindly refer to the DataConnector documentation.

Now let's create a dashboard to display the Google Analytics data.

Use Case: Website Traffic Dashboard

Google Analytics lets its users create different reports and dashboards to assist the expected audience in viewing the analytical data. When comparing both, dashboards are more appealing and convenient to understand as they can help the user visualize the information. Google Analytics dashboards are created using up to 12 widgets, whereas a widget is simply a visualization representing data, like a table or a graph. Though Google Dashboard is a free service and can be used by any user with a Google Analytics account, it limits users to understand and use the Google Analytics tool for the purpose, which might not help to work with Google Analytics data in a .Net application.

This example utilizes Google Analytics data to create a dashboard in a .Net application making use of the DashboardLayout control and other Winforms controls from the Winforms Edition of ComponentOne. The DashboardLayout control would help us design a dashboard, where as other Winforms controls will act as widgets to showcase the data in dashboard.

This example utilizes Google Analytics data to create a dashboard in a .Net application using the DashboardLayout control and other Winforms controls from the Winforms Edition of ComponentOne. The DashboardLayout control would help design a dashboard, whereas other Winforms controls will allow widgets to showcase the dashboard data.

We will create a basic dashboard to analyze the last month's traffic for a website in five specific countries. Here is the list of metrics that can be analyzed:

1. The total number of sessions from each country providing details of the number of new users, number of new sessions, and the landing page of each session/user.
2. Popular traffic sources directing the users to the website.
3. The average bounce rate of each country which would help understand in which country did the users quit at a single page view.
4. The average number of pages viewed per session in each country.
5. The average percentage of session duration in each country.

Here is a quick look at the dashboard we will be creating:

Let's understand how to create this dashboard by following the steps below:

Note: Refer to DashboardLayout control's documentation to gain an insight on the control before starting with these steps._

Configure Application:

1. Download and install ComponentOne Winforms Edition to start working with DashboardLayout control and other Winforms controls used to design the dashboard.
2. Create a new Winforms (.Net Framework) application.
3. Install C1.AdoNet.GoogleAnalytics package in the application with the help of Nuget Package Manager. Refer to the following linkfor detailed steps.
4. Drag-drop C1ThemeController control on the form to apply custom themes to the application or other controls. Refer to the following link for details.

Configure DashboardLayout Control:

1. Using the properties window, set the default Windows Form Width to 1210, height to 694, and FormBorderStyle to None.
2. Drag and drop the DashboardLayout control from the toolbox onto the form and set its Dock property to Fill.
3. The LayoutType property of DashboardLayout control is set to Split by default. Change the LayoutType property of DashboardLayout control to Grid using the properties window. This would split the control into two rows with three columns each, resulting in six cells by default.
4. Delete the panel contained in the first cell of each row.

This is how the DashboardLayout control would appear in Windows Form designer after following the steps listed above:

We will add controls to each of these cells to design a Dashboard used to visualize and analyze the website traffic.

Add controls to DashboardLayout:

To add controls to DashboardLayout, drag-drop control from the toolbox to a specific cell in the Grid layout of DashboardLayout control. As soon as users drop control in the cell, it will create a new panel in the cell and place the control within that panel.
Here are the steps to add controls to each of these cells:

1. Add a C1FlexGrid control to the first cell and set its Dock property to Fill. Notice the C1FlexGrid control is added in a Panel named "panel2" and set the Dock property of this panel to Fill.
2. Set the ColumnSpan property of the panel containing FlexGrid(panel2) to 2 to merge the first two cells in the first row. This is how the DashboardLayout control would look after adding the FlexGrid control and merging the cells.

3. Add FlexPie control to the last cell in the first row and set its Dock property to Fill.
4. Add FlexChart control to each of the three cells in the second row and set the Dock property of each FlexChart control to Fill. This is how the DashboardLayout control would look after adding the remaining controls:

Fetch Google Analytics Data using the ADO.NET provider for Google Analytics:

Define the following method in code file for Windows form to fetch the required data from Google Analytics using the ADO.NET provider for Google Analytics:

static DataSet ReadOperation()  
{  
  //Define Connection String  
  string gaConnection = string.Format("Key File={0};View Id={1}", KeyFile, ViewId);  

  //Define SQL commands  
  string gsql = "SELECT Country, Sessions, NewUsers, PercentNewSessions, LandingPagePath FROM Traffic WHERE Country = 'United States' OR Country = 'India' OR Country = 'Australia' OR Country = 'Canada' OR Country = 'United Kingdom'AND StartDate = '7DaysAgo' AND EndDate = 'Today'";  
   string psql = "SELECT Medium, Users FROM Traffic WHERE Medium = 'referral' OR Medium = 'organic' OR Medium = 'Click-to-Site' OR Medium = 'prospects' OR Medium = 'CPC' OR Medium = '(none)' AND Country = 'United States' OR Country = 'India' OR Country = 'Australia' OR Country = 'Canada' OR Country = 'United Kingdom'";      
    string csql = "SELECT Country, BounceRate, PageViewsPerSession, AvgSessionDuration FROM Traffic WHERE Country = 'United States' OR Country = 'India' OR Country = 'Australia' OR Country = 'Canada' OR Country = 'United Kingdom'";                   

        //Fetch data  
    using (var con = new C1GoogleAnalyticsConnection(gaConnection))  
    {  
       con.Open();  

       DataSet ds = new DataSet();  

       //Using C1GoogleAnalyticsDataAdapter  
       C1GoogleAnalyticsDataAdapter gaAdapter1 = new C1GoogleAnalyticsDataAdapter(con, gsql);  
       gaAdapter1.Fill(ds, "GridData");  

       C1GoogleAnalyticsDataAdapter gaAdapter2 = new C1GoogleAnalyticsDataAdapter(con, psql);  
       gaAdapter2.Fill(ds, "PieData");  

       C1GoogleAnalyticsDataAdapter gaAdapter3 = new C1GoogleAnalyticsDataAdapter(con, csql);  
       gaAdapter3.Fill(ds, "FlexChartData");  

       return ds;  
     }  
 }

Bind controls to fetched data:

Define the following methods in code file for Windows form to bind fetched data to all the controls placed in DashboardLayout control:

//Binding FlexGrid  
public static void BindFlexGrid(DataSet datasource, C1FlexGrid flexGrid, C1ThemeController themeController)  
{  
   //Binding FlexGrid to Datasource  
   flexGrid.DataSource = datasource.Tables[0];  
      //Apply theme to C1FlexGrid, refer to [link](/componentone/docs/win/online-themes/QSApplyingBuiltInThemetoApp.html) for details  
   themeController.SetTheme(flexGrid, "Office2016White");  
   //Styling FlexGrid  
   flexGrid.BorderStyle = C1.Win.C1FlexGrid.Util.BaseControls.BorderStyleEnum.None;  
   flexGrid.Rows[0].TextAlign = TextAlignEnum.CenterCenter;  
   flexGrid.Cols[0].Visible = false;  
   flexGrid.Cols[2].Width = 200;  
   flexGrid.Cols[2].Aggregate = AggregateEnum.Sum;  
   flexGrid.Cols[2].Format = "N0";  
   flexGrid.Cols[3].Aggregate = AggregateEnum.Sum;  
   flexGrid.Cols[3].Format = "N0";  
   flexGrid.Cols[4].Width = 150;  
   flexGrid.Cols[4].Aggregate = AggregateEnum.Average;  
   flexGrid.Cols[4].Format = "N2";  
   flexGrid.ExtendLastCol = true;  
   //Grouping FlexGrid  
   flexGrid.GroupDescriptions = new List<C1.Win.C1FlexGrid.GroupDescription>() { new C1.Win.C1FlexGrid.GroupDescription("Country") };  
   flexGrid.HideGroupedColumns = true;  
   for (var i = 1; i < flexGrid.Nodes.Length; i++)  
   {  
       flexGrid.Nodes[i].Collapsed = true;  
   }  
}  

//Binding FlexPie  
public static void BindFlexPie(DataSet datasource, FlexPie flexPie)  
{  
   //Passing data in FlexPie  
   flexPie.DataSource = datasource.Tables[1];  
   //Binding FlexPie's AxisX to 'Value' so values appear in Horizontal axis  
   flexPie.Binding = "Users";  
   flexPie.BindingName = "Medium";  
   flexPie.InnerRadius = 0.5;  
   //Specify what and how to show data values  
   flexPie.DataLabel.Content = "{name} : {p:0}%";  
   //Specify where to position the data labels relative to pie slices  
   flexPie.DataLabel.Position = PieLabelPosition.Radial;  
}  

//Binding FlexCharts  
public static void BindFlexCharts(DataSet datasource, FlexChart chart1, FlexChart chart2, FlexChart chart3)  
{  
   //Binding FlexChart1  
   chart1.Series.Clear();  
   chart1.DataSource = datasource.Tables[2];  
   chart1.ChartType = ChartType.Column;  
   chart1.AxisY.MajorGrid = false;  
   //Adding a Series to chart and binding it (AxisY) to 'BounceRate' field  
   chart1.Series.Add(new C1.Win.Chart.Series  
   {  
       Binding = "BounceRate"  
   });  
   //Binding chart's AxisX to 'Country'  
   chart1.BindingX = "Country";  

   //Binding FlexChart2  
   chart2.Series.Clear();  
   chart2.DataSource = datasource.Tables[2];  
   chart2.ChartType = ChartType.LineSymbols;  
   chart2.AxisY.MajorGrid = false;  
   //Adding a Series to chart and binding it (AxisY) to 'PageViewsPerSession' field  
   chart2.Series.Add(new C1.Win.Chart.Series  
   {  
      Binding = "PageViewsPerSession"  
   });  
   //Binding chart's AxisX to 'Country'  
   chart2.BindingX = "Country";  

   //Binding FlexChart3  
   chart3.Series.Clear();  
   chart3.DataSource = datasource.Tables[2];  
   chart3.ChartType = ChartType.LineSymbols;  
   chart3.AxisY.MajorGrid = false;  
   //Adding a Series to chart and binding it (AxisY) to 'AvgSessionDuration' field  
   chart3.Series.Add(new C1.Win.Chart.Series  
   {  
      Binding = "AvgSessionDuration"  
   });  
   //Binding chart's AxisX to 'Country'  
   chart3.BindingX = "Country";  
}

Styling Dashboard:

Change the appearance of the dashboard to make it look similar to a Google Analytics dashboard, by defining the following method which styles the DashboardLayout control:

public static void StylingDashboard(C1DashboardLayout dashboardLayout)  
{  
   DashboardTheme dashboardTheme = dashboardLayout.Styles;  
   dashboardTheme.Common.Padding = new C1.Framework.Thickness(10, 10, 10, 10);  
   dashboardTheme.ItemContainer.BackColor = Color.White;  
   dashboardTheme.ItemContainer.Margins = new C1.Framework.Thickness(10, 10, 10, 10);             
}

Generate Dashboard:

Invoke all the methods defined above in the Form_Load event to populate the Dashboard controls with data and style the dashboard. Here is the sample code snippet for the same:

private void Form1_Load(object sender, EventArgs e)  
{  
  DataSet ds = ReadOperation();  
  BindFlexGrid(ds, c1FlexGrid1, c1ThemeController1);  
  BindFlexPie(ds, flexPie1);  
  BindFlexCharts(ds, flexChart1, flexChart2, flexChart3);  
  StylingDashboard(c1DashboardLayout1);  

  //Set the titles for dashboard tile  
  c1DashboardLayout1.SetCaption(panel2, "Users/Sessions");  
  c1DashboardLayout1.SetCaption(panel7, "Traffic Sources");  
  c1DashboardLayout1.SetCaption(panel9, "Bounce Rate");  
  c1DashboardLayout1.SetCaption(panel11, "Page Views per Session");  
  c1DashboardLayout1.SetCaption(panel13, "Average Session Duration");  
 }

Save and execute the application to view the dashboard.

Similarly, users can create other Dashboards with the different controls available in Winforms edition by binding to other tables/views available in Google Analytics as explained in the Schema details provided here.

Get started with Dashboards and Google Analytics in a .Net application using the described approach.