ID Unification Tips

Tips: Visualizing a Graph Table Using Graphviz

Step 1: Query and Export Graph Table Data as CSV

Run the following SQL query on the graph_unify_loop_${N} (or graph) table for each iteration, and download the resulting data as a CSV file.

SELECT
  CONCAT('"',leader_id,'"') AS leader_id,
  CONCAT('"',follower_id,'"') AS follower_id,
  '' AS node_label,
  '' AS node_shape,
  '' AS edge_label
FROM ${canonical_id_name}_graph_unify_loop_${N};

Example output:

Step 2: Edit CSV File

Open the CSV file in a text editor and replace all instances of '""' with ''. This will clean up unnecessary quotes around the values.

Edited example:

Step 3: Convert CSV to DOT Language Graph

Ex. Use the 3rd party service - https://docpose.com/csv-to-dot

Tips: Expanding and Visualizing Histogram Columns from result_key_stats (Example: 100 Rows)

Overview

In this guide, we process and visualize histogram columns such as histogram_${key_name} from the result_key_stats table. Using SQL, we transform these columns into a structured format and plot histograms and pie charts for insights.

Sample result_key_stats Table

SQL Query to Transform and Normalize Histogram Data

The following SQL extracts, normalizes, and formats histogram data for plotting. It includes logic to handle missing x-values and calculate percentages for each bin.

WITH tbl_histogram AS
(
  SELECT
     CAST(SPLIT_PART(elm, ':', 1) AS BIGINT) AS x,
     CAST(SPLIT_PART(elm, ':', 2) AS BIGINT) AS num
  FROM (
    SELECT
      SPLIT(histogram_${key_name}, ',') AS hist_ary,
      ROW_NUMBER() OVER (ORDER BY time DESC) AS id
    FROM ${canonical_id_name}_result_key_stats
    WHERE from_table = '*'
  )
  CROSS JOIN UNNEST(hist_ary) AS t(elm)
  WHERE id = 1
),
tbl_serial_numbers AS
(
  SELECT val
  FROM (VALUES (SEQUENCE(1, 100, 1))) AS t(seq_ary)
  CROSS JOIN UNNEST(seq_ary) AS t(val)
)
SELECT x, num, num * 1.0 / SUM(num) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS percentage_of_total
FROM
(
  SELECT x, num
  FROM tbl_histogram
  UNION ALL
  SELECT x, 0 AS num
  FROM (SELECT val AS x FROM tbl_serial_numbers)
  WHERE x NOT IN (SELECT x FROM tbl_histogram)
)
ORDER BY x
LIMIT 100;

Key Components of the Query

1. Percentage Calculation
   percentage_of_total computes the proportion of each x relative to the total.

2. Histogram Bin Range
   SEQUENCE(1, 100, 1) ensures the x-axis ranges from 1 to 100, even if certain bins are missing.

3. Row Limit
   LIMIT 100 restricts the output to the first 100 bins.

Example Visualizations

1. distinct_with_email Histogram

Output Table

Observations

• Most canonical_ids are mapped to only one email.
• Small counts exist for multiple emails per canonical_id.

Bar Graph

Pie Chart

2. distinct_with_td_client_id Histogram

Output Table

Observations

• Majority of canonical_ids correspond to a single td_client_id.
• Potential reasons:
  • Many users interact briefly, resulting in only one td_client_id.
  • Incomplete stitching of td_client_ids across sessions.

Bar Graph

Pie Chart

Insights and Considerations

1. Stitching Accuracy
   Results reflect the accuracy of canonical_id stitching. High counts for x=1 may indicate insufficient linking across identifiers.

2. User Behavior Patterns
   Distributions help identify how users interact with your platform (e.g., single or multiple identifiers).

3. Data Quality
   Missing or misaligned data may result in skewed histograms.

By visualizing these patterns, we gain deeper insights into user identity stitching and potential areas for data enhancement.