Campaigns

​

Campaigns in Oration

​

Using Customer Uploaded Data in Campaigns

1. Prepare Your CSV File: Create a CSV file with the necessary fields that match your SQL query requirements. Below is a sample snippet of the CSV format you should use:

   ​

   File Structure

phoneNumber,campaign,identifier,customerName,customerType,lastPurchaseDate
+919876543210,welcome-series-q1,CUST-12345,John Smith,welcome-campaign,2024-01-15
+15551234567,welcome-series-q1,CUST-67890,Jane Doe,welcome-campaign,2024-02-20
+447700900123,welcome-series-q1,CUST-11111,Bob Johnson,welcome-campaign,2024-01-30

• identifier: Unique identifier for each customer.
• customerType: Type of customer (e.g., ‘welcome-campaign’).
• lastPurchaseDate: The date of the customer’s last interaction or purchase. This is customized variable aka Dynamic Variable used for personalized message for each customer.

2. Upload the CSV File: Navigate to the Customers section in the platform and upload your prepared CSV file. Ensure that the data is correctly formatted and all required fields are included.
3. Create your campaign: Once the customer data is uploaded, you are all set to create your campaign.

​

Create New Campaign

​

Campaign Status

​

Agent Selection

​

Campaign Identity

​

Customer Targeting

SELECT cust.id 
FROM public.customers cust 
WHERE 
    cust.metadata->>'customerType' = 'campaign' 
    AND cust.workspace_id = {{WORKSPACE_ID}} 
    AND cust.updated_at BETWEEN {{CAMPAIGN_RUN_START_DATE}} AND {{CAMPAIGN_RUN_START_DATE}} + INTERVAL '1 day';

​

What does this query do?

​

Step‑by‑step explanation:

1. Filter by customer type
   Every customer has some extra information stored in a metadata field. One of these details is customerType. In this query, we are looking only for customers where customerType = 'campaign'. You can change ‘campaign’ to any other value depending on the type of customers you want to filter — for example, ‘lead’, ‘prospect’, or ‘subscriber’.
2. Filter by workspace
   Each customer belongs to a specific workspace. The query uses WORKSPACE_ID to fetch customers for only that specific workspace. This is managed internally by the Oration team, so you usually don’t need to change it.
3. Filter by date range
   It only picks customers whose updated_at date is after the campaign run start date (CAMPAIGN_RUN_START_DATE) and before one day after that date. In simple terms, it’s finding customers who were updated within 24 hours from the start of the campaign.

​

In short:

​

Reattempt Rule Sql Queries

WITH customer_speech_status as (
SELECT
	c.customer_id,
	BOOL_OR(t.messages is not null and t.messages ilike '%userMessage%') as has_spoken
FROM
	conversations c
LEFT JOIN transcripts t on
	c.id = t.conversation_id
WHERE
	c.conversation_type = 'telephony'
	and c.campaign_run_id = {{CAMPAIGN_RUN_ID}}
	and c.workspace_id = {{WORKSPACE_ID}}
	and c.customer_id is not null
GROUP BY
	c.customer_id )
SELECT
	cust.id
FROM
	customers cust
INNER JOIN (
	select
		customer_id
	from
		customer_speech_status
	where
		has_spoken = false
		or has_spoken is null ) unpicked on
	cust.id = unpicked.customer_id
WHERE
	( cust.metadata->>'customerType' = 'welcome-campaign'
		and cust.workspace_id = {{WORKSPACE_ID}}
		and cust.updated_at > {{CAMPAIGN_RUN_START_DATE}}
		and cust.updated_at < {{CAMPAIGN_RUN_START_DATE}} + interval '1 day' );

​

What does this query do?

• Belong to a specific campaign and workspace
• Were updated within one day of the campaign starting
• Had a telephony call conversation during that campaign but no actual user messages recorded in the transcript.

​

Step‑by‑step explanation

1. Identify customers from telephony conversations without user messages
   First, the query looks at the conversations table and picks out conversations that:
   • Are of type telephony (phone calls)
   • Are part of a specific campaign run (CAMPAIGN_RUN_ID)
   • Belong to a specific workspace (WORKSPACE_ID)
   • Have a linked customer (customer_id is not empty)
   • Either have no conversation transcript (t.messages IS NULL) or the transcript text does not contain the term userMessage (meaning the agent spoke, but the system did not detect anything from the customer).
   This produces a list of customer IDs who took part in such calls.
2. Match those customers in the customers list
   The query then joins this list with the customers table to get details for those customers.
3. Apply customer filters
   The results are further filtered to include only customers who:
   • Have customerType in their metadata set to ‘welcome-campaign’. This is just a tag to identify the campaign type — the name can be different depending on the campaign.
   • Belong to the same workspace indicated by WORKSPACE_ID
   • Were updated after the campaign started (CAMPAIGN_RUN_START_DATE) and before one day later.
   In other words, they were updated during the first 24 hours of the campaign.

​

In short:

​

Call Attempt

​

Campaign Type

​

Attempt Management

• Max Attempts Daily: Set the maximum number of call attempts per customer per day. This prevents overwhelming customers while ensuring adequate outreach.
• Max Re Attempt Days: Define the maximum number of days over which reattempts should be made. This controls the campaign duration and prevents indefinite calling.
• Min Re Attempt Delay: Set the minimum delay between reattempts to the same customer. This ensures appropriate spacing between calls.

​

Concurrency Control

​

Concurrency and Scheduling

​

Scheduling

​

Business Hours

​

Campaign Timeline

• Start Date: Set the earliest time to make calls using 24-hour format (dd/mm/yyyy, —:— —). This defines when your campaign becomes active.
• End Date: Set the latest time to make calls using 24-hour format (dd/mm/yyyy, —:— —). This automatically stops the campaign after the specified date.

​

Best Practices

​

SQL Query Tips

• Use precise targeting criteria to reach the most relevant customers
• Test your SQL queries with small datasets before launching full campaigns
• Consider customer preferences and opt-out status in your queries
• Use different logic for first attempts vs. reattempts

​

Scheduling Recommendations

• Set business hours that align with your customers’ time zones
• Avoid calling during holidays or inappropriate hours
• Use reasonable attempt delays to prevent customer annoyance
• Monitor campaign performance and adjust timing as needed

​

Campaign Management

• Start with small test campaigns before scaling up
• Monitor max concurrency to ensure system performance
• Set appropriate end dates to prevent campaigns from running indefinitely
• Regularly review and update your targeting criteria

Need more help? Reach out to our team at developers@oration.ai