How One Should Pay Attention to Both Business and Technical Sides of Advertisement Campaigns

How often do you encounter overlaps in your work schedule? In some cases, planning may be the main activity of your company. For example, in logistics and production warehouse management, overlaying certain business processes on top of each other helps to see the current loading schedule and to create a plan for future loading. But, there are cases when visualizing possible overlaps leads to the prevention of an unacceptable situation.

Therefore, let’s consider an example of planning advertising campaigns. In this case, there is a great risk of overlapping with competitors’ advertising campaigns in the same locations. The number of locations is limited, as is the number of advertising spaces. So, let’s see in detail what can be done.

What Can Be Done From a Business Point of View

The end goal of a campaign varies from one company to another. Whether you want to improve brand visibility, reach out to a new audience, or increase sales, you will need to do a lot of planning. It’s a time-consuming and tiring process, but it greatly helps teams to understand what they are doing and why. Therefore, first of all, it is important to understand the essence of an advertisement campaign and advertising campaign planning and to define what is necessary for planning one.

An advertising campaign is a strategy designed for a specific theme and goal and aimed at the achievement of desired results. As we said, the end goal is different for each business. As for the advertising campaign planning, it is basically the process of determining the milestones for a campaign. What should be done is one needs to gather all the information about the product that will be advertised, target audience, company competitors, distribution channel, etc. It is vital to compile data from various sources in order to have a clear picture of the company’s further actions.

To plan an advertising campaign, one should also know the stages it contains. Therefore, let’s look at the commonly used steps.

Preparatory

The whole planning you do is what the first stage includes. You need to set the goals in order to define the tasks for your team. Also, you should set advertising objectives to know how to inform customers about your product, persuade them to choose your brand over others, and remind clients where they can find your product or services. Decide which channels you will be using, whether it will be social media, billboards, or other sources. The design is also important. For example, if your product is an application, the end-users should be able to understand how to interact with its UI/UX design. And, of course, you need to plan your budget.

Meridian

This is basically the deployment stage of all your plans. After you deal with the planning, all the data gathering, and content compiling, your next move is to execute the plan. To make the booking needed for the advertisement campaigns easier, many companies use booking and scheduling software.

Source: Task Management and Tracking Tool For Creative Industries

Final

The final stage includes the analysis of your campaign. Pay attention to all the KPIs, evaluate the results. Is the company successful? Does it generate sales? Proper supervision is advised, because you may need some changes in plans if something goes wrong.

Read Also Why Dealing with Complaints Is Important and How Complaint Management System Can Become a Game-Changer

What Can Be Done From a Technical Point of View

Now, you have a basic idea of what to do. But, what about the technical part? Which tools can be used and what can be done?

So, let’s imagine a situation. Due to the delivery of new models to the BMW dealership, your advertising agency has been commissioned to run a billboard campaign throughout the city from 15/05 to 30/06, including in front of competing brand dealerships. However, the billboard in front of the Audi dealership is booked for Mercedes until 31/05 – leaving an interval from 1/06 to 30/06. The billboard in front of the Lexus dealership is rented for Infiniti from 15/06 – the interval is limited from 15/05 to 15/06. All the above restrictions should be taken into account, but it is better to see them in advance.

However, what if we imagine a situation with a larger advertising campaign for a fast-food restaurant chain or a mass-market clothing store – there could be hundreds of advertising points and just as many overlaps with other advertising campaigns. Only one agency can have a large number of such campaigns, and with the help of modern technological tools, chaos in digital planning can be avoided.

The solution can be found by comparing the types of data stored in the database:

An advertising campaign (event) has a start date and an end date.
Each location for advertising refers to an event but can have its own start and end dates.
When planning an event for a location, we track the availability of time slots.

Instead of searching the database through SQL queries, it is easier to use TypeORM to represent records from the database as separate objects.

External fields “places” and “events” link a record in the “event_places” table – nothing special. The EventPlacesEntity class is created, linked to the “event_places” table with fields id, event, place, startDate, and endDate.

The “id” field is a unique key for combinations of “event” and “place“, and the “startDate” and “endDate” fields represent the start and end dates of the event.

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
  Unique
} from 'typeorm';

import {EventEntity} from './event.entity';
import {PlaceEntity} from './place.entity';

