One footfall number for 70+ brands, when every source counted something different

Alshaya measured how many people came through its doors in at least five different ways, and none of them agreed. I owned the business side of a single footfall model that brought those sources into one trusted view of traffic, measured the same way across 70+ brands and across the Middle East, Turkey and Central Europe, so the business could finally put traffic next to sales and read conversion consistently.

I did not design the database or write the pipelines. I owned the problem, the definitions, the validation and the delivery, working across the data architect, the engineering team and the BI team. That distinction matters, and I come back to it at the end.

The problem

Leaders wanted a simple answer: are we turning foot traffic into sales? That answer was surprisingly hard to give, because footfall did not mean one thing.

The sources looked like this.

ARES count sensors, the legacy system, installed at store entrances across most locations, counting entries hour by hour.

EveryAngle AI cameras, the newer system, rolling out from December with far richer analytics. I worked directly with the EveryAngle team, including their CEO, to identify what their platform actually produced and how it would feed ours.

Simphony and MyMicros POS, used by restaurants and cafes that have no sensor or camera at the door. Here footfall is a guest count entered at the venue, based on what staff observe, and it exists only at daily level.

Google Analytics, where the closest thing to footfall online is a web session.

Regional feeds from Turkey and Central Europe, arriving in their own shapes on their own timelines.

Five kinds of counting, three levels of detail, and a rollout that meant any given store might be on the old system, the new one, or briefly both. With no shared definition, you could not put traffic and sales side by side with confidence. Conversion was not comparable between a store, a restaurant and the website, let alone across countries. Operations and brand teams were making staffing and performance calls on numbers that quietly meant different things.

What I owned

The data architect designed the model, with dimensions for brand, store, date and hour. My job was not the schema. It was the business meaning behind every number flowing into it.

I went source by source to find where each figure actually came from and what it truly counted. I wrote the SQL to interrogate it, defined the business logic (what qualifies as footfall in each channel, which hours count as valid trading hours, how to handle the cases that do not fit), and translated those rules for the engineers so ingestion landed correctly. I ran the validation and UAT, and worked with the BI team on the reporting layer above it.

From then on I was the single point of contact for the model. When a number looked wrong, or a new source or region came online, the question came to me.

The design principle: do not add unlike things

The key decision was that these sources cannot simply be summed. A person walking through a shop door, a guest paying in a restaurant, and a session on a website are not the same event. Adding them produces one clean number that is quietly meaningless.

So the principle was honesty over false precision. Keep each channel faithful to what it actually measures. Align them on what they genuinely share, which is brand, store, date, hour and valid trading hours. Be explicit where they do not align, such as the restaurant data that only exists daily and therefore cannot appear in an hourly view.

That is what made one model trustworthy. Not that every source looked identical, but that everyone knew exactly what each number counted and when it was fair to compare.

Three investigations

Most of the real work was not design. It was answering “why does this number look wrong?” Three cases stand out, and they went three different ways.

A store in Saudi Arabia was reporting from two systems at once. During the camera rollout, IT was meant to remove the legacy sensor when installing the AI camera. Here we were receiving both. It looked like a rollout error, and the obvious fix was to suppress one feed.

We called the store first. It turned out the site was partly renovated and had two floors. The renovated section had the new camera. The older section still had the sensor. Both feeds were correct, and together they were the only complete picture of that store. We updated the logic to reflect it.

The lesson: an outlier is a question, not a verdict. The data was right and our assumption was wrong, and the only way to know was to ask someone who could see the building.

A cafe was reporting roughly double what it should. This one was a genuine fault. The venue appeared in both MyMicros, the legacy POS, and Simphony, the newer Oracle system. Normally a restaurant runs one. During migration both are live, one in production and one in test, and our pipeline was ingesting from both and counting the same guests twice.

The fix came from reading the source system properly rather than patching the output. The POS carried an active and inactive status flag. We filtered to the production system and the duplication disappeared.

The lesson: during any migration, expect two systems to be live at once, and look for the flag that tells you which one is real before you start writing exceptions.

Some stores had no footfall at all. Tracing back through the layers, the records were present in the bronze layer and missing in silver. The data had arrived. Something in the transformation was dropping it.

The silver logic was filtering records on a partial-data condition that was stricter than intended, and valid rows were failing it. Once we identified the cause, the transformation was corrected and the data flowed.

The lesson: when data exists upstream and disappears downstream, the filter is the first suspect, not the source.

What changed

The business got one footfall view across physical and digital channels, on definitions everyone had agreed. Traffic could sit next to sales, so conversion read the same way across brands and countries instead of being reinvented in every report. Operations could look at traffic against sales by store and by hour. New regions had a clear path to plug in, because the rules for adding a source were already written down. And the metric had an owner, which is what keeps a shared number trusted long after the first dashboard ships.

What I would tell another analyst

On a single-source-of-truth project, the hard part is almost never the pipeline. It is agreeing what the number means, and then owning that meaning as sources, regions and people change. Define the business logic before engineering starts. Keep each source honest to what it actually measures instead of forcing everything into one shape. And make sure one person is clearly accountable, because a number is only trusted when someone can answer, without hesitating, exactly what it counts.

There is a second thing I took from it, and it is less comfortable.

Look again at that third investigation. I was reading data in a bronze layer, finding it missing in silver, and identifying that the transformation was at fault. I knew that architecture well enough to debug it from the outside. But the fix lived in code I did not write, in a pipeline I did not build, in a model I did not design. Every time.

In a company that size, that is normal and correct. Specialists own their layers. But it left me with a fair question about my own range, and eventually I decided the honest way to answer it was to go and build the whole thing myself, from source to dashboard, and find out where I struggled.

That is the next post.

← Back to Projects