{"id":1815,"date":"2026-07-22T09:00:00","date_gmt":"2026-07-22T14:00:00","guid":{"rendered":"https:\/\/tolinku.com\/blog\/?p=1815"},"modified":"2026-03-07T03:37:25","modified_gmt":"2026-03-07T08:37:25","slug":"analytics-data-warehousing","status":"publish","type":"post","link":"https:\/\/tolinku.com\/blog\/analytics-data-warehousing\/","title":{"rendered":"Deep Link Analytics Data Warehousing"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Your deep link platform retains 30 or 90 days of analytics data. Your data warehouse retains everything. When a stakeholder asks &quot;how did our deep link performance change over the past year?&quot; or &quot;what is the lifetime value of users acquired through referral deep links in Q1?&quot;, you need historical data in a queryable format.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This guide covers data warehousing for deep link analytics. For data export, see <a href=\"https:\/\/tolinku.com\/blog\/analytics-data-export\/\">exporting deep link analytics data<\/a>. For API integration, see <a href=\"https:\/\/tolinku.com\/blog\/analytics-api-integration\/\">analytics API integration for deep links<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><img decoding=\"async\" src=\"https:\/\/tolinku.com\/blog\/wp-content\/uploads\/2026\/03\/screenshot-analytics-1772819420927.png\" alt=\"Tolinku analytics dashboard showing click metrics and conversion funnel\">\n<em>The analytics dashboard with date range selector, filters, charts, and breakdowns.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why a Data Warehouse?<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Limitations of Platform Analytics<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table>\n<thead>\n<tr>\n<th>Limitation<\/th>\n<th>Impact<\/th>\n<th>Data Warehouse Solution<\/th>\n<\/tr>\n<\/thead>\n<tbody><tr>\n<td>30-90 day retention<\/td>\n<td>Cannot analyze year-over-year trends<\/td>\n<td>Unlimited retention<\/td>\n<\/tr>\n<tr>\n<td>No cross-system joins<\/td>\n<td>Cannot join clicks with revenue, CRM, or product data<\/td>\n<td>Full SQL JOIN support<\/td>\n<\/tr>\n<tr>\n<td>Limited query flexibility<\/td>\n<td>Predefined dashboards only<\/td>\n<td>Arbitrary SQL queries<\/td>\n<\/tr>\n<tr>\n<td>No custom aggregations<\/td>\n<td>Cannot build custom metrics<\/td>\n<td>Define any metric via SQL<\/td>\n<\/tr>\n<tr>\n<td>Rate-limited API<\/td>\n<td>Cannot run intensive analysis<\/td>\n<td>No rate limits on queries<\/td>\n<\/tr>\n<\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">When You Need a Data Warehouse<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You want to analyze deep link data alongside revenue, CRM, or product data.<\/li>\n<li>You need more than 90 days of historical data.<\/li>\n<li>You want to build custom metrics and dimensions not available in the platform UI.<\/li>\n<li>You have data analysts or scientists who need raw data access.<\/li>\n<li>You want to use BI tools (Looker, Tableau, Metabase) for custom reporting.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Schema Design<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Fact Table: Clicks<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The primary fact table stores one row per deep link click:<\/p>\n\n\n\n<pre><code class=\"language-sql\">CREATE TABLE fact_deep_link_clicks (\n  click_id        VARCHAR(32) NOT NULL,\n  timestamp       TIMESTAMP NOT NULL,\n  date            DATE NOT NULL,              -- Partition key\n  hour            SMALLINT NOT NULL,\n\n  -- Deep link dimensions\n  route           VARCHAR(256),\n  route_pattern   VARCHAR(128),               -- e.g., \/products\/:id\n  appspace_id     VARCHAR(32),\n\n  -- Campaign dimensions\n  source          VARCHAR(64),\n  medium          VARCHAR(64),\n  campaign        VARCHAR(128),\n  content         VARCHAR(128),\n\n  -- Device dimensions\n  platform        VARCHAR(16),                -- ios, android, web\n  os_version      VARCHAR(16),\n  browser         VARCHAR(64),\n  device_type     VARCHAR(16),                -- phone, tablet, desktop\n  is_in_app       BOOLEAN,\n\n  -- Geographic dimensions\n  country         CHAR(2),\n  region          VARCHAR(64),\n  city            VARCHAR(128),\n\n  -- Outcome\n  outcome         VARCHAR(32),                -- app_opened, fallback, store_redirect, error\n  latency_ms      INTEGER,\n  error_code      VARCHAR(32),\n\n  -- User (if available)\n  anonymous_id    VARCHAR(64),\n  user_id         VARCHAR(64),\n\n  -- Deferred deep link\n  is_deferred     BOOLEAN DEFAULT FALSE,\n  deferred_matched BOOLEAN,\n\n  PRIMARY KEY (click_id, date)\n)\nPARTITION BY RANGE (date);\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Fact Table: Conversions<\/h3>\n\n\n\n<pre><code class=\"language-sql\">CREATE TABLE fact_deep_link_conversions (\n  conversion_id   VARCHAR(32) NOT NULL,\n  click_id        VARCHAR(32) NOT NULL,       -- FK to clicks\n  timestamp       TIMESTAMP NOT NULL,\n  date            DATE NOT NULL,\n\n  conversion_type VARCHAR(64),                -- purchase, signup, feature_use\n  revenue         DECIMAL(10, 2),\n  currency        CHAR(3),\n\n  -- Attribution\n  attribution_window VARCHAR(16),             -- 1h, 24h, 7d, 30d\n  is_first_touch  BOOLEAN,\n  is_last_touch   BOOLEAN,\n\n  PRIMARY KEY (conversion_id, date)\n)\nPARTITION BY RANGE (date);\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Dimension Tables<\/h3>\n\n\n\n<pre><code class=\"language-sql\">CREATE TABLE dim_campaigns (\n  campaign_id     VARCHAR(128) PRIMARY KEY,\n  campaign_name   VARCHAR(256),\n  start_date      DATE,\n  end_date        DATE,\n  budget          DECIMAL(10, 2),\n  channel         VARCHAR(64),\n  target_audience VARCHAR(128),\n  status          VARCHAR(16)\n);\n\nCREATE TABLE dim_routes (\n  route_pattern   VARCHAR(128) PRIMARY KEY,\n  route_name      VARCHAR(256),\n  category        VARCHAR(64),                -- product, offer, content, settings\n  target_screen   VARCHAR(128),\n  created_date    DATE\n);\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Materialized Views<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Pre-aggregate common queries for fast dashboard loading:<\/p>\n\n\n\n<pre><code class=\"language-sql\">-- Daily summary by campaign\nCREATE MATERIALIZED VIEW mv_daily_campaign_summary AS\nSELECT\n  date,\n  campaign,\n  source,\n  medium,\n  platform,\n  COUNT(*) AS clicks,\n  SUM(CASE WHEN outcome = &#39;app_opened&#39; THEN 1 ELSE 0 END) AS app_opens,\n  SUM(CASE WHEN outcome = &#39;fallback&#39; THEN 1 ELSE 0 END) AS fallbacks,\n  SUM(CASE WHEN outcome = &#39;error&#39; THEN 1 ELSE 0 END) AS errors,\n  ROUND(AVG(latency_ms), 0) AS avg_latency_ms,\n  COUNT(DISTINCT anonymous_id) AS unique_users\nFROM fact_deep_link_clicks\nGROUP BY date, campaign, source, medium, platform;\n\n-- Daily summary by geography\nCREATE MATERIALIZED VIEW mv_daily_geo_summary AS\nSELECT\n  date,\n  country,\n  platform,\n  COUNT(*) AS clicks,\n  SUM(CASE WHEN outcome = &#39;app_opened&#39; THEN 1 ELSE 0 END) AS app_opens,\n  ROUND(\n    SUM(CASE WHEN outcome = &#39;app_opened&#39; THEN 1 ELSE 0 END)::DECIMAL \/ COUNT(*) * 100, 1\n  ) AS open_rate\nFROM fact_deep_link_clicks\nGROUP BY date, country, platform;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Warehouse Platforms<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">BigQuery (Google Cloud)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Good for: teams already on GCP, serverless (no infrastructure), pay-per-query pricing.<\/p>\n\n\n\n<pre><code class=\"language-sql\">-- BigQuery-specific: Partitioned and clustered table\nCREATE TABLE `project.analytics.deep_link_clicks`\n(\n  click_id STRING NOT NULL,\n  timestamp TIMESTAMP NOT NULL,\n  route STRING,\n  source STRING,\n  campaign STRING,\n  platform STRING,\n  country STRING,\n  outcome STRING,\n  latency_ms INT64\n)\nPARTITION BY DATE(timestamp)\nCLUSTER BY campaign, platform, country;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Loading data:<\/p>\n\n\n\n<pre><code class=\"language-bash\"># Load from Cloud Storage\nbq load --source_format=NEWLINE_DELIMITED_JSON \\\n  analytics.deep_link_clicks \\\n  gs:\/\/analytics-bucket\/clicks\/2026-07-21\/*.json\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Snowflake<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Good for: teams needing strong SQL compatibility, separate compute and storage scaling, data sharing.<\/p>\n\n\n\n<pre><code class=\"language-sql\">-- Snowflake-specific: Micro-partitioned by default\nCREATE TABLE analytics.deep_link_clicks (\n  click_id VARCHAR(32) NOT NULL,\n  timestamp TIMESTAMP_NTZ NOT NULL,\n  route VARCHAR(256),\n  source VARCHAR(64),\n  campaign VARCHAR(128),\n  platform VARCHAR(16),\n  country CHAR(2),\n  outcome VARCHAR(32),\n  latency_ms INTEGER\n)\nCLUSTER BY (TO_DATE(timestamp), campaign);\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Loading data:<\/p>\n\n\n\n<pre><code class=\"language-sql\">-- Load from S3 stage\nCOPY INTO analytics.deep_link_clicks\nFROM @s3_analytics_stage\/clicks\/\nFILE_FORMAT = (TYPE = &#39;JSON&#39;)\nMATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Amazon Redshift<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Good for: teams already on AWS, familiar with PostgreSQL, need tight integration with AWS services.<\/p>\n\n\n\n<pre><code class=\"language-sql\">-- Redshift-specific: Distribution and sort keys\nCREATE TABLE analytics.deep_link_clicks (\n  click_id VARCHAR(32) NOT NULL,\n  timestamp TIMESTAMP NOT NULL,\n  route VARCHAR(256),\n  source VARCHAR(64),\n  campaign VARCHAR(128),\n  platform VARCHAR(16),\n  country CHAR(2),\n  outcome VARCHAR(32),\n  latency_ms INTEGER\n)\nDISTKEY(campaign)\nSORTKEY(timestamp);\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">ETL Pipeline<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Daily Load Pipeline<\/h3>\n\n\n\n<pre><code class=\"language-typescript\">interface ETLConfig {\n  source: {\n    type: &#39;api&#39; | &#39;s3&#39; | &#39;webhook&#39;;\n    endpoint: string;\n  };\n  transform: {\n    dedup: boolean;\n    enrich: boolean;   \/\/ Add derived fields\n    validate: boolean;\n  };\n  destination: {\n    warehouse: &#39;bigquery&#39; | &#39;snowflake&#39; | &#39;redshift&#39;;\n    table: string;\n    writeMode: &#39;append&#39; | &#39;merge&#39;;\n  };\n}\n\nasync function runDailyETL(config: ETLConfig, date: string) {\n  \/\/ 1. Extract\n  const rawData = await extractFromSource(config.source, date);\n  console.log(`Extracted ${rawData.length} rows for ${date}`);\n\n  \/\/ 2. Transform\n  let transformed = rawData;\n\n  if (config.transform.validate) {\n    const { valid, invalid } = validateRows(transformed);\n    transformed = valid;\n    if (invalid.length &gt; 0) {\n      console.warn(`${invalid.length} invalid rows discarded`);\n      await logInvalidRows(invalid, date);\n    }\n  }\n\n  if (config.transform.dedup) {\n    const before = transformed.length;\n    transformed = deduplicateByClickId(transformed);\n    console.log(`Deduped: ${before} \u2192 ${transformed.length}`);\n  }\n\n  if (config.transform.enrich) {\n    transformed = transformed.map(row =&gt; ({\n      ...row,\n      date: row.timestamp.split(&#39;T&#39;)[0],\n      hour: new Date(row.timestamp).getUTCHours(),\n      route_pattern: extractRoutePattern(row.route),\n      is_in_app: detectInAppBrowser(row.browser)\n    }));\n  }\n\n  \/\/ 3. Load\n  await loadToWarehouse(config.destination, transformed);\n  console.log(`Loaded ${transformed.length} rows to ${config.destination.table}`);\n}\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Incremental vs Full Load<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table>\n<thead>\n<tr>\n<th>Approach<\/th>\n<th>Pros<\/th>\n<th>Cons<\/th>\n<\/tr>\n<\/thead>\n<tbody><tr>\n<td>Incremental (append new data daily)<\/td>\n<td>Fast, low cost<\/td>\n<td>May miss late-arriving data<\/td>\n<\/tr>\n<tr>\n<td>Full refresh (reload all data)<\/td>\n<td>Always complete<\/td>\n<td>Slow, expensive for large tables<\/td>\n<\/tr>\n<tr>\n<td>Merge (upsert)<\/td>\n<td>Handles late data and updates<\/td>\n<td>More complex queries<\/td>\n<\/tr>\n<\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">For deep link analytics, incremental loading with a 3-day lookback handles most late-arriving data:<\/p>\n\n\n\n<pre><code class=\"language-sql\">-- Merge pattern: load last 3 days, replace existing rows\nMERGE INTO fact_deep_link_clicks target\nUSING staging_clicks source\nON target.click_id = source.click_id\nWHEN MATCHED THEN UPDATE SET\n  outcome = source.outcome,\n  latency_ms = source.latency_ms\nWHEN NOT MATCHED THEN INSERT (\n  click_id, timestamp, date, route, source, campaign,\n  platform, country, outcome, latency_ms\n) VALUES (\n  source.click_id, source.timestamp, source.date, source.route,\n  source.source, source.campaign, source.platform, source.country,\n  source.outcome, source.latency_ms\n);\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Query Patterns<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Year-over-Year Comparison<\/h3>\n\n\n\n<pre><code class=\"language-sql\">SELECT\n  DATE_TRUNC(&#39;month&#39;, date) AS month,\n  COUNT(*) AS clicks,\n  SUM(CASE WHEN outcome = &#39;app_opened&#39; THEN 1 ELSE 0 END) AS app_opens,\n  ROUND(\n    SUM(CASE WHEN outcome = &#39;app_opened&#39; THEN 1 ELSE 0 END)::DECIMAL \/ COUNT(*) * 100, 1\n  ) AS open_rate\nFROM fact_deep_link_clicks\nWHERE date &gt;= DATE_TRUNC(&#39;year&#39;, CURRENT_DATE) - INTERVAL &#39;1 year&#39;\nGROUP BY month\nORDER BY month;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">LTV by Acquisition Source<\/h3>\n\n\n\n<pre><code class=\"language-sql\">SELECT\n  c.source,\n  c.campaign,\n  COUNT(DISTINCT c.user_id) AS users,\n  ROUND(SUM(conv.revenue) \/ COUNT(DISTINCT c.user_id), 2) AS ltv,\n  ROUND(AVG(conv.revenue), 2) AS avg_order_value,\n  COUNT(conv.conversion_id) AS total_conversions\nFROM fact_deep_link_clicks c\nJOIN fact_deep_link_conversions conv ON c.click_id = conv.click_id\nWHERE c.date &gt;= &#39;2026-01-01&#39;\n  AND c.is_deferred = FALSE\nGROUP BY c.source, c.campaign\nHAVING COUNT(DISTINCT c.user_id) &gt;= 50\nORDER BY ltv DESC;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Cost and Storage<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table>\n<thead>\n<tr>\n<th>Warehouse<\/th>\n<th>Storage Cost<\/th>\n<th>Query Cost<\/th>\n<th>Notes<\/th>\n<\/tr>\n<\/thead>\n<tbody><tr>\n<td>BigQuery<\/td>\n<td>$0.02\/GB\/mo<\/td>\n<td>$5\/TB scanned<\/td>\n<td>Partition pruning reduces cost<\/td>\n<\/tr>\n<tr>\n<td>Snowflake<\/td>\n<td>$23\/TB\/mo (compressed)<\/td>\n<td>Credit-based<\/td>\n<td>Separate compute scaling<\/td>\n<\/tr>\n<tr>\n<td>Redshift<\/td>\n<td>$0.25\/GB\/mo (RA3)<\/td>\n<td>Included in instance cost<\/td>\n<td>Reserved instances cheaper<\/td>\n<\/tr>\n<\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">For a mid-size app generating 100K clicks\/day, expect roughly 3GB\/month of raw data, or 36GB\/year. Storage costs are negligible; query costs depend on query frequency and complexity.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Tolinku for Data Warehousing<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/tolinku.com\/features\/analytics\">Tolinku&#39;s analytics<\/a> support data export for warehouse loading. Export click data via the <a href=\"https:\/\/tolinku.com\/docs\/developer\/api-reference\/analytics\/\">analytics API<\/a> or the <a href=\"https:\/\/tolinku.com\/docs\/user-guide\/analytics\/exporting\/\">dashboard export<\/a>. Load exported data into your warehouse on a daily schedule for long-term analysis and cross-system joins.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For data export, see <a href=\"https:\/\/tolinku.com\/blog\/analytics-data-export\/\">exporting deep link analytics data<\/a>. For analytics fundamentals, see <a href=\"https:\/\/tolinku.com\/blog\/deep-link-analytics-measuring-what-matters\/\">deep link analytics: measuring what matters<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Store and query deep link analytics at scale. Set up data warehousing with BigQuery, Snowflake, or Redshift for long-term analytics storage.<\/p>\n","protected":false},"author":2,"featured_media":1814,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"rank_math_title":"Deep Link Analytics Data Warehousing","rank_math_description":"Store and query deep link analytics at scale. Set up data warehousing with BigQuery, Snowflake, or Redshift for long-term analytics.","rank_math_focus_keyword":"analytics data warehousing","rank_math_canonical_url":"","rank_math_facebook_title":"","rank_math_facebook_description":"","rank_math_facebook_image":"https:\/\/tolinku.com\/blog\/wp-content\/uploads\/2026\/03\/og-analytics-data-warehousing.png","rank_math_facebook_image_id":"","rank_math_twitter_title":"","rank_math_twitter_description":"","rank_math_twitter_image":"https:\/\/tolinku.com\/blog\/wp-content\/uploads\/2026\/03\/og-analytics-data-warehousing.png","footnotes":""},"categories":[14],"tags":[37,277,553,544,20,297,278,554],"class_list":["post-1815","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","tag-analytics","tag-bigquery","tag-data-engineering","tag-data-warehouse","tag-deep-linking","tag-scalability","tag-snowflake","tag-sql"],"_links":{"self":[{"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/posts\/1815","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/comments?post=1815"}],"version-history":[{"count":2,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/posts\/1815\/revisions"}],"predecessor-version":[{"id":2424,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/posts\/1815\/revisions\/2424"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/media\/1814"}],"wp:attachment":[{"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/media?parent=1815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/categories?post=1815"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tolinku.com\/blog\/wp-json\/wp\/v2\/tags?post=1815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}