Basically, it is a kind of difficult sometimes to query the MongoDB directly, I had a similar scenario and following what I did:
I created aggregation processor that will be called when you rebuild the analytics database or when aggregation occurs:
public class PageEventProcessor : AggregationProcessor
{
protected override void OnProcess(AggregationPipelineArgs args)
{
Assert.ArgumentNotNull(args, "args");
var fact = args.GetFact<PageEvent>();
foreach (var page in args.Context.Visit.Pages)
{
try
{
foreach (var pEvent in page.PageEvents)
{
try
{
var eventkey = new PageEventKey
{
EventId = pEvent.PageEventDefinitionId,
Date = args.DateTimeStrategy.Translate(pEvent.DateTime),
ItemId = pEvent.ItemId,
PageId = page.Item.Id
};
var eventValue = new PageEventValue
{
Count = 1
};
fact.Emit(eventkey, eventValue);
}
catch (Exception ex)
{
}
}
}
catch(Exception ex)
{
}
}
}
}
Classes needed:
public class PageEventValue : DictionaryValue
{
public int Count { get; set; }
internal static PageEventValue Reduce(PageEventValue left, PageEventValue right)
{
var profileValue = new PageEventValue
{
Count = left.Count + right.Count
};
return profileValue;
}
}
public class PageEvent : Fact<PageEventKey, PageEventValue>
{
public PageEvent() : base(PageEventValue.Reduce)
{
}
}
public class PageEventKey : DictionaryKey
{
public Guid EventId { get; set; }
public Guid PageId { get; set; }
public Guid ItemId { get; set; }
public DateTime Date { get; set; }
}
Also you need to include the following configuration:
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
<sitecore>
<pipelines>
<group groupName="analytics.aggregation">
<pipelines>
<interactions>
<processor type="DllName.PageEventProcessor, DllName" />
</interactions>
</pipelines>
</group>
</pipelines>
</sitecore>
</configuration>
Hope this help!