Identifying custom SQL usage in RPI

Overview

Redpoint Interaction (RPI) provides robust support for both simple and advanced audience selection logic, catering to a wide range of marketing needs. In particular, custom SQL is often utilized in more complex scenarios, especially within Standard Selection Rules (SSRs). This capability allows marketers to implement intricate joins, dynamic expressions, and tailored logic specific to individual campaigns.

Understanding where and how custom SQL is employed is crucial for several reasons:

• It plays a vital role in governance, ensuring that the processes and data handling comply with organizational standards and regulations.

• It aids in troubleshooting, allowing teams to identify and resolve issues that may arise during campaign execution.

• Optimization is another key benefit; by analyzing custom SQL usage, marketers can refine their strategies for better performance and efficiency.

• Compliance is essential in maintaining the integrity of data usage and ensuring that all practices align with legal and ethical standards.

In summary, the strategic use of custom SQL within RPI not only enhances the audience selection process, but also supports critical aspects of governance, troubleshooting, optimization, and compliance, making it an indispensable tool for effective marketing campaigns.

Where custom SQL appears in RPI

Standard Selection Rules (SSR)

• SSRs allow direct embedding of custom SQL blocks for advanced logic.

• SSRs are used in campaign modules and can reference multiple data sources, perform complex joins, and include user-defined SQL expressions.

• SSRs support traceability features, such as copying the trace log for auditing.

Custom Attributes and Pipelines

• Custom SQL may be used in attribute definitions or data preparation pipelines, especially for parsing, normalization, or advanced data transformations.

Process for identifying custom SQL

Step 1: Review Selection Rules in the Audience Designer

1. Navigate to the Audience Designer in RPI.

2. Open each audience and inspect the selection rules:

   • Basic Selection Rules (BSR): Custom SQL not supported.

   • Standard Selection Rules (SSR): Look for rules with embedded SQL.

3. Look for “Custom SQL” blocks or advanced expressions in SSRs.

Step 2: Use the Trace Log feature

• For SSRs, use the “Copy Trace Log to Clipboard” feature.

• The trace log will show the actual SQL generated and executed, including any custom SQL blocks.

• This is useful for both auditing and debugging.

Step 3: Export or Audit Selection Rule Definitions

• If you have Interaction database access (Ops Database), you can query the RPI metadata tables to extract selection rule definitions.

• Example (for SQL Server or Postgres):

  SQL

   --Custom SQL attribute
   --Cust*Lfrotu(s
  leET  amert(seeI ,m.entObjj.value(('rs)@rs)','nvarchar(maxsql from op_files f1
  	CROS')APPLY f1.Desqi s.nodfs(r/obj/p')oa(intObj)
  wherepf1.islatest=1_andff1.type='Attribute'iandla.intObj1
  	CROSS( rA)PPLY f1.Details.nodis not(null
  UNION
  --Rule/
  seleot namj)tyje,ID,a.intObj)
  where (fqy)islatest=1 and f1.ts=lifrom and a.inOf1
  	bj.value('(@'1aDchar(max)') is not nulNION/p
  --a(intORj)
  where f1.islatelt=1 and f1.type='Selection Rule' and a.intObj.value('(@qy)','nvarceas(maxs') is not nulleUNION
  --CustomlAgg
  select name,type,ID,a.intObj.value('(@cFnc)','nvarchar(max)') sql from op_files f1
  	ect name,typD1nDObj.value('(@qy)','nvarahintObjr(where f1.sql from opandlf1.es f1
  ACROSS APPLand1a.intObjDetails.(ncFnc)es('/obj/shr/obj/p'isanot null
  ) as temp
  ordertby name desc
  where f1.islatest=1 and f1.type='Selection Rule' and a.intObj.value('(@qy)','nvarchar(max)') is not null
  UNION
  --Custom Agg
  select name,type,ID,a.intObj.value('(@cFnc)','nvarchar(max)') sql from op_files f1
  	CROSS APPLY f1.Details.nodes('/obj/p') a(intObj)
  where f1.islatest=1 and f1.type='Attribute' and a.intObj.value('(@cFnc)','nvarchar(max)') is not null
  ) as temp
  order by name desc

• This query extracts custom attribute definitions and their associated SQL.

Use caution when executing queries within the Ops DB. If help is needed, reach out to the Redpoint Client Support team.

Step 4: Documentation and Best Practices

• Ensure all custom SQL logic is documented within the campaign or audience documentation.

• Maintain transparency by keeping an audit trail of changes to SSRs and custom attributes.

Best Practices for Auditing Custom SQL

• Document all custom SQL logic in campaign and audience documentation to ensure clarity and consistency. This practice not only aids in understanding the underlying data processes, but also serves as a valuable reference for team members who may need to review or modify the SQL in the future. By providing detailed explanations of the logic used, we can facilitate better collaboration and knowledge sharing among team members.

• Use metadata and audit trails to track changes and rationale for custom SQL usage. Implementing a robust system for documenting changes allows you to maintain a clear history of modifications, which is essential for accountability and compliance. This approach enables you to understand the context behind each change, making it easier to troubleshoot issues or assess the impact of specific SQL queries on overall data integrity.

• Regularly review SSRs and custom attribute definitions for compliance and optimization. Conducting periodic reviews helps identify any discrepancies or outdated practices that may hinder data processes. By ensuring that SSRs and custom attributes align with current standards and best practices, you can enhance the efficiency and effectiveness of data operations.

• Leverage dashboards and automated monitoring to track performance and data quality impacts of custom SQL. Utilizing advanced analytics tools allows you to visualize key performance indicators and monitor data quality in real time. This proactive approach enables you to quickly identify and address any issues that may arise from custom SQL usage, ensuring that data remains reliable and actionable and fostering a culture of continuous improvement and data-driven decision-making.

Summary

To identify custom SQL in use within RPI, it is essential to focus on several key areas. Start by examining the SSRs within the Audience Designer. These reports are crucial as they often contain the custom SQL queries that drive data retrieval and reporting functionalities.

In addition to reviewing SSRs, utilizing trace logs can provide valuable insights into the execution of SQL queries. Trace logs capture the details of query performance and can help identify any custom SQL that may be affecting system efficiency or data accuracy.

Another important step is to query the metadata tables for custom attributes. These tables often hold information about the structure and definitions of custom SQL elements, which can be instrumental in understanding how data is being manipulated and accessed within the system.

Furthermore, it is advisable to review the data pipelines associated with RPI. These pipelines may incorporate custom SQL logic at various stages, influencing how data is processed and transformed before it reaches its final destination.

Lastly, maintaining thorough documentation and audit trails for all custom SQL logic is vital. This practice not only aids in tracking changes and understanding the evolution of SQL queries but also ensures compliance and facilitates troubleshooting when issues arise. By following these steps, you can effectively identify and manage custom SQL within RPI, leading to improved data integrity and system performance and ensuring transparency and compliance.

Additional resources

• https://docs.redpointglobal.com/rpi/interaction-designer-interactive-activity