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 void recordPaidSearchVisitSummary(PaidSearchVisitSummary paidSearchVisitSummary)
{
SqlConnection conn = new SqlConnection(connectionAnalytics);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"IF NOT EXISTS(SELECT (1) FROM Analytics_PaidSearch_Page (nolock) WHERE Analytics_Date = @Date AND Site = @Account AND Source = @Source AND Medium = @Medium AND PagePath = @PagePath)
INSERT INTO Analytics_PaidSearch_Page (Analytics_Date, Site, Source, Medium, PagePath, Visits)
VALUES (@Date, @Account, @Source, @Medium, @PagePath, @Visits)
ELSE
UPDATE Analytics_PaidSearch_Page
SET Visits = @Visits
WHERE Analytics_Date = @Date AND Site = @Account AND Source = @Source AND Medium = @Medium AND PagePath = @PagePath";
cmd.Parameters.Add("@Date", SqlDbType.DateTime);
cmd.Parameters["@Date"].Value = paidSearchVisitSummary.gaDate;
cmd.Parameters.Add("@Account", SqlDbType.NVarChar);
cmd.Parameters["@Account"].Value = truncate(paidSearchVisitSummary.gaAccount, 15);
cmd.Parameters.Add("@Source", SqlDbType.NVarChar);
cmd.Parameters["@Source"].Value = truncate(paidSearchVisitSummary.source, 50);
cmd.Parameters.Add("@Medium", SqlDbType.NVarChar);
cmd.Parameters["@Medium"].Value = truncate(paidSearchVisitSummary.medium, 50);
cmd.Parameters.Add("@PagePath", SqlDbType.NVarChar);
cmd.Parameters["@PagePath"].Value = truncate(paidSearchVisitSummary.pagePath, 255);
cmd.Parameters.Add("@Visits", SqlDbType.Int);
cmd.Parameters["@Visits"].Value = paidSearchVisitSummary.visits;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (SqlException sqle)
{
log.Error("Error in recordPaidSearchVisitSummary", sqle);
}
finally
{
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
}