The Complete Linkedin Industry List in 2023 (+ Linked Ads Categories)

Linkedin Ads Company targeting

As of January 2023, there are 416 industry labels to choose from to categorize a company page. However, Linkedin Ads only allows advertisers to target based on 20 industry segments. Each of these 416 industry labels belongs to one of these 20 industry segments. That discrepancy amounts to a big problem for advertisers who want to run highly targeted account-based campaigns with for a specific ideal customer profile (ICP). 

A poignant example of this is demonstrated by the different labels between two well-known brands: Nike and Adidas. As you can see on their company pages, Nike has chosen a sensible category, “Retail,” which rolls up predictably into the “Retail” industry segment.

Linkedin company page industry label for Nike

On the other hand, Adidas has chosen to label itself as a “Sporting Goods Manufacturer,” which, unfortunately for advertisers, rolls up into the “Manufacturing” industry segment.

Linkedin company page industry label for Adidas

Of course, both of these labels make sense for each of the brands, but it makes it impossible to target both brands in the same campaign with only one target industry! You could either choose to target the Retail segment and capture Nike along with everybody from Ralphs Grocery to IKEA or the Manufacturing segment, which encompasses everyone from 3M to the Willamette Valley Wineries Association!

How could two big brands that are so similar be categorized so differently? That’s what this post is meant to help you understand! In the tables below, you can see how much granularity is lost in Linkedin Ads industry targeting, so you can improve your targeting decisions going forward.

Want better company targeting on Linkedin?

Connect with me on Linkedin if you’d like to create a transparent and targeted named account list from a database of nearly 12 million companies worldwide. Get company lists with corresponding firmographic data starting at $100.

Linkedin Ads Industry List for Account Targeting

Here are the 20 industry segments that advertisers can target with Linkedin Ads in 2023. The columns show how many Linkedin Company page industry labels and NAICS codes roll up into each industry segment. As you can see, there is a big difference between the homogeneity of the  Holding Companies industry segment (with just a single industry label) and the Manufacturing industry segment, which encompasses 84 industry labels!

As you can tell, the NAICS (North American Industry Classification System), which the Linkedin industry taxonomy closely mirrors, is far more specific than the Linkedin Industry classifications. It goes without saying most advertisers are better off targeting an exhaustive list of companies within a specific NAICS classification than rolling the dice with Linkedin industry targeting.

Linkedin Ads Industry SegmentNumber of Linkedin Industries IncludedNumber of NAICS Codes includedLinkedin Members
Accommodation93426,000,000+
Administrative and Support Services187823,000,000+
Construction127326,000,000+
Consumer Services199824,000,000+
Education123832,000,000+
Entertainment Providers216125,000,000+
Farming, Ranching, Forestry61315,000,000+
Financial Services208944,000,000+
Government Administration277539,000,000+
Holding Companies173,500,000+
Hospitals and Health Care229236,000,000+
Manufacturing8465092,000,000+
Oil, Gas, and Mining84813,000,000+
Professional Services3392110,000,000+
Real Estate and Equipment Rental Services73912,000,000+
Retail2016742,000,000+
Technology, Information and Media378157,000,000+
Transportation, Logistics, Supply Chain and Storage1614121,000,000+
Utilities16366,400,000+
Wholesale2215621,000,000+

The Complete Linkedin Company Industry List

You’re probably wondering how each of these industry labels fits within an industry category and just how watered down are these industry categories. Look no further.

Linkedin has categories and recategorized industries (in 2023, the industry system is currently in v2) loosely along the NAICS system. In fact, their industry ID system resembles the NAICS’ hierarchical numbering system with a little bit less specificity. The Linkedin industry categorization system just has three levels of hierarchy and groups many NAICS codes together within the second or third level of the hierarchy.

From a Linkedin Ads targeting perspective, only the top level of the hierarchy matters, but when labeling a company page, you can choose a label from any of the three levels depending on the level of specificity needed to categorize a company.

Here is how the complete industry taxonomy breaks down.

Linkedin Industry IDLinkedin Industry LabelLevel 1 (Linkedin Ads Industry Segment)Level 2Level 3
2190Accommodation ServicesAccommodation Services

34Food and Beverage ServicesAccommodation ServicesFood and Beverage Services
2217Bars, Taverns, and NightclubsAccommodation ServicesFood and Beverage ServicesBars, Taverns, and Nightclubs
2212CaterersAccommodation ServicesFood and Beverage ServicesCaterers
2214Mobile Food ServicesAccommodation ServicesFood and Beverage ServicesMobile Food Services
32RestaurantsAccommodation ServicesFood and Beverage ServicesRestaurants
31HospitalityAccommodation ServicesHospitality
2197Bed-and-Breakfasts, Hostels, HomestaysAccommodation ServicesHospitalityBed-and-Breakfasts, Hostels, Homestays
2194Hotels and MotelsAccommodation ServicesHospitalityHotels and Motels
1912Administrative and Support ServicesAdministrative and Support Services

1938Collection AgenciesAdministrative and Support ServicesCollection Agencies
110Events ServicesAdministrative and Support ServicesEvents Services
122Facilities ServicesAdministrative and Support ServicesFacilities Services
1965Janitorial ServicesAdministrative and Support ServicesFacilities ServicesJanitorial Services
2934Landscaping ServicesAdministrative and Support ServicesFacilities ServicesLandscaping Services
101FundraisingAdministrative and Support ServicesFundraising
1916Office AdministrationAdministrative and Support ServicesOffice Administration
121Security and InvestigationsAdministrative and Support ServicesSecurity and Investigations
1956Security Guards and Patrol ServicesAdministrative and Support ServicesSecurity and InvestigationsSecurity Guards and Patrol Services
1958Security Systems ServicesAdministrative and Support ServicesSecurity and InvestigationsSecurity Systems Services
104Staffing and RecruitingAdministrative and Support ServicesStaffing and Recruiting
1923Executive Search ServicesAdministrative and Support ServicesStaffing and RecruitingExecutive Search Services
1925Temporary Help ServicesAdministrative and Support ServicesStaffing and RecruitingTemporary Help Services
1931Telephone Call CentersAdministrative and Support ServicesTelephone Call Centers
108Translation and LocalizationAdministrative and Support ServicesTranslation and Localization
30Travel ArrangementsAdministrative and Support ServicesTravel Arrangements
103Writing and EditingAdministrative and Support ServicesWriting and Editing
48ConstructionConstruction

406Building ConstructionConstructionBuilding Construction
413Nonresidential Building ConstructionConstructionBuilding ConstructionNonresidential Building Construction
408Residential Building ConstructionConstructionBuilding ConstructionResidential Building Construction
51Civil EngineeringConstructionCivil Engineering
431Highway, Street, and Bridge ConstructionConstructionCivil EngineeringHighway, Street, and Bridge Construction
428Subdivision of LandConstructionCivil EngineeringSubdivision of Land
419Utility System ConstructionConstructionCivil EngineeringUtility System Construction
435Specialty Trade ContractorsConstructionSpecialty Trade Contractors
453Building Equipment ContractorsConstructionSpecialty Trade ContractorsBuilding Equipment Contractors
460Building Finishing ContractorsConstructionSpecialty Trade ContractorsBuilding Finishing Contractors
436Building Structure and Exterior ContractorsConstructionSpecialty Trade ContractorsBuilding Structure and Exterior Contractors
91Consumer ServicesConsumer Services

90Civic and Social OrganizationsConsumer ServicesCivic and Social Organizations
1909Industry AssociationsConsumer ServicesCivic and Social OrganizationsIndustry Associations
107Political OrganizationsConsumer ServicesCivic and Social OrganizationsPolitical Organizations
1911Professional OrganizationsConsumer ServicesCivic and Social OrganizationsProfessional Organizations
2318Household ServicesConsumer ServicesHousehold Services
100Non-profit OrganizationsConsumer ServicesNon-profit Organizations
2258Personal and Laundry ServicesConsumer ServicesPersonal and Laundry Services
2272Laundry and Drycleaning ServicesConsumer ServicesPersonal and Laundry ServicesLaundry and Drycleaning Services
2259Personal Care ServicesConsumer ServicesPersonal and Laundry ServicesPersonal Care Services
2282Pet ServicesConsumer ServicesPersonal and Laundry ServicesPet Services
131Philanthropic Fundraising ServicesConsumer ServicesPhilanthropic Fundraising Services
89Religious InstitutionsConsumer ServicesReligious Institutions
2225Repair and MaintenanceConsumer ServicesRepair and Maintenance
2247Commercial and Industrial Machinery MaintenanceConsumer ServicesRepair and MaintenanceCommercial and Industrial Machinery Maintenance
2240Electronic and Precision Equipment MaintenanceConsumer ServicesRepair and MaintenanceElectronic and Precision Equipment Maintenance
2255Footwear and Leather Goods RepairConsumer ServicesRepair and MaintenanceFootwear and Leather Goods Repair
2253Reupholstery and Furniture RepairConsumer ServicesRepair and MaintenanceReupholstery and Furniture Repair
2226Vehicle Repair and MaintenanceConsumer ServicesRepair and MaintenanceVehicle Repair and Maintenance
1999EducationEducation

132E-Learning ProvidersEducationE-Learning Providers
68Higher EducationEducationHigher Education
67Primary and Secondary EducationEducationPrimary and Secondary Education
105Professional Training and CoachingEducationProfessional Training and Coaching
2018Technical and Vocational TrainingEducationTechnical and Vocational Training
2019Cosmetology and Barber SchoolsEducationTechnical and Vocational TrainingCosmetology and Barber Schools
2025Fine Arts SchoolsEducationTechnical and Vocational TrainingFine Arts Schools
2020Flight TrainingEducationTechnical and Vocational TrainingFlight Training
2029Language SchoolsEducationTechnical and Vocational TrainingLanguage Schools
2012Secretarial SchoolsEducationTechnical and Vocational TrainingSecretarial Schools
2027Sports and Recreation InstructionEducationTechnical and Vocational TrainingSports and Recreation Instruction
28Entertainment ProvidersEntertainment Providers

