Introduction

Think of your favorite website: how did you find it? Did you use a search engine? Or click on an ad? Or follow a link in a blog post?

Web developers, marketers, and data analysts use that information to improve their sources (sometimes called channels or touchpoints) online. If an ad campaign drives a lot of visits to their site, then they know that source is working! We say that those visits are attributed to the ad campaign.

But how do websites capture that information? The answer is UTM parameters. These parameters capture when and how a user finds the site. Site owners use special links containing UTM parameters in their ads, blog posts, and other sources. When a user clicks one, a row is added to a database describing their page visit. You can see Datagran’s schema for a “page visits” table below.

Datagran web pixle generates raw logs in the form of a table. Below is the Schema of the basic elements:

• dg_ user_id - A unique identifier for each visitor to a page (it is defined by Datagran)
• hit_time_utc - The time at which the visitor came to the page
• hit_type - Datagran has 4 different ways to identify the action: a) pv: Page View b) click: click c) ce: Custom Event, d) fs: FormSubmission
• dynamic_payload - this column is JSON-formated string and contains querystring of landing page. Use JSON_EXTRACT function to transform into JSON i.e. JSON_EXTRACT(dynamic_payload, '$.utm_source'). You can find utm parameters here.
  • utm_source
  • utm_medium
  • utm_campaign
• hit_payload - this column contains information about the events described in hit_type column.
  • if the event is pv (page view), an example of typical hit_payload is {"title": "Datagran", "url": "[<https://www.datagran.io/>](<https://www.datagran.io/>)", "ref": "[<https://www.google.no/>](<https://www.google.no/>)"}
  • if the event is ce (custom event), an example of typical hit_payload is {"en": "Visit GT 1m", "ep": {"User id": "Capture from Cookie"}}
  • if the event is click, an example of typical hit_payload is {"tag": "a", "class": "navlink w-inline-block", "id": "", "text": "", "anchor": "", "pu": "[<https://www.datagran.io/>](<https://www.datagran.io/>)"}
• f_source - this column contains source name where visitor came from.

User table:

• dg_ user_id - A unique identifier for each visitor to a page (it is defined by Datagran)
• custom_user_id - A unique identifier for each visitor to a page set by the client. This identifier will be in a different table ending with user. (this id is defined by the customer with dg_tracker.identify('useridentify') function)

First Touch Example