GADATA

contacts_etlc_changes.rb

### Contacts Table Migration
class CreateHubspotContacts < ActiveRecord::Migration[5.2]
  def change
    create_table :hubspot_contacts, id: false do |t|
      t.string :contact_id 
      t.string :first_name,
      t.string :last_name,
      t.string :email,
      t.string :contact_source,
      t.string :lifecycle_stage,
      t.date :create_date,
      t.date :became_a_marketing_qualified_lead_date,
      t.date :became_a_sales_qualified_lead_date,
      t.date :became_an_opportunity_date,
      t.date :became_an_other_lifecycle_date,
      t.string :sales_campaign,
      t.date :sales_campaign_date,
      t.string :original_source,
      t.string :source_notes,
      t.string :associated_company_id
    end
  end
end

require_relative '../../boot'

count = 0

pre_process do
  $logger.info "Starting #{File.basename(__FILE__)}..."
end

source Hubspot::Source, {
  client_method: :get_all_contacts,
  properties: ["first_name", "last_name", "email", "contact_source", "lifecycle_stage", "create_date", "became_a_marketing_qualified_lead_date", "became_a_sales_qualified_lead_date", "became_an_opportunity_date", "became_an_other_lifecycle_date", "sales_campaign", "sales_campaign_date", "original_source", "source_notes", "associated_company_id"],
  collection_key: 'contacts',
  has_more_key: 'has-more'
}

transform do |row|
  count += 1

  mapping = {
    'first_name' => 'firstname',
    'last_name' => 'lastname',
    'email' => 'email',
    'contact_source' => 'contact_source',
    'lifecycle_stage' => 'lifecyclestage',
    'create_date' => 'createdate',
    'became_a_marketing_qualified_lead_date' => 'hs_lifecyclestage_marketingqualifiedlead_date',
    'became_a_sales_qualified_lead_date' => 'hs_lifecyclestage_salesqualifiedlead_date',
    'became_an_opportunity_date' => 'hs_lifecyclestage_opportunity_date',
    'became_an_other_lifecycle_date' => 'hs_lifecyclestage_other_date',
    'sales_campaign' => 'sales_campaign',
    'sales_campaign_date' => 'sales_campaign_date',
    'original_source' => 'hs_analytics_source',
    'source_notes' => 'source_notes',
    'associated_company_id' => 'associatedcompanyid'
  }

  newrow = {}
  newrow['contact_id'] = row['contactId']
  mapping.each do |key, value|
    newrow[key] = row.dig('properties', value, 'value')
  end
  newrow
end

transform Hubspot::DateTransform, "create_date", "became_a_marketing_qualified_lead_date", "became_a_sales_qualified_lead_date", "became_an_opportunity_date", "became_an_other_lifecycle_date", "sales_campaign_date"

destination CSVDestination,
  filename: File.join(ENV['TEMP_DIR'], ENV['JOB_ID'], 'hubspot_contacts.csv'), write_headers: false

post_process do
  $logger.info "Completed #{File.basename(__FILE__)}: #{count} rows written to CSV"
end