All events arriving at CoolaData are marked as either valid or invalid. Invalid events are stored in a separate table, not included in queries.
Validity information can be seen in the Live Events page. The following three columns can be selected from the columns list:
- validation: valid/invalid/pending (a new event that will be added to the schema if valid)
- invalidComments: will specify the reason if the event is invalid.
- extraComments: will specify any changes made to the event data.
Once you resolve the validity issues, newly received events will be valid. Previously received events remain invalid in the CoolaData database.
Reasons an event could be invalid and method of handling
- Event Structure (JSON): If the event JSON is not a valid JSON structure the event will be invalid.
- Missing Data: CoolaData trackers manage user_id and event_timestamp_epoch automatically. However, when sending events via REST API all properties must be included. Each event must be sent with the following 3 mandatory properties:
- Wrong Data Type: If the data received with an existing property is of a different data type, the property will be invalid. For example, an integer property sent with quotation marks (as a string) will be invalid. To update/add properties go to the Project – Properties page.
- Property Quota: Each project is set up with a certain quota on the number of properties that can be used in the Data Scheme. If you reach the limit of the Data Scheme new properties will be invalid. To extend your Data Scheme quota, please contact your customer success manager.
- Date Out of Valid Range: A time stamp in the future or in the far past will be considered an invalid time stamp and the entire event will be invalidated. The default values of future/past events are: past – 30 days, future – 1 hour. This setting can be configured for your specific project. Contact email@example.com for further assistance.
- Mapping Modes: A project can have one of two mapping modes:
- In Locked mode, new events and properties are marked as invalid and will not be stored. If any of the properties in the event is invalid for any reason, the entire event will be marked invalid and will not be saved. To update/add events go to the Project – Events page. To update/add properties go to the Project – Properties page.
- In Self-Learned mode, new events and properties are automatically added to the project schema. Recognizing and registering new events and properties to the schema in Self-Learned mode can take up to 5 minutes, during which time new events will be marked as invalid.
For more information on mapping modes see Project Configuration.
Querying Invalid Events
- Wait an hour or two after sending the events to CoolaData using a Tracker.
- Open a new CQL report (click the CQL button in the main menu).
- Select a timeframe in the top, right of the window.
- Paste in the following query:
123SELECT date(msec_to_timestamp(failure_timestamp)) as date, SUBSTR(eventAsJson, INSTR(eventAsJson, 'event_name')+13, INSTR(substr(eventAsJson, INSTR(eventAsJson, 'event_name')+13),'"')-1) as event_name, count(*) as countFROM table_date_range(invalid_, timestamp(date_range_start), timestamp(date_range_end))GROUP BY 1,2
- To group events by failure reason, use the following query:
123SELECT date(msec_to_timestamp(failure_timestamp)) as date, SUBSTR(eventAsJson, INSTR(eventAsJson, 'event_name')+13, INSTR(substr(eventAsJson, INSTR(eventAsJson, 'event_name')+13),'"')-1) as event_name, reason, count(*) as number_of_invalidsFROM table_date_range(invalid_, timestamp(date_range_start), timestamp(date_range_end))GROUP BY 1,2,3