Database Schema
FH Analytics persists two main types of data:
- log data: raw analytics data that FH Analytics receives in the tracker.
- archive data: aggregated analytics data (constructed from log data) that is cached and used to build reports.
FH Analytics also persists other simpler forms of data including:
- websites
- users
- goals
- options
Log data
There are five types of log data:
- visits
- action types
- plugin specific actions
- conversions
- ecommerce items
All log data is persisted in a similar way: new data is constantly added to the set at high volume and updates are non-existent, except for visits.
Visit data is updated while visits are active. So, until a visit ends it is possible that FH Analytics will try to update it.
Log data is read when calculating analytics data and old data will sometimes be deleted (via the data purging feature).
Backends must ensure that inserting new log data is as fast as possible and aggregating log data is not too slow (though obviously, faster is better).
Meta Details
Table Name: piwik_log_link_visit_action
Database Name: fh_analytics
| Column Name | Data Type | Description |
| idlink_va | decimal(20,0) | The ID of the log_link_visit_action entry that matched this step. |
| idsite | bigint | The ID of the website. |
| idvisitor | binary | The ID of the visitor that caused this crash. |
| idvisit | decimal(20,0) | The ID of the visit that caused this crash. |
| idaction_url_ref | bigint | The previous pageview’s Page URL. |
| idaction_name_ref | bigint | The previous pageview’s Page Title. |
| custom_float | double | An unspecified float field, mainly used to store the Custom Event value, as well as store the time it took the server to serve this action. |
| server_time | timestamp | The server timestamp on which this crash event was reported. |
| idaction_name | bigint | The ID of the page title action type for this action. |
| idaction_url | bigint | A log_action ID referencing on what page URL this page view was recorded. |
| time_spent_ref_action | bigint | This column contains the time spent by the visitor on their previous pageview. |
| idaction_event_action | bigint | The ID of the action type for this event. |
| idaction_event_category | bigint | This refers to the ID for other action types. |
| idaction_content_interaction | bigint | The ID for the interaction with the content. |
| idaction_content_name | bigint | This refers to the name (header) of the content. |
| idaction_content_piece | bigint | This refers to part/piece of the content. |
| idaction_content_target | bigint | This refers to target (audience) of the content. |
| custom_var_k1 | varchar(200) | The custom variable name of the first slot for page custom variables. |
| custom_var_v1 | varchar(200) | The custom variable value of the first slot for page custom variables. |
| custom_var_k2 | varchar(200) | The custom variable name of the second slot for page custom variables. |
| custom_var_v2 | varchar(200) | The custom variable value of the second slot for page custom variables. |
| custom_var_k3 | varchar(200) | The custom variable name of the third slot for page custom variables. |
| custom_var_v3 | varchar(200) | The custom variable value of the third slot for page custom variables. |
| custom_var_k4 | varchar(200) | The custom variable name of the fourth slot for page custom variables. |
| custom_var_v4 | varchar(200) | The custom variable value of the fourth slot for page custom variables. |
| custom_var_k5 | varchar(200) | The custom variable name of the fifth slot for page custom variables. |
| custom_var_v5 | varchar(200) | The custom variable value of the visit in the fifth slot for visit custom variables. |
| time_spent | bigint | The amount of time spent on this action (set by the CustomDimensions plugin). |
| custom_dimension_1 | varchar(255) | Dimension 1 for the custom report. |
| custom_dimension_2 | varchar(255) | Dimension 2 for the custom report. |
| custom_dimension_3 | varchar(255) | Dimension 3 for the custom report. |
| custom_dimension_4 | varchar(255) | Dimension 4 for the custom report. |
| custom_dimension_5 | varchar(255) | Dimension 5 for the custom report. |
| custom_var_k6 | varchar(200) | The custom variable name of the sixth slot for page custom variables. |
| custom_var_v6 | varchar(200) | The custom variable value of the visit in the sixth slot for visit custom variables. |
| custom_var_k7 | varchar(200) | The custom variable name of the seventh slot for page custom variables. |
| custom_var_v7 | varchar(200) | The custom variable value of the visit in the seventh slot for visit custom variables. |
| custom_var_k8 | varchar(200) | The custom variable name of the eighth slot for page custom variables. |
| custom_var_v8 | varchar(200) | The custom variable value of the visit in the eighth slot for visit custom variables. |
| custom_var_k9 | varchar(200) | The custom variable name of the ninth slot for page custom variables. |
| custom_var_v9 | varchar(200) | The custom variable value of the visit in the ninth slot for visit custom variables. |
| custom_var_k10 | varchar(200) | The custom variable name of the tenth slot for page custom variables. |
| custom_var_v10 | varchar(200) | The custom variable value of the visit in the tenth slot for visit custom variables. |
| idpageview | char(6) | The ID of the pageview entry matching the log_link_visit_action.idpageview column. Allows us to assign this interaction to a specific action. |
| interaction_position | int | The action is interaction position. |
| custom_var_k11 | varchar(200) | The custom variable name of the eleventh slot for page custom variables. |
| custom_var_v11 | varchar(200) | The custom variable value of the visit in the eleventh slot for visit custom variables. |
| custom_var_k12 | varchar(200) | The custom variable name of the twelfth slot for page custom variables. |
| custom_var_v12 | varchar(200) | The custom variable value of the visit in the twelfth slot for visit custom variables. |
| custom_var_k13 | varchar(200) | The custom variable name of the thirteenth slot for page custom variables. |
| custom_var_v13 | varchar(200) | The custom variable value of the visit in the thirteenth slot for visit custom variables. |
| custom_var_k14 | varchar(200) | The custom variable name of the fourteenth slot for page custom variables. |
| custom_var_v14 | varchar(200) | The custom variable value of the visit in the fourteenth slot for visit custom variables. |
| custom_var_k15 | varchar(200) | The custom variable name of the fifteenth slot for page custom variables. |
| custom_var_v15 | varchar(200) | The custom variable value of the visit in the fifteenth slot for visit custom variables. |
| custom_dimension_6 | varchar(255) | Dimension 6 for the custom report. |
| custom_dimension_7 | varchar(255) | Dimension 7 for the custom report. |
| custom_dimension_8 | varchar(255) | Dimension 8 for the custom report. |
| custom_dimension_9 | varchar(255) | Dimension 9 for the custom report. |
| custom_dimension_10 | varchar(255) | Dimension 10 for the custom report. |
| custom_dimension_11 | varchar(255) | Dimension 11 for the custom report. |
| custom_dimension_12 | varchar(255) | Dimension 12 for the custom report. |
| custom_dimension_13 | varchar(255) | Dimension 13 for the custom report. |
| custom_dimension_14 | varchar(255) | Dimension 14 for the custom report. |
| custom_dimension_15 | varchar(255) | Dimension 15 for the custom report. |
| time_network | int | This indicates the network time. |
| time_server | int | This indicates the server time. |
| time_transfer | int | This indicates the time take for the file transfer. |
| time_dom_completion | int | This column contains the time taken for completion of the DOM Element. |
| time_dom_processing | int | This column contains the time taken to process the DOM Element. |
| time_on_load | int | This column contains the time taken to load for previous pageview. |
| pageview_position | int | The position of the pageview within the visit, starting at 1. If the action being recorded is not a pageview (but is another action like an event, download, etc.) then the position is set to the last recorded pageview’s position (before this action was recorded). |
| search_cat | varchar(200) | The custom variable name for the search category. |
| search_count | int | This refers to the number of search count. |
| product_price | double | This refers to the price of the product. |
| idaction_product_cat5 | int | This refers to the ID for product category 5. |
| idaction_product_cat4 | int | This refers to the ID for product category 4. |
| idaction_product_cat3 | int | This refers to the ID for product category 3. |
| idaction_product_cat2 | int | This refers to the ID for product category 2. |
| idaction_product_cat1 | int | This refers to the ID for product category 1. |
| idaction_product_cat | int | This refers to the ID for product category. |
| idaction_product_sku | int | The ID for the product SKU for this action. |
| idaction_product_name | int | The ID for the product name for this action. |
Meta Details 2
Table Name: piwik_log_action
Database Name: fh_analytics
| Column Name | Data Type | Description |
| idaction | bigint | The ID of the action type. |
| name | varchar(40996) | The visitor’s name |
| hash | bigint | A hash value calculated using the name. |
| type | int | The action type |
| url_prefix | int | If the name is a URL this refers to the prefix of the URL. The prefix is removed from actual URLs so the protocol and www. parts of a URL are ignored during analysis. Can be the following values: 0: ‘http://’ 1: ‘http://www.’ 2: ‘https://’ 3: ‘https://www.’ |
Meta Details 3
Table Name: piwik_log_visit
Database Name: fh_analytics
| Column Name | Data Type | Description |
| idvisit | decimal(20,0) | The ID of the visit. |
| idsite | bigint | The ID of the website it was tracked for. |
| idvisitor | binary | The visitor ID (an 8-byte binary string). |
| visit_last_action_time | timestamp | The datetime of the visit’s last action. |
| config_id | binary | A hash of all the visit’s configuration options, including the OS, browser name, browser version, browser language, IP address and all browser plugin information. |
| location_ip | binary | The IP address of the computer that the visit was made from. Can be anonymized. |
| user_id | varchar(200) | The User ID (if set). |
| visit_first_action_time | timestamp | The datetime of the visit’s first action. |
| visit_goal_buyer | boolean | Whether the visitor ordered something during this visit or not. |
| visit_goal_converted | boolean | Whether this visit converted a goal or not. |
| visitor_days_since_first | int | The number of days since the visitor’s first visit (if any). |
| visitor_days_since_order | int | The number of days since this visitor’s last order (if any). |
| visitor_returning | boolean | Whether the visit is the first visit for this visitor or not. |
| visitor_count_visits | bigint | The number of visits the visitor has made up to this one. |
| visit_entry_idaction_name | bigint | The ID of the page title action type of this visit’s first action. |
| visit_entry_idaction_url | bigint | The ID of the URL action type of the visit’s first action. |
| visit_exit_idaction_name | bigint | The ID of the page title action type of the visit’s last action. |
| visit_exit_idaction_url | bigint | The ID of the URL action type of the visit’s last action. |
| visit_total_actions | bigint | The count of actions performed during this visit. |
| visit_total_searches | int | The count of site searches performed during this visit. |
| referer_keyword | varchar(255) | The keyword used if a search engine was the referrer. |
| referer_name | varchar(70) | This means, depending on the specific referrer type. |
| referer_type | int | The type of this visitor’s referrer. Can be one of the following values: Common::REFERRER_TYPE_DIRECT_ENTRY = 1: If set to this value, other referer_… fields have no meaning.Common::REFERRER_TYPE_SEARCH_ENGINE = 2: If set to this value, referer_url is the url of the search engine and referer_keyword is the keyword used (if we can find it).Common::REFERRER_TYPE_WEBSITE = 3: If set to this value, referer_url is the url of the website.Common::REFERRER_TYPE_CAMPAIGN = 6: If set to this value, referer_name is the name of the campaign.Common::REFERRER_TYPE_SOCIAL_NETWORK = 7: If set to this value, referer_name is the name of the social network. |
| referer_url | varchar(65535) | The referrer URL; its meaning depends on the specific referrer type. |
| location_browser_lang | varchar(20) | A string describing the language used in the visitor’s browser. |
| config_browser_engine | varchar(10) | A short string identifying the browser engine used to make this visit. |
| config_browser_name | varchar(10) | A short string identifying the browser used to make this visit. |
| config_browser_version | varchar(20) | A string identifying the version of the browser used to make this visit. |
| config_device_brand | varchar(100) | A string identifying th brand of the device used to make this visit. |
| config_device_model | varchar(100) | A string identifying the model of the device used to make this visit. |
| config_device_type | int | This identifies the type of the device used to make this visit. |
| config_os | char(3) | A short string identifying the operating system used to make this visit. |
| config_os_version | varchar(100) | A string identifying the version of the operating system used to make this visit. |
| visit_total_events | bigint | The count of custom events performed during this visit. |
| visitor_localtime | timestamp | The visit datetime in the visitor’s time of day. |
| visitor_days_since_last | int | The number of days since the visitor’s last visit (if any). |
| config_resolution | varchar(18) | A string identifying the screen resolution the visitor used to make this visit (e.g., ‘1024×768’). |
| config_cookie | boolean | Whether the visitor’s browser has cookies enabled or not. |
| config_director | boolean | Whether the visitor’s browser can view the directory or not. |
| config_flash | boolean | Whether the visitor’s browser can view flash files or not. |
| config_gears | boolean | Whether the visitor’s browser can have the access to the gears or not. |
| config_java | boolean | Whether the visitor’s browser can run Java or not. |
| config_pdf | boolean | Whether the visitor’s browser can view PDF files or not. |
| config_quicktime | boolean | Whether the visitor’s browser uses quicktime to play media files or not. |
| config_realplayer | boolean | Whether the visitor’s browser can play realplayer media files or not. |
| config_silverlight | boolean | Whether the visitor’s browser can run silverlight programs or not. |
| config_windowsmedia | boolean | Whether the visitor’s browser uses windows media player to play media files. |
| visit_total_time | bigint | The total elapsed time of the visit. |
| location_city | varchar(255) | A string naming the city the visitor was in while visiting the site. Set by the UserCountry plugin. |
| location_country | char(3) | A two-character string describing the country the visitor was located in while visiting the site. Set by the UserCountry plugin. |
| location_latitude | decimal(9,6) | The latitude of the visitor while he/she visited the site. Set by the UserCountry plugin. |
| location_longitude | decimal(9,6) | The longitude of the visitor while he/she visited the site. Set by the UserCountry plugin. |
| location_region | char(3) | A two-character string describing the region of the country the visitor was in. Set by the UserCountry plugin. |
| is_added | int | Whether the user is added the campaign or not. |
| custom_var_k1 | varchar(200) | The custom variable name of the visit in the first slot for visit custom variables. |
| custom_var_v1 | varchar(200) | The custom variable value of the visit in the first slot for visit custom variables. |
| custom_var_k2 | varchar(200) | The custom variable name of the visit in the second slot for visit custom variables. |
| custom_var_v2 | varchar(200) | The custom variable value of the visit in the second slot for visit custom variables. |
| custom_var_k3 | varchar(200) | The custom variable name of the visit in the third slot for visit custom variables. |
| custom_var_v3 | varchar(200) | The custom variable value of the visit in the third slot for visit custom variables. |
| custom_var_k4 | varchar(200) | The custom variable name of the visit in the fourth slot for visit custom variables. |
| custom_var_v4 | varchar(200) | The custom variable value of the visit in the fourth slot for visit custom variables. |
| custom_var_k5 | varchar(200) | The custom variable name of the visit in the fifth slot for visit custom variables. |
| custom_var_v5 | varchar(200) | The custom variable value of the visit in the fifth slot for visit custom variables. |
| last_idlink_va | decimal(20,0) | The ID of the last visit action that resulted in this conversion. |
| custom_dimension_1 | varchar(255) | Dimension 1 for the custom visit. |
| custom_dimension_2 | varchar(255) | Dimension 2 for the custom visit. |
| custom_dimension_3 | varchar(255) | Dimension 3 for the custom visit. |
| custom_dimension_4 | varchar(255) | Dimension 4 for the custom visit. |
| custom_dimension_5 | varchar(255) | Dimension 5 for the custom visit. |
| custom_var_k6 | varchar(200) | The custom variable name of the visit in the sixth slot for visit custom variables. |
| custom_var_v6 | varchar(200) | The custom variable value of the visit in the sixth slot for visit custom variables. |
| custom_var_k7 | varchar(200) | The custom variable name of the visit in the seventh slot for visit custom variables. |
| custom_var_v7 | varchar(200) | The custom variable value of the visit in the seventh slot for visit custom variables. |
| custom_var_k8 | varchar(200) | The custom variable name of the visit in the eighth slot for visit custom variables. |
| custom_var_v8 | varchar(200) | The custom variable value of the visit in the eighth slot for visit custom variables. |
| custom_var_k9 | varchar(200) | The custom variable name of the visit in the ninth slot for visit custom variables. |
| custom_var_v9 | varchar(200) | The custom variable value of the visit in the ninth slot for visit custom variables |
| custom_var_k10 | varchar(200) | The custom variable name of the visit in the tenth slot for visit custom variables. |
| custom_var_v10 | varchar(200) | The custom variable value of the visit in the tenth slot for visit custom variables. |
| visit_total_interactions | int | The count of interactions performed during this visit. |
| usr_unique_id | varchar(50) | The unique ID of the user. |
| usr_interaction_id | varchar(100) | The interaction ID of the user recorded against each visit. |
| campaign_content | varchar(255) | The campaign content. |
| campaign_id | varchar(100) | The campaign ID. |
| campaign_keyword | varchar(255) | The keyword used for the campaign. |
| campaign_medium | varchar(255) | The medium of the campaign medium. |
| campaign_name | varchar(255) | The name of the campaign. |
| campaign_source | varchar(255) | The source of the campaign. |
| custom_var_k11 | varchar(200) | The custom variable name of the visit in the eleventh slot for visit custom variables. |
| custom_var_v11 | varchar(200) | The custom variable value of the visit in the eleventh slot for visit custom variables. |
| custom_var_k12 | varchar(200) | The custom variable name of the visit in the twelfth slot for visit custom variables. |
| custom_var_v12 | varchar(200) | The custom variable value of the visit in the twelfth slot for visit custom variables. |
| custom_var_k13 | varchar(200) | The custom variable name of the visit in the thirteenth slot for visit custom variables. |
| custom_var_v13 | varchar(200) | The custom variable value of the visit in the thirteenth slot for visit custom variables. |
| custom_var_k14 | varchar(200) | The custom variable name of the visit in the fourteenth slot for visit custom variables. |
| custom_var_v14 | varchar(200) | The custom variable value of the visit in the fourteenth slot for visit custom variables. |
| custom_var_k15 | varchar(200) | The custom variable name of the visit in the fifteenth slot for visit custom variables. |
| custom_var_v15 | varchar(200) | The custom variable value of the visit in the fifteenth slot for visit custom variables. |
| custom_dimension_6 | varchar(255) | Dimension 6 for the custom visit. |
| custom_dimension_7 | varchar(255) | Dimension 7 for the custom visit. |
| custom_dimension_8 | varchar(255) | Dimension 8 for the custom visit. |
| custom_dimension_9 | varchar(255) | Dimension 9 for the custom visit. |
| custom_dimension_10 | varchar(255) | Dimension 10 for the custom visit. |
| custom_dimension_11 | varchar(255) | Dimension 11 for the custom visit. |
| custom_dimension_12 | varchar(255) | Dimension 12 for the custom visit. |
| custom_dimension_13 | varchar(255) | Dimension 13 for the custom visit. |
| custom_dimension_14 | varchar(255) | Dimension 14 for the custom visit. |
| custom_dimension_15 | varchar(255) | Dimension 15 for the custom visit. |
| visitor_seconds_since_first | int | The number of seconds since this visitor’s first visit. |
| visitor_seconds_since_order | int | The number of seconds since this visitor’s last order (if any). |
| visitor_seconds_since_last | int | The number of seconds since this visitor’s last visit (if any). |
| profilable | int | This is either 0 or 1. If no visitorId or userId was used, then the value is 0. This means no profile can be created for this specific visitor meaning visitor profile would in most cases only show one visit, features like new/returning visit won’t work for this visitor etc. |
| config_client_type | int | This identifies the client type used to make this visit. |
| campaign_group | varchar(255) | The campaign group. |
| campaign_placement | varchar(100) | The campaign placement. |
| fh_updated_date | timestamp | The date time when the heatmap or session recording was last updated in FH. |