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