Useful scripts for date functions

To get last completed month's startdate and enddate and its fiscalmonth, fiscalquarter and fiscalyear

SELECT ? = DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)

SELECT ? = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

DECLARE @DATE DATETIME, @Quarter INT

SET @DATE = (convert(varchar(20),(SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)),111))

SET @Quarter = Datepart(QQ,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))

SELECT ? = CASE WHEN (MONTH(@Date) > 9) THEN YEAR(@Date) + 1

ELSE YEAR(@Date) END -- AS FiscalYear

SELECT ?= CASE WHEN (MONTH(@Date) > 9) THEN MONTH(@Date) - 9

ELSE MONTH(@Date) + 3 END -- AS FiscalMonth

SELECT ?= (SELECT DISTINCT FiscalQuarter FROM DimDate WHERE QuarterNumber = @Quarter)

To get last completed quarter's startdate and enddate and its ficalquarter and fiscalmonth

DECLARE @Date DATETIME

,@StartOfQuarter DATETIME

,@EndOfQuarter DATETIME

,@FiscalYear INT

,@FiscalQuarter INT

SET @Date = GETDATE()

SELECT @FiscalYear = FiscalYear, @FiscalQuarter = FiscalQuarter FROM DimDate

WHERE CONVERT(DATE, DateValue) = CONVERT(DATE,@Date)

--SELECT @FiscalYear, @FiscalQuarter

IF @FiscalYear = YEAR(@Date)

BEGIN

SET @FiscalYear = @FiscalYear

SET @FiscalQuarter = @FiscalQuarter - 1

--Print @FiscalYear

END

ELSE

BEGIN

IF(@FiscalQuarter = 1)

BEGIN

SET @FiscalYear = @FiscalYear-1

SET @FiscalQuarter = 4

END

ELSE

BEGIN

SET @FiscalYear = @FiscalYear -1

SET @FiscalQuarter = @FiscalQuarter

END

END

SELECT ? = @FiscalYear

SELECT ? = @FiscalQuarter

SELECT @StartOfQuarter = (SELECT Min(datevalue) from dimdate where fiscalyear = @FiscalYear AND

fiscalquarter = @FiscalQuarter)

SELECT ? = @StartOfQuarter

SELECT @EndOfQuarter = (SELECT Max(datevalue) from dimdate where fiscalyear = @FiscalYear AND

fiscalquarter = @FiscalQuarter)

SELECT ? = @EndOfQuarter

To get FYTD's startdate and endate and its fiscalmonth, fiscalquarter and fiscalyear

DECLARE @Date DATETIME

,@FYTDStartDate DATETIME

,@FYTDEndDate DATETIME

,@FiscalYear INT ,@FiscalQuarter INT ,@Year INT

,@Month INT, @FiscalMonth INT

,@PreviousMonth DATETIME

SET @PreviousMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

SET @Date = GETDATE()

SELECT @FiscalYear = fiscalyear, @Month = fiscalmonth, @FiscalQuarter = FiscalQuarter FROM dimdate

WHERE CONVERT(DATE, datevalue) = CONVERT(DATE,@Date)

SELECT @FiscalQuarter = FiscalQuarter FROM dimdate

WHERE CONVERT(DATE, datevalue) = CONVERT(DATE,@PreviousMonth)

SET @FiscalMonth = @Month

SET @Year = @FiscalYear

IF @FiscalYear = YEAR(@Date)

BEGIN

SET @FiscalYear = @FiscalYear

SET @Year = @FiscalYear

SET @Month = 1

SET @FiscalMonth = @FiscalMonth-1

END

ELSE

BEGIN

IF(@Month = 1)

BEGIN

SET @FiscalYear = @FiscalYear-1

SET @Year = @FiscalYear

SET @Month = 1

SET @FiscalMonth = 12

END

ELSE

BEGIN

SET @FiscalYear = @FiscalYear-1

SET @Year = @FiscalYear+1

SET @Month = 1

SET @FiscalMonth = @FiscalMonth-1

END

END

SELECT ?= @Year

SELECT ?= @FiscalMonth

SELECT ?= @FiscalQuarter

SELECT @FYTDStartDate = (SELECT Min(datevalue) from dimdate where

(fiscalyear >= @Year AND fiscalyear <= @Year) AND

(fiscalmonth >= @Month AND fiscalmonth <= @FiscalMonth))

SELECT ?= @FYTDStartDate

SELECT @FYTDEndDate = (SELECT Max(datevalue) from dimdate where

(fiscalyear >= @FiscalYear AND fiscalyear <= @Year) AND

(fiscalmonth >= @Month AND fiscalmonth <= @FiscalMonth))

SELECT ?= @FYTDEndDate

Note: we need DimDate table 

Google Analytics Business Intelligence Project with SSIS

Schema to create DateControlMonth table

CREATE TABLE [web].[DateControlMonth](

[FiscalYear] [smallint] NULL,

[FiscalQuarter] [tinyint] NULL,

[FiscalMonth] [tinyint] NULL,

[FiscalYearStartDate] [date] NULL,

[FiscalYearEndDate] [date] NULL,

[FiscalMonthStartDate] [date] NULL,

[FiscalMonthEndDate] [date] NULL

)

Script to populate value in DateControlMonth table

SELECT dd.[FiscalYear]

,dd.FiscalQuarter

,dd.[FiscalMonth]

,(SELECT MIN(DD2.DateValue)

FROM [EDW20].[dbo].DimDate dd2

WHERE dd2.FiscalYear = DD.FiscalYear) AS [FiscalYearStartDate]

,(SELECT MAX(DD2.DateValue)

FROM [EDW20].[dbo].DimDate dd2

WHERE dd2.FiscalYear = DD.FiscalYear) AS [FiscalYearEndDate]

,MIN(dd.dateValue) [FiscalMonthStartDate]

,MAX(dd.dateValue) [FiscalMonthEndDate]

FROM [EDW20].[dbo].DimDate dd

WHERE FiscalYear > 2005 //provide year to get data from

GROUP BY dd.[FiscalYear]

,dd.FiscalQuarter

,dd.[FiscalMonth]

ORDER BY dd.[FiscalYear]

,dd.FiscalQuarter

,dd.[FiscalMonth]

Schema to create DateControlQuarter

CREATE TABLE [web].[DateControlQuarter](

[FiscalYear] [smallint] NULL,

[FiscalQuarter] [tinyint] NULL,

[FiscalQuarterStartDate] [date] NULL,

[FiscalQuarterEndDate] [date] NULL

)

Script to populate value in DateControlQuarter table

SELECT dd.[FiscalYear]

,dd.[FiscalQuarter]

,MIN(dd.dateValue) [FiscalQuarterStartDate]

,MAX(dd.dateValue) [FiscalQuarterEndDate]

FROM [EDW20].[dbo].DimDate dd

