Improve Log File Processing Times with Lookup Tables

Put data in Delivery Performance report log files into tables that contain IDs only. Put non-ID metadata in separate lookup tables to help reduce file size and processing times.

Log File Metadata Increases File Size and Processing Time

A typical log file used by the Delivery Performance report usually contains thousands of rows and dozens of columns. It consists of numeric IDs and human-readable information such as names for creatives, advertisers, insertion orders, etc.

This non-ID information is referred to as metadata (i.e., information about other information) and gets written in each row of the log file.

However, the Delivery Performance report mainly works with the IDs in the log file. The metadata is useful, but repetitious. It increases file size and data ingestion times.

Reduce File Size and Shorten Processing Time With Index Tables

To help improve performance, your main data file should contain IDs only. Put metadata in a separate lookup (or index) table and link those records to the main file with a key variable common to both.

How Lookup Tables Reduce File Size

Let’s say you have a data file that looks similar to the one below.

User ID
Ad ID
Ad Name
Order ID
Order Name
Advertiser ID
Advertiser Name
1
111
Shoe A
456
Sneakers
27
Company A
2
111
Shoe A
456
Sneakers
27
Company A
3
111
Shoe A
456
Sneakers
27
Company A
4
222
Shoe B
789
Hiking
14
Company B
5
222
Shoe B
789
Hiking
14
Company B

Here’s the same log file with the metadata removed. The file is smaller and easier to process when it consists of IDs only.

User ID
Ad ID
Order ID
Advertiser ID
1
111
456
27
2
111
456
27
3
111
456
27
4
222
789
14
5
222
789
14

The lookup file below holds the metadata and can be linked back to the main file with the Ad ID. Note the size as well. Instead of repeating each advertiser several times, you only need one reference for each.

Ad ID
Ad Name
Order Name
Advertiser Name
111
Shoe A
Sneakers
Company A
222
Shoe B
Hiking
Company B

APIs Can Eliminate the Need for Lookup Tables

If your ad serving system has an API, you might not need to send metadata in a lookup file. We may be able to get that information through the API. When this is the case, your log files should contain IDs only. We’ll work with you to determine if metadata can be obtained through an API.

de293fbf-b489-49b0-8daa-51ed303af695