Common reasons for data discrepancies

Companies often use several tools to understand their user’s behaviour. Alongside Cooladata, most companies also use Google Analytics or their own DB for comparison. 

We suggest investigating discrepancies if there is more than a 5% difference between Cooladata and other tools. Any less is likely not material enough to warrant a full tracking audit, since often analytics are used to identify trends (e.g. how fast are we growing?), rather than exact numbers. If the difference is greater than 5% across all events, or specific events don’t match between systems, then further investigation is called for.

Check this guide to identify common reasons for data discrepancies between various systems:

Timezone

Cooladata’s default timezone is UTC. When comparing data between Cooladata and other system such as Google Analytics, consider the time zone differences.

Double-check your query

When weird numbers appear in a report, it’s not always a data issue. Sometimes the report is just not querying what we intend it to query. Make sure you are querying the correct date range, and no filters are applied, filtering out relevant data.

Invalids

Cooladata automatically validates the data sent to Cooladata in order to prevent Garbage In – Garbage Out situations. Events marked as invalid are not stored with the rest of the valid events, but in a separate, designated table for invalid events, which you can query to check what went wrong. See Handling Invalid Events to learn more.

Data Sampling

Google Analytics sometimes use sampled data in reports, causing discrepancies in the numbers from Cooladata. Google Analytics sampling occurs automatically when more than 500K sessions are collected for a report. Google Analytics state that a report is based on sampling in text above the report. When comparing Cooladata to a sampled Google Analytics report, discrepancies are expectable.

Session Definition

A session in Cooladata starts when someone visits your site or app, sending an event, and ends after thirty minutes of inactivity. The session duration is calculated as the difference between the first and last event in that session. This thirty minutes timeframe is a configurable parameter. If you are the project’s admin, you can see this parameter under ‘Session timeout“ in your project settings page. Most analytics tools, such as Google Analytics use this thirty minute definition, which might cause discrepancies when comparing session duration or number of sessions if you set this parameter to be different than thirty minutes in Cooladata. Also, consider that Google Analytics will count additional sessions for clicks on AdWords campaigns, and will hard stop all sessions at midnight, whereas in Cooladata sessions occurring across midnight (starting before midnight and ending afterwards) would be stored as one session. Other mobile analytics tools platforms also end sessions if the user moved the app to the background for more than a minute.

Events are sent differently

A common cause for discrepancy is the way the events are sent to Cooladata and other tools. For instance, if one tool is receiving events from the server-side and the other from the client side, differences in numbers will most likely occur.

Even if both tools are sent from the client side, the code needs to be checked. Sometimes, there is a logical condition for sending an event to one tool which is not the same as the code sending the event to the other tool. When using JS SDK, the location of the trackEvent code is important. If the call sending the event to one tool is at the top of the code and the call sending the event to another tool is at the bottom of the code, there might be some discrepancies, due to an error in the code or if the user manually closed the window before the trackEvent function was called.

Bots and Test Users

Some tools automatically filter out events created by bots, Cooladata does not. Cooladata does have several solutions available if you wish to slice out bad IP’s or bots. To find the best solution for you, contact your Customer Support Manager.   

If your operational DB automatically cleans test user’s activity, make sure you filter out test users in Cooladata as well.

Funnel and Conversion Definitions

Funnels in Cooladata count distinct (unique) users who completed the funnel in the date range in question, in the time window set in the report. Conversions are sometimes defined differently in other tools. For instance, Google Analytics count the number of sessions in which the funnel’s steps were completed.  

Notice that if you choose to set the funnel in Cooladata to show users who completed the funnel by X days, the funnel will only include users who did the first event X days before the end of the report date range when looking at the last week or month. This is done in order to give users who came in at the beginning of the date range, the same “chance” to complete the funnel as users who came in at the end of the date range.

Redirects and Self Referrals