WHERE FiscalYear > 2005 //provide year to get data from

GROUP BY dd.[FiscalYear]

,dd.[FiscalQuarter]

ORDER BY dd.[FiscalYear]

,dd.[FiscalQuarter]

Schema to create SiteprofileHistory_Date

CREATE TABLE [web].[SiteProfileHistory_Date](

[UniqueId] [int] IDENTITY(1,1) NOT NULL,

[ProfileId] [varchar](24) NULL,

[FiscalYear] [smallint] NULL,

[FiscalMonth] [tinyint] NULL,

[FiscalQuarter] [tinyint] NULL,

[MonthStartDate] [date] NULL,

[MonthEndDate] [date] NULL,

[QuarterStartDate] [date] NULL,

[QuarterEndDate] [date] NULL,

[YearStartDate] [date] NULL,

[YearEndDate] [date] NULL

)

SSIS-ETL Steps to populate values in  SiteprofileHistory_Date

Schema to create SiteprofileHistoryDate_Quarter

CREATE TABLE [web].[SiteProfileHistoryDate_Quarter](

[UniqueId] [int] IDENTITY(1,1) NOT NULL,

[ProfileId] [varchar](24) NULL,

[FiscalQuarterStartDate] [date] NULL,

[FiscalQuarterEndDate] [date] NULL,

[FiscalYear] [smallint] NULL,

[FiscalQuarter] [tinyint] NULL

)

Script to populate value in SiteprofileHistoryDate_Quarter

SELECT DISTINCT [ProfileId]

,[FiscalYear]

,[FiscalQuarter]

,[QuarterStartDate]

,[QuarterEndDate]

FROM [web].[SiteProfileHistory_Date]

Schema to create SiteprofileHistoryDate_FYTD

CREATE TABLE [web].[SiteProfileHistoryDate_FYTD](

[UniqueId] [int] IDENTITY(1,1) NOT NULL,

[ProfileId] [varchar](24) NULL,

[FYTDStartDate] [date] NULL,

[FYTDEndDate] [date] NULL,

[FiscalYear] [smallint] NULL,

[FiscalMonth] [tinyint] NULL,

[FiscalQuarter] [tinyint] NULL

)

Script to populate value in SiteprofileHistoryDate_FYTD

SELECT DISTINCT [ProfileId]

,[FiscalYear]

,[FiscalMonth]

,[FiscalQuarter]

,[YearStartDate] AS FYTDStarteDate

,[MonthEndDate] AS FYTDEndDate

FROM [web].[SiteProfileHistory_Date]

Example table and package to retrieve value from Google Analytics for the last completed single month

Once the date related tasks have done, we can start with create table for PageDetailByURL and how to populate value to this table. The following schema is to create table for PageDetailByURL_SingleMonth which will load data for the last completed month.

We can also pull the data for the last completed quarter and FYTD.

CREATE TABLE [web].[PagesDetailByURL_SingleMonth](

[ProfileID] [int] NOT NULL,

[Interval] [varchar](20) NOT NULL,

[FiscalYear] [smallint] NULL,

[FiscalMonth] [tinyint] NULL,

[FiscalQuarter] [tinyint] NULL,

[PageURL] [nvarchar](max) NULL,

[PageViewCount] [int] NULL,

[UniquePageViewCount] [int] NULL,

[AvgTimeOnPage] [numeric](18, 2) NULL,

[EntranceCount] [int] NULL,

[BounceRate] [numeric](18, 2) NULL,

[ExitPercent] [numeric](18, 2) NULL,

[TotalValue] [numeric](18, 2) NULL,

[RecordExists] [bit] NULL,

[AuditTaskId] [int] NULL

)

Below is the example figures for ETL to load data in a table 

Created variables:

ControlFlow example:

Below is the script to get FromDate, ToDate, FiscalMonth, FiscalQuarter and FiscalYear those will be passed as parameter in Google Analytics to retrieve data for the particular period. (Last completed month data)

SELECT ? = DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)

SELECT ? = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

DECLARE @DATE DATETIME, @Quarter INT

SET @DATE = (convert(varchar(20),(SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)),111))

SET @Quarter = Datepart(QQ,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))

SELECT ? = CASE WHEN (MONTH(@Date) > 9) THEN YEAR(@Date) + 1

ELSE YEAR(@Date) END -- AS FiscalYear

SELECT ?= CASE WHEN (MONTH(@Date) > 9) THEN MONTH(@Date) - 9

ELSE MONTH(@Date) + 3 END -- AS FiscalMonth

SELECT ?= (SELECT DISTINCT FiscalQuarter FROM DimDate WHERE QuarterNumber = @Quarter)

Note: we need DimDate table to bring fiscal quarter

DataFlowTask steps:

We need to select variables to pass in the script component coding area.

Once the output columns created in SCR task, Click on EditScript to go the VisaProjects. In the Solution Explorer screen, Right click on – References – click Add References – Select the DLL file as given in the below figure.

In the main.cs file, write the below coding.

#region Help: Introduction to the Script Component

/* The Script Component allows you to perform virtually any operation that can be accomplished in

* a .Net application within the context of an Integration Services data flow.

*

* Expand the other regions which have "Help" prefixes for examples of specific ways to use

* Integration Services features within this script component. */

#endregion

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Windows.Forms;

using GoogleAnalyticsSupport;

#endregion

.................
.................etc,.

public override void CreateNewOutputRows()

{

ReportRequestorWithSorting RR = new ReportRequestorWithSorting();

RR.Email = Variables.UserName; //"test_googleanalytics@mycompany.org";

RR.Password = Variables.Password; // "abcdefgh";

DateTime value =Variables.FromDate; //new DateTime(2010, 9, 1);

DateTime value1 = Variables.ToDate; //new DateTime(2012, 9, 26);

AnalyticsAccountInfo AAI = new AnalyticsAccountInfo();

AAI.AccountName = Variables.AccountName; //"myaccount";

AAI.WebPropertyID = Variables.WebPropertyId; //"http://myaccount.abcd.com";

AAI.ProfileID = Convert.ToString(Variables.ProfileID); // "123456";

AAI.ID = Variables.ID; // "ga:123456";

AAI.Title = Variables.Title; // "myaccount.abcd.com";

var PagesDetailByURL = RR.ReportRequestWF(AAI, new Dimension[] { Dimension.pagePath }

,new Metric[7]{Metric.pageviews,Metric.uniquePageviews

,Metric.avgTimeOnPage,Metric.entrances,Metric.visitBounceRate,Metric.exitRate

,Metric.totalValue},new Sort[]{Sort.pageviews},value, value1);

foreach (var p in PagesDetailByURL)

{

Output0Buffer.AddRow();

Output0Buffer.PageURL = p.Dimensions[0].Value.ToString();

Output0Buffer.PageViewCount = p.Metrics[0].Value.ToString();

Output0Buffer.UniquePageViewCount = p.Metrics[1].Value.ToString();

Output0Buffer.AvgTimeOnPage = p.Metrics[2].Value.ToString();

Output0Buffer.EntranceCount = p.Metrics[3].Value.ToString();

Output0Buffer.BounceRate = p.Metrics[4].Value.ToString();

Output0Buffer.ExitPercent = p.Metrics[5].Value.ToString();

Output0Buffer.TotalValue = p.Metrics[6].Value.ToString();

Output0Buffer.SiteProfileID = AAI.ProfileID.ToString();

}

}

}

