Table of Contents

Creating an Account List From a Campaign

Camela Thompson Updated by Camela Thompson

You Have Options

Customers often ask for a list of accounts associated with a specific campaign. While we are building out our Segment capabilities, we list two options:

  1. A CSV file upload
  2. A new list from slightly modified SQL, provided below.

Creating a List From a CSV File Upload

If you have a CRM report with your list of accounts associated with a campaign, make sure you have the Account ID (18 characters) and website associated with each account record in the report.

  1. Export your CRM report into a file.
  2. Save the file in a CSV format.
  3. Double-check the file does not exceed 20 MB.
  4. Follow the instructions here.

Creating a Comprehensive (Lead & Account) List in SQL

To build out a comprehensive list of possible accounts (including those associated with Leads that are not created as an Account yet), we recommend using the cm_company table. This is a parent table that combines both lead and account data to ensure your list is comprehensive.

We've provided an example query to copy and paste into your List Builder. To modify this query to fit your campaign, you'll either need your Salesforce 18 string ID or Dynamics ID.

  1. To create a list, go to the Lists tab in the CaliberMind app:
  2. Click View All:
  3. Click the '+' sign to create a new list:
  4. Name your list something user friendly and descriptive like "2022.11 Webinar Target Accounts" and click OK:
  5. Copy and paste the code we've provided below:

SELECT DISTINCT a.company_id

,a.company_name

,a.website

,a.state

,a.country

,a.industry_short

FROM cm.cm_company as a

JOIN cm.cm_event as e

ON a.company_id = e.company_id

WHERE e.campaign_id = '7012M000001KejbQAC'

  1. Replace the highlighted text with your campaign ID (7012M000001KejbQAC) but be sure to keep the single quotation marks around your ID.
  2. Click Save and Test:
Click Save and Test to ensure your CaliberMind query is valid
  1. If your query passes the test, there will not be an error message and you will get a row number:
  2. Click Save then Preview to view your new target list:

Creating a List of True Accounts (SQL)

If you want to restrict your list to a known universe of converted accounts, we need to tell the query not to return Salesforce lead records.

To modify the query, you will need the 18 character ID from your Salesforce Campaign. If you use Microsoft Dynamics, you will need to alter the string slightly to match the lead ID pattern in your system.

  1. Follow the instructions above and swap out the code below for step 5:

SELECT DISTINCT a.company_id

,a.company_name

,a.website

,a.state

,a.country

,a.industry_short

FROM cm.cm_company as a

JOIN cm.cm_event as e

ON a.company_id = e.company_id

WHERE a.company_id NOT LIKE '00Q%'

AND e.campaign_id = '7012M000001KejbQAC'

  1. You will need to copy and paste your Campaign ID in the yellow highlighted string above (7012M000001KejbQAC). Note that you must keep the single quotation marks around your ID string.
  2. If you are using Microsoft Dynamics, swap out the unique ID pattern in the first few characters in the green highlighted string above (00Q%). Note that you must keep the single quotation marks around the text string.
  3. Click Test. If you do not have an error, the Preview bar will display a record count as shown below:

Creating a List of Accounts from a Campaign List (SQL)

When you have multiple campaigns you want to draw from, and if an account is eligible if they are in any of the campaigns, follow the steps in "Creating a Comprehensive (Lead and Account) List" until step 5.

To modify the query, you will need the 18 character ID from your Salesforce Campaigns. If you use Microsoft Dynamics, you will need to know the unique pattern in the Lead ID String at the beginning of an ID and the full ID from your campaign record.

  1. Swap out the code in step 5 above with the following:

SELECT DISTINCT a.company_id

,a.company_name

,a.website

,a.state

,a.country

,a.industry_short

FROM cm.cm_company as a

JOIN cm.cm_event as e

ON a.company_id = e.company_id

WHERE a.company_id NOT LIKE '00Q%'

AND e.campaign_id IN ('7012M000001KejbQAC'

,'7012M000001KexOQAS'

,'7012M000001KexTQAS'

)

If you use Microsoft Dynamics, swap the green highlighted text (00Q%) with Microsoft's unique string characters at the beginning of a lead ID. If you use Salesforce, leave it as is.

  1. Replace the yellow highlighted text with your own ID strings.
    1. Have fewer IDs than we provided? No problem. Just make sure that when you remove an ID, you remove the comma and leave the parenthesis as in the example below and remember to replace the yellow text with your unique campaign IDs:
    SELECT DISTINCT a.company_id
    ,a.company_name
    ,a.website
    ,a.state
    ,a.country
    ,a.industry_short
    FROM cm.cm_company as a
    JOIN cm.cm_event as e
    ON a.company_id = e.company_id
    WHERE a.company_id NOT LIKE '00Q%'
    AND e.campaign_id IN ('7012M000001KejbQAC'
    ,'7012M000001KexOQAS'
    )
    1. Have more IDs than we provided? Also not a problem. When adding a new ID, add a comma and put both the comma and string inside the parenthesis as shown below:
    SELECT DISTINCT a.company_id
    ,a.company_name
    ,a.website
    ,a.state
    ,a.country
    ,a.industry_short
    FROM cm.cm_company as a
    JOIN cm.cm_event as e
    ON a.company_id = e.company_id
    WHERE a.company_id NOT LIKE '00Q%'
    AND e.campaign_id IN ('7012M000001KejbQAC'
    ,'7012M000001KexOQAS'
    ,'7012M000001KexTQAS'
    ,'7012M000001TYaAQAW'
    )

How did we do?

Keyboard Shortcuts using SQL

How to create a list of all Opportunities that have a very low Engagement

Contact