38Artists and WritersEntertainment ProvidersArtists and Writers
37Museums, Historical Sites, and ZoosEntertainment ProvidersMuseums, Historical Sites, and Zoos
2161Historical SitesEntertainment ProvidersMuseums, Historical Sites, and ZoosHistorical Sites
2159MuseumsEntertainment ProvidersMuseums, Historical Sites, and ZoosMuseums
2163Zoos and Botanical GardensEntertainment ProvidersMuseums, Historical Sites, and ZoosZoos and Botanical Gardens
115MusiciansEntertainment ProvidersMusicians
2130Performing Arts and Spectator SportsEntertainment ProvidersPerforming Arts and Spectator Sports
2139Circuses and Magic ShowsEntertainment ProvidersPerforming Arts and Spectator SportsCircuses and Magic Shows
2135Dance CompaniesEntertainment ProvidersPerforming Arts and Spectator SportsDance Companies
39Performing ArtsEntertainment ProvidersPerforming Arts and Spectator SportsPerforming Arts
33Spectator SportsEntertainment ProvidersPerforming Arts and Spectator SportsSpectator Sports
2143RacetracksEntertainment ProvidersPerforming Arts and Spectator SportsSpectator Sports
2142Sports Teams and ClubsEntertainment ProvidersPerforming Arts and Spectator SportsSpectator Sports
2133Theater CompaniesEntertainment ProvidersPerforming Arts and Spectator SportsTheater Companies
40Recreational FacilitiesEntertainment ProvidersRecreational Facilities
2167Amusement Parks and ArcadesEntertainment ProvidersRecreational FacilitiesAmusement Parks and Arcades
29Gambling Facilities and CasinosEntertainment ProvidersRecreational FacilitiesGambling Facilities and Casinos
2179Golf Courses and Country ClubsEntertainment ProvidersRecreational FacilitiesGolf Courses and Country Clubs
2181Skiing FacilitiesEntertainment ProvidersRecreational FacilitiesSkiing Facilities
124Wellness and Fitness ServicesEntertainment ProvidersRecreational FacilitiesWellness and Fitness Services
201Farming, Ranching, ForestryFarming, Ranching, Forestry

63FarmingFarming, Ranching, ForestryFarming
150HorticultureFarming, Ranching, ForestryFarmingHorticulture
298Forestry and LoggingFarming, Ranching, ForestryForestry and Logging
256Ranching and FisheriesFarming, Ranching, ForestryRanching and Fisheries
66FisheriesFarming, Ranching, ForestryRanching and FisheriesFisheries
64RanchingFarming, Ranching, ForestryRanching and FisheriesRanching
43Financial ServicesFinancial Services

129Capital MarketsFinancial ServicesCapital Markets
1720Investment AdviceFinancial ServicesCapital MarketsInvestment Advice
45Investment BankingFinancial ServicesCapital MarketsInvestment Banking
46Investment ManagementFinancial ServicesCapital MarketsInvestment Management
1713Securities and Commodity ExchangesFinancial ServicesCapital MarketsSecurities and Commodity Exchanges
106Venture Capital and Private Equity PrincipalsFinancial ServicesCapital MarketsVenture Capital and Private Equity Principals
1673Credit IntermediationFinancial ServicesCredit Intermediation
41BankingFinancial ServicesCredit IntermediationBanking
141International Trade and DevelopmentFinancial ServicesCredit IntermediationInternational Trade and Development
1696Loan BrokersFinancial ServicesCredit IntermediationLoan Brokers
1678Savings InstitutionsFinancial ServicesCredit IntermediationSavings Institutions
1742Funds and TrustsFinancial ServicesFunds and Trusts
1743Insurance and Employee Benefit FundsFinancial ServicesFunds and TrustsInsurance and Employee Benefit Funds
1745Pension FundsFinancial ServicesFunds and TrustsPension Funds
1750Trusts and EstatesFinancial ServicesFunds and TrustsTrusts and Estates
42InsuranceFinancial ServicesInsurance
1738Claims Adjusting, Actuarial ServicesFinancial ServicesInsuranceClaims Adjusting, Actuarial Services
1737Insurance Agencies and BrokeragesFinancial ServicesInsuranceInsurance Agencies and Brokerages
1725Insurance CarriersFinancial ServicesInsuranceInsurance Carriers
75Government AdministrationGovernment Administration

73Administration of JusticeGovernment AdministrationAdministration of Justice
3068Correctional InstitutionsGovernment AdministrationAdministration of JusticeCorrectional Institutions
3065Courts of LawGovernment AdministrationAdministration of JusticeCourts of Law
3070Fire ProtectionGovernment AdministrationAdministration of JusticeFire Protection
77Law EnforcementGovernment AdministrationAdministration of JusticeLaw Enforcement
78Public SafetyGovernment AdministrationAdministration of JusticePublic Safety
2375Economic ProgramsGovernment AdministrationEconomic Programs
3085Transportation ProgramsGovernment AdministrationEconomic ProgramsTransportation Programs
3086Utilities AdministrationGovernment AdministrationEconomic ProgramsUtilities Administration
388Environmental Quality ProgramsGovernment AdministrationEnvironmental Quality Programs
2366Air, Water, and Waste Program ManagementGovernment AdministrationEnvironmental Quality ProgramsAir, Water, and Waste Program Management
2368Conservation ProgramsGovernment AdministrationEnvironmental Quality ProgramsConservation Programs
2353Health and Human ServicesGovernment AdministrationHealth and Human Services
69Education Administration ProgramsGovernment AdministrationHealth and Human ServicesEducation Administration Programs
2360Public Assistance ProgramsGovernment AdministrationHealth and Human ServicesPublic Assistance Programs
2358Public HealthGovernment AdministrationHealth and Human ServicesPublic Health
2369Housing and Community DevelopmentGovernment AdministrationHousing and Community Development
2374Community Development and Urban PlanningGovernment AdministrationHousing and Community DevelopmentCommunity Development and Urban Planning
3081Housing ProgramsGovernment AdministrationHousing and Community DevelopmentHousing Programs
2391Military and International AffairsGovernment AdministrationMilitary and International Affairs
71Armed ForcesGovernment AdministrationMilitary and International AffairsArmed Forces
74International AffairsGovernment AdministrationMilitary and International AffairsInternational Affairs
79Public Policy OfficesGovernment AdministrationPublic Policy Offices
76Executive OfficesGovernment AdministrationPublic Policy OfficesExecutive Offices
72Legislative OfficesGovernment AdministrationPublic Policy OfficesLegislative Offices
3089Space Research and TechnologyGovernment AdministrationSpace Research and Technology
1905Holding CompaniesHolding Companies

14Hospitals and Health CareHospitals and Health Care

2115Community ServicesHospitals and Health CareCommunity Services
2112Services for the Elderly and DisabledHospitals and Health CareCommunity ServicesServices for the Elderly and Disabled
2081HospitalsHospitals and Health CareHospitals
88Individual and Family ServicesHospitals and Health CareIndividual and Family Services
2128Child Day Care ServicesHospitals and Health CareIndividual and Family ServicesChild Day Care Services
2122Emergency and Relief ServicesHospitals and Health CareIndividual and Family ServicesEmergency and Relief Services
2125Vocational Rehabilitation ServicesHospitals and Health CareIndividual and Family ServicesVocational Rehabilitation Services
13Medical PracticesHospitals and Health CareMedical Practices
125Alternative MedicineHospitals and Health CareMedical PracticesAlternative Medicine
2077Ambulance ServicesHospitals and Health CareMedical PracticesAmbulance Services
2048ChiropractorsHospitals and Health CareMedical PracticesChiropractors
2045DentistsHospitals and Health CareMedical PracticesDentists
2060Family Planning CentersHospitals and Health CareMedical PracticesFamily Planning Centers
2074Home Health Care ServicesHospitals and Health CareMedical PracticesHome Health Care Services
2069Medical and Diagnostic LaboratoriesHospitals and Health CareMedical PracticesMedical and Diagnostic Laboratories
139Mental Health CareHospitals and Health CareMedical PracticesMental Health Care
2050OptometristsHospitals and Health CareMedical PracticesOptometrists
2063Outpatient Care CentersHospitals and Health CareMedical PracticesOutpatient Care Centers
2054Physical, Occupational and Speech TherapistsHospitals and Health CareMedical PracticesPhysical, Occupational and Speech Therapists
2040PhysiciansHospitals and Health CareMedical PracticesPhysicians
2091Nursing Homes and Residential Care FacilitiesHospitals and Health CareNursing Homes and Residential Care Facilities
25ManufacturingManufacturing

