In this chapter, the Midianites (i.e. Moabites) lure the Israelites into worshiping Baal and sexual immorality.
First, a quick note: the Midianites and the Moabites are grouped together in this chapter. We also saw this in Num 22:7 when "the elders of Moab and the elders of Midian departed...", but I didn't mention it then because it's largely immaterial to the story. It still is, but I figure it could get confusing if people think they are two separate nations but the bible considers them equivalent.
Anyway, while it's not obvious, this chapter is a continuation of the story of Balaam because what is happening is Balak is trying to attack Israel again. Before, he sought to curse Israel and thereby defeat them, and that failed because the LORD protected Israel. Now he is trying to attack Israel by "deceiving" them into committing idolatry and sexual immorality, which would strip them of their divine protection. It's a clever strategy, really: if the LORD is protecting them, then the most logical place to attack is to fracture their relationship with the LORD.
What it tells us about the Israelites is that they remain deeply immature and disinclined to follow the LORD. This is 40 years after the rebellion of Ex 32 and after the spies returned an unfavorable report of the promised land in Num 13-14. While the punishment of this chapter is less severe, the offenses are pretty bad. Bowing down to other gods and sacrificing to other gods violates the ten commandments of Ex 20. They additionally commit sexual immorality with Moabite women (verse 1). All of this probably points to their history: before Moses, the Israelites probably worshiped many of the gods of Egypt. Arguably one of the big reasons why the Israelites were taken out of Egypt was to separate them from these many gods whom they worshiped.
I talked about sacrificing to idols a bit in Lev 17. In particular, that chapter prohibited any sacrifice that was not brought before the tent of the LORD, and I speculated that this prohibition is intended to precisely stop this kind of behavior. It is essentially a hedonistic feast that combines three elements: eating sacrificed animals, worshiping the god to whom it was offered and sexual immorality. The LORD intends to replace this with offerings made in the Levitical system, before the tabernacle, to direct the people's worship towards himself.
The point I'm trying to make is not "sexual immorality is bad", we can all figure that out. The point I'm trying to make is the inherent connection that we see here because the "sacrifices of their gods", "the people ate" and "bowed down to their gods" (all from v. 2). I don't think people in our time naturally associate feasts with worshiping a particular god, but that appears to be the way of things in this time.
Anyway, the LORD is enraged and commands that "all the leaders" be executed. We are only given the example of a single man, Zimri, who is slain for his idolatry, because he brought a Midianite woman (probably to have sex) while the rest of the congregation is "weeping at the doorway of the tent of meeting", so this presents a fairly strong contrast to us. Incidentally, there is also a plague that kills 24,000, similar to the plague that broke out in Num 16.
As a whole, this chapter approximately continues the escalation of sin, although it is not the same as the rebellions against Moses we saw before. What's perhaps most striking about this chapter is the jealousy of Phinehas, who strikes down Zimri, a leader of Israel. So far the only people who seemed to have zeal for the LORD were Moses, Joshua and Caleb. So Phinehas is also marked for success because of his actions.
The long term effect of this episode is that it further engenders hostility between Moab and Israel, although we had already seen a prediction of this hostility from Balaam in the prior chapter. The LORD calls it "hostility", "tricks", and they "deceived you". This hostility will continue for generations to greater and lesser extents.