When looking at Cooladata’s “referring_url” and “referring_domain” you should see the url and domain the user was referred from. Sometimes, you see url’s and domain’s that you do not expect, such as your own url. This happens when your site uses redirecting rules, usually set up by your site admin.

Cooladata Sessions Table

Cooladata stores your data in two separate tables: one is your event table, in which every row is an event, and the other is your sessions table, in which every row is a session. The event table holds all the event-level data and event-scope properties, as well as user and session scope properties. The sessions table holds session-specific properties (such as session duration and the session path) as well as session and user scope properties. In order to optimize performance, Cooladata automatically shifts your queries to run on top of the sessions table if all the data you are searching for is there. For instance, Daily Active Users (DAU), counting number of unique users per day will run over the sessions table, but if you want to count Daily Active Payers (pDAU), you will have to run over the events table in order to add a filter for the payment event. Shifting between the sessions and events table might cause for slight differences due to the fact that when running over the sessions table, the date range is filtered according to the session start time (session_start_time_ts) whereas when running over the events table the date range is filtered according to the event (event_time_ts)  timestamp.

Aggregation Tables

Aggregation Tables automatically run scheduled calculations and data aggregations and save them in a separate, permanent table. This can be used to:

  1. Compute and store aggregated data in order to enhance the performance of queries running on large scales of data.
  2. Fetch data from external data bases into tables in your project for faster and easier querying.
  3. Send events data to your project from external data bases. Contact you Customer Success Manager or email us at support@cooladata.com for more information on this function.

Multiple tasks can be created on the same table. In this way you can make different manipulations to the same table, in multiple queries and/or schedules. For example, first fetch the data from your linked data source, then add an aggregation to it in the same table.

Table are saved until manually deleted. To delete a table, delete all tasks related to it.

 

Creating/Editing an Aggregation Table

  1. From the Project menu, go to Aggregation Tables.
  2. A list of your saved Aggregation Tables is displayed. Click a task to open it.
  3. To create a new Aggregation Table, click the + button at the top right, or the Add Aggregation button, in case your list is empty. The following opens:
  4. In the Aggregation Table editor page, enter the following details:
    • Task Name: The name of the task. Supports any text. Enter something descriptive to help you identify the task.
    • Query: enter any query, on any data source, including all tables and linked data sources in your project.
      Notes:

      • Do not use the “filters (context)” or “date_range (context)” features in queries run via Aggregation Tables. This is because there is no (report/dashboard) context for these kind of queries, and therefore the query will fail.  Instead write explicit date range and conditions.
        Tip: use the report options “Show CQL” to see the final query (without “context”) when copying a query from a report to an Aggregation Table.
      • The top query aliases are stored as the columns names in the table, and therefore cannot contain spaces, special characters or begin with a number.
      • When editing a saved query, only change the names or number of columns in the final results if using “Replace” in Write mode (see below), otherwise the the schema of the results on the next run will not match the saved table, and it will fail.
    • Run the query to see a preview of your results. By default the preview is set to the top 50 rows – change this figure for the right hand side of the Run button row.
    • Settings:
      • Save to: where the table will be saved. The default options is “Cooladata”, which means the results will be saved to a table in your project.
        Additionally, Linked Data Sources in your project can be used as the save to destination, which means the results will be saved to a table in your database (make sure to give Cooladata write permissions on your database).
        Another option is sending the results of the query as events to your projects. Choose to save to “Cooladata (events)”. Make sure to review our documentation on sending batch events to Cooladata before you do this.
      • Table name: the table to which the data will be saved. Table names are case sensitive, and cannot include spaces or special characters. Take care when using an existing table, as existing data might be overwritten.
      • Write mode:
        • Append: new rows are added to the table each time the query is computed.
        • Append and update: new rows are added to the table each time the query is computed, and existing rows that match the unique key you selected will be replaced with the updated data.
        • Replace: The entire table data is overwritten each time the query is computed.
    • Notify on Failure to: email/s to be notified if any scheduled run fail.
    • Schedule: when to run the query.
      • Active: turn this off to prevent any run of this task. Note that this will also block Jobs from running it.
      • Frequency:
        • Daily: At a specific hour of the day (UTC).
        • Weekly: On a specific day of the week, at a specific hour of the day (UTC).
        • Monthly: On a specific day of the month, at a specific hour of the day (UTC).
        • CRON: Set the frequency by specifying a CRON expression. The CRON expression should be in the format of – minutes (0 – 59) hour (0 – 23) day of month (1 – 31) month (1 – 12) day of week (0 – 6). The star symbol (*) should be placed as a wild card. For example, this CRON expression will run the Aggregation Table daily at 3:30 am UTC: 30 3 * * *. You may refer to www.cronmaker.com for a description of CRON expression syntax.
      • Run now: if the task has been saved, click to run it immediately and update the table.
  1. Click Save to save this task. If you entered a new table name, the table will be created on the next (successful) run. Click Save and Run to create/update the table with your new/updated query immediately.

 