598Apparel ManufacturingManufacturingApparel Manufacturing
615Fashion Accessories ManufacturingManufacturingApparel ManufacturingFashion Accessories Manufacturing
112Appliances, Electrical, and Electronics ManufacturingManufacturingAppliances, Electrical, and Electronics Manufacturing
998Electric Lighting Equipment ManufacturingManufacturingAppliances, Electrical, and Electronics ManufacturingElectric Lighting Equipment Manufacturing
2468Electrical Equipment ManufacturingManufacturingAppliances, Electrical, and Electronics ManufacturingElectrical Equipment Manufacturing
1005Household Appliance ManufacturingManufacturingAppliances, Electrical, and Electronics ManufacturingHousehold Appliance Manufacturing
54Chemical ManufacturingManufacturingChemical Manufacturing
709Agricultural Chemical ManufacturingManufacturingChemical ManufacturingAgricultural Chemical Manufacturing
703Artificial Rubber and Synthetic Fiber ManufacturingManufacturingChemical ManufacturingArtificial Rubber and Synthetic Fiber Manufacturing
690Chemical Raw Materials ManufacturingManufacturingChemical ManufacturingChemical Raw Materials Manufacturing
722Paint, Coating, and Adhesive ManufacturingManufacturingChemical ManufacturingPaint, Coating, and Adhesive Manufacturing
18Personal Care Product ManufacturingManufacturingChemical ManufacturingPersonal Care Product Manufacturing
15Pharmaceutical ManufacturingManufacturingChemical ManufacturingPharmaceutical Manufacturing
727Soap and Cleaning Product ManufacturingManufacturingChemical ManufacturingSoap and Cleaning Product Manufacturing
24Computers and Electronics ManufacturingManufacturingComputers and Electronics Manufacturing
973Audio and Video Equipment ManufacturingManufacturingComputers and Electronics ManufacturingAudio and Video Equipment Manufacturing
964Communications Equipment ManufacturingManufacturingComputers and Electronics ManufacturingCommunications Equipment Manufacturing
3Computer Hardware ManufacturingManufacturingComputers and Electronics ManufacturingComputer Hardware Manufacturing
994Magnetic and Optical Media ManufacturingManufacturingComputers and Electronics ManufacturingMagnetic and Optical Media Manufacturing
983Measuring and Control Instrument ManufacturingManufacturingComputers and Electronics ManufacturingMeasuring and Control Instrument Manufacturing
7Semiconductor ManufacturingManufacturingComputers and Electronics ManufacturingSemiconductor Manufacturing
144Renewable Energy Semiconductor ManufacturingManufacturingComputers and Electronics ManufacturingSemiconductor Manufacturing
840Fabricated Metal ProductsManufacturingFabricated Metal Products
852Architectural and Structural Metal ManufacturingManufacturingFabricated Metal ProductsArchitectural and Structural Metal Manufacturing
861Boilers, Tanks, and Shipping Container ManufacturingManufacturingFabricated Metal ProductsBoilers, Tanks, and Shipping Container Manufacturing
871Construction Hardware ManufacturingManufacturingFabricated Metal ProductsConstruction Hardware Manufacturing
849Cutlery and Handtool ManufacturingManufacturingFabricated Metal ProductsCutlery and Handtool Manufacturing
883Metal TreatmentsManufacturingFabricated Metal ProductsMetal Treatments
887Metal Valve, Ball, and Roller ManufacturingManufacturingFabricated Metal ProductsMetal Valve, Ball, and Roller Manufacturing
873Spring and Wire Product ManufacturingManufacturingFabricated Metal ProductsSpring and Wire Product Manufacturing
876Turned Products and Fastener ManufacturingManufacturingFabricated Metal ProductsTurned Products and Fastener Manufacturing
23Food and Beverage ManufacturingManufacturingFood and Beverage Manufacturing
562BreweriesManufacturingFood and Beverage ManufacturingBreweries
2500WineriesManufacturingFood and Beverage ManufacturingWineries
481Animal Feed ManufacturingManufacturingFood and Beverage ManufacturingAnimal Feed Manufacturing
529Baked Goods ManufacturingManufacturingFood and Beverage ManufacturingBaked Goods Manufacturing
142Beverage ManufacturingManufacturingFood and Beverage ManufacturingBeverage Manufacturing
65Dairy Product ManufacturingManufacturingFood and Beverage ManufacturingDairy Product Manufacturing
564DistilleriesManufacturingFood and Beverage ManufacturingDistilleries
504Fruit and Vegetable Preserves ManufacturingManufacturingFood and Beverage ManufacturingFruit and Vegetable Preserves Manufacturing
521Meat Products ManufacturingManufacturingFood and Beverage ManufacturingMeat Products Manufacturing
528Seafood Product ManufacturingManufacturingFood and Beverage ManufacturingSeafood Product Manufacturing
495Sugar and Confectionery Product ManufacturingManufacturingFood and Beverage ManufacturingSugar and Confectionery Product Manufacturing
26Furniture and Home Furnishings ManufacturingManufacturingFurniture and Home Furnishings Manufacturing
1080Household and Institutional Furniture ManufacturingManufacturingFurniture and Home Furnishings ManufacturingHousehold and Institutional Furniture Manufacturing
1095Mattress and Blinds ManufacturingManufacturingFurniture and Home Furnishings ManufacturingMattress and Blinds Manufacturing
1090Office Furniture and Fixtures ManufacturingManufacturingFurniture and Home Furnishings ManufacturingOffice Furniture and Fixtures Manufacturing
145Glass, Ceramics and Concrete ManufacturingManufacturingGlass, Ceramics and Concrete Manufacturing
799Abrasives and Nonmetallic Minerals ManufacturingManufacturingGlass, Ceramics and Concrete ManufacturingAbrasives and Nonmetallic Minerals Manufacturing
773Clay and Refractory Products ManufacturingManufacturingGlass, Ceramics and Concrete ManufacturingClay and Refractory Products Manufacturing
779Glass Product ManufacturingManufacturingGlass, Ceramics and Concrete ManufacturingGlass Product Manufacturing
794Lime and Gypsum Products ManufacturingManufacturingGlass, Ceramics and Concrete ManufacturingLime and Gypsum Products Manufacturing
616Leather Product ManufacturingManufacturingLeather Product Manufacturing
622Footwear ManufacturingManufacturingLeather Product ManufacturingFootwear Manufacturing
625Women’s Handbag ManufacturingManufacturingLeather Product ManufacturingWomen’s Handbag Manufacturing
55Machinery ManufacturingManufacturingMachinery Manufacturing
901Agriculture, Construction, Mining Machinery ManufacturingManufacturingMachinery ManufacturingAgriculture, Construction, Mining Machinery Manufacturing
147Automation Machinery ManufacturingManufacturingMachinery ManufacturingAutomation Machinery Manufacturing
918Commercial and Service Industry Machinery ManufacturingManufacturingMachinery ManufacturingCommercial and Service Industry Machinery Manufacturing
935Engines and Power Transmission Equipment ManufacturingManufacturingMachinery ManufacturingEngines and Power Transmission Equipment Manufacturing
3241Renewable Energy Equipment ManufacturingManufacturingMachinery ManufacturingEngines and Power Transmission Equipment Manufacturing
923HVAC and Refrigeration Equipment ManufacturingManufacturingMachinery ManufacturingHVAC and Refrigeration Equipment Manufacturing
135Industrial Machinery ManufacturingManufacturingMachinery ManufacturingIndustrial Machinery Manufacturing
928Metalworking Machinery ManufacturingManufacturingMachinery ManufacturingMetalworking Machinery Manufacturing
17Medical Equipment ManufacturingManufacturingMedical Equipment Manufacturing
679Oil and Coal Product ManufacturingManufacturingOil and Coal Product Manufacturing
61Paper and Forest Product ManufacturingManufacturingPaper and Forest Product Manufacturing
743Plastics and Rubber Product ManufacturingManufacturingPlastics and Rubber Product Manufacturing
146Packaging and Containers ManufacturingManufacturingPlastics and Rubber Product ManufacturingPackaging and Containers Manufacturing
117Plastics ManufacturingManufacturingPlastics and Rubber Product ManufacturingPlastics Manufacturing
763Rubber Products ManufacturingManufacturingPlastics and Rubber Product ManufacturingRubber Products Manufacturing
807Primary Metal ManufacturingManufacturingPrimary Metal Manufacturing
83Printing ServicesManufacturingPrinting Services
20Sporting Goods ManufacturingManufacturingSporting Goods Manufacturing
60Textile ManufacturingManufacturingTextile Manufacturing
21Tobacco ManufacturingManufacturingTobacco Manufacturing
1029Transportation Equipment ManufacturingManufacturingTransportation Equipment Manufacturing
52Aviation and Aerospace Component ManufacturingManufacturingTransportation Equipment ManufacturingAviation and Aerospace Component Manufacturing
1Defense and Space ManufacturingManufacturingTransportation Equipment ManufacturingDefense and Space Manufacturing
53Motor Vehicle ManufacturingManufacturingTransportation Equipment ManufacturingMotor Vehicle Manufacturing
1042Motor Vehicle Parts ManufacturingManufacturingTransportation Equipment ManufacturingMotor Vehicle Parts Manufacturing
62Railroad Equipment ManufacturingManufacturingTransportation Equipment ManufacturingRailroad Equipment Manufacturing
58ShipbuildingManufacturingTransportation Equipment ManufacturingShipbuilding
784Wood Product ManufacturingManufacturingWood Product Manufacturing
332Oil, Gas, and MiningOil, Gas, and Mining

56MiningOil, Gas, and MiningMining
341Coal MiningOil, Gas, and MiningMiningCoal Mining
345Metal Ore MiningOil, Gas, and MiningMiningMetal Ore Mining
356Nonmetallic Mineral MiningOil, Gas, and MiningMiningNonmetallic Mineral Mining
57Oil and GasOil, Gas, and MiningOil and Gas
3096Natural Gas ExtractionOil, Gas, and MiningOil and GasNatural Gas Extraction
3095Oil ExtractionOil, Gas, and MiningOil and GasOil Extraction
1810Professional ServicesProfessional Services

