/// <summary>
/// Returns Search Keywords returned from GWT
/// </summary>
/// <param name="websiteIds">List of websiteids</param>
/// <param name="limit">Min Limit is 10, Max Limit is 50</param>
/// <param name="offset"></param>
/// <returns></returns>
public static List <DetailedSearchAnalytics> GetSearchAnalytics(List <string> websiteIds, int limit = 10, int offset = 0)
{
if (websiteIds == null || websiteIds.Count() == 0)
{
return(null);
}
if (limit > 50)
{
limit = 50;
}
try
{
#region Joining the websiteids
var websiteIdsKey = new StringBuilder(String.Empty);
foreach (var websiteId in websiteIds)
{
if (!String.IsNullOrEmpty(websiteIdsKey.ToString()))
{
websiteIdsKey.Append(", '");
websiteIdsKey.Append(websiteId);
websiteIdsKey.Append("'");
}
else
{
websiteIdsKey.Append("'");
websiteIdsKey.Append(websiteId);
websiteIdsKey.Append("'");
}
}
#endregion
string query = $"select id, date, websiteId, keyword, pageurl, country, device, avgposition, impressions, clicks, feedback from searchAnalytics.gwtSearchAnalytics_kitsune where websiteId IN ({websiteIdsKey}) ORDER BY date DESC LIMIT {limit} OFFSET {offset}";
using (MySqlConnection mysqlConnectionObj = new MySqlConnection(GWTConnectionString))
{
mysqlConnectionObj.Open();
using (MySqlCommand command = new MySqlCommand(query, mysqlConnectionObj))
{
List <DetailedSearchAnalytics> detailedSearchAnalyticsList = new List <DetailedSearchAnalytics>();
using (var mysqlDataReaderObj = command.ExecuteReader())
{
while (mysqlDataReaderObj.Read())
{
try
{
string id = mysqlDataReaderObj.IsDBNull(0) ? "" : Convert.ToString(mysqlDataReaderObj["id"]);
DateTime date = Convert.ToDateTime(mysqlDataReaderObj["date"]);
string identifierVal = mysqlDataReaderObj.IsDBNull(2) ? "" : mysqlDataReaderObj.GetString(2);
string keyword = mysqlDataReaderObj.IsDBNull(3) ? "" : Convert.ToString(mysqlDataReaderObj["keyword"]);
string pageurl = mysqlDataReaderObj.IsDBNull(4) ? "" : Convert.ToString(mysqlDataReaderObj["pageurl"]);
string country = mysqlDataReaderObj.IsDBNull(5) ? "" : Convert.ToString(mysqlDataReaderObj["country"]);
string device = mysqlDataReaderObj.IsDBNull(6) ? "" : Convert.ToString(mysqlDataReaderObj["device"]);
int avgposition = Convert.ToInt32(mysqlDataReaderObj["avgposition"]);
int impressions = Convert.ToInt32(mysqlDataReaderObj["impressions"]);
int clicks = Convert.ToInt32(mysqlDataReaderObj["clicks"]);
string feedback = mysqlDataReaderObj.IsDBNull(10) ? "" : Convert.ToString(mysqlDataReaderObj["feedback"]);
if (string.IsNullOrWhiteSpace(identifierVal) || string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(keyword))
{
continue;
}
DetailedSearchAnalytics detailedSearchAnalytics = new KitsuneDetailedSearchAnalytics()
{
WebsiteId = identifierVal,
Id = id,
Date = date,
Keyword = keyword,
PageURL = pageurl,
Country = country,
Device = device,
AveragePosition = avgposition,
Impressions = impressions,
Clicks = clicks,
Feedback = null
};
if (detailedSearchAnalytics != null)
{
if (String.Compare(feedback, ("relevant"), true) == 0)
{
detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.RELEVANT;
}
else if (String.Compare(feedback, ("spam"), true) == 0)
{
detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.SPAM;
}
detailedSearchAnalyticsList.Add(detailedSearchAnalytics);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
if (detailedSearchAnalyticsList?.Count > 0)
{
return(detailedSearchAnalyticsList);
}
}
}
mysqlConnectionObj.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return(null);
}
public static List <DetailedSearchAnalytics> GetDetailedSearchAnalytics(string websiteId, DateTime startDate, DateTime endDate)
{
if (string.IsNullOrWhiteSpace(websiteId))
{
return(null);
}
try
{
string query = $"select id, date, websiteId, keyword, pageurl, country, device, avgposition, impressions, clicks, feedback from searchAnalytics.gwtSearchAnalytics_kitsune where websiteId = @identifier and date>=@startDate and date<@endDate";
using (MySqlConnection mysqlConnectionObj = new MySqlConnection(GWTConnectionString))
{
mysqlConnectionObj.Open();
using (MySqlCommand command = new MySqlCommand(query, mysqlConnectionObj))
{
command.Parameters.AddWithValue("@identifier", websiteId);
command.Parameters.AddWithValue("@startDate", startDate.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@endDate", endDate.ToString("yyyy-MM-dd"));
List <DetailedSearchAnalytics> detailedSearchAnalyticsList = new List <DetailedSearchAnalytics>();
using (var mysqlDataReaderObj = command.ExecuteReader())
{
while (mysqlDataReaderObj.Read())
{
try
{
string id = mysqlDataReaderObj.IsDBNull(0) ? "" : Convert.ToString(mysqlDataReaderObj["id"]);
DateTime date = Convert.ToDateTime(mysqlDataReaderObj["date"]);
string identifierVal = mysqlDataReaderObj.IsDBNull(2) ? "" : mysqlDataReaderObj.GetString(2);
string keyword = mysqlDataReaderObj.IsDBNull(3) ? "" : Convert.ToString(mysqlDataReaderObj["keyword"]);
string pageurl = mysqlDataReaderObj.IsDBNull(4) ? "" : Convert.ToString(mysqlDataReaderObj["pageurl"]);
string country = mysqlDataReaderObj.IsDBNull(5) ? "" : Convert.ToString(mysqlDataReaderObj["country"]);
string device = mysqlDataReaderObj.IsDBNull(6) ? "" : Convert.ToString(mysqlDataReaderObj["device"]);
int avgposition = Convert.ToInt32(mysqlDataReaderObj["avgposition"]);
int impressions = Convert.ToInt32(mysqlDataReaderObj["impressions"]);
int clicks = Convert.ToInt32(mysqlDataReaderObj["clicks"]);
string feedback = mysqlDataReaderObj.IsDBNull(10) ? "" : Convert.ToString(mysqlDataReaderObj["feedback"]);
if (string.IsNullOrWhiteSpace(identifierVal) || string.IsNullOrWhiteSpace(id) || string.IsNullOrWhiteSpace(keyword))
{
continue;
}
DetailedSearchAnalytics detailedSearchAnalytics = new KitsuneDetailedSearchAnalytics()
{
WebsiteId = identifierVal,
Id = id,
Date = date,
Keyword = keyword,
PageURL = pageurl,
Country = country,
Device = device,
AveragePosition = avgposition,
Impressions = impressions,
Clicks = clicks,
Feedback = null
};
if (detailedSearchAnalytics != null)
{
if (String.Compare(feedback, ("relevant"), true) == 0)
{
detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.RELEVANT;
}
else if (String.Compare(feedback, ("spam"), true) == 0)
{
detailedSearchAnalytics.Feedback = SearchQueryFeedbackEnum.SPAM;
}
detailedSearchAnalyticsList.Add(detailedSearchAnalytics);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
if (detailedSearchAnalyticsList?.Count > 0)
{
return(detailedSearchAnalyticsList);
}
}
}
mysqlConnectionObj.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return(null);
}