Querying Aggregation Tables

To query an Aggregation Table, in a CQL query, as with any table in your project, state the table name in the FROM clause, instead of “cooladata”. For example:

FROM users_table

You can also use these tables as the data source for various report builders, such as KPI. See the report documentation for more information.

To use a date picker on a query from a table, use the TABLE_DATE_RANGE function – see Date and Time Functions for more information.

Aggregation Tables saved to your project can be found in the Schema, under Aggregation Tables.

  • Expand any table to see the table’s columns and their data types.
  • Drag&drop a table/column name to use it in a query.

 

Manipulating Tables

Cooladata provides you direct access to your tables.
Note to use this with care, as overwritten/deleted data cannot be restored.
Internal Cooladata tables (events data) are protected from these methods.

Insert Into

With the “insert into” expressions you can append new data to any of your external tables. You can append data to any of the existing data columns in the table. Data columns you do not append to will be updated with NULL values.

Example – the following query will append data to the user_country column in my_table table (other columns in the table will be updated with NULL values):

insert into my_table(
  select ip_country 
  from cooladata
  where date_range(last 7 days) 
  group by ip_country
  )

Truncate Table

With the “truncate table” expressions you can clear any data stored in one of your external tables. This method will remove all lines from the table, leaving it empty, but retain the table’s data scheme, so that you can append new data to it later on.

Example – the following query will empty the table my_table:

truncate table my_table

Drop Table

With the “drop table” expressions you can delete any of your external tables. This will complete remove the table and all its data, and this action cannot be undone.

Example – the following query will delete the table my_table:

drop table my_table

Alerts

Alerts enable you to receive notifications on changes or special business cases in your data.

Alerts can be used in two business cases:

  • To monitor the system (operational)
  • To indicate a business change

When you define an alert, you will receive an email containing a set of reports whenever a condition you define applies, according to the schedule you specify.

The email graphically displays the report charts and provides an attached CSV file containing the raw data of the reports.

 

Defining an alert

To define a new alert:

  1. From the main menu, choose Project  – Alerts. This shows a list of all previously defined alerts in the project. From here you can also update and delete existing alerts.
  2. Click the Add + button at the top right corner.
  3. Define your alert:
    • Name: Give your alert a name – it will be included in the email subject and body.
    • CQL: add a CQL query that will trigger the alert. The query must return a single numeric value. For example, the default query returns the number of events received during the previous day:

      SELECT count(*)

      FROM Cooladata

      WHERE date_range (yesterday)

    • Condition: define a limit value for the query response, above/below which an alert will be sent. For example, the default condition will send an alert if the query returns a result of less than 1, i.e. if no events were received during the previous day.

      Notice that the alert will refer to null values as 0.

    • Check Condition: you can check the query and condition by clicking Check Condition – the query response and trigger result will be shown (if the query is valid).
    • Frequency: specify the schedule for checking/sending the alert:
      • Daily: specify the hour at which the report should be sent (UTC)
      • Weekly: specify the day of the week and hour at which the report should be sent (UTC)
      • Monthly: specify the day of the month and hour at which the report should be sent (UTC)
      • CRON Expression: see www.cronmaker.com for a description of CRON expression syntax.
    • Recipients List: select one or more CoolaData users and/or enter any email address (can also add emails not registered to CoolaData). Add your own email as a recipient to get a copy of the alert when it is sent.
    • Text: you can add a rich HTML text to the email body. This is not mandatory.
    • Reports to include: Select one or more reports from the project to include in the email body. This is not mandatory.
    • Click Save.
    • Run Now: Once saved, you can also run the alert manually by clicking Run Now – if the condition is triggered, the alert email will be sent to the list of recipients you defined.

 

