Perform Advanced Audience Overlap Analysis in Data Distiller Query Pro Mode

[#1]

Perform Advanced Audience Overlap Analysis in Data Distiller Query Pro Mode

Author: @annamalai  (Senior Product Manager) & @ Purnaram Kodavatiganti (Architect)

Reviewers: @SaurabhMa2  (Group Product Manager) & @ Anil Malkani (Director Engineering)

Welcome to a guide on utilizing Data Distiller's Query Pro Mode to create a SQL based analysis template that performs Advanced Audience Overlaps.

First let’s look at why different personas would be interested in performing this analysis?

Use Cases by different personas

Marketer

• Campaign Optimization: Identify overlapping audiences across different campaigns to reduce duplication and optimize marketing strategy by understanding campaign performance concerning the audiences.

Marketing Ops

• Data Hygiene: Analyze and eliminate duplicate audience segments to ensure deduplicated datasets for downstream activations.
• Efficiency in Activation: Prevent activating the same set of profiles via different audiences to the same destinations.

Now, I like the Audience Overlap Report on the Audience Overview page. Still, I don't want to be limited by the ability to cycle one audience at a time. Instead, I want to select multiple audiences at a time and review the overlaps. I also don't want to be limited to only viewing 50 overlaps at max. Instead, I want to review up to 5000 overlaps. To do this, I can follow the below steps:

Step 1: Get SQL Inspiration from Standard Charts

I can visit the Audiences Overview page and select the toggle for ViewSQL to view the SQL for the Audience Overlap Report. All I need to do is copy the SQL, which I can use in Data Distiller Query Pro Mode to author my version of the Advanced Audience Overlap Report.

Step 2: Create Multi-Select Audience Filter

I can follow the filtering authoring tutorial to create my audience filter. The SQL I used is provided below, which brings in the audience name (segment name) to display for the user and the audience ID (segment ID) to pass to the chart SQL as a parameter.

SELECT
  b.Segment_Name AS Value,
  b.segment_id AS ID
FROM
  qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines a
  JOIN qsaccel.profile_agg.adwh_dim_segments b ON a.segment_id = b.segment_ID
  JOIN qsaccel.profile_agg.adwh_dim_merge_policies c ON a.merge_policy_id = c.merge_policy_id
  JOIN (
    SELECT
      MAX(process_date) last_process_date,
      merge_policy_id
    FROM
      qsaccel.profile_agg.adwh_lkup_process_delta_log
    WHERE
      process_name = 'FACT_TABLES_PROCESSING'
      AND process_status = 'SUCCESSFUL'
    GROUP BY
      merge_policy_id
  ) d ON a.merge_policy_id = d.merge_policy_id
  AND a.date_key = d.last_process_date
GROUP BY
  b.Segment_Name,
  b.segment_id
ORDER BY
  b.Segment_Name limit 0;

Step 3: Build Advanced Audience Overlap Table

Finally, I can follow the advanced table widget creation tutorial with a clear purpose-to author my table using the SQL I copied from the Step 1.

The Key changes to the SQL I have made are to include the SQL parameters from the global filter created in Step 2 and also add a limit of 5000, which increases from 50 in the standard out-of-the-box chart.

Here is the chart authoring screen for reference. I have added the necessary columns and enabled sorting, and then I have provided the updated SQL. Note the global filter linking to SQL query parameter in the screen shot below.

SELECT
  b.source_segment_name,
  a.source_segment_id,
  c.overlap_segment_name,
  a.overlap_segment_id,
  max(b.source_segment_audience_count) source_segment_audience_count,
  max(c.overlap_segment_audience_count) overlap_segment_audience_count,
  max(a.overlap_audience_count) overlap_audience_count,
  CASE
    WHEN (
      max(b.source_segment_audience_count) + max(c.overlap_segment_audience_count) - max(a.overlap_audience_count)
    ) > 0 THEN (
      cast(max(a.overlap_audience_count) AS DECIMAL(18, 2)) / cast(
        (
          max(b.source_segment_audience_count) + max(c.overlap_segment_audience_count) - max(a.overlap_audience_count)
        ) AS DECIMAL(18, 2)
      )
    ) * 100::DECIMAL(9, 2)
    ELSE 100.00
  END overlapping_percentage
FROM
  (
    SELECT
      adwh_fact_profile_overlap_of_segments.Segment1 source_segment_id,
      adwh_fact_profile_overlap_of_segments.Segment2 overlap_segment_id,
      Sum(count_of_overlap) overlap_audience_count
    FROM
      qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments
    WHERE
      qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.date_key = (
        Select
          MAX(date_key)
        from
          qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
      )
    GROUP BY
      qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.Segment2,
      qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.Segment1,
      qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.merge_policy_id
  ) a
  INNER JOIN (
    SELECT
      sum(
        qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.count_of_profiles
      ) source_segment_audience_count,
      qsaccel.profile_agg.adwh_dim_segments.segment_name source_segment_name,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id Segment1
    FROM
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
      JOIN qsaccel.profile_agg.adwh_dim_segments ON qsaccel.profile_agg.adwh_dim_segments.segment_id = qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_id
    WHERE
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = (
        Select
          MAX(date_key)
        from
          qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
      )
    GROUP BY
      qsaccel.profile_agg.adwh_dim_segments.segment_name,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id
  ) b ON a.source_segment_id = b.segment1
  INNER JOIN (
    SELECT
      sum(
        qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.count_of_profiles
      ) overlap_segment_audience_count,
      qsaccel.profile_agg.adwh_dim_segments.segment_name overlap_segment_name,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id Segment2
    FROM
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
      JOIN qsaccel.profile_agg.adwh_dim_segments ON qsaccel.profile_agg.adwh_dim_segments.segment_id = adwh_fact_profile_by_segment_trendlines.segment_id
    WHERE
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = (
        Select
          MAX(date_key)
        from
          qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
      )
    GROUP BY
      qsaccel.profile_agg.adwh_dim_segments.segment_name,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id,
      qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id
  ) c ON a.overlap_segment_id = c.segment2
WHERE
  (
        (
          UPPER(COALESCE($AUDIENCE_FILTER, '')) IN ('', 'NULL')
        )
        OR (a.source_segment_id IN ($AUDIENCE_FILTER))
      )
  OR (
        (
          UPPER(COALESCE($AUDIENCE_FILTER, '')) IN ('', 'NULL')
        )
        OR (a.overlap_segment_id IN ($AUDIENCE_FILTER))
      )
GROUP BY
  b.source_segment_name,
  a.source_segment_id,
  c.overlap_segment_name,
  a.overlap_segment_id
ORDER BY
  overlapping_percentage DESC
LIMIT
  5000;

Final Outcome

And that's it. You set up this template once, and every day, both business and technical personas can easily visit it, update the filters to select the desired audiences, and review the overlaps. What's more, you also automatically download it as PDF and CSV without any additional work. Below is the output of how it looks once completed.