47AccountingProfessional ServicesAccounting
80Advertising ServicesProfessional ServicesAdvertising Services
148Government Relations ServicesProfessional ServicesAdvertising ServicesGovernment Relations Services
98Public Relations and Communications ServicesProfessional ServicesAdvertising ServicesPublic Relations and Communications Services
97Market ResearchProfessional ServicesAdvertising ServicesMarket Research
50Architecture and PlanningProfessional ServicesArchitecture and Planning
11Business Consulting and ServicesProfessional ServicesBusiness Consulting and Services
86Environmental ServicesProfessional ServicesBusiness Consulting and ServicesEnvironmental Services
137Human Resources ServicesProfessional ServicesBusiness Consulting and ServicesHuman Resources Services
1862Marketing ServicesProfessional ServicesBusiness Consulting and ServicesMarketing Services
2401Operations ConsultingProfessional ServicesBusiness Consulting and ServicesOperations Consulting
123Outsourcing and Offshoring ConsultingProfessional ServicesBusiness Consulting and ServicesOutsourcing and Offshoring Consulting
102Strategic Management ServicesProfessional ServicesBusiness Consulting and ServicesStrategic Management Services
99Design ServicesProfessional ServicesDesign Services
140Graphic DesignProfessional ServicesDesign ServicesGraphic Design
3126Interior DesignProfessional ServicesDesign ServicesInterior Design
3242Engineering ServicesProfessional ServicesEngineering Services
96IT Services and IT ConsultingProfessional ServicesIT Services and IT Consulting
118Computer and Network SecurityProfessional ServicesIT Services and IT ConsultingComputer and Network Security
3102IT System Custom Software DevelopmentProfessional ServicesIT Services and IT ConsultingIT System Custom Software Development
3106IT System Data ServicesProfessional ServicesIT Services and IT ConsultingIT System Data Services
1855IT System Design ServicesProfessional ServicesIT Services and IT ConsultingIT System Design Services
3104IT System Installation and DisposalProfessional ServicesIT Services and IT ConsultingIT System Installation and Disposal
3103IT System Operations and MaintenanceProfessional ServicesIT Services and IT ConsultingIT System Operations and Maintenance
3107IT System Testing and EvaluationProfessional ServicesIT Services and IT ConsultingIT System Testing and Evaluation
3105IT System Training and SupportProfessional ServicesIT Services and IT ConsultingIT System Training and Support
10Legal ServicesProfessional ServicesLegal Services
120Alternative Dispute ResolutionProfessional ServicesLegal ServicesAlternative Dispute Resolution
9Law PracticeProfessional ServicesLegal ServicesLaw Practice
136PhotographyProfessional ServicesPhotography
70Research ServicesProfessional ServicesResearch Services
114Nanotechnology ResearchProfessional ServicesResearch ServicesNanotechnology Research
130Think TanksProfessional ServicesResearch ServicesThink Tanks
3243Services for Renewable EnergyProfessional ServicesServices for Renewable Energy
16Veterinary ServicesProfessional ServicesVeterinary Services
1757Real Estate and Equipment Rental ServicesReal Estate and Equipment Rental Services

1779Equipment Rental ServicesReal Estate and Equipment Rental ServicesEquipment Rental Services
1798Commercial and Industrial Equipment RentalReal Estate and Equipment Rental ServicesEquipment Rental ServicesCommercial and Industrial Equipment Rental
1786Consumer Goods RentalReal Estate and Equipment Rental ServicesEquipment Rental ServicesConsumer Goods Rental
128Leasing Non-residential Real EstateReal Estate and Equipment Rental ServicesLeasing Real EstateLeasing Non-residential Real Estate
1770Leasing Real Estate Agents and BrokersReal Estate and Equipment Rental ServicesLeasing Real EstateLeasing Real Estate Agents and Brokers
1759Leasing Residential Real EstateReal Estate and Equipment Rental ServicesLeasing Real EstateLeasing Residential Real Estate
44Real EstateReal Estate and Equipment Rental ServicesReal Estate
27RetailRetail

1339Food and Beverage RetailRetailFood and Beverage Retail
22Retail GroceriesRetailFood and Beverage RetailRetail Groceries
1445Online and Mail Order RetailRetailOnline and Mail Order Retail
19Retail Apparel and FashionRetailRetail Apparel and Fashion
1319Retail Appliances, Electrical, and Electronic EquipmentRetailRetail Appliances, Electrical, and Electronic Equipment
3186Retail Art DealersRetailRetail Art Dealers
111Retail Art SuppliesRetailRetail Art Supplies
1409Retail Books and Printed NewsRetailRetail Books and Printed News
1324Retail Building Materials and Garden EquipmentRetailRetail Building Materials and Garden Equipment
1423Retail FloristsRetailRetail Florists
1309Retail Furniture and Home FurnishingsRetailRetail Furniture and Home Furnishings
1370Retail GasolineRetailRetail Gasoline
1359Retail Health and Personal Care ProductsRetailRetail Health and Personal Care Products
143Retail Luxury Goods and JewelryRetailRetail Luxury Goods and Jewelry
1292Retail Motor VehiclesRetailRetail Motor Vehicles
1407Retail Musical InstrumentsRetailRetail Musical Instruments
138Retail Office EquipmentRetailRetail Office Equipment
1424Retail Office Supplies and GiftsRetailRetail Office Supplies and Gifts
1431Retail Recyclable Materials & Used MerchandiseRetailRetail Recyclable Materials & Used Merchandise
1594Technology, Information and MediaTechnology, Information and Media

3133Media and TelecommunicationsTechnology, Information and MediaMedia and Telecommunications
82Book and Periodical PublishingTechnology, Information and MediaMedia and TelecommunicationsBook and Periodical Publishing
1602Book PublishingTechnology, Information and MediaMedia and TelecommunicationsBook and Periodical Publishing
81Newspaper PublishingTechnology, Information and MediaMedia and TelecommunicationsBook and Periodical Publishing
1600Periodical PublishingTechnology, Information and MediaMedia and TelecommunicationsBook and Periodical Publishing
36Broadcast Media Production and DistributionTechnology, Information and MediaMedia and TelecommunicationsBroadcast Media Production and Distribution
1641Cable and Satellite ProgrammingTechnology, Information and MediaMedia and TelecommunicationsBroadcast Media Production and Distribution
1633Radio and Television BroadcastingTechnology, Information and MediaMedia and TelecommunicationsBroadcast Media Production and Distribution
35Movies, Videos and SoundTechnology, Information and MediaMedia and TelecommunicationsMovies, Videos and Sound
127Animation and Post-productionTechnology, Information and MediaMedia and TelecommunicationsMovies, Videos and Sound
126Media ProductionTechnology, Information and MediaMedia and TelecommunicationsMovies, Videos and Sound
1611Movies and Sound RecordingTechnology, Information and MediaMedia and TelecommunicationsMovies, Videos and Sound
1623Sound RecordingTechnology, Information and MediaMedia and TelecommunicationsMovies, Videos and Sound
1625Sheet Music PublishingTechnology, Information and MediaMedia and TelecommunicationsMovies, Videos and Sound
8TelecommunicationsTechnology, Information and MediaMedia and TelecommunicationsTelecommunications
1649Satellite TelecommunicationsTechnology, Information and MediaMedia and TelecommunicationsTelecommunications
1644Telecommunications CarriersTechnology, Information and MediaMedia and TelecommunicationsTelecommunications
119Wireless ServicesTechnology, Information and MediaMedia and TelecommunicationsTelecommunications
6Technology, Information and InternetTechnology, Information and MediaTechnology, Information and Internet
2458Data Infrastructure and AnalyticsTechnology, Information and MediaTechnology, Information and InternetData Infrastructure and Analytics
3134Blockchain ServicesTechnology, Information and MediaTechnology, Information and InternetData Infrastructure and Analytics
3128Business Intelligence PlatformsTechnology, Information and MediaTechnology, Information and InternetData Infrastructure and Analytics
84Information ServicesTechnology, Information and MediaTechnology, Information and InternetInformation Services
3132Internet PublishingTechnology, Information and MediaTechnology, Information and InternetInformation Services
3129Business ContentTechnology, Information and MediaTechnology, Information and InternetInformation Services
113Online Audio and Video MediaTechnology, Information and MediaTechnology, Information and InternetInformation Services
3124Internet NewsTechnology, Information and MediaTechnology, Information and InternetInformation Services
85LibrariesTechnology, Information and MediaTechnology, Information and InternetInformation Services
3125BlogsTechnology, Information and MediaTechnology, Information and InternetInformation Services
1285Internet Marketplace PlatformsTechnology, Information and MediaTechnology, Information and InternetInternet Marketplace Platforms
3127Social Networking PlatformsTechnology, Information and MediaTechnology, Information and InternetSocial Networking Platforms
4Software DevelopmentTechnology, Information and MediaTechnology, Information and InternetSoftware Development
109Computer GamesTechnology, Information and MediaTechnology, Information and InternetSoftware Development
3131Mobile Gaming AppsTechnology, Information and MediaTechnology, Information and InternetSoftware Development
5Computer Networking ProductsTechnology, Information and MediaTechnology, Information and InternetSoftware Development
3101Desktop Computing Software ProductsTechnology, Information and MediaTechnology, Information and InternetSoftware Development
3099Embedded Software ProductsTechnology, Information and MediaTechnology, Information and InternetSoftware Development
3100Mobile Computing Software ProductsTechnology, Information and MediaTechnology, Information and InternetSoftware Development
3130Data Security Software ProductsTechnology, Information and MediaTechnology, Information, and InternetSoftware Development
116Transportation, Logistics, Supply Chain and StorageTransportation, Logistics, Supply Chain and Storage

94Airlines and AviationTransportation, Logistics, Supply Chain and StorageAirlines and Aviation
87Freight and Package TransportationTransportation, Logistics, Supply Chain and StorageFreight and Package Transportation
1495Ground Passenger TransportationTransportation, Logistics, Supply Chain and StorageGround Passenger Transportation
1504Interurban and Rural Bus ServicesTransportation, Logistics, Supply Chain and StorageGround Passenger TransportationInterurban and Rural Bus Services
1512School and Employee Bus ServicesTransportation, Logistics, Supply Chain and StorageGround Passenger TransportationSchool and Employee Bus Services
1517Shuttles and Special Needs Transportation ServicesTransportation, Logistics, Supply Chain and StorageGround Passenger TransportationShuttles and Special Needs Transportation Services
1532Sightseeing TransportationTransportation, Logistics, Supply Chain and StorageGround Passenger TransportationSightseeing Transportation
1505Taxi and Limousine ServicesTransportation, Logistics, Supply Chain and StorageGround Passenger TransportationTaxi and Limousine Services
1497Urban Transit ServicesTransportation, Logistics, Supply Chain and StorageGround Passenger TransportationUrban Transit Services
95Maritime TransportationTransportation, Logistics, Supply Chain and StorageMaritime Transportation
1520Pipeline TransportationTransportation, Logistics, Supply Chain and StoragePipeline Transportation
1573Postal ServicesTransportation, Logistics, Supply Chain and StoragePostal Services
1481Rail TransportationTransportation, Logistics, Supply Chain and StorageRail Transportation
92Truck TransportationTransportation, Logistics, Supply Chain and StorageTruck Transportation
93Warehousing and StorageTransportation, Logistics, Supply Chain and StorageWarehousing and Storage
59UtilitiesUtilities

