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