Where are Page Events that have not yet been aggregated in the Reporting Database stored?

Question

When I was using MongoDB on Sitecore 8.1, Page Events were stored in the Interactions collection; I could query them this way:

`db.getCollection('Interactions').find({ "Pages.PageEvents" : { $size: 1 } }).sort({StartDateTime: -1})`

I am creating Page Events and they are indeed being aggregated in the Reporting Database the next day; but despite my best efforts, I could not find where they are being stored.

Question

Where are Custom Page Events stored before Aggregation in an XP Scaled Sitecore 9.1 solution?

Answer 1

Page Event Definitions

These are stored under /sitecore/system/Marketing Control Panel/Events When you say "Custom Page Events" you would be talking about custom ones that exist here. There are several out of the box page events such as "Page View".

As with any item in the /sitecore/system/Marketing Control Panel area, these will only work if they are in the final workflow state and have been deployed to the Reference Data database. To deploy them you need to run Deploy Marketing Definitions from control panel.

Page Event Data in Sitecore 9

Events happen as part of an Interaction, and so they will be stored on the xDB Shards [xdb_collection].[Interactions] table.

Rough equivalent query from your Sitecore 8 Mongo query (last 7 days):

SELECT [Events],[LastModified],[StartDateTime],[EndDateTime]
    FROM [Shard0].[xdb_collection].[Interactions]
    WHERE LastModified >= DATEADD(day,-7, GETDATE())
UNION 
SELECT [Events],[LastModified],[StartDateTime],[EndDateTime]
    FROM [Shard1].[xdb_collection].[Interactions]
    WHERE LastModified >= DATEADD(day,-7, GETDATE())
ORDER BY [LastModified] DESC

You should then see data in the Events column in OData Json format. For example

[{"@odata.type":"#Sitecore.XConnect.Collection.Model.PageViewEvent","CustomValues":[],"DefinitionId":"9326cb1e-cec8-48f2-9a3e-91c7dbb2166c","ItemId":"f51e14d7-e75b-4846-abdd-cdafd6d58cc8","Id":"4adf8681-c8c2-4492-b6e1-81d166c1497f","Timestamp":"2019-06-27T05:21:33.5828090Z","Duration":"PT24.78S","ItemLanguage":"en","ItemVersion":1,"Url":"/","SitecoreRenderingDevice":{"Id":"fe5d7fdf-89c0-4d99-9aa3-b5fbd009c9f3","Name":"Default"}}

Data not what you expected?

Most likely you need to deploy your marketing definitions and make sure there are no errors on session end from the CD (this is when the events are written to xDB).