383Electric Power GenerationUtilitiesElectric Power Generation
385Fossil Fuel Electric Power GenerationUtilitiesElectric Power GenerationFossil Fuel Electric Power Generation
386Nuclear Electric Power GenerationUtilitiesElectric Power GenerationNuclear Electric Power Generation
3240Renewable Energy Power GenerationUtilitiesElectric Power GenerationRenewable Energy Power Generation
390Biomass Electric Power GenerationUtilitiesElectric Power GenerationRenewable Energy Power Generation
389Geothermal Electric Power GenerationUtilitiesElectric Power GenerationRenewable Energy Power Generation
384Hydroelectric Power GenerationUtilitiesElectric Power GenerationRenewable Energy Power Generation
387Solar Electric Power GenerationUtilitiesElectric Power GenerationRenewable Energy Power Generation
2489Wind Electric Power GenerationUtilitiesElectric Power GenerationRenewable Energy Power Generation
382Electric Power Transmission, Control, and DistributionUtilitiesElectric Power Transmission, Control, and Distribution
397Natural Gas DistributionUtilitiesNatural Gas Distribution
398Water, Waste, Steam, and Air Conditioning ServicesUtilitiesWater, Waste, Steam, and Air Conditioning Services
404Steam and Air-Conditioning SupplyUtilitiesWater, Waste, Steam, and Air Conditioning ServicesSteam and Air-Conditioning Supply
1981Waste CollectionUtilitiesWater, Waste, Steam, and Air Conditioning ServicesWaste Collection
1986Waste Treatment and DisposalUtilitiesWater, Waste, Steam, and Air Conditioning ServicesWaste Treatment and Disposal
400Water Supply and Irrigation SystemsUtilitiesWater, Waste, Steam, and Air Conditioning ServicesWater Supply and Irrigation Systems
133WholesaleWholesale

1267Wholesale Alcoholic BeveragesWholesaleWholesale Alcoholic Beverages
1222Wholesale Apparel and Sewing SuppliesWholesaleWholesale Apparel and Sewing Supplies
1171Wholesale Appliances, Electrical, and ElectronicsWholesaleWholesale Appliances, Electrical, and Electronics
49Wholesale Building MaterialsWholesaleWholesale Building Materials
1257Wholesale Chemical and Allied ProductsWholesaleWholesale Chemical and Allied Products
1157Wholesale Computer EquipmentWholesaleWholesale Computer Equipment
1221Wholesale Drugs and SundriesWholesaleWholesale Drugs and Sundries
1231Wholesale Food and BeverageWholesaleWholesale Food and Beverage
1230Wholesale FootwearWholesaleWholesale Footwear
1137Wholesale Furniture and Home FurnishingsWholesaleWholesale Furniture and Home Furnishings
1178Wholesale Hardware, Plumbing, Heating EquipmentWholesaleWholesale Hardware, Plumbing, Heating Equipment
134Wholesale Import and ExportWholesaleWholesale Import and Export
1208Wholesale Luxury Goods and JewelryWholesaleWholesale Luxury Goods and Jewelry
1187Wholesale MachineryWholesaleWholesale Machinery
1166Wholesale Metals and MineralsWholesaleWholesale Metals and Minerals
1128Wholesale Motor Vehicles and PartsWholesaleWholesale Motor Vehicles and Parts
1212Wholesale Paper ProductsWholesaleWholesale Paper Products
1262Wholesale Petroleum and Petroleum ProductsWholesaleWholesale Petroleum and Petroleum Products
1153Wholesale Photography Equipment and SuppliesWholesaleWholesale Photography Equipment and Supplies
1250Wholesale Raw Farm ProductsWholesaleWholesale Raw Farm Products
1206Wholesale Recyclable MaterialsWholesaleWholesale Recyclable Materials
3187Defense & SpaceDEPRECATED

3188VeterinaryDEPRECATED

3189CosmeticsDEPRECATED

3190Apparel and FashionDEPRECATED

3191Consumer ElectronicsDEPRECATED

3192Consumer GoodsDEPRECATED

3193FurnitureDEPRECATED

3194Leisure, Travel & TourismDEPRECATED

3196Fine ArtDEPRECATED

3197Building MaterialsDEPRECATED

3198AutomotiveDEPRECATED

3199DairyDEPRECATED

3200Education ManagementDEPRECATED

3201Public PolicyDEPRECATED

3202Non-profit Organization ManagementDEPRECATED

3203Program DevelopmentDEPRECATED

3204Arts and CraftsDEPRECATED

3205MusicDEPRECATED

3206Outsourcing/OffshoringDEPRECATED

3207Health, Wellness and FitnessDEPRECATED

3208E-LearningDEPRECATED

3209Import and ExportDEPRECATED

3210Human ResourcesDEPRECATED

3211Business Supplies and EquipmentDEPRECATED

3213Renewables & EnvironmentDEPRECATED

3214Online MediaDEPRECATED

3215AnimationDEPRECATED

3216Industrial AutomationDEPRECATED

3217Medical DevicesDEPRECATED

3218SemiconductorsDEPRECATED

3219Commercial Real EstateDEPRECATED

3220Wine and SpiritsDEPRECATED

3221Mechanical or Industrial EngineeringDEPRECATED

3222Transportation/Trucking/RailroadDEPRECATED

3223Packaging and ContainersDEPRECATED

3224Food ProductionDEPRECATED

3225Sporting GoodsDEPRECATED

3226Aviation & AerospaceDEPRECATED

3227DesignDEPRECATED

3228TobaccoDEPRECATED

3229WarehousingDEPRECATED

3230PhilanthropyDEPRECATED

3231Information Technology and ServicesDEPRECATED

3232Government RelationsDEPRECATED

3233ResearchDEPRECATED

3234Computer NetworkingDEPRECATED

3235Computer HardwareDEPRECATED

3236MaritimeDEPRECATED

3237EntertainmentDEPRECATED

3238BiotechnologyDEPRECATED

3239Paper and Forest ProductsDEPRECATED

A note on Linkedin Ads industry targeting

While it’s useful to understand how company industry labels roll up into industry segments, it also shows how vague industry targeting is on Linkedin. 

This lack of control is great for Linkedin—less granularity means more ad spend to find your target audience, but it’s bad news for advertisers. Most businesses are far better off identifying a handful of specific NAICS codes that cover their specific market than using Linkedin targeting to spray and pray. 

Advertisers will always run into the tradeoff between coverage and assurance that you’ll hit your entire market vs. wasting ad dollars targeting companies who aren’t in a market.

Want to optimize your account targeting on Linkedin Ads? Contact me on Linkedin to start building highly targeted account lists from a database of over 11M companies with a wide variety of firmographic data.

A better way to LISTAGG in Google Sheets (with Pivot Tables)

The SQL LISTAGG function in Snowflake or Redshift (or STRING_AGG in BigQuery) are aggregation functions that condense textual data into easy-to-view reports. Similar to other aggregation functions (like SUM or MAX), these functions aggregate data according to the column names in a GROUP BY clause. 

This is a common pattern for rolling up a column with a large variety of values into a column with less variety.

You might like: From Spreadsheets to SQL: Step one towards a minimum viable data stack

How LISTAGG works in SQL

Here’s an example of what it would look like in BigQuery. 

Let’s say we have a table that has cities and companies, and we want to see a list of companies that are located in each city. 

In SQL, we would write something like this:

SELECT
 city,
 STRING_AGG(company,', ') AS cityagg
FROM my_table
GROUP BY city;

And then we would get a result that looks like this.

How to LISTAGG in Google Sheets with a Pivot Table

When I was looking for a solution to this, I found a bunch of solutions that required some pretty hairy amalgamations of the Google Sheets functions like QUERY, FILTER, and JOIN.  These all felt overly complex to me because pivot tables provide the same aggregation capability that you’d find by using a GROUP BY clause in SQL. 

The problem is that there is no native LISTAGG function in Google Sheets. Or so I thought!

In fact, the JOIN function does exactly what the LISTAGG function does in SQL—it just does it differently.  The JOIN function takes a one-dimensional array and glues them all together with a chosen separator. This is just the same as LISTAGG, but it only works within groups.

The question is, then, how do you get the JOIN function to behave like a LISTAGG function?

It turns out to be quite simple! The answer is Calculated Fields in pivot tables.

Performing a LISTAGG-like aggregation in Google Sheets is easy—just three steps: 

  1. Create a pivot table 
  2. Choose one or more fields to pivot into Rows
  3. For the aggregation Values, choose Calculated Field and use the following formula:
=JOIN(“, “, your_column_name)

There are a few things to keep in mind: 

  • Be sure to “Summarize by”  “Custom, “ or else you’ll get a value of 0 for your aggregations.
  • Use lowercase column headings without spaces (or use underscores_like_this) for column names in your data. This makes it easier to set up Calculated Fields because the column reference in the Calculated Field must exactly match the column name in your data. 
  • Uncheck “Show Totals” because this will list all possible values in the LISTAGG’ed column. That could end up being one really long value!

That’s it! Let me know in the comments if anything is unclear.  Happy aggregating!

Linkedin Ads Company Targeting Options and Statistics for 2023

Linkedin Ads Company targeting

Company targeting is one of the most unique advertising features of Linkedin Ads. No other advertising platform has access to such an extensive and up-to-date employer-employee data set. For that reason, no other platform can natively support targeting audiences based on their employer. This makes Linkedin Ads an indispensable platform for B2B marketers and the only platform designed for ABM (Account-Based Marketing).

It’s a powerful feature, but you have to be careful! Linkedin Ads Manager allows you to target companies based on company segments that are frequently less granular than you’d like. Additionally, there is no way of knowing precisely what companies you’re targeting. There is nothing worse than setting up targeting a lead gen campaign with the expectation that you’ll generate leads from a specific set of companies and seeing the leads come in from anything other than what you’d expect!

It’s also important to know exactly what your chosen targeting criteria are actually targeting. For instance, “Company Growth Rate” refers to the growth in the number of employees who claim that the company is an employer on Linkedin, not revenue growth. (Though it’d be nice!) But don’t worry. That’s why I put this page together for reference.

Want better company targeting on Linkedin?