@Entity({name: 'event_places'})
@Unique('UK_EVENT_PLACE', ['event', 'place'])
export class EventPlaceEntity {
  @PrimaryGeneratedColumn({unsigned: true})
  id: number;

  @ManyToOne(() => EventEntity, {nullable: false, onDelete: 'CASCADE'})
  @JoinColumn({name: 'event_id'})
  event: EventEntity;

  @ManyToOne(() => PlaceEntity, {nullable: false, onDelete: 'CASCADE'})
  @JoinColumn({name: 'place_id'})
  place: PlaceEntity;

  @Column({name: 'start_date', type: 'date', nullable: true})
  startDate: Date;

  @Column({name: 'end_date', type: 'date', nullable: true})
  endDate: Date;
}

import {

Column,

Entity,

JoinColumn,

ManyToOne,

PrimaryGeneratedColumn,

Unique

} from 'typeorm';

import {EventEntity} from './event.entity';

import {PlaceEntity} from './place.entity';

@Entity({name: 'event_places'})

@Unique('UK_EVENT_PLACE', ['event', 'place'])

export class EventPlaceEntity {

@PrimaryGeneratedColumn({unsigned: true})

id: number;

@ManyToOne(() => EventEntity, {nullable: false, onDelete: 'CASCADE'})

@JoinColumn({name: 'event_id'})

event: EventEntity;

@ManyToOne(() => PlaceEntity, {nullable: false, onDelete: 'CASCADE'})

@JoinColumn({name: 'place_id'})

place: PlaceEntity;

@Column({name: 'start_date', type: 'date', nullable: true})

startDate: Date;

@Column({name: 'end_date', type: 'date', nullable: true})

endDate: Date;

}

Defining a time slot for advertising campaigns consists of two tasks:

Checking the availability of a free date, which is also checking the intersection of two time slots;
Coordination of the availability of all locations.

Let’s take a closer look at checking the intersection of two time slots. As an example, let’s use the DHTMLX Scheduler.

There is a time slot TS1 (black color) and TS2 (red color), each with a start date SD and an end date ED. In fact, we need to check 4 possible options:

TS1.sd < TS2.sd and TS1.ed < TS2.sd – The end date of TS1 intersects with the start date of TS2
TS1.sd < TS2.sd and TS1.ed > TS2.sd – The start and end dates of TS2 are within the TS1 framework
TS1.sd > TS2.sd and TS1.ed > TS2.sd – The end date of TS2 intersects with the start date of TS1
TS1.sd > TS2.sd and TS1.ed < TS2.sd – The start and end dates of TS1 are within the TS2 framework

Alternatively, replace the overlap check with a check for TS2 being outside of TS1. In other words:

TS2.sd > TS1.ed
TS2.ed < TS1.sd

Thus, the check can be implemented using SQL and executed in the database, which significantly speeds up execution.

Now, let’s look at the coordination of location availability. In cases where the advertising campaign is extensive and there are few free time slots, calling a loop with a function to iterate through free time slots will lead to excessive load on the database and slow down the application itself. The reverse approach would be an effective solution – using a function that returns an array of places that will be occupied in the specified time slot and suggesting new time slots for these places within the advertising campaign.

This approach will significantly speed up the visualization of advertising campaigns on the Webix Gantt planner and reduce the load on the database.

Let’s take a closer look at the parameters for the checkBusyEventPlaces function:

placeIds: number[] – an array of “id” for “place” that need to be checked;

startDate: string – start date for checking;

endDate: string – end date for checking;

excludeEventIds: number[] – an array of “id” for “event” that need to be excluded from checking;

shortReport: boolean – if we just need to check dates without additional information, set this parameter to true, by default it is false;

customRepository: Repository<EventPlaceEntity> – the repository that will be used for searching, by default – null;

import {HttpStatus, Injectable, UnprocessableEntityException} from '@nestjs/common';
import {InjectRepository} from '@nestjs/typeorm';
import {format} from 'date-fns';
import {Repository} from 'typeorm';


import {adjustDateForDBOptional, suggestedIntervalShift} from '../utils/date.utils';
import {EventPlaceEntity} from './entities/event-place.entity';