Example table and package to retrieve data from the inception date of Google Analytics

The following schema is to create table for PageDetailByURLHistory_SingleMonth which will load data for the from the inception date to the last completed month.

We can also pull the data for the quarter and FYTD.

CREATE TABLE [web].[PagesDetailByURLHistory_SingleMonth](

[ProfileID] [int] NOT NULL,

[Interval] [varchar](20) NULL,

[FiscalYear] [smallint] NULL,

[FiscalMonth] [tinyint] NULL,

[FiscalQuarter] [tinyint] NULL,

[PageURL] [nvarchar](max) NULL,

[PageViewCount] [int] NULL,

[UniquePageViewCount] [int] NULL,

[AvgTimeOnPage] [numeric](18, 2) NULL,

[EntranceCount] [int] NULL,

[BounceRate] [numeric](18, 2) NULL,

[ExitPercent] [numeric](18, 2) NULL,

[TotalValue] [numeric](18, 2) NULL,

[RecordExists] [bit] NULL,

[AuditTaskId] [int] NULL

)

Below is the example figures for ETL to load data in a table 

Created variables:

ControlFlow Steps

DataFlowTask steps:

We need to select variables to pass in the script component coding area.

Once the output columns created in SCR task, Click on EditScript to go the VisaProjects. In the Solution Explorer screen, Right click on – References – click Add References – Select the DLL file as given in the below figure.

In the main.cs file, write the below coding.

#region Help: Introduction to the Script Component

/* The Script Component allows you to perform virtually any operation that can be accomplished in

* a .Net application within the context of an Integration Services data flow.

*

* Expand the other regions which have "Help" prefixes for examples of specific ways to use

* Integration Services features within this script component. */

#endregion

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Windows.Forms;

using GoogleAnalyticsSupport;

#endregion

.............................
....................etc,.

public override void CreateNewOutputRows()

{

ReportRequestorWithSorting RR = new ReportRequestorWithSorting();

RR.Email = Variables.UserName; //"test_googleanalytics@mycompany.org";

RR.Password = Variables.Password; // "abcdef";

DateTime value =Variables.FromDate; //new DateTime(2010, 9, 1);

DateTime value1 = Variables.ToDate; //new DateTime(2012, 9, 26);

AnalyticsAccountInfo AAI = new AnalyticsAccountInfo();

AAI.AccountName = Variables.AccountName; //"myaccount";

AAI.WebPropertyID = Variables.WebPropertyId; //"http://myaccount.abcd.com";

AAI.ProfileID = Convert.ToString(Variables.ProfileID); // "12345";

AAI.ID = Variables.ID; // "ga:12345";

AAI.Title = Variables.Title; // "myaccount.abcd.com";

int fiscalYear = Variables.FiscalYear;

int fiscalMonth = Variables.FiscalMonth;

int fiscalQuarter = Variables.FiscalQuarter;

var PagesDetailByURL = RR.ReportRequestWF(AAI, new Dimension[] { Dimension.pagePath }

,new Metric[7]{Metric.pageviews,Metric.uniquePageviews

,Metric.avgTimeOnPage,Metric.entrances,Metric.visitBounceRate,Metric.exitRate

,Metric.totalValue},new Sort[]{Sort.pageviews},value, value1);

foreach (var p in PagesDetailByURL)

{

Output0Buffer.AddRow();

Output0Buffer.PageURL = p.Dimensions[0].Value.ToString();

Output0Buffer.PageViewCount = p.Metrics[0].Value.ToString();

Output0Buffer.UniquePageViewCount = p.Metrics[1].Value.ToString();

Output0Buffer.AvgTimeOnPage = p.Metrics[2].Value.ToString();

Output0Buffer.EntranceCount = p.Metrics[3].Value.ToString();

Output0Buffer.BounceRate = p.Metrics[4].Value.ToString();

Output0Buffer.ExitPercent = p.Metrics[5].Value.ToString();

Output0Buffer.TotalValue = p.Metrics[6].Value.ToString();

Output0Buffer.SiteProfileID = AAI.ProfileID.ToString();

Output0Buffer.FiscalMonth = fiscalMonth;

Output0Buffer.FiscalYear = fiscalYear;

Output0Buffer.FiscalQuarter = fiscalQuarter;

}

}

}

Creating Delay function using script component task where there is an issue in network. Can set the sleep timing according to the requirement.

public void Main()

{

// TODO: Add your code here

System.Threading.Thread.Sleep(9000);

Dts.TaskResult = (int)ScriptResults.Success;

}

Google Analytics Business Intelligence Project with C-Sharp

The following information will provide the detailed steps on how to navigate data in Google Analytics through Query Explorer and how to retrieve data from GA to Integration service using C sharp coding.

API Query explorer URL: http://ga-dev-tools.appspot.com/explorer/

First we can see creating class files to write coding to retrieve data from Google Analytics

Open – MS Visual Studio 2012

Go to – File – New – Project and proceed as given in the below figure:

Click Ok to go to class file screen as given in the below figures:

Name the class file and type coding.

Can add the class files by right click on the Project Name – Add – Class – name it and save.

Below are the list of class files needed to retrieve data from Google Analytics

AnalyticsAccountInfo.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace GoogleAnalyticsSupport

{

public class AnalyticsAccountInfo

{

#region Fields

private string _id = null;

private string _title = null;

private string _accountId = null;

private string _accountName = null;

private string _profileId = null;

private string _webProperty = null;

#endregion

#region Properties

public string ID

{

get { return _id; }

set { _id = value; }

}

public string Title

{

get { return _title; }

set { _title = value; }

}

public string AccountID

{

get { return _accountId; }

set { _accountId = value; }

}

public string AccountName

{

get { return _accountName; }

set { _accountName = value; }

}

public string ProfileID

{

get { return _profileId; }

set { _profileId = value; }

}

public string WebPropertyID

{

get { return _webProperty; }

set { _webProperty = value; }

}

#endregion

}

}

Dimension.cs

All the possible dimensions are added in the class file to use in the script component task in SSIS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace GoogleAnalyticsSupport

