How can I amend my AMPScript to create dynamic content by Location?

Question

I'm trying to pull certain values from a DE depending on the Subscriber location. Please see below for details:

Data extension 01 - "SubscriberList" (to use for sending):

SubscriberKey | Location  | Name | Email
=================================================
SK01          | Melbourne | Bill | [email protected]
SK02          | Sydney    | Jane | [email protected]
SK03          | Newcastle | Tom  | [email protected]
SK04          | null      | Amy  | [email protected]
SK05          | Perth     | Ted  | [email protected]

Data extension 02 - "NewsletterContent":

Category | Location | Heading | Text
News     | Melbourne | NewsTitle01 | NewsContent01<
News     | Sydney Perth | NewsTitle02 | NewsContent02
News     | Null | NewsTitle03 | NewsContent03
Events   | Newcastle | EventsTitle01 | EventsContent01
Events   | Brisbane | EventsTitle02 | EventsContent02
Events   | Sydney | EventsTitle03 | EventsContent03

Current AMPScript:

%%[
var @rows, @row, @rowCount, @Category, @i
set @Category = AttributeValue("Category")
set @Category = "News"
set @rows = LookupRows("NewsletterContent","Category", @Category)
set @rowCount = rowcount(@rows)
if @rowCount > 0 then
  for @i = 1 to @rowCount do
    var @Heading, @Text
    set @row = row(@rows, @i)
    set @Heading = field(@row,"Heading")
    set @Text = field(@row,"Text")
]%%
%%=v(@Heading)=%%
%%=v(@Text)=%%

%%[
next @i ]%%
%%[ else ]%%
%%[ endif ]%%

Current output:

NewsTitle01
NewsContent01

NewsTitle02
NewsContent02

NewsTitle03
NewsContent03

Requirements:

1. Since I’ve set the category as “News”, it’s only pulling News values from NewsletterContent. Is there any way I could make it so that it pulls “Events” values as well?
2. Current output pulls all the data that is in NewsletterContent. I am trying to refine it so that the relevant content is displayed depending on the subscriber’s location. For example:

• If Location = Sydney in SubscriberList, News and Events content in NewsletterContent for Sydney is displayed.
• Where NewsletterContent location is null, the content gets displayed for all subscribers regardless of their location.

How would I go about doing all of the above?

Thank you in advance!

Answer 1

1. You can use BuildRowsetFromString function to achieve the result Something like this:

%%[
  set @categoryList = "News**Events"
  set @categoryRows = BuildRowSetFromString(@categoryList, "**")
  if RowCount(@categoryRows) > 0 then
    for @i= 1 to RowCount(@categoryRows) do
      set @row = Row(@categoryRows, @i)
      set @Category = Field(@row, 1)
      /* you put your processing here to pull the heading and text 
       E.g. LookupRows("NewsletterContent","Category", @Category)
      so on....
       */
    
    next @i
  ENDIF

]%%

2. To pull the data based on location, then you need to lookup at location attribute. Put a validation on location value to check whether the location is null, otherwise it shows the default content. I would do something like this

%%[

set @Location= AttributeValue("Location")
set @rows = LookupRows("NewsletterContent","Location", @Location)
if RowCount(@rows) > 0 then
  /* pull the heading and text after that */
else
  set @heading = 'default heading'
  set @text = 'default content'
    
ENDIF

]%%

Note: the code above is not the full code, you need to adjust based on what you need.