public static List <SearchConsoleDataSet> SearchQueryWiseDataSet(DateTime startDate, DateTime endDate, string query, int domainID) { SearchConsoleContext context = new SearchConsoleContext(); DateTime sDate = startDate; List <SearchConsoleDataSet> QueryWiseData = new List <SearchConsoleDataSet>(); if (context != null) { if (query != "" && query != null) { List <SearchConsoleDataSet> dataset = context.SearchConsoleDataSets.Where(s => s.Date >= sDate && s.Date < endDate && s.Query == query && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); QueryWiseData = dataset.GroupBy(l => l.Date).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Query = cl.First().Query, Date = cl.First().Date }).OrderBy(l => l.Date).ToList(); } else { List <SearchConsoleDataSet> dataset = context.SearchConsoleDataSets.Where(s => s.Date >= sDate && s.Date < endDate && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); QueryWiseData = dataset.GroupBy(l => l.Query).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Query = cl.First().Query }).OrderByDescending(c => c.Clicks).ToList(); } } return(QueryWiseData); }
public static List <SearchConsoleDataSet> SearchDeviceWiseDataSet(DateTime startDate, DateTime endDate, int domainID) { SearchConsoleContext context = new SearchConsoleContext(); DateTime sDate = startDate; List <SearchConsoleDataSet> DeviceWiseData = new List <SearchConsoleDataSet>(); if (context != null) { List <SearchConsoleDataSet> dataset = context.SearchConsoleDataSets.Where(s => s.Date >= sDate && s.Date < endDate && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); DeviceWiseData = dataset.GroupBy(l => l.DeviceType).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), DeviceType = cl.First().DeviceType }).OrderByDescending(c => c.Clicks).ToList(); } return(DeviceWiseData); }
public static List <SearchConsoleDataSet> GetFirstAndLastDate(int domainID) { SearchConsoleContext context = new SearchConsoleContext(); List <SearchConsoleDataSet> res = new List <SearchConsoleDataSet>(); SearchConsoleDataSet firstDate = new SearchConsoleDataSet(); SearchConsoleDataSet lastDate = new SearchConsoleDataSet(); if (context != null) { firstDate = context.SearchConsoleDataSets.Where(s => s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).FirstOrDefault(); lastDate = context.SearchConsoleDataSets.Where(s => s.DomainId == domainID && s.country == "aus").OrderByDescending(l => l.Date).FirstOrDefault(); } res.Add(firstDate); res.Add(lastDate); return(res); }
public static List <List <SearchConsoleDataSet> > Comp_SearchDateWiseDataSet(DateTime startDate1, DateTime endDate1, DateTime startDate2, DateTime endDate2, int domainID) { SearchConsoleContext context = new SearchConsoleContext(); DateTime sDate = startDate1; List <SearchConsoleDataSet> QueryWiseData1 = new List <SearchConsoleDataSet>(); List <SearchConsoleDataSet> QueryWiseData2 = new List <SearchConsoleDataSet>(); List <SearchConsoleDataSet> QueryWiseData3 = new List <SearchConsoleDataSet>(); List <SearchConsoleDataSet> QueryWiseData4 = new List <SearchConsoleDataSet>(); List <List <SearchConsoleDataSet> > res = new List <List <SearchConsoleDataSet> >(); if (context != null) { List <SearchConsoleDataSet> dataset = context.SearchConsoleDataSets.Where(s => s.Date >= sDate && s.Date < endDate1 && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); QueryWiseData1 = dataset.GroupBy(l => l.Date).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Date = cl.First().Date }).ToList(); List <SearchConsoleDataSet> dataset1 = context.SearchConsoleDataSets.Where(s => s.Date >= startDate2 && s.Date < endDate2 && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); QueryWiseData2 = dataset1.GroupBy(l => l.Date).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Date = cl.First().Date }).ToList(); //foreach (SearchConsoleDataSet item in QueryWiseData1) //{ // foreach (SearchConsoleDataSet item1 in QueryWiseData2) // { // if (item.Query == item1.Query) // { // QueryWiseData3.Add(item); // QueryWiseData4.Add(item1); // } // } //} //res.Add(QueryWiseData3); //res.Add(QueryWiseData4); res.Add(QueryWiseData1); res.Add(QueryWiseData2); } return(res); }
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); //} } } }
public static List <List <SearchConsoleDataSet> > Comp_SearchPageWiseDataSet(DateTime startDate1, DateTime endDate1, DateTime startDate2, DateTime endDate2, string page, int domainID) { SearchConsoleContext context = new SearchConsoleContext(); DateTime sDate = startDate1; List <SearchConsoleDataSet> PageWiseData1 = new List <SearchConsoleDataSet>(); List <SearchConsoleDataSet> PageWiseData2 = new List <SearchConsoleDataSet>(); List <SearchConsoleDataSet> PageWiseData3 = new List <SearchConsoleDataSet>(); List <SearchConsoleDataSet> PageWiseData4 = new List <SearchConsoleDataSet>(); List <List <SearchConsoleDataSet> > res = new List <List <SearchConsoleDataSet> >(); if (context != null) { if (page != "" && page != null) { List <SearchConsoleDataSet> dataset = context.SearchConsoleDataSets.Where(s => s.Date >= sDate && s.Date < endDate1 && s.Page == page && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); PageWiseData1 = dataset.GroupBy(l => l.Date).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Page = cl.First().Page, Date = cl.First().Date }).OrderBy(l => l.Date).ToList(); List <SearchConsoleDataSet> dataset1 = context.SearchConsoleDataSets.Where(s => s.Date >= startDate2 && s.Date < endDate2 && s.Page == page && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); PageWiseData2 = dataset1.GroupBy(l => l.Date).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Page = cl.First().Page, Date = cl.First().Date }).OrderBy(l => l.Date).ToList(); foreach (SearchConsoleDataSet item in PageWiseData1) { foreach (SearchConsoleDataSet item1 in PageWiseData2) { if (item.Page == item1.Page) { PageWiseData3.Add(item); PageWiseData4.Add(item1); } } } } else { List <SearchConsoleDataSet> dataset = context.SearchConsoleDataSets.Where(s => s.Date >= sDate && s.Date < endDate1 && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); PageWiseData1 = dataset.GroupBy(l => l.Page).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Page = cl.First().Page }).OrderByDescending(c => c.Clicks).ToList(); List <SearchConsoleDataSet> dataset1 = context.SearchConsoleDataSets.Where(s => s.Date >= startDate2 && s.Date < endDate2 && s.DomainId == domainID && s.country == "aus").OrderBy(l => l.Date).ToList(); PageWiseData2 = dataset1.GroupBy(l => l.Page).Select(cl => new SearchConsoleDataSet { Clicks = cl.Sum(c => c.Clicks), Impressions = cl.Sum(d => d.Impressions), CTR = cl.Sum(c => c.Clicks) / cl.Sum(d => d.Impressions), Position = cl.Average(f => f.Position), Page = cl.First().Page }).OrderByDescending(c => c.Clicks).ToList(); foreach (SearchConsoleDataSet item in PageWiseData1) { foreach (SearchConsoleDataSet item1 in PageWiseData2) { if (item.Page == item1.Page) { PageWiseData3.Add(item); PageWiseData4.Add(item1); } } } } res.Add(PageWiseData3); res.Add(PageWiseData4); res.Add(PageWiseData1); res.Add(PageWiseData2); } return(res); }