private SearchConsoleData GetSearchConsoleData(RequestObject request) { var dataResult = new SearchConsoleData { Results = new List <SearchConsoleResult>() }; using var searchConsole = new Google.Apis.Webmasters.v3.WebmastersService( new Google.Apis.Services.BaseClientService.Initializer { HttpClientInitializer = credential, ApplicationName = "Search Console API test" }); SearchAnalyticsQueryRequest body = new SearchAnalyticsQueryRequest(); IList <string> dimensions = new List <string>(); dimensions.Add("query"); dimensions.Add("page"); body.StartDate = DateTime.Today.AddDays(-90).ToShortDateString(); body.EndDate = DateTime.Today.ToShortDateString(); body.Dimensions = dimensions; body.RowLimit = 25000; var result = searchConsole.Searchanalytics.Query(body, request.siteUrl).Execute(); List <SearchConsoleResult> dataList = new List <SearchConsoleResult>(); foreach (var row in result.Rows) { dataList.Add(new SearchConsoleResult { Keyword = row.Keys[0], Page = row.Keys[1], Clicks = (int)row.Clicks, Impressions = (int)row.Impressions, Ctr = row.Ctr, Position = (int)row.Position }); } dataResult.Results.AddRange(dataList); dataResult.NumberOfRows = dataList.Count; return(dataResult); }
private SearchConsoleData GetAnalysis(SqlConnection conn, int id) { var dataResult = new SearchConsoleData { Results = new List <SearchConsoleResult>() }; var sqlQuery = "SELECT DataId, Keyword, PageUrl, Impressions, Clicks, CTR, Position, Category, SubCategory1, SubCategory2, Intent, Colour FROM AnalysisData WHERE Id = @Id"; using SqlCommand cmd = new SqlCommand(sqlQuery, conn); cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id; conn.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { var searchResult = new SearchConsoleResult(); searchResult.DataId = (int)reader["DataId"]; searchResult.Keyword = reader["Keyword"].ToString(); searchResult.Impressions = (int)reader["Impressions"]; searchResult.Clicks = (int)reader["Clicks"]; searchResult.Position = (int)reader["Position"]; searchResult.Category = reader["Category"].ToString(); searchResult.SubCategory1 = reader["SubCategory1"].ToString(); searchResult.SubCategory2 = reader["SubCategory2"].ToString(); searchResult.Intent = reader["Intent"].ToString(); searchResult.Colour = reader["Colour"].ToString(); searchResult.Page = reader["PageUrl"].ToString(); searchResult.Ctr = Double.Parse(reader["CTR"].ToString()); dataResult.Results.Add(searchResult); } conn.Close(); return(dataResult); }
public string CreateAnalysis([FromBody] RequestObject request) { SearchConsoleData dataResult = GetSearchConsoleData(request); try { using SqlConnection conn = new SqlConnection(connString.SQLConnection); // Output the ID from the newly generated analysis for later use var sqlQuery = "INSERT INTO Analyses (Customer, Url, CreatedDate) output INSERTED.Id " + "VALUES (@Customer, @Url, @CreatedDate) "; conn.Open(); using SqlCommand cmd = new SqlCommand(sqlQuery, conn); cmd.Parameters.Add("@Customer", SqlDbType.NVarChar).Value = request.customerName; cmd.Parameters.Add("@Url", SqlDbType.NVarChar).Value = request.siteUrl; cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime2).Value = DateTime.UtcNow; var newId = (int)cmd.ExecuteScalar(); // Datatable for quickly bulk inserting new analysis into DB. var table = new DataTable(); var idColumn = new DataColumn("Id", typeof(int)) { DefaultValue = newId }; table.Columns.Add(idColumn); table.Columns.Add("DataId", typeof(int)); table.Columns.Add("Keyword", typeof(string)); table.Columns.Add("SiteUrl", typeof(string)); table.Columns.Add("Impressions", typeof(int)); table.Columns.Add("Clicks", typeof(int)); table.Columns.Add("CTR", typeof(double)); table.Columns.Add("Position", typeof(int)); table.Columns.Add("Category", typeof(string)); table.Columns.Add("SubCategory1", typeof(string)); table.Columns.Add("SubCategory2", typeof(string)); table.Columns.Add("Intent", typeof(string)); table.Columns.Add("Colour", typeof(string)); List <string> keywords = new List <string>(); // Populate datatable using data fetched from google. // Also adds all keywords to seperate list, for splitting later. foreach (var data in dataResult.Results) { var row = table.NewRow(); row["Keyword"] = data.Keyword; row["SiteUrl"] = data.Page; row["Clicks"] = data.Clicks; row["Impressions"] = data.Impressions; row["CTR"] = data.Ctr; row["Position"] = data.Position; keywords.AddRange(data.Keyword.Split()); table.Rows.Add(row); } using var bulk = new SqlBulkCopy(conn); bulk.DestinationTableName = "AnalysisData"; bulk.WriteToServer(table); var keywordsTable = new DataTable(); keywordsTable.Columns.Add("Id", typeof(int)); var analysisIdColumn = new DataColumn("AnalysisId", typeof(int)) { DefaultValue = newId }; keywordsTable.Columns.Add(analysisIdColumn); keywordsTable.Columns.Add("Keyword", typeof(string)); keywordsTable.Columns.Add("Count", typeof(int)); // Remove all keywords length 2 or less, and return only unique keywords. var distinctKeywords = keywords.Distinct().ToList(); distinctKeywords.RemoveAll(word => word.Length <= 2); Dictionary <string, int> keywordDictionary = distinctKeywords.ToDictionary(d => d, x => keywords.Count(s => x == s)); foreach (var keyword in keywordDictionary) { var row = keywordsTable.NewRow(); row["Keyword"] = keyword.Key; row["Count"] = keyword.Value; keywordsTable.Rows.Add(row); } bulk.DestinationTableName = "SplitKeywords"; bulk.WriteToServer(keywordsTable); conn.Close(); // SQL Server does a bit of formatting on floats/decimals, so we fetch the newly saved data // from the database, and serve it directly. Ensures same result every time. var responseData = GetAnalysis(conn, newId); responseData.NumberOfRows = responseData.Results.Count; responseData.AnalysisId = newId; responseData.CustomerName = request.customerName; responseData.SiteUrl = request.siteUrl; return(JsonConvert.SerializeObject(responseData)); } catch (Exception e) { Debug.WriteLine(e.Message); } return(JsonConvert.SerializeObject("No data found")); }
static void Main(string[] args) { string clientId = ConfigurationManager.AppSettings.Get("clientId"); string clientSecret = ConfigurationManager.AppSettings.Get("clientSecret"); string userName = ConfigurationManager.AppSettings.Get("userName"); SearchConsoleContext db = new SearchConsoleContext(); WebmastersService service = AuthenticateOauth(clientId, clientSecret, userName); if (db != null && service != null) { foreach (Domain domain in db.Domains.ToList()) { if (domain.Id == 2) { continue; } IList <string> dimensions = new List <string>(); db.FilterTypes.ToList().ForEach(ft => dimensions.Add(ft.Type)); //SearchConsoleDataKey dataKey = null; SearchConsoleDataSet dataKey = null; FilterType dateFilterType = db.FilterTypes.FirstOrDefault(ft => ft.Type == "date"); if (dateFilterType != null && dateFilterType.Id != 0) { //dataKey = db.SearchConsoleDataKeys.Where(d => d.FilterTypeId == dateFilterType.Id).OrderByDescending(k => k.Key).FirstOrDefault(); dataKey = db.SearchConsoleDataSets.Where(d => d.DomainId == domain.Id).OrderByDescending(k => k.Date).FirstOrDefault(); } SearchAnalyticsQueryRequest request = new SearchAnalyticsQueryRequest(); request.RowLimit = 5000; request.Dimensions = dimensions; request.AggregationType = "bypage"; DateTime startDate = DateTime.Now.AddDays(-78).Date; DateTime endDate = startDate; if (dataKey != null) { //startDate = DateTime.Parse(dataKey.Key).AddDays(1); startDate = dataKey.Date.AddDays(1); endDate = startDate; } //while (startDate < endDate) //{ request.StartDate = startDate.ToString("yyyy-MM-dd"); request.EndDate = endDate.ToString("yyyy-MM-dd"); var result = service.Searchanalytics.Query(request, domain.Name).Execute(); if (result != null && result.Rows != null && result.Rows.Count > 0) { for (int i = 0; i < result.Rows.Count; i++) { SearchConsoleData data = new SearchConsoleData(); data.Clicks = result.Rows[i].Clicks; data.CTR = result.Rows[i].Ctr; data.Impressions = result.Rows[i].Impressions; data.Position = result.Rows[i].Position; data.DomainId = domain.Id; data.SearchConsoleDataKeys = new List <SearchConsoleDataKey>(); SearchConsoleDataSet dataInOneRow = new SearchConsoleDataSet(); dataInOneRow.Clicks = result.Rows[i].Clicks; dataInOneRow.CTR = result.Rows[i].Ctr; dataInOneRow.Impressions = result.Rows[i].Impressions; dataInOneRow.Position = result.Rows[i].Position; dataInOneRow.DomainId = domain.Id; dataInOneRow.Query = result.Rows[i].Keys[0]; dataInOneRow.Page = result.Rows[i].Keys[1]; dataInOneRow.country = result.Rows[i].Keys[2]; string device = result.Rows[i].Keys[3]; DeviceType deviceType = db.DeviceTypes.FirstOrDefault(dt => dt.Type == device); dataInOneRow.DeviceType = deviceType.Id; dataInOneRow.Date = DateTime.Parse(result.Rows[i].Keys[4]); //NOTE: Keys are arranged according to how the dimensions was arranged in the request. for (int key = 0; key < dimensions.Count; key++) { string filter = dimensions[key]; FilterType filterType = db.FilterTypes.FirstOrDefault(ft => ft.Type == filter); data.SearchConsoleDataKeys.Add( new SearchConsoleDataKey { FilterTypeId = filterType.Id, Key = result.Rows[i].Keys[key] }); } db.SearchConsoleDataCollection.Add(data); db.SearchConsoleDataSets.Add(dataInOneRow); db.SaveChanges(); } } // startDate = startDate.AddDays(1); //} } } }