{

public enum Dimension

{

visitorType,

visitCount,

daysSinceLastVisit,

userDefinedValue,

visitLength,

referralPath,

campaign,

source,

medium,

keyword,

adContent,

socialNetwork,

hasSocialSourceReferral,

adGroup,

adSlot,

adSlotPosition,

adDistributionNetwork,

adMatchType,

adMatchedQuery,

adPlacementDomain,

adPlacementUrl,

adFormat,

adTargetingType,

adTargetingOption,

adDisplayUrl,

adDestinationUrl,

adwordsCustomerID,

adwordsCampaignID,

adwordsAdGroupID,

adwordsCreativeID,

adwordsCriteriaID,

browser,

browserVersion,

operatingSystem,

operatingSystemVersion,

isMobile,

mobileDeviceBranding,

mobileDeviceModel,

mobileInputSelector,

mobileDeviceInfo,

continent,

subContinent,

country,

metro,

region,

city,

latitude,

longitude,

networkDomain,

networkLocation,

flashVersion,

javaEnabled,

language,

screenColors,

screenResolution,

socialActivityEndorsingUrl,

socialActivityDisplayName,

socialActivityPost,

socialActivityTimestamp,

socialActivityUserHandle,

socialActivityUserPhotoUrl,

socialActivityUserProfileUrl,

socialActivityContentUrl,

socialActivityTagsSummary,

socialActivityAction,

socialActivityNetworkAction,

hostname,

pagePath,

pagePathLevel1,

pagePathLevel2,

pagePathLevel3,

pagePathLevel4,

pageTitle,

landingPagePath,

secondPagePath,

exitPagePath,

previousPagePath,

nextPagePath,

pageDepth,

searchUsed,

searchKeyword,

searchKeywordRefinement,

searchCategory,

searchStartPage,

searchDestinationPage,

eventCategory,

eventAction,

eventLabel,

transactionId,

affiliation,

visitsToTransaction,

daysToTransaction,

productSku,

productName,

productCategory,

socialInteractionNetwork,

socialInteractionAction,

socialInteractionNetworkAction,

socialInteractionTarget,

userTimingCategory,

userTimingLabel,

userTimingVariable,

customVarNamen,

customVarValuen,

date,

month,

year,

week,

day,

hour,

nthMonth,

nthWeek,

nthDay,

dayOfWeek

}

}

Metric.cs

All the possible metics are added in the class file to use in the script component task in SSIS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace GoogleAnalyticsSupport

{

public enum Metric

{

visitors,

newVisits,

percentNewVisits,

visits,

bounces,

entranceBounceRate,

visitBounceRate,

timeOnSite,

avgTimeOnSite,

organicSearches,

impressions,

adClicks,

adCost,

CPM,

CPC,

CTR,

costPerTransaction,

costPerGoalConversion,

costPerConversion,

RPC,

ROI,

margin,

goalnStarts,

goalStartsAll,

goalnCompletions,

goalCompletionsAll,

goalnValue,

goalValueAll,

goalValuePerVisit,

goalnConversionRate,

goalConversionRateAll,

goalnAbandons,

goalAbandonsAll,

goalnAbandonRate,

goalAbandonRateAll,

socialActivities,

entrances,

entranceRate,

pageviews,

pageviewsPerVisit,

uniquePageviews,

timeOnPage,

avgTimeOnPage,

exits,

exitRate,

searchResultViews,

searchUniques,

avgSearchResultViews,

searchVisits,

percentVisitsWithSearch,

searchDepth,

avgSearchDepth,

searchRefinements,

searchDuration,

avgSearchDuration,

searchExits,

searchExitRate,

searchGoalnConversionRate,

searchGoalConversionRateAll,

goalValueAllPerSearch,

pageLoadTime,

pageLoadSample,

avgPageLoadTime,

domainLookupTime,

avgDomainLookupTime,

pageDownloadTime,

avgPageDownloadTime,

redirectionTime,

avgRedirectionTime,

serverConnectionTime,

avgServerConnectionTime,

avgServerResponseTime,

serverResponseTime,

speedMetricsSample,

appviews,

uniqueAppviews,

appviewsPerVisit,

totalEvents,

uniqueEvents,

eventValue,

avgEventValue,

visitsWithEvent,

eventsPerVisitWithEvent,

transactions,

transactionsPerVisit,

transactionRevenue,

revenuePerTransaction,

transactionRevenuePerVisit,

transactionShipping,

transactionTax,

totalValue,

itemQuantity,

uniquePurchases,

revenuePerItem,

itemRevenue,

itemsPerPurchase,

socialInteractions,

uniqueSocialInteractions,

socialInteractionsPerVisit,

userTimingValue,

userTimingSample,

avgUserTimingValue,

exceptions,

fatalExceptions

}

}

GenericEntry.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace GoogleAnalyticsSupport

{

public class GenericEntry

{

#region Fields

List<KeyValuePair<Dimension, string>> _dimensions = null;

List<KeyValuePair<Metric, string>> _metrics = null;

List<KeyValuePair<Sort, string>> _sorts = null;

#endregion

#region Properties

public List<KeyValuePair<Dimension, string>> Dimensions

{

get { return _dimensions; }

set { _dimensions = value; }

}

public List<KeyValuePair<Metric, string>> Metrics

{

get { return _metrics; }

set { _metrics = value; }

}

public List<KeyValuePair<Sort, string>> Sorts

{

get { return _sorts; }

set { _sorts = value; }

}

#endregion

}

}

ReportRequestor.cs

It is a common class file to retrieve data with dimensions and metrics

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Net;

using System.IO;

using System.Xml.Linq;

using System.Globalization;

namespace GoogleAnalyticsSupport

{

public class ReportRequestor

{

#region Fields

private static readonly string requestUrlFormat = "https://www.google.com/analytics/feeds/data?ids={0}&dimensions={1}&metrics={2}&start-date={3}&end-date={4}&start-index={5}&max-results={6}";

private static readonly string authUrlFormat = "accountType=GOOGLE&Email={0}&Passwd={1}&source=reimers.dk-analyticsreader-0.1&service=analytics";

private static CultureInfo ci = CultureInfo.GetCultureInfo("en-US");

private string _token = null;

private string _username = null;

private string _password = null;

#endregion

#region Constructor

public ReportRequestor() { }

public ReportRequestor(string email, string password)

{

_username = email;

_password = password;

}

#endregion

#region Properties

public string Email

{

get { return _username; }

set

{

if (!string.Equals(_username, value))

{

_username = value;

_token = null;

}

}

}

public string Password

{

get { return _password; }

set

{

if (!string.Equals(_password, value))

{

_password = value;

_token = null;

}

}

}

#endregion

#region Methods

private string GetToken(string username, string password)

{

if (string.IsNullOrEmpty(_username) || string.IsNullOrEmpty(_password))

{

throw new ArgumentNullException("Username, Password", "Username and/or password not set");

}

string authBody = string.Format(authUrlFormat, username, password);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/accounts/ClientLogin");

req.Method = "POST";

req.ContentType = "application/x-www-form-urlencoded";

req.UserAgent = "Example.dk req";

Stream stream = req.GetRequestStream();

StreamWriter sw = new StreamWriter(stream);

sw.Write(authBody);

sw.Close();

sw.Dispose();

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

StreamReader sr = new StreamReader(response.GetResponseStream());

string token = sr.ReadToEnd();

string[] tokens = token.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);

foreach (string item in tokens)

{

if (item.StartsWith("Auth="))

{

return item.Replace("Auth=", "");

}

}

return string.Empty;

}

public IEnumerable<AnalyticsAccountInfo> GetAccounts()

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/analytics/feeds/accounts/default");

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

StreamReader sr = new StreamReader(responseStream);

string responseXml = sr.ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var entries = from en in doc.Root.Descendants(defaultSpace + "entry")

select new AnalyticsAccountInfo

{

AccountID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountId").First().Attribute("value").Value,

AccountName = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountName").First().Attribute("value").Value,

ID = en.Element(defaultSpace + "id").Value,

Title = en.Element(defaultSpace + "title").Value,

ProfileID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:profileId").First().Attribute("value").Value,

WebPropertyID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:webPropertyId").First().Attribute("value").Value

};

return entries;

}