Alert Builder

Another way to define alerts is to use CoolaData’s alert builder. To access the builder, first select the ‘Alerts’ tab in your settings menu.

After selecting the ‘Alerts’ tab you will be transferred to the alert list, where your different alerts will be concentrated. On the top right-hand corner of the list you can find the following menu, from which you can access the alert builder:

The alert builder is comprised of two sections:

The top section defines the alert’s trigger settings. You may use your existing KPIs, in addition to condition of your choice to choose the alert’s trigger. In this example the alert will be triggered if the user count has increased by 10% compared to the previous day. The button ‘Check Condition’ runs a test in which the trigger is checked, when pressed message will appear next to it detailing whether the condition is met at the time of the manual check.

The bottom section defines the alert’s running settings. If the condition set in the top section is met, an alert will be sent out via email. This section allows you to define the sent alert’s settings, as well as it’s checking frequency. Recipients, text and email attached reports are also defined in this section. In this example the test will run daily, and if it’s conditions are met an alert will be sent to ‘example@cooladata.com’, with the text ‘User count increased by 10%’. In this case there will be no added report, but you may choose to add any of your predefined reports to the alert message.

If you are interested in viewing the CQL code components of your query, you may select the ‘Options’ tab on the top right-hand corner of your screen, under which you will find the ‘Show CQL’ option.

After naming and defining your alert you will be able to save it. Once saved an alert will run a trigger check according to the selected frequency. You may also select the ‘Manual’ frequency in order to save an alert without running it on a scheduled time.

Alert History

To see when an alert was last run or sent, select History from the list item menu.

Here you can also see which reports were sent and the number of recipients. Click each row to see more details.

Step 2 – Implement Cooladata

Sign up to Cooladata

  1. Go to http://app.cooladata.com.
  2. Click Sign up.
  3. Fill in the form and click Sign up
  4. Wait until you receive an email with a registration link to the email you signed up with, and click the link to log in.

 

Create a Project

To create a new project:

  1. In the top right corner, click the Create your first project button.
  2. Enter the name of the project.
  3. Click CREATE.

With CoolaData, you can put all your data into a single project or you can divide your data up among multiple projects – as you see fit.

Why create multiple projects? 

You control which dashboards, reports and publications users see. However, Admin users can access all the data in any project to which they are allowed access. To control the data that each CoolaData Admin users can access you can create multiple CoolaData projects.

For example, in an ecommerce site, you might have different stores – each belonging to a different company. Therefore, you might only want each company’s Admin user to see the data of their own company. In this case, you should create a project for each company, so that each Admin user can only access their own data in dashboards, publications and using the CoolaData API.

 

Track Events

To start tracking events using the Cooladata trackers:

  1. After creating a project, if the page shown is not already displayed, select Project – Tracking.
  2. Choose the required OS and follow the on-screen instruction.

See more details and implementation options in the Sending Data section.

 

Monitor Incoming Events

CoolaData enables you to monitor incoming events so that you can verify proper integration with your application/website, regardless of the type of Tracker you use to send events to CoolaData.

