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(); } } } }