public SourceTime(DataEntry gaDataEntry, Site site)
{
gaAccount = site.account;
foreach (Dimension dimension in gaDataEntry.Dimensions)
{
switch (dimension.Name)
{
case "ga:source": source = dimension.Value;
break;
case "ga:medium": medium = dimension.Value;
break;
case "ga:keyword": keyword = dimension.Value;
break;
case "ga:campaign": campaign = dimension.Value;
break;
case "ga:daysToTransaction": daysToTransaction = Convert.ToInt32(dimension.Value);
break;
case "ga:visitsToTransaction": visitsToTransaction = Convert.ToInt32(dimension.Value);
break;
case "ga:transactionId": orderNumber = dimension.Value;
break;
}
}
}
public SiteEvent(DataEntry gaDataEntry, Site site)
{
gaAccount = site.account;
foreach (Dimension dimension in gaDataEntry.Dimensions)
{
switch (dimension.Name)
{
case "ga:date": gaDate = DateTime.ParseExact(dimension.Value, "yyyyMMdd", null);
break;
case "ga:eventCategory": category = dimension.Value;
break;
case "ga:eventAction": action = dimension.Value;
break;
case "ga:eventLabel": label = dimension.Value;
break;
}
}
foreach (Metric metric in gaDataEntry.Metrics)
{
switch (metric.Name)
{
case "ga:totalEvents": totalEvents = Convert.ToInt32(metric.Value);
break;
case "ga:uniqueEvents": uniqueEvents = Convert.ToInt32(metric.Value);
break;
}
}
}
public PaidSearchVisitSummary(DataEntry gaDataEntry, Site site)
{
gaAccount = site.account;
foreach (Dimension dimension in gaDataEntry.Dimensions)
{
switch (dimension.Name)
{
case "ga:date": gaDate = DateTime.ParseExact(dimension.Value, "yyyyMMdd", null);
break;
case "ga:source": source = dimension.Value;
break;
case "ga:medium": medium = dimension.Value;
break;
case "ga:pagePath": pagePath = dimension.Value;
break;
}
}
foreach (Metric metric in gaDataEntry.Metrics)
{
switch (metric.Name)
{
case "ga:visits": visits = Convert.ToInt32(metric.Value);
break;
}
}
}
public DetailedVisit(DataEntry gaDataEntry, Site site)
{
gaAccount = site.account;
foreach (Dimension dimension in gaDataEntry.Dimensions)
{
switch (dimension.Name)
{
case "ga:date": gaDate = DateTime.ParseExact(dimension.Value, "yyyyMMdd", null);
break;
case "ga:medium": medium = dimension.Value;
break;
}
}
foreach (Metric metric in gaDataEntry.Metrics)
{
switch (metric.Name)
{
case "ga:visitors": uniqueVisitors = Convert.ToInt32(metric.Value);
break;
case "ga:visits": visits = Convert.ToInt32(metric.Value);
break;
case "ga:transactions": transactions = Convert.ToInt32(metric.Value);
break;
}
}
}
private static void GetAdwordsCostData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetAdwordsCostData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository analyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<AdwordsCost> adwordsCosts = gaService.getAdwordsData(fromDate, toDate, site);
analyticsRepository.deleteAdwordsCostData(site, fromDate, toDate);
foreach (AdwordsCost adwordsCost in adwordsCosts)
{
analyticsRepository.recordAdwordsCostData(adwordsCost);
}
}
private static void GetSearchData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetSearchData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository AnalyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<Search> searchData = gaService.getSiteSearchData(fromDate, toDate, site);
foreach (Search search in searchData)
{
AnalyticsRepository.recordAnalyticsSiteSearch(search);
}
}
private static void GetPaidSearchVisitData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetPaidSearchVisitData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository AnalyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<PaidSearchVisitSummary> visits = gaService.getPaidSearchVisits(fromDate, toDate, site);
foreach (PaidSearchVisitSummary visit in visits)
{
AnalyticsRepository.recordPaidSearchVisitSummary(visit);
}
}
private static void GetGeoSystemData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetGeoSystemData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository AnalyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<GeoSystem> geoSystems = gaService.getGeoSystemData(fromDate, toDate, site);
foreach (GeoSystem geoSystem in geoSystems)
{
AnalyticsRepository.recordAnalyticsGeoSystem(geoSystem);
}
}
private static void GetEventData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetEventData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository AnalyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<SiteEvent> events = gaService.getEventData(fromDate, toDate, site);
foreach (SiteEvent siteEvent in events)
{
AnalyticsRepository.recordAnalyticsEvent(siteEvent);
}
}
public Site[] getTHGSites()
{
List<Site> siteList = new List<Site>();
SqlConnection conn = new SqlConnection();
try
{
conn.ConnectionString = connectionAnalytics;
conn.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.CommandType = System.Data.CommandType.Text;
sqlCommand.CommandText = "SELECT Site, Analytics_AccountID FROM [Analytics_Site_Account] a (NOLOCK) WHERE a.THG_Site = 1";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
Site site = new Site();
site.account = sqlDataReader["Analytics_AccountID"].ToString();
site.siteId = Convert.ToInt32(sqlDataReader["Site"]);
siteList.Add(site);
}
sqlDataReader.Close();
sqlCommand.Dispose();
}
catch (SqlException sqlex)
{
throw new Exception("Error in AnalyticsRepositoryImpl getting sites", sqlex);
}
finally
{
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
return siteList.ToArray();
}
public void deleteAdwordsCostData(Site site, DateTime fromDate, DateTime toDate)
{
SqlConnection conn = new SqlConnection(connectionAnalytics);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"DELETE FROM Analytics_Adwords_Cost
WHERE GA_Account = @account AND Date >= @fromDate AND Date <= @toDate";
cmd.Parameters.Add("@account", SqlDbType.VarChar);
cmd.Parameters["@account"].Value = site.account;
cmd.Parameters.Add("@fromDate", SqlDbType.DateTime);
cmd.Parameters["@fromDate"].Value = fromDate;
cmd.Parameters.Add("@toDate", SqlDbType.DateTime);
cmd.Parameters["@toDate"].Value = toDate;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (SqlException sqle)
{
log.Error("Error in deleteAdwordsCostData", sqle);
}
finally
{
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
}
public List<SiteEvent> getEventData(DateTime fromDate, DateTime toDate, Site site)
{
List<SiteEvent> events = new List<SiteEvent>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:eventCategory,ga:eventAction,ga:eventLabel,ga:date",
"ga:totalEvents,ga:uniqueEvents",
"ga:eventCategory==Availability,ga:eventCategory==Search,ga:eventAction==Click", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
SiteEvent siteEvent = new SiteEvent(singleEntry, site);
events.Add(siteEvent);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getEventData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return events;
}
public List<AdwordsCost> getAdwordsData(DateTime fromDate, DateTime toDate, Site site)
{
List<AdwordsCost> adwordsCosts = new List<AdwordsCost>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:source,ga:medium,ga:keyword,ga:adwordsCampaignId,ga:date",
"ga:adClicks,ga:adCost,ga:transactions,ga:transactionRevenue",
"ga:medium==ppc,ga:medium==cpc,ga:medium==PPC,ga:medium==CPC", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
AdwordsCost adwordsCost = new AdwordsCost(singleEntry, site);
adwordsCosts.Add(adwordsCost);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getAdwordsData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return adwordsCosts;
}
public AdwordsCost(DataEntry gaDataEntry, Site site)
{
gaAccount = site.account;
foreach (Dimension dimension in gaDataEntry.Dimensions)
{
switch (dimension.Name)
{
case "ga:source": source = dimension.Value;
break;
case "ga:medium": medium = dimension.Value;
break;
case "ga:keyword": keywords = dimension.Value;
break;
case "ga:adwordsCampaignID": adwordsCampaign = dimension.Value;
break;
case "ga:date": gaDate = DateTime.ParseExact(dimension.Value, "yyyyMMdd", null);
break;
}
}
foreach (Metric metric in gaDataEntry.Metrics)
{
switch (metric.Name)
{
case "ga:adClicks": clickCount = Convert.ToInt32(metric.Value);
break;
case "ga:transactions": transactionCount = Convert.ToInt32(metric.Value);
break;
case "ga:adCost": adCost = Convert.ToDouble(metric.Value);
break;
case "ga:transactionRevenue": transactionRevenue = Convert.ToDouble(metric.Value);
break;
}
}
}
private static void GetVisitSourceData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetVisitSourceData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository AnalyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<DetailedVisit> visits = gaService.getVisitSourceData(fromDate, toDate, site);
foreach (DetailedVisit visit in visits)
{
AnalyticsRepository.recordAnalyticsVisitSource(visit);
}
}
private DataFeed getGADataFeed(Site site,
DateTime fromDay,
DateTime toDay,
string dimensions,
string metrics,
string filters,
string sort,
int startIndex)
{
AnalyticsService asv = new AnalyticsService(applicationName);
asv.setUserCredentials(userId, password);
DataQuery query = new DataQuery(analyticsApiURL);
query.Ids = site.account;
query.Dimensions = dimensions;
query.Metrics = metrics;
query.Filters = filters;
query.Sort = sort;
query.NumberToRetrieve = numberOfGARecordsToRetrievePerRequest;
query.StartIndex = startIndex;
query.GAStartDate = String.Format("{0:yyyy-MM-dd}", fromDay);
query.GAEndDate = String.Format("{0:yyyy-MM-dd}", toDay);
log.Debug("Querying Google API : " + query.Uri.ToString());
DataFeed feed = asv.Query(query);
System.Threading.Thread.Sleep(requestPause); // wait between requests
return feed;
}
public List<DetailedVisit> getVisitSourceData(DateTime fromDate, DateTime toDate, Site site)
{
List<DetailedVisit> visits = new List<DetailedVisit>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:medium,ga:date",
"ga:visitors,ga:visits,ga:transactions",
"", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
DetailedVisit visit = new DetailedVisit(singleEntry, site);
visits.Add(visit);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getVisitData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return visits;
}
public List<Visitor> getVisitorData(DateTime fromDate, DateTime toDate, Site site)
{
List<Visitor> visitors = new List<Visitor>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:daysSinceLastVisit,ga:pageDepth,ga:visitCount,ga:visitorType,ga:landingPagePath,ga:transactionId",
"ga:transactions",
"", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
Visitor visitor = new Visitor(singleEntry);
visitors.Add(visitor);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getVisitorData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return visitors;
}
public List<SourceTime> getSourceTimeData(DateTime fromDate, DateTime toDate, Site site)
{
List<SourceTime> sourceTimes = new List<SourceTime>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:campaign,ga:keyword,ga:medium,ga:source,ga:daysToTransaction,ga:transactionId,ga:visitsToTransaction",
"ga:transactions",
"", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
SourceTime sourceTime = new SourceTime(singleEntry, site);
sourceTimes.Add(sourceTime);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getSourceTimeData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return sourceTimes;
}
public List<Search> getSiteSearchData(DateTime fromDate, DateTime toDate, Site site)
{
List<Search> searchData = new List<Search>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:visitLength,ga:transactionId,ga:searchUsed",
"ga:transactions",
"", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
Search search = new Search(singleEntry);
searchData.Add(search);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getSiteSearchData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return searchData;
}
public List<PaidSearchVisitSummary> getPaidSearchVisits(DateTime fromDate, DateTime toDate, Site site)
{
List<PaidSearchVisitSummary> visits = new List<PaidSearchVisitSummary>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:medium,ga:date,ga:pagePath,ga:source",
"ga:visits",
"ga:medium==ppc,ga:medium==cpc,ga:medium==PPC,ga:medium==CPC", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
PaidSearchVisitSummary visit = new PaidSearchVisitSummary(singleEntry, site);
visits.Add(visit);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getPaidSearchVisits", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return visits;
}
public List<GeoSystem> getGeoSystemData(DateTime fromDate, DateTime toDate, Site site)
{
List<GeoSystem> geoSystems = new List<GeoSystem>();
int startIndex = 1;
Boolean isMore;
DataFeed feed = null;
do
{
isMore = false;
try
{
feed = getGADataFeed(site, fromDate, toDate,
"ga:city,ga:country,ga:isMobile,ga:language,ga:transactionId",
"ga:transactions",
"", "", startIndex);
if (feed != null)
{
foreach (DataEntry singleEntry in feed.Entries)
{
GeoSystem geoSystem = new GeoSystem(singleEntry);
geoSystems.Add(geoSystem);
isMore = true;
}
}
}
catch (Exception rEx)
{
log.Error("Error in getGeoSystemData", rEx);
}
if (isMore) startIndex += numberOfGARecordsToRetrievePerRequest;
} while (isMore);
return geoSystems;
}
private static void GetSourceTimeData(DateTime fromDate, DateTime toDate, Site site)
{
System.Console.WriteLine("GetSourceTimeData for site : " + site.siteId + ", GA account : " + site.account);
AnalyticsRepository AnalyticsRepository = new AnalyticsRepositoryImpl();
GAService gaService = new GAService_Impl();
List<SourceTime> gaSourceTimes = gaService.getSourceTimeData(fromDate, toDate, site);
foreach (SourceTime sourceTime in gaSourceTimes)
{
AnalyticsRepository.recordAnalyticsSourceTime(sourceTime);
}
}