As each event is received by CoolaData, it is classified as either valid or invalid and is displayed in the Live Events window within approximately five minutes. Valid events undergo additional processing by CoolaData and within an hour or two after being received, may be reclassified as invalid. In order to investigate the reason for invalid events, CoolaData provides a details option in the Live Events window and enables you to query its database for invalid events. See Handling Invalid Events for more information.

The Live Events page is used to monitor your incoming events to CoolaData. Open the page from the main menu Project – Live Events.

Events are shown in near realtime – from the time you send an event it may take up to a minute to appear on this page. The page shows the 500 most recently received events, sorted by the time they were received, with the most recent events appearing first. Click any column title to sort the list.

Each row represents an event. Click a row to see the raw JSON format it was sent in.

You can search the list or specific columns in the list from the top left search box.

The columns displayed in this list can be customized from the top right icon. The list of columns contains all available common properties in the project, as well as the following information:

  • raw_data: displays the original JSON sent (can also be seen by clicking the event).
  • Validity: an events can be valid – will be saved, invalid – will not be saved, or pending – a new event that will be saved if doesn’t conflict with other events in the project schema.
  • invalidComments: invalid events will specify the rejection reason in this column.
  • extraComments: pending events will specify the reason in this column.

JavaScript

The Cooladata JavaScript Tracker gives you full control over events sent from your website, by defining default setting (init), sending custom events, and more advanced options.

Initialization

The init function initializes a new instance of the CooladataTracker object.
To start tracking with Cooladata JavaScript Tracker, add the following code to the header of each page you want to track (between the <head> and the </head> tags) and replace the app key with your own:

Note: on WordPress sites, add the snippet in the header.php file. It can usually be found in the wp-admin under appearance -> Editor -> theme header(header.php).

<!-- Start Cooladata -->
<script type="text/javascript">
    (function(d,a){if(!a.__SV){var b,c,g,e;window.cooladata=a;a._i=[];a.init=function(b,c,f){function d(a,b){var c=b.split(".");2==c.length&&(a=a[c[0]],b=c[1]);a[b]=function(){a.push([b].concat(Array.prototype.slice.call(arguments,0)))}}var h=a;"undefined"!==typeof f?h=a[f]=[]:f="cooladata";g=["trackEvent","trackEventLater","trackPageload","flush","setConfig"];for(e=0;e<g.length;e++)d(h,g[e]);a._i.push([b,c,f])};a.__SV=1.2;b=d.createElement("script");b.type="text/javascript";b.async=!0;b.src="https://cdn.cooladata.com/tracking/cooladata-latest.min.js";c=d.getElementsByTagName("script")[0];c.parentNode.insertBefore(b,c)}})(document,window.cooladata||[]);

    cooladata.init({
        "app_key": "<YOUR_APP_KEY>", //replace with the app key, found under "Event Sources" in the Cooladata project menu
        "track_pageload": true,
        "img_src_get_request": true
    });

</script>
<!-- End Cooladata -->

The above setup includes the recommended implementation options:

  • Track page views automatically
  • Uses a tracking pixel

The following table includes all configuration options:

ParameterTypeMandatoryDefaultDescription
app_keyStringYesThe specific project app key provided by CoolaData
http_postBooleanNoFalseUse HTTP POST method instead of the default GET method
img_src_get_requestBooleanNoFalseSend an image (1x1 pixel) as GET request for XHR requests
disable_cookieBooleanNoFalseDisables cookie creation on the user browser when tracking an event. When true, the user ID sent by CoolaData will change with every page load, and user identification management will be dependent on the user ID sent in the event tracker ("user_id" in the tracker, "customer_user_id" in CQL).
api_hostStringNoapi.cooladata.comReplace the default API domain with a custom domain as the tracker endpoint. For example: "api_host":"tracking.com" will be sent to: http://tracking.com/v1/ [app_key]/track
track_pageload*BooleanNoFalseSend page_load event automatically every time a user views the page.
Sent with the following properties: page_url, page_url_params, page_title.
user_idStringNoFalseSpecify the user_id on the page's cooladata tracker init to associate all events (including track_pageload) to the current user.

