Segment and Newsletters Report

report.csv

segment_newsletters.rb

fields = [
  'Store ID',
  'Store Type',
  'Total Customers',
  'Customers Without Email',
  'System Segments',
  'Custom Segments',
  'Newsletters',
  'System Segments in Newsletters',
  'System Segments NOT in Newsletters',
  'Custom Segments in Newsletters',
  'Custom Segments NOT in Newsletters'
]

report = CSV.generate do |csv|
  csv << fields
  Store.important.each do |store|
    row = CSV::Row.new fields, []
    row['Store ID'] = store.id
    row['Store Type'] = store.platform
    row['Total Customers'] = store.customers.count

    # It's easier to count positive emails (field with value > empty string) 
    # than negatives (which could be nil, '', or nonexistent)
    has_email = store.customers.where(:email.gt => '')
    row['Customers Without Email'] = row['Total Customers'] - has_email.count

    # Get the IDs of system and custom segments
    system_ids = store.segments.in(name: store.system_segments.keys).distinct(:id)
    custom_ids = CustomSegment.where(store_id: store.id).distinct(:id)

    # Get the IDs of non-Newsletter segments referred to in newsletters
    news_ids = Newsletters::SegmentWrapper.where(store_id: store.id).distinct(:legacy_segment_id)

    row['System Segments'] = system_ids.count
    row['Custom Segments'] = custom_ids.count
    row['Newsletters'] = Newsletters::Newsletter.where(store_id: store.id).count

    row['System Segments in Newsletters'] = (system_ids & news_ids).count
    row['System Segments NOT in Newsletters'] = (system_ids - news_ids).count
    row['Custom Segments in Newsletters'] = (custom_ids & news_ids).count
    row['Custom Segments NOT in Newsletters'] = (custom_ids - news_ids).count

    puts row
    csv << row
  end
end