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 void recordAdwordsCostData(AdwordsCost adwordsCost)
{
SqlConnection conn = new SqlConnection(connectionAnalytics);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"INSERT INTO Analytics_Adwords_Cost (GA_Account, Date,
GA_Medium, GA_Source, GA_Adwords_Campaign, GA_Ad_Cost, GA_Transactions,
GA_Revenue, GA_Keywords, GA_Clicks)
VALUES (@accountId, @date, @gamedium, @gasource, @gaAdwordscampaign, @gacost,
@gatransactions, @gatransactionrev, @gakeywords, @gaclicks)";
cmd.Parameters.Add("@accountId", SqlDbType.NVarChar);
cmd.Parameters["@accountId"].Value = truncate(adwordsCost.gaAccount,20);
cmd.Parameters.Add("@date", SqlDbType.DateTime);
cmd.Parameters["@date"].Value = adwordsCost.gaDate;
cmd.Parameters.Add("@gasource", SqlDbType.NVarChar);
cmd.Parameters["@gasource"].Value = truncate(adwordsCost.source, 50) ;
cmd.Parameters.Add("@gamedium", SqlDbType.NVarChar);
cmd.Parameters["@gamedium"].Value = truncate(adwordsCost.medium, 50);
cmd.Parameters.Add("@gaAdwordscampaign", SqlDbType.NVarChar);
cmd.Parameters["@gaAdwordscampaign"].Value = truncate(adwordsCost.adwordsCampaign, 50);
cmd.Parameters.Add("@gacost", SqlDbType.Money);
cmd.Parameters["@gacost"].Value = adwordsCost.adCost;
cmd.Parameters.Add("@gatransactions", SqlDbType.Int);
cmd.Parameters["@gatransactions"].Value = adwordsCost.transactionCount;
cmd.Parameters.Add("@gatransactionrev", SqlDbType.Money);
cmd.Parameters["@gatransactionrev"].Value = adwordsCost.transactionRevenue;
cmd.Parameters.Add("@gakeywords", SqlDbType.NVarChar);
cmd.Parameters["@gakeywords"].Value = truncate(adwordsCost.keywords, 255);
cmd.Parameters.Add("@gaclicks", SqlDbType.Int);
cmd.Parameters["@gaclicks"].Value = adwordsCost.clickCount;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (SqlException sqle)
{
log.Error("Error in recordAdwordsCostData", sqle);
}
finally
{
if (conn != null)
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
}