private XDocument getReport(AnalyticsAccountInfo account, IEnumerable<Dimension> dimensions, IEnumerable<Metric> metrics, DateTime from, DateTime to, int startindex=1, int maxresults = 10000)

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

StringBuilder dims = new StringBuilder();

foreach (Dimension item in dimensions)

{

dims.Append("ga:" + item.ToString() + ",");

}

StringBuilder mets = new StringBuilder();

foreach (Metric item in metrics)

{

mets.Append("ga:" + item.ToString() + ",");

}

string requestUrl = string.Format(requestUrlFormat, "ga:" + account.ProfileID, dims.ToString().Trim(",".ToCharArray()), mets.ToString().Trim(",".ToCharArray()), from.ToString("yyyy-MM-dd"), to.ToString("yyyy-MM-dd"), startindex, maxresults);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(requestUrl);

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

string responseXml = new StreamReader(responseStream, Encoding.UTF8, true).ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

return doc;

}

public IEnumerable<GenericEntry> RequestReport(AnalyticsAccountInfo account, IEnumerable<Dimension> dimensions, IEnumerable<Metric> metrics, DateTime from, DateTime to,int startindex=1, int maxresults=10000)

{

XDocument doc = getReport(account, dimensions, metrics, from, to, startindex, maxresults);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var gr = from r in doc.Root.Descendants(defaultSpace + "entry")

select new GenericEntry

{

Dimensions = new List<KeyValuePair<Dimension, string>>(

from rd in r.Elements(dxpSpace + "dimension")

select new KeyValuePair<Dimension, string>(

(Dimension)Enum.Parse(

typeof(Dimension),

rd.Attribute("name").Value.Replace("ga:", ""),

true),

rd.Attribute("value").Value)),

Metrics = new List<KeyValuePair<Metric, string>>(

from rm in r.Elements(dxpSpace + "metric")

select new KeyValuePair<Metric, string>(

(Metric)Enum.Parse(typeof(Metric), rm.Attribute("name").Value.Replace("ga:", ""), true),

rm.Attribute("value").Value))

};

return gr;

}

#endregion

}

}

ReportRequestorWithoutDimension.cs

It is a class file to retrieve data without having dimensions. Metrics value only.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Net;

using System.IO;

using System.Xml.Linq;

using System.Globalization;

namespace GoogleAnalyticsSupport

{

public class ReportRequestorWithoutDimension

{

#region Fields

private static readonly string requestUrlFormat = "https://www.google.com/analytics/feeds/data?ids={0}&metrics={1}&start-date={2}&end-date={3}&max-results={4}";

private static readonly string authUrlFormat = "accountType=GOOGLE&Email={0}&Passwd={1}&source=reimers.dk-analyticsreader-0.1&service=analytics";

private static CultureInfo ci = CultureInfo.GetCultureInfo("en-US");

private string _token = null;

private string _username = null;

private string _password = null;

#endregion

#region Constructor

public ReportRequestorWithoutDimension() { }

public ReportRequestorWithoutDimension(string email, string password)

{

_username = email;

_password = password;

}

#endregion

#region Properties

public string Email

{

get { return _username; }

set

{

if (!string.Equals(_username, value))

{

_username = value;

_token = null;

}

}

}

public string Password

{

get { return _password; }

set

{

if (!string.Equals(_password, value))

{

_password = value;

_token = null;

}

}

}

#endregion

#region Methods

private string GetToken(string username, string password)

{

if (string.IsNullOrEmpty(_username) || string.IsNullOrEmpty(_password))

{

throw new ArgumentNullException("Username, Password", "Username and/or password not set");

}

string authBody = string.Format(authUrlFormat, username, password);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/accounts/ClientLogin");

req.Method = "POST";

req.ContentType = "application/x-www-form-urlencoded";

req.UserAgent = "Example.dk req";

Stream stream = req.GetRequestStream();

StreamWriter sw = new StreamWriter(stream);

sw.Write(authBody);

sw.Close();

sw.Dispose();

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

StreamReader sr = new StreamReader(response.GetResponseStream());

string token = sr.ReadToEnd();

string[] tokens = token.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);

foreach (string item in tokens)

{

if (item.StartsWith("Auth="))

{

return item.Replace("Auth=", "");

}

}

return string.Empty;

}

public IEnumerable<AnalyticsAccountInfo> GetAccounts()

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/analytics/feeds/accounts/default");

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

StreamReader sr = new StreamReader(responseStream);

string responseXml = sr.ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var entries = from en in doc.Root.Descendants(defaultSpace + "entry")

select new AnalyticsAccountInfo

{

AccountID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountId").First().Attribute("value").Value,

AccountName = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountName").First().Attribute("value").Value,

ID = en.Element(defaultSpace + "id").Value,

Title = en.Element(defaultSpace + "title").Value,

ProfileID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:profileId").First().Attribute("value").Value,

WebPropertyID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:webPropertyId").First().Attribute("value").Value

};

return entries;

}

private XDocument getReport(AnalyticsAccountInfo account, IEnumerable<Metric> metrics, DateTime from, DateTime to, int maxresults=10000)

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

StringBuilder mets = new StringBuilder();

foreach (Metric item in metrics)

{

mets.Append("ga:" + item.ToString() + ",");

}

string requestUrl = string.Format(requestUrlFormat, "ga:" + account.ProfileID,mets.ToString().Trim(",".ToCharArray()), from.ToString("yyyy-MM-dd"), to.ToString("yyyy-MM-dd"), maxresults);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(requestUrl);

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

string responseXml = new StreamReader(responseStream, Encoding.UTF8, true).ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

return doc;

}