@Injectable()
export class EventPlacesService {
  constructor(
    @InjectRepository(EventPlaceEntity)
    private readonly eventPlacesRepository: Repository<EventPlaceEntity>
  ) {}

  public async checkBusyEventPlaces(
    placeIds: number[],
    startDate: Date,
    endDate: Date,
    excludeEventIds?: number[],
    shortReport = false,
    customRepository: Repository<EventPlaceEntity> = null
  ): Promise<any> {
    const eventPlaceRepository =
      customRepository || this.eventPlacesRepository;

    const req = eventPlaceRepository
      .createQueryBuilder('eventPlace')
      .leftJoin('eventPlace.event', 'event')
      .leftJoin('eventPlace.place', 'place')
      .where(
        'COALESCE(eventPlace.startDate, event.startDate) <= :endDate',
        {endDate}
      )
      .andWhere(
        'COALESCE(eventPlace.endDate, event.endDate) >= :startDate',
        {startDate}
      );

    if (placeIds.length) {
      req.andWhere(
        'eventPlace.place_id IN(:placeIds)',
        {
          placeIds
        }
      );
    }
    if (excludeEventIds && excludeEventIds.length) {
      req.andWhere(
        'eventPlace.event_id NOT IN(:excludeEventIds)',
        {
          excludeEventIds
        }
      );
    }
    let busyEventPlaces = await req.getMany();

    if (busyEventPlaces.length) {
      const busyCount = busyEventPlaces.length;
      if (shortReport) {
        throw new UnprocessableEntityException(
          `${busyCount} places are booked by another event(s)`
        );
      }
      const busyEventPlacesIds = busyEventPlaces.map(eventPlace => eventPlace.id);
      busyEventPlaces = await eventPlaceRepository
        .createQueryBuilder('eventPlace')
        .leftJoinAndSelect('eventPlace.event', 'event')
        .leftJoinAndSelect('eventPlace.place', 'place')
        .where('eventPlace.id IN (:busyEventPlacesIds)', {busyEventPlacesIds})
        .limit(2500)
        .getMany();
      const busyPlacesSet = new Set();
      const messages = busyEventPlaces.map((busyEventPlace) => {
        const localStartDate = new Date(
          busyEventPlace.startDate ||
          busyEventPlace.event.startDate
        );
        const localEndDate = new Date(
          busyEventPlace.endDate || busyEventPlace.event.endDate
        );

        const suggestedShifting = suggestedIntervalShift(
          {
            start: startDate,
            end: endDate
          },
          {start: localStartDate, end: localEndDate}
        );

        busyPlacesSet.add(busyEventPlace.place.id);
        return {
          id: busyEventPlace.id,
          eventId: busyEventPlace.event.id,
          eventName: `${busyEventPlace.event.name}`,
          placeId: busyEventPlace.place.id,
          placeName: busyEventPlace.place.name,
          eventRange: {
            start: format(localStartDate, 'yyyy-MM-dd'),
            end: format(localEndDate, 'yyyy-MM-dd')
          },
          suggestedShifting: {
            start: adjustDateForDBOptional(suggestedShifting.start, null),
            end: adjustDateForDBOptional(suggestedShifting.end, null)
          }
        };
      });
      throw new UnprocessableEntityException({
        error: `${busyCount} places are booked by another event(s)`,
        message: messages,
        totalCount: placeIds.length,
        conflictsCount: busyCount,
        busyCount: busyPlacesSet.size,
        statusCode: HttpStatus.UNPROCESSABLE_ENTITY
      });
    }
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

import {HttpStatus, Injectable, UnprocessableEntityException} from '@nestjs/common';

import {InjectRepository} from '@nestjs/typeorm';

import {format} from 'date-fns';

import {Repository} from 'typeorm';

import {adjustDateForDBOptional, suggestedIntervalShift} from '../utils/date.utils';

import {EventPlaceEntity} from './entities/event-place.entity';

@Injectable()

export class EventPlacesService {

constructor(

@InjectRepository(EventPlaceEntity)

private readonly eventPlacesRepository: Repository<EventPlaceEntity>

) {}

public async checkBusyEventPlaces(

placeIds: number[],

startDate: Date,

endDate: Date,

excludeEventIds?: number[],

shortReport = false,

customRepository: Repository<EventPlaceEntity> = null

): Promise<any> {

const eventPlaceRepository =

customRepository || this.eventPlacesRepository;

const req = eventPlaceRepository

.createQueryBuilder('eventPlace')

.leftJoin('eventPlace.event', 'event')

.leftJoin('eventPlace.place', 'place')

.where(

'COALESCE(eventPlace.startDate, event.startDate) <= :endDate',

{endDate}

)

.andWhere(

'COALESCE(eventPlace.endDate, event.endDate) >= :startDate',

{startDate}

);

if (placeIds.length) {

req.andWhere(

'eventPlace.place_id IN(:placeIds)',

{

placeIds

}

);

}

if (excludeEventIds && excludeEventIds.length) {

req.andWhere(

'eventPlace.event_id NOT IN(:excludeEventIds)',

{

excludeEventIds

}

);

}

let busyEventPlaces = await req.getMany();

if (busyEventPlaces.length) {

const busyCount = busyEventPlaces.length;

if (shortReport) {

throw new UnprocessableEntityException(

`${busyCount} places are booked by another event(s)`

);

}

const busyEventPlacesIds = busyEventPlaces.map(eventPlace => eventPlace.id);

busyEventPlaces = await eventPlaceRepository

.createQueryBuilder('eventPlace')

.leftJoinAndSelect('eventPlace.event', 'event')

.leftJoinAndSelect('eventPlace.place', 'place')

.where('eventPlace.id IN (:busyEventPlacesIds)', {busyEventPlacesIds})

.limit(2500)

.getMany();

const busyPlacesSet = new Set();

const messages = busyEventPlaces.map((busyEventPlace) => {

const localStartDate = new Date(

busyEventPlace.startDate ||

busyEventPlace.event.startDate

);

const localEndDate = new Date(

busyEventPlace.endDate || busyEventPlace.event.endDate

);

const suggestedShifting = suggestedIntervalShift(

{

start: startDate,

end: endDate

{start: localStartDate, end: localEndDate}

);

busyPlacesSet.add(busyEventPlace.place.id);

return {

id: busyEventPlace.id,

eventId: busyEventPlace.event.id,

eventName: `${busyEventPlace.event.name}`,

placeId: busyEventPlace.place.id,

placeName: busyEventPlace.place.name,

eventRange: {

start: format(localStartDate, 'yyyy-MM-dd'),

end: format(localEndDate, 'yyyy-MM-dd')

suggestedShifting: {

start: adjustDateForDBOptional(suggestedShifting.start, null),

end: adjustDateForDBOptional(suggestedShifting.end, null)

}

};

});

throw new UnprocessableEntityException({

error: `${busyCount} places are booked by another event(s)`,

message: messages,

totalCount: placeIds.length,

conflictsCount: busyCount,

busyCount: busyPlacesSet.size,

statusCode: HttpStatus.UNPROCESSABLE_ENTITY

});

}

As a result of the function’s work, we get the “suggestedShifting” parameter. This parameter suggests a suitable free time slot for a place that is not available in the originally required time slot. This is exactly what is needed.

Using the method presented above, the agency will find it much easier and more efficient to plan an advertising campaign without the risk of missing a duplicate or overlapping with a previously booked date. The efficiency of this solution is due to the optimization of the time that an employee will spend on making the right decision instead of analyzing unnecessary information.

Thanks to TypeORM, it was possible to simplify interaction with the database and obtain the result of processing the request in an understandable form, without complex SQL queries.

Read Also Working with Multiple Databases Simultaneously Using NestJS and TypeORM

The technical side of a campaign can be done by the outsourcing companies that are able to develop custom software solutions. The specialists can implement the relevant campaign management tools that will enhance the business side of the process.

Conclusions

Effective use of time for decision-making leads to resource savings, and the saved resources are directed towards the development of the company. The development of the company inevitably leads to scaling, and scaling will be much more efficient with the ability to make quick decisions. Therefore, it is vital to plan your advertisement campaign thoroughly and understand which tools can be beneficial for the process and outcome.

If you want to know more about the tools that can be used for your campaign, such as booking systems, data visualization apps, or other options that you can implement in your own software, please contact us and our experts will be able to offer you the best solution.