Automating Complex Assignments in Omni with CSV Uploads and Saved Query Views: A Step-by-Step Guide

[jade]

In this tutorial, we’ll explore how to leverage input tables to combine a results set and a lookup table with complex criteria, all within Omni. Specifically, we’ll demonstrate how to dynamically assign campaign managers based on category and total sales price criteria.

Pre-requisites:

• Set up csv write back to your data warehouse (docs here)
• User must have one of the following roles: restricted queriers, queriers, OR connection admin

Video Guide

Written Guide

Step 1: Preparing Your Data

Begin by organizing your data in a spreadsheet. Represented in a table, this could look like a column for category, campaign manager, and notes about when the assignment applies.

For example, you might want to assign Richard as the campaign manager for the category “Fashion hoodies & sweaters” if the total sales price exceeds $600,000.

To structure this criteria effectively, break it down into two columns:

• Numeric Column: Extract numerical values like the total sales price into a separate column, which we’ll call “sold amount.”
• Criteria Column: Define the inequality you want to evaluate, such as “greater than.”

Step 2: Creating a Query View and Joining Your Lookup

Let’s say we have a results set in Omni that looks like this:

1. Save as query view Start by saving your result set as a query view: Model → Save as query view.
2. Create a new tab: In a new query tab, go to All Views and Fields and find your new fact table. Hover over the table name and open the menu → Joins → + New join
3. Combine your data: In the join modal, navigate to the SQL tab to add your logic. The first line will just be a basic join to category in each table. Then, you’ll want to add the criteria checks:
   • Use a case statement to evaluate if the total sales price in the order fact table is greater than the sold amount in your criteria table.
   • Repeat this process for each evaluation criterion.

Copy, paste, modify as needed:

 ${campaign_manager_lookup.category} = ${order_fact.category}
  AND
  CASE 
    WHEN ${campaign_manager_lookup.criteria} = '>' AND ${order_fact.total_sale_price} > ${campaign_manager_lookup.sold_amount} THEN 1
    WHEN ${campaign_manager_lookup.criteria} = '<' AND ${order_fact.total_sale_price} < ${campaign_manager_lookup.sold_amount} THEN 1
    WHEN ${campaign_manager_lookup.criteria} = '>=' AND ${order_fact.total_sale_price} >= ${campaign_manager_lookup.sold_amount} THEN 1
    WHEN ${campaign_manager_lookup.criteria} = '<=' AND ${order_fact.total_sale_price} <= ${campaign_manager_lookup.sold_amount} THEN 1
    ELSE 0
  END = 1

Step 3: Check Your Work!

Once the join logic is set up, Omni will assign campaign managers to categories where the criteria are met.

1. Select relevant fields: Select category from your order fact table and campaign manager from your lookup table.
2. Check the data: To verify the accuracy of these assignments, select the total sales price from your order fact table and compare it with the sold amount and criteria from your lookup table.

Dynamic Updates

A powerful feature of this approach is the ability to update campaign managers or criteria dynamically. If your campaign managers change weekly or the criteria evolve, simply update the spreadsheet and refresh the data. This flexibility ensures that your assignments remain accurate and relevant over time.

For example, if I wanted to update the criteria for “dresses” to only assign Arielle if the total sale price is less than 320K, I can go back and update the CSV within Omni.

Hitting Save CSV will update the assignment in our final results tab:

By following these steps, you can efficiently manage complex assignments within Omni, making it easier to adapt to changing business needs.