Connect with me on Linkedin if you’d like to create a transparent and targeted named account list from a database of nearly 12 million companies worldwide. Get company lists with corresponding firmographic data starting at $100.

Linkedin Ads Company Targeting Options List

Learn about what each targeting criterion means and see the complete list of options listed below: 

Company Names

Reach members based on the organization(s) a member has listed as their employer(s). Examples of this would be names like Stripe, Walmart, or Tiffanys.

Company Revenue

Reach members based on their company’s earned revenue (in USD) in the previous year.

Company Revenue RangeLinkedin Members
Under $1M44,000,000+
$1M-$10M38,000,000+
$10M-$100M40,000,000+
$100M-$1B49,000,000+
$1B+94,000,000+

Company Size

Reach members based on the size of the organization they work for. Company size is determined by the number of employees listed on the organization’s Company Page.

Company SizeLinkedin Members
Myself Only6,400,000+
2-10 employees35,000,000+
11-50 employees42,000,000+
51-200 employees37,000,000+
201-500 employees28,000,000+
501-1000 employees21,000,000+
1001-5000 employees47,000,000+
5001-10,000 employees20,000,000+
10,001+ employees86,000,000+

Company Industries

I go into much more detail about LinkedIn industry categories in the complete Linkedin industry list post, but here is the short version…

Reach members within specific industries by using the primary industry of the company the member is employed. Note that these Industry segments are less granular than the category found on a company’s profile page. 

In the example above, Adidas is listed as “Sporting Goods Manufacturing,” whereas its counterpart Nike is listed as “Retail.” This means that Nike would be found in the “Retail” industry category while Adidas would be found in the “Manufacturing” industry category. A little counterintuitive!

Company IndustryLinkedin Members
Accommodation26,000,000+
Administrative and Support Services23,000,000+
Construction26,000,000+
Consumer Services24,000,000+
Education32,000,000+
Entertainment Providers25,000,000+
Farming, Ranching, Forestry5,000,000+
Financial Services44,000,000+
Government Administration39,000,000+
Holding Companies3,500,000+
Hospitals and Health Care36,000,000+
Manufacturing92,000,000+
Oil, Gas, and Mining13,000,000+
Professional Services110,000,000+
Real Estate and Equipment Rental Services12,000,000+
Retail42,000,000+
Technology, Information and Media57,000,000+
Transportation, Logistics, Supply Chain and Storage21,000,000+
Utilities6,400,000+
Wholesale21,000,000+

Company Growth Rate

Reach members based on the year-over-year employee growth rate associated with their company. The growth rate for a company is determined by comparing the recent total employee count vs. one year prior.

Company Employee Growth Rate SegmentLinkedin Members
Negative growth companies42,000,000+
0% – 3% company growth93,000,000+
3% – 10% company growth110,000,000+
10% – 20% company growth62,000,000+
20%+ company growth47,000,000+

Company Connections

Reach the 1st-degree connections of employees at the companies you select. Companies will be available if they have over 500 employees.

Company Follower of

Reach followers of your Company Page. To utilize this facet, your Ads account must be associated with your Company Page.

Company Category

Reach members based on the category their company falls into. Company categories are defined based on publications and industry lists that group companies into specific market segments.

Company CategoryLinkedin Members
Forbes World’s Most Innovative Companies4,000,000+
Fortune 100 Fast Growing Companies (Worldwide)2,200,000+
Fortune 1000 (US only)24,000,000+
Fortune 500 (US only)21,000,000+
Fortune Global 500 (Worldwide)24,000,000+
Linkedin News Editors’ Top Companies (Argentina)2,400,000+
Linkedin News Editors’ Top Companies (Australia)5,300,000+
Linkedin News Editors’ Top Companies (Austria)1,800,000+
Linkedin News Editors’ Top Companies (Belgium)2,000,000+
Linkedin News Editors’ Top Companies (Brazil)4,100,000+
Linkedin News Editors’ Top Companies (Canada)5,300,000+
Linkedin News Editors’ Top Companies (Chile)1,600,000+
Linkedin News Editors’ Top Companies (China)5,100,000+
Linkedin News Editors’ Top Companies (Columbia)1,400,000+
Linkedin News Editors’ Top Companies (Denmark)1,700,000+
Linkedin News Editors’ Top Companies (Egypt)1,900,000+
Linkedin News Editors’ Top Companies (Egypt)1,400,000+
Linkedin News Editors’ Top Companies (France)2,900,000+
Linkedin News Editors’ Top Companies (Germany)4,500,000+
Linkedin News Editors’ Top Companies (India)7,100,000+
Linkedin News Editors’ Top Companies (Indonesia)530,000+
Linkedin News Editors’ Top Companies (Ireland)4,800,000+
Linkedin News Editors’ Top Companies (Israel)2,300,000+
Linkedin News Editors’ Top Companies (Italy)2,800,000+
Linkedin News Editors’ Top Companies (Japan)5,900,000+
Linkedin News Editors’ Top Companies (Kenya)1,300,000+
Linkedin News Editors’ Top Companies (Malaysia)2,500,000+
Linkedin News Editors’ Top Companies (Mexico)6,100,000+
Linkedin News Editors’ Top Companies (Netherlands)3,800,000+
Linkedin News Editors’ Top Companies (Nigeria)2,500,000+
Linkedin News Editors’ Top Companies (Philippines)2,600,000+
Linkedin News Editors’ Top Companies (Poland)5,100,000+
Linkedin News Editors’ Top Companies (Portugal)2,500,000+
Linkedin News Editors’ Top Companies (Qatar)240,000+
Linkedin News Editors’ Top Companies (Saudi Arabia)430,000+
Linkedin News Editors’ Top Companies (Singapore)3,200,000+
Linkedin News Editors’ Top Companies (South Africa)2,700,000+
Linkedin News Editors’ Top Companies (Spain)3,900,000+
Linkedin News Editors’ Top Companies (Sweden)1,100,000+
Linkedin News Editors’ Top Companies (Switzerland)2,200,000+
Linkedin News Editors’ Top Companies (United Arab Emirates)2,400,000+
Linkedin News Editors’ Top Companies (United Kingdom)5,500,000+
Linkedin News Editors’ Top Companies (United States)12,000,000+
Linkedin News Editors’ Top Startups (Argentina)2,500+
Linkedin News Editors’ Top Startups (Australia)17,000+
Linkedin News Editors’ Top Startups (Austria)6,100+
Linkedin News Editors’ Top Startups (Belgium)1,900+
Linkedin News Editors’ Top Startups (Brazil)44,000+
Linkedin News Editors’ Top Startups (Canada)11,000+
Linkedin News Editors’ Top Startups (China)49,000+
Linkedin News Editors’ Top Startups (Egypt)4,200+
Linkedin News Editors’ Top Startups (France)15,000+
Linkedin News Editors’ Top Startups (Germany)16,000+
Linkedin News Editors’ Top Startups (India)95,000+
Linkedin News Editors’ Top Startups (Indonesia)15,000+
Linkedin News Editors’ Top Startups (Ireland)1,700+
Linkedin News Editors’ Top Startups (Israel)4,100+
Linkedin News Editors’ Top Startups (Italy)2,600+
Linkedin News Editors’ Top Startups (Japan)3,700+
Linkedin News Editors’ Top Startups (Mexico)10,000+
Linkedin News Editors’ Top Startups (Netherlands)7,400+
Linkedin News Editors’ Top Startups (New Zealand)1,600+
Linkedin News Editors’ Top Startups (Saudi Arabia)3,000+
Linkedin News Editors’ Top Startups (Singapore)10,000+
Linkedin News Editors’ Top Startups (Spain)4,200+
Linkedin News Editors’ Top Startups (Sweden)6,100+
Linkedin News Editors’ Top Startups (Switzerland)1,600+
Linkedin News Editors’ Top Startups (United Arab Emirates)5,700+
Linkedin News Editors’ Top Startups (United Kingdom)28,000+
Linkedin News Editors’ Top Startups (United States)190,000+
London Stock Exchange (Worldwide)5,500,000+

Getting started with the Company targeting feature in Linkedin Ads Manager

With the “Company” targeting feature, you can ensure that your ads are only shown to users who work at the companies you’ve selected. This targeting method can also be used for exclusions.

How to setup Company targeting in Linkedin Ads

Here’s how to use the “Company” targeting feature:

  • Go to the LinkedIn Ads dashboard and click on the “Create” button to create a new ad campaign.
  • In the “Targeting” section,  choose “Audience Attributes” and select the “Company” tab.
  • Select any combination of the targeting criteria listed above.
  • Click “Save” to apply your targeting and continue setting up your ad campaign.

Ready to get started? Save yourself time and money by building a transparent account list. Contact me on Linkedin to get started. You’ll save a lot of wasted ad spend by starting with a target company upload rather than targeting according to company criteria.

Writing Custom Dimension to Google Analytics from Snowflake DB

Like many data geeks, Google Analytics was the thing that first sparked my curiosity. Last week, Census released our Google Analytics integration. I could write a long list of game-changing applications for creating custom dimensions in Google Analytics from data in a Snoflake data warehouse but I’ve been swirling around one that I find particularly interesting. Here’s what I’m thinking.

Census, as you’d imagine, has a long sales cycle. It’s a freemium product in the middle of multiple stakeholders and touchpoints. It should come as no surprise that it’s easy for us to lose the connection between early marketing efforts and later sales outcomes. Lead scores based on demographics, activity, content consumption, or product usage have been really helpful for us to aggregate signals into a few metrics that show how similar new leads are to past customers.

The problem is that those metrics are only helpful for understanding the funnel after a trial is started. Growth is a process of expanding what works by experimenting and iterating. So to advance our experimentation towards the top of the funnel, we need valid signals early in the journey.

This is finally where Google Analytics comes in. GA is the platform we trust for web analytics and a quick way to understand attribution. But as you know, it was built for ecommerce—not for B2B SaaS. Firing a conversion based on an event gives us a really shallow view of success.

Send data to Google Analytics from Snowflake