public IEnumerable<GenericEntry> RequestReportwod(AnalyticsAccountInfo account

, IEnumerable<Metric> metrics, DateTime from, DateTime to, int maxresults=10000)

{

XDocument doc = getReport(account, metrics, from, to, maxresults);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var gr = from r in doc.Root.Descendants(defaultSpace + "entry")

select new GenericEntry

{

Dimensions = new List<KeyValuePair<Dimension, string>>(

from rd in r.Elements(dxpSpace + "dimension")

select new KeyValuePair<Dimension, string>(

(Dimension)Enum.Parse(

typeof(Dimension),

rd.Attribute("name").Value.Replace("ga:", ""),

true),

rd.Attribute("value").Value)),

Metrics = new List<KeyValuePair<Metric, string>>(

from rm in r.Elements(dxpSpace + "metric")

select new KeyValuePair<Metric, string>(

(Metric)Enum.Parse(typeof(Metric), rm.Attribute("name").Value.Replace("ga:", ""), true),

rm.Attribute("value").Value))

};

return gr;

}

#endregion

}

}

ReportRequestorWithSorting.cs

It is a class file to retrieve data by sorting in descending and ascending order

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Net;

using System.IO;

using System.Xml.Linq;

using System.Globalization;

namespace GoogleAnalyticsSupport

{

public class ReportRequestorWithSorting

{

#region Fields

private static readonly string requestUrlFormat = "https://www.google.com/analytics/feeds/data?ids={0}&dimensions={1}&metrics={2}&sort={3}&start-date={4}&end-date={5}&start-index={6}&max-results={7}";

private static readonly string authUrlFormat = "accountType=GOOGLE&Email={0}&Passwd={1}&source=reimers.dk-analyticsreader-0.1&service=analytics";

private static CultureInfo ci = CultureInfo.GetCultureInfo("en-US");

private string _token = null;

private string _username = null;

private string _password = null;

#endregion

#region Constructor

public ReportRequestorWithSorting() { }

public ReportRequestorWithSorting(string email, string password)

{

_username = email;

_password = password;

}

#endregion

#region Properties

public string Email

{

get { return _username; }

set

{

if (!string.Equals(_username, value))

{

_username = value;

_token = null;

}

}

}

public string Password

{

get { return _password; }

set

{

if (!string.Equals(_password, value))

{

_password = value;

_token = null;

}

}

}

#endregion

#region Methods

private string GetToken(string username, string password)

{

if (string.IsNullOrEmpty(_username) || string.IsNullOrEmpty(_password))

{

throw new ArgumentNullException("Username, Password", "Username and/or password not set");

}

string authBody = string.Format(authUrlFormat, username, password);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/accounts/ClientLogin");

req.Method = "POST";

req.ContentType = "application/x-www-form-urlencoded";

req.UserAgent = "Example.dk req";

Stream stream = req.GetRequestStream();

StreamWriter sw = new StreamWriter(stream);

sw.Write(authBody);

sw.Close();

sw.Dispose();

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

StreamReader sr = new StreamReader(response.GetResponseStream());

string token = sr.ReadToEnd();

string[] tokens = token.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);

foreach (string item in tokens)

{

if (item.StartsWith("Auth="))

{

return item.Replace("Auth=", "");

}

}

return string.Empty;

}

public IEnumerable<AnalyticsAccountInfo> GetAccounts()

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/analytics/feeds/accounts/default");

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

StreamReader sr = new StreamReader(responseStream);

string responseXml = sr.ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var entries = from en in doc.Root.Descendants(defaultSpace + "entry")

select new AnalyticsAccountInfo

{

AccountID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountId").First().Attribute("value").Value,

AccountName = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountName").First().Attribute("value").Value,

ID = en.Element(defaultSpace + "id").Value,

Title = en.Element(defaultSpace + "title").Value,

ProfileID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:profileId").First().Attribute("value").Value,

WebPropertyID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:webPropertyId").First().Attribute("value").Value

};

return entries;

}

private XDocument getReport(AnalyticsAccountInfo account, IEnumerable<Dimension> dimensions, IEnumerable<Metric> metrics

, IEnumerable<Sort> sorts, DateTime from, DateTime to, int startindex = 1, int maxresults = 250)

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

StringBuilder dims = new StringBuilder();

foreach (Dimension item in dimensions)

{

dims.Append("ga:" + item.ToString() + ",");

}

StringBuilder mets = new StringBuilder();

foreach (Metric item in metrics)

{

mets.Append("ga:" + item.ToString() + ",");

}

StringBuilder srt = new StringBuilder();

foreach (Sort item in sorts)

{

srt.Append("-" + "ga:" + item.ToString() + ",");

}

string requestUrl = string.Format(requestUrlFormat, "ga:" + account.ProfileID, dims.ToString().Trim(",".ToCharArray()), mets.ToString().Trim(",".ToCharArray())

, srt.ToString().Trim(",".ToCharArray()), from.ToString("yyyy-MM-dd"), to.ToString("yyyy-MM-dd"), startindex, maxresults);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(requestUrl);

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

string responseXml = new StreamReader(responseStream, Encoding.UTF8, true).ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

return doc;

}

public IEnumerable<GenericEntry> ReportRequestWF(AnalyticsAccountInfo account, IEnumerable<Dimension> dimensions, IEnumerable<Metric> metrics

, IEnumerable<Sort> sorts, DateTime from, DateTime to, int startindex = 1, int maxresults = 250)

{

XDocument doc = getReport(account, dimensions, metrics

, sorts, from, to, startindex, maxresults);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var gr = from r in doc.Root.Descendants(defaultSpace + "entry")

select new GenericEntry

{

Dimensions = new List<KeyValuePair<Dimension, string>>(

from rd in r.Elements(dxpSpace + "dimension")

select new KeyValuePair<Dimension, string>(

(Dimension)Enum.Parse(

typeof(Dimension),

rd.Attribute("name").Value.Replace("ga:", ""),

true),

rd.Attribute("value").Value)),

Metrics = new List<KeyValuePair<Metric, string>>(

from rm in r.Elements(dxpSpace + "metric")

select new KeyValuePair<Metric, string>(

(Metric)Enum.Parse(typeof(Metric), rm.Attribute("name").Value.Replace("ga:", ""), true),

rm.Attribute("value").Value)),

Sorts = new List<KeyValuePair<Sort, string>>(

from rs in r.Elements(dxpSpace + "sort")

select new KeyValuePair<Sort, string>(

(Sort)Enum.Parse(typeof(Sort), rs.Attribute("name").Value.Replace("ga:", ""), true),

rs.Attribute("value").Value))

};

return gr;

}

#endregion

}

}

ReportRequestorWithFilterandSort.cs

It is a class file to retrieve data with filtering by some conditions and sorting by order

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Net;

using System.IO;

using System.Xml.Linq;

using System.Globalization;

namespace GoogleAnalyticsSupport

