/// <summary>
/// Returns a grouping of achieved goals against visits per variation
/// </summary>
/// <param name="goalItem"></param>
/// <param name="testItem"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List <VisitVariation> GetAchievedGoals(Item goalItem, Item testItem, DateTime startDate, DateTime endDate)
{
if (goalItem.IsNull() || testItem.IsNull())
{
return(new List <VisitVariation>());
}
try
{
using (AnalyticsDataContext AnalyticsDataContext = new AnalyticsDataContext(ConfigurationManager.ConnectionStrings["analytics"].ConnectionString))
{
//how many people saw the test
string visitQuery = "select count(*) as VisitCount, dbo.fn_abc_hexadecimal(p.testValues) AS TestValues from pages p " +
"where p.testSetId = {0} and p.[datetime] >= {1} and p.[datetime] <= {2} " +
"group by testValues";
//how many people performed action (EV) against the test?
string evQuery = "select count(*) as AchievedGoalCount, dbo.fn_abc_hexadecimal(testValues) as TestValues from " +
"(" +
"select p.itemId, p.[DateTime], (select distinct(testValues) from pages where testSetId = {0} and visitId = p.visitId) as testValues " +
"from pages p " +
"inner join pageEvents pe on pe.pageId = p.pageId " +
"where pe.pageEventDefinitionId = {1} and p.[datetime] >= {2} and p.[datetime] <= {3} " +
") t " +
"where testValues is not null " +
"group by testValues";
//get page hits
var visitResults = AnalyticsDataContext.ExecuteQuery <VisitVariation>(visitQuery, testItem.ID.RemoveBrackets(), startDate, endDate).ToList();
if (visitResults.Count == 0)
{
return(new List <VisitVariation>());
}
//get hits that hit goal
var engagementResults = AnalyticsDataContext.ExecuteQuery <VisitVariation>(evQuery, testItem.ID.RemoveBrackets(), goalItem.ID.RemoveBrackets(), startDate, endDate).ToList();
List <VisitVariation> variations = new List <VisitVariation>();
foreach (VisitVariation variationResult in visitResults)
{
VisitVariation result = variationResult;
Int32 achievedCount = engagementResults.Where(x => x.TestValues == result.TestValues).Select(x => x.AchievedGoalCount).FirstOrDefault();
result.AchievedGoalCount = achievedCount;
variations.Add(result);
}
return(variations);
}
}
catch (Exception e)
{
Logger.Error("Page Statistics Context - Could not retrieve the achieved goals");
Logger.Error(e.Message);
Logger.Error(e.InnerException);
}
return(new List <VisitVariation>());
}
/// <summary>
/// Retrieves goals met during visit
/// </summary>
/// <param name="visitId"></param>
public List <VisitGoal> GetGoalsMetDuringVisit(Guid visitId)
{
try
{
using (AnalyticsDataContext AnalyticsDataContext = new AnalyticsDataContext(ConfigurationManager.ConnectionStrings["analytics"].ConnectionString))
{
string sqlCommandText = "SELECT " +
"pe.pageEventDefinitionId as GoalId, " +
"ped.Name as GoalName, " +
"pe.DateTime as GoalMetDate, " +
"value as EngagementValue " +
"FROM pageEvents pe " +
"inner join PageEventDefinitions ped on pe.pageEventDefinitionId = ped.pageEventDefinitionId " +
"where " +
"isgoal = 1 " +
"and pe.visitId = {0}";
//get page hits
var visitResults = AnalyticsDataContext.ExecuteQuery <VisitGoal>(sqlCommandText, visitId).ToList();
if (visitResults.Count == 0)
{
return(new List <VisitGoal>());
}
return(visitResults);
}
}
catch (Exception e)
{
Logger.Error("Page Statistics Context - Could not retrieve goals met during visit");
Logger.Error(e.Message);
Logger.Error(e.InnerException);
}
return(new List <VisitGoal>());
}
/// <summary>
/// Returns a grouping of achieved goals against visits per variation
/// </summary>
/// <param name="goalItem"></param>
/// <param name="testItem"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List<VisitVariation> GetAchievedGoals(Item goalItem, Item testItem, DateTime startDate, DateTime endDate)
{
if (goalItem.IsNull() || testItem.IsNull())
{
return new List<VisitVariation>();
}
try
{
using (AnalyticsDataContext AnalyticsDataContext = new AnalyticsDataContext(ConfigurationManager.ConnectionStrings["analytics"].ConnectionString))
{
//how many people saw the test
string visitQuery = "select count(*) as VisitCount, dbo.fn_abc_hexadecimal(p.testValues) AS TestValues from pages p " +
"where p.testSetId = {0} and p.[datetime] >= {1} and p.[datetime] <= {2} " +
"group by testValues";
//how many people performed action (EV) against the test?
string evQuery = "select count(*) as AchievedGoalCount, dbo.fn_abc_hexadecimal(testValues) as TestValues from " +
"(" +
"select p.itemId, p.[DateTime], (select distinct(testValues) from pages where testSetId = {0} and visitId = p.visitId) as testValues " +
"from pages p " +
"inner join pageEvents pe on pe.pageId = p.pageId " +
"where pe.pageEventDefinitionId = {1} and p.[datetime] >= {2} and p.[datetime] <= {3} " +
") t " +
"where testValues is not null " +
"group by testValues";
//get page hits
var visitResults = AnalyticsDataContext.ExecuteQuery<VisitVariation>(visitQuery, testItem.ID.RemoveBrackets(), startDate, endDate).ToList();
if (visitResults.Count == 0)
{
return new List<VisitVariation>();
}
//get hits that hit goal
var engagementResults = AnalyticsDataContext.ExecuteQuery<VisitVariation>(evQuery, testItem.ID.RemoveBrackets(), goalItem.ID.RemoveBrackets(), startDate, endDate).ToList();
List<VisitVariation> variations = new List<VisitVariation>();
foreach (VisitVariation variationResult in visitResults)
{
VisitVariation result = variationResult;
Int32 achievedCount = engagementResults.Where(x => x.TestValues == result.TestValues).Select(x => x.AchievedGoalCount).FirstOrDefault();
result.AchievedGoalCount = achievedCount;
variations.Add(result);
}
return variations;
}
}
catch (Exception e)
{
Logger.Error("Page Statistics Context - Could not retrieve the achieved goals");
Logger.Error(e.Message);
Logger.Error(e.InnerException);
}
return new List<VisitVariation>();
}
/// <summary>
/// Retrieves goals met during visit
/// </summary>
/// <param name="visitId"></param>
public List<VisitGoal> GetGoalsMetDuringVisit(Guid visitId)
{
try
{
using (AnalyticsDataContext AnalyticsDataContext = new AnalyticsDataContext(ConfigurationManager.ConnectionStrings["analytics"].ConnectionString))
{
string sqlCommandText = "SELECT " +
"pe.pageEventDefinitionId as GoalId, " +
"ped.Name as GoalName, " +
"pe.DateTime as GoalMetDate, " +
"value as EngagementValue " +
"FROM pageEvents pe " +
"inner join PageEventDefinitions ped on pe.pageEventDefinitionId = ped.pageEventDefinitionId " +
"where " +
"isgoal = 1 " +
"and pe.visitId = {0}";
//get page hits
var visitResults = AnalyticsDataContext.ExecuteQuery<VisitGoal>(sqlCommandText, visitId).ToList();
if (visitResults.Count == 0)
{
return new List<VisitGoal>();
}
return visitResults;
}
}
catch (Exception e)
{
Logger.Error("Page Statistics Context - Could not retrieve goals met during visit");
Logger.Error(e.Message);
Logger.Error(e.InnerException);
}
return new List<VisitGoal>();
}