With Clearbit Reveal and account-based scoring, we can put score-based thresholds on the traffic coming in (for example high/mid/low score). With reverse ETL data integration for Google Analytics, We can map these thresholds against custom dimensions to measure the volume of high-quality traffic a given channel is bringing in and evaluate channels/costs against traffic quality. Sure the signal is imperfect (all models are) but it’s a lot stronger than the far-less-frequent conversion events. It feels a lot better to me than living and dying by button clicks.

I recently presented on how to get consistent metrics across Google Analytics, your ads platforms, and Hubspot called Marketing as a Data Product: Operational Analytics for Growth which shows how I did this. Check it out!

Yes, you can use Census for free. You can even send custom dimensions from Google Sheets.

Setting up Airbyte ETL: Minimum Viable Data Stack Part II

In the first post in the Minimum Viable Data Stack series, we set up a process to start using SQL to analyze CSV data. We set up a Postgres database instance on a Mac personal computer, uploaded a CSV file, and wrote a query to analyze the data in the CSV file. 

That’s a good start! You could follow that pattern to do some interesting analysis on CSV files that wouldn’t fit in Excel or Google Sheets. But that pattern is slow and requires you to continually upload new data as new data becomes available. 

This post will demonstrate how to connect directly to a data source so that you can automatically load data as it becomes available.

This process is called ETL, short for Extract, Transform, Load. Put simply,  ETL just means, “connecting to a data source, structuring the data  in a way that it can be stored in database tables, and loading it into those tables.” There’s a lot more to it if you really want to get into it, but for our purposes, this is all you’ll need to know right now.

For this part of the tutorial, we are going to use an open-source ETL tool called Airbyte to connect to Hubspot and load some Contact data into the same Postgres database we set up before. Then we’ll run a couple of analytical queries to whet your appetite!

Setting up an ETL Tool (Airbyte)

I chose Airbyte for this demo because it is open source which means it’s free to use as long as you have a computer or a server to run it on. Much of it is based on the open-source work of another ETL tool called Stitch had been pushing before they got acquired by Talend. That project was called Singer.

The best thing about Airbyte for our Minimum Viable Data Stack is that they make running the open-source code so easy because it is packaged in yet another software framework called Docker. Yes, if you’re keeping score at home, that means we are using one open-source framework packaged in another open-source framework packaged in yet another open-source framework. Oh, the beauty of open-source!

To keep this tutorial manageable, I am going to completely “hand wave” the Docker setup. Luckily, it’s easy to do. Since this tutorial is for Mac, follow the Docker installation instructions for Mac.

🎵 Interlude music plays as you install Docker 🎵

Once you’ve installed Docker, you can run the Docker app which will then allow you to run apps called “containers.” Think of a container as “all the code and dependencies an app needs, packaged so you can basically just click start” (Instead of having to load all the individual dependencies one by one!)

Setting up Docker

We’re only going to download and run one app on Docker: Airbyte! 

Note: If you need help on the next few steps, Airbyte has a Slack community that is really helpful.

To download Airbyte the instructions are simple. Just open up your terminal (you can find this by using Mac’s spotlight search [cmd+space] and typing in “Terminal”). In the terminal just paste in the following three commands:

git clone https://github.com/airbytehq/airbyte.git
cd airbyte
docker-compose up

The commands tell your computer to copy all the code from their Github repository to your computer into a folder called “airbyte”, then “cd” aka “changing the directory” to the “airbyte” directory, then tell  Docker to run the Airbyte app container.

The beauty of this is that once you run this the first time from the command line, you can start Airbyte from the Docker UI by just clicking the “play” button.

Installing Airbyte via the command line

Airbyte will do a bit of setup and then your terminal will display the text shown above. At that point Airbyte is running on your computer and to use it, all you have to do is open your browser and go to http://localhost:8000

If you’re wondering how this works, Airbyte is running a webserver to provide a web interface to interact with the code that does all the heavy-ETL-lifting. If this were a common ETL tool like Stitch or Fivetran, the webserver and the ETL processes would run on an actual server instead of your personal computer.

If everything has gone according to plan you can go to  http://localhost:8000 and see the Airbyte app UI running and ready to start ETL-ing!

Setting up the Posgress connection in Airbyte

Setting up your first ETL job (Hubspot to Postgres)

I’ll admit, that last part got a little gruesome but don’t worry, it gets easier from here (as long as everything goes according to plan…)

From here we have to connect both our database and data sources to Airbyte so it has access to the source data and permission to write to the database.

I’ve chosen to load data from Hubspot because it is really easy to connect and because it shows the ups and downs of ETL… And of course, we’re still using Postgres.

Creating a Postgres Destination

All you have to do is paste in your database credentials from Postgres.app. Here are Airbyte’s instructions for connecting to Postgres

These are the same ODBC credentials we used to connect Postico in the last article. You can find them on the Postico home screen by clicking your database name. Note 

Posgres ODBC connection details

In my case, these are the settings:

  • Name: I called it “Postgres.app” but it could be anything you want
  • Host: Use host.docker.internal (localhost doesn’t work with Docker. See instruction above)
  • Port: 5432 
  • Database Name: Mine is “trevorfox.” That’s the name of my default Postgres.app database
  • Schema: I left it as “public.” You might want to use schemas for organizational purposes. Schemas are “namespaces” and you can think of them as folders for your tables. 
  • User: Again, mine is “trevorfox” because that is my default from when I set up Postgres.app
  • Password: You can leave this blank unless you set up a password on in Postgres.app 

From there you can test your connection and you should see a message that says, “All connection tests passed!”

Creating a Hubspot Source

You’ll first need to retrieve your API key. Once you’ve got it, you can create a new Hubspot Source in Airbyte.

I used these settings:

  • Name: Hubspot
  • Source type: Hubspot
  • API Key:  My Hubspot  API key
  • start_date:  I used 2017-01-25T00:00:00Z which is the machine-readable timestamp for 1/1/2017
Setting up the Hubspot connection in Airbyte

Here’s a cute picture to celebrate getting this far!

Getting started with Airbyte

Creating the ETL Connection

Since we’ve already created a Destination and a Source, all we have to do is to tell Airbyte we want to extract from the Source and load data to the Destination. 

Go back to the Destinations screen and open your Postgres.app source, click “add source,” and choose your source. For me, this is the source I created called “Hubspot.”

Airbyte will then go and test both the Source and Destination. Once both tests succeed, you can set up your sync. 

Setting up an ETL job with Airbyte

There are a lot of settings! Luckily you can leave most of them as they are until you want to get more specific about how you store and organize your data. 

For now, set the Sync frequency to “manual,” and uncheck every Hubspot object besides Contacts.

In the future, you could choose to load more objects for more sophisticated analysis but starting with Contacts is good because it will be a lot faster to complete the first load and the analyses will still be pretty interesting.

 Click the “Set up connection” button at the bottom of the screen.

Setting up the first sync with Airbyte

You’ve created your first Connection! Click “Sync now” and start the ETL job!

As the sync runs, you’ll see lots of logs. If you look carefully, you’ll see some that read “…  Records read: 3000” etc. which will give you a sense of the progress of the sync.

Airbyte ETL logs

What’s happening in Postgres now?

Airbyte is creating temporary tables and loading all the data into those. It will then copy that data into its final-state tables. Those tables will be structured in a way that is a bit easier to analyze. This is some more of the T and L of the ETL process!

As the sync is running, you can go back to Postico and refresh the table list (cmd+R) to see new tables as they are generated. 

Let’s look at the data!

When the job completes, you’ll notice that Airbyte has created a lot of tables in the database. There is a “contacts” table, but there are a lot of others prefixed with “contacts_.”

Why so many tables? 

These are all residue from taking data from a JSON REST API and turning it all into tables. JSON is a really flexible way to organize data. Tables are not. So in order to get all that nested JSON data to fit nicely into tables, you end up with lots of tables to represent all the nesting.  The Contacts API resource alone generated 124 “contacts_” tables. See for yourself:

​​select count(tablename)
from pg_tables t
where t.tablename like 'contacts_%'

This query queries the Postgres system table called pg_tables which, as you probably guessed, contains a row for each table with some metadata. By counting the tables that match the prefix “contacts_,” you’ll see all the tables that come from the Contacts resource. 

Why you care about Data Modeling and ELT

In order to structure this data in a way that is more suitable for analysis, you’ll have to join the tables together and select columns you want to keep. That cleaning process plus other business logic and filtering is called data modeling

Recently it has become more common to model your data with SQL once it’s in the database  (rather than right after it is extracted and before it’s loaded into the database). This gave rise to the term ELT to clarify that most of the transformations are happening after the data has landed in the database. There is a lot to discuss here. I might have to double back on this at some point…

Previewing SQL data tables

Luckily, we can ignore the majority of these tables. We are going to focus on the “contacts” table.

Analyzing the Data

Let’s first inspect the data and get a feel for what it looks like. It’s good to start with a sanity check to make sure that all your contacts are in there. This query will tell you how many of the contacts made it into the database. That should match what you see in the Hubspot app.

select count(*)
from contacts

One of the first questions you’ll probably have is how are my contacts growing over time? This is a good query to demonstrate a few things: the imperfect way that ETL tools write data into tables, the importance of data modeling, and ELT in action.

In the screenshot above, you’ll notice that the “contacts” table has a bunch of columns but one of them is full of lots of data. The “properties” column represents a nested object within the Hubspot Contacts API response. That object has all the interesting properties about a  Contact like when it was created, what country they are from, and other data a business might store about their Contacts in Hubspot. 

Airbyte, by default, dumps the whole object into Postgres as a JSON field. This means you have to get crafty in order to destructure the data into columns. Here’s how you would get a Contact’s id, the data it was created. (This would be the first step towards contact count over time)

select c.properties->>'hs_object_id' id, 
	c.createdat::date
from contacts c
limit 10;

Notice the field, “c.properties->>’hs_object_id’.“ The “->>” is how you get a JSON object field from the JSON-typed fields.

To count new contacts by month, we can add a little aggregation to the query above.

select date_trunc('week', c.createdat::date) created_month,
	count(distinct c.properties->>'hs_object_id')  contact_count
from contacts c
group by created_month
order by created_month desc;