{

public class ReportRequestorWithFilterandSort

{

#region Fields

private static readonly string requestUrlFormat = "https://www.google.com/analytics/feeds/data?ids={0}&dimensions={1}&metrics={2}&filters={3}&sort={4}&start-date={5}&end-date={6}&start-index={7}&max-results={8}";

private static readonly string authUrlFormat = "accountType=GOOGLE&Email={0}&Passwd={1}&source=reimers.dk-analyticsreader-0.1&service=analytics";

private static CultureInfo ci = CultureInfo.GetCultureInfo("en-US");

private string _token = null;

private string _username = null;

private string _password = null;

#endregion

#region Constructor

public ReportRequestorWithFilterandSort() { }

public ReportRequestorWithFilterandSort(string email, string password)

{

_username = email;

_password = password;

}

#endregion

#region Properties

public string Email

{

get { return _username; }

set

{

if (!string.Equals(_username, value))

{

_username = value;

_token = null;

}

}

}

public string Password

{

get { return _password; }

set

{

if (!string.Equals(_password, value))

{

_password = value;

_token = null;

}

}

}

#endregion

#region Methods

private string GetToken(string username, string password)

{

if (string.IsNullOrEmpty(_username) || string.IsNullOrEmpty(_password))

{

throw new ArgumentNullException("Username, Password", "Username and/or password not set");

}

string authBody = string.Format(authUrlFormat, username, password);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/accounts/ClientLogin");

req.Method = "POST";

req.ContentType = "application/x-www-form-urlencoded";

req.UserAgent = "Example.dk req";

Stream stream = req.GetRequestStream();

StreamWriter sw = new StreamWriter(stream);

sw.Write(authBody);

sw.Close();

sw.Dispose();

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

StreamReader sr = new StreamReader(response.GetResponseStream());

string token = sr.ReadToEnd();

string[] tokens = token.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);

foreach (string item in tokens)

{

if (item.StartsWith("Auth="))

{

return item.Replace("Auth=", "");

}

}

return string.Empty;

}

public IEnumerable<AnalyticsAccountInfo> GetAccounts()

{

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create("https://www.google.com/analytics/feeds/accounts/default");

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

StreamReader sr = new StreamReader(responseStream);

string responseXml = sr.ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

var entries = from en in doc.Root.Descendants(defaultSpace + "entry")

select new AnalyticsAccountInfo

{

AccountID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountId").First().Attribute("value").Value,

AccountName = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:accountName").First().Attribute("value").Value,

ID = en.Element(defaultSpace + "id").Value,

Title = en.Element(defaultSpace + "title").Value,

ProfileID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:profileId").First().Attribute("value").Value,

WebPropertyID = en.Elements(dxpSpace + "property").Where(xe => xe.Attribute("name").Value == "ga:webPropertyId").First().Attribute("value").Value

};

return entries;

}

private XDocument getReport(AnalyticsAccountInfo account, IEnumerable<Dimension> dimensions, IEnumerable<Metric> metrics

, string filters, IEnumerable<Sort> sorts

,DateTime from, DateTime to, int startindex = 1, int maxresults = 250)

{

//string value = string.Empty;

if (string.IsNullOrEmpty(_token))

{

_token = GetToken(_username, _password);

}

StringBuilder dims = new StringBuilder();

foreach (Dimension item in dimensions)

{

dims.Append("ga:" + item.ToString() + ",");

}

StringBuilder mets = new StringBuilder();

foreach (Metric item in metrics)

{

mets.Append("ga:" + item.ToString() + ",");

}

StringBuilder srt = new StringBuilder();

foreach (Sort item in sorts)

{

srt.Append("-"+"ga:" + item.ToString() + ",");

}

//StringBuilder flt = new StringBuilder();

//foreach (Filter item in filters)

//{

// flt.Append("ga:" + item.ToString() + " ");//+ "%3D@Marriage");//.Equals("New York"));

//}

string requestUrl = string.Format(requestUrlFormat, "ga:" + account.ProfileID, dims.ToString().Trim(",".ToCharArray()), mets.ToString().Trim(",".ToCharArray())

,filters.ToString() , srt.ToString().Trim(",".ToCharArray()), from.ToString("yyyy-MM-dd"), to.ToString("yyyy-MM-dd"), startindex, maxresults);

HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(requestUrl);

req.Headers.Add("Authorization: GoogleLogin auth=" + _token);

HttpWebResponse response = (HttpWebResponse)req.GetResponse();

Stream responseStream = response.GetResponseStream();

string responseXml = new StreamReader(responseStream, Encoding.UTF8, true).ReadToEnd();

XDocument doc = XDocument.Parse(responseXml);

return doc;

}

public IEnumerable<GenericEntry> ReportRequestWFS(AnalyticsAccountInfo account, IEnumerable<Dimension> dimensions, IEnumerable<Metric> metrics

, string filters, IEnumerable<Sort> sorts

, DateTime from, DateTime to, int startindex = 1, int maxresults = 250)

{

XDocument doc = getReport(account, dimensions, metrics

,filters, sorts, from, to, startindex, maxresults);

XNamespace dxpSpace = doc.Root.GetNamespaceOfPrefix("dxp");

XNamespace defaultSpace = doc.Root.GetDefaultNamespace();

string fil = string.Empty;

var gr = from r in doc.Root.Descendants(defaultSpace + "entry")

select new GenericEntry

{

Dimensions = new List<KeyValuePair<Dimension, string>>(

from rd in r.Elements(dxpSpace + "dimension")

select new KeyValuePair<Dimension, string>(

(Dimension)Enum.Parse(

typeof(Dimension),

rd.Attribute("name").Value.Replace("ga:", ""),

true),

rd.Attribute("value").Value)),

Metrics = new List<KeyValuePair<Metric, string>>(

from rm in r.Elements(dxpSpace + "metric")

select new KeyValuePair<Metric, string>(

(Metric)Enum.Parse(typeof(Metric), rm.Attribute("name").Value.Replace("ga:", ""), true),

rm.Attribute("value").Value)),

Sorts = new List<KeyValuePair<Sort, string>>(

from rs in r.Elements(dxpSpace + "sort")

select new KeyValuePair<Sort, string>(

(Sort)Enum.Parse(typeof(Sort), rs.Attribute("name").Value.Replace("ga:", ""), true),

rs.Attribute("value").Value)) //,

//Filters = new List<KeyValuePair<Filter, string>>(

// from rf in r.Elements(dxpSpace + "Filter")

// select new KeyValuePair<Filter, string>(

// (Filter)Enum.Parse(typeof(Filter), rf.Attribute("name").Value.Replace("ga:", ""), true),

// rf.Attribute("value").Value) )

};

return gr;

}

#endregion

}

}

Sort.cs

It is a class file to use for sorting functions

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace GoogleAnalyticsSupport

