Arial Software
  • Home
  • Product
    • Campaign Enterprise
    • Campaign Enterprise Upgrade
  • Support
    • Chat Now
    • Demos
    • Tutorials
    • Education/Tools
    • Newsletter Signup
    • Blog
  • Contact Us
    • About Us
    • Privacy Policy

Blog

SQL Statements Add Flexibility and Control

1/15/2013

1 Comment

 
By: Chris Lewis

When the first Arial Software products were create the database language of SQL was thought of to be too hard to use and just for programmers.  Many of the database operations that were done using our program were simple field/column updates, like incrementing a number in a record if the email was sent. The program was set up so that the field to be updated was in a selection box and the only operation you could do is increment the field by one. This functionality still exists today even in version 11, but as many have seen this simple operation is not sufficient.  Because of this we added the ability to run an SQL statement when an even happens in Campaign Enterprise, like when a record is sent, when an email is unsubscribed, or a click through occurs.  This feature can be turned on in the administration area which then causes the program to show this new SQL statement box on each even configuration screen.  Some have opted to, instead of incrementing a record, to insert a new record recording the event into a table that can be used for later reporting.  And example of this, say for an unsubscribe operation would be:

INSERT INTO tblUnsubscribes (CampaignID,UniqueID,EventDateTime) values ({CAMPAIGNID},{UNIQUEID},GetDate());

So, when a unsubscribe event happens for a particular email that was sent, this statement is sent to the database you are using and the values within the braces { } are replaced with real values.  The resulting statement that is actually sent to your database would look like this:

INSERT INTO tblUnsubscribes (CampaignID,UniqueID,EventDateTime) values (14,43456,GetDate());

After the operation is done, you will have a new record in the tblUnusubscribes table recording this event.  This data can now be used for filtering and/or reporting purposes later.  The advantage of this method is that it records the specific date and time of the event, and the data is separate from the original record.  Using this method is required when the original data cannot be modified due to security or database structure.

You can use any legal SQL statement you want for these events.  You could opt to update a record instead of inserting, or you could run a stored procedure to trigger other events.  More information is available on this subject on this website or by request.

1 Comment

De-Dupe Email Address List in MS Access

1/30/2012

1 Comment

 
_While Campaign Enterprise does contain a de-dupe (de-duplication) email address feature in the Datasource Tab when you edit a campaign, it’s best to take care of duplicate entries permanently on the source database table. Here is how to do that with Microsoft Access. You can use this query to delete records with duplicate email addresses, however, the Access table must have an autonumber type of field as the unique identifier. Here are the steps:

  1. Create a new query in design view
  2. Select the table from which you want to remove duplicate records and click Add; then Close
  3. Go to Query and select Delete Query
  4. Go to View and select SQL View
  5. Use the following SQL statement:

    DELETE *
    FROM Table1
    WHERE (ID) NOT IN (SELECT First([Table1].ID) AS ID FROM [Table1] GROUP BY [Table1].Email);


    Table1 is the table with the duplicate email addresses in it, ID is your unique id field and Email is your email address field. You will need to rename this information in the SQL Statement to match the information from the table you are using. This example will keep the first instance of the email address in your table and remove any instances after that. Backing up your original table before you run this query would be a good idea.
  6. Save and Run the Query.
It is important to keep your email list free of duplicate entries so that you don't accidentally send out multiple email messages to the same address. If you collect subscribers via web form, please consider disallowing duplicate entries in the first place. If duplicates do occur, re-run this query every so often, and as a final fail-safe, check the de-dupe during send feature on the Datasource Tab of Campaign Enterprise.
1 Comment

    Archives

    December 2017
    March 2017
    July 2016
    January 2016
    May 2015
    March 2014
    January 2014
    October 2013
    August 2013
    July 2013
    June 2013
    February 2013
    January 2013
    December 2012
    November 2012
    October 2012
    September 2012
    August 2012
    July 2012
    May 2012
    April 2012
    March 2012
    January 2012
    December 2011

    Categories

    All
    Access
    Addresses
    Bounce
    Campaign Enterprise
    Campaign Enterprise
    Character Set
    Connections
    Cost Benefit
    Cost-benefit
    Database
    Design
    Editor
    Email Lists
    Email Marketing Director
    Email Service Providers
    How To
    How To
    Html
    Iis
    Installation
    Manage Lists
    Mysql
    Network
    News
    Newsletter
    Port Managment
    Query
    Relaying
    Saving Money
    Sending
    Smtp
    Sql
    Support
    Troubleshooting
    VERPS
    Write Backs
    Write Backs

    RSS Feed

Arial Software          info@arialsoftware.com          Ph 949.218.3852
  • Home
  • Product
    • Campaign Enterprise
    • Campaign Enterprise Upgrade
  • Support
    • Chat Now
    • Demos
    • Tutorials
    • Education/Tools
    • Newsletter Signup
    • Blog
  • Contact Us
    • About Us
    • Privacy Policy