​

Understanding Hardal Data Structure

Your Hardal data is stored in a MySQL database with a JSON properties column containing detailed event information. Let’s learn how to access this data effectively.

​

Creating Calculated Fields

Here’s how to create essential calculated fields to extract data from the JSON properties.

CASE 
  WHEN JSON_EXTRACT(properties, '$.device_type') IS NOT NULL 
  THEN REPLACE(JSON_EXTRACT(properties, '$.device_type'), '"', '')
  ELSE 'unknown'
END

CASE 
  WHEN JSON_EXTRACT(properties, '$.device_type') IS NOT NULL 
  THEN REPLACE(JSON_EXTRACT(properties, '$.device_type'), '"', '')
  ELSE 'unknown'
END

CASE 
  WHEN JSON_EXTRACT(properties, '$.distinct.server_distinct_id') IS NOT NULL 
  THEN REPLACE(JSON_EXTRACT(properties, '$.distinct.server_distinct_id'), '"', '')
  ELSE REPLACE(JSON_EXTRACT(properties, '$.distinct.client_distinct_id'), '"', '')
END

CASE 
  WHEN JSON_EXTRACT(properties, '$.page.url') IS NOT NULL 
  THEN REPLACE(JSON_EXTRACT(properties, '$.page.url'), '"', '')
  ELSE '(not set)'
END

​

Common JSON Paths

Here are the essential JSON paths you’ll need for various analytics purposes:

​

User Information

$.distinct.server_distinct_id
$.distinct.client_distinct_id
$.device_type

​

Page Information

$.page.url
$.page.path
$.page.title
$.page.referrer

​

Campaign Tracking

$.query_params.utm_source
$.query_params.utm_medium
$.query_params.utm_campaign

​

Creating Essential Reports

Let’s create some fundamental reports for your analytics.

​

User Flow Analysis

To track user navigation patterns:

-- Session ID Calculation
CONCAT(
  REPLACE(JSON_EXTRACT(properties, '$.distinct.server_distinct_id'), '"', ''),
  '_',
  DATE_FORMAT(created_at, '%Y%m%d%H%i')
)

​

Campaign Performance

For tracking marketing effectiveness:

-- Campaign Source
CASE 
  WHEN JSON_EXTRACT(properties, '$.query_params.utm_source') IS NOT NULL 
  THEN REPLACE(JSON_EXTRACT(properties, '$.query_params.utm_source'), '"', '')
  ELSE 'direct'
END

​

Best Practices

When working with Hardal data in Looker Studio:

1. JSON Extraction

   • Always use REPLACE to remove quotes

   • Handle NULL values appropriately

   • Use consistent naming conventions

2. Performance

   • Keep calculations simple

   • Create composite fields when needed

   • Use appropriate data types

3. Report Organization

   • Group related metrics

   • Use clear naming conventions

   • Include field descriptions

​

Troubleshooting

​

Solutions

​

Advanced Techniques

​

Session Analysis

Create sophisticated session tracking:

CASE 
  WHEN TIME_TO_SEC(TIMEDIFF(created_at, 
    LAG(created_at) OVER (
      PARTITION BY JSON_EXTRACT(properties, '$.distinct.server_distinct_id') 
      ORDER BY created_at
    ))
  ) > 1800 
  OR LAG(created_at) OVER (
    PARTITION BY JSON_EXTRACT(properties, '$.distinct.server_distinct_id') 
    ORDER BY created_at
  ) IS NULL 
  THEN 1 
  ELSE 0 
END

​

User Engagement Scoring

Calculate engagement levels:

CASE
  WHEN JSON_EXTRACT(properties, '$.events[0].event_name') IN ('purchase', 'signup')
  THEN 10
  WHEN JSON_EXTRACT(properties, '$.events[0].event_name') = 'page_view'
  THEN 1
  ELSE 0
END

​

Next Steps

After setting up your basic reports:

1. Create custom dashboards for different stakeholders

2. Set up automated reporting

3. Create calculated metrics for business KPIs