THIS IS IT! This is the beauty of analytics with a proper analytics stack. Tomorrow, the next day, and every day in the future, you can run the Hubspot sync and see up-to-date metrics in this report!

You’ll learn that the more queries you run, the more you’ll get tired of cleaning and formatting the data. And that, my friends, is why data modeling and ELT!

SQL analytics in Postico

I changed to dark mode since the last post :]

Looking Forward

At this point, the stack is pretty viable. We have a Postgres database (our data warehouse), an ETL process that will keep our data in sync with the data in the source systems (Airbyte), and the tools for analyzing this data (SQL and Postico). Now you can answer any question you might have about your Hubspot data at whatever frequency you like—and you’ll never have to touch a spreadsheet!

The foundation is set but there is still more inspiration ahead. The natural place to go from here is a deeper dive into analysis and visualization. 

In the next post, we’ll set up Metabase to visualize the Hubspot data and create a simple dashboard based on some SQL queries. From there, I imagine we’ll head towards reverse ETL and push the analysis back to Hubspot. =]

I hope this was interesting at the least and helpful if you’re brave enough to follow along. Let me know in the comments if you got hung up or if there are instructions I should add.

From Spreadsheets to SQL: Step One towards the Minimum Viable Data Stack

“A spreadsheet is just data in single-player mode”

Last week I made a mistake. I boldly claimed that “A spreadsheet is just data in single-player mode.” And while I stand by that claim. I didn’t expect to be called to account for it.

As it turns out, the post was pretty popular and I think I know why. To me it boils down to five factors. 

  1. The scale and application of data still growing (duh)
  2. There aren’t enough people with the skills to work with data at scale
  3. There are plenty of resources to learn SQL but the path to using it in the “real world” isn’t very clear
  4. The tools have caught up and basically, anybody with spreadsheet skills can set up a data stack that works at scale
  5. Now is a great time to  upskill and become more effective in almost any career take advantage of the demand

The hard part? You have to pull yourself away from spreadsheets for a while—go slow to go fast (and big).

You’ll thank yourself in the end. Being able to think about data at scale will change how you approach your work and being able to work at scale will increase your efficiency and impact. On top of that, it’s just more fun!

A Minimum Viable Data Stack

In the spirit of a true MVP, This first step is going to get you from spreadsheet to SQL and with the least amount of overhead and a base level of utility.

In the next hour you will:

  • Stand up an analytical database
  • Write a SQL query to replace a pivot table
  • Have basic tooling and process for a repeatable analytical workflow 

In the next hour you will not (yet):

  • Run anything on a cloud server (or be able to share reports beyond your computer)
  • Setup any continually updating data syncs for “live” reporting

But don’t underestimate this. Once you start to work with data in this way, you’ll recognize that the process is a lot less error-prone and repeatable than spreadsheet work because the code is simpler and it’s easier to retrace your steps.

Starting up a Postgres database

Postgres isn’t the first thing that people think of for massive-scale data warehousing, but it works pretty well for analytics—especially at this scale, it is definitely the easiest way to get started and of course, it’s free. Ultimately, you’ll be working with BigQuery, Snowflake, and if this were 2016, Redshift. 

I apologize in advance but this tutorial will be for a Mac. It won’t really matter once everything moves to the cloud, but I don’t own a Windows machine…

The easiest way to get a Postgres server running on a Mac is Postgres.app. It wraps everything in a shiny Mac UI and the download experience is no different than something like Spotify.

Congrats! You have installed a Postgres server on your local machine and it’s up and running!

Here are some instructions for installing Postgres on Windows. And here’s a list of  Postgres clients for Windows that you’d use instead of Postico.

Now let’s see how quickly we get connected to the database.

Your Postgres server is up and running

Connecting to Postgres

There are plenty of good SQL editors for Postgres but since we are keeping this MVP, I’m going to recommend Postico. Again, it has a simple Mac UI and is designed for more of an analytical workflow than hardcore database management. 

  • Step 1: Head over to https://eggerapps.at/postico/ and download the app
  • Step 2: Move the app to the Applications folder and then open it by double-clicking on the icon
  • Step 3: Create a database connection by clicking on the “New Favorite” button. Leave all fields blank; the default values are suitable for connecting to Postgres.app. Optionally provide a nickname, eg. “Postgres.app”. Click “Connect”
  • Step 4: Go back to Postico and choose the SQL Query icon
  • Step 5: Test your connection by running a query.
Create a new “Favorite” connection in Postico

Run the query “select * from pg_tables;” to see a list of all the tables in your Postgres database. Since you haven’t loaded any tables, you’ll just see a list of Postgres system tables that start with the prefix, “pg_.” As you probably guessed, the “pg” stands for Postgres.

Running your first SQL query in Postico

You’ve done it! You’ve started up a Postgres database, connected to it, and run your first query!

Loading data into Postgres

Ok, the boring stuff is out of the way and it’s only been about 15 minutes! Now we can get to the actual analysis. Next, let’s load some actual data into Postgres.

Loading tables in Postgres is a little bit different (aka more involved) than loading a CSV into Google Sheets or Excel. You have to tell the database exactly how each table should be structured and then what data should be added to each table. 

You might not yet know how to run CREATE TABLE commands but that’s ok. There are tools out there that will shortcut that process for us too. 

The imaginatively named, convertcsv.com generates the SQL commands to populate tables based on a CSV file’s contents. There are lots of ways to populate data into a database but again, this is an MVP. 

For this tutorial, I’m using the Google Analytics Geo Targets CSV list found here. Why? Because the file is big enough that it would probably run pretty slowly in a spreadsheet tool.

  • Step 1: Head over to https://www.convertcsv.com/csv-to-sql.htm
  • Step 2: Select the “Choose File” tab to upload a CSV file. 
  • Step 3: Change the table name in the Output Options section  where it says “Schema.Table or View Name:” to “geotargets”
  • Step 4: Scroll down to the Generate Output section and click the “CSV to SQL Insert” button to update the output, then copy the SQL commands
  • Step 5: Go back to Postico and click on the SQL Query icon
  • Step 6: Paste the SQL commands into the SQL window 
  • Step 7: Highlight the entirety of the SQL commands and click “Execute Statement”
Uploading a CSV file to convertcsv.com

You’ve loaded data into your database! Now you can run super fast analyses on this data.

Analyze your data!

You’ve already run some commands in the SQL window in the previous step. The good news is it’s always just as simple as that. Now analysis is basically just the repetition of writing a command into the SQL editor and viewing the results. 

Here’s a simple analytical query that would be the equivalent of creating a pivot table that counts the number of rows within each group. Paste this in to find the results.

select g.country_code, count(criteria_id) count_id
from geotargets g
group by g.country_code
order by count_id desc

You’ve done it! You’ve replaced a spreadsheet with SQL! 

But this is not the end. PRACTICE, PRACTICE, PRACTICE! Analysis in SQL needs to become as comfortable as sorting, filtering, and pivoting in a spreadsheet.

Looking forward

If you’re thinking to yourself, “this still feels like single-player mode…” you’re right. This is like the first level of a game where you play in single-player mode so you can learn how to play the game and avoid getting destroyed in a multiplayer scenario.

In fact, you probably wouldn’t do this type of analysis in a database unless you were going to pull in CSV files with millions of rows or if you were to pull in a bunch of other spreadsheets and join them all together. In those cases, you’d see significant performance improvements over an actual spreadsheet program.

The real utility of a database for analysis comes when you have data dynamically being imported from an ETL tool or custom code. On top of that, running a database (or data warehouse) on the cloud makes it possible for you and your team to access the data and reports in real-time instead of just doing analysis locally and sending it to someone else as a CSV or other type of report document. Hopefully, I don’t need to tell you why that is a bad process!

If I stay motivated… The next step will be to dynamically import data into the new Postgres database with an ETL tool called Airbyte which also runs on your computer. At that point, the scale and complexity of the analysis will really increase.  

After that, as long as I keep at it… the next step would be to set up a BigQuery instance on Google Cloud. At that point, you can combine a cloud-based business intelligence tool with Airbyte and BigQuery and start to get a taste of what a functioning modern data stack looks like. 

I hope this was a helpful start for you.  Let me know in the comments if you get hung up.

Groucho Test

The content

Using Hosted JSON-LD Files as applications/ld+json Scripts for SEO

Sometimes it’s just easier to separate concerns. Just like how stylesheets and .js scripts separate the form and function of the page from the presentation of the page, the same can be done with the JSON-LD schematic markup / structured data of the page. This example shows how you can add structured data for SEO using Javascript which in many cases may prove to be much easier than messing around with your server side code or CMS.

The following Javascript script shows how you can load a stored .jsonld file from your server onto your page as an application/ld+json script tag. Just add it to your page to add hosted JSON-LD files to your page.

How to Add JSON-LD Markup with Javascript

The code below does the following four steps.

  1. The js makes a call to the local .jsonld file
  2. When the file is returned, an application/ld+json script tag is created
  3. The contents of the .jsonld file is inserted as the contents of the script tag
  4. The tag data is ready to be consumed by other applications

The script uses jQuery but the same could be achieved with plain js or any other library/framework of choice. This code can also be added by a tag management system such as Google Tag Manager.

 <script>
   // Add a schema tag to your
   $.getJSON( "/your-schema-file.jsonld", function( data ) {
     $( "<script/>", {
       "type": "application/ld+json",
       "html": JSON.stringify(data)
     }).appendTo( "head" );
   });
 </script>

Does Google Think its Valid Structured Data?

Yes. See this demo for a live working example or go straight to Google’s Structured Data Testing Tool to see the valid results of the demo. Other crawlers may not recognize the script because the script is rendered to the page using Javascript. Therefore the crawler must be able to run javascript which is not all that common.

Creating .jsonld Files

To learn about every single minute detail of creating .jsonld files see this spec about syntax. But essentially, .jsonld files are no different syntactically than JSON files. It is only the specific way that the JSON-LD files signify entities that differs from JSON.

If you need to create multiple JSON-LD files, checkout this Bulk JSON-LD Generator for Google Sheets.

I hope you find this useful. I would love to hear your thoughts in the comments.