* Note: The track_pageload parameter is sent together with the user_id. If no user_id was explicitly specified in the cooladata.init() function, a random user_id will be generated and used for each event. Pay attention that if you DON’T send a user_id in the init function but send one in the cooladata.trackEvent() both random and custom user_ids will not be associated with each other.

Track Custom Events

Once you have the Tracker snippet in your page, you can track an event by calling cooladata.trackEvent(); with the event name and its properties:

How to use:

cooladata.trackEvent('<event_name>',{'<property_name1>':'<property_value1>', '<property_name2>':'<property_value2>'});

The function is built of a few parts:

  • The function call: cooladata.trackEvent()
  • The event name
  • The properties & their values a JSON. The JSON must be “flat” so you can’t use any nested JSON.

Example:

cooladata.trackEvent('Add Item', {'Type': 'T-Shirt', 'Amount': 2, 'email':'sam@email.com'});

Each event can include properties. For example, the above ‘Add Item‘ event includes the item type, number of items, and the purchaser’s email address. Read more about properties.

You could also use JQuery to send an event to the Cooladata server. In this example a function is called when a form’s SEND button is clicked (this example uses ninja forms). JQuery is used here to extract the data from the differentn form’s fields.

jQuery(document).ready(function($){
    var sendButton = $("#ninja_forms_field_51"); //the form object sending the form (send button)
    sendButton.click(function(){
        callTrack4();
    });
});

function callTrack4(){
    var name_var = $("#ninja_forms_field_45").val(); //form variables for properties
    var lastName_var = $("#ninja_forms_field_46").val();
    var email_var = $("#ninja_forms_field_47").val();
    var phone_var = $("#ninja_forms_field_48").val();
    var property_value_var = $("#ninja_forms_field_49").val();
    cooladata.trackEvent('secondPopup', {'name': name_var, 'last_name':lastName_var, 'email':email_var , 'phone':phone_var,'property_value':property_value_var}); //CoolaData trackEvent
}

In the table below you can find all cooladata.trackEvent() elements (some are optional):

ParameterTypeMandatoryDescription
eventNameStringYesThe name of the event to report.
eventPropertiesObjectNoObject containing all additional properties in format: "property_name":"property_value". See example below.
user_idStringNoSend if you want to be able to identify users according to your definition for user ID. For example: use this for cross-platform identification where users perform a log-in action.
event_timestamp_epochNumberYes**Must be sent if the project is NOT set to override event time-stamp with server time.
Format is milliseconds (13 digits) only. If the project is defined to override event time-stamp with server time, any time-stamp sent with the event will be ignored.
custom propertiesString/NumberNoAny additional properties mapped in the project. See more information in Managing Properties.

Use a callback function

In some cases, after sending an event to the server, you would want to execute some code on the Browser. This can be easily done by adding a callback function to your cooladata.trackEvent() call.

The process works as follows: Tracker sends event to Cooladata server -> Cooladata server sends OK response to Tracker -> callback function is executed.

How to use:

cooladata.trackEvent('<event_name>',{'<property_name>':'<property_value>'},function(){
  //your callback function code here
});

Example:

cooladata.trackEvent('get_coupon',{'discount':'35%',function(){ 
  window.alert('You've got 35% discount!') 
});

Track a bulk of events

The cooladata.trackEventLater() method provides the ability to track a bulk of events. All events will be stored on a local queue until the cooladata.flush() method is called. This method can be useful when you want to submit more than one event at a time, for example to send a list of events that are linked together all in a single request, instead of multiple requests.

Example:

1. Add an event to the queue:

cooladata.trackEventLater("register_submit",{"user_id":"JohnDoe", "subscription_active":0})

2. Add a second event to the queue:

cooladata.trackEventLater("add_to_cart",{"user_id":"JohnDoe", "sku":JSV-4532})

3. Send the events to the Cooladata server

cooladata.flush();