Advanced SQL ID Graph Computation

Step 1 – Initialize Environment

Step 1.1 – Initialize ID Graph Logging

1. initialize_id_graph_logging : Initializes a logging table that helps to document workflow start & end timestamps

Step 1.2 – Initialize Source Data Demo Tables

1. initialize_crm_data.sql : Includes an example CRM Dataset
2. initialize_event_stream.sql : Includes an example Event Stream (incl. Web Browsing Events, Purchase Events, etc.)
3. initialize_cookie_consent_table.sql : Includes an example Consent Event Log (incl. Cookie Consent Opt-Ins, etc.)

Step 1.3 – Initialize Edges Tables

1. initialize_list_of_crm_edges.sql : Includes all Edges with CRM Identifiers
2. initialize_list_of_cookie_edges.sql : Includes all Edges with Cookie or Fingerprinting Identifiers
3. initialize_list_of_auth_signals.sql : Includes all Edges created by Authentication Signals (e.g. Website Logins)
4. initialize_list_of_link_only_edges.sql : Includes all Edges with IDs that should not be used for merging but should be included in the Identity Graph after the main computation

Step 1.4 – Initialize the ID Dictionary

1. initialize_id_dictionary.sql : Includes the ID dictionary of all IDs mentioned in the above Edges Tables

Step 1.5 – Initialize the Integer Encoded Edges Tables

1. initialize_list_of_int_crm_edges.sql : Includes all Integer Encoded Edges with CRM Identifiers
2. initialize_list_of_int_cookie_edges.sql : Includes all Integer Encoded Edges with Cookie or Fingerprinting Identifiers
3. initialize_list_of_int_auth_signals.sql : Includes all Integer Encoded Edges created by Authentication Signals (e.g. Website Logins)
4. initialize_list_of_int_link_only_edges.sql : Includes all Integer Encoded Edges with IDs that should not be used for merging but should be included in the Identity Graph after the main computation

Step 1.6 – Initialize the Edges Main Tables

1. initialize_edges_main_table_crm.sql : Includes the CRM Edges for the Profile Graph Modelling
2. initialize_edges_main_table_cookies.sql : Includes the Cookie Edges for the Cookie Pool / Device Graph Modelling
3. initialize_edges_link_only_table.sql : Includes the Edges that should only be linked to Profile or Device Graph

Step 1.7 – Initialize the Iteration Log Tables

1. initialize_iteration_log_crm.sql : Logs the number of iterations and number of updates for the Complex Deduplication started for the CRM Edges Main Table
2. initialize_iteration_log_cookie.sql : Logs the number of iterations and number of updates for the Complex Deduplication started for the Cookie Edges Main Table

Step 1.8 – Initialize the Profile to Cookie Pool Connections Table

1. initialize_crm_cookie_pool_connections.sql : Logs the connections between Profile and Device including the device strength and the maximum strength of any profile to a specific device

Step 2 – Load Demo Data

Step 2.1 – Create the initial data loads

1. cookie_consent_demo.sql : Loads Demo Data into the Cookie Consent Table cookie_consent
2. crm_data_demo.sql : Loads Demo Data into the CRM Table crm_data
3. event_stream.sql : Loads Demo Data into the Event Table event_stream
4. list_of_auth_signals.sql : Loads Demo Data into the Edges Table list_of_auth_signals
5. list_of_crm_edges_demo.sql : Loads Demo Data into the Edges Table list_of_crm_edges
6. list_of_edges_demo.sql : Loads Demo Data into the Edges Table list_of_cookie_edges
7. list_of_link_only_edges.sql : Loads Demo Data into the Edges Table list_of_link_only_edges

Step 2.2 – Clean the input data

1. Update ID type descriptions in list_of_crm_edges
2. Update ID type descriptions in list_of_cookie_edges
3. Update ID type descriptions in list_of_link_only_edges
4. Update ID type descriptions in list_of_auth_signals
5. Replace whitespace with _ and convert uppercase letters to lowercase in type_1 and type_2

Step 3 – Graph Modelling

Step 3.1 – Update ID Dictionary

1. Merge new IDs in the list_of_crm_edges into the id_dictionary
2. Merge new IDs in the list_of_cookie_edges into the id_dictionary
3. Merge new IDs in the list_of_link_only_edges into the id_dictionary
4. Merge new IDs in the list_of_auth_signals into the id_dictionary

Step 3.2 – Integer Encoding of IDs

1. Use the id_dictionary to integer encode the list_of_crm_edges
2. Use the id_dictionary to integer encode the list_of_cookie_edges
3. Use the id_dictionary to integer encode the list_of_link_only_edges
4. Use the id_dictionary to integer encode the list_of_auth_signals

Step 3.3 – Set Edges Table

1. Merge new edges from the list_of_int_crm_edges into the edges_main_table_crm
2. Merge new edges from the list_of_int_cookie_edges into the edges_main_table_cookies
3. Merge new edges from the list_of_link_only_edges into the edges_link_only_table

Step 3.4 – Detect Anomalies

For all Edges Table mentioned above:

1. Update the count of edges that include the more frequently observed ID of the edge.
2. Update the z_score values for each edge and mark edges as “Normal” or “Outlier”
3. Reset the group_id to remove the edge identified as “Outlier” from modelling

Step 3.5 – Update Edges Table

For all Edges Table mentioned above:

1. Initialize the group ID for edges without a group_id that are marked as “Normal”
2. Duplicate edges and switch int_1 and int_2 as final data preparation before the Complex Deduplication

Step 3.6 – Complex Deduplication

For the CRM & Cookie Edges Table mentioned above:

1. Run the Complex Deduplication in iterations until no more group_id updates can be found

For the Link Only Edges Main Table:

1. Run a simpler join to connect the Edges to the Group ID mentioned in the CRM or Cookie Edges Tables

Step 4 – Establish Profile & Device Connections

Step 4.1 – Create the Profile & Device Connections Table

1. Leverage the list_of_auth_signals to merge new data into the crm_cookie_pool_connections table

Step 4.2 – Update the Connection Strength Values

1. Leverage the list_of_auth_signals to update the connection strength values for each Profile to Device connection
2. Leverage the list_of_auth_signals to update the maximum connection strength value any Profile has to a Device

Step 5 – Save Results

Step 5.1 – Save CRM Profile Nested Results Table

1. Define a list of CRM ID types logged in the id_dictionary
2. Define the dynamic SQL to iterate each CRM ID type logged in the id_dictionary .
3. Execute the dynamic SQL to build the CRM nested results table that includes all relevant ID types

Step 5.2 – Save Cookie Profile Nested Results Table

1. Define a list of Cookie ID types logged in the id_dictionary
2. Define the dynamic SQL to iterate each Cookie ID type logged in the id_dictionary
3. Execute the dynamic SQL to build the Cookie nested results table that includes all relevant ID types

Step 5.3 – Save Combined Nested Results Table

1. Define a list of available ID types logged in the id_dictionary
2. Define the dynamic SQL to iterate each ID type logged in the id_dictionary
3. Execute the dynamic SQL to build the final nested results table that includes all relevant ID types

Step 5.3 – Save Combined Flat Results Table

1. Create a flat results table of CRM Group ID, Unique ID, ID Type & ID Values
2. The flat results table can be used in some scenarios as it is easier to query (no unnesting is necessary)

Step 6 – Post Processing

1. Add the CRM Group ID to the cookie_consent table
2. Add the CRM Group ID to the event_stream table