{

public enum Sort

{

visitorType,

visitCount,

daysSinceLastVisit,

userDefinedValue,

visitLength,

referralPath,

campaign,

source,

medium,

keyword,

adContent,

socialNetwork,

hasSocialSourceReferral,

adGroup,

adSlot,

adSlotPosition,

adDistributionNetwork,

adMatchType,

adMatchedQuery,

adPlacementDomain,

adPlacementUrl,

adFormat,

adTargetingType,

adTargetingOption,

adDisplayUrl,

adDestinationUrl,

adwordsCustomerID,

adwordsCampaignID,

adwordsAdGroupID,

adwordsCreativeID,

adwordsCriteriaID,

browser,

browserVersion,

operatingSystem,

operatingSystemVersion,

isMobile,

mobileDeviceBranding,

mobileDeviceModel,

mobileInputSelector,

mobileDeviceInfo,

continent,

subContinent,

country,

metro,

region,

city,

latitude,

longitude,

networkDomain,

networkLocation,

flashVersion,

javaEnabled,

language,

screenColors,

screenResolution,

socialActivityEndorsingUrl,

socialActivityDisplayName,

socialActivityPost,

socialActivityTimestamp,

socialActivityUserHandle,

socialActivityUserPhotoUrl,

socialActivityUserProfileUrl,

socialActivityContentUrl,

socialActivityTagsSummary,

socialActivityAction,

socialActivityNetworkAction,

hostname,

pagePath,

pagePathLevel1,

pagePathLevel2,

pagePathLevel3,

pagePathLevel4,

pageTitle,

landingPagePath,

secondPagePath,

exitPagePath,

previousPagePath,

nextPagePath,

pageDepth,

searchUsed,

searchKeyword,

searchKeywordRefinement,

searchCategory,

searchStartPage,

searchDestinationPage,

eventCategory,

eventAction,

eventLabel,

transactionId,

affiliation,

visitsToTransaction,

daysToTransaction,

productSku,

productName,

productCategory,

socialInteractionNetwork,

socialInteractionAction,

socialInteractionNetworkAction,

socialInteractionTarget,

userTimingCategory,

userTimingLabel,

userTimingVariable,

customVarNamen,

customVarValuen,

date,

month,

year,

week,

day,

hour,

nthMonth,

nthWeek,

nthDay,

dayOfWeek,

visitors,

newVisits,

percentNewVisits,

visits,

bounces,

entranceBounceRate,

visitBounceRate,

timeOnSite,

avgTimeOnSite,

organicSearches,

impressions,

adClicks,

adCost,

CPM,

CPC,

CTR,

costPerTransaction,

costPerGoalConversion,

costPerConversion,

RPC,

ROI,

margin,

goalnStarts,

goalStartsAll,

goalnCompletions,

goalCompletionsAll,

goalnValue,

goalValueAll,

goalValuePerVisit,

goalnConversionRate,

goalConversionRateAll,

goalnAbandons,

goalAbandonsAll,

goalnAbandonRate,

goalAbandonRateAll,

socialActivities,

entrances,

entranceRate,

pageviews,

pageviewsPerVisit,

uniquePageviews,

timeOnPage,

avgTimeOnPage,

exits,

exitRate,

searchResultViews,

searchUniques,

avgSearchResultViews,

searchVisits,

percentVisitsWithSearch,

searchDepth,

avgSearchDepth,

searchRefinements,

searchDuration,

avgSearchDuration,

searchExits,

searchExitRate,

searchGoalnConversionRate,

searchGoalConversionRateAll,

goalValueAllPerSearch,

pageLoadTime,

pageLoadSample,

avgPageLoadTime,

domainLookupTime,

avgDomainLookupTime,

pageDownloadTime,

avgPageDownloadTime,

redirectionTime,

avgRedirectionTime,

serverConnectionTime,

avgServerConnectionTime,

avgServerResponseTime,

serverResponseTime,

speedMetricsSample,

appviews,

uniqueAppviews,

appviewsPerVisit,

totalEvents,

uniqueEvents,

eventValue,

avgEventValue,

visitsWithEvent,

eventsPerVisitWithEvent,

transactions,

transactionsPerVisit,

transactionRevenue,

revenuePerTransaction,

transactionRevenuePerVisit,

transactionShipping,

transactionTax,

totalValue,

itemQuantity,

uniquePurchases,

revenuePerItem,

itemRevenue,

itemsPerPurchase,

socialInteractions,

uniqueSocialInteractions,

socialInteractionsPerVisit,

userTimingValue,

userTimingSample,

avgUserTimingValue,

exceptions,

fatalExceptions

}

}

Once the necessary class files created, we need to give the Strong name to the Dynamic link library (DLL file). Below are the steps to give strong name to DLL and how to install DLL in GAC.

Once the Strong name is created, that information needs to be added in the AssemblyInfo.cs class file. Below is the existing example coding. In the below coding, we should mention the path where the strong name key created and saved.

using System.Reflection;

using System.Runtime.CompilerServices;

using System.Runtime.InteropServices;

// General Information about an assembly is controlled through the following

// set of attributes. Change these attribute values to modify the information

// associated with an assembly.

[assembly: AssemblyTitle("GoogleAnalyticsSupport")]

[assembly: AssemblyDescription("")]

[assembly: AssemblyConfiguration("")]

[assembly: AssemblyCompany("")]

[assembly: AssemblyProduct("GoogleAnalyticsSupport")]

[assembly: AssemblyCopyright("Copyright © 2012")]

[assembly: AssemblyTrademark("")]

[assembly: AssemblyCulture("")]

[assembly: AssemblyDelaySign(false)]

[assembly: AssemblyKeyFile(@"M:\SSIS\MDR2.0\Imports\Assembly\GASupport\GASupport\bin\Debug\GoogleAnalyticsSupport.snk")]

[assembly: AssemblyKeyName("")]

// Setting ComVisible to false makes the types in this assembly not visible

// to COM components. If you need to access a type in this assembly from

// COM, set the ComVisible attribute to true on that type.

[assembly: ComVisible(false)]

// The following GUID is for the ID of the typelib if this project is exposed to COM

[assembly: Guid("98b5b909-07b0-428a-b6d9-1e6561b8d855")]

// Version information for an assembly consists of the following four values:

//

// Major Version

// Minor Version

// Build Number

// Revision

//

// You can specify all the values or you can default the Build and Revision Numbers

// by using the '*' as shown below:

// [assembly: AssemblyVersion("1.0.*")]

[assembly: AssemblyVersion("1.0.0.0")]

[assembly: AssemblyFileVersion("1.0.0.0")]

Once the information provided in the AssemblyInfo.cs file, the DLL file which is saved in the path should be added/installed in the Assembly folder. Example figure:

Go to – Start – Type as Run in the search option - and type as Assembly. Drag the DLL file and move it to this folder.

Example figure for class file process steps: