Connecting
Campaign with Queries
Smart writing and alternate
write-back tables are vital when using database
queries with Campaign Enterprise
By Jim Kinkade, Customer
Support Supervisor
Arial Software Tech Support Center
Queries are amazing database tools. A query
is generally defined as a request for information
from a database. Using a query, you can pull a
myriad of information from a number of tables
in your database, creating a dynamic record set.
Queries can then be stored in your database and
run at your convenience.
Using a query, you can find all the people in
your database who wear green socks on Thursdays
(provided that you've gathered that information
from your customers or prospects in advance).
Armed with this data, you can whittle your list
size from several thousand down to a few hundred
to whom you can send a relevant message, such
as a notice for your new line of chartreuse hosiery.
When the original data is modified, the query
will reflect any changes in your source data the
next time the query is run.
Campaign Can Connect or Store
Campaign Enterprise can easily connect to certain
types of queries saved in your database, or it
can store the query for you directly in the Data
Source tab.
A common pitfall with using a query as your source
record set can occur if one of the criteria used
for including a record in the query suddenly changes,
and you attempt to update the query, rather than
the source table. For example: if you are looking
at a record set that includes all email addresses
where the email_sent field equals zero, and upon
sending you increment the same field, email_sent
by one, the newly updated records will no longer
appear in the query results. Subsequent update
attempts to that record may fail if you try to
update the query, which now has no records. This
is why it is imperative to write-back to an alternate
table when using the advanced tracking features
of Campaign.
Writing Your Query
The primary concerns when writing your query
is to understand how the database with which you
are working interprets SQL (Structured Query Language).
While SQL is somewhat standard, databases do behave
differently and have syntax idiosyncrasies. Get
with someone who knows your database to help you
construct good queries. The following is a very
basic example of a query that would work well
with Campaign:
SELECT * FROM dbo.table
WHERE email_sent < 1.
This will return all records from the table called
dbo.table where the specified field, email_sent,
has a value less than one. To find all of the
people in the database who might wear green socks
on Thursdays you might write the following SQL
statement:
SELECT email, first_name,
last_name, email_sent, favorite_color FROM dbo.table
WHERE favorite_color IS ‘green’
AND email_sent < 1
Suppose this query returned five records from
your table. Assuming that you have your campaign
set up correctly, once you run the campaign, the
records you just sent to will no longer be available
since the email_sent field for those five records
would be updated to one. Since you are updating
the email_sent field, you might reconsider using
the email_sent field as a condition. Instead you
might rewrite the query using other information
in your table as the criteria:
SELECT email, first_name,
last_name, email_sent, favorite_color offer_subscriber
FROM dbo.table WHERE favorite_color IS ‘green’
AND offer_subscriber = 1
Using this select statement would allow you to
pull records that subscribed to the current offer
for chartreuse hosiery and would allow you to
increment the email_sent field during sending,
without affecting the results of the query. The
query will continue to be available for updating
as long the values in the favorite_color and offer_subscriber
fields do not change.
Using an Alternate Table for Write-Backs
When using queries directly in Campaign, you
must connect to a separate write-back table to
handle these requests. This step minimizes the
problems with subsequent database updates, including
click-through and open tracking requests. When
connecting to a query located in your database,
you should also consider using an alternate write-back
table.
The write-back table needs contain all of the
information that you want to update with the advanced
settings, plus the email address and the unique
id fields. Preferably, the alternate write-back
table would be the parent table, where most of
your raw data is already stored. This would ensure
that the email address fields and the unique ID
fields match directly. It would also ensure that
any write-backs would still occur after a record
is removed from a query, because it still resides
in the source table.
To clarify one point, when writing back to an
alternate table, some of the write-backs occur
when the record is first opened and records are
submitted to the mail server. If you select the
Database Write Back feature, to increment a sent
email field, or date sent field in the database,
that write-back occurs on the source data. If
you subsequently use information you are updating
to query your records (see the previous example),
records may be removed from the source data as
soon as they are sent. If that is the case, none
of the other write-backs can occur because the
records are no longer found by that particular
query. It is imperative that you select a separate
table to write-back when using a query as your
source data.
Conclusion
Logical, well written queries are the key to
managing your email campaigns to their full potential.
If the query does not make logical sense at the
time you build it, it will not make sense to Campaign.
Seek help when writing your queries and inspect
them prior to sending your emails.
The bottom line: Campaign can only do what you
tell it to do. Make sure that what you are saying
to it makes sense programmatically, sequally and
data-basically.
Additional Information
For online assistance and information about queries
and SQL, take a look at these websites:
Return